# 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 foo_raw (a int4, b int8, u text) statement ok create materialized view foo as select * from foo_raw where a is not null and b is not null statement ok create table bar_raw (a int4, v text) statement ok create materialized view bar as select distinct on (a) a, v from bar_raw statement ok create materialized view ban_nn as select * from bar where a is not null statement ok create table baz_raw (b int8, c int2, w text) statement ok create materialized view baz as select distinct on (b) b, c, w from baz_raw where b is not null statement ok create table quux_raw (c int2, x text) statement ok create materialized view quux as select distinct on (c) c, x from quux_raw where c is not null # Demonstrate core semijoin idempotence simplification: semijoin removal. # The resulting plan should have 1 join with 2 inputs and no distinct operators. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR select * from (with keys as ( select distinct foo.a from foo, bar where foo.a = bar.a ) select * from foo, keys where foo.a = keys.a) ---- Explained Query: Project (#0{a}..=#2{u}, #0{a}) Join on=(#0{a} = #3{a}) type=differential ArrangeBy keys=[[#0{a}]] ReadStorage materialize.public.foo ArrangeBy keys=[[#0{a}]] Project (#0{a}) Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.bar Source materialize.public.foo Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Target cluster: quickstart EOF # Ensure LEFT JOIN is planned with only one join. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR select * from foo_raw left join bar on foo_raw.a = bar.a; ---- Explained Query: With cte l0 = Project (#0{a}..=#2{u}, #4{v}) Join on=(#0{a} = #3{a}) type=differential ArrangeBy keys=[[#0{a}]] Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.foo_raw ArrangeBy keys=[[#0{a}]] Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.bar Return Union Map (null, null) Union Negate Project (#0{a}..=#2{u}) Get l0 ReadStorage materialize.public.foo_raw Project (#0{a}..=#2{u}, #0{a}, #3{v}) Get l0 Source materialize.public.foo_raw Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Target cluster: quickstart EOF # RIGHT JOIN should be planned with only one join. # At the moment, we do not see the join symmetry. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR select * from bar right join foo_raw on foo_raw.a = bar.a; ---- Explained Query: With cte l0 = ArrangeBy keys=[[#0{a}]] Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.foo_raw cte l1 = Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.bar Return Union Project (#3, #4, #0{a}..=#2{u}) Map (null, null) Union Negate Project (#0{a}..=#2{u}) Join on=(#0{a} = #3{a}) type=differential Get l0 ArrangeBy keys=[[#0{a}]] Project (#0{a}) Get l1 ReadStorage materialize.public.foo_raw Project (#0{a}, #1{v}, #0{a}, #3{b}, #4{u}) Join on=(#0{a} = #2{a}) type=differential ArrangeBy keys=[[#0{a}]] Get l1 Get l0 Source materialize.public.foo_raw Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Target cluster: quickstart EOF # Ensure that multiple left joins still produce one join operator each. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR select * from foo left join bar on foo.a = bar.a left join baz on foo.b = baz.b left join quux on baz.c = quux.c; ---- Explained Query: Project (#0{a}..=#2{u}, #14, #15, #17..=#19, #21, #22) Map ((#5) IS NULL, case when #13 then null else #0{a} end, case when #13 then null else #4{v} end, (#9) IS NULL, case when #16 then null else #1{b} end, case when #16 then null else #7{c} end, case when #16 then null else #8{w} end, (#12) IS NULL, case when #20 then null else #10{c} end, case when #20 then null else #11{x} end) Join on=(#0{a} = #3{a} AND #1{b} = #6{b} AND #10{c} = case when (#9) IS NULL then null else #7{c} end) type=delta ArrangeBy keys=[[#0{a}], [#1{b}]] ReadStorage materialize.public.foo ArrangeBy keys=[[#0{a}]] Union Filter (#0{a}) IS NOT NULL Map (true) ReadStorage materialize.public.bar Map (null, null) Threshold Union Negate Project (#0{a}) Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.bar Distinct project=[#0{a}] Project (#0{a}) ReadStorage materialize.public.foo ArrangeBy keys=[[#0{b}], [case when (#3) IS NULL then null else #1{c} end]] Union Map (true) ReadStorage materialize.public.baz Map (null, null, null) Threshold Union Negate Project (#0{b}) ReadStorage materialize.public.baz Distinct project=[#0{b}] Project (#1{b}) ReadStorage materialize.public.foo ArrangeBy keys=[[#0{c}]] Union Map (true) ReadStorage materialize.public.quux Map (null, null) Threshold Union Negate Project (#0{c}) ReadStorage materialize.public.quux Distinct project=[#0{c}] Union Project (#1{c}) ReadStorage materialize.public.baz Constant - (null) Source materialize.public.foo Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Source materialize.public.baz Source materialize.public.quux Target cluster: quickstart EOF # Record how we do not yet fully optimize projections in left join stacks. # This plan appears to require further projection pushdown to cancel the last join. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR select count(*) from foo left join bar on foo.a = bar.a left join baz on foo.b = baz.b left join quux on baz.c = quux.c; ---- Explained Query: With cte l0 = Project (#0{a}) Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.bar cte l1 = Project (#0{c}) ReadStorage materialize.public.quux cte l2 = Reduce aggregates=[count(*)] Project () Join on=(#0{a} = #2{a} AND #1{b} = #3{b} AND #6{c} = case when (#5) IS NULL then null else #4{c} end) type=delta ArrangeBy keys=[[#0{a}], [#1{b}]] Project (#0{a}, #1{b}) ReadStorage materialize.public.foo ArrangeBy keys=[[#0{a}]] Union Get l0 Threshold Union Negate Get l0 Distinct project=[#0{a}] Project (#0{a}) ReadStorage materialize.public.foo ArrangeBy keys=[[#0{b}], [case when (#2) IS NULL then null else #1{c} end]] Union Project (#0{b}, #1{c}, #3) Map (true) ReadStorage materialize.public.baz Map (null, null) Threshold Union Negate Project (#0{b}) ReadStorage materialize.public.baz Distinct project=[#0{b}] Project (#1{b}) ReadStorage materialize.public.foo ArrangeBy keys=[[#0{c}]] Union Get l1 Threshold Union Negate Get l1 Distinct project=[#0{c}] Union Project (#1{c}) ReadStorage materialize.public.baz Constant - (null) Return Union Get l2 Map (0) Union Negate Project () Get l2 Constant - () Source materialize.public.foo Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Source materialize.public.baz Source materialize.public.quux Target cluster: quickstart EOF # Record how we can push filters through left joins to their source. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR select * from foo left join baz on foo.b = baz.b left join quux on baz.c = quux.c where foo.a = 7; ---- Explained Query: Project (#0{a}..=#2{u}, #11..=#13, #15, #16) Map ((#6) IS NULL, case when #10 then null else #1{b} end, case when #10 then null else #4{c} end, case when #10 then null else #5{w} end, (#9) IS NULL, case when #14 then null else #7{c} end, case when #14 then null else #8{x} end) Join on=(#1{b} = #3{b} AND #7{c} = case when (#6) IS NULL then null else #4{c} end) type=delta ArrangeBy keys=[[#1{b}]] Filter (#0{a} = 7) ReadStorage materialize.public.foo ArrangeBy keys=[[#0{b}], [case when (#3) IS NULL then null else #1{c} end]] Union Map (true) ReadStorage materialize.public.baz Map (null, null, null) Threshold Union Negate Project (#0{b}) ReadStorage materialize.public.baz Distinct project=[#0{b}] Project (#1{b}) ReadStorage materialize.public.foo ArrangeBy keys=[[#0{c}]] Union Map (true) ReadStorage materialize.public.quux Map (null, null) Threshold Union Negate Project (#0{c}) ReadStorage materialize.public.quux Distinct project=[#0{c}] Union Project (#1{c}) ReadStorage materialize.public.baz Constant - (null) Source materialize.public.foo Source materialize.public.baz Source materialize.public.quux Target cluster: quickstart EOF ## -------------------- Tests for WITH MUTUALLY RECURSIVE -------------------- # Trivial test: Just marking SemijoinIdempotence recursion_safe should already handle this, because the semijoin pattern # in this test doesn't reference any ids that are defined inside the WMR. Therefore, SemijoinIdempotence doesn't need to # collect info about ids inside a LetRec. # The resulting plan should have 1 join with 2 inputs. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR with mutually recursive c0(a int4, b int8, u text, key int4) as ( select * from ( with keys as ( select distinct foo.a from foo, bar where foo.a = bar.a ) select * from foo, keys where foo.a = keys.a ) union select * from c0 ) select * from c0; ---- Explained Query: With Mutually Recursive cte l0 = Distinct project=[#0{a}..=#3{a}] Union Project (#0{a}..=#2{u}, #0{a}) Join on=(#0{a} = #3{a}) type=differential ArrangeBy keys=[[#0{a}]] ReadStorage materialize.public.foo ArrangeBy keys=[[#0{a}]] Project (#0{a}) Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.bar Get l0 Return Get l0 Source materialize.public.foo Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Target cluster: quickstart EOF # Manually written idempotent semijoin. # The resulting plan should have 1 join with 2 inputs. # Here, SemijoinIdempotence relies on the keys introduced by the `select distinct on` being propagated through the # recursive Get. Note that in this test even if SemijoinIdempotence wouldn't work, RedundantJoin would step in. (But # see later a similar situation, but with a LEFT JOIN, where RedundantJoin wouldn't be able to eliminate a join.) # With materialize#27389 this stopped testing a thing; see issue database-issues#8294. query T multiline EXPLAIN OPTIMIZED PLAN WITH (keys) AS VERBOSE TEXT FOR with mutually recursive c0(a int4, b int8, u text, key int4) as ( select distinct on (1) * from ( select * from ( with keys as ( select distinct foo.a from foo, (select a, u from c0) as likebar where foo.a = likebar.a ) select * from foo, keys where foo.a = keys.a ) union select * from c0 ) ) select * from c0; ---- Explained Query (fast path): Constant Target cluster: quickstart EOF # Manually written idempotent semijoin. # Similar to the previous test, but the recursive Get is at the other input. This means that the input that should have # a known key is the static one, for which key inference works fine. # The resulting plan should have 1 join with 2 inputs. # With materialize#27389 this stopped testing a thing; see issue database-issues#8294. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR with mutually recursive c0(a int4, b int8, u text, key int4) as ( select * from ( with keys as ( select distinct c0.a from c0, bar where c0.a = bar.a ) select c0.a, c0.b, c0.u, c0.key from c0, keys where c0.a = keys.a ) union select * from c0 ) select * from c0; ---- Explained Query (fast path): Constant Target cluster: quickstart EOF # Manually written idempotent semijoin. # Similar to the previous test, but the CTE from inside c0 is manually lifted to the enclosing LetRec. # The resulting plan should have 1 join with 2 inputs. # With materialize#27389 this stopped testing a thing; see issue database-issues#8294. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR with mutually recursive keys(a int4) as ( select distinct c0.a from c0, bar where c0.a = bar.a ), c0(a int4, b int8, u text, key int4) as ( select c0.a, c0.b, c0.u, c0.key from c0, keys where c0.a = keys.a union select * from c0 ) select * from c0; ---- Explained Query (fast path): Constant Target cluster: quickstart EOF # Manually written idempotent semijoin. # Similar to the previous test, but instead of using bar directly, we add an extra cte (bar2), which will recursively # refer to `c0`. `keys` will refer to `bar2` instead of `c0`. # The resulting plan should have 1 join with 2 inputs. # With materialize#27389 this stopped testing a thing; see issue database-issues#8294. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR with mutually recursive bar2(a int4, v text) as ( select * from bar union select a, u from c0 ), keys(a int4) as ( select distinct c0.a from c0, (select distinct on(a) * from bar2) as bar3 where c0.a = bar3.a ), c0(a int4, b int8, u text, key int4) as ( select c0.a, c0.b, c0.u, c0.key from c0, keys where c0.a = keys.a union select * from c0 ) select * from c0; ---- Explained Query (fast path): Constant Target cluster: quickstart EOF # Similar to the previous test, but SemijoinIdempotence should NOT kick in here! (That is, 2 joins should be in the # plan.) This is because we reordered the bindings compared to the previous test, and now the `bar2` reference in `keys` # changes meaning when `bar2` is being assigned after `key`. To correctly handle this situation, we need the expirations # in SemijoinIdempotence. To demonstrate this, if we comment out the two `do_expirations` lines, SemijoinIdempotence # incorrectly transforms this plan. # With materialize#27389 this stopped testing a thing; see issue database-issues#8294. query T multiline EXPLAIN OPTIMIZED PLAN WITH (keys) AS VERBOSE TEXT FOR with mutually recursive keys(a int4) as ( select distinct c0.a from c0, (select distinct on(a) * from bar2) as bar3 where c0.a = bar3.a ), bar2(a int4, v text) as ( select * from bar union select a, u from c0 ), c0(a int4, b int8, u text, key int4) as ( select c0.a, c0.b, c0.u, c0.key from c0, keys where c0.a = keys.a union select * from c0 ) select * from c0; ---- Explained Query (fast path): Constant Target cluster: quickstart EOF # Manually written idempotent semijoin. # Another negative test. Similar to the first test with `bar2`, but both `keys` and `bar2` are being referenced from the # body, which means that SemijoinIdempotence can't kick in, as it would eliminate the intermediate Join's result, which # is now being referenced from the body. # With materialize#27389 this stopped testing a thing; see issue database-issues#8294. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR with mutually recursive bar2(a int4, v text) as ( select * from bar union select a, u from c0 ), keys(a int4) as ( select distinct c0.a from c0, (select distinct on(a) * from bar2) as bar3 where c0.a = bar3.a ), c0(a int4, b int8, u text, key int4) as ( select c0.a, c0.b, c0.u, c0.key from c0, keys where c0.a = keys.a union select * from c0 ) select * from c0 union all select a, -1, v, a from bar2 union all select a, -2, 'keys', a from keys; ---- Explained Query: Project (#0{a}, #2, #1{v}, #0{a}) Map (-1) ReadStorage materialize.public.bar Source materialize.public.bar Target cluster: quickstart EOF # Test that LEFT JOIN inside WMR is planned with only one join. # Here, SemijoinIdempotence relies on the keys introduced by the `select distinct on` being propagated through the # recursive Get. query T multiline EXPLAIN OPTIMIZED PLAN WITH(keys, humanized expressions) AS VERBOSE TEXT FOR with mutually recursive c0(a int4, b int8, u text, a2 int4, v2 text) as ( select distinct on (1) * from ( select foo_raw.a, foo_raw.b, foo_raw.u, c0.a, c0.u from foo_raw left join c0 on foo_raw.a = c0.a union select *, a, u from foo_raw ) ) select * from c0 ---- Explained Query: With Mutually Recursive cte l0 = Project (#0{a}..=#2{u}, #4) // { keys: "()" } Join on=(#0{a} = #3) type=differential // { keys: "()" } ArrangeBy keys=[[#0{a}]] // { keys: "()" } Filter (#0{a}) IS NOT NULL // { keys: "()" } ReadStorage materialize.public.foo_raw // { keys: "()" } ArrangeBy keys=[[#0]] // { keys: "([0])" } Project (#0, #2) // { keys: "([0])" } Filter (#0{a}) IS NOT NULL // { keys: "([0])" } Get l1 // { keys: "([0])" } cte l1 = TopK group_by=[#0{a}] limit=1 // { keys: "([0])" } Distinct project=[#0{a}..=#4{u}] // { keys: "([0, 1, 2, 3, 4])" } Union // { keys: "()" } Map (null, null) // { keys: "()" } Union // { keys: "()" } Negate // { keys: "()" } Project (#0{a}..=#2{u}) // { keys: "()" } Get l0 // { keys: "()" } ReadStorage materialize.public.foo_raw // { keys: "()" } Project (#0{a}..=#2{u}, #0{a}, #3) // { keys: "()" } Get l0 // { keys: "()" } Project (#0{a}..=#2{u}, #0{a}, #2{u}) // { keys: "()" } ReadStorage materialize.public.foo_raw // { keys: "()" } Return // { keys: "([0])" } Get l1 // { keys: "([0])" } Source materialize.public.foo_raw Target cluster: quickstart EOF # Test that LEFT JOIN inside WMR is planned with only one join. # Similar to the previous test, but the recursive Get is at the other input. This means that the input that should have # a known key is the static one, for which we don't need to go propagate a key through a recursive Get. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR with mutually recursive c0(a int4, b int8, u text, a_bar int4, v_bar text) as ( ( select c0.a, c0.b, c0.u, bar.a, bar.v from c0 left join bar on c0.a = bar.a ) union select *, a, u from foo_raw ) select * from c0 ---- Explained Query: With Mutually Recursive cte l0 = Project (#0..=#2, #4{v}) Join on=(#0{a} = #3{a}) type=differential ArrangeBy keys=[[#0{a}]] Project (#0..=#2) Filter (#0{a}) IS NOT NULL Get l1 ArrangeBy keys=[[#0{a}]] Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.bar cte l1 = Distinct project=[#0{a}..=#4{v}] Union Map (null, null) Union Negate Project (#0..=#2) Get l0 Project (#0{a}..=#2{u}) Get l1 Project (#0..=#2, #0, #3{v}) Get l0 Project (#0{a}..=#2{u}, #0{a}, #2{u}) ReadStorage materialize.public.foo_raw Return Get l1 Source materialize.public.foo_raw Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Target cluster: quickstart EOF # Ensure LEFT JOIN inside WMR is planned with only one join. # A variation of the previous one. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR with mutually recursive c0(a int4, b int8, u text, a_bar int4, v_bar text) as ( ( select * from foo_raw left join bar on foo_raw.a = bar.a ) union select * from c0 ) select * from c0 ---- Explained Query: With cte l0 = Project (#0{a}..=#2{u}, #4{v}) Join on=(#0{a} = #3{a}) type=differential ArrangeBy keys=[[#0{a}]] Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.foo_raw ArrangeBy keys=[[#0{a}]] Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.bar Return With Mutually Recursive cte l1 = Distinct project=[#0{a}..=#4{v}] Union Map (null, null) Union Negate Project (#0{a}..=#2{u}) Get l0 ReadStorage materialize.public.foo_raw Project (#0{a}..=#2{u}, #0{a}, #3{v}) Get l0 Get l1 Return Get l1 Source materialize.public.foo_raw Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Target cluster: quickstart EOF # Ensure that multiple left joins inside a WMR still produce one join operator each. query T multiline EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR with mutually recursive c0(a int4, b int8, u text) as ( ( select c0.a + bar.a, c0.b + baz.b, c0.u || cast(baz.c + quux.c as text) from c0 left join bar on c0.a = bar.a left join baz on c0.b = baz.b left join quux on baz.c = quux.c ) union select * from foo ) select * from c0; ---- Explained Query: With Mutually Recursive cte l0 = Union Project (#0{a}, #1{b}) Get l1 Constant - (null, null) cte l1 = Distinct project=[#0{a}..=#2{u}] Union Project (#11..=#13) Map ((#7) IS NULL, (#0{a} + case when (#4) IS NULL then null else #0 end), (#1{b} + case when #10 then null else #1 end), (#2{u} || smallint_to_text((case when #10 then null else #6{c} end + case when (#9) IS NULL then null else #8{c} end)))) Join on=(#0 = #3{a} AND #1 = #5{b} AND #8{c} = case when (#7) IS NULL then null else #6{c} end) type=delta ArrangeBy keys=[[#0{a}], [#1{b}]] Get l1 ArrangeBy keys=[[#0{a}]] Union Project (#0{a}, #2) Filter (#0{a}) IS NOT NULL Map (true) ReadStorage materialize.public.bar Map (null) Threshold Union Negate Project (#0{a}) Filter (#0{a}) IS NOT NULL ReadStorage materialize.public.bar Distinct project=[#0] Project (#0) Get l0 ArrangeBy keys=[[#0{b}], [case when (#2) IS NULL then null else #1{c} end]] Union Project (#0{b}, #1{c}, #3) Map (true) ReadStorage materialize.public.baz Map (null, null) Threshold Union Negate Project (#0{b}) ReadStorage materialize.public.baz Distinct project=[#0] Project (#1) Get l0 ArrangeBy keys=[[#0{c}]] Union Project (#0{c}, #2) Map (true) ReadStorage materialize.public.quux Map (null) Threshold Union Negate Project (#0{c}) ReadStorage materialize.public.quux Distinct project=[#0{c}] Union Project (#1{c}) ReadStorage materialize.public.baz Constant - (null) ReadStorage materialize.public.foo Return Get l1 Source materialize.public.foo Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Source materialize.public.baz Source materialize.public.quux Target cluster: quickstart EOF # Ensure that when the info coming from the right side of a left join is not used, then the join is optimized out. # The plan currently has one join, but maybe that could be optimized out too: the join with baz is currently present, # because baz.c is used in the condition of the join with quux. However, the join with quux is optimized out. query T multiline EXPLAIN OPTIMIZED PLAN WITH(arity, humanized expressions) AS VERBOSE TEXT FOR with mutually recursive c0(a int4, b int8, u text) as ( ( select c0.a, c0.b, c0.u from c0 left join bar on c0.a = bar.a left join baz on c0.b = baz.b left join quux on baz.c = quux.c ) union select * from foo ) select * from c0; ---- Explained Query: With cte l0 = Project (#0{a}) // { arity: 1 } Filter (#0{a}) IS NOT NULL // { arity: 2 } ReadStorage materialize.public.bar // { arity: 2 } cte l1 = Project (#0{c}) // { arity: 1 } ReadStorage materialize.public.quux // { arity: 2 } Return // { arity: 3 } With Mutually Recursive cte l2 = Distinct project=[#0{a}..=#2{u}] // { arity: 3 } Union // { arity: 3 } Project (#0..=#2) // { arity: 3 } Join on=(#0 = #3{a} AND #1 = #4{b} AND #7{c} = case when (#6) IS NULL then null else #5{c} end) type=delta // { arity: 8 } ArrangeBy keys=[[#0{a}], [#1{b}]] // { arity: 3 } Get l2 // { arity: 3 } ArrangeBy keys=[[#0{a}]] // { arity: 1 } Union // { arity: 1 } Get l0 // { arity: 1 } Threshold // { arity: 1 } Union // { arity: 1 } Negate // { arity: 1 } Get l0 // { arity: 1 } Distinct project=[#0] // { arity: 1 } Project (#0{a}) // { arity: 1 } Get l2 // { arity: 3 } ArrangeBy keys=[[#0{b}], [case when (#2) IS NULL then null else #1{c} end]] // { arity: 3 } Union // { arity: 3 } Project (#0{b}, #1{c}, #3) // { arity: 3 } Map (true) // { arity: 4 } ReadStorage materialize.public.baz // { arity: 3 } Map (null, null) // { arity: 3 } Threshold // { arity: 1 } Union // { arity: 1 } Negate // { arity: 1 } Project (#0{b}) // { arity: 1 } ReadStorage materialize.public.baz // { arity: 3 } Distinct project=[#0] // { arity: 1 } Project (#1{b}) // { arity: 1 } Get l2 // { arity: 3 } ArrangeBy keys=[[#0{c}]] // { arity: 1 } Union // { arity: 1 } Get l1 // { arity: 1 } Threshold // { arity: 1 } Union // { arity: 1 } Negate // { arity: 1 } Get l1 // { arity: 1 } Distinct project=[#0{c}] // { arity: 1 } Union // { arity: 1 } Project (#1{c}) // { arity: 1 } ReadStorage materialize.public.baz // { arity: 3 } Constant // { arity: 1 } - (null) ReadStorage materialize.public.foo // { arity: 3 } Return // { arity: 3 } Get l2 // { arity: 3 } Source materialize.public.foo Source materialize.public.bar filter=((#0{a}) IS NOT NULL) Source materialize.public.baz Source materialize.public.quux Target cluster: quickstart EOF