Materialize/test/sqllogictest/explain/optimized_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 TABLE u (
  c int,
  d int
);

statement ok
CREATE TABLE v (
  e int,
  f int
);

statement ok
CREATE INDEX t_a_idx ON t(a);

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

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

statement ok
CREATE INDEX iv_a_idx ON iv(a);

statement ok
CREATE INDEX iv_b_idx ON iv(b);

# This is an identical index to the above (on the same object, on the same key)
statement ok
CREATE INDEX iv_b_idx_2 ON iv(b);

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

statement ok
CREATE MATERIALIZED VIEW non_empty_mv AS
SELECT 1 as x, 2 as y;

statement ok
CREATE INDEX non_empty_mv_idx ON non_empty_mv(y + 7);

statement ok
CREATE MATERIALIZED VIEW empty_mv AS
SELECT;

statement ok
CREATE INDEX empty_mv_idx ON empty_mv();

mode cockroach

# Test target cluster selection for mz_system tables without transactions.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(no fast path, humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM mz_views;
----
Explained Query:
  ReadIndex on=mz_views mz_views_ind=[*** full scan ***]

Used Indexes:
  - mz_catalog.mz_views_ind (*** full scan ***)

Target cluster: mz_catalog_server

EOF

# Test target cluster selection for mz_system tables inside a transaction.
statement ok
BEGIN

statement ok
SELECT * FROM t;

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(no fast path, humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM mz_views;
----
Explained Query:
  ReadStorage mz_catalog.mz_views

Source mz_catalog.mz_views

Target cluster: quickstart

EOF

statement ok
ROLLBACK

# Test constant error.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT 1 / 0
----
Explained Query (fast path):
  Error "division by zero"

Target cluster: quickstart

EOF

# Test constant with two elements.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
(SELECT 1, 2) UNION ALL (SELECT 1, 2) UNION ALL (SELECT 3, 4)
----
Explained Query (fast path):
  Constant
    - ((1, 2) x 2)
    - (3, 4)

Target cluster: mz_catalog_server

EOF

# Test catalog queries (index found based on cluster auto-routing).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM mz_internal.mz_source_status_history
----
Explained Query (fast path):
  Project (#1{occurred_at}, #0{source_id}, #2{status}..=#5{replica_id})
    ReadIndex on=mz_internal.mz_source_status_history mz_source_status_history_ind=[*** full scan ***]

Used Indexes:
  - mz_internal.mz_source_status_history_ind (*** full scan ***)

Target cluster: mz_catalog_server

EOF

# Test fast path rendering on a non-trivial index.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM non_empty_mv where y + 7 = 9
----
Explained Query (fast path):
  Project (#1{x}, #2{y})
    ReadIndex on=materialize.public.non_empty_mv non_empty_mv_idx=[lookup value=(9)]

Used Indexes:
  - materialize.public.non_empty_mv_idx (lookup)

Target cluster: quickstart

EOF

# Test fast path rendering on an empty index on an empty relation.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM empty_mv
----
Explained Query (fast path):
  ReadIndex on=materialize.public.empty_mv empty_mv_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.empty_mv_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test basic linear chains (fast path).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT 1, a + b as c FROM iv WHERE b = 5 and a < 0 and a + b > 0
----
Explained Query (fast path):
  Project (#4, #3)
    Filter (#1{a} < 0) AND ((#1{a} + #0{b}) > 0)
      Map ((#1{a} + 5), 1)
        ReadIndex on=materialize.public.iv iv_b_idx_2=[lookup value=(5)]

Used Indexes:
  - materialize.public.iv_b_idx_2 (lookup)

Target cluster: quickstart

EOF

# Test basic linear chains (slow path).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT 1, a + b as c FROM mv WHERE a > 0 and b < 0 and a + b > 0
----
Explained Query:
  Project (#3, #2)
    Filter (#1{b} < 0) AND (#0{a} > 0) AND (#2 > 0)
      Map ((#0{a} + #1{b}), 1)
        ReadStorage materialize.public.mv

Source materialize.public.mv
  filter=((#0{a} > 0) AND (#1{b} < 0) AND ((#0{a} + #1{b}) > 0))

Target cluster: quickstart

EOF

# Test table functions in the select clause (FlatMap).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT generate_series(a, b) from t
----
Explained Query:
  Project (#2)
    FlatMap generate_series(#0{a}, #1{b}, 1)
      ReadIndex on=t t_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test Threshold, Union, Distinct, Negate.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT a FROM t EXCEPT SELECT b FROM mv
----
Explained Query:
  Threshold
    Union
      Distinct project=[#0{a}]
        Project (#0{a})
          ReadIndex on=t t_a_idx=[*** full scan ***]
      Negate
        Distinct project=[#0{b}]
          Project (#1{b})
            ReadStorage materialize.public.mv

Source materialize.public.mv

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test Threshold, Union, Distinct, Negate.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT a FROM t EXCEPT ALL SELECT b FROM mv
----
Explained Query:
  Threshold
    Union
      Project (#0{a})
        ReadIndex on=t t_a_idx=[*** full scan ***]
      Negate
        Project (#1{b})
          ReadStorage materialize.public.mv

Source materialize.public.mv

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test TopK.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM ov
----
Explained Query:
  TopK order_by=[#1{b} asc nulls_last, #0{a} desc nulls_first] limit=5
    ReadIndex on=t t_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test Finish.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM t ORDER BY b asc, a desc LIMIT 5
----
Explained Query (fast path):
  Finish order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5 output=[#0, #1]
    ReadIndex on=materialize.public.t t_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test Reduce (global).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT abs(min(a) - max(a)) FROM t
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[min(#0{a}), max(#0{a})]
        Project (#0{a})
          ReadIndex on=t t_a_idx=[*** full scan ***]
  Return
    Project (#2)
      Map (abs((#0{min_a} - #1{max_a})))
        Union
          Get l0
          Map (null, null)
            Union
              Negate
                Project ()
                  Get l0
              Constant
                - ()

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test Reduce (local).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT abs(min(a) - max(a)) FROM t GROUP BY b
----
Explained Query:
  Project (#3)
    Map (abs((#1{min_a} - #2{max_a})))
      Reduce group_by=[#1{b}] aggregates=[min(#0{a}), max(#0{a})]
        ReadIndex on=t t_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test EXISTS subqueries.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE 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)
----
Explained Query:
  With
    cte l0 =
      Project (#0{a}, #1{b})
        Join on=(#0{a} = #2{a}) type=differential
          ArrangeBy keys=[[#0{a}]]
            ReadIndex on=t t_a_idx=[differential join]
          ArrangeBy keys=[[#0{a}]]
            Distinct project=[#0{a}]
              Project (#0{a})
                Filter (#0{a} < #1{a})
                  CrossJoin type=differential
                    ArrangeBy keys=[[]]
                      Distinct project=[#0{a}]
                        Project (#0{a})
                          ReadIndex on=t t_a_idx=[*** full scan ***]
                    ArrangeBy keys=[[]]
                      Project (#0{a})
                        ReadStorage materialize.public.mv
  Return
    Project (#0{a}, #1{b})
      Join on=(#1{b} = #2{b}) type=differential
        ArrangeBy keys=[[#1{b}]]
          Get l0
        ArrangeBy keys=[[#0{b}]]
          Distinct project=[#0{b}]
            Project (#0{b})
              Filter (#0{b} > #1{b})
                CrossJoin type=differential
                  ArrangeBy keys=[[]]
                    Distinct project=[#0{b}]
                      Project (#1{b})
                        Get l0
                  ArrangeBy keys=[[]]
                    Project (#1{b})
                      ReadStorage materialize.public.mv

Source materialize.public.mv

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***, differential join)

Target cluster: quickstart

EOF

# Test SELECT subqueries.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT (SELECT iv.a FROM iv WHERE iv.b = t.b LIMIT 1), (SELECT mv.a FROM mv WHERE mv.b = t.b LIMIT 1) FROM t
----
Explained Query:
  With
    cte l0 =
      Project (#1{b})
        ReadIndex on=t t_a_idx=[*** full scan ***]
    cte l1 =
      Distinct project=[#0{b}]
        Get l0
    cte l2 =
      ArrangeBy keys=[[#0{b}]]
        Get l1
    cte l3 =
      TopK group_by=[#0{b}] limit=1
        Project (#0{b}, #1{a})
          Filter (#0{b}) IS NOT NULL
            Join on=(#0{b} = #2{b}) type=differential
              Get l2
              ArrangeBy keys=[[#1{b}]]
                ReadIndex on=iv iv_b_idx=[differential join]
    cte l4 =
      TopK group_by=[#0{b}] limit=1
        Project (#0{b}, #1{a})
          Join on=(#0{b} = #2{b}) type=differential
            Get l2
            ArrangeBy keys=[[#1{b}]]
              Filter (#1{b}) IS NOT NULL
                ReadStorage materialize.public.mv
  Return
    Project (#2{a}, #4{a})
      Join on=(#0{b} = #1{b} = #3{b}) type=delta
        ArrangeBy keys=[[#0{b}]]
          Get l0
        ArrangeBy keys=[[#0{b}]]
          Union
            Get l3
            Map (null)
              Union
                Negate
                  Project (#0{b})
                    Get l3
                Get l1
        ArrangeBy keys=[[#0{b}]]
          Union
            Get l4
            Map (null)
              Union
                Negate
                  Project (#0{b})
                    Get l4
                Get l1

Source materialize.public.mv
  filter=((#1{b}) IS NOT NULL)

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)
  - materialize.public.iv_b_idx (differential join)

Target cluster: quickstart

EOF

# Test outer joins (ON syntax).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE 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
----
Explained Query:
  With
    cte l0 =
      Filter (#1{b}) IS NOT NULL
        ReadIndex on=t t_a_idx=[*** full scan ***]
    cte l1 =
      ArrangeBy keys=[[#1{b}]]
        Get l0
    cte l2 =
      ArrangeBy keys=[[#0{b}]]
        Project (#1{b})
          Get l0
    cte l3 =
      Project (#0{a}..=#2{a})
        Join on=(#1{b} = #3{b} = #4{b}) type=delta
          Get l1
          Get l1
          Get l2
  Return
    Union
      Map (null, null)
        Union
          Negate
            Project ()
              Join on=(#0{b} = #1{b}) type=differential
                Get l2
                ArrangeBy keys=[[#0{b}]]
                  Distinct project=[#0{b}]
                    Project (#1{b})
                      Get l3
          Project ()
            ReadIndex on=t t_a_idx=[*** full scan ***]
      Project (#0{a}, #2{a})
        Get l3

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test a single CTE.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE 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
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]]
        Project (#2)
          Map ((#0{a} * #1{b}))
            ReadIndex on=t t_a_idx=[*** full scan ***]
  Return
    Project (#2)
      Map ((#0{v} + #1{v}))
        CrossJoin type=differential
          Get l0
          Get l0

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test EXPLAIN INDEX for an indexed source
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
INDEX t_a_idx
----
materialize.public.t_a_idx:
  ArrangeBy keys=[[#0{a}]]
    ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

# Test EXPLAIN INDEX for an indexed view (first index)
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
INDEX iv_a_idx;
----
materialize.public.iv_a_idx:
  ArrangeBy keys=[[#0{a}]]
    ReadGlobalFromSameDataflow materialize.public.iv

materialize.public.iv:
  Filter (#0{a}) IS NOT NULL
    ReadIndex on=t t_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test EXPLAIN INDEX for an indexed view (based on a prior index)
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
INDEX iv_b_idx;
----
materialize.public.iv_b_idx:
  ArrangeBy keys=[[#1{b}]]
    ReadIndex on=iv iv_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.iv_a_idx (*** full scan ***, index export)

Target cluster: quickstart

EOF

# Test EXPLAIN INDEX for an indexed view where the index is exactly the same as a prior index.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
INDEX iv_b_idx_2;
----
materialize.public.iv_b_idx_2:
  ArrangeBy keys=[[#1{b}]]
    ReadIndex on=iv iv_b_idx=[plan root (no new arrangement)]

Used Indexes:
  - materialize.public.iv_b_idx (plan root (no new arrangement), index export)

Target cluster: quickstart

Notices:
  - Notice: Index materialize.public.iv_b_idx_2 is identical to materialize.public.iv_b_idx, which is also defined on iv(b).
    Hint: Please drop all indexes except the first index created on iv(b) and recreate all dependent objects.

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 OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE 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;
----
Explained Query:
  With
    cte l0 =
      Project (#0{a})
        ReadIndex on=t t_a_idx=[*** full scan ***]
    cte l1 =
      ArrangeBy keys=[[]]
        Get l0
    cte l2 =
      Project (#0{a}, #1{b}, #3{max})
        Filter (#0{a} != #3{max})
          Join on=(#0{a} = #2{a}) type=differential
            ArrangeBy keys=[[#0{a}]]
              ReadIndex on=t t_a_idx=[differential join]
            ArrangeBy keys=[[#0{a}]]
              Reduce group_by=[#0{a}] aggregates=[max((#0{a} * #1{a}))]
                CrossJoin type=differential
                  ArrangeBy keys=[[]]
                    Distinct project=[#0{a}]
                      Get l0
                  Get l1
  Return
    Project (#0{a}..=#2{max}, #4{max})
      Filter (#0{a} != #4{max})
        Join on=(#0{a} = #3{a}) type=differential
          ArrangeBy keys=[[#0{a}]]
            Get l2
          ArrangeBy keys=[[#0{a}]]
            Reduce group_by=[#0{a}] aggregates=[max((#0{a} * #1{a}))]
              CrossJoin type=differential
                ArrangeBy keys=[[]]
                  Distinct project=[#0{a}]
                    Project (#0{a})
                      Get l2
                Get l1

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***, differential join)

Target cluster: quickstart

EOF

# Test cross join.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT t1.a, t2.a FROM t as t1, t as t2
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[]]
        Project (#0{a})
          ReadIndex on=t t_a_idx=[*** full scan ***]
  Return
    CrossJoin type=differential
      Get l0
      Get l0

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test cyclic join.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE 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
----
Explained Query:
  With
    cte l0 =
      Filter (#1{b}) IS NOT NULL
        ReadIndex on=t t_a_idx=[*** full scan ***]
    cte l1 =
      ArrangeBy keys=[[#1{b}]]
        Get l0
  Return
    Project (#0{a}, #2{a})
      Join on=(#1{b} = #3{b} = #4{b}) type=delta
        Get l1
        Get l1
        ArrangeBy keys=[[#0{b}]]
          Project (#1{b})
            Get l0

Used Indexes:
  - materialize.public.t_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Create indexes required for differential join tests

statement ok
CREATE INDEX u_c_idx ON U(c);

statement ok
CREATE INDEX u_d_idx ON U(d);

statement ok
CREATE INDEX v_e_idx ON V(e);

# Temporarily switch off enable_eager_delta_joins
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_eager_delta_joins = false
----
COMPLETE 0

# Test a differential join.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT a, b, c, d, e, f
FROM t, u, v
WHERE a = c and d = e and b = f
----
Explained Query:
  Project (#0{a}, #1{b}, #0{a}, #3{d}, #3{d}, #1{b})
    Filter (#0{a}) IS NOT NULL
      Join on=(#0{a} = #2{c} AND #1{b} = #5{f} AND #3{d} = #4{e}) type=differential
        ArrangeBy keys=[[#0{a}]]
          ReadIndex on=t t_a_idx=[differential join]
        ArrangeBy keys=[[#0{c}]]
          ReadIndex on=u u_c_idx=[differential join]
        ArrangeBy keys=[[#0{e}, #1{f}]]
          Filter (#0{e}) IS NOT NULL AND (#1{f}) IS NOT NULL
            ReadIndex on=v v_e_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (differential join)
  - materialize.public.u_c_idx (differential join)
  - materialize.public.v_e_idx (*** full scan ***)

Target cluster: quickstart

EOF

# Test a differential join WITH(join implementations).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, join implementations) AS VERBOSE TEXT FOR
SELECT a, b, c, d, e, f
FROM t, u, v
WHERE a = c and d = e and b = f
----
Explained Query:
  Project (#0{a}, #1{b}, #0{a}, #3{d}, #3{d}, #1{b})
    Filter (#0{a}) IS NOT NULL
      Join on=(#0{a} = #2{c} AND #1{b} = #5{f} AND #3{d} = #4{e}) type=differential
        implementation
          %0:t[#0{a}]KA » %1:u[#0{c}]KA » %2:v[#0{e}, #1{f}]KK
        ArrangeBy keys=[[#0{a}]]
          ReadIndex on=t t_a_idx=[differential join]
        ArrangeBy keys=[[#0{c}]]
          ReadIndex on=u u_c_idx=[differential join]
        ArrangeBy keys=[[#0{e}, #1{f}]]
          Filter (#0{e}) IS NOT NULL AND (#1{f}) IS NOT NULL
            ReadIndex on=v v_e_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx (differential join)
  - materialize.public.u_c_idx (differential join)
  - materialize.public.v_e_idx (*** full scan ***)

Target cluster: quickstart

EOF

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_eager_delta_joins = true
----
COMPLETE 0

# Create indexes required for delta join tests

statement ok
CREATE INDEX t_b_idx ON T(b);

# Test a delta join without WITH(join implementations).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT a, b, c, d, e, f
FROM t, u, v
WHERE b = c and d = e
----
Explained Query:
  Project (#0{a}, #1{b}, #1{b}, #3{d}, #3{d}, #5{f})
    Filter (#1{b}) IS NOT NULL AND (#3{d}) IS NOT NULL
      Join on=(#1{b} = #2{c} AND #3{d} = #4{e}) type=delta
        ArrangeBy keys=[[#1{b}]]
          ReadIndex on=t t_b_idx=[delta join 1st input (full scan)]
        ArrangeBy keys=[[#0{c}], [#1{d}]]
          ReadIndex on=u u_c_idx=[delta join lookup] u_d_idx=[delta join lookup]
        ArrangeBy keys=[[#0{e}]]
          ReadIndex on=v v_e_idx=[delta join lookup]

Used Indexes:
  - materialize.public.u_c_idx (delta join lookup)
  - materialize.public.u_d_idx (delta join lookup)
  - materialize.public.v_e_idx (delta join lookup)
  - materialize.public.t_b_idx (delta join 1st input (full scan))

Target cluster: quickstart

EOF

# Test a delta join WITH(join implementations).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
SELECT a, b, c, d, e, f
FROM t, u, v
WHERE b = c and d = e
----
Explained Query:
  Project (#0{a}, #1{b}, #1{b}, #3{d}, #3{d}, #5{f})
    Filter (#1{b}) IS NOT NULL AND (#3{d}) IS NOT NULL
      Join on=(#1{b} = #2{c} AND #3{d} = #4{e}) type=delta
        implementation
          %0:t » %1:u[#0{c}]KA » %2:v[#0{e}]KA
          %1:u » %0:t[#1{b}]KA » %2:v[#0{e}]KA
          %2:v » %1:u[#1{d}]KA » %0:t[#1{b}]KA
        ArrangeBy keys=[[#1{b}]]
          ReadIndex on=t t_b_idx=[delta join 1st input (full scan)]
        ArrangeBy keys=[[#0{c}], [#1{d}]]
          ReadIndex on=u u_c_idx=[delta join lookup] u_d_idx=[delta join lookup]
        ArrangeBy keys=[[#0{e}]]
          ReadIndex on=v v_e_idx=[delta join lookup]

Used Indexes:
  - materialize.public.u_c_idx (delta join lookup)
  - materialize.public.u_d_idx (delta join lookup)
  - materialize.public.v_e_idx (delta join lookup)
  - materialize.public.t_b_idx (delta join 1st input (full scan))

Target cluster: quickstart

EOF

# Test a delta join where the first input has both a full scan and a lookup.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
SELECT a, b, c, d, e, f
FROM t, u, v
WHERE a = c and b = e
----
Explained Query:
  Project (#0{a}, #1{b}, #0{a}, #3{d}, #1{b}, #5{f})
    Filter (#0{a}) IS NOT NULL AND (#1{b}) IS NOT NULL
      Join on=(#0{a} = #2{c} AND #1{b} = #4{e}) type=delta
        implementation
          %0:t » %1:u[#0{c}]KA » %2:v[#0{e}]KA
          %1:u » %0:t[#0{a}]KA » %2:v[#0{e}]KA
          %2:v » %0:t[#1{b}]KA » %1:u[#0{c}]KA
        ArrangeBy keys=[[#0{a}], [#1{b}]]
          ReadIndex on=t t_a_idx=[delta join 1st input (full scan)] t_b_idx=[delta join lookup]
        ArrangeBy keys=[[#0{c}]]
          ReadIndex on=u u_c_idx=[delta join lookup]
        ArrangeBy keys=[[#0{e}]]
          ReadIndex on=v v_e_idx=[delta join lookup]

Used Indexes:
  - materialize.public.t_a_idx (delta join 1st input (full scan))
  - materialize.public.u_c_idx (delta join lookup)
  - materialize.public.v_e_idx (delta join lookup)
  - materialize.public.t_b_idx (delta join lookup)

Target cluster: quickstart

EOF

# Test an IndexedFilter join.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT a, max(b)
FROM t
WHERE a = 0
GROUP BY a
----
Explained Query:
  Project (#1, #0{max_b})
    Map (0)
      Reduce aggregates=[max(#0{b})]
        Project (#1{b})
          ReadIndex on=materialize.public.t t_a_idx=[lookup value=(0)]

Used Indexes:
  - materialize.public.t_a_idx (lookup)

Target cluster: quickstart

EOF

# Create index for IndexedFilter test

statement ok
CREATE INDEX t_a_b_idx ON T(a,b)

# Test an IndexedFilter join WITH(join implementations).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
SELECT a, max(b)
FROM t
WHERE (a = 0 AND b = 1) OR (a = 3 AND b = 4) OR (a = 7 AND b = 8)
GROUP BY a
----
Explained Query:
  Reduce group_by=[#0{a}] aggregates=[max(#1{b})]
    Project (#0{a}, #1{b})
      ReadIndex on=materialize.public.t t_a_b_idx=[lookup values=[(0, 1); (3, 4); (7, 8)]]

Used Indexes:
  - materialize.public.t_a_b_idx (lookup)

Target cluster: quickstart

EOF

# Test an IndexedFilter join on fast path WITH(join implementations).
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(join implementations, humanized expressions) AS VERBOSE TEXT FOR
SELECT *
FROM t
WHERE (a = 0 AND b = 1) OR (a = 3 AND b = 4) OR (a = 7 AND b = 8)
----
Explained Query (fast path):
  Project (#0{a}, #1{b})
    ReadIndex on=materialize.public.t t_a_b_idx=[lookup values=[(0, 1); (3, 4); (7, 8)]]

Used Indexes:
  - materialize.public.t_a_b_idx (lookup)

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

## linear chains is currently disabled for WMR.
statement error not supported
EXPLAIN OPTIMIZED PLAN WITH(linear chains) AS VERBOSE 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;

# Regression test for database-issues#5674: support mz_now() on select from indexed table
# ---

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO false;
----
COMPLETE 0

statement ok
DROP SCHEMA IF EXISTS public CASCADE;

simple conn=mz_system,user=mz_system
ALTER SYSTEM RESET enable_rbac_checks;
----
COMPLETE 0

statement ok
CREATE SCHEMA public;

statement ok
CREATE TABLE t(a TIMESTAMP);

statement ok
CREATE DEFAULT INDEX ON t;

# EXPLAIN output is time-dependent, so we don't want show the output here, just
# assert that the query doesn't fail.
statement ok
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t WHERE a < mz_now();

# Regression test for materialize#19177
# ---

statement ok
DROP SCHEMA IF EXISTS public CASCADE;

statement ok
CREATE SCHEMA public;

statement ok
CREATE TABLE t1(x text);

statement ok
CREATE TABLE t2(x text);

statement ok
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t1, t2 WHERE t1.x || mz_internal.mz_session_id()  = t2.x || mz_internal.mz_session_id();

# Regression test for the join visitation part of materialize#19177
statement ok
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT * FROM t1, t2 WHERE t1.x || mz_now()  = t2.x || mz_now();

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT lag(x, 3, 'default') IGNORE NULLS OVER (ORDER BY x || x)
FROM t1;
----
Explained Query:
  Project (#2)
    Map (record_get[0](#1))
      FlatMap unnest_list(#0{lag})
        Reduce aggregates=[lag[ignore_nulls=true, order_by=[#0{x} asc nulls_last]](row(row(row(#0{x}), row(#0{x}, 3, "default")), (#0{x} || #0{x})))]
          ReadStorage materialize.public.t1

Source materialize.public.t1

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT first_value(x) OVER (ORDER BY x || x ROWS BETWEEN 5 preceding AND CURRENT ROW)
FROM t1;
----
Explained Query:
  Project (#2)
    Map (record_get[0](#1))
      FlatMap unnest_list(#0{first_value})
        Reduce aggregates=[first_value[order_by=[#0{x} asc nulls_last] rows between 5 preceding and current row](row(row(row(#0{x}), #0{x}), (#0{x} || #0{x})))]
          ReadStorage materialize.public.t1

Source materialize.public.t1

Target cluster: quickstart

EOF

## "Used indexes" tests

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

statement ok
CREATE TABLE u (
  c int,
  d int
);

# If two indexes exist on the same table, then "Used indexes" should print the one that we are actually going to use

statement ok
CREATE INDEX u_c ON u(c);

statement ok
CREATE INDEX u_d ON u(d);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT *
FROM t, u
WHERE t.b = u.c;
----
Explained Query:
  Project (#0{a}, #1{b}, #1{b}, #3{d})
    Join on=(#1{b} = #2{c}) type=differential
      ArrangeBy keys=[[#1{b}]]
        Filter (#1{b}) IS NOT NULL
          ReadStorage materialize.public.t
      ArrangeBy keys=[[#0{c}]]
        ReadIndex on=u u_c=[differential join]

Source materialize.public.t
  filter=((#1{b}) IS NOT NULL)

Used Indexes:
  - materialize.public.u_c (differential join)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT *
FROM t, u
WHERE t.b = u.d;
----
Explained Query:
  Project (#0{a}..=#2{c}, #1{b})
    Join on=(#1{b} = #3{d}) type=differential
      ArrangeBy keys=[[#1{b}]]
        Filter (#1{b}) IS NOT NULL
          ReadStorage materialize.public.t
      ArrangeBy keys=[[#1{d}]]
        ReadIndex on=u u_d=[differential join]

Source materialize.public.t
  filter=((#1{b}) IS NOT NULL)

Used Indexes:
  - materialize.public.u_d (differential join)

Target cluster: quickstart

EOF

statement ok
DROP INDEX u_c;

# Let's test the weird situation that two identical indexes exist.

statement ok
CREATE INDEX t_a_idx_1 ON t(a);

statement ok
CREATE INDEX t_a_idx_2 ON t(a);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT *
FROM t, u
WHERE t.a = u.c
----
Explained Query:
  Project (#0{a}, #1{b}, #0{a}, #3{d})
    Join on=(#0{a} = #2{c}) type=differential
      ArrangeBy keys=[[#0{a}]]
        ReadIndex on=t t_a_idx_1=[differential join]
      ArrangeBy keys=[[#0{c}]]
        Filter (#0{c}) IS NOT NULL
          ReadIndex on=u u_d=[*** full scan ***]

Used Indexes:
  - materialize.public.u_d (*** full scan ***)
  - materialize.public.t_a_idx_1 (differential join)

Target cluster: quickstart

EOF

# An index is used two times by the same (self) join. We should show a 1st input and a non-1st input usage.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT *
FROM t AS t1, t AS t2, t AS t3
WHERE t1.a = t2.a AND t2.a = t3.a;
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{a}]]
        ReadIndex on=t t_a_idx_1=[delta join lookup, delta join 1st input (full scan)]
  Return
    Project (#0{a}, #1{b}, #0{a}, #3{b}, #0{a}, #5{b})
      Filter (#0{a}) IS NOT NULL
        Join on=(#0{a} = #2{a} = #4{a}) type=delta
          Get l0
          Get l0
          Get l0

Used Indexes:
  - materialize.public.t_a_idx_1 (delta join lookup, delta join 1st input (full scan))

Target cluster: quickstart

EOF

# An index is used in both a join and a full scan.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
(SELECT t1.a + t2.a AS a, t1.b + t2.b AS b
 FROM t AS t1, t AS t2
 WHERE t1.a = t2.a)
UNION
(SELECT *
 FROM t
 WHERE b > 5)
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#0{a}]]
        ReadIndex on=t t_a_idx_1=[differential join]
  Return
    Distinct project=[#0{a}, #1{b}]
      Union
        Project (#4, #5)
          Filter (#0{a}) IS NOT NULL
            Map ((#0{a} + #0{a}), (#1{b} + #3{b}))
              Join on=(#0{a} = #2{a}) type=differential
                Get l0
                Get l0
        Filter (#1{b} > 5)
          ReadIndex on=t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (*** full scan ***, differential join)

Target cluster: quickstart

EOF

# An index exists that can't be used for the join because of having the wrong key.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
(SELECT t1.a + t2.a AS a, t1.b + t2.b AS b
 FROM t AS t1, t AS t2
 WHERE t1.b = t2.b)
UNION
(SELECT *
 FROM t
 WHERE b > 5)
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#1{b}]]
        Filter (#1{b}) IS NOT NULL
          ReadIndex on=t t_a_idx_1=[*** full scan ***]
  Return
    Distinct project=[#0{a}, #1{b}]
      Union
        Project (#4, #5)
          Map ((#0{a} + #2{a}), (#1{b} + #1{b}))
            Join on=(#1{b} = #3{b}) type=differential
              Get l0
              Get l0
        Filter (#1{b} > 5)
          ReadIndex on=t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (*** full scan ***)

Target cluster: quickstart

EOF

# Similar to the previous test, but exercises the full scan code inside the context loop of the Get case in
# `collect_index_reqs_inner`, where we don't have an index for the requested key.

statement ok
CREATE TABLE t_non_null (
  a int NOT NULL,
  b int NOT NULL
);

statement ok
CREATE INDEX t_non_null_a_idx ON t_non_null(a);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
(SELECT t1.a + t2.a AS a, t1.b + t2.b AS b
 FROM t_non_null AS t1, t_non_null AS t2
 WHERE t1.b = t2.b)
UNION
(SELECT *
 FROM t_non_null
 WHERE b > 5)
----
Explained Query:
  With
    cte l0 =
      ArrangeBy keys=[[#1{b}]]
        ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]
  Return
    Distinct project=[#0{a}, #1{b}]
      Union
        Project (#4, #5)
          Map ((#0{a} + #2{a}), (#1{b} + #1{b}))
            Join on=(#1{b} = #3{b}) type=differential
              Get l0
              Get l0
        Filter (#1{b} > 5)
          ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_non_null_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# This has 1 more full scan than the previous test, because the join needs 2 different arrangements.
# (But we print only one full scan due to deduplication.)
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
(SELECT t1.a + t2.a AS a, t1.b + t2.b AS b
 FROM t_non_null AS t1, t_non_null AS t2
 WHERE t1.b = t2.b + 1)
UNION
(SELECT *
 FROM t_non_null
 WHERE b > 5)
----
Explained Query:
  Distinct project=[#0{a}, #1{b}]
    Union
      Project (#4, #5)
        Map ((#0{a} + #2{a}), (#1{b} + #3{b}))
          Join on=(#1{b} = (#3{b} + 1)) type=differential
            ArrangeBy keys=[[#1{b}]]
              ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]
            ArrangeBy keys=[[(#1{b} + 1)]]
              ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]
      Filter (#1{b} > 5)
        ReadIndex on=t_non_null t_non_null_a_idx=[*** full scan ***]

Used Indexes:
  - materialize.public.t_non_null_a_idx (*** full scan ***)

Target cluster: quickstart

EOF

# An index is used in both a lookup and a full scan.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM t
UNION
SELECT * FROM t WHERE a = 5;
----
Explained Query:
  Distinct project=[#0{a}, #1{b}]
    Union
      ReadIndex on=t t_a_idx_2=[*** full scan ***]
      Project (#0{a}, #1{b})
        ReadIndex on=materialize.public.t t_a_idx_2=[lookup value=(5)]

Used Indexes:
  - materialize.public.t_a_idx_2 (*** full scan ***, lookup)

Target cluster: quickstart

EOF

# Several lookups using different indexes

statement ok
CREATE INDEX t_b_idx ON t(b);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM t
UNION ALL
SELECT * FROM t WHERE b = 7
UNION ALL
SELECT * FROM t WHERE a = 5
UNION ALL
SELECT * FROM u WHERE c = 3
UNION ALL
SELECT * FROM u WHERE d = 1;
----
Explained Query:
  Union
    ReadIndex on=t t_b_idx=[*** full scan ***]
    Project (#0{a}, #1{b})
      ReadIndex on=materialize.public.t t_b_idx=[lookup value=(7)]
    Project (#0{a}, #1{b})
      ReadIndex on=materialize.public.t t_a_idx_2=[lookup value=(5)]
    Filter (#0{c} = 3)
      ReadIndex on=u u_d=[*** full scan ***]
    Project (#0{c}, #1{d})
      ReadIndex on=materialize.public.u u_d=[lookup value=(1)]

Used Indexes:
  - materialize.public.u_d (*** full scan ***, lookup)
  - materialize.public.t_a_idx_2 (lookup)
  - materialize.public.t_b_idx (*** full scan ***, lookup)

Target cluster: quickstart

EOF

# Fast path with a LIMIT and no ORDER BY. This is not a full scan.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT a+b as x
FROM t
WHERE a < 7
LIMIT 3;
----
Explained Query (fast path):
  Finish limit=3 output=[#0]
    Project (#2)
      Filter (#0{a} < 7)
        Map ((#0{a} + #1{b}))
          ReadIndex on=materialize.public.t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (fast path limit)

Target cluster: quickstart

EOF

# Same query without a LIMIT, so full scan
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT a+b as x
FROM t
WHERE a < 7;
----
Explained Query (fast path):
  Project (#2)
    Filter (#0{a} < 7)
      Map ((#0{a} + #1{b}))
        ReadIndex on=materialize.public.t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (*** full scan ***)

Target cluster: quickstart

EOF

# Same query with a LIMIT + ORDER BY, so full scan
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT a+b as x
FROM t
WHERE a < 7
ORDER BY x
LIMIT 3;
----
Explained Query (fast path):
  Finish order_by=[#0 asc nulls_last] limit=3 output=[#0]
    Project (#2)
      Filter (#0{a} < 7)
        Map ((#0{a} + #1{b}))
          ReadIndex on=materialize.public.t t_a_idx_1=[*** full scan ***]

Used Indexes:
  - materialize.public.t_a_idx_1 (*** full scan ***)

Target cluster: quickstart

EOF

# We should choose an index whose key is a unique key (if exists)
statement ok
CREATE TABLE t4(
    a int,
    b int primary key,
    c int
);

statement ok
CREATE INDEX t4_idx_a ON t4(a);

statement ok
CREATE INDEX t4_idx_b ON t4(b);

statement ok
CREATE INDEX t4_idx_c ON t4(c);

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM t4;
----
Explained Query (fast path):
  Project (#1{a}, #0{b}, #2{c})
    ReadIndex on=materialize.public.t4 t4_idx_b=[*** full scan ***]

Used Indexes:
  - materialize.public.t4_idx_b (*** full scan ***)

Target cluster: quickstart

EOF

# EXPLAIN should work even if there are no replicas.
statement ok
CREATE CLUSTER no_replicas (SIZE '1', REPLICATION FACTOR 0);

statement ok
SET CLUSTER = no_replicas;

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT * FROM t4;
----
Explained Query:
  ReadStorage materialize.public.t4

Source materialize.public.t4

Target cluster: no_replicas

EOF

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

statement ok
CREATE TABLE t6(
    a int NOT NULL,
    b int
);

# WITH(EQUIVALENCES)
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(EQUIVALENCES, humanized expressions) AS VERBOSE TEXT FOR
SELECT *
FROM t5, t6
WHERE x = a AND b IN (8,9);
----
Explained Query:
  Project (#0{x}..=#2{z}, #0{x}, #4{b}) // { equivs: "[[#0{x}, #3{x}], [false, (#0{x}) IS NULL, (#1{y}) IS NULL], [true, ((#4{b} = 8) OR (#4{b} = 9))]]" }
    Join on=(#0{x} = #3{a}) type=differential // { equivs: "[[#0{x}, #3{a}], [false, (#0{x}) IS NULL, (#1{y}) IS NULL], [true, ((#4{b} = 8) OR (#4{b} = 9))]]" }
      ArrangeBy keys=[[#0{x}]] // { equivs: "[[false, (#0{x}) IS NULL, (#1{y}) IS NULL]]" }
        Filter (#0{x}) IS NOT NULL // { equivs: "[[false, (#0{x}) IS NULL, (#1{y}) IS NULL]]" }
          ReadStorage materialize.public.t5 // { equivs: "[[false, (#1{y}) IS NULL]]" }
      ArrangeBy keys=[[#0{a}]] // { equivs: "[[false, (#0{a}) IS NULL], [true, ((#1{b} = 8) OR (#1{b} = 9))]]" }
        Filter ((#1{b} = 8) OR (#1{b} = 9)) // { equivs: "[[false, (#0{a}) IS NULL], [true, ((#1{b} = 8) OR (#1{b} = 9))]]" }
          ReadStorage materialize.public.t6 // { equivs: "[[false, (#0{a}) IS NULL]]" }

Source materialize.public.t5
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t6
  filter=(((#1{b} = 8) OR (#1{b} = 9)))

Target cluster: no_replicas

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(EQUIVALENCES, HUMANIZED EXPRESSIONS) AS VERBOSE TEXT FOR
SELECT *
FROM t5, t6
WHERE x = a AND b IN (8,9);
----
Explained Query:
  Project (#0{x}..=#2{z}, #0{x}, #4{b}) // { equivs: "[[#0{x}, #3{x}], [false, (#0{x}) IS NULL, (#1{y}) IS NULL], [true, ((#4{b} = 8) OR (#4{b} = 9))]]" }
    Join on=(#0{x} = #3{a}) type=differential // { equivs: "[[#0{x}, #3{a}], [false, (#0{x}) IS NULL, (#1{y}) IS NULL], [true, ((#4{b} = 8) OR (#4{b} = 9))]]" }
      ArrangeBy keys=[[#0{x}]] // { equivs: "[[false, (#0{x}) IS NULL, (#1{y}) IS NULL]]" }
        Filter (#0{x}) IS NOT NULL // { equivs: "[[false, (#0{x}) IS NULL, (#1{y}) IS NULL]]" }
          ReadStorage materialize.public.t5 // { equivs: "[[false, (#1{y}) IS NULL]]" }
      ArrangeBy keys=[[#0{a}]] // { equivs: "[[false, (#0{a}) IS NULL], [true, ((#1{b} = 8) OR (#1{b} = 9))]]" }
        Filter ((#1{b} = 8) OR (#1{b} = 9)) // { equivs: "[[false, (#0{a}) IS NULL], [true, ((#1{b} = 8) OR (#1{b} = 9))]]" }
          ReadStorage materialize.public.t6 // { equivs: "[[false, (#0{a}) IS NULL]]" }

Source materialize.public.t5
  filter=((#0{x}) IS NOT NULL)
Source materialize.public.t6
  filter=(((#1{b} = 8) OR (#1{b} = 9)))

Target cluster: no_replicas

EOF

# `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 OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT count(*)
FROM t5;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[count(*)]
        Project ()
          ReadStorage materialize.public.t5
  Return
    Union
      Get l0
      Map (0)
        Union
          Negate
            Project ()
              Get l0
          Constant
            - ()

Source materialize.public.t5

Target cluster: no_replicas

EOF

query error DISTINCT \* not supported as function args
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE 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 OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT count(true)
FROM t5;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[count(*)]
        Project ()
          ReadStorage materialize.public.t5
  Return
    Union
      Get l0
      Map (0)
        Union
          Negate
            Project ()
              Get l0
          Constant
            - ()

Source materialize.public.t5

Target cluster: no_replicas

EOF

# But `count(DISTINCT true)` means an entirely different thing, so EXPLAIN shouldn't conflate it with `count(*)`.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT count(DISTINCT true)
FROM t5;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[count(distinct true)]
        Project ()
          ReadStorage materialize.public.t5
  Return
    Union
      Get l0
      Map (0)
        Union
          Negate
            Project ()
              Get l0
          Constant
            - ()

Source materialize.public.t5

Target cluster: no_replicas

EOF

# OFFSET clause in RowSetFinishing
query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR
SELECT a+b
FROM t4
OFFSET 1;
----
Explained Query:
  Finish offset=1 output=[#0]
    Project (#3)
      Map ((#0{a} + #1{b}))
        ReadStorage materialize.public.t4

Source materialize.public.t4

Target cluster: no_replicas

EOF

# OFFSET clause in TopK
query T multiline
EXPLAIN OPTIMIZED PLAN AS VERBOSE TEXT FOR
SELECT a+b, (SELECT a*b FROM t4 OFFSET 1)
FROM t4;
----
Explained Query:
  With
    cte l0 =
      TopK offset=1
        Project (#3)
          Map ((#0{a} * #1{b}))
            ReadStorage materialize.public.t4
    cte l1 =
      Union
        Get l0
        Project (#1)
          FlatMap guard_subquery_size(#0)
            Reduce aggregates=[count(*)]
              Project ()
                Get l0
  Return
    Project (#3, #2)
      Map ((#0{a} + #1{b}))
        CrossJoin type=differential
          ArrangeBy keys=[[]]
            Project (#0, #1)
              ReadStorage materialize.public.t4
          ArrangeBy keys=[[]]
            Union
              Get l1
              Map (null)
                Union
                  Negate
                    Distinct project=[]
                      Project ()
                        Get l1
                  Constant
                    - ()

Source materialize.public.t4

Target cluster: no_replicas

EOF