Materialize/test/sqllogictest/explain/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 OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a, array_agg(b) FROM t GROUP BY a;
----
Explained Query:
  Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{b}]))]
    Project (#0{a}, #1{b})
      ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a, array_agg(b), array_agg(c) FROM t GROUP BY a;
----
Explained Query:
  Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{b}])), array_agg[order_by=[]](row(array[#2{c}]))]
    ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a, array_agg(b), string_agg(c, ',') FROM t GROUP BY a;
----
Explained Query:
  Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{b}])), string_agg[order_by=[]](row(row(#2{c}, ",")))]
    ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a, array_agg(b), string_agg(c, ',' ORDER BY b DESC) FROM t GROUP BY a;
----
Explained Query:
  Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{b}])), string_agg[order_by=[#0{a} desc nulls_first]](row(row(#2{c}, ","), #1{b}))]
    ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a, array_agg(b), max(c) FROM t WHERE c <> 'x' GROUP BY a;
----
Explained Query:
  Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{b}])), max(#2{c})]
    Filter (#2{c} != "x")
      ReadStorage materialize.public.t

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a, array_agg(b), max(b) FROM t GROUP BY a HAVING count(a) > 1;
----
Explained Query:
  Project (#0{a}..=#2{max_b})
    Filter (#3{count} > 1)
      Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{b}])), max(#1{b}), count(*)]
        Project (#0{a}, #1{b})
          ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a, min(b), max(b) FROM t GROUP BY a;
----
Explained Query:
  Reduce group_by=[#0{a}] aggregates=[min(#1{b}), max(#1{b})]
    Project (#0{a}, #1{b})
      ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) 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 group_by=[#0{a}] aggregates=[array_agg[order_by=[#0{a} asc nulls_last]](row(array[#1{b}], #1{b})), array_agg[order_by=[#0{a} desc nulls_first]](row(array[#1{b}], #1{b}))]
    Project (#0{a}, #1{b})
      ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) 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 aggregates=[array_agg[order_by=[#0{b} asc nulls_last]](row(array[#0{b}], #0{b})), array_agg[order_by=[#0{b} desc nulls_first]](row(array[#0{b}], #0{b})), sum(1)]
        Project (#1{b})
          ReadStorage materialize.public.t
  Return
    Project (#0{array_agg}, #1{array_agg}, #3)
      Map ((#2{sum} > 0))
        Union
          Get l0
          Map (null, null, null)
            Union
              Negate
                Project ()
                  Get l0
              Constant
                - ()

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) 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 =
      Project (#0{a}, #2{c})
        Filter (#2{c}) IS NOT NULL
          ReadStorage materialize.public.t
  Return
    Project (#0{a}, #1{array_agg}, #1{array_agg})
      Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{c}]))]
        Project (#0{a}, #1{c})
          Join on=(#1{c} = #2{c}) type=differential
            ArrangeBy keys=[[#1{c}]]
              Get l0
            ArrangeBy keys=[[#0{c}]]
              Project (#1{c})
                Get l0

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

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT sum(a), jsonb_agg(b), array_agg(b), array_agg(b) FROM t;
----
Explained Query:
  With
    cte l0 =
      Reduce aggregates=[sum(#0{a}), jsonb_agg[order_by=[]](row(jsonbable_to_jsonb(#1{b}))), array_agg[order_by=[]](row(array[#1{b}]))]
        Project (#0{a}, #1{b})
          ReadStorage materialize.public.t
  Return
    Project (#0{sum_a}..=#2{array_agg}, #2{array_agg})
      Union
        Get l0
        Map (null, null, null)
          Union
            Negate
              Project ()
                Get l0
            Constant
              - ()

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) 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:
  Project (#0{a}, #1{array_agg})
    Filter (#1{array_agg} = #2{array_agg})
      Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[#0{a} asc nulls_last]](row(array[#1{b}], #1{b})), array_agg[order_by=[#0{a} desc nulls_first]](row(array[#1{b}], #1{b}))]
        Project (#0{a}, #1{b})
          ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

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

----
Explained Query:
  Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{b}])), array_agg[order_by=[]](row(array[digest(text_to_bytea(#1{b}), "sha256")]))]
    Project (#0{a}, #1{b})
      ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF


query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) 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 group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{b}])), array_agg[order_by=[]](row(array[case when (#0{a} = 1) then "ooo" else #1{b} end]))]
    Project (#0{a}, #1{b})
      ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR SELECT a, dense_rank() OVER (ORDER BY a), array_agg(b) FROM t GROUP BY a;
----
Explained Query:
  Project (#3, #5, #4)
    Map (record_get[1](#1), record_get[0](#2), record_get[1](#2), record_get[0](#1))
      FlatMap unnest_list(#0{dense_rank})
        Reduce aggregates=[dense_rank[order_by=[#0{a} asc nulls_last]](row(list[row(#0{a}, #1{array_agg})], #0{a}))]
          Reduce group_by=[#0{a}] aggregates=[array_agg[order_by=[]](row(array[#1{b}]))]
            Project (#0{a}, #1{b})
              ReadStorage materialize.public.t

Source materialize.public.t

Target cluster: quickstart

EOF