Materialize/test/sqllogictest/postgres/join-lateral.slt

# 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;