Materialize/test/sqllogictest/explain/physical_plan_aggregates.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

statement ok
CREATE TABLE t (
  a INT NOT NULL,
  b TEXT NOT NULL,
  c TEXT
);

statement ok
INSERT INTO t VALUES (1, '10', 'x'), (2, '20', NULL), (3, '30', NULL), (4, '40', 'x'), (5, '50a', 'x'), (5, '50b', 'y'), (5, '50c', 'z');

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b) FROM t GROUP BY a;
----
Explained Query:
  Reduce::Basic
    aggr=(0, array_agg[order_by=[]](row(array[#1{b}])))
    val_plan
      project=(#2)
      map=(row(array[#1{b}]))
    key_plan
      project=(#0)
    Get::Collection materialize.public.t
      raw=true

Source materialize.public.t
  project=(#0, #1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b), array_agg(c) FROM t GROUP BY a;
----
Explained Query:
  Reduce::Basic
    aggrs[0]=(0, array_agg[order_by=[]](row(array[#1{b}])))
    aggrs[1]=(1, array_agg[order_by=[]](row(array[#2{c}])))
    val_plan
      project=(#3, #4)
      map=(row(array[#1{b}]), row(array[#2{c}]))
    key_plan
      project=(#0)
    Get::PassArrangements materialize.public.t
      raw=true

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b), string_agg(c, ',') FROM t GROUP BY a;
----
Explained Query:
  Reduce::Basic
    aggrs[0]=(0, array_agg[order_by=[]](row(array[#1{b}])))
    aggrs[1]=(1, string_agg[order_by=[]](row(row(#2{c}, ","))))
    val_plan
      project=(#3, #4)
      map=(row(array[#1{b}]), row(row(#2{c}, ",")))
    key_plan
      project=(#0)
    Get::PassArrangements materialize.public.t
      raw=true

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b), string_agg(c, ',' ORDER BY b DESC) FROM t GROUP BY a;
----
Explained Query:
  Reduce::Basic
    aggrs[0]=(0, array_agg[order_by=[]](row(array[#1{b}])))
    aggrs[1]=(1, string_agg[order_by=[#0 desc nulls_first]](row(row(#2{c}, ","), #1{b})))
    val_plan
      project=(#3, #4)
      map=(row(array[#1{b}]), row(row(#2{c}, ","), #1{b}))
    key_plan
      project=(#0)
    Get::PassArrangements materialize.public.t
      raw=true

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b), max(c) FROM t WHERE c <> 'x' GROUP BY a;
----
Explained Query:
  Reduce::Collation
    aggregate_types=[b, h]
    hierarchical
      aggr_funcs=[max]
      skips=[1]
      monotonic
      must_consolidate
    basic
      aggr=(0, array_agg[order_by=[]](row(array[#1{b}])))
    val_plan
      project=(#3, #2)
      map=(row(array[#1{b}]))
    key_plan
      project=(#0)
    Get::Collection materialize.public.t
      raw=true

Source materialize.public.t
  filter=((#2{c} != "x"))

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b), max(b) FROM t GROUP BY a HAVING count(a) > 1;
----
Explained Query:
  Reduce::Collation
    aggregate_types=[b, h, a]
    accumulable
      simple_aggrs[0]=(0, 2, count(*))
    hierarchical
      aggr_funcs=[max]
      skips=[1]
      monotonic
      must_consolidate
    basic
      aggr=(0, array_agg[order_by=[]](row(array[#1{b}])))
    val_plan
      project=(#2, #1, #3)
      map=(row(array[#1{b}]), true)
    key_plan
      project=(#0)
    mfp_after
      project=(#0..=#2)
      filter=((#3{"?column?"} > 1))
    Get::Collection materialize.public.t
      raw=true

Source materialize.public.t
  project=(#0, #1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, min(b), max(b) FROM t GROUP BY a;
----
Explained Query:
  Reduce::Hierarchical
    aggr_funcs=[min, max]
    skips=[0, 0]
    monotonic
    must_consolidate
    val_plan
      project=(#1, #1)
    key_plan
      project=(#0)
    Get::Collection materialize.public.t
      raw=true

Source materialize.public.t
  project=(#0, #1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b ORDER BY b ASC), array_agg(b ORDER BY b DESC) FROM t GROUP BY a;
----
Explained Query:
  Reduce::Basic
    aggrs[0]=(0, array_agg[order_by=[#0 asc nulls_last]](row(array[#1{b}], #1{b})))
    aggrs[1]=(1, array_agg[order_by=[#0 desc nulls_first]](row(array[#1{b}], #1{b})))
    val_plan
      project=(#2, #2)
      map=(row(array[#1{b}], #1{b}))
    key_plan
      project=(#0)
    Get::Collection materialize.public.t
      raw=true

Source materialize.public.t
  project=(#0, #1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT array_agg(b ORDER BY b ASC), array_agg(b ORDER BY b DESC), bool_or(b IS NOT NULL) FROM t;
----
Explained Query:
  With
    cte l0 =
      Reduce::Collation
        aggregate_types=[b, b, a]
        accumulable
          simple_aggrs[0]=(0, 2, sum(1))
        basic
          aggrs[0]=(0, array_agg[order_by=[#0 asc nulls_last]](row(array[#0{b}], #0{b})))
          aggrs[1]=(1, array_agg[order_by=[#0 desc nulls_first]](row(array[#0{b}], #0{b})))
        val_plan
          project=(#1, #1, #2)
          map=(row(array[#0{b}], #0{b}), 1)
        key_plan
          project=()
        Get::Collection materialize.public.t
          raw=true
  Return
    Mfp
      project=(#0, #1, #3)
      map=((#2{"?column?"} > 0))
      Union
        ArrangeBy
          input_key=[]
          raw=true
          Get::PassArrangements l0
            raw=false
            arrangements[0]={ key=[], permutation=id, thinning=(#0..=#2) }
        Mfp
          project=(#0..=#2)
          map=(null, null, null)
          Union consolidate_output=true
            Negate
              Get::Arrangement l0
                project=()
                key=
                raw=false
                arrangements[0]={ key=[], permutation=id, thinning=(#0..=#2) }
            Constant
              - ()

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT t1.a, array_agg(t1.c), array_agg(t2.c) FROM t t1 INNER JOIN t t2 ON t1.c = t2.c WHERE t1.c IS NOT NULL GROUP BY t1.a;
----
Explained Query:
  With
    cte l0 =
      Get::Collection materialize.public.t
        raw=true
  Return
    Mfp
      project=(#0, #1, #1)
      input_key=#0
      Reduce::Basic
        aggr=(0, array_agg[order_by=[]](row(array[#1{c}])))
        val_plan
          project=(#2)
          map=(row(array[#1{c}]))
        key_plan
          project=(#0)
        Join::Linear
          linear_stage[0]
            closure
              project=(#1, #0)
            lookup={ relation=1, key=[#0{c}] }
            stream={ key=[#1{c}], thinning=(#0) }
          source={ relation=0, key=[#1{c}] }
          ArrangeBy
            raw=true
            arrangements[0]={ key=[#1{c}], permutation={#0: #1, #1: #0}, thinning=(#0) }
            types=[integer, text]
            Get::PassArrangements l0
              raw=true
          ArrangeBy
            raw=true
            arrangements[0]={ key=[#0{c}], permutation=id, thinning=() }
            types=[text]
            Get::Collection l0
              project=(#1)
              raw=true

Source materialize.public.t
  project=(#0, #2)
  filter=((#2{c}) IS NOT NULL)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT sum(a), jsonb_agg(b), array_agg(b), array_agg(b) FROM t;
----
Explained Query:
  With
    cte l0 =
      Reduce::Collation
        aggregate_types=[a, b, b]
        accumulable
          simple_aggrs[0]=(0, 0, sum(#0{a}))
        basic
          aggrs[0]=(1, jsonb_agg[order_by=[]](row(jsonbable_to_jsonb(#1{b}))))
          aggrs[1]=(2, array_agg[order_by=[]](row(array[#1{b}])))
        val_plan
          project=(#0, #2, #3)
          map=(row(jsonbable_to_jsonb(#1{b})), row(array[#1{b}]))
        key_plan
          project=()
        Get::Collection materialize.public.t
          raw=true
  Return
    Mfp
      project=(#0..=#2, #2)
      Union
        ArrangeBy
          input_key=[]
          raw=true
          Get::PassArrangements l0
            raw=false
            arrangements[0]={ key=[], permutation=id, thinning=(#0..=#2) }
        Mfp
          project=(#0..=#2)
          map=(null, null, null)
          Union consolidate_output=true
            Negate
              Get::Arrangement l0
                project=()
                key=
                raw=false
                arrangements[0]={ key=[], permutation=id, thinning=(#0..=#2) }
            Constant
              - ()

Source materialize.public.t
  project=(#0, #1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b ORDER BY b) FROM t GROUP BY a HAVING array_agg(b ORDER BY b) = array_agg(b ORDER BY b DESC);
----
Explained Query:
  Reduce::Basic
    aggrs[0]=(0, array_agg[order_by=[#0 asc nulls_last]](row(array[#1{b}], #1{b})))
    aggrs[1]=(1, array_agg[order_by=[#0 desc nulls_first]](row(array[#1{b}], #1{b})))
    val_plan
      project=(#2, #2)
      map=(row(array[#1{b}], #1{b}))
    key_plan
      project=(#0)
    mfp_after
      project=(#0, #1)
      filter=((#1{"?column?"} = #2{"?column?"}))
    Get::Collection materialize.public.t
      raw=true

Source materialize.public.t
  project=(#0, #1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b), array_agg(sha256(b::BYTEA)) FROM t GROUP BY a;

----
Explained Query:
  Reduce::Basic
    aggrs[0]=(0, array_agg[order_by=[]](row(array[#1{b}])))
    aggrs[1]=(1, array_agg[order_by=[]](row(array[digest(text_to_bytea(#1{b}), "sha256")])))
    val_plan
      project=(#2, #3)
      map=(row(array[#1{b}]), row(array[digest(text_to_bytea(#1{b}), "sha256")]))
    key_plan
      project=(#0)
    Get::Collection materialize.public.t
      raw=true

Source materialize.public.t
  project=(#0, #1)

Target cluster: quickstart

EOF


query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, array_agg(b), array_agg(CASE WHEN a = 1 THEN 'ooo' ELSE b END) FROM t GROUP BY a;
----
Explained Query:
  Reduce::Basic
    aggrs[0]=(0, array_agg[order_by=[]](row(array[#1{b}])))
    aggrs[1]=(1, array_agg[order_by=[]](row(array[case when (#0{a} = 1) then "ooo" else #1{b} end])))
    val_plan
      project=(#2, #3)
      map=(row(array[#1{b}]), row(array[case when (#0{a} = 1) then "ooo" else #1{b} end]))
    key_plan
      project=(#0)
    Get::Collection materialize.public.t
      raw=true

Source materialize.public.t
  project=(#0, #1)

Target cluster: quickstart

EOF

query T multiline
EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT FOR SELECT a, dense_rank() OVER (ORDER BY a), array_agg(b) FROM t GROUP BY a;
----
Explained Query:
  Mfp
    project=(#2, #4, #3)
    map=(record_get[1](#0), record_get[0](#1), record_get[1](#1), record_get[0](#0))
    input_key=
    Reduce::Basic
      aggr=(0, dense_rank[order_by=[#0 asc nulls_last]](row(list[row(#0, #1)], #0{a})), fused_unnest_list=true)
      val_plan
        project=(#2)
        map=(row(list[row(#0, #1)], #0{a}))
      key_plan
        project=()
      input_key=#0
      Reduce::Basic
        aggr=(0, array_agg[order_by=[]](row(array[#1{b}])))
        val_plan
          project=(#2)
          map=(row(array[#1{b}]))
        key_plan
          project=(#0)
        Get::Collection materialize.public.t
          raw=true

Source materialize.public.t
  project=(#0, #1)

Target cluster: quickstart

EOF