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

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 <empty>

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 <empty>

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 <empty>

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 <empty>

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