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

statement ok
CREATE TABLE t (
  a int,
  b int
)

statement ok
CREATE VIEW ov AS SELECT * FROM t ORDER BY b asc, a desc LIMIT 5

statement ok
CREATE VIEW v AS
SELECT * FROM t WHERE a IS NOT NULL

statement ok
CREATE INDEX v_a_idx ON v(a)

statement ok
CREATE MATERIALIZED VIEW mv AS
SELECT * FROM t WHERE a IS NOT NULL

mode cockroach

# EXPLAIN INDEX is not supported for decorrelated plans
statement error cannot EXPLAIN DECORRELATED PLAN FOR INDEX
EXPLAIN DECORRELATED PLAN AS TEXT FOR
INDEX v_a_idx

# EXPLAIN CREATE INDEX is not supported for decorrelated plans
statement error cannot EXPLAIN DECORRELATED PLAN FOR CREATE INDEX
EXPLAIN DECORRELATED PLAN AS TEXT FOR
CREATE INDEX v_a_idx ON v(a)

# Test constant error.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT 1 / 0
----
Project (#0)
  Map ((1 / 0))
    Constant
      - ()

Target cluster: quickstart

EOF

# Test constant with two elements.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
(SELECT 1, 2) UNION ALL (SELECT 1, 2) UNION ALL (SELECT 3, 4)
----
Union
  Project (#0, #1)
    Map (1, 2)
      Constant
        - ()
  Project (#0, #1)
    Map (1, 2)
      Constant
        - ()
  Project (#0, #1)
    Map (3, 4)
      Constant
        - ()

Target cluster: mz_catalog_server

EOF

# Test basic linear chains.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT 1, a + b as c FROM mv WHERE a > 0 and b < 0 and a + b > 0
----
Project (#2, #3)
  Map (1, (#0{a} + #1{b}))
    Filter (((#0{a} > 0) AND (#1{b} < 0)) AND ((#0{a} + #1{b}) > 0))
      CrossJoin
        Constant
          - ()
        Get materialize.public.mv

Target cluster: quickstart

EOF

# Test table functions in the select clause (FlatMap).
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT generate_series(a, b) from t
----
Project (#2)
  FlatMap generate_series(#0{a}, #1{b}, 1)
    CrossJoin
      Constant
        - ()
      Get materialize.public.t

Target cluster: quickstart

EOF

# Test Threshold, Union, Distinct, Negate.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT a FROM t EXCEPT SELECT b FROM mv
----
Threshold
  Union
    Distinct project=[#0]
      Project (#0)
        CrossJoin
          Constant
            - ()
          Get materialize.public.t
    Negate
      Distinct project=[#0]
        Project (#1)
          CrossJoin
            Constant
              - ()
            Get materialize.public.mv

Target cluster: quickstart

EOF

# Test Threshold, Union, Distinct, Negate.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT a FROM t EXCEPT ALL SELECT b FROM mv
----
Threshold
  Union
    Project (#0)
      CrossJoin
        Constant
          - ()
        Get materialize.public.t
    Negate
      Project (#1)
        CrossJoin
          Constant
            - ()
          Get materialize.public.mv

Target cluster: quickstart

EOF

# Test Finish.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT * FROM t ORDER BY b asc, a desc LIMIT 5
----
Finish order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5 output=[#0, #1]
  CrossJoin
    Constant
      - ()
    Get materialize.public.t

Target cluster: quickstart

EOF

# Test Reduce (global).
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT abs(min(a) - max(a)) FROM t
----
With
  cte l0 =
    Reduce aggregates=[min(#0{a}), max(#0{a})]
      CrossJoin
        Constant
          - ()
        Get materialize.public.t
Return
  Project (#2)
    Map (abs((#0{"?column?"} - #1{"?column?"})))
      Union
        Get l0
        CrossJoin
          Project ()
            CrossJoin
              Union
                Negate
                  Distinct project=[]
                    Get l0
                Distinct project=[]
                  Constant
                    - ()
              Constant
                - ()
          Constant
            - (null, null)

Target cluster: quickstart

EOF

# Test Reduce (local).
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT abs(min(a) - max(a)) FROM t GROUP BY b
----
Project (#3)
  Map (abs((#1{"?column?"} - #2{"?column?"})))
    Reduce group_by=[#2] aggregates=[min(#0{a}), max(#0{a})]
      Project (#0..=#2)
        Map (#1{b})
          CrossJoin
            Constant
              - ()
            Get materialize.public.t

Target cluster: quickstart

EOF

# Test EXISTS subqueries.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT * FROM t WHERE EXISTS(SELECT * FROM mv WHERE t.a < mv.a) AND EXISTS(SELECT * FROM mv WHERE t.b > mv.b)
----
With
  cte l0 =
    Filter (true AND true)
      CrossJoin
        Constant
          - ()
        Get materialize.public.t
  cte l1 =
    Distinct project=[#0]
      Get l0
  cte l2 =
    Map (true)
      Distinct project=[#0]
        Filter (#0{a} < #1{a})
          CrossJoin
            Get l1
            Get materialize.public.mv
  cte l3 =
    Project (#0, #1)
      Filter #2
        Project (#0, #1, #3)
          Join on=(#0 = #2)
            Get l0
            Union
              Get l2
              CrossJoin
                Project (#0)
                  Join on=(#0 = #1)
                    Union
                      Negate
                        Distinct project=[#0]
                          Get l2
                      Distinct project=[#0]
                        Get l1
                    Get l1
                Constant
                  - (false)
  cte l4 =
    Distinct project=[#1]
      Get l3
  cte l5 =
    Map (true)
      Distinct project=[#0]
        Filter (#0{b} > #2{b})
          CrossJoin
            Get l4
            Get materialize.public.mv
Return
  Project (#0, #1)
    Filter #2
      Project (#0, #1, #3)
        Join on=(#1 = #2)
          Get l3
          Union
            Get l5
            CrossJoin
              Project (#0)
                Join on=(#0 = #1)
                  Union
                    Negate
                      Distinct project=[#0]
                        Get l5
                    Distinct project=[#0]
                      Get l4
                  Get l4
              Constant
                - (false)

Target cluster: quickstart

EOF

# Test SELECT subqueries.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT (SELECT v.a FROM v WHERE v.b = t.b LIMIT 1), (SELECT mv.a FROM mv WHERE mv.b = t.b LIMIT 1) FROM t
----
With
  cte l0 =
    CrossJoin
      Constant
        - ()
      Get materialize.public.t
  cte l1 =
    Distinct project=[#0, #1]
      Get l0
  cte l2 =
    Distinct project=[#1]
      Get l1
  cte l3 =
    Project (#0, #1)
      TopK group_by=[#0] limit=1
        Filter (#2{b} = #0{b})
          CrossJoin
            Get l2
            Get materialize.public.v
  cte l4 =
    Union
      Get l3
      Project (#0, #2)
        FlatMap guard_subquery_size(#1)
          Reduce group_by=[#0] aggregates=[count(*)]
            Get l3
  cte l5 =
    Distinct project=[#0, #1]
      Get l0
  cte l6 =
    Distinct project=[#1]
      Get l5
  cte l7 =
    Project (#0, #1)
      TopK group_by=[#0] limit=1
        Filter (#2{b} = #0{b})
          CrossJoin
            Get l6
            Get materialize.public.mv
  cte l8 =
    Union
      Get l7
      Project (#0, #2)
        FlatMap guard_subquery_size(#1)
          Reduce group_by=[#0] aggregates=[count(*)]
            Get l7
Return
  Project (#8, #9)
    Map (#4, #7)
      Join on=(#0 = #2 = #5 AND #1 = #3 = #6)
        Get l0
        Project (#0, #1, #3)
          Join on=(#1 = #2)
            Get l1
            Union
              Get l4
              CrossJoin
                Project (#0)
                  Join on=(#0 = #1)
                    Union
                      Negate
                        Distinct project=[#0]
                          Get l4
                      Distinct project=[#0]
                        Get l2
                    Get l2
                Constant
                  - (null)
        Project (#0, #1, #3)
          Join on=(#1 = #2)
            Get l5
            Union
              Get l8
              CrossJoin
                Project (#0)
                  Join on=(#0 = #1)
                    Union
                      Negate
                        Distinct project=[#0]
                          Get l8
                      Distinct project=[#0]
                        Get l6
                    Get l6
                Constant
                  - (null)

Target cluster: quickstart

EOF

# Test CrossJoin derived from a comma join without a predicate.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT t1.a, t2.a FROM t as t1, t as t2
----
Project (#0, #2)
  Project (#0..=#3)
    CrossJoin
      CrossJoin
        Constant
          - ()
        Get materialize.public.t
      CrossJoin
        Constant
          - ()
        Get materialize.public.t

Target cluster: quickstart

EOF

# Test CrossJoin derived from an INNER JOIN with a trivial ON clause.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT t1.a, t2.a FROM t as t1 INNER JOIN t as t2 ON true
----
Project (#0, #2)
  Project (#0..=#3)
    CrossJoin
      CrossJoin
        Constant
          - ()
        Get materialize.public.t
      CrossJoin
        Constant
          - ()
        Get materialize.public.t

Target cluster: quickstart

EOF

# Test InnerJoin (comma syntax).
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT t1.a, t2.a
FROM
  t as t1,
  t as t2,
  t as t3
WHERE t1.b = t2.b AND t2.b = t3.b
----
Project (#0, #2)
  Filter ((#1{b} = #3{b}) AND (#3{b} = #5{b}))
    Project (#0..=#5)
      CrossJoin
        Project (#0..=#3)
          CrossJoin
            CrossJoin
              Constant
                - ()
              Get materialize.public.t
            CrossJoin
              Constant
                - ()
              Get materialize.public.t
        CrossJoin
          Constant
            - ()
          Get materialize.public.t

Target cluster: quickstart

EOF

# Test InnerJoin (ON syntax).
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT t1.a, t2.a
FROM t as t1
INNER JOIN t as t2 ON t1.b = t2.b
INNER JOIN t as t3 ON t2.b = t3.b
----
Project (#0, #2)
  Filter (#3{b} = #5{b})
    Project (#0..=#5)
      CrossJoin
        Filter (#1{b} = #3{b})
          Project (#0..=#3)
            CrossJoin
              CrossJoin
                Constant
                  - ()
                Get materialize.public.t
              CrossJoin
                Constant
                  - ()
                Get materialize.public.t
        CrossJoin
          Constant
            - ()
          Get materialize.public.t

Target cluster: quickstart

EOF

# Test LEFT JOIN and RIGHT JOIN.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT t1.a, t2.a
FROM t as t1
LEFT JOIN t as t2 ON t1.b = t2.b
RIGHT JOIN t as t3 ON t2.b = t3.b
----
With
  cte l0 =
    CrossJoin
      Constant
        - ()
      Get materialize.public.t
  cte l1 =
    Filter (#1{b} = #3{b})
      Project (#0..=#3)
        CrossJoin
          Get l0
          CrossJoin
            Constant
              - ()
            Get materialize.public.t
  cte l2 =
    CrossJoin
      Constant
        - ()
      Get materialize.public.t
  cte l3 =
    Filter (#3{b} = #5{b})
      Project (#0..=#5)
        CrossJoin
          Union
            Map (null, null)
              Union
                Negate
                  Project (#0, #1)
                    Join on=(#1{b} = #2)
                      Filter (#1{b}) IS NOT NULL
                        Get l0
                      Distinct project=[#0]
                        Project (#1)
                          Get l1
                Get l0
            Get l1
          Get l2
Return
  Project (#0, #2)
    Union
      Project (#2..=#5, #0, #1)
        Map (null, null, null, null)
          Union
            Negate
              Project (#0, #1)
                Join on=(#1{b} = #2)
                  Filter (#1{b}) IS NOT NULL
                    Get l2
                  Distinct project=[#0]
                    Project (#3)
                      Get l3
            Get l2
      Get l3

Target cluster: quickstart

EOF

# Test a single CTE.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
WITH x AS (SELECT t.a * t.b as v from t) SELECT a.v + b.v FROM x as a, x as b
----
With
  cte l0 =
    Project (#2)
      Map ((#0{a} * #1{b}))
        CrossJoin
          Constant
            - ()
          Get materialize.public.t
Return
  Project (#2)
    Project (#0..=#2)
      Map ((#0{v} + #1{v}))
        Project (#0, #1)
          CrossJoin
            Get l0
            Get l0

Target cluster: quickstart

EOF

# Test multiple CTEs: a case where we cannot pull the let statement up through
# the join because the local l0 is correlated against the lhs of the enclosing join.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT
  *
FROM
  (
    SELECT * FROM t
  ) as r1
  CROSS JOIN LATERAL (
    WITH r2 as (
      SELECT MAX(r1.a * t.a) AS m FROM t
    )
    SELECT * FROM r2 WHERE r2.m != r1.a
  ) as r3
  CROSS JOIN LATERAL (
    WITH r4 as (
      SELECT MAX(r1.a * t.a) AS m FROM t
    )
    SELECT * FROM r4 WHERE r4.m != r1.a OR (r4.m IS NOT NULL AND r1.a IS NULL)
  ) as r5;
----
With
  cte l0 =
    CrossJoin
      Constant
        - ()
      Get materialize.public.t
  cte l1 =
    Distinct project=[#0]
      Get l0
  cte l2 =
    Reduce group_by=[#0] aggregates=[max((#0{a} * #1{a}))]
      CrossJoin
        Get l1
        Get materialize.public.t
  cte l3 =
    Project (#0, #1, #3)
      Join on=(#0 = #2)
        Get l0
        Filter (#1{m} != #0{a})
          Union
            Get l2
            CrossJoin
              Project (#0)
                Join on=(#0 = #1)
                  Union
                    Negate
                      Distinct project=[#0]
                        Get l2
                    Distinct project=[#0]
                      Get l1
                  Get l1
              Constant
                - (null)
  cte l4 =
    Distinct project=[#0]
      Get l3
  cte l5 =
    Reduce group_by=[#0] aggregates=[max((#0{a} * #1{a}))]
      CrossJoin
        Get l4
        Get materialize.public.t
Return
  Project (#0..=#2, #4)
    Join on=(#0 = #3)
      Get l3
      Filter ((#1{m} != #0{a}) OR ((#1{m}) IS NOT NULL AND (#0{a}) IS NULL))
        Union
          Get l5
          CrossJoin
            Project (#0)
              Join on=(#0 = #1)
                Union
                  Negate
                    Distinct project=[#0]
                      Get l5
                  Distinct project=[#0]
                    Get l4
                Get l4
            Constant
              - (null)

Target cluster: quickstart

EOF

# Test multiple CTEs: a case where we cannot pull the let statement up
# through the join because the local l0 is correlated against the lhs of
# the enclosing join.
query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR
SELECT
  *
FROM
  (
    SELECT * FROM t
  ) as r1
  CROSS JOIN LATERAL (
    WITH r4 as (
      SELECT MAX(r1.a * t.a) AS m FROM t
    )
    SELECT *
    FROM
      r4
      CROSS JOIN LATERAL (
        WITH r2 as (
          SELECT MAX(r1.a * t.a) AS m FROM t
        )
        SELECT * FROM r2 WHERE r1.a = r4.m AND r2.m > 5
      ) as r3
    WHERE a != r1.a
  ) as r5;
----
With
  cte l0 =
    CrossJoin
      Constant
        - ()
      Get materialize.public.t
  cte l1 =
    Distinct project=[#0]
      Get l0
  cte l2 =
    Reduce group_by=[#0] aggregates=[max((#0{a} * #1{a}))]
      CrossJoin
        Get l1
        Get materialize.public.t
  cte l3 =
    Union
      Get l2
      CrossJoin
        Project (#0)
          Join on=(#0 = #1)
            Union
              Negate
                Distinct project=[#0]
                  Get l2
              Distinct project=[#0]
                Get l1
            Get l1
        Constant
          - (null)
  cte l4 =
    Distinct project=[#1, #0]
      Get l3
  cte l5 =
    Reduce group_by=[#0, #1] aggregates=[max((#1{a} * #2{a}))]
      CrossJoin
        Get l4
        Get materialize.public.t
Return
  Project (#0, #1, #3, #4)
    Join on=(#0 = #2)
      Get l0
      Filter (#0{a} != #0{a})
        Project (#0, #1, #4)
          Join on=(#1 = #2 AND #0 = #3)
            Get l3
            Filter ((#1{a} = #0{m}) AND (#2{m} > 5))
              Union
                Get l5
                CrossJoin
                  Project (#0, #1)
                    Join on=(#0 = #2 AND #1 = #3)
                      Union
                        Negate
                          Distinct project=[#0, #1]
                            Get l5
                        Distinct project=[#0, #1]
                          Get l4
                      Get l4
                  Constant
                    - (null)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN DECORRELATED PLAN AS TEXT FOR SELECT COUNT(*);
----
With
  cte l0 =
    Reduce aggregates=[count(*)]
      Constant
        - ()
Return
  Union
    Get l0
    CrossJoin
      Project ()
        CrossJoin
          Union
            Negate
              Distinct project=[]
                Get l0
            Distinct project=[]
              Constant
                - ()
          Constant
            - ()
      Constant
        - (0)

Target cluster: quickstart

EOF

# Test materialize#17348.

statement ok
CREATE TABLE r(f0 INT, f1 INT, f2 INT, f3 INT, f4 INT, f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, f10 INT, f11 INT, f12 INT, f13 INT, f14 INT, f15 INT, f16 INT);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT *
FROM r AS r0, r AS r1
WHERE
  r0.f0=r1.f0 AND
  r0.f2=r1.f2 AND
  r0.f3=r1.f3 AND
  r0.f4=r1.f4 AND
  r0.f6=r1.f6 AND
  r0.f8=r1.f8 AND
  r0.f9=r1.f9 AND
  r0.f11=r1.f11 AND
  r0.f12=r1.f12 AND
  r0.f13=r1.f13 AND
  r0.f15=r1.f15 AND
  r0.f16=r1.f16;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{f0}, #2{f2}..=#4{f4}, #6{f6}, #8{f8}, #9{f9}, #11{f11}..=#13{f13}, #15{f15}, #16{f16}]]
        Filter (#0{f0}) IS NOT NULL AND (#2{f2}) IS NOT NULL AND (#3{f3}) IS NOT NULL AND (#4{f4}) IS NOT NULL AND (#6{f6}) IS NOT NULL AND (#8{f8}) IS NOT NULL AND (#9{f9}) IS NOT NULL AND (#11{f11}) IS NOT NULL AND (#12{f12}) IS NOT NULL AND (#13{f13}) IS NOT NULL AND (#15{f15}) IS NOT NULL AND (#16{f16}) IS NOT NULL
          ReadStorage materialize.public.r
  Return
    Project (#0{f0}..=#16{f16}, #0{f0}, #18{f1}, #2{f2}..=#4{f4}, #22{f5}, #6{f6}, #24{f7}, #8{f8}, #9{f9}, #27{f10}, #11{f11}..=#13{f13}, #31{f14}, #15{f15}, #16{f16})
      Join on=(#0{f0} = #17{f0} AND #2{f2} = #19{f2} AND #3{f3} = #20{f3} AND #4{f4} = #21{f4} AND #6{f6} = #23{f6} AND #8{f8} = #25{f8} AND #9{f9} = #26{f9} AND #11{f11} = #28{f11} AND #12{f12} = #29{f12} AND #13{f13} = #30{f13} AND #15{f15} = #32{f15} AND #16{f16} = #33{f16}) type=differential
        Get l0
        Get l0

Source materialize.public.r
  filter=((#0{f0}) IS NOT NULL AND (#2{f2}) IS NOT NULL AND (#3{f3}) IS NOT NULL AND (#4{f4}) IS NOT NULL AND (#6{f6}) IS NOT NULL AND (#8{f8}) IS NOT NULL AND (#9{f9}) IS NOT NULL AND (#11{f11}) IS NOT NULL AND (#12{f12}) IS NOT NULL AND (#13{f13}) IS NOT NULL AND (#15{f15}) IS NOT NULL AND (#16{f16}) IS NOT NULL)

Target cluster: quickstart

EOF