Materialize/test/sqllogictest/explain/physical_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 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 INDEX t_b_idx ON T(b);

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

statement ok
CREATE INDEX ov_a_idx ON ov(a);

statement ok
CREATE INDEX ov_b_idx ON ov(b);

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

statement ok
CREATE VIEW hierarchical_group_by AS
SELECT
  a,
  MIN(b),
  MAX(DISTINCT b)
FROM t
GROUP BY a

statement ok
CREATE MATERIALIZED VIEW hierarchical_group_by_mv AS
SELECT * FROM hierarchical_group_by

statement ok
CREATE VIEW hierarchical_global AS
SELECT
  MIN(b),
  MAX(DISTINCT b)
FROM t

statement ok
CREATE MATERIALIZED VIEW hierarchical_global_mv AS
SELECT * FROM hierarchical_global

statement ok
CREATE VIEW collated_group_by AS
SELECT
  a,
  COUNT(DISTINCT b),
  STRING_AGG(b::text || '1',  ',') AS b1,
  MIN(b),
  MAX(DISTINCT b),
  SUM(b),
  STRING_AGG(b::text || '2',  ',') AS b2
FROM t
GROUP BY a

statement ok
CREATE MATERIALIZED VIEW collated_group_by_mv AS
SELECT * FROM collated_group_by

statement ok
CREATE VIEW collated_global AS
SELECT
  COUNT(DISTINCT b),
  STRING_AGG(b::text || '1',  ',') AS b1,
  MIN(b),
  MAX(DISTINCT b),
  SUM(b),
  STRING_AGG(b::text || '2',  ',') AS b2
FROM t

statement ok
CREATE MATERIALIZED VIEW collated_global_mv AS
SELECT * FROM collated_global

mode cockroach

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

Target cluster: quickstart

EOF

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

Target cluster: mz_catalog_server

EOF

# FastPathPlan is show instead of PHYSICAL PLAN if present.
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT * FROM t where a = 5
----
Explained Query (fast path):
  Project (#0, #1)
    ReadIndex on=materialize.public.t t_a_idx=[lookup value=(5)]

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

Target cluster: quickstart

EOF

# Test basic linear chains.

# PassArrangements plan (identity transform on an arranged input).
query T multiline
EXPLAIN PHYSICAL PLAN WITH(no fast path) AS VERBOSE TEXT FOR
SELECT * FROM t
----
Explained Query:
  Get::PassArrangements materialize.public.t
    raw=false
    arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
    types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# PassArrangements plan (identity transform on a raw input).
query T multiline
EXPLAIN PHYSICAL PLAN WITH(no fast path) AS VERBOSE TEXT FOR
SELECT * FROM u
----
Explained Query:
  Get::PassArrangements materialize.public.u
    raw=true

Source materialize.public.u

Target cluster: quickstart

EOF

# GetArrangement plan (linear transform of an arranged input).
query T multiline
EXPLAIN PHYSICAL PLAN WITH(no fast path) AS VERBOSE TEXT FOR
SELECT a + b, 1 FROM t
----
Explained Query:
  Get::Arrangement materialize.public.t
    project=(#2, #3)
    map=((#0{a} + #1{b}), 1)
    key=#0{a}
    raw=false
    arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
    types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# GetCollection plan (linear transform of a raw input).
query T multiline
EXPLAIN PHYSICAL PLAN WITH(no fast path) AS VERBOSE TEXT FOR
SELECT c + d, 1 FROM u
----
Explained Query:
  Get::Collection materialize.public.u
    raw=true

Source materialize.public.u
  project=(#2, #3)
  map=((#0{c} + #1{d}), 1)

Target cluster: quickstart

EOF

# TopKBasic plan.
query T multiline
EXPLAIN PHYSICAL PLAN WITH(no fast path) AS VERBOSE TEXT FOR
INDEX ov_a_idx
----
materialize.public.ov_a_idx:
  ArrangeBy
    raw=true
    arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
    types=[integer?, integer?]
    Get::PassArrangements materialize.public.ov
      raw=true

materialize.public.ov:
  TopK::Basic order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5
    ArrangeBy
      input_key=[#0{a}]
      raw=true
      Get::PassArrangements materialize.public.t
        raw=false
        arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
        types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# MonotonicTopK plan.
query T multiline
EXPLAIN PHYSICAL PLAN WITH(no fast path) AS VERBOSE TEXT FOR
SELECT * FROM (SELECT * FROM t ORDER BY b asc, a desc LIMIT 5)
----
Explained Query:
  TopK::MonotonicTopK order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5 must_consolidate
    ArrangeBy
      input_key=[#0{a}]
      raw=true
      Get::PassArrangements materialize.public.t
        raw=false
        arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
        types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Threshold, Union, Distinct, Negate.
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT a FROM t EXCEPT ALL SELECT b FROM mv
----
Explained Query:
  Threshold::Basic ensure_arrangement={ key=[#0], permutation=id, thinning=() }
    ArrangeBy
      raw=false
      arrangements[0]={ key=[#0], permutation=id, thinning=() }
      types=[integer?]
      Union consolidate_output=true
        Get::Arrangement materialize.public.t
          project=(#0)
          key=#0{a}
          raw=false
          arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
          types=[integer?, integer?]
        Negate
          Get::Collection materialize.public.mv
            raw=true

Source materialize.public.mv
  project=(#1)

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

Target cluster: quickstart

EOF

# Test CTEs.
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
WITH cte(x) as (SELECT a FROM t EXCEPT ALL SELECT b FROM mv)
(SELECT x + 1 FROM cte UNION ALL SELECT x - 1 FROM cte)
----
Explained Query:
  With
    cte l0 =
      Threshold::Basic ensure_arrangement={ key=[#0], permutation=id, thinning=() }
        ArrangeBy
          raw=false
          arrangements[0]={ key=[#0], permutation=id, thinning=() }
          types=[integer?]
          Union consolidate_output=true
            Get::Arrangement materialize.public.t
              project=(#0)
              key=#0{a}
              raw=false
              arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
              types=[integer?, integer?]
            Negate
              Get::Collection materialize.public.mv
                raw=true
  Return
    Union
      Get::Arrangement l0
        project=(#1)
        map=((#0{x} + 1))
        key=#0
        raw=false
        arrangements[0]={ key=[#0], permutation=id, thinning=() }
        types=[integer?]
      Get::Arrangement l0
        project=(#1)
        map=((#0{x} - 1))
        key=#0
        raw=false
        arrangements[0]={ key=[#0], permutation=id, thinning=() }
        types=[integer?]

Source materialize.public.mv
  project=(#1)

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

Target cluster: quickstart

EOF

# Test Mfp.
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
WITH cte(x) as (SELECT a FROM t EXCEPT ALL SELECT b FROM mv)
SELECT x * 5 FROM cte WHERE x = 5
----
Explained Query:
  Mfp
    project=(#1)
    map=(25)
    input_key=#0
    Threshold::Basic ensure_arrangement={ key=[#0], permutation=id, thinning=() }
      ArrangeBy
        raw=false
        arrangements[0]={ key=[#0], permutation=id, thinning=() }
        types=[integer]
        Union consolidate_output=true
          Join::Linear
            linear_stage[0]
              closure
                project=(#0)
              lookup={ relation=0, key=[#0{a}] }
              stream={ key=[#0], thinning=() }
            source={ relation=1, key=[#0] }
            Get::PassArrangements materialize.public.t
              raw=false
              arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
              types=[integer?, integer?]
            ArrangeBy
              raw=true
              arrangements[0]={ key=[#0], permutation=id, thinning=() }
              types=[integer]
              Constant
                - (5)
          Negate
            Get::Collection materialize.public.mv
              raw=true

Source materialize.public.mv
  project=(#1)
  filter=((#1{x} = 5))

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

Target cluster: quickstart

EOF

# Test FlatMap.
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT generate_series(a, b) from t
----
Explained Query:
  FlatMap generate_series(#0{a}, #1{b}, 1)
    mfp_after
      project=(#2)
    input_key=#0{a}
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Reduce::Distinct.
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT DISTINCT a, b FROM t
----
Explained Query:
  Reduce::Distinct
    val_plan
      project=()
    key_plan=id
    input_key=#0{a}
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Reduce::Accumulable (with GROUP BY).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  a,
  SUM(b),
  COUNT(DISTINCT b)
FROM t
GROUP BY a
----
Explained Query:
  Reduce::Accumulable
    simple_aggrs[0]=(0, 0, sum(#1{b}))
    distinct_aggrs[0]=(1, 1, count(distinct #1{b}))
    val_plan
      project=(#1, #1)
    key_plan
      project=(#0)
    input_key=#0{a}
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Reduce::Accumulable (global aggregate).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  SUM(b),
  COUNT(DISTINCT b)
FROM t
----
Explained Query:
  With
    cte l0 =
      Reduce::Accumulable
        simple_aggrs[0]=(0, 0, sum(#0{b}))
        distinct_aggrs[0]=(1, 1, count(distinct #0{b}))
        val_plan
          project=(#0, #0)
        key_plan
          project=()
        Get::Arrangement materialize.public.t
          project=(#1)
          key=#0{a}
          raw=false
          arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
          types=[integer?, integer?]
  Return
    Union
      ArrangeBy
        input_key=[]
        raw=true
        Get::PassArrangements l0
          raw=false
          arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
      Mfp
        project=(#0, #1)
        map=(null, 0)
        Union consolidate_output=true
          Negate
            Get::Arrangement l0
              project=()
              key=
              raw=false
              arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
          Constant
            - ()

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

Target cluster: quickstart

EOF

# Test Reduce::Hierarchical (with GROUP BY).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
MATERIALIZED VIEW hierarchical_group_by_mv
----
materialize.public.hierarchical_group_by_mv:
  Reduce::Hierarchical
    aggr_funcs=[min, max]
    skips=[0, 0]
    buckets=[268435456, 16777216, 1048576, 65536, 4096, 256, 16]
    val_plan
      project=(#1, #1)
    key_plan
      project=(#0)
    input_key=#0{a}
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Reduce::Hierarchical (with GROUP BY, one-shot).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT * FROM hierarchical_group_by
----
Explained Query:
  Reduce::Hierarchical
    aggr_funcs=[min, max]
    skips=[0, 0]
    monotonic
    must_consolidate
    val_plan
      project=(#1, #1)
    key_plan
      project=(#0)
    input_key=#0{a}
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Reduce::Hierarchical (global aggregate).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
MATERIALIZED VIEW hierarchical_global_mv
----
materialize.public.hierarchical_global_mv:
  With
    cte l0 =
      Reduce::Hierarchical
        aggr_funcs=[min, max]
        skips=[0, 0]
        buckets=[268435456, 16777216, 1048576, 65536, 4096, 256, 16]
        val_plan
          project=(#0, #0)
        key_plan
          project=()
        Get::Arrangement materialize.public.t
          project=(#1)
          key=#0{a}
          raw=false
          arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
          types=[integer?, integer?]
  Return
    Union
      ArrangeBy
        input_key=[]
        raw=true
        Get::PassArrangements l0
          raw=false
          arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
      Mfp
        project=(#0, #1)
        map=(null, null)
        Union consolidate_output=true
          Negate
            Get::Arrangement l0
              project=()
              key=
              raw=false
              arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
          Constant
            - ()

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

Target cluster: quickstart

EOF

# Test Reduce::Hierarchical (global aggregate, one-shot).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT * FROM hierarchical_global
----
Explained Query:
  With
    cte l0 =
      Reduce::Hierarchical
        aggr_funcs=[min, max]
        skips=[0, 0]
        monotonic
        must_consolidate
        val_plan
          project=(#0, #0)
        key_plan
          project=()
        Get::Arrangement materialize.public.t
          project=(#1)
          key=#0{a}
          raw=false
          arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
          types=[integer?, integer?]
  Return
    Union
      ArrangeBy
        input_key=[]
        raw=true
        Get::PassArrangements l0
          raw=false
          arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
      Mfp
        project=(#0, #1)
        map=(null, null)
        Union consolidate_output=true
          Negate
            Get::Arrangement l0
              project=()
              key=
              raw=false
              arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
          Constant
            - ()

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

Target cluster: quickstart

EOF

# Test Reduce::Basic (with GROUP BY).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  a,
  STRING_AGG(b::text || '1',  ','),
  STRING_AGG(b::text || '2',  ',')
FROM t
GROUP BY a
----
Explained Query:
  Reduce::Basic
    aggrs[0]=(0, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || "1"), ","))))
    aggrs[1]=(1, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || "2"), ","))))
    val_plan
      project=(#3, #4)
      map=(integer_to_text(#1{b}), row(row((#2 || "1"), ",")), row(row((#2 || "2"), ",")))
    key_plan
      project=(#0)
    input_key=#0{a}
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Reduce::Basic (global aggregate).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  STRING_AGG(b::text || '1',  ','),
  STRING_AGG(b::text || '2',  ',')
FROM t
----
Explained Query:
  With
    cte l0 =
      Reduce::Basic
        aggrs[0]=(0, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || "1"), ","))))
        aggrs[1]=(1, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || "2"), ","))))
        val_plan
          project=(#2, #3)
          map=(integer_to_text(#0{b}), row(row((#1 || "1"), ",")), row(row((#1 || "2"), ",")))
        key_plan
          project=()
        Get::Arrangement materialize.public.t
          project=(#1)
          key=#0{a}
          raw=false
          arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
          types=[integer?, integer?]
  Return
    Union
      ArrangeBy
        input_key=[]
        raw=true
        Get::PassArrangements l0
          raw=false
          arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
      Mfp
        project=(#0, #1)
        map=(null, null)
        Union consolidate_output=true
          Negate
            Get::Arrangement l0
              project=()
              key=
              raw=false
              arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
          Constant
            - ()

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

Target cluster: quickstart

EOF

# Test Reduce::Collated (with GROUP BY).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
MATERIALIZED VIEW collated_group_by_mv
----
materialize.public.collated_group_by_mv:
  Reduce::Collation
    aggregate_types=[a, b, h, h, a, b]
    accumulable
      simple_aggrs[0]=(1, 4, sum(#1{b}))
      distinct_aggrs[0]=(0, 0, count(distinct #1{b}))
    hierarchical
      aggr_funcs=[min, max]
      skips=[2, 0]
      buckets=[268435456, 16777216, 1048576, 65536, 4096, 256, 16]
    basic
      aggrs[0]=(1, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || "1"), ","))))
      aggrs[1]=(5, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || "2"), ","))))
    val_plan
      project=(#1, #3, #1, #1, #1, #4)
      map=(integer_to_text(#1{b}), row(row((#2 || "1"), ",")), row(row((#2 || "2"), ",")))
    key_plan
      project=(#0)
    input_key=#0{a}
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Reduce::Collated (with GROUP BY, one-shot).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT * FROM collated_group_by
----
Explained Query:
  Reduce::Collation
    aggregate_types=[a, b, h, h, a, b]
    accumulable
      simple_aggrs[0]=(1, 4, sum(#1{b}))
      distinct_aggrs[0]=(0, 0, count(distinct #1{b}))
    hierarchical
      aggr_funcs=[min, max]
      skips=[2, 0]
      monotonic
      must_consolidate
    basic
      aggrs[0]=(1, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || "1"), ","))))
      aggrs[1]=(5, string_agg[order_by=[]](row(row((integer_to_text(#1{b}) || "2"), ","))))
    val_plan
      project=(#1, #3, #1, #1, #1, #4)
      map=(integer_to_text(#1{b}), row(row((#2 || "1"), ",")), row(row((#2 || "2"), ",")))
    key_plan
      project=(#0)
    input_key=#0{a}
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Reduce::Collated (global aggregate).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
MATERIALIZED VIEW collated_global_mv
----
materialize.public.collated_global_mv:
  With
    cte l0 =
      Reduce::Collation
        aggregate_types=[a, b, h, h, a, b]
        accumulable
          simple_aggrs[0]=(1, 4, sum(#0{b}))
          distinct_aggrs[0]=(0, 0, count(distinct #0{b}))
        hierarchical
          aggr_funcs=[min, max]
          skips=[2, 0]
          buckets=[268435456, 16777216, 1048576, 65536, 4096, 256, 16]
        basic
          aggrs[0]=(1, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || "1"), ","))))
          aggrs[1]=(5, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || "2"), ","))))
        val_plan
          project=(#0, #2, #0, #0, #0, #3)
          map=(integer_to_text(#0{b}), row(row((#1 || "1"), ",")), row(row((#1 || "2"), ",")))
        key_plan
          project=()
        Get::Arrangement materialize.public.t
          project=(#1)
          key=#0{a}
          raw=false
          arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
          types=[integer?, integer?]
  Return
    Union
      ArrangeBy
        input_key=[]
        raw=true
        Get::PassArrangements l0
          raw=false
          arrangements[0]={ key=[], permutation=id, thinning=(#0..=#5) }
      Mfp
        project=(#0..=#5)
        map=(0, null, null, null, null, null)
        Union consolidate_output=true
          Negate
            Get::Arrangement l0
              project=()
              key=
              raw=false
              arrangements[0]={ key=[], permutation=id, thinning=(#0..=#5) }
          Constant
            - ()

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

Target cluster: quickstart

EOF

# Test Reduce::Collated (global aggregate, one-shot).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT * FROM collated_global
----
Explained Query:
  With
    cte l0 =
      Reduce::Collation
        aggregate_types=[a, b, h, h, a, b]
        accumulable
          simple_aggrs[0]=(1, 4, sum(#0{b}))
          distinct_aggrs[0]=(0, 0, count(distinct #0{b}))
        hierarchical
          aggr_funcs=[min, max]
          skips=[2, 0]
          monotonic
          must_consolidate
        basic
          aggrs[0]=(1, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || "1"), ","))))
          aggrs[1]=(5, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || "2"), ","))))
        val_plan
          project=(#0, #2, #0, #0, #0, #3)
          map=(integer_to_text(#0{b}), row(row((#1 || "1"), ",")), row(row((#1 || "2"), ",")))
        key_plan
          project=()
        Get::Arrangement materialize.public.t
          project=(#1)
          key=#0{a}
          raw=false
          arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
          types=[integer?, integer?]
  Return
    Union
      ArrangeBy
        input_key=[]
        raw=true
        Get::PassArrangements l0
          raw=false
          arrangements[0]={ key=[], permutation=id, thinning=(#0..=#5) }
      Mfp
        project=(#0..=#5)
        map=(0, null, null, null, null, null)
        Union consolidate_output=true
          Negate
            Get::Arrangement l0
              project=()
              key=
              raw=false
              arrangements[0]={ key=[], permutation=id, thinning=(#0..=#5) }
          Constant
            - ()

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

Target cluster: quickstart

EOF



# Test EXPLAIN INDEX for an indexed source
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
INDEX t_a_idx
----
materialize.public.t_a_idx:
  ArrangeBy
    raw=true
    arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
    types=[integer?, integer?]
    Get::PassArrangements materialize.public.t
      raw=true

Source materialize.public.t

Target cluster: quickstart

EOF

# Test EXPLAIN INDEX for an indexed view (first index)
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
INDEX ov_a_idx;
----
materialize.public.ov_a_idx:
  ArrangeBy
    raw=true
    arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
    types=[integer?, integer?]
    Get::PassArrangements materialize.public.ov
      raw=true

materialize.public.ov:
  TopK::Basic order_by=[#1 asc nulls_last, #0 desc nulls_first] limit=5
    ArrangeBy
      input_key=[#0{a}]
      raw=true
      Get::PassArrangements materialize.public.t
        raw=false
        arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
        types=[integer?, integer?]

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 PHYSICAL PLAN AS VERBOSE TEXT FOR
INDEX ov_b_idx;
----
materialize.public.ov_b_idx:
  ArrangeBy
    input_key=[#0{a}]
    raw=false
    arrangements[0]={ key=[#1{b}], permutation={#0: #1, #1: #0}, thinning=(#0) }
    types=[integer?, integer?]
    Get::PassArrangements materialize.public.ov
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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

Target cluster: quickstart

EOF


# Test Join::Differential (acyclic).
query T multiline
EXPLAIN PHYSICAL PLAN WITH(non negative) AS VERBOSE TEXT FOR
SELECT b + d, c + e, a + e
FROM t, u, v
WHERE a = c AND d = e AND b + d > 42
----
Explained Query:
  Join::Delta
    plan_path[0]
      final_closure
        project=(#0, #1, #1)
      delta_stage[1]
        closure
          project=(#1, #2)
        lookup={ relation=2, key=[#0{e}] }
        stream={ key=[#0{d}], thinning=(#1, #2) }
      delta_stage[0]
        closure
          project=(#2..=#4)
          filter=((#3 > 42))
          map=((#1{b} + #2{d}), (#0{a} + #2{d}))
        lookup={ relation=1, key=[#0{c}] }
        stream={ key=[#0{a}], thinning=(#1) }
      source={ relation=0, key=[#0{a}] }
    plan_path[1]
      final_closure
        project=(#0, #1, #1)
      delta_stage[1]
        closure
          project=(#1, #2)
        lookup={ relation=2, key=[#0{e}] }
        stream={ key=[#0{d}], thinning=(#1, #2) }
      delta_stage[0]
        closure
          project=(#1, #3, #4)
          filter=((#3 > 42))
          map=((#2{b} + #1{d}), (#0{a} + #1{d}))
        lookup={ relation=0, key=[#0{a}] }
        stream={ key=[#0{c}], thinning=(#1) }
      source={ relation=1, key=[#0{c}] }
    plan_path[2]
      final_closure
        project=(#0, #1, #1)
      delta_stage[1]
        closure
          project=(#3, #4)
          filter=((#3 > 42))
          map=((#2{b} + #1{d}), (#0{a} + #1{d}))
        lookup={ relation=0, key=[#0{a}] }
        stream={ key=[#0{c}], thinning=(#1) }
      delta_stage[0]
        closure
          project=(#1, #0)
        lookup={ relation=1, key=[#1{d}] }
        stream={ key=[#0{e}], thinning=() }
      source={ relation=2, key=[#0{e}] }
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]
    ArrangeBy
      raw=true
      arrangements[0]={ key=[#0{c}], permutation=id, thinning=(#1) }
      arrangements[1]={ key=[#1{d}], permutation={#0: #1, #1: #0}, thinning=(#0) }
      types=[integer, integer]
      Get::Collection materialize.public.u
        raw=true
    ArrangeBy
      raw=true
      arrangements[0]={ key=[#0{e}], permutation=id, thinning=() }
      types=[integer]
      Get::Collection materialize.public.v
        raw=true

Source materialize.public.u
  filter=((#0{c}) IS NOT NULL AND (#1{d}) IS NOT NULL)
Source materialize.public.v
  project=(#0)
  filter=((#0{e}) IS NOT NULL)

Used Indexes:
  - materialize.public.t_a_idx (delta join 1st input (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);

# Test Join::Differential (cyclic).
query T multiline
EXPLAIN PHYSICAL PLAN WITH(non negative) AS VERBOSE TEXT FOR
SELECT a, b, c, d, e, f
FROM t, u, v
WHERE a = c AND d = e AND f = a
----
Explained Query:
  Join::Delta
    plan_path[0]
      final_closure
        project=(#0, #1, #0, #2, #2, #0)
      delta_stage[1]
        closure
          project=(#1, #2, #0)
        lookup={ relation=2, key=[#0{e}, #1{f}] }
        stream={ key=[#2{d}, #0{a}], thinning=(#1) }
      delta_stage[0]
        lookup={ relation=1, key=[#0{c}] }
        stream={ key=[#0{a}], thinning=(#1) }
      source={ relation=0, key=[#0{a}] }
    plan_path[1]
      final_closure
        project=(#0, #1, #0, #2, #2, #0)
      delta_stage[1]
        closure
          project=(#0, #2, #1)
        lookup={ relation=0, key=[#0{a}] }
        stream={ key=[#0{c}], thinning=(#1) }
      delta_stage[0]
        closure
          project=(#1, #0)
        lookup={ relation=2, key=[#0{e}, #1{f}] }
        stream={ key=[#1{d}, #0{c}], thinning=() }
      source={ relation=1, key=[#0{c}] }
    plan_path[2]
      final_closure
        project=(#0, #1, #0, #2, #2, #0)
      delta_stage[1]
        closure
          project=(#0, #2, #1)
        lookup={ relation=0, key=[#0{a}] }
        stream={ key=[#1{f}], thinning=(#0) }
      delta_stage[0]
        closure
          project=(#1, #0)
        lookup={ relation=1, key=[#0{c}, #1{d}] }
        stream={ key=[#1{f}, #0{e}], thinning=() }
      source={ relation=2, key=[#0{e}, #1{f}] }
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]
    ArrangeBy
      raw=true
      arrangements[0]={ key=[#0{c}], permutation=id, thinning=(#1) }
      arrangements[1]={ key=[#0{c}, #1{d}], permutation=id, thinning=() }
      types=[integer, integer]
      Get::Arrangement materialize.public.u
        filter=((#0{c}) IS NOT NULL AND (#1{d}) IS NOT NULL)
        key=#0{c}
        raw=false
        arrangements[0]={ key=[#0{c}], permutation=id, thinning=(#1) }
        types=[integer?, integer?]
    ArrangeBy
      raw=true
      arrangements[0]={ key=[#0{e}, #1{f}], permutation=id, thinning=() }
      types=[integer, integer]
      Get::Arrangement materialize.public.v
        filter=((#0{e}) IS NOT NULL AND (#1{f}) IS NOT NULL)
        key=#0{e}
        raw=false
        arrangements[0]={ key=[#0{e}], permutation=id, thinning=(#1) }
        types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test Join::Delta (star).
query T multiline
EXPLAIN PHYSICAL PLAN WITH(non negative) AS VERBOSE TEXT FOR
SELECT a, b, c, d, e, f
FROM t, u, v
WHERE a = c and a = e
----
Explained Query:
  Join::Delta
    plan_path[0]
      final_closure
        project=(#0, #1, #0, #2, #0, #3)
      delta_stage[1]
        lookup={ relation=2, key=[#0{e}] }
        stream={ key=[#0{a}], thinning=(#1, #2) }
      delta_stage[0]
        lookup={ relation=1, key=[#0{c}] }
        stream={ key=[#0{a}], thinning=(#1) }
      initial_closure
        filter=((#0{a}) IS NOT NULL)
      source={ relation=0, key=[#0{a}] }
    plan_path[1]
      final_closure
        project=(#0, #1, #0, #2, #0, #3)
      delta_stage[1]
        closure
          project=(#1..=#4)
        lookup={ relation=2, key=[#0{e}] }
        stream={ key=[#2{c}], thinning=(#0, #1, #3) }
      delta_stage[0]
        closure
          project=(#0, #2, #0, #1)
          filter=((#0{a}) IS NOT NULL)
        lookup={ relation=0, key=[#0{a}] }
        stream={ key=[#0{c}], thinning=(#1) }
      source={ relation=1, key=[#0{c}] }
    plan_path[2]
      final_closure
        project=(#0, #1, #0, #2, #0, #3)
      delta_stage[1]
        closure
          project=(#1, #2, #4, #3)
        lookup={ relation=1, key=[#0{c}] }
        stream={ key=[#2{e}], thinning=(#0, #1, #3) }
      delta_stage[0]
        closure
          project=(#0, #2, #0, #1)
          filter=((#0{a}) IS NOT NULL)
        lookup={ relation=0, key=[#0{a}] }
        stream={ key=[#0{e}], thinning=(#1) }
      source={ relation=2, key=[#0{e}] }
    Get::PassArrangements materialize.public.t
      raw=false
      arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]
    Get::PassArrangements materialize.public.u
      raw=false
      arrangements[0]={ key=[#0{c}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]
    Get::PassArrangements materialize.public.v
      raw=false
      arrangements[0]={ key=[#0{e}], permutation=id, thinning=(#1) }
      types=[integer?, integer?]

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)

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

# Test `LetRec` printing, with and without RECURSION LIMIT
# With materialize#27389 this stopped testing a thing; see issue database-issues#8294.
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT * FROM (
    WITH MUTUALLY RECURSIVE (RECURSION LIMIT 5)
        foo (a int8) AS (SELECT DISTINCT a FROM foo)
    SELECT * FROM foo
)
UNION ALL
SELECT * FROM (
    WITH MUTUALLY RECURSIVE
        bar (a int8) AS (SELECT DISTINCT a - 2 FROM bar)
    SELECT * FROM bar
);
----
Explained Query (fast path):
  Constant <empty>

Target cluster: mz_catalog_server

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT lead(b, 3, -5) IGNORE NULLS OVER () as l
FROM t;
----
Explained Query:
  Mfp
    project=(#1)
    map=(record_get[0](#0))
    input_key=
    Reduce::Basic
      aggr=(0, lead[ignore_nulls=true, order_by=[]](row(row(row(#0, #1), row(#1{b}, 3, -5)))), fused_unnest_list=true)
      val_plan
        project=(#2)
        map=(row(row(row(#0, #1), row(#1{b}, 3, -5))))
      key_plan
        project=()
      input_key=#0{a}
      Get::PassArrangements materialize.public.t
        raw=false
        arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
        types=[integer?, integer?]

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT lag(b, 3, -5) IGNORE NULLS OVER (PARTITION BY b, a ORDER BY b+8, a-7) as l
FROM t;
----
Explained Query:
  Mfp
    project=(#3)
    map=(record_get[0](#2))
    input_key=#0, #1
    Reduce::Basic
      aggr=(0, lag[ignore_nulls=true, order_by=[#0 asc nulls_last, #1 asc nulls_last]](row(row(row(#0, #1), row(#1{b}, 3, -5)), (#1{b} + 8), (#0{a} - 7))), fused_unnest_list=true)
      val_plan
        project=(#2)
        map=(row(row(row(#0, #1), row(#1{b}, 3, -5)), (#1{b} + 8), (#0{a} - 7)))
      key_plan
        project=(#1, #0)
      input_key=#0{a}
      Get::PassArrangements materialize.public.t
        raw=false
        arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
        types=[integer?, integer?]

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

Target cluster: quickstart

EOF

# Test annotation of node IDs.
query T multiline
EXPLAIN PHYSICAL PLAN WITH (node identifiers) AS VERBOSE TEXT FOR
WITH cte(x) as (SELECT a FROM t EXCEPT ALL SELECT b FROM mv)
SELECT x * 5 FROM cte WHERE x = 5
----
Explained Query:
  Mfp // { node_id: LirId(10) }
    project=(#1)
    map=(25)
    input_key=#0
    Threshold::Basic ensure_arrangement={ key=[#0], permutation=id, thinning=() } // { node_id: LirId(9) }
      ArrangeBy // { node_id: LirId(8) }
        raw=false
        arrangements[0]={ key=[#0], permutation=id, thinning=() }
        types=[integer]
        Union consolidate_output=true // { node_id: LirId(7) }
          Join::Linear // { node_id: LirId(4) }
            linear_stage[0]
              closure
                project=(#0)
              lookup={ relation=0, key=[#0{a}] }
              stream={ key=[#0], thinning=() }
            source={ relation=1, key=[#0] }
            Get::PassArrangements materialize.public.t // { node_id: LirId(1) }
              raw=false
              arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
              types=[integer?, integer?]
            ArrangeBy // { node_id: LirId(3) }
              raw=true
              arrangements[0]={ key=[#0], permutation=id, thinning=() }
              types=[integer]
              Constant // { node_id: LirId(2) }
                - (5)
          Negate // { node_id: LirId(6) }
            Get::Collection materialize.public.mv // { node_id: LirId(5) }
              raw=true

Source materialize.public.mv
  project=(#1)
  filter=((#1{x} = 5))

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

Target cluster: quickstart

EOF

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

# Test Reduce::Basic (global aggregate).
query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR
SELECT
  STRING_AGG(b::text || '1',  ','),
  STRING_AGG(b::text || '2',  ',')
FROM t
----
Explained Query:
  With
    cte l0 =
      Reduce::Basic
        aggrs[0]=(0, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || "1"), ","))))
        aggrs[1]=(1, string_agg[order_by=[]](row(row((integer_to_text(#0{b}) || "2"), ","))))
        val_plan
          project=(#2, #3)
          map=(integer_to_text(#0{b}), row(row((#1 || "1"), ",")), row(row((#1 || "2"), ",")))
        key_plan
          project=()
        Get::Arrangement materialize.public.t
          project=(#1)
          key=#0{a}
          raw=false
          arrangements[0]={ key=[#0{a}], permutation=id, thinning=(#1) }
          types=[integer?, integer?]
  Return
    Union
      ArrangeBy
        input_key=[]
        raw=true
        Get::PassArrangements l0
          raw=false
          arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
      Mfp
        project=(#0, #1)
        map=(null, null)
        Union consolidate_output=true
          Negate
            Get::Arrangement l0
              project=()
              key=
              raw=false
              arrangements[0]={ key=[], permutation=id, thinning=(#0, #1) }
          Constant
            - ()

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

Target cluster: quickstart

EOF

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