Materialize/test/sqllogictest/aggregates_2.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 TT rowsort
SELECT a, array_agg(b) FROM t GROUP BY a;
----
1  {10}
2  {20}
3  {30}
4  {40}
5  {50a,50b,50c}


query TTT rowsort
SELECT a, array_agg(b), array_agg(c) FROM t GROUP BY a;
----
2  {20}  {NULL}
3  {30}  {NULL}
1  {10}  {x}
4  {40}  {x}
5  {50a,50b,50c}  {x,y,z}


query TTT rowsort
SELECT a, array_agg(b), string_agg(c, ',') FROM t GROUP BY a;
----
2  {20}  NULL
3  {30}  NULL
1  {10}  x
4  {40}  x
5  {50a,50b,50c}  x,y,z


query TTT rowsort
SELECT a, array_agg(b), string_agg(c, ',' ORDER BY b DESC) FROM t GROUP BY a;
----
2  {20}  NULL
3  {30}  NULL
1  {10}  x
4  {40}  x
5  {50a,50b,50c}  z,y,x


query TTT rowsort
SELECT a, array_agg(b), max(c) FROM t WHERE c <> 'x' GROUP BY a;
----
5  {50b,50c}  z


query TTT rowsort
SELECT a, array_agg(b), max(b) FROM t GROUP BY a HAVING count(a) > 1;
----
5  {50a,50b,50c}  50c


query TTT rowsort
SELECT a, min(b), max(b) FROM t GROUP BY a;
----
1  10  10
2  20  20
3  30  30
4  40  40
5  50a  50c


query TTT rowsort
SELECT a, array_agg(b ORDER BY b ASC), array_agg(b ORDER BY b DESC) FROM t GROUP BY a;
----
1  {10}  {10}
2  {20}  {20}
3  {30}  {30}
4  {40}  {40}
5  {50a,50b,50c}  {50c,50b,50a}


query TTT rowsort
SELECT array_agg(b ORDER BY b ASC), array_agg(b ORDER BY b DESC), bool_or(b IS NOT NULL) FROM t;
----
{10,20,30,40,50a,50b,50c}  {50c,50b,50a,40,30,20,10}  true


query TTT rowsort
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;
----
1  {x,x,x}  {x,x,x}
4  {x,x,x}  {x,x,x}
5  {x,x,x,y,z}  {x,x,x,y,z}


query TTTT rowsort
SELECT sum(a), jsonb_agg(b), array_agg(b), array_agg(b) FROM t;
----
25  ["10","20","30","40","50a","50b","50c"]  {10,20,30,40,50a,50b,50c}  {10,20,30,40,50a,50b,50c}


query TT rowsort
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);
----
1  {10}
2  {20}
3  {30}
4  {40}


query TTT rowsort
SELECT a, array_agg(b), array_agg(sha256(b::BYTEA)) FROM t GROUP BY a;
----
1  {10}  {"\\x4a44dc15364204a80fe80e9039455cc1608281820fe2b24f1e5233ade6af1dd5"}
2  {20}  {"\\xf5ca38f748a1d6eaf726b8a42fb575c3c71f1864a8143301782de13da2d9202b"}
3  {30}  {"\\x624b60c58c9d8bfb6ff1886c2fd605d2adeb6ea4da576068201b6c6958ce93f4"}
4  {40}  {"\\xd59eced1ded07f84c145592f65bdf854358e009c5cd705f5215bf18697fed103"}
5  {50a,50b,50c}  {"\\x189a55d80e074ffaacb282b6f9b51dcc1152a4e3f365a09d086347cbddcbb49b","\\xb3aecad3a43c511942fac3629ec9d191ff86961285d711875822721a1b676cd9","\\xb7abe8e9eedafe78353d0d3c6d837e1295b727f2ce3e220b4271b25a3b2e347b"}


query TTT rowsort
SELECT a, array_agg(b), array_agg(CASE WHEN a = 1 THEN 'ooo' ELSE b END) FROM t GROUP BY a;
----
2  {20}  {20}
3  {30}  {30}
4  {40}  {40}
1  {10}  {ooo}
5  {50a,50b,50c}  {50a,50b,50c}


query TTT rowsort
SELECT a, dense_rank() OVER (ORDER BY a), array_agg(b) FROM t GROUP BY a;
----
1  1  {10}
2  2  {20}
3  3  {30}
4  4  {40}
5  5  {50a,50b,50c}