# Copyright 1994, Regents of the University of California. # Copyright 1996-2019 PostgreSQL Global Development Group. # Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. # # This file is derived from the regression test suite in PostgreSQL. # The original file was retrieved on July 23, 2020 from: # # https://github.com/postgres/postgres/blob/5940ffb221316ab73e6fdc780dfe9a07d4221ebb/src/test/regress/expected/join.out # # The original source code is subject to the terms of the PostgreSQL # license, a copy of which can be found in the LICENSE file at the # root of this repository. mode cockroach statement ok CREATE TABLE int2_tbl (f1 smallint) statement ok INSERT INTO int2_tbl (f1) VALUES (0), (1234), (-1234), (32767), (-32767); statement ok CREATE TABLE int4_tbl (f1 int) statement ok INSERT INTO int4_tbl (f1) VALUES (0), (123456), (-123456), (2147483647), (-2147483647) statement ok CREATE TABLE int8_tbl (q1 bigint, q2 bigint) statement ok INSERT INTO int8_tbl VALUES (123, 456), (123, 4567890123456789), (4567890123456789, 123), (4567890123456789, 4567890123456789), (4567890123456789, -4567890123456789) statement ok CREATE TABLE tenk1 ( unique1 int, unique2 int, two int ) statement ok INSERT INTO tenk1 VALUES (2, 0, 0), (1, 3, 1), (6, 9, 0), (5, 8, 0), (0, 4, 0), (7, 7, 1), (9, 2, 1), (4, 6, 1), (8, 1, 0), (3, 5, 1) # NOTE(benesch): The tenk1 table is named as such because it is meant to contain # 10k rows. We include only 10 rows here because 10k rows causes Materialize to # absolutely fall over since the plans for a lot of these lateral joins are # absolutely horrible. statement ok CREATE TABLE onerow () statement ok INSERT INTO onerow DEFAULT VALUES query II colnames select unique2, x.* from tenk1 a, lateral (select * from int4_tbl b where f1 = a.unique1) x; ---- unique2 f1 4 0 query II colnames select unique2, x.* from int4_tbl x, lateral (select unique2 from tenk1 where f1 = unique1) ss; ---- unique2 f1 4 0 query II colnames,rowsort select unique2, x.* from int4_tbl x left join lateral (select unique1, unique2 from tenk1 where f1 = unique1) ss on true; ---- unique2 f1 4 0 NULL -123456 NULL -2147483647 NULL 123456 NULL 2147483647 # check scoping of lateral versus parent references # the first of these should return int8_tbl.q2, the second int8_tbl.q1 query III colnames,rowsort select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl ---- q1 q2 r 123 456 456 123 4567890123456789 4567890123456789 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 -4567890123456789 query III colnames,rowsort select *, (select r from (select q1 as q2) x, lateral (select q2 as r) y) from int8_tbl; ---- q1 q2 r 123 456 123 123 4567890123456789 123 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 # lateral with function in FROM query I colnames select count(*) from tenk1 a, lateral generate_series(1,two) g; ---- count 5 query III colnames,rowsort select * from generate_series(100,200) g (g), lateral (select * from int8_tbl a where g = q1 union all select * from int8_tbl b where g = q2) ss ---- g q1 q2 123 123 456 123 123 4567890123456789 123 4567890123456789 123 query I colnames select count(*) from tenk1 a, tenk1 b join lateral (values(a.unique1)) ss(x) on b.unique2 = ss.x; ---- count 10 query I colnames select count(*) from tenk1 a, tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x; ---- count 10 # lateral injecting a strange outer join condition query IIIII colnames 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; ---- q1 q2 q1 q2 z 123 456 123 456 NULL 123 456 123 4567890123456789 NULL 123 456 4567890123456789 -4567890123456789 NULL 123 456 4567890123456789 123 123 123 456 4567890123456789 123 123 123 456 4567890123456789 123 123 123 456 4567890123456789 123 123 123 456 4567890123456789 123 123 123 456 4567890123456789 4567890123456789 NULL 123 4567890123456789 123 456 NULL 123 4567890123456789 123 4567890123456789 NULL 123 4567890123456789 4567890123456789 -4567890123456789 NULL 123 4567890123456789 4567890123456789 123 123 123 4567890123456789 4567890123456789 123 123 123 4567890123456789 4567890123456789 123 123 123 4567890123456789 4567890123456789 123 123 123 4567890123456789 4567890123456789 123 123 123 4567890123456789 4567890123456789 4567890123456789 NULL 4567890123456789 -4567890123456789 123 456 NULL 4567890123456789 -4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 -4567890123456789 NULL 4567890123456789 -4567890123456789 4567890123456789 123 NULL 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 123 456 NULL 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 -4567890123456789 NULL 4567890123456789 123 4567890123456789 123 NULL 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 456 NULL 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 NULL 4567890123456789 4567890123456789 4567890123456789 123 NULL 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # lateral reference to a join alias variable query III colnames select * from (select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1, lateral (select x) ss2(y); ---- x f1 y 0 0 0 query III colnames,rowsort select * from (select f1 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1, lateral (values(x)) ss2(y); ---- x f1 y 0 0 0 123456 123456 123456 -123456 -123456 -123456 2147483647 2147483647 2147483647 -2147483647 -2147483647 -2147483647 query III colnames select * from ((select f1/2 as x from int4_tbl) ss1 join int4_tbl i4 on x = f1) j, lateral (select x) ss2(y); ---- x f1 y 0 0 0 # lateral references requiring pullup query II rowsort select * from (values(1)) x(lb), lateral generate_series(lb,4) x4; ---- 1 1 1 2 1 3 1 4 query II rowsort select * from (select f1/1000000000 from int4_tbl) x(lb), lateral generate_series(lb,4) x4; ---- -2 0 -2 1 -2 2 -2 3 -2 4 -2 -1 -2 -2 0 0 0 0 0 0 0 1 0 1 0 1 0 2 0 2 0 2 0 3 0 3 0 3 0 4 0 4 0 4 2 2 2 3 2 4 query II colnames select * from (values(1)) x(lb), lateral (values(lb)) y(lbcopy) ---- lb lbcopy 1 1 query II colnames select * from (values(1)) x(lb), lateral (select lb from int4_tbl) y(lbcopy); ---- lb lbcopy 1 1 1 1 1 1 1 1 1 1 query IIIIIII colnames,rowsort 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); ---- q1 q2 q1 q2 xq1 yq1 yq2 4567890123456789 -4567890123456789 NULL NULL 4567890123456789 NULL NULL 4567890123456789 123 123 456 4567890123456789 123 456 4567890123456789 123 123 4567890123456789 4567890123456789 123 4567890123456789 123 456 NULL NULL 123 NULL NULL 123 4567890123456789 4567890123456789 123 123 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 -4567890123456789 123 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 -4567890123456789 query IIIIIII colnames,rowsort 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); ---- q1 q2 q1 q2 xq1 yq1 yq2 4567890123456789 -4567890123456789 NULL NULL 4567890123456789 NULL NULL 4567890123456789 123 123 456 4567890123456789 123 456 4567890123456789 123 123 4567890123456789 4567890123456789 123 4567890123456789 123 456 NULL NULL 123 NULL NULL 123 4567890123456789 4567890123456789 123 123 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 -4567890123456789 123 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 -4567890123456789 query II colnames,rowsort 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); ---- q1 q2 123 456 123 4567890123456789 123 4567890123456789 123 4567890123456789 4567890123456789 123 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 query II colnames,rowsort 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); ---- vx vy 123 NULL 456 NULL 123 4567890123456789 4567890123456789 -4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 123 4567890123456789 123 123 4567890123456789 4567890123456789 123 123 456 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 NULL -4567890123456789 NULL query II colnames,rowsort 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); ---- vx vy 4567890123456789 NULL -4567890123456789 NULL 4567890123456789 123 123 456 4567890123456789 123 123 4567890123456789 123 NULL 456 NULL 123 4567890123456789 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 query II colnames,rowsort 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 from onerow union all select x.q2,y.q2 from onerow) v(vx,vy); ---- vx vy 4567890123456789 123 123 456 4567890123456789 123 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 123 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 123 4567890123456789 4567890123456789 -4567890123456789 123 NULL 456 NULL 4567890123456789 NULL -4567890123456789 NULL query IIIII rowsort select * from int8_tbl a left join lateral (select *, a.q2 as x from int8_tbl b) ss on a.q2 = ss.q1; ---- 123 456 NULL NULL NULL 123 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 123 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 NULL NULL NULL 4567890123456789 123 123 456 123 4567890123456789 123 123 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 query IIIII colnames,rowsort 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; ---- q1 q2 q1 q2 x 123 456 NULL NULL NULL 123 4567890123456789 4567890123456789 123 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 123 123 456 123 4567890123456789 123 123 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 123 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 -4567890123456789 4567890123456789 4567890123456789 -4567890123456789 NULL NULL NULL # lateral can result in join conditions appearing below their # real semantic level query II colnames,rowsort select * from int4_tbl i left join lateral (select * from int2_tbl j where i.f1 = j.f1) k on true; ---- f1 f1 0 0 123456 NULL -123456 NULL 2147483647 NULL -2147483647 NULL query IT colnames,rowsort select * from int4_tbl left join lateral (select coalesce(int4_tbl) from int2_tbl j where int4_tbl.f1 = j.f1) k on true; ---- f1 coalesce 0 (0) 123456 NULL -123456 NULL 2147483647 NULL -2147483647 NULL query IIII colnames,rowsort 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; ---- f1 f1 q1 q2 0 0 NULL NULL 0 123456 NULL NULL 0 -123456 NULL NULL 0 2147483647 NULL NULL 0 -2147483647 NULL NULL 123456 0 NULL NULL 123456 123456 NULL NULL 123456 -123456 NULL NULL 123456 2147483647 NULL NULL 123456 -2147483647 NULL NULL -123456 0 NULL NULL -123456 123456 NULL NULL -123456 -123456 NULL NULL -123456 2147483647 NULL NULL -123456 -2147483647 NULL NULL 2147483647 0 NULL NULL 2147483647 123456 NULL NULL 2147483647 -123456 NULL NULL 2147483647 2147483647 NULL NULL 2147483647 -2147483647 NULL NULL -2147483647 0 NULL NULL -2147483647 123456 NULL NULL -2147483647 -123456 NULL NULL -2147483647 2147483647 NULL NULL -2147483647 -2147483647 NULL NULL # TODO(benesch): least function is unsupported. # # lateral reference in a PlaceHolderVar evaluated at join level # query IIIII colnames,rowsort # 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; # ---- # q1 q2 bq1 cq1 least # 123 456 NULL NULL NULL # 123 4567890123456789 4567890123456789 123 123 # 123 4567890123456789 4567890123456789 123 123 # 123 4567890123456789 4567890123456789 123 123 # 123 4567890123456789 4567890123456789 123 123 # 123 4567890123456789 4567890123456789 123 123 # 123 4567890123456789 4567890123456789 123 123 # 123 4567890123456789 4567890123456789 4567890123456789 123 # 123 4567890123456789 4567890123456789 4567890123456789 123 # 123 4567890123456789 4567890123456789 4567890123456789 123 # 123 4567890123456789 4567890123456789 4567890123456789 123 # 123 4567890123456789 4567890123456789 4567890123456789 123 # 123 4567890123456789 4567890123456789 4567890123456789 123 # 123 4567890123456789 4567890123456789 4567890123456789 123 # 123 4567890123456789 4567890123456789 4567890123456789 123 # 123 4567890123456789 4567890123456789 4567890123456789 123 # 4567890123456789 123 123 123 123 # 4567890123456789 123 123 123 123 # 4567890123456789 123 123 123 123 # 4567890123456789 123 123 123 123 # 4567890123456789 123 123 4567890123456789 123 # 4567890123456789 123 123 4567890123456789 123 # 4567890123456789 123 123 4567890123456789 123 # 4567890123456789 123 123 4567890123456789 123 # 4567890123456789 123 123 4567890123456789 123 # 4567890123456789 123 123 4567890123456789 123 # 4567890123456789 4567890123456789 4567890123456789 123 123 # 4567890123456789 4567890123456789 4567890123456789 123 123 # 4567890123456789 4567890123456789 4567890123456789 123 123 # 4567890123456789 4567890123456789 4567890123456789 123 123 # 4567890123456789 4567890123456789 4567890123456789 123 123 # 4567890123456789 4567890123456789 4567890123456789 123 123 # 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # 4567890123456789 4567890123456789 4567890123456789 4567890123456789 4567890123456789 # 4567890123456789 -4567890123456789 NULL NULL NULL # check handling of nested appendrels inside LATERAL query II colnames,rowsort 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; ---- v v 2 4 2 5 2 2 3 4 3 5 3 3 # check we don't try to do a unique-ified semijoin with LATERAL query III colnames select * from (values (0,4), (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; ---- id x f1 0 4 0 query error column "f1" does not exist select f1,g from int4_tbl a, (select f1 as g) ss; query error column "a.f1" does not exist select f1,g from int4_tbl a, (select a.f1 as g) ss; query error column "f1" does not exist select f1,g from int4_tbl a cross join (select f1 as g) ss; query error column "a.f1" does not exist 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 query error the combining JOIN type must be INNER or LEFT for a LATERAL reference select f1,g from int4_tbl a right join lateral generate_series(0, a.f1) g on true; query error the combining JOIN type must be INNER or LEFT for a LATERAL reference 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 # query error table reference "x" is ambiguous # 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 # # TODO(benesch): when we support aggregates that refer exclusively to outer # columns, this case should still be disallowed, but with the following error # message instead: # # aggregate functions are not allowed in FROM clause of their own query level # # See: https://www.postgresql.org/message-id/1375925710.17807.13.camel%40vanquo.pezone.net query error aggregate functions that refer exclusively to outer columns not yet supported select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;