Materialize/test/sqllogictest/outer_join_lowering.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_new_outer_join_lowering TO true;
----
COMPLETE 0

statement ok
DROP SCHEMA IF EXISTS left_joins_raw CASCADE;

statement ok
DROP SCHEMA IF EXISTS left_joins CASCADE;

statement ok
CREATE SCHEMA left_joins_raw;

statement ok
CREATE SCHEMA left_joins;

statement ok
CREATE TABLE left_joins_raw.outer(
    x int not null,
    y int not null
);

statement ok
CREATE MATERIALIZED VIEW left_joins.outer AS
SELECT DISTINCT ON(x, y) * FROM left_joins_raw.outer;

statement ok
CREATE TABLE left_joins_raw.facts(
    facts_k01 int not null,
    dim01_k01 int,
    dim02_k01 int,
    dim03_k01 int,
    facts_d01 int,
    facts_d02 int,
    facts_d03 int,
    facts_d04 int,
    facts_d05 int
);

statement ok
CREATE MATERIALIZED VIEW left_joins.facts AS
SELECT DISTINCT ON(facts_k01) * FROM left_joins_raw.facts;

statement ok
CREATE TABLE left_joins_raw.dim01(
    dim01_k01 int,
    dim01_d01 int,
    dim01_d02 int,
    dim01_d03 int,
    dim01_d04 int,
    dim01_d05 int
);

statement ok
CREATE MATERIALIZED VIEW left_joins.dim01 AS
SELECT DISTINCT ON(dim01_k01) * FROM left_joins_raw.dim01;

statement ok
CREATE TABLE left_joins_raw.dim02(
    dim02_k01 int,
    dim02_d01 int,
    dim02_d02 int,
    dim02_d03 int,
    dim02_d04 int,
    dim02_d05 int
);

statement ok
CREATE MATERIALIZED VIEW left_joins.dim02 AS
SELECT DISTINCT ON(dim02_k01) * FROM left_joins_raw.dim02;

# A fallback lowering strategy for outer joins with theta-join ON predicates.
#
# The "outer" part is characterized by:
# 1. A `Union`/`Negate` of two `Distinct` with fat `group_by` keys.
# 2. An equi-join with the preserving side with a fat `on` predicate.
#
# Simplification opportunities:
# - The `Distinct` nodes from (1) can be removed by `ReduceElision`.
# - The `Join` from (2) can be removed by `RedundantJoin`.
#
# Applying the optimization above depends on unique key information.
#
# Projection pushdown is blocked unless the `Join` is removed.
query T multiline
EXPLAIN DECORRELATED PLAN WITH(humanized expressions, arity) FOR
SELECT
  facts.facts_k01,
  facts.facts_d01,
  facts.dim01_k01,
  dim01.dim01_d01
FROM
  left_joins_raw.facts LEFT JOIN
  left_joins_raw.dim01 ON(facts.dim01_k01 > dim01.dim01_k01);
----
With
  cte l0 =
    CrossJoin // { arity: 9 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins_raw.facts // { arity: 9 }
  cte l1 =
    Filter (#1{dim01_k01} > #9{dim01_k01}) // { arity: 15 }
      Project (#0{facts_k01}..=#14{dim01_d05}) // { arity: 15 }
        CrossJoin // { arity: 15 }
          Get l0 // { arity: 9 }
          CrossJoin // { arity: 6 }
            Constant // { arity: 0 }
              - ()
            Get materialize.left_joins_raw.dim01 // { arity: 6 }
Return // { arity: 4 }
  Project (#0{facts_k01}, #4{facts_d01}, #1{dim01_k01}, #10{dim01_d01}) // { arity: 4 }
    Union // { arity: 15 }
      Get l1 // { arity: 15 }
      CrossJoin // { arity: 15 }
        Project (#0{facts_k01}..=#8{facts_d05}) // { arity: 9 }
          Join on=(#0{facts_k01} = #9{facts_k01} AND #1{dim01_k01} = #10{dim01_k01} AND #2{dim02_k01} = #11{dim02_k01} AND #3{dim03_k01} = #12{dim03_k01} AND #4{facts_d01} = #13{facts_d01} AND #5{facts_d02} = #14{facts_d02} AND #6{facts_d03} = #15{facts_d03} AND #7{facts_d04} = #16{facts_d04} AND #8{facts_d05} = #17{facts_d05}) // { arity: 18 }
            Union // { arity: 9 }
              Negate // { arity: 9 }
                Distinct project=[#0{facts_k01}..=#8{facts_d05}] // { arity: 9 }
                  Get l1 // { arity: 15 }
              Distinct project=[#0{facts_k01}..=#8{facts_d05}] // { arity: 9 }
                Get l0 // { arity: 9 }
            Get l0 // { arity: 9 }
        Constant // { arity: 6 }
          - (null, null, null, null, null, null)

Target cluster: quickstart

EOF

# An optimized lowering strategy for outer joins with equijoin ON predicates.
#
# The "outer" part is characterized by:
# 1. A semijoin between the inner result (l1) and the preserving side (l0).
# 2. An antijoin of l0 w.r.t. this semijoin.
#
# Simplification opportunities:
# - The semijoin from (1) can be removed by `SemijoinIdempotence`.
#
# Applying the optimization above depends on unique key information.
#
# Projection pushdown is always possible.
query T multiline
EXPLAIN DECORRELATED PLAN WITH(humanized expressions, arity) FOR
SELECT
  facts.facts_k01,
  facts.facts_d01,
  facts.dim01_k01,
  dim01.dim01_d01
FROM
  left_joins_raw.facts LEFT JOIN
  left_joins_raw.dim01 ON(facts.dim01_k01 = dim01.dim01_k01);
----
With
  cte l0 =
    CrossJoin // { arity: 9 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins_raw.facts // { arity: 9 }
  cte l1 =
    Filter (#1{dim01_k01} = #9{dim01_k01}) // { arity: 15 }
      Project (#0{facts_k01}..=#14{dim01_d05}) // { arity: 15 }
        CrossJoin // { arity: 15 }
          Get l0 // { arity: 9 }
          CrossJoin // { arity: 6 }
            Constant // { arity: 0 }
              - ()
            Get materialize.left_joins_raw.dim01 // { arity: 6 }
Return // { arity: 4 }
  Project (#0{facts_k01}, #4{facts_d01}, #1{dim01_k01}, #10{dim01_d01}) // { arity: 4 }
    Union // { arity: 15 }
      Map (null, null, null, null, null, null) // { arity: 15 }
        Union // { arity: 9 }
          Negate // { arity: 9 }
            Project (#0{facts_k01}..=#8{facts_d05}) // { arity: 9 }
              Join on=(#1{dim01_k01} = #9{dim01_k01}) // { arity: 10 }
                Filter (#1{dim01_k01}) IS NOT NULL // { arity: 9 }
                  Get l0 // { arity: 9 }
                Distinct project=[#0{dim01_k01}] // { arity: 1 }
                  Project (#1{dim01_k01}) // { arity: 1 }
                    Get l1 // { arity: 15 }
          Get l0 // { arity: 9 }
      Get l1 // { arity: 15 }

Target cluster: quickstart

EOF

# ON predicates with non-trivial equi-join conjuncts were previously planned using
# the fallback lowering strategy.
query T multiline
EXPLAIN DECORRELATED PLAN WITH(enable new outer join lowering = false, humanized expressions, arity) FOR
SELECT * FROM left_joins.outer CROSS JOIN LATERAL (
  SELECT
    facts.facts_k01,
    facts.facts_d01,
    facts.dim01_k01,
    dim01.dim01_d01
  FROM
    left_joins_raw.facts LEFT JOIN
    left_joins_raw.dim01 ON(facts.dim01_k01 + x = dim01.dim01_k01 + y)
);
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins.outer // { arity: 2 }
  cte l1 =
    Distinct project=[#0{x}, #1{y}] // { arity: 2 }
      Get l0 // { arity: 2 }
  cte l2 =
    CrossJoin // { arity: 11 }
      Get l1 // { arity: 2 }
      Get materialize.left_joins_raw.facts // { arity: 9 }
  cte l3 =
    Filter ((#3{dim01_k01} + #0{x}) = (#11{dim01_k01} + #1{y})) // { arity: 17 }
      Project (#0{x}..=#10{facts_d05}, #13{dim01_k01}..=#18{dim01_d05}) // { arity: 17 }
        Join on=(#0{x} = #11{x} AND #1{y} = #12{y}) // { arity: 19 }
          Get l2 // { arity: 11 }
          CrossJoin // { arity: 8 }
            Get l1 // { arity: 2 }
            Get materialize.left_joins_raw.dim01 // { arity: 6 }
Return // { arity: 6 }
  Project (#0{x}, #1{y}, #4{facts_k01}..=#7{dim01_d01}) // { arity: 6 }
    Join on=(#0{x} = #2{x} AND #1{y} = #3{y}) // { arity: 8 }
      Get l0 // { arity: 2 }
      Project (#0{x}..=#2{facts_k01}, #6{facts_d01}, #3{dim01_k01}, #12{dim01_d01}) // { arity: 6 }
        Union // { arity: 17 }
          Get l3 // { arity: 17 }
          CrossJoin // { arity: 17 }
            Project (#0{x}..=#10{facts_d05}) // { arity: 11 }
              Join on=(#0{x} = #11{x} AND #1{y} = #12{y} AND #2{facts_k01} = #13{facts_k01} AND #3{dim01_k01} = #14{dim01_k01} AND #4{dim02_k01} = #15{dim02_k01} AND #5{dim03_k01} = #16{dim03_k01} AND #6{facts_d01} = #17{facts_d01} AND #7{facts_d02} = #18{facts_d02} AND #8{facts_d03} = #19{facts_d03} AND #9{facts_d04} = #20{facts_d04} AND #10{facts_d05} = #21{facts_d05}) // { arity: 22 }
                Union // { arity: 11 }
                  Negate // { arity: 11 }
                    Distinct project=[#0{x}..=#10{facts_d05}] // { arity: 11 }
                      Get l3 // { arity: 17 }
                  Distinct project=[#0{x}..=#10{facts_d05}] // { arity: 11 }
                    Get l2 // { arity: 11 }
                Get l2 // { arity: 11 }
            Constant // { arity: 6 }
              - (null, null, null, null, null, null)

Target cluster: quickstart

EOF

# ON predicates with non-trivial equi-join conjuncts are planned using the
# optimized lowering strategy.
query T multiline
EXPLAIN DECORRELATED PLAN WITH(humanized expressions, arity) FOR
SELECT * FROM left_joins.outer CROSS JOIN LATERAL (
  SELECT
    facts.facts_k01,
    facts.facts_d01,
    facts.dim01_k01,
    dim01.dim01_d01
  FROM
    left_joins_raw.facts LEFT JOIN
    left_joins_raw.dim01 ON(facts.dim01_k01 + x = dim01.dim01_k01 + y)
);
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins.outer // { arity: 2 }
  cte l1 =
    Distinct project=[#0{x}, #1{y}] // { arity: 2 }
      Get l0 // { arity: 2 }
  cte l2 =
    CrossJoin // { arity: 11 }
      Get l1 // { arity: 2 }
      Get materialize.left_joins_raw.facts // { arity: 9 }
  cte l3 =
    Filter ((#3{dim01_k01} + #0{x}) = (#11{dim01_k01} + #1{y})) // { arity: 17 }
      Project (#0{x}..=#10{facts_d05}, #13{dim01_k01}..=#18{dim01_d05}) // { arity: 17 }
        Join on=(#0{x} = #11{x} AND #1{y} = #12{y}) // { arity: 19 }
          Get l2 // { arity: 11 }
          CrossJoin // { arity: 8 }
            Get l1 // { arity: 2 }
            Get materialize.left_joins_raw.dim01 // { arity: 6 }
Return // { arity: 6 }
  Project (#0{x}, #1{y}, #4{facts_k01}..=#7{dim01_d01}) // { arity: 6 }
    Join on=(#0{x} = #2{x} AND #1{y} = #3{y}) // { arity: 8 }
      Get l0 // { arity: 2 }
      Project (#0{x}..=#2{facts_k01}, #6{facts_d01}, #3{dim01_k01}, #12{dim01_d01}) // { arity: 6 }
        Union // { arity: 17 }
          Map (null, null, null, null, null, null) // { arity: 17 }
            Union // { arity: 11 }
              Negate // { arity: 11 }
                Project (#0{x}..=#10{facts_d05}) // { arity: 11 }
                  Join on=(#0{x} = #11{x} AND #1{y} = #12{y} AND (#3{dim01_k01} + #0{x}) = #13) // { arity: 14 }
                    Filter ((#3{dim01_k01} + #0{x})) IS NOT NULL // { arity: 11 }
                      Get l2 // { arity: 11 }
                    Distinct project=[#0{x}..=#2] // { arity: 3 }
                      Project (#17{x}..=#19) // { arity: 3 }
                        Map (#0{x}, #1{y}, (#3{dim01_k01} + #0{x})) // { arity: 20 }
                          Get l3 // { arity: 17 }
              Get l2 // { arity: 11 }
          Get l3 // { arity: 17 }

Target cluster: quickstart

EOF

# ON predicates with non-trivial equi-join conjuncts are planned using the
# optimized lowering strategy. Same as above, but a RIGHT JOIN.
query T multiline
EXPLAIN DECORRELATED PLAN WITH(humanized expressions, arity) FOR
SELECT * FROM left_joins.outer CROSS JOIN LATERAL (
  SELECT
    facts.facts_k01,
    facts.facts_d01,
    facts.dim01_k01,
    dim01.dim01_d01
  FROM
    left_joins_raw.dim01 RIGHT JOIN
    left_joins_raw.facts ON(facts.dim01_k01 + x = dim01.dim01_k01 + y)
);
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins.outer // { arity: 2 }
  cte l1 =
    Distinct project=[#0{x}, #1{y}] // { arity: 2 }
      Get l0 // { arity: 2 }
  cte l2 =
    CrossJoin // { arity: 11 }
      Get l1 // { arity: 2 }
      Get materialize.left_joins_raw.facts // { arity: 9 }
  cte l3 =
    Filter ((#2{dim01_k01} + #1{y}) = (#9{dim01_k01} + #0{x})) // { arity: 17 }
      Project (#0{x}..=#7{dim01_d05}, #10{facts_k01}..=#18{facts_d05}) // { arity: 17 }
        Join on=(#0{x} = #8{x} AND #1{y} = #9{y}) // { arity: 19 }
          CrossJoin // { arity: 8 }
            Get l1 // { arity: 2 }
            Get materialize.left_joins_raw.dim01 // { arity: 6 }
          Get l2 // { arity: 11 }
Return // { arity: 6 }
  Project (#0{x}, #1{y}, #4{facts_k01}..=#7{dim01_d01}) // { arity: 6 }
    Join on=(#0{x} = #2{x} AND #1{y} = #3{y}) // { arity: 8 }
      Get l0 // { arity: 2 }
      Project (#0{x}, #1{y}, #8{facts_k01}, #12{facts_d01}, #9{dim01_k01}, #3{dim01_d01}) // { arity: 6 }
        Union // { arity: 17 }
          Project (#0{x}, #1{y}, #11..=#16, #2{facts_k01}..=#10{facts_d05}) // { arity: 17 }
            Map (null, null, null, null, null, null) // { arity: 17 }
              Union // { arity: 11 }
                Negate // { arity: 11 }
                  Project (#0{x}..=#10{facts_d05}) // { arity: 11 }
                    Join on=(#0{x} = #11{x} AND #1{y} = #12{y} AND (#3{dim01_k01} + #0{x}) = #13) // { arity: 14 }
                      Filter ((#3{dim01_k01} + #0{x})) IS NOT NULL // { arity: 11 }
                        Get l2 // { arity: 11 }
                      Distinct project=[#0{x}..=#2] // { arity: 3 }
                        Project (#17{x}..=#19) // { arity: 3 }
                          Map (#0{x}, #1{y}, (#2{dim01_k01} + #1{y})) // { arity: 20 }
                            Get l3 // { arity: 17 }
                Get l2 // { arity: 11 }
          Get l3 // { arity: 17 }

Target cluster: quickstart

EOF

# ON predicates with at least one equi-join conjunct were previouisly planned
# using the fallback lowering strategy.
query T multiline
EXPLAIN DECORRELATED PLAN WITH(enable new outer join lowering = false, humanized expressions, arity) FOR
SELECT * FROM left_joins.outer CROSS JOIN LATERAL (
  SELECT
    facts.facts_k01,
    facts.facts_d01,
    facts.dim01_k01,
    dim01.dim01_d01
  FROM
    left_joins_raw.facts LEFT JOIN
    left_joins_raw.dim01 ON(
      facts.dim01_k01 + x = dim01.dim01_k01 + y AND
      facts_d02 = 42 AND
      dim01_d02 = 24
    )
);
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins.outer // { arity: 2 }
  cte l1 =
    Distinct project=[#0{x}, #1{y}] // { arity: 2 }
      Get l0 // { arity: 2 }
  cte l2 =
    CrossJoin // { arity: 11 }
      Get l1 // { arity: 2 }
      Get materialize.left_joins_raw.facts // { arity: 9 }
  cte l3 =
    Filter ((((#3{dim01_k01} + #0{x}) = (#11{dim01_k01} + #1{y})) AND (#7{facts_d02} = 42)) AND (#13{dim01_d02} = 24)) // { arity: 17 }
      Project (#0{x}..=#10{facts_d05}, #13{dim01_k01}..=#18{dim01_d05}) // { arity: 17 }
        Join on=(#0{x} = #11{x} AND #1{y} = #12{y}) // { arity: 19 }
          Get l2 // { arity: 11 }
          CrossJoin // { arity: 8 }
            Get l1 // { arity: 2 }
            Get materialize.left_joins_raw.dim01 // { arity: 6 }
Return // { arity: 6 }
  Project (#0{x}, #1{y}, #4{facts_k01}..=#7{dim01_d01}) // { arity: 6 }
    Join on=(#0{x} = #2{x} AND #1{y} = #3{y}) // { arity: 8 }
      Get l0 // { arity: 2 }
      Project (#0{x}..=#2{facts_k01}, #6{facts_d01}, #3{dim01_k01}, #12{dim01_d01}) // { arity: 6 }
        Union // { arity: 17 }
          Get l3 // { arity: 17 }
          CrossJoin // { arity: 17 }
            Project (#0{x}..=#10{facts_d05}) // { arity: 11 }
              Join on=(#0{x} = #11{x} AND #1{y} = #12{y} AND #2{facts_k01} = #13{facts_k01} AND #3{dim01_k01} = #14{dim01_k01} AND #4{dim02_k01} = #15{dim02_k01} AND #5{dim03_k01} = #16{dim03_k01} AND #6{facts_d01} = #17{facts_d01} AND #7{facts_d02} = #18{facts_d02} AND #8{facts_d03} = #19{facts_d03} AND #9{facts_d04} = #20{facts_d04} AND #10{facts_d05} = #21{facts_d05}) // { arity: 22 }
                Union // { arity: 11 }
                  Negate // { arity: 11 }
                    Distinct project=[#0{x}..=#10{facts_d05}] // { arity: 11 }
                      Get l3 // { arity: 17 }
                  Distinct project=[#0{x}..=#10{facts_d05}] // { arity: 11 }
                    Get l2 // { arity: 11 }
                Get l2 // { arity: 11 }
            Constant // { arity: 6 }
              - (null, null, null, null, null, null)

Target cluster: quickstart

EOF

# ON predicates with at least one equi-join conjunct are planned using the
# optimized lowering strategy.
query T multiline
EXPLAIN DECORRELATED PLAN WITH(humanized expressions, arity) FOR
SELECT * FROM left_joins.outer CROSS JOIN LATERAL (
  SELECT
    facts.facts_k01,
    facts.facts_d01,
    facts.dim01_k01,
    dim01.dim01_d01
  FROM
    left_joins_raw.facts LEFT JOIN
    left_joins_raw.dim01 ON(
      facts.dim01_k01 + x = dim01.dim01_k01 + y AND
      facts_d02 = 42 AND
      dim01_d02 = 24
    )
);
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins.outer // { arity: 2 }
  cte l1 =
    Distinct project=[#0{x}, #1{y}] // { arity: 2 }
      Get l0 // { arity: 2 }
  cte l2 =
    CrossJoin // { arity: 11 }
      Get l1 // { arity: 2 }
      Get materialize.left_joins_raw.facts // { arity: 9 }
  cte l3 =
    Filter (#7{facts_d02} = 42) AND (#13{dim01_d02} = 24) AND ((#3{dim01_k01} + #0{x}) = (#11{dim01_k01} + #1{y})) // { arity: 17 }
      Project (#0{x}..=#10{facts_d05}, #13{dim01_k01}..=#18{dim01_d05}) // { arity: 17 }
        Join on=(#0{x} = #11{x} AND #1{y} = #12{y}) // { arity: 19 }
          Get l2 // { arity: 11 }
          CrossJoin // { arity: 8 }
            Get l1 // { arity: 2 }
            Get materialize.left_joins_raw.dim01 // { arity: 6 }
Return // { arity: 6 }
  Project (#0{x}, #1{y}, #4{facts_k01}..=#7{dim01_d01}) // { arity: 6 }
    Join on=(#0{x} = #2{x} AND #1{y} = #3{y}) // { arity: 8 }
      Get l0 // { arity: 2 }
      Project (#0{x}..=#2{facts_k01}, #6{facts_d01}, #3{dim01_k01}, #12{dim01_d01}) // { arity: 6 }
        Union // { arity: 17 }
          Map (null, null, null, null, null, null) // { arity: 17 }
            Union // { arity: 11 }
              Negate // { arity: 11 }
                Project (#0{x}..=#10{facts_d05}) // { arity: 11 }
                  Join on=(#0{x} = #11{x} AND #1{y} = #12{y} AND (#3{dim01_k01} + #0{x}) = #13) // { arity: 14 }
                    Filter ((#3{dim01_k01} + #0{x})) IS NOT NULL AND (#7{facts_d02} = 42) // { arity: 11 }
                      Get l2 // { arity: 11 }
                    Distinct project=[#0{x}..=#2] // { arity: 3 }
                      Project (#17{x}..=#19) // { arity: 3 }
                        Map (#0{x}, #1{y}, (#3{dim01_k01} + #0{x})) // { arity: 20 }
                          Get l3 // { arity: 17 }
              Get l2 // { arity: 11 }
          Get l3 // { arity: 17 }

Target cluster: quickstart

EOF

# ON predicates with at least one equi-join conjunct are planned using the
# optimized lowering strategy. Same as above, but a RIGHT JOIN.
query T multiline
EXPLAIN DECORRELATED PLAN WITH(humanized expressions, arity) FOR
SELECT * FROM left_joins.outer CROSS JOIN LATERAL (
  SELECT
    facts.facts_k01,
    facts.facts_d01,
    facts.dim01_k01,
    dim01.dim01_d01
  FROM
    left_joins_raw.dim01 RIGHT JOIN
    left_joins_raw.facts ON(
      facts.dim01_k01 + x = dim01.dim01_k01 + y AND
      facts_d02 = 42 AND
      dim01_d02 = 24
    )
);
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins.outer // { arity: 2 }
  cte l1 =
    Distinct project=[#0{x}, #1{y}] // { arity: 2 }
      Get l0 // { arity: 2 }
  cte l2 =
    CrossJoin // { arity: 11 }
      Get l1 // { arity: 2 }
      Get materialize.left_joins_raw.facts // { arity: 9 }
  cte l3 =
    Filter (#4{dim01_d02} = 24) AND (#13{facts_d02} = 42) AND ((#2{dim01_k01} + #1{y}) = (#9{dim01_k01} + #0{x})) // { arity: 17 }
      Project (#0{x}..=#7{dim01_d05}, #10{facts_k01}..=#18{facts_d05}) // { arity: 17 }
        Join on=(#0{x} = #8{x} AND #1{y} = #9{y}) // { arity: 19 }
          CrossJoin // { arity: 8 }
            Get l1 // { arity: 2 }
            Get materialize.left_joins_raw.dim01 // { arity: 6 }
          Get l2 // { arity: 11 }
Return // { arity: 6 }
  Project (#0{x}, #1{y}, #4{facts_k01}..=#7{dim01_d01}) // { arity: 6 }
    Join on=(#0{x} = #2{x} AND #1{y} = #3{y}) // { arity: 8 }
      Get l0 // { arity: 2 }
      Project (#0{x}, #1{y}, #8{facts_k01}, #12{facts_d01}, #9{dim01_k01}, #3{dim01_d01}) // { arity: 6 }
        Union // { arity: 17 }
          Project (#0{x}, #1{y}, #11..=#16, #2{facts_k01}..=#10{facts_d05}) // { arity: 17 }
            Map (null, null, null, null, null, null) // { arity: 17 }
              Union // { arity: 11 }
                Negate // { arity: 11 }
                  Project (#0{x}..=#10{facts_d05}) // { arity: 11 }
                    Join on=(#0{x} = #11{x} AND #1{y} = #12{y} AND (#3{dim01_k01} + #0{x}) = #13) // { arity: 14 }
                      Filter ((#3{dim01_k01} + #0{x})) IS NOT NULL AND (#7{facts_d02} = 42) // { arity: 11 }
                        Get l2 // { arity: 11 }
                      Distinct project=[#0{x}..=#2] // { arity: 3 }
                        Project (#17{x}..=#19) // { arity: 3 }
                          Map (#0{x}, #1{y}, (#2{dim01_k01} + #1{y})) // { arity: 20 }
                            Get l3 // { arity: 17 }
                Get l2 // { arity: 11 }
          Get l3 // { arity: 17 }

Target cluster: quickstart

EOF

# ON predicates with non-trivial equi-join conjuncts and local predicates are
# planned using the optimized lowering strategy (FULL OUTER JOIN).
query T multiline
EXPLAIN DECORRELATED PLAN WITH(humanized expressions, arity) FOR
SELECT * FROM left_joins.outer CROSS JOIN LATERAL (
  SELECT
    dim01_k01,
    dim01_k01,
    dim02_d02,
    dim02_d02
  FROM
    left_joins_raw.dim01 FULL OUTER JOIN
    left_joins_raw.dim02 ON(
      coalesce(dim01_k01, x) = coalesce(dim02_k01, y) AND
      dim01_d03 > 42 AND dim02_d03 < 24
    )
);
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins.outer // { arity: 2 }
  cte l1 =
    Distinct project=[#0{x}, #1{y}] // { arity: 2 }
      Get l0 // { arity: 2 }
  cte l2 =
    CrossJoin // { arity: 8 }
      Get l1 // { arity: 2 }
      Get materialize.left_joins_raw.dim01 // { arity: 6 }
  cte l3 =
    CrossJoin // { arity: 8 }
      Get l1 // { arity: 2 }
      Get materialize.left_joins_raw.dim02 // { arity: 6 }
  cte l4 =
    Filter (coalesce(#2{dim01_k01}, #0{x}) = coalesce(#8{dim02_k01}, #1{y})) AND (#11{dim02_d03} < 24) AND (#5{dim01_d03} > 42) // { arity: 14 }
      Project (#0{x}..=#7{dim01_d05}, #10{dim02_k01}..=#15{dim02_d05}) // { arity: 14 }
        Join on=(#0{x} = #8{x} AND #1{y} = #9{y}) // { arity: 16 }
          Get l2 // { arity: 8 }
          Get l3 // { arity: 8 }
  cte l5 =
    Distinct project=[#0{x}..=#2] // { arity: 3 }
      Project (#14{x}..=#16) // { arity: 3 }
        Map (#0{x}, #1{y}, coalesce(#2{dim01_k01}, #0{x})) // { arity: 17 }
          Get l4 // { arity: 14 }
Return // { arity: 6 }
  Project (#0{x}, #1{y}, #4{dim01_k01}..=#7{dim02_d02}) // { arity: 6 }
    Join on=(#0{x} = #2{x} AND #1{y} = #3{y}) // { arity: 8 }
      Get l0 // { arity: 2 }
      Project (#0{x}..=#2{dim01_k01}, #2{dim01_k01}, #10{dim02_d02}, #10{dim02_d02}) // { arity: 6 }
        Union // { arity: 14 }
          Project (#0{x}, #1{y}, #8..=#13, #2{dim02_k01}..=#7{dim02_d05}) // { arity: 14 }
            Map (null, null, null, null, null, null) // { arity: 14 }
              Union // { arity: 8 }
                Negate // { arity: 8 }
                  Project (#0{x}..=#7{dim02_d05}) // { arity: 8 }
                    Join on=(#0{x} = #8{x} AND #1{y} = #9{y} AND coalesce(#2{dim02_k01}, #1{y}) = #10) // { arity: 11 }
                      Filter (coalesce(#2{dim02_k01}, #1{y})) IS NOT NULL AND (#5{dim02_d03} < 24) // { arity: 8 }
                        Get l3 // { arity: 8 }
                      Get l5 // { arity: 3 }
                Get l3 // { arity: 8 }
          Map (null, null, null, null, null, null) // { arity: 14 }
            Union // { arity: 8 }
              Negate // { arity: 8 }
                Project (#0{x}..=#7{dim01_d05}) // { arity: 8 }
                  Join on=(#0{x} = #8{x} AND #1{y} = #9{y} AND coalesce(#2{dim01_k01}, #0{x}) = #10) // { arity: 11 }
                    Filter (coalesce(#2{dim01_k01}, #0{x})) IS NOT NULL AND (#5{dim01_d03} > 42) // { arity: 8 }
                      Get l2 // { arity: 8 }
                    Get l5 // { arity: 3 }
              Get l2 // { arity: 8 }
          Get l4 // { arity: 14 }

Target cluster: quickstart

EOF

# ON predicates with subqueries are still planned using the fallback lowering
# strategy.
query T multiline
EXPLAIN DECORRELATED PLAN WITH(humanized expressions, arity) FOR
SELECT * FROM left_joins.outer CROSS JOIN LATERAL (
  SELECT
    facts.facts_k01,
    facts.facts_d01,
    facts.dim01_k01,
    dim01.dim01_d01
  FROM
    left_joins_raw.facts LEFT JOIN
    left_joins_raw.dim01 ON(
      facts.dim01_k01 + x = dim01.dim01_k01 + y AND
      facts_d02 = 42 AND
      dim01_d01 = ANY('{24, 42}'::int[])
    )
);
----
With
  cte l0 =
    CrossJoin // { arity: 2 }
      Constant // { arity: 0 }
        - ()
      Get materialize.left_joins.outer // { arity: 2 }
  cte l1 =
    Distinct project=[#0{x}, #1{y}] // { arity: 2 }
      Get l0 // { arity: 2 }
  cte l2 =
    CrossJoin // { arity: 11 }
      Get l1 // { arity: 2 }
      Get materialize.left_joins_raw.facts // { arity: 9 }
  cte l3 =
    Project (#0{x}..=#10{facts_d05}, #13{dim01_k01}..=#18{dim01_d05}) // { arity: 17 }
      Join on=(#0{x} = #11{x} AND #1{y} = #12{y}) // { arity: 19 }
        Get l2 // { arity: 11 }
        CrossJoin // { arity: 8 }
          Get l1 // { arity: 2 }
          Get materialize.left_joins_raw.dim01 // { arity: 6 }
  cte l4 =
    Distinct project=[#12{dim01_d01}] // { arity: 1 }
      Get l3 // { arity: 17 }
  cte l5 =
    Reduce group_by=[#0{dim01_d01}] aggregates=[any((#0{dim01_d01} = #1{right_col0_0}))] // { arity: 2 }
      FlatMap unnest_array(strtoarray("{24, 42}")) // { arity: 2 }
        Get l4 // { arity: 1 }
  cte l6 =
    Union // { arity: 2 }
      Get l5 // { arity: 2 }
      CrossJoin // { arity: 2 }
        Project (#0{dim01_d01}) // { arity: 1 }
          Join on=(#0{dim01_d01} = #1{dim01_d01}) // { arity: 2 }
            Union // { arity: 1 }
              Negate // { arity: 1 }
                Distinct project=[#0{dim01_d01}] // { arity: 1 }
                  Get l5 // { arity: 2 }
              Distinct project=[#0{dim01_d01}] // { arity: 1 }
                Get l4 // { arity: 1 }
            Get l4 // { arity: 1 }
        Constant // { arity: 1 }
          - (false)
  cte l7 =
    Union // { arity: 2 }
      Get l6 // { arity: 2 }
      Project (#0{dim01_d01}, #2) // { arity: 2 }
        FlatMap guard_subquery_size(#1{count}) // { arity: 3 }
          Reduce group_by=[#0{dim01_d01}] aggregates=[count(*)] // { arity: 2 }
            Get l6 // { arity: 2 }
  cte l8 =
    Project (#0{x}..=#16{dim01_d05}) // { arity: 17 }
      Filter ((((#3{dim01_k01} + #0{x}) = (#11{dim01_k01} + #1{y})) AND (#7{facts_d02} = 42)) AND #17{any}) // { arity: 18 }
        Project (#0{x}..=#16{dim01_d05}, #18{any}) // { arity: 18 }
          Join on=(#12{dim01_d01} = #17{dim01_d01}) // { arity: 19 }
            Get l3 // { arity: 17 }
            Union // { arity: 2 }
              Get l7 // { arity: 2 }
              CrossJoin // { arity: 2 }
                Project (#0{dim01_d01}) // { arity: 1 }
                  Join on=(#0{dim01_d01} = #1{dim01_d01}) // { arity: 2 }
                    Union // { arity: 1 }
                      Negate // { arity: 1 }
                        Distinct project=[#0{dim01_d01}] // { arity: 1 }
                          Get l7 // { arity: 2 }
                      Distinct project=[#0{dim01_d01}] // { arity: 1 }
                        Get l4 // { arity: 1 }
                    Get l4 // { arity: 1 }
                Constant // { arity: 1 }
                  - (null)
Return // { arity: 6 }
  Project (#0{x}, #1{y}, #4{facts_k01}..=#7{dim01_d01}) // { arity: 6 }
    Join on=(#0{x} = #2{x} AND #1{y} = #3{y}) // { arity: 8 }
      Get l0 // { arity: 2 }
      Project (#0{x}..=#2{facts_k01}, #6{facts_d01}, #3{dim01_k01}, #12{dim01_d01}) // { arity: 6 }
        Union // { arity: 17 }
          Get l8 // { arity: 17 }
          CrossJoin // { arity: 17 }
            Project (#0{x}..=#10{facts_d05}) // { arity: 11 }
              Join on=(#0{x} = #11{x} AND #1{y} = #12{y} AND #2{facts_k01} = #13{facts_k01} AND #3{dim01_k01} = #14{dim01_k01} AND #4{dim02_k01} = #15{dim02_k01} AND #5{dim03_k01} = #16{dim03_k01} AND #6{facts_d01} = #17{facts_d01} AND #7{facts_d02} = #18{facts_d02} AND #8{facts_d03} = #19{facts_d03} AND #9{facts_d04} = #20{facts_d04} AND #10{facts_d05} = #21{facts_d05}) // { arity: 22 }
                Union // { arity: 11 }
                  Negate // { arity: 11 }
                    Distinct project=[#0{x}..=#10{facts_d05}] // { arity: 11 }
                      Get l8 // { arity: 17 }
                  Distinct project=[#0{x}..=#10{facts_d05}] // { arity: 11 }
                    Get l2 // { arity: 11 }
                Get l2 // { arity: 11 }
            Constant // { arity: 6 }
              - (null, null, null, null, null, null)

Target cluster: quickstart

EOF

# The following tests are for the EXPLAIN override of this
# feature flag, so we want to disable it.
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_new_outer_join_lowering TO false;
----
COMPLETE 0

# EXPLAIN a SELECT with the feature turned in the EXPLAIN config.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(enable new outer join lowering, humanized expressions, arity) AS VERBOSE TEXT FOR
SELECT
  facts.facts_k01,
  facts.facts_d01,
  facts.facts_d02,
  dim01.dim01_k01,
  dim01.dim01_d01,
  dim01.dim01_d02
FROM
  left_joins_raw.facts LEFT JOIN
  left_joins_raw.dim01 ON(
    coalesce(facts.dim01_k01, 5) = coalesce(dim01.dim01_k01, 5) AND
    facts_d01 > 42 AND dim01_d02 < 24
  );
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[coalesce(#1{dim01_k01}, 5)]] // { arity: 4 }
        Project (#0{facts_k01}, #1{dim01_k01}, #4{facts_d01}, #5{facts_d02}) // { arity: 4 }
          Filter (#4{facts_d01} > 42) // { arity: 9 }
            ReadStorage materialize.left_joins_raw.facts // { arity: 9 }
    cte l1 =
      Join on=(coalesce(#1{dim01_k01}, 5) = coalesce(#4{dim01_k01}, 5)) type=differential // { arity: 7 }
        Get l0 // { arity: 4 }
        ArrangeBy keys=[[coalesce(#0{dim01_k01}, 5)]] // { arity: 3 }
          Project (#0{dim01_k01}..=#2{dim01_d02}) // { arity: 3 }
            Filter (#2{dim01_d02} < 24) // { arity: 6 }
              ReadStorage materialize.left_joins_raw.dim01 // { arity: 6 }
  Return // { arity: 6 }
    Union // { arity: 6 }
      Map (null, null, null) // { arity: 6 }
        Union // { arity: 3 }
          Negate // { arity: 3 }
            Project (#0{facts_k01}, #2{facts_d01}, #3{facts_d02}) // { arity: 3 }
              Join on=(#4 = coalesce(#1{dim01_k01}, 5)) type=differential // { arity: 5 }
                Get l0 // { arity: 4 }
                ArrangeBy keys=[[#0]] // { arity: 1 }
                  Distinct project=[coalesce(#0{dim01_k01}, 5)] // { arity: 1 }
                    Project (#1{dim01_k01}) // { arity: 1 }
                      Get l1 // { arity: 7 }
          Project (#0{facts_k01}, #4{facts_d01}, #5{facts_d02}) // { arity: 3 }
            ReadStorage materialize.left_joins_raw.facts // { arity: 9 }
      Project (#0{facts_k01}, #2{facts_d01}..=#6{dim01_d02}) // { arity: 6 }
        Get l1 // { arity: 7 }

Source materialize.left_joins_raw.facts
Source materialize.left_joins_raw.dim01
  filter=((#2{dim01_d02} < 24))

Target cluster: quickstart

EOF

# Define the above statement as a view.
statement ok
CREATE VIEW v AS
SELECT
  facts.facts_k01,
  facts.facts_d01,
  facts.facts_d02,
  dim01.dim01_k01,
  dim01.dim01_d01,
  dim01.dim01_d02
FROM
  left_joins_raw.facts LEFT JOIN
  left_joins_raw.dim01 ON(
    coalesce(facts.dim01_k01, 5) = coalesce(dim01.dim01_k01, 5) AND
    facts_d01 > 42 AND dim01_d02 < 24
  );

# EXPLAIN a SELECT * FROM <view> with the feature turned in the EXPLAIN config.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(enable new outer join lowering, reoptimize imported views, humanized expressions, arity) AS VERBOSE TEXT FOR
SELECT * FROM v;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[coalesce(#1{dim01_k01}, 5)]] // { arity: 4 }
        Project (#0{facts_k01}, #1{dim01_k01}, #4{facts_d01}, #5{facts_d02}) // { arity: 4 }
          Filter (#4{facts_d01} > 42) // { arity: 9 }
            ReadStorage materialize.left_joins_raw.facts // { arity: 9 }
    cte l1 =
      Join on=(coalesce(#1{dim01_k01}, 5) = coalesce(#4{dim01_k01}, 5)) type=differential // { arity: 7 }
        Get l0 // { arity: 4 }
        ArrangeBy keys=[[coalesce(#0{dim01_k01}, 5)]] // { arity: 3 }
          Project (#0{dim01_k01}..=#2{dim01_d02}) // { arity: 3 }
            Filter (#2{dim01_d02} < 24) // { arity: 6 }
              ReadStorage materialize.left_joins_raw.dim01 // { arity: 6 }
  Return // { arity: 6 }
    Union // { arity: 6 }
      Map (null, null, null) // { arity: 6 }
        Union // { arity: 3 }
          Negate // { arity: 3 }
            Project (#0{facts_k01}, #2{facts_d01}, #3{facts_d02}) // { arity: 3 }
              Join on=(#4 = coalesce(#1{dim01_k01}, 5)) type=differential // { arity: 5 }
                Get l0 // { arity: 4 }
                ArrangeBy keys=[[#0]] // { arity: 1 }
                  Distinct project=[coalesce(#0{dim01_k01}, 5)] // { arity: 1 }
                    Project (#1{dim01_k01}) // { arity: 1 }
                      Get l1 // { arity: 7 }
          Project (#0{facts_k01}, #4{facts_d01}, #5{facts_d02}) // { arity: 3 }
            ReadStorage materialize.left_joins_raw.facts // { arity: 9 }
      Project (#0{facts_k01}, #2{facts_d01}..=#6{dim01_d02}) // { arity: 6 }
        Get l1 // { arity: 7 }

Source materialize.left_joins_raw.facts
Source materialize.left_joins_raw.dim01
  filter=((#2{dim01_d02} < 24))

Target cluster: quickstart

EOF

# EXPLAIN a CREATE INDEX with the feature turned in the EXPLAIN config.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(enable new outer join lowering, reoptimize imported views, humanized expressions, arity) AS VERBOSE TEXT FOR
CREATE INDEX ON v(facts_k01);
----
materialize.public.v_facts_k01_idx:
  ArrangeBy keys=[[#0{facts_k01}]] // { arity: 6 }
    ReadGlobalFromSameDataflow materialize.public.v // { arity: 6 }

materialize.public.v:
  With
    cte l0 =
      ArrangeBy keys=[[coalesce(#1{dim01_k01}, 5)]] // { arity: 4 }
        Project (#0{facts_k01}, #1{dim01_k01}, #4{facts_d01}, #5{facts_d02}) // { arity: 4 }
          Filter (#4{facts_d01} > 42) // { arity: 9 }
            ReadStorage materialize.left_joins_raw.facts // { arity: 9 }
    cte l1 =
      Join on=(coalesce(#1{dim01_k01}, 5) = coalesce(#4{dim01_k01}, 5)) type=differential // { arity: 7 }
        Get l0 // { arity: 4 }
        ArrangeBy keys=[[coalesce(#0{dim01_k01}, 5)]] // { arity: 3 }
          Project (#0{dim01_k01}..=#2{dim01_d02}) // { arity: 3 }
            Filter (#2{dim01_d02} < 24) // { arity: 6 }
              ReadStorage materialize.left_joins_raw.dim01 // { arity: 6 }
  Return // { arity: 6 }
    Union // { arity: 6 }
      Map (null, null, null) // { arity: 6 }
        Union // { arity: 3 }
          Negate // { arity: 3 }
            Project (#0{facts_k01}, #2{facts_d01}, #3{facts_d02}) // { arity: 3 }
              Join on=(#4 = coalesce(#1{dim01_k01}, 5)) type=differential // { arity: 5 }
                Get l0 // { arity: 4 }
                ArrangeBy keys=[[#0]] // { arity: 1 }
                  Distinct project=[coalesce(#0{dim01_k01}, 5)] // { arity: 1 }
                    Project (#1{dim01_k01}) // { arity: 1 }
                      Get l1 // { arity: 7 }
          Project (#0{facts_k01}, #4{facts_d01}, #5{facts_d02}) // { arity: 3 }
            ReadStorage materialize.left_joins_raw.facts // { arity: 9 }
      Project (#0{facts_k01}, #2{facts_d01}..=#6{dim01_d02}) // { arity: 6 }
        Get l1 // { arity: 7 }

Source materialize.left_joins_raw.facts
Source materialize.left_joins_raw.dim01
  filter=((#2{dim01_d02} < 24))

Target cluster: quickstart

EOF

# EXPLAIN a CREATE MATERIALIZED VIEW with the feature turned in the EXPLAIN config.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(enable new outer join lowering, humanized expressions, arity) AS VERBOSE TEXT FOR
CREATE MATERIALIZED VIEW mv AS
SELECT
  facts.facts_k01,
  facts.facts_d01,
  facts.facts_d02,
  dim01.dim01_k01,
  dim01.dim01_d01,
  dim01.dim01_d02
FROM
  left_joins_raw.facts LEFT JOIN
  left_joins_raw.dim01 ON(
    coalesce(facts.dim01_k01, 5) = coalesce(dim01.dim01_k01, 5) AND
    facts_d01 > 42 AND dim01_d02 < 24
  );
----
materialize.public.mv:
  With
    cte l0 =
      ArrangeBy keys=[[coalesce(#1{dim01_k01}, 5)]] // { arity: 4 }
        Project (#0{facts_k01}, #1{dim01_k01}, #4{facts_d01}, #5{facts_d02}) // { arity: 4 }
          Filter (#4{facts_d01} > 42) // { arity: 9 }
            ReadStorage materialize.left_joins_raw.facts // { arity: 9 }
    cte l1 =
      Join on=(coalesce(#1{dim01_k01}, 5) = coalesce(#4{dim01_k01}, 5)) type=differential // { arity: 7 }
        Get l0 // { arity: 4 }
        ArrangeBy keys=[[coalesce(#0{dim01_k01}, 5)]] // { arity: 3 }
          Project (#0{dim01_k01}..=#2{dim01_d02}) // { arity: 3 }
            Filter (#2{dim01_d02} < 24) // { arity: 6 }
              ReadStorage materialize.left_joins_raw.dim01 // { arity: 6 }
  Return // { arity: 6 }
    Union // { arity: 6 }
      Map (null, null, null) // { arity: 6 }
        Union // { arity: 3 }
          Negate // { arity: 3 }
            Project (#0{facts_k01}, #2{facts_d01}, #3{facts_d02}) // { arity: 3 }
              Join on=(#4 = coalesce(#1{dim01_k01}, 5)) type=differential // { arity: 5 }
                Get l0 // { arity: 4 }
                ArrangeBy keys=[[#0]] // { arity: 1 }
                  Distinct project=[coalesce(#0{dim01_k01}, 5)] // { arity: 1 }
                    Project (#1{dim01_k01}) // { arity: 1 }
                      Get l1 // { arity: 7 }
          Project (#0{facts_k01}, #4{facts_d01}, #5{facts_d02}) // { arity: 3 }
            ReadStorage materialize.left_joins_raw.facts // { arity: 9 }
      Project (#0{facts_k01}, #2{facts_d01}..=#6{dim01_d02}) // { arity: 6 }
        Get l1 // { arity: 7 }

Source materialize.left_joins_raw.facts
Source materialize.left_joins_raw.dim01
  filter=((#2{dim01_d02} < 24))

Target cluster: quickstart

EOF