# 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