Materialize/test/sqllogictest/joins.slt

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

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_mz_join_core = true;
----
COMPLETE 0

statement ok
CREATE TABLE l (la int, lb text)

statement ok
CREATE TABLE r (ra int, rb text)

statement ok
INSERT INTO l VALUES (1, 'l1'), (2, 'l2'), (3, 'l3')

statement ok
INSERT INTO r VALUES (1, 'r1'), (3, 'r3'), (4, 'r4')

query ITIT rowsort
SELECT * FROM l LEFT JOIN r ON l.la = r.ra
----
1  l1  1     r1
2  l2  NULL  NULL
3  l3  3     r3

# This test may look the same as the last, but listing out the columns
# explicitly checks for regressions of database-issues#472.
query ITIT rowsort
SELECT l.la, l.lb, r.ra, r.rb FROM l LEFT JOIN r ON l.la = r.ra
----
1  l1  1     r1
2  l2  NULL  NULL
3  l3  3     r3

query ITIT rowsort
SELECT * FROM l RIGHT JOIN r ON l.la = r.ra
----
NULL  NULL  4  r4
1     l1    1  r1
3     l3    3  r3

# This test may look the same as the last, but listing out the columns
# explicitly checks for regressions of database-issues#472.
query ITIT rowsort
SELECT l.la, l.lb, r.ra, r.rb FROM l RIGHT JOIN r ON l.la = r.ra
----
NULL  NULL  4  r4
1     l1    1  r1
3     l3    3  r3

# Test that columns detected to be equivalent retain the names that the user
# asks for. Protects against regression of database-issues#429.
query II colnames,rowsort
SELECT ra, r.ra FROM l JOIN r ON l.la = r.ra LIMIT 0
----
ra  ra

# Test that columns detected to be equivalent retain the names that the user
# asks for. Protects against regression of database-issues#429.
query ITIT colnames,rowsort
SELECT * FROM l JOIN r ON l.la = r.ra LIMIT 0
----
la  lb  ra  rb

# Test that columns detected to be equivalent do not interfere with qualified
# wildcard expansion. Protects against regression of database-issues#1389.
query ITIT colnames
SELECT l.*, r.* FROM l JOIN r ON l.la = r.ra LIMIT 0
----
la  lb  ra  rb

# Like the last test, but with the equality reversed.
query ITIT colnames
SELECT l.*, r.* FROM l JOIN r ON r.ra = l.la LIMIT 0
----
la  lb  ra  rb

# Test that projections work through join plans with scalars.
query ITT rowsort
SELECT l1.la, l2.lb, l3.lb
FROM l as l1, l as l2, l as l3
WHERE l1.la + 1 = l2.la AND l3.la = l1.la + l2.la
----
1  l2  l3

# Test that join plans with scalars work in subqueries
query IT rowsort
SELECT l1.la, l1.lb
FROM l as l1
WHERE l1.la IN (
    SELECT l2.la + 1
    FROM l AS l2
    WHERE l2.la IN (
        SELECT l3.la + 1
        FROM l as l3
    )
)
----
3  l3

# Test that scalar expressions are introduced to join plans.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT l1.la, l2.lb, l3.lb
FROM l as l1, l as l2, l as l3
WHERE l1.la + 1 = l2.la AND l3.la = l1.la + l2.la
----
Explained Query:
  With
    cte l0 =
      Filter (#0{la}) IS NOT NULL // { arity: 2 }
        ReadStorage materialize.public.l // { arity: 2 }
    cte l1 =
      ArrangeBy keys=[[#0{la}]] // { arity: 2 }
        Get l0 // { arity: 2 }
  Return // { arity: 3 }
    Project (#0{la}, #2{lb}, #4{lb}) // { arity: 3 }
      Join on=(#1{la} = (#0{la} + 1) AND #3{la} = (#0{la} + #1{la})) type=delta // { arity: 5 }
        implementation
          %0:l0 » %1:l1[#0{la}]K » %2:l1[#0{la}]K
          %1:l1 » %0:l0[(#0{la} + 1)]K » %2:l1[#0{la}]K
          %2:l1 » %0:l0[×] » %1:l1[#0{la}]K
        ArrangeBy keys=[[], [(#0{la} + 1)]] // { arity: 1 }
          Project (#0{la}) // { arity: 1 }
            Get l0 // { arity: 2 }
        Get l1 // { arity: 2 }
        Get l1 // { arity: 2 }

Source materialize.public.l
  filter=((#0{la}) IS NOT NULL)

Target cluster: quickstart

EOF

# Confirm that a +1 can exist in a subquery based join.
# Note that the other +1 is found instead in a filter,
# because subquery planning re-uses the relation it wraps.
# It is perfectly acceptable for this plan to change, esp
# if it improves (i.e. the cross join is removed).

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT l1.la, l1.lb
FROM l as l1
WHERE l1.la IN (
    SELECT l2.la + 1
    FROM l AS l2
    WHERE l2.la IN (
        SELECT l3.la + 1
        FROM l as l3
    )
)
----
Explained Query:
  With
    cte l0 =
      Project (#0{la}) // { arity: 1 }
        ReadStorage materialize.public.l // { arity: 2 }
    cte l1 =
      CrossJoin type=differential // { arity: 2 }
        implementation
          %0[×] » %1:l0[×]
        ArrangeBy keys=[[]] // { arity: 1 }
          Distinct project=[#0{la}] // { arity: 1 }
            Get l0 // { arity: 1 }
        ArrangeBy keys=[[]] // { arity: 1 }
          Get l0 // { arity: 1 }
  Return // { arity: 2 }
    Project (#0{la}, #1{lb}) // { arity: 2 }
      Join on=(#0{la} = #2{la}) type=differential // { arity: 3 }
        implementation
          %1[#0]UKA » %0:l[#0]K
        ArrangeBy keys=[[#0{la}]] // { arity: 2 }
          ReadStorage materialize.public.l // { arity: 2 }
        ArrangeBy keys=[[#0{la}]] // { arity: 1 }
          Distinct project=[#0{la}] // { arity: 1 }
            Project (#0{la}) // { arity: 1 }
              Join on=(#1{la} = #2{la} = #3) type=delta // { arity: 4 }
                implementation
                  %0:l1 » %1[#0]UKA » %2[#0]UKA
                  %1 » %2[#0]UKA » %0:l1[#1]Kf
                  %2 » %1[#0]UKA » %0:l1[#1]Kf
                ArrangeBy keys=[[#1{la}]] // { arity: 2 }
                  Filter (#0{la} = (#1{la} + 1)) // { arity: 2 }
                    Get l1 // { arity: 2 }
                ArrangeBy keys=[[#0{la}]] // { arity: 1 }
                  Distinct project=[#0{la}] // { arity: 1 }
                    Project (#1{la}) // { arity: 1 }
                      Filter (#1{la}) IS NOT NULL // { arity: 2 }
                        Get l1 // { arity: 2 }
                ArrangeBy keys=[[#0]] // { arity: 1 }
                  Distinct project=[(#0{la} + 1)] // { arity: 1 }
                    Project (#0{la}) // { arity: 1 }
                      Filter (#0{la}) IS NOT NULL // { arity: 2 }
                        ReadStorage materialize.public.l // { arity: 2 }

Source materialize.public.l

Target cluster: quickstart

EOF

# Test that multiplicities in outer joins are preserved.
# Fixed in database-issues#931.
statement ok
CREATE TABLE l2 (la int, lb text)

statement ok
CREATE TABLE r2 (ra int, rb text)

statement ok
INSERT INTO l2 VALUES (1, 'l1'), (2, 'l2'),  (2, 'l2'), (3, 'l3')

statement ok
INSERT INTO r2 VALUES (1, 'r1'), (3, 'r3'), (4, 'r4'), (4, 'r4')

query ITIT rowsort
SELECT * FROM l2 LEFT JOIN r2 ON l2.la = r2.ra
----
1  l1  1     r1
2  l2  NULL  NULL
2  l2  NULL  NULL
3  l3  3     r3

query ITIT rowsort
SELECT * FROM l2 RIGHT JOIN r2 ON l2.la = r2.ra
----
NULL  NULL  4  r4
NULL  NULL  4  r4
1     l1    1  r1
3     l3    3  r3


# Test that outer joins work correctly in correlated subqueries (database-issues#1007)

query IT rowsort
SELECT * FROM l WHERE EXISTS (SELECT * from l as l2 LEFT JOIN r ON l.la = r.ra)
----
1  l1
2  l2
3  l3

query IT rowsort
SELECT * FROM l WHERE EXISTS (SELECT * from l as l2 LEFT JOIN LATERAL (SELECT * FROM r) r ON l.la = r.ra);
----
1  l1
2  l2
3  l3

query IT rowsort
SELECT * FROM r WHERE EXISTS (SELECT * from l RIGHT JOIN r as r2 ON l.la = r.ra)
----
4  r4
1  r1
3  r3

# Regression test for database-issues#1089.
query III colnames
SELECT * FROM
    (SELECT 1 AS baz) t1
    INNER JOIN (
        (SELECT 1 AS foo) t2
        INNER JOIN (SELECT 1 AS bar) t3 ON true
    ) ON foo = bar;
----
baz  foo  bar
1    1    1

# Test that join planning with constants does not panic due to missing arrangements.
# This test could vanish if we conclude that this is just an anomalous defect is the
# join planning, more than an interesting property to maintain.
query ITIIII rowsort
select * from
    l2,
    (values (1, 2), (2, 2)) as foo,
    (values (2, 3), (3, 3)) as bar
where
    foo.column1 = bar.column1
----
1  l1  2  2  2  3
2  l2  2  2  2  3
2  l2  2  2  2  3
3  l3  2  2  2  3

# Test that joins with an `(= #x null)` constraint pass records, rather than drop them.
query III rowsort
SELECT * FROM
    (((SELECT 1 FROM l2) LEFT JOIN
    (SELECT 1 FROM r2) ON false) LEFT JOIN (SELECT 1 FROM r2) ON false);
----
1  NULL  NULL
1  NULL  NULL
1  NULL  NULL
1  NULL  NULL

# Regression test for database-issues#1218. The gist is that we want to exercise the
# redundant join optimization on a join whose inputs contain projections. This
# turns out to be extremely hard to accomplish because the projection lifting
# optimization is very good at eliminating all but the top-level projection.
# Having this test seems better than not, but it's fragile.
statement ok
CREATE VIEW gh3914 AS VALUES (NULL::int)

query TTI
SELECT * FROM (
    (SELECT 'foo')
    RIGHT JOIN (
        (SELECT true) CROSS JOIN (SELECT 1 FROM gh3914 EXCEPT ALL (SELECT 456 WHERE false))
    ) ON true
)
----
foo  true  1


# Test for outer join planning.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM l LEFT JOIN r ON l.la = r.ra
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{la}]] // { arity: 2 }
        Filter (#0{la}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.l // { arity: 2 }
    cte l1 =
      Project (#0{la}, #1{lb}, #3{rb}) // { arity: 3 }
        Join on=(#0{la} = #2{ra}) type=differential // { arity: 4 }
          implementation
            %0:l0[#0{la}]K » %1:r[#0{ra}]K
          Get l0 // { arity: 2 }
          ArrangeBy keys=[[#0{ra}]] // { arity: 2 }
            Filter (#0{ra}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.r // { arity: 2 }
  Return // { arity: 4 }
    Union // { arity: 4 }
      Map (null, null) // { arity: 4 }
        Union // { arity: 2 }
          Negate // { arity: 2 }
            Project (#0{la}, #1{lb}) // { arity: 2 }
              Join on=(#0{la} = #2{la}) type=differential // { arity: 3 }
                implementation
                  %1[#0]UKA » %0:l0[#0{la}]K
                Get l0 // { arity: 2 }
                ArrangeBy keys=[[#0{la}]] // { arity: 1 }
                  Distinct project=[#0{la}] // { arity: 1 }
                    Project (#0{la}) // { arity: 1 }
                      Get l1 // { arity: 3 }
          ReadStorage materialize.public.l // { arity: 2 }
      Project (#0{la}, #1{lb}, #0{la}, #2{rb}) // { arity: 4 }
        Get l1 // { arity: 3 }

Source materialize.public.l
Source materialize.public.r
  filter=((#0{ra}) IS NOT NULL)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM l RIGHT JOIN r ON l.la = r.ra
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{ra}]] // { arity: 2 }
        Filter (#0{ra}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.r // { arity: 2 }
    cte l1 =
      Project (#0{la}, #1{lb}, #3{rb}) // { arity: 3 }
        Join on=(#0{la} = #2{ra}) type=differential // { arity: 4 }
          implementation
            %0:l[#0{la}]K » %1:l0[#0{ra}]K
          ArrangeBy keys=[[#0{la}]] // { arity: 2 }
            Filter (#0{la}) IS NOT NULL // { arity: 2 }
              ReadStorage materialize.public.l // { arity: 2 }
          Get l0 // { arity: 2 }
  Return // { arity: 4 }
    Union // { arity: 4 }
      Project (#2, #3, #0{ra}, #1{rb}) // { arity: 4 }
        Map (null, null) // { arity: 4 }
          Union // { arity: 2 }
            Negate // { arity: 2 }
              Project (#0{ra}, #1{rb}) // { arity: 2 }
                Join on=(#0{ra} = #2{la}) type=differential // { arity: 3 }
                  implementation
                    %1[#0]UKA » %0:l0[#0{ra}]K
                  Get l0 // { arity: 2 }
                  ArrangeBy keys=[[#0{la}]] // { arity: 1 }
                    Distinct project=[#0{la}] // { arity: 1 }
                      Project (#0{la}) // { arity: 1 }
                        Get l1 // { arity: 3 }
            ReadStorage materialize.public.r // { arity: 2 }
      Project (#0{la}, #1{lb}, #0{la}, #2{rb}) // { arity: 4 }
        Get l1 // { arity: 3 }

Source materialize.public.l
  filter=((#0{la}) IS NOT NULL)
Source materialize.public.r

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT * FROM l FULL JOIN r ON l.la = r.ra
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{la}]] // { arity: 2 }
        Filter (#0{la}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.l // { arity: 2 }
    cte l1 =
      ArrangeBy keys=[[#0{ra}]] // { arity: 2 }
        Filter (#0{ra}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.r // { arity: 2 }
    cte l2 =
      Project (#0{la}, #1{lb}, #3{rb}) // { arity: 3 }
        Join on=(#0{la} = #2{ra}) type=differential // { arity: 4 }
          implementation
            %0:l0[#0{la}]K » %1:l1[#0{ra}]K
          Get l0 // { arity: 2 }
          Get l1 // { arity: 2 }
    cte l3 =
      ArrangeBy keys=[[#0{la}]] // { arity: 1 }
        Distinct project=[#0{la}] // { arity: 1 }
          Project (#0{la}) // { arity: 1 }
            Get l2 // { arity: 3 }
  Return // { arity: 4 }
    Union // { arity: 4 }
      Project (#2, #3, #0{ra}, #1{rb}) // { arity: 4 }
        Map (null, null) // { arity: 4 }
          Union // { arity: 2 }
            Negate // { arity: 2 }
              Project (#0{ra}, #1{rb}) // { arity: 2 }
                Join on=(#0{ra} = #2{la}) type=differential // { arity: 3 }
                  implementation
                    %1:l3[#0]UKA » %0:l1[#0{ra}]K
                  Get l1 // { arity: 2 }
                  Get l3 // { arity: 1 }
            ReadStorage materialize.public.r // { arity: 2 }
      Map (null, null) // { arity: 4 }
        Union // { arity: 2 }
          Negate // { arity: 2 }
            Project (#0{la}, #1{lb}) // { arity: 2 }
              Join on=(#0{la} = #2{la}) type=differential // { arity: 3 }
                implementation
                  %1:l3[#0]UKA » %0:l0[#0{la}]K
                Get l0 // { arity: 2 }
                Get l3 // { arity: 1 }
          ReadStorage materialize.public.l // { arity: 2 }
      Project (#0{la}, #1{lb}, #0{la}, #2{rb}) // { arity: 4 }
        Get l2 // { arity: 3 }

Source materialize.public.l
Source materialize.public.r

Target cluster: quickstart

EOF

query ITIT rowsort
SELECT * FROM l INNER JOIN r ON mod(l.la, 2) = mod(r.ra, 2)
----
1 l1 1 r1
1 l1 3 r3
2 l2 4 r4
3 l3 1 r1
3 l3 3 r3

# Test that when both keys are expressions, the join is not planned as a cross
# join. Protects against regression of database-issues#1290.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM l INNER JOIN r ON mod(l.la, 2) = mod(r.ra, 2)
----
Explained Query:
  Join on=((#0{la} % 2) = (#2{ra} % 2)) type=differential // { arity: 4 }
    implementation
      %0:l[(#0{la} % 2)]K » %1:r[(#0{ra} % 2)]K
    ArrangeBy keys=[[(#0{la} % 2)]] // { arity: 2 }
      Filter (#0{la}) IS NOT NULL // { arity: 2 }
        ReadStorage materialize.public.l // { arity: 2 }
    ArrangeBy keys=[[(#0{ra} % 2)]] // { arity: 2 }
      Filter (#0{ra}) IS NOT NULL // { arity: 2 }
        ReadStorage materialize.public.r // { arity: 2 }

Source materialize.public.l
  filter=((#0{la}) IS NOT NULL)
Source materialize.public.r
  filter=((#0{ra}) IS NOT NULL)

Target cluster: quickstart

EOF

# Regression test for database-issues#1355, in which inter-view predicate pushdown would cause
# misoptimizations. Specifically, a predicate that was applied to only one use
# of a view could be incorrectly applied to all uses of that view.
#
# Inter-view (mis-)optimizations don't appear in the output of EXPLAIN, so we
# have to test this rather obliquely.

statement ok
CREATE TABLE t4362 (name text NOT NULL, id int NOT NULL)

statement ok
INSERT INTO t4362 VALUES ('a', 1), ('a', 2), ('b', 3)

statement ok
CREATE VIEW v4362 AS SELECT name, id FROM t4362

query TT
SELECT name, id FROM v4362 WHERE name = 'a'
----
a 1
a 2

query TT
SELECT name, id FROM v4362 WHERE name = (SELECT name FROM v4362 WHERE id = 1)
----
a 1
a 2

mode standard

# At the time of writing, for this test to properly validate inter-view
# optimization correctness, exactly one of the `Get materialize.public.t4362`
# nodes must be followed a `Filter` node. If that is not the case in the plan
# below, likely the test is no longer testing for the bug.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT name, id FROM v4362 WHERE name = (SELECT name FROM v4362 WHERE id = 1)
----
Explained Query:
  With
    cte l0 =
      Project (#0{name}) // { arity: 1 }
        Filter (#1{id} = 1) // { arity: 2 }
          ReadStorage materialize.public.t4362 // { arity: 2 }
  Return // { arity: 2 }
    Project (#0{name}, #1{id}) // { arity: 2 }
      Join on=(#0{name} = #2{name}) type=differential // { arity: 3 }
        implementation
          %0:t4362[#0{name}]K » %1[#0]K
        ArrangeBy keys=[[#0{name}]] // { arity: 2 }
          ReadStorage materialize.public.t4362 // { arity: 2 }
        ArrangeBy keys=[[#0{name}]] // { arity: 1 }
          Union // { arity: 1 }
            Get l0 // { arity: 1 }
            Project (#1) // { arity: 1 }
              FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
                Reduce aggregates=[count(*)] // { arity: 1 }
                  Project () // { arity: 0 }
                    Get l0 // { arity: 1 }

Source materialize.public.t4362

Target cluster: quickstart

EOF

mode cockroach

# Test implicitly coercible USING columns
statement ok
CREATE TABLE big_l (la int8, lb text)

statement ok
INSERT INTO big_l VALUES (1, 'big_l1'), (3, 'bigl_3'), (5, 'bigl_5')

query ITT
SELECT la, l.lb, big_l.lb FROM l JOIN big_l USING (la)
----
1  l1  big_l1
3  l3  bigl_3

query T multiline
EXPLAIN RAW PLAN FOR SELECT la, l.lb, big_l.lb FROM l JOIN big_l USING (la)
----
Project (#0, #1, #3)
  InnerJoin (integer_to_bigint(#0{la}) = #2{la})
    Get materialize.public.l
    Get materialize.public.big_l

Target cluster: quickstart

EOF

statement ok
CREATE TABLE join_fail (la date);

query error NATURAL/USING join column "la" types integer and date cannot be matched
SELECT la FROM l JOIN join_fail USING (la)

# test that joins properly handle null keys
statement ok
CREATE TABLE l3 (la int, lb text)

statement ok
CREATE TABLE r3 (ra int, rb text)

statement ok
INSERT INTO l3 VALUES (1, 'l1'), (2, 'l2'), (3, 'l3'), (NULL, 'l4')

statement ok
INSERT INTO r3 VALUES (1, 'r1'), (3, 'r3'), (4, 'r4'), (NULL, 'r5')

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT lb, rb FROM l3 INNER JOIN r3 ON la = ra
----
Explained Query:
  Project (#1{lb}, #3{rb}) // { arity: 2 }
    Join on=(#0{la} = #2{ra}) type=differential // { arity: 4 }
      implementation
        %0:l3[#0{la}]K » %1:r3[#0{ra}]K
      ArrangeBy keys=[[#0{la}]] // { arity: 2 }
        Filter (#0{la}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.l3 // { arity: 2 }
      ArrangeBy keys=[[#0{ra}]] // { arity: 2 }
        Filter (#0{ra}) IS NOT NULL // { arity: 2 }
          ReadStorage materialize.public.r3 // { arity: 2 }

Source materialize.public.l3
  filter=((#0{la}) IS NOT NULL)
Source materialize.public.r3
  filter=((#0{ra}) IS NOT NULL)

Target cluster: quickstart

EOF

query TT
SELECT lb, rb FROM l3, r3 WHERE la = ra
----
l1  r1
l3  r3

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT lb, rb FROM l3 INNER JOIN r3 ON la = ra OR (ra IS NULL AND la IS NULL)
----
Explained Query:
  Project (#1{lb}, #3{rb}) // { arity: 2 }
    Join on=(#0{la} = #2{ra}) type=differential // { arity: 4 }
      implementation
        %0:l3[#0{la}]K » %1:r3[#0{ra}]K
      ArrangeBy keys=[[#0{la}]] // { arity: 2 }
        ReadStorage materialize.public.l3 // { arity: 2 }
      ArrangeBy keys=[[#0{ra}]] // { arity: 2 }
        ReadStorage materialize.public.r3 // { arity: 2 }

Source materialize.public.l3
Source materialize.public.r3

Target cluster: quickstart

EOF

query TT rowsort
SELECT lb, rb FROM l3 INNER JOIN r3 ON la = ra OR (ra IS NULL AND la IS NULL)
----
l1  r1
l3  r3
l4  r5

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT lb, rb FROM l3 INNER JOIN r3 ON (la IS NULL AND ra IS NULL) OR la = ra
----
Explained Query:
  Project (#1{lb}, #3{rb}) // { arity: 2 }
    Join on=(#0{la} = #2{ra}) type=differential // { arity: 4 }
      implementation
        %0:l3[#0{la}]K » %1:r3[#0{ra}]K
      ArrangeBy keys=[[#0{la}]] // { arity: 2 }
        ReadStorage materialize.public.l3 // { arity: 2 }
      ArrangeBy keys=[[#0{ra}]] // { arity: 2 }
        ReadStorage materialize.public.r3 // { arity: 2 }

Source materialize.public.l3
Source materialize.public.r3

Target cluster: quickstart

EOF

query TT rowsort
SELECT lb, rb FROM l3 INNER JOIN r3 ON (la IS NULL AND ra IS NULL) OR la = ra
----
l1  r1
l3  r3
l4  r5

# Regression test for https://github.com/MaterializeInc/database-issues/issues/2364

statement ok
CREATE TABLE t1 (f1 int, f2 int);

statement ok
CREATE TABLE t2 (f1 int, f2 int);

statement ok
CREATE TABLE t3 (f1 int, f3 int);

statement ok
CREATE TABLE t4 (f3 int, f4 int);

query T
SELECT f1 FROM t1 JOIN t2 USING (f1);
----

query error column reference "f2" is ambiguous
SELECT f1, f2 FROM t1 JOIN t2 USING (f1);

query T
SELECT f1, t1.f2 AS f1 FROM t1 JOIN t2 USING (f1);
----

query error column reference "f1" is ambiguous
SELECT f1, t1.f2 AS f1 FROM t1 JOIN t2 USING (f1) ORDER BY f1;

query error  common column name "f2" appears more than once in left table
SELECT * FROM t1 LEFT JOIN t2 USING (f1) RIGHT JOIN t3 USING (f2);

statement ok
INSERT INTO t1 VALUES
    (1, 2),
    (3, 4);

query TTT
SELECT *
FROM t1
    LEFT JOIN t2 USING (f1)
ORDER BY f1;
----
1  2  NULL
3  4  NULL

query TTT
SELECT *
FROM t1
    LEFT JOIN t2 USING (f1)
WHERE t2.f1 IS NOT NULL
ORDER BY f1;
----

statement ok
INSERT INTO t2 VALUES
    (3, 4),
    (5, 6);

statement ok
INSERT INTO t3 VALUES
    (3, 4),
    (7, 8);

statement ok
INSERT INTO t4 VALUES
    (4, 3),
    (9, 10);

# Test join using aliases
# Adapted from: https://github.com/postgres/postgres/blob/master/src/test/regress/sql/join.sql

query T
SELECT * FROM t1 JOIN t2 USING (f1) AS x WHERE t1.f2 = 1;
----

query III colnames
SELECT * FROM t1 JOIN t2 USING (f1) AS x WHERE t1.f1 = 3;
----
f1  f2  f2
3   4   4

query III colnames
SELECT * FROM t1 JOIN t2 USING (f1) AS x;
----
f1  f2  f2
3   4   4

query II colnames
SELECT * FROM t1 JOIN t2 USING (f1, f2) AS x;
----
f1  f2
3   4

query II colnames
SELECT * FROM (SELECT x.f2 AS f3 FROM t1 JOIN t2 USING (f2) AS x) t5 JOIN t3 USING (f3) AS x;
----
f3  f1
4   3

query error table name "x" specified more than once
SELECT * FROM t1 JOIN t2 USING (f1) AS x JOIN t3 USING (f1) AS x;

query IIII colnames
SELECT * FROM t1 JOIN t2 USING (f1) AS x JOIN t3 USING (f1) AS y;
----
f1  f2  f2  f3
3   4   4   4

query error column "t1.f1" does not exist
SELECT * FROM (t1 JOIN t2 USING (f1)) AS x WHERE t1.f1 = 1;

query error column "x.f5" does not exist
SELECT * FROM t1 JOIN t2 USING (f1) AS x WHERE x.f5 = 'one';

query error column "x.f4" does not exist
SELECT * FROM (t3 JOIN t4 USING (f3) AS x) AS xx WHERE x.f4 = 1;

query error table name "a1" specified more than once
SELECT * FROM t1 a1 JOIN t2 a2 USING (f1) AS a1

query I colnames
SELECT x.* FROM t3 JOIN t4 USING (f3) AS x WHERE t3.f3 = 4;
----
f3
4

query II colnames
SELECT x.* FROM t1 JOIN t2 USING (f1, f2) AS x;
----
f1  f2
3   4

query T colnames
SELECT ROW(x.*) FROM t1 JOIN t2 USING (f1) AS x;
----
row
("(3)")

statement ok
CREATE VIEW v1 AS SELECT x.* FROM t1 JOIN t2 USING (f1) AS x WHERE x.f1 = 3;

query T colnames
SELECT * FROM v1;
----
f1
3

# Ensure the output from `SHOW CREATE VIEW` contains a correctly-formed `AS` part

mode standard

query TT
SHOW CREATE VIEW v1;
----
materialize.public.v1
CREATE VIEW⏎    materialize.public.v1⏎    AS⏎        SELECT x.*⏎        FROM materialize.public.t1 JOIN materialize.public.t2 USING(f1) AS x⏎        WHERE x.f1 = 3;

mode cockroach

statement ok
DROP VIEW v1;


# Left

query TTTTTT
SELECT *,
    f1 IS NULL AS f1_null,
    t1.f1 IS NULL AS t1_f1_null,
    t2.f1 IS NULL AS t2_f1_null
FROM t1
    LEFT JOIN t2 USING (f1)
ORDER BY f1;
----
1  2  NULL  false  false  true
3  4  4  false  false  false


query TTTTTT
SELECT *,
    f1 IS NULL AS f1_null,
    t1.f1 IS NULL AS t1_f1_null,
    t2.f1 IS NULL AS t2_f1_null
FROM t2
    LEFT JOIN t1 USING (f1)
ORDER BY f1;
----
3  4  4  false  false  false
5  6  NULL  false  true  false

query TTTT
SELECT *
FROM t1
    LEFT JOIN t2 USING (f1)
    LEFT JOIN t3 USING (f1)
ORDER BY f1;
----
1  2  NULL  NULL
3  4  4  4

# Right

query TTTTTT
SELECT *,
    f1 IS NULL AS f1_null,
    t1.f1 IS NULL AS t1_f1_null,
    t2.f1 IS NULL AS t2_f1_null
FROM t1
    RIGHT JOIN t2 USING (f1)
ORDER BY f1;
----
3  4  4  false  false  false
5  NULL  6  false  true  false

query TTTTTT
SELECT *,
    f1 IS NULL AS f1_null,
    t1.f1 IS NULL AS t1_f1_null,
    t2.f1 IS NULL AS t2_f1_null
FROM t2
    RIGHT JOIN t1
    USING (f1)
ORDER BY f1;
----
1  NULL  2  false  false  true
3  4  4  false  false  false

query TTTT
SELECT *
FROM t1
    RIGHT JOIN t2 USING (f1)
    RIGHT JOIN t3 USING (f1)
ORDER BY f1;
----
3  4  4  4
7  NULL  NULL  8

query TTTT
SELECT *
FROM t1
    RIGHT JOIN t2 USING (f1)
    LEFT JOIN t3 USING (f1)
ORDER BY f1;
----
3  4  4  4
5  NULL  6  NULL

query TTTT
SELECT *
FROM t1
    LEFT JOIN t2 USING (f1)
    RIGHT JOIN t3 USING (f1)
ORDER BY f1;
----
3  4  4  4
7  NULL  NULL  8

# Inner

query TTTTTT
SELECT *,
    f1 IS NULL AS f1_null,
    t1.f1 IS NULL AS t1_f1_null,
    t2.f1 IS NULL AS t2_f1_null
FROM t1
    INNER JOIN t2
    USING (f1)
ORDER BY f1;
----
3  4  4  false  false  false


query TTTTTT
SELECT *,
    f1 IS NULL AS f1_null,
    t1.f1 IS NULL AS t1_f1_null,
    t2.f1 IS NULL AS t2_f1_null
FROM t2
    INNER JOIN t1
    USING (f1)
ORDER BY f1;
----
3  4  4  false  false  false

query TTTT
SELECT *
FROM t1
    INNER JOIN t2 USING (f1)
    INNER JOIN t3 USING (f1)
ORDER BY f1;
----
3  4  4  4

# Full

query TTTTTT
SELECT *,
    f1 IS NULL AS f1_null,
    t1.f1 IS NULL AS t1_f1_null,
    t2.f1 IS NULL AS t2_f1_null
FROM t1
    FULL OUTER JOIN t2
    USING (f1)
ORDER BY f1;
----
1  2  NULL  false  false  true
3  4  4  false  false  false
5  NULL  6  false  true  false

query TTTTTT
SELECT *,
    f1 IS NULL AS f1_null,
    t1.f1 IS NULL AS t1_f1_null,
    t2.f1 IS NULL AS t2_f1_null
FROM t2
    INNER JOIN t1
    USING (f1)
ORDER BY f1;
----
3  4  4  false  false  false

query TTTT
SELECT *
FROM t1
    FULL OUTER JOIN t2 USING (f1)
    FULL OUTER JOIN t3 USING (f1)
ORDER BY f1;
----
1  2  NULL  NULL
3  4  4  4
5  NULL  6  NULL
7  NULL  NULL  8

# Most recent joined cols are always leftmost in return select

query TTTTT colnames
SELECT *
    FROM t1
    JOIN t2 USING (f1)
    JOIN t3 USING (f1)
    JOIN t4 USING (f3);
----
f3 f1 f2 f2 f4
4  3  4  4  3

# https://github.com/MaterializeInc/materialize/pull/9489#issuecomment-992186563
# Ensure priority does not persist through joins
query error column reference "f2" is ambiguous
SELECT *, f2 IS NULL
     FROM t1 AS t1
     JOIN t1 AS t2 USING (f1, f2)
     JOIN t1 AS t3 USING (f1);

query error common column name "f2" appears more than once in left table
SELECT *
    FROM t1 AS t1
    JOIN t1 AS t2 USING (f1, f2)
    JOIN t1 AS t3 USING (f1)
    JOIN t1 AS t4 USING (f2);

# https://github.com/MaterializeInc/materialize/pull/9489#issuecomment-992195117
# Comma-joins of adjacent tables are equivalent to nested cross joins
query TTTTT
SELECT *
    FROM t2,
    t2 AS x
    JOIN t1
    USING (f2)
ORDER BY 1;
----
3  4  4  3  3
5  6  4  3  3

query TTTTT
SELECT *
    FROM t2
    CROSS JOIN (
    t2 AS x
        JOIN t1
        USING (f2)
    )
ORDER BY 1;
----
3  4  4  3  3
5  6  4  3  3

# https://github.com/MaterializeInc/materialize/pull/9489#issuecomment-992207932
statement ok
DELETE FROM t1;

statement ok
DELETE FROM t2;

statement ok
INSERT INTO t1 VALUES
    (NULL, 0),
    (1, 1),
    (1, 1),
    (2, 2);

statement ok
INSERT INTO t2 VALUES
    (NULL, 0),
    (NULL, 0),
    (1, 1);

query II
SELECT a3.f1,
       a4.f1
  FROM t2 AS a1
  JOIN t1 AS a2 USING (f1), t2 AS a3
  JOIN t1 AS a4 USING (f1);
----
1 1
1 1
1 1
1 1

# Simple USING column missing from the right table.
query error column "a" specified in USING clause does not exist in right table
SELECT * FROM (SELECT 1 a) s1 JOIN (SELECT 2 b) s2 USING (a)

# Simple USING column missing from the left table.
query error column "b" specified in USING clause does not exist in left table
SELECT * FROM (SELECT 1 a) s1 JOIN (SELECT 2 b) s2 USING (b)

# USING column missing from both tables, but existing in the outer scope.
query error column "c" specified in USING clause does not exist in left table
SELECT (SELECT * FROM (SELECT 1 a) s1 JOIN (SELECT 2 b) s2 USING (c)) FROM (SELECT 3 c) s3

# USING column missing from the right table only but existing in the outer scope.
query error column "a" specified in USING clause does not exist in right table
SELECT (SELECT * FROM (SELECT 1 a) s1 JOIN (SELECT 2 b) s2 USING (a)) FROM (SELECT 3 a) s3

query II colnames
SELECT * FROM l NATURAL JOIN l AS a LIMIT 0
----
la  lb

# Regression test for https://github.com/MaterializeInc/database-issues/issues/5220
# This query fails if JoinImplementation is not in the same fixpoint loop with LiteralLifting.

statement ok
CREATE TABLE IF NOT EXISTS a (a boolean, b boolean);

statement ok
CREATE VIEW IF NOT EXISTS b AS SELECT 2 + 2;

statement ok
CREATE TABLE IF NOT EXISTS c (data jsonb);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT (SELECT NULL FROM a) AS c1, ref_0."?column?" AS c4 FROM b AS ref_0
WHERE EXISTS (SELECT FROM c AS ref_7 WHERE EXISTS (SELECT ref_0."?column?" AS c2)) OR ref_0."?column?" IS NOT NULL;
----
Explained Query:
  With
    cte l0 =
      Distinct project=[] // { arity: 0 }
        Project () // { arity: 0 }
          ReadStorage materialize.public.c // { arity: 1 }
    cte l1 =
      Union // { arity: 1 }
        Project (#2) // { arity: 1 }
          Map (null) // { arity: 3 }
            ReadStorage materialize.public.a // { arity: 2 }
        Project (#1) // { arity: 1 }
          FlatMap guard_subquery_size(#0{count}) // { arity: 2 }
            Reduce aggregates=[count(*)] // { arity: 1 }
              Project () // { arity: 0 }
                ReadStorage materialize.public.a // { arity: 2 }
  Return // { arity: 2 }
    Map (4) // { arity: 2 }
      CrossJoin type=differential // { arity: 1 }
        implementation
          %0[×] » %1[×]
        ArrangeBy keys=[[]] // { arity: 0 }
          Union // { arity: 0 }
            Get l0 // { arity: 0 }
            Negate // { arity: 0 }
              Get l0 // { arity: 0 }
            Constant // { arity: 0 }
              - ()
        ArrangeBy keys=[[]] // { arity: 1 }
          Union // { arity: 1 }
            Get l1 // { arity: 1 }
            Map (null) // { arity: 1 }
              Union // { arity: 0 }
                Negate // { arity: 0 }
                  Distinct project=[] // { arity: 0 }
                    Project () // { arity: 0 }
                      Get l1 // { arity: 1 }
                Constant // { arity: 0 }
                  - ()

Source materialize.public.a
Source materialize.public.c

Target cluster: quickstart

EOF

# Verify that the fueling logic of the linear join implementation is correct
# by running a large cross-join that exhausts the avaible fuel (currently
# 1 million output records) at least once.

statement ok
CREATE TABLE fuel_test_1 (a int)

statement ok
CREATE TABLE fuel_test_2 (b int)

statement ok
INSERT INTO fuel_test_1 SELECT generate_series(1, 2000)

statement ok
INSERT INTO fuel_test_2 SELECT generate_series(2001, 4000)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT sum(a + b) FROM fuel_test_1, fuel_test_2
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum((#0{a} + #1{b}))]
        CrossJoin type=differential
          ArrangeBy keys=[[]]
            ReadStorage materialize.public.fuel_test_1
          ArrangeBy keys=[[]]
            ReadStorage materialize.public.fuel_test_2
  Return
    Union
      Get l0
      Map (null)
        Union
          Negate
            Project ()
              Get l0
          Constant
            - ()

Source materialize.public.fuel_test_1
Source materialize.public.fuel_test_2

Target cluster: quickstart

EOF

query I
SELECT sum(a + b) FROM fuel_test_1, fuel_test_2
----
16004000000

# Test the code path for producing errors from joins.
statement ok
CREATE TABLE error_test (a int)

statement ok
INSERT INTO error_test VALUES (0)

query error division by zero
SELECT t1.a / t2.a FROM error_test t1, error_test t2

# Regression test for https://github.com/MaterializeInc/database-issues/issues/5998
query
select
  from
    (select
          ref_4."schema_id" as c0
        from
          mz_catalog.mz_default_privileges as ref_4
        where current_schemas(
            CAST((select max(atthasdef) from pg_attribute)
               as bool)) = mz_internal.mz_normalize_object_name(
            CAST(ref_4."schema_id" as text))
               ) as subq_7
  where (select "id" from mz_internal.mz_comments limit 1)
       = subq_7."c0";
----