Materialize/test/sqllogictest/outer_join.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

statement ok
CREATE TABLE a(a INTEGER);

statement ok
CREATE TABLE b(b INTEGER);

statement ok
CREATE TABLE c(c INTEGER);

statement ok
INSERT INTO a VALUES (1);

statement ok
INSERT INTO b VALUES (2);

statement ok
INSERT INTO c VALUES (3);

query III rowsort
SELECT * FROM a, b full join c on b = c;
----
1  NULL  3
1  2  NULL

query III
SELECT * FROM a, b right join c on b = c;
----
1  NULL  3

query III
SELECT * FROM a, b left join c on b = c;
----
1  2  NULL

query III
SELECT * FROM a CROSS JOIN b JOIN LATERAL(SELECT a.a FROM c) x ON TRUE;
----
1 2 1

query III
SELECT * FROM a, b FULL JOIN LATERAL(SELECT a.a FROM c) x ON TRUE;
----
1 2 1

query III
SELECT * FROM a CROSS JOIN (b FULL JOIN LATERAL(SELECT a.a FROM c) x ON TRUE);
----
1 2 1

statement ok
CREATE TABLE t1 (a int, b int);

statement ok
CREATE TABLE t2 (a int, c int);

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

statement ok
INSERT INTO t2 VALUES (2, 4), (5, 7);

query IIII rowsort
SELECT * FROM generate_series(1, 2), LATERAL (SELECT * FROM t1) _ NATURAL RIGHT JOIN t2;
----
1 2 3    4
2 2 3    4
1 5 NULL 7
2 5 NULL 7

statement ok
create table left(x int, y int);

statement ok
create table right1(x int, y int);

statement ok
create view right1_keyed(x, y) as select distinct on(x) * from right1;

statement ok
create table right2(x int, y int);

statement ok
insert into left values (0,0);

statement ok
insert into right2 values (0,0);

# `consolidate_output` should be true when there is a negated input to a Union.
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
select *
from
  left
  LEFT JOIN right1_keyed ON left.x = right1_keyed.x
  LEFT JOIN right2 ON left.x = right2.x;
----
Explained Query:
  With
    cte l0 =
      TopK::MonotonicTop1 group_by=[#0] must_consolidate
        Get::Collection materialize.public.right1
          raw=true
    cte l1 =
      Reduce::Distinct
        val_plan
          project=()
        key_plan=id
        Union
          Get::Collection materialize.public.left
            project=(#0)
            raw=true
          Constant
            - (null)
  Return
    Join::Delta
      plan_path[0]
        delta_stage[1]
          closure
            project=(#0..=#3, #7, #8)
            map=((#5) IS NULL, case when #6 then null else #0 end, case when #6 then null else #4 end)
          lookup={ relation=2, key=[#0] }
          stream={ key=[#0], thinning=(#1..=#3) }
        delta_stage[0]
          closure
            project=(#0, #1, #5, #6)
            map=((#3) IS NULL, case when #4 then null else #0 end, case when #4 then null else #2 end)
          lookup={ relation=1, key=[#0] }
          stream={ key=[#0], thinning=(#1) }
        source={ relation=0, key=[#0] }
      plan_path[1]
        delta_stage[1]
          closure
            project=(#1, #2, #4, #3, #8, #9)
            map=((#6) IS NULL, case when #7 then null else #1 end, case when #7 then null else #5 end)
          lookup={ relation=2, key=[#0] }
          stream={ key=[#2], thinning=(#0, #1, #3, #4) }
        delta_stage[0]
          closure
            project=(#0, #3, #0, #2, #4)
            map=(case when #1 then null else #0 end)
          lookup={ relation=0, key=[#0] }
          stream={ key=[#0], thinning=(#1, #2) }
        initial_closure
          project=(#0, #3, #4)
          map=((#2) IS NULL, case when #3 then null else #1 end)
        source={ relation=1, key=[#0] }
      plan_path[2]
        delta_stage[1]
          closure
            project=(#1, #2, #8, #9, #4, #3)
            map=((#6) IS NULL, case when #7 then null else #1 end, case when #7 then null else #5 end)
          lookup={ relation=1, key=[#0] }
          stream={ key=[#2], thinning=(#0, #1, #3, #4) }
        delta_stage[0]
          closure
            project=(#0, #3, #0, #2, #4)
            map=(case when #1 then null else #0 end)
          lookup={ relation=0, key=[#0] }
          stream={ key=[#0], thinning=(#1, #2) }
        initial_closure
          project=(#0, #3, #4)
          map=((#2) IS NULL, case when #3 then null else #1 end)
        source={ relation=2, key=[#0] }
      ArrangeBy
        raw=true
        arrangements[0]={ key=[#0], permutation=id, thinning=(#1) }
        types=[integer?, integer?]
        Get::PassArrangements materialize.public.left
          raw=true
      ArrangeBy
        raw=true
        arrangements[0]={ key=[#0], permutation=id, thinning=(#1, #2) }
        types=[integer?, integer?, boolean?]
        Union
          Get::Collection l0
            project=(#0..=#2)
            map=(true)
            raw=true
          Mfp
            project=(#0..=#2)
            map=(null, null)
            input_key=#0
            Threshold::Basic ensure_arrangement={ key=[#0], permutation=id, thinning=() }
              ArrangeBy
                raw=false
                arrangements[0]={ key=[#0], permutation=id, thinning=() }
                types=[integer?]
                Union consolidate_output=true
                  Negate
                    Get::Collection l0
                      project=(#0)
                      raw=true
                  ArrangeBy
                    input_key=[#0]
                    raw=true
                    Get::PassArrangements l1
                      raw=false
                      arrangements[0]={ key=[#0], permutation=id, thinning=() }
      ArrangeBy
        raw=true
        arrangements[0]={ key=[#0], permutation=id, thinning=(#1, #2) }
        types=[integer?, integer?, boolean?]
        Union
          Get::Collection materialize.public.right2
            project=(#0..=#2)
            map=(true)
            raw=true
          Mfp
            project=(#0..=#2)
            map=(null, null)
            input_key=#0
            Threshold::Basic ensure_arrangement={ key=[#0], permutation=id, thinning=() }
              ArrangeBy
                raw=false
                arrangements[0]={ key=[#0], permutation=id, thinning=() }
                types=[integer?]
                Union consolidate_output=true
                  Negate
                    Get::Collection materialize.public.right2
                      project=(#0)
                      raw=true
                  ArrangeBy
                    input_key=[#0]
                    raw=true
                    Get::PassArrangements l1
                      raw=false
                      arrangements[0]={ key=[#0], permutation=id, thinning=() }

Source materialize.public.left
Source materialize.public.right1
  filter=((#0) IS NOT NULL)
Source materialize.public.right2
  filter=((#0) IS NOT NULL)

Target cluster: quickstart

EOF

query IIIIII
select *
from
  left
  LEFT JOIN right1_keyed ON left.x = right1_keyed.x
  LEFT JOIN right2 ON left.x = right2.x;
----
0  0  NULL  NULL  0  0