drop schema if exists lateral_with_dop_test;
create schema lateral_with_dop_test;
set search_path=lateral_with_dop_test;
SET query_dop = 4;
CREATE TABLE tenk1 (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name
);
COPY tenk1 FROM '@abs_srcdir@/data/tenk.data';
CREATE TABLE INT4_TBL(f1 int4);
INSERT INTO INT4_TBL(f1) VALUES
(' 0 '),
('123456 '),
(' -123456'),
('2147483647'), -- largest and smallest values
('-2147483647');
VACUUM INT4_TBL;
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
INSERT INTO INT8_TBL VALUES
(' 123 ',' 456'),
('123 ','4567890123456789'),
('4567890123456789','123'),
(+4567890123456789,'4567890123456789'),
('+4567890123456789','-4567890123456789');
VACUUM INT8_TBL;
CREATE TABLE INT2_TBL(f1 int2);
INSERT INTO INT2_TBL(f1) VALUES
('0 '),
(' 1234 '),
(' -1234'),
('32767'), -- largest and smallest values
('-32767');
VACUUM INT2_TBL;
--
-- Test LATERAL
--
select unique2, x.*
from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x;
explain (costs off)
select unique2, x.*
from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x;
select unique2, x.*
from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss;
explain (costs off)
select unique2, x.*
from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss;
explain (costs off)
select unique2, x.*
from int4_tbl x cross join lateral (select unique2 from tenk1 where f1 = unique1) ss;
select unique2, x.*
from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true;
explain (costs off)
select unique2, x.*
from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true;
-- check scoping of lateral versus parent references
-- the first of these should return int8_tbl.q2, the second int8_tbl.q1
select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl;
select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl;
-- lateral with function in FROM
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
-- don't need the explicit LATERAL keyword for functions
explain (costs off)
select count(*) from tenk1 a, generate_series(1,two) g;
-- lateral with UNION ALL subselect
explain (costs off)
select * from generate_series(100,200) g,
lateral (select * from int8_tbl a where g = q1 union all
select * from int8_tbl b where g = q2) ss;
select * from generate_series(100,200) g,
lateral (select * from int8_tbl a where g = q1 union all
select * from int8_tbl b where g = q2) ss;
-- lateral with VALUES
explain (costs off)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x;
-- lateral with VALUES, no flattening possible
explain (costs off)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;
-- lateral injecting a strange outer join condition
explain (costs off)
select * from int8_tbl a,
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
on x.q2 = ss.z
order by a.q1, a.q2, x.q1, x.q2, ss.z;
select * from int8_tbl a,
int8_tbl x left join lateral (select a.q1 from int4_tbl y) ss(z)
on x.q2 = ss.z
order by a.q1, a.q2, x.q1, x.q2, ss.z;
-- lateral reference to a join alias variable
select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1,
lateral (select x) ss2(y);
select * from (select f1 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1,
lateral (values(x)) ss2(y);
select * from ((select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1) j,
lateral (select x) ss2(y);
-- lateral references requiring pullup
select * from (values(1)) x(lb),
lateral generate_series(lb,4) x4;
select * from (select f1/1000000000 from int4_tbl) x(lb),
lateral generate_series(lb,4) x4;
select * from (values(1)) x(lb),
lateral (values(lb)) y(lbcopy);
select * from (values(1)) x(lb),
lateral (select lb from int4_tbl) y(lbcopy);
select * from
int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2);
select * from
int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
select x.* from
int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1,
lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
select v.* from
(int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1)
left join int4_tbl z on z.f1 = x.q2,
lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy);
select v.* from
(int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1)
left join int4_tbl z on z.f1 = x.q2,
lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy);
explain (verbose, costs off)
select * from
int8_tbl a left join
lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;
select * from
int8_tbl a left join
lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1;
explain (verbose, costs off)
select * from
int8_tbl a left join
lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1;
select * from
int8_tbl a left join
lateral (select *, coalesce(a.q2, 42) as x from int8_tbl b) ss on a.q2 = ss.q1;
-- lateral can result in join conditions appearing below their
-- real semantic level
explain (verbose, costs off)
select * from int4_tbl i left join
lateral (select * from int2_tbl j where i.f1 = j.f1) k on true;
select * from int4_tbl i left join
lateral (select * from int2_tbl j where i.f1 = j.f1) k on true;
explain (verbose, costs off)
select * from int4_tbl i left join
lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true;
select * from int4_tbl i left join
lateral (select coalesce(i) from int2_tbl j where i.f1 = j.f1) k on true;
explain (verbose, costs off)
select * from int4_tbl a,
lateral (
select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2)
) ss;
select * from int4_tbl a,
lateral (
select * from int4_tbl b left join int8_tbl c on (b.f1 = q1 and a.f1 = q2)
) ss;
-- lateral reference in a PlaceHolderVar evaluated at join level
explain (verbose, costs off)
select * from
int8_tbl a left join lateral
(select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from
int8_tbl b cross join int8_tbl c) ss
on a.q2 = ss.bq1;
select * from
int8_tbl a left join lateral
(select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from
int8_tbl b cross join int8_tbl c) ss
on a.q2 = ss.bq1;
-- case requiring nested PlaceHolderVars
explain (verbose, costs off)
select * from
int8_tbl c left join (
int8_tbl a left join (select q1, coalesce(q2,42) as x from int8_tbl b) ss1
on a.q2 = ss1.q1
cross join
lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
) on c.q2 = ss2.q1,
lateral (select ss2.y offset 0) ss3;
-- case that breaks the old ph_may_need optimization
explain (verbose, costs off)
select c.*,a.*,ss1.q1,ss2.q1,ss3.* from
int8_tbl c left join (
int8_tbl a left join
(select q1, coalesce(q2,f1) as x from int8_tbl b, int4_tbl b2
where q1 < f1) ss1
on a.q2 = ss1.q1
cross join
lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
) on c.q2 = ss2.q1,
lateral (select * from int4_tbl i where ss2.y > f1) ss3;
-- check processing of postponed quals (bug #9041)
explain (verbose, costs off)
select * from
(select 1 as x offset 0) x cross join (select 2 as y offset 0) y
left join lateral (
select * from (select 3 as z offset 0) z where z.z = x.x
) zz on zz.z = y.y;
-- a new postponed-quals issue (bug #17768)
explain (costs off)
select * from int4_tbl t1,
lateral (select * from int4_tbl t2 inner join int4_tbl t3 on t1.f1 = 1
inner join (int4_tbl t4 left join int4_tbl t5 on true) on true) ss;
-- check dummy rels with lateral references (bug #15694)
explain (verbose, costs off)
select * from int8_tbl i8 left join lateral
(select *, i8.q2 from int4_tbl where false) ss on true;
explain (verbose, costs off)
select * from int8_tbl i8 left join lateral
(select *, i8.q2 from int4_tbl i1, int4_tbl i2 where false) ss on true;
-- check handling of nested appendrels inside LATERAL
select * from
((select 2 as v) union all (select 3 as v)) as q1
cross join lateral
((select * from
((select 4 as v) union all (select 5 as v)) as q3)
union all
(select q1.v)
) as q2;
-- check the number of columns specified
SELECT * FROM (int8_tbl i cross join int4_tbl j) ss(a,b,c,d);
-- check we don't try to do a unique-ified semijoin with LATERAL
explain (verbose, costs off)
select * from
(values (0,9998), (1,1000)) v(id,x),
lateral (select f1 from int4_tbl
where f1 = any (select unique1 from tenk1
where unique2 = v.x offset 0)) ss;
select * from
(values (0,9998), (1,1000)) v(id,x),
lateral (select f1 from int4_tbl
where f1 = any (select unique1 from tenk1
where unique2 = v.x offset 0)) ss;
-- check proper extParam/allParam handling (this isn't exactly a LATERAL issue,
-- but we can make the test case much more compact with LATERAL)
explain (verbose, costs off)
select * from (values (0), (1)) v(id),
lateral (select * from int8_tbl t1,
lateral (select * from
(select * from int8_tbl t2
where q1 = any (select q2 from int8_tbl t3
where q2 = (select greatest(t1.q1,t2.q2))
and (select v.id=0)) offset 0) ss2) ss
where t1.q1 = ss.q2) ss0;
select * from (values (0), (1)) v(id),
lateral (select * from int8_tbl t1,
lateral (select * from
(select * from int8_tbl t2
where q1 = any (select q2 from int8_tbl t3
where q2 = (select greatest(t1.q1,t2.q2))
and (select v.id=0)) offset 0) ss2) ss
where t1.q1 = ss.q2) ss0;
-- test some error cases where LATERAL should have been used but wasn't
select f1,g from int4_tbl a, (select f1 as g) ss;
select f1,g from int4_tbl a, (select a.f1 as g) ss;
select f1,g from int4_tbl a cross join (select f1 as g) ss;
select f1,g from int4_tbl a cross join (select a.f1 as g) ss;
-- SQL:2008 says the left table is in scope but illegal to access here
select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true;
select f1,g from int4_tbl a full join lateral generate_series(0, a.f1) g on true;
-- check we complain about ambiguous table references
select * from
int8_tbl x cross join (int4_tbl x cross join lateral (select x.f1) ss);
-- LATERAL can be used to put an aggregate into the FROM clause of its query
select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
-- check behavior of LATERAL in UPDATE/DELETE
create temp table xx1 as select f1 as x1, -f1 as x2 from int4_tbl;
-- error, can't do this:
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = x1) ss;
update xx1 set x2 = f1 from (select * from int4_tbl where f1 = xx1.x1) ss;
-- can't do it even with LATERAL:
update xx1 set x2 = f1 from lateral (select * from int4_tbl where f1 = x1) ss;
-- we might in future allow something like this, but for now it's an error:
update xx1 set x2 = f1 from xx1, lateral (select * from int4_tbl where f1 = x1) ss;
-- also errors:
delete from xx1 using (select * from int4_tbl where f1 = x1) ss;
delete from xx1 using (select * from int4_tbl where f1 = xx1.x1) ss;
delete from xx1 using lateral (select * from int4_tbl where f1 = x1) ss;
--
-- test LATERAL reference propagation down a multi-level inheritance hierarchy
-- produced for a multi-level partitioned table hierarchy.
--
create table join_pt1 (a int, b int, c varchar) partition by range(a)(
PARTITION P1 VALUES LESS THAN(100), PARTITION P2 VALUES LESS THAN(200), PARTITION P3 VALUES LESS THAN(MAXVALUE));
insert into join_pt1 values (1, 1, 'x'), (101, 101, 'y');
create table join_ut1 (a int, b int, c varchar);
insert into join_ut1 values (101, 101, 'y'), (2, 2, 'z');
explain (verbose, costs off)
select t1.b, ss.phv from join_ut1 t1 left join lateral
(select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss
on t1.a = ss.t2a order by t1.a;
select t1.b, ss.phv from join_ut1 t1 left join lateral
(select t2.a as t2a, t3.a t3a, least(t1.a, t2.a, t3.a) phv
from join_pt1 t2 join join_ut1 t3 on t2.a = t3.b) ss
on t1.a = ss.t2a order by t1.a;
-- cross apply
create table departments(department_name varchar(50), department_id int);
create table employees(employee_id int, department_id int, last_name varchar(50));
insert into departments values ('Marketing', 1);
insert into departments values ('Public Relations', 2);
insert into departments values ('Operations', 3);
insert into departments values ('Develop', 4);
insert into departments values ('Research', 5);
insert into departments values ('CEO', 6);
insert into departments values ('CFO', 7);
insert into employees values(1, 1, 'zhangsan1');
insert into employees values(2, 1, 'zhangsan2');
insert into employees values(3, 1, 'zhangsan3');
insert into employees values(4, 1, 'zhangsan4');
insert into employees values(5, 2, 'lisi1');
insert into employees values(6, 2, 'lisi2');
insert into employees values(7, 2, 'lisi3');
insert into employees values(8, 2, 'lisi4');
insert into employees values(9, 3, 'wangwu1');
insert into employees values(10, 3, 'wangwu2');
insert into employees values(11, 3, 'wangwu3');
insert into employees values(12, 3, 'wangwu4');
insert into employees values(13, 4, 'heliu1');
insert into employees values(14, 4, 'heliu2');
insert into employees values(15, 4, 'heliu3');
insert into employees values(16, 4, 'heliu4');
insert into employees values(17, 5, 'chenqi1');
insert into employees values(18, 5, 'chenqi2');
insert into employees values(19, 5, 'chenqi3');
insert into employees values(20, 5, 'chenqi4');
create function fn_salar (departmentid int) returns table (employee_id int, department_id int, last_name varchar) language sql as 'select employee_id, department_id, concat(last_name,last_name) as last_name2 from employees WHERE department_id = departmentid';
select* from departments d cross apply employees e where e.department_id = d.department_id;
select* from departments d cross apply (select d.department_id from employees x) e where e.department_id = d.department_id;
SELECT * FROM employees AS e CROSS APPLY fn_Salar(e.department_id) AS f;
SELECT d.department_name, v.employee_id, v.last_name
FROM departments d CROSS APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v
WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations')
ORDER BY d.department_name, v.employee_id;
select* from departments d outer apply employees e where e.department_id = d.department_id;
select* from departments d cross apply (select d.department_id from employees x) e where e.department_id = d.department_id;
SELECT * FROM employees AS e outer APPLY fn_Salar(e.department_id) AS f;
SELECT d.department_name, v.employee_id, v.last_name
FROM departments d outer APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v
WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations')
ORDER BY d.department_name, v.employee_id;
-- view
create view v1 as SELECT d.department_name, v.employee_id, v.last_name
FROM departments d CROSS APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v
WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations')
ORDER BY d.department_name, v.employee_id;
create view v2 as SELECT d.department_name, v.employee_id, v.last_name
FROM departments d outer APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v
WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations')
ORDER BY d.department_name, v.employee_id;
create view v3 as select v.* from
(int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1)
left join int4_tbl z on z.f1 = x.q2,
lateral (select x.q1,y.q1 union all select x.q2,y.q2) v(vx,vy);
create view v4 as select count(*) from tenk1 a, lateral generate_series(1,two) g;
select * from v1;
select * from v2;
select * from v3;
select * from v4;
-- plsql
create or replace procedure plpgsql_1 (param1 varchar, param2 varchar, param3 varchar, param4 varchar )
IS
BEGIN
create table tt1 as SELECT d.department_name, v.employee_id, v.last_name
FROM departments d CROSS APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v
WHERE d.department_name IN (param1, param2, param3)
ORDER BY d.department_name, v.employee_id;
END;
/
create or replace procedure plpgsql_2 (param1 varchar, param2 varchar, param3 varchar, param4 varchar)
IS
BEGIN
create table tt2 as SELECT d.department_name, v.employee_id, v.last_name
FROM departments d OUTER APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v
WHERE d.department_name IN (param1, param2, param3)
ORDER BY d.department_name, v.employee_id;
END;
/
create or replace procedure plpgsql_3 (param1 varchar, param2 varchar, param3 varchar, param4 varchar)
IS
BEGIN
create table tt3 as select id from (values (0), (1)) v(id),
lateral (select * from int8_tbl t1,
lateral (select * from
(select * from int8_tbl t2
where q1 = any (select q2 from int8_tbl t3
where q2 = (select greatest(t1.q1,t2.q2))
and (select v.id=0)) offset 0) ss2) ss
where t1.q1 = ss.q2) ss0;
END;
/
create or replace procedure plpgsql_4 (param1 varchar, param2 varchar, param3 varchar, param4 varchar)
IS
BEGIN
create table tt4 as select * from
int8_tbl a left join lateral
(select b.q1 as bq1, c.q1 as cq1, least(a.q1,b.q1,c.q1) from
int8_tbl b cross join int8_tbl c) ss
on a.q2 = ss.bq1;
END;
/
call plpgsql_1(param1:='Marketing', param2:='Operations', param3:='Public Relations', param4:='CEO');
call plpgsql_2(param1:='Marketing', param2:='Operations', param3:='Public Relations', param4:='CEO');
call plpgsql_3(param1:='Marketing', param2:='Operations', param3:='Public Relations', param4:='CEO');
call plpgsql_4(param1:='Marketing', param2:='Operations', param3:='Public Relations', param4:='CEO');
select * from tt1;
select * from tt2;
select * from tt3;
select * from tt4;
drop PROCEDURE plpgsql_1;
drop PROCEDURE plpgsql_2;
drop PROCEDURE plpgsql_3;
drop PROCEDURE plpgsql_4;
-- cursor expression
create or replace procedure test_cursor_1
as
company_name varchar(100);
last_name_name varchar(100);
type ref_cur_type is ref cursor;
my_cur ref_cur_type;
cursor c1 is SELECT e.last_name, CURSOR(SELECT d.department_name FROM departments d CROSS APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER BY d.department_name, v.employee_id) abc FROM employees e;
begin
OPEN c1;
loop
fetch c1 into company_name, my_cur;
exit when c1%notfound;
raise notice 'company_name : % %',company_name, my_cur;
loop
fetch my_cur into last_name_name;
exit when my_cur%notfound;
raise notice ' last_name_name : %',last_name_name;
end loop;
end loop;
end;
/
call test_cursor_1();
drop procedure test_cursor_1;
create or replace procedure test_cursor_2
as
company_name varchar(100);
last_name_name varchar(100);
type ref_cur_type is ref cursor;
my_cur ref_cur_type;
cursor c1 is SELECT e.last_name, CURSOR(SELECT d.department_name FROM departments d outer APPLY (SELECT * FROM employees e WHERE e.department_id = d.department_id) v WHERE d.department_name IN ('Marketing', 'Operations', 'Public Relations') ORDER BY d.department_name, v.employee_id) abc FROM employees e;
begin
OPEN c1;
loop
fetch c1 into company_name, my_cur;
exit when c1%notfound;
raise notice 'company_name : % %',company_name, my_cur;
loop
fetch my_cur into last_name_name;
exit when my_cur%notfound;
raise notice ' last_name_name : %',last_name_name;
end loop;
end loop;
end;
/
call test_cursor_2();
drop procedure test_cursor_2;
create or replace procedure test_cursor_3
as
company_name varchar(100);
last_name_name varchar(100);
type ref_cur_type is ref cursor;
my_cur ref_cur_type;
cursor c1 is SELECT CURSOR(select xq1::varchar from int8_tbl x left join (select q1,coalesce(q2,0) q2 from int8_tbl) y on x.q2 = y.q1, lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2)) abc;
begin
OPEN c1;
loop
fetch c1 into my_cur;
exit when c1%notfound;
loop
fetch my_cur into last_name_name;
exit when my_cur%notfound;
raise notice ' last_name_name : %',last_name_name;
end loop;
end loop;
end;
/
call test_cursor_3();
drop procedure test_cursor_3;
drop table tt1;
drop table tt2;
drop table tt3;
drop table tt4;
drop function fn_salar;
drop view v1;
drop view v2;
drop view v3;
drop view v4;
drop table tenk1;
drop table INT4_TBL;
drop table INT8_TBL;
drop table INT2_TBL;
drop table departments;
drop table employees;
set query_dop = 1;
-- clean
drop schema if exists lateral_with_dop_test cascade;
