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

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 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 raw plans
statement error cannot EXPLAIN RAW PLAN FOR INDEX
EXPLAIN RAW PLAN AS TEXT FOR
INDEX v_a_idx

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

# Test basic linear chains.
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT a + 1, b, 4 FROM mv WHERE a > 0
----
Project (#2, #1, #3)
  Map ((#0{a} + 1), 4)
    Filter (#0{a} > 0)
      Get materialize.public.mv

Target cluster: quickstart

EOF

# Test table functions (CallTable).
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT * FROM generate_series(1, 7)
----
CallTable generate_series(1, 7, 1)

Target cluster: quickstart

EOF

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

Target cluster: quickstart

EOF

# Test virtual syntax (EXCEPT).
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT a FROM t EXCEPT SELECT b FROM mv
----
Except
  Project (#0)
    Get materialize.public.t
  Project (#1)
    Get materialize.public.mv

Target cluster: quickstart

EOF

# Test virtual syntax (EXCEPT ALL).
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT a FROM t EXCEPT ALL SELECT b FROM mv
----
ExceptAll
  Project (#0)
    Get materialize.public.t
  Project (#1)
    Get materialize.public.mv

Target cluster: quickstart

EOF

# Test Finish.
query T multiline
EXPLAIN RAW 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]
  Get materialize.public.t

Target cluster: quickstart

EOF

# Test Reduce (global).
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT abs(min(a) - max(a)) FROM t
----
Project (#2)
  Map (abs((#0{?column?} - #1{?column?})))
    Reduce aggregates=[min(#0{a}), max(#0{a})]
      Get materialize.public.t

Target cluster: quickstart

EOF

# Test Reduce (local).
query T multiline
EXPLAIN RAW 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})]
      Map (#1{b})
        Get materialize.public.t

Target cluster: quickstart

EOF

# Test EXISTS subqueries.
query T multiline
EXPLAIN RAW 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 [l2 as subquery-2] =
    Filter (#^1{b} > #1{b})
      Get materialize.public.mv
  cte [l1 as subquery-1] =
    Filter (#^0{a} < #0{a})
      Get materialize.public.mv
Return
  Filter (exists(Get l1) AND exists(Get l2))
    Get materialize.public.t

Target cluster: quickstart

EOF

# Test SELECT subqueries.
query T multiline
EXPLAIN RAW 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
----
Project (#2, #3)
  With
    cte [l2 as subquery-2] =
      Project (#0)
        TopK limit=1
          Filter (#1{b} = #^1{b})
            Get materialize.public.mv
    cte [l1 as subquery-1] =
      Project (#0)
        TopK limit=1
          Filter (#1{b} = #^1{b})
            Get materialize.public.v
  Return
    Map (select(Get l1), select(Get l2))
      Get materialize.public.t

Target cluster: quickstart

EOF

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

Target cluster: quickstart

EOF

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

Target cluster: quickstart

EOF

# Test InnerJoin (comma syntax).
query T multiline
EXPLAIN RAW 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}))
    CrossJoin
      CrossJoin
        Get materialize.public.t
        Get materialize.public.t
      Get materialize.public.t

Target cluster: quickstart

EOF

# Test InnerJoin (ON syntax).
query T multiline
EXPLAIN RAW 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)
  InnerJoin (#3{b} = #5{b})
    InnerJoin (#1{b} = #3{b})
      Get materialize.public.t
      Get materialize.public.t
    Get materialize.public.t

Target cluster: quickstart

EOF

# Test InnerJoin (ON syntax).
query T multiline
EXPLAIN RAW 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
----
Project (#0, #2)
  RightOuterJoin (#3{b} = #5{b})
    LeftOuterJoin (#1{b} = #3{b})
      Get materialize.public.t
      Get materialize.public.t
    Get materialize.public.t

Target cluster: quickstart

EOF

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

Target cluster: quickstart

EOF

# Test multiple CTEs: directly nested 'Let' variants are rendered in a flattened way.
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
WITH A AS (SELECT 1 AS a), B as (SELECT a as b FROM A WHERE a > 0) SELECT * FROM A, B;
----
With
  cte [l0 as a] =
    Map (1)
      Constant
        - ()
  cte [l1 as b] =
    Filter (#0{a} > 0)
      Get l0
Return
  CrossJoin
    Get l0
    Get l1

Target cluster: mz_catalog_server

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 RAW 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;
----
CrossJoin
  CrossJoin
    Get materialize.public.t
    With
      cte [l0 as r2] =
        Reduce aggregates=[max((#^0{a} * #0{a}))]
          Get materialize.public.t
    Return
      Filter (#0{m} != #^0{a})
        Get l0
  With
    cte [l0 as r4] =
      Reduce aggregates=[max((#^0{a} * #0{a}))]
        Get materialize.public.t
  Return
    Filter ((#0{m} != #^0{a}) OR ((#0{m}) IS NOT NULL AND (#^0{a}) IS NULL))
      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 RAW 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;
----
CrossJoin
  Get materialize.public.t
  With
    cte [l0 as r4] =
      Reduce aggregates=[max((#^0{a} * #0{a}))]
        Get materialize.public.t
  Return
    Filter (#^0{a} != #^0{a})
      CrossJoin
        Get l0
        With
          cte [l1 as r2] =
            Reduce aggregates=[max((#^^0{a} * #0{a}))]
              Get materialize.public.t
        Return
          Filter ((#^^0{a} = #^0{m}) AND (#0{m} > 5))
            Get l1

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR SELECT COUNT(*);
----
Reduce aggregates=[count(*)]
  Constant
    - ()

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

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT lag(b, 3) OVER ()
FROM t;
----
Project (#2)
  Map (lag(row(#1{b}, 3, null)) over (order by []))
    Get materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT lag(b, 333) RESPECT NULLS OVER ()
FROM t;
----
Project (#2)
  Map (lag(row(#1{b}, 333, null)) over (order by []))
    Get materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT lag(b, 333) IGNORE NULLS OVER ()
FROM t;
----
Project (#2)
  Map (lag(row(#1{b}, 333, null)) ignore nulls over (order by []))
    Get materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT row_number() OVER (ORDER BY r DESC)
FROM (
    SELECT row_number() OVER (PARTITION BY l) as r
    FROM (
        SELECT lag(b, 3) OVER (PARTITION BY b%2, a%3 ORDER BY a DESC, -b, 2*b + a NULLS FIRST) as l
        FROM t
    )
);
----
Project (#1)
  Map (row_number() over (order by [#0{r} desc nulls_first]))
    Project (#1)
      Map (row_number() over (partition by [#0{l}] order by []))
        Project (#2)
          Map (lag(row(#1{b}, 3, null)) over (partition by [(#1{b} % 2), (#0{a} % 3)] order by [#0{a} desc nulls_first, -(#1{b}) asc nulls_last, ((2 * #1{b}) + #0{a}) asc nulls_first]))
            Get materialize.public.t

Target cluster: quickstart

EOF

# Default frame is not printed (even when explicitly specified by the user).
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT
    first_value(b) over (partition by b%6 order by b + 33 asc range between unbounded preceding and current row)
FROM t;
----
Project (#2)
  Map (first_value(#1{b}) over (partition by [(#1{b} % 6)] order by [(#1{b} + 33) asc nulls_last]))
    Get materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT
    first_value(b) over (partition by b%6 order by b + 33 asc rows between 2 preceding and current row)
FROM t;
----
Project (#2)
  Map (first_value(#1{b}) over (partition by [(#1{b} % 6)] order by [(#1{b} + 33) asc nulls_last] rows between 2 preceding and current row))
    Get materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT
    sum(b + 8) over (partition by b%6 order by b + 33 desc rows between 2 preceding and 3 following)
FROM t;
----
Project (#2)
  Map (sum((#1{b} + 8)) over (partition by [(#1{b} % 6)] order by [(#1{b} + 33) desc nulls_first] rows between 2 preceding and 3 following))
    Get materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
WITH MUTUALLY RECURSIVE
    foo (a int, b int) AS (SELECT 1, 2 UNION SELECT a, 7 FROM bar),
    bar (a int) as (SELECT a FROM foo)
SELECT * FROM bar;
----
With Mutually Recursive
  cte [l0 as foo] =
    Distinct
      Union
        Map (1, 2)
          Constant
            - ()
        Map (7)
          Get l1
  cte [l1 as bar] =
    Project (#0)
      Get l0
Return
  Get l1

Target cluster: mz_catalog_server

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
WITH MUTUALLY RECURSIVE (RECURSION LIMIT = 5)
    foo (a int, b int) AS (SELECT 1, 2 UNION SELECT a, 7 FROM bar),
    bar (a int) as (SELECT a FROM foo)
SELECT * FROM bar;
----
With Mutually Recursive [recursion_limit=5]
  cte [l0 as foo] =
    Distinct
      Union
        Map (1, 2)
          Constant
            - ()
        Map (7)
          Get l1
  cte [l1 as bar] =
    Project (#0)
      Get l0
Return
  Get l1

Target cluster: mz_catalog_server

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
WITH MUTUALLY RECURSIVE (RETURN AT RECURSION LIMIT = 5)
    foo (a int, b int) AS (SELECT 1, 2 UNION SELECT a, 7 FROM bar),
    bar (a int) as (SELECT a FROM foo)
SELECT * FROM bar;
----
With Mutually Recursive [recursion_limit=5, return_at_limit]
  cte [l0 as foo] =
    Distinct
      Union
        Map (1, 2)
          Constant
            - ()
        Map (7)
          Get l1
  cte [l1 as bar] =
    Project (#0)
      Get l0
Return
  Get l1

Target cluster: mz_catalog_server

EOF

statement ok
CREATE TABLE accounts(id int, balance int);

statement ok
CREATE TABLE account_details(id bigint, address string);

statement ok
CREATE OR REPLACE VIEW v AS
SELECT
  *
FROM
  accounts a
  LEFT JOIN account_details ad USING(id)
WHERE
  balance = 100;

# Must explain the "Raw Plan".
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
VIEW v;
----
Project (#0, #1, #3)
  Filter (#1{balance} = 100)
    LeftOuterJoin (integer_to_bigint(#0{id}) = #2{id})
      Get materialize.public.accounts
      Get materialize.public.account_details

EOF

statement ok
CREATE TABLE t5(
    x int,
    y int NOT NULL,
    z int
);

# `count(*)` is planned as `count(true)`. We take care in EXPLAIN to show `count(true)` as `count(*)` to avoid confusing
# users.
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT count(*)
FROM t5;
----
Reduce aggregates=[count(*)]
  Get materialize.public.t5

Target cluster: quickstart

EOF

query error DISTINCT \* not supported as function args
EXPLAIN RAW PLAN AS TEXT FOR
SELECT count(distinct *)
FROM t5;

# `count(true)` is currently also printed as `count(*)` in EXPLAIN, which I'd say is fine.
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT count(true)
FROM t5;
----
Reduce aggregates=[count(*)]
  Get materialize.public.t5

Target cluster: quickstart

EOF

# But `count(DISTINCT true)` means an entirely different thing, so EXPLAIN shouldn't conflate it with `count(*)`.
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT count(DISTINCT true)
FROM t5;
----
Reduce aggregates=[count(distinct true)]
  Get materialize.public.t5

Target cluster: quickstart

EOF

# OFFSET clause in RowSetFinishing
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT a+b
FROM t
OFFSET 1;
----
Finish offset=1 output=[#0]
  Project (#2)
    Map ((#0{a} + #1{b}))
      Get materialize.public.t

Target cluster: quickstart

EOF

# OFFSET clause in TopK
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT a+b, (SELECT a*b FROM t OFFSET 1)
FROM t;
----
Project (#2, #3)
  With
    cte [l1 as subquery-1] =
      Project (#2)
        TopK offset=1
          Map ((#0{a} * #1{b}))
            Get materialize.public.t
  Return
    Map ((#0{a} + #1{b}), select(Get l1))
      Get materialize.public.t

Target cluster: quickstart

EOF

# OFFSET 0 makes the TopK disappear, even if it was an expression that comes out to 0.
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT a+b
FROM t
OFFSET 0;
----
Project (#2)
  Map ((#0{a} + #1{b}))
    Get materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT a+b
FROM t
OFFSET 7-7;
----
Project (#2)
  Map ((#0{a} + #1{b}))
    Get materialize.public.t

Target cluster: quickstart

EOF

# The OFFSET should _not_ be printed when it is 0.
query T multiline
EXPLAIN RAW PLAN AS TEXT FOR
SELECT a+b
FROM t
LIMIT 9
OFFSET 7-7;
----
Finish limit=9 output=[#0]
  Project (#2)
    Map ((#0{a} + #1{b}))
      Get materialize.public.t

Target cluster: quickstart

EOF

query error db error: ERROR: Invalid OFFSET clause: invalid input syntax for type bigint: invalid digit found in string: "aaa"
EXPLAIN RAW PLAN AS TEXT FOR
SELECT a+b
FROM t
OFFSET 'aaa'::bigint;