aggregate

subtest other

statement ok
CREATE TABLE kv (
  k INT PRIMARY KEY,
  v INT,
  w INT,
  s STRING,
  i INTERVAL
)

# Aggregate functions return NULL if there are no rows.
query IIIIRRRRBBTII
SELECT min(1), max(1), count(1), sum_int(1), avg(1), sum(1), stddev(1), variance(1), bool_and(true), bool_and(false), xor_agg(b'\x01'), bit_and(1), bit_or(1) FROM kv
----
NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

# Regression test for #29695
query T
SELECT min(NULL)
----
NULL

# Aggregate functions return NULL if there are no rows.
query T
SELECT array_agg(1) FROM kv
----
NULL

query T
SELECT json_agg(1) FROM kv
----
NULL

query T
SELECT jsonb_agg(1) FROM kv
----
NULL

query TTTT
SELECT min(i), avg(i), max(i), sum(i) FROM kv
----
NULL NULL NULL NULL

query IIIIRRRRBBT
SELECT min(v), max(v), count(v), sum_int(1), avg(v), sum(v), stddev(v), variance(v), bool_and(v = 1), bool_and(v = 1), xor_agg(s::bytes) FROM kv
----
NULL NULL 0 NULL NULL NULL NULL NULL NULL NULL NULL

query T
SELECT array_agg(v) FROM kv
----
NULL

query T
SELECT json_agg(v) FROM kv
----
NULL

query T
SELECT jsonb_agg(v) FROM kv
----
NULL

# Aggregate functions triggers aggregation and computation when there is no source.
query IIIIRRRRBBT
SELECT min(1), count(1), max(1), sum_int(1), avg(1)::float, sum(1), stddev(1), variance(1), bool_and(true), bool_or(true), to_hex(xor_agg(b'\x01'))
----
1 1 1 1 1 1 NULL NULL true true 01

# Aggregate functions triggers aggregation and computation when there is no source.
query T
SELECT array_agg(1)
----
{1}

query T
SELECT json_agg(1)
----
[1]

query T
SELECT jsonb_agg(1)
----
[1]

# Some aggregate functions are not normalized to NULL when given a NULL
# argument.
query I
SELECT count(NULL)
----
0

query T
SELECT json_agg(NULL)
----
[null]

query T
SELECT jsonb_agg(NULL)
----
[null]

# This should ideally return {NULL}, but this is a pathological case, and
# Postgres has the same behavior, so it's sufficient for now.
statement error ambiguous call
SELECT array_agg(NULL)

# With an explicit cast, this works as expected.
query T
SELECT array_agg(NULL::TEXT)
----
{NULL}

# Regression test for #25724 (problem with typed NULLs and distsql planning).
# The previous query doesn't run under distsql.
query T
SELECT array_agg(NULL::TEXT) FROM (VALUES (1)) AS t(x)
----
{NULL}

# Check that COALESCE using aggregate results over an empty table
# work properly.
query I
SELECT COALESCE(max(1), 0) FROM generate_series(1,0)
----
0

query I
SELECT count_rows() FROM generate_series(1,100)
----
100

# Same, using arithmetic on COUNT.
query I
SELECT 1 + count(*) FROM generate_series(1,0)
----
1

# Same, using an empty table.
# The following test *must* occur before the first INSERT to the tables,
# so that it can observe an empty table.
query II
SELECT count(*), COALESCE(max(k), 1) FROM kv
----
0 1

# Same, using a subquery. (#12705)
query I
SELECT (SELECT COALESCE(max(1), 0) FROM generate_series(1,0))
----
0

statement OK
INSERT INTO kv VALUES
(1, 2, 3, 'a', '1min'),
(3, 4, 5, 'a', '2sec'),
(5, NULL, 5, NULL, NULL),
(6, 2, 3, 'b', '1ms'),
(7, 2, 2, 'b', '4 days'),
(8, 4, 2, 'A', '3 years')

# Aggregate functions triggers aggregation and computation for every row even when applied to a constant.
# NB: The XOR result is 00 because \x01 is XOR'd an even number of times.
query IIIIRRRRBBT
SELECT min(1), count(1), max(1), sum_int(1), avg(1)::float, sum(1), stddev(1), variance(1)::float, bool_and(true), bool_or(true), to_hex(xor_agg(b'\x01')) FROM kv
----
1 6 1 6 1 6 0 0 true true 00

# Aggregate functions triggers aggregation and computation for every row even when applied to a constant.
query T
SELECT array_agg(1) FROM kv
----
{1,1,1,1,1,1}

query T
SELECT json_agg(1) FROM kv
----
[1, 1, 1, 1, 1, 1]

query T
SELECT jsonb_agg(1) FROM kv
----
[1, 1, 1, 1, 1, 1]

# Even with no aggregate functions, grouping occurs in the presence of GROUP BY.
query I rowsort
SELECT 1 FROM kv GROUP BY v
----
1
1
1

# Presence of HAVING triggers aggregation, reducing results to one row (even without GROUP BY).
query I rowsort
SELECT 3 FROM kv HAVING TRUE
----
3

query error pgcode 42803 column "k" must appear in the GROUP BY clause or be used in an aggregate function
SELECT count(*), k FROM kv

query error unsupported comparison operator: <string> < <int>
SELECT count(*) FROM kv GROUP BY s < 5

query II rowsort
SELECT count(*), k FROM kv GROUP BY k
----
1 1
1 3
1 5
1 6
1 7
1 8

# GROUP BY specified using column index works.
query II rowsort
SELECT count(*), k FROM kv GROUP BY 2
----
1 1
1 3
1 5
1 6
1 7
1 8

query error aggregate functions are not allowed in GROUP BY
SELECT * FROM kv GROUP BY v, count(DISTINCT w)

query error aggregate functions are not allowed in GROUP BY
SELECT count(DISTINCT w) FROM kv GROUP BY 1

query error aggregate functions are not allowed in RETURNING
INSERT INTO kv (k, v) VALUES (99, 100) RETURNING sum(v)

query error column "v" does not exist
SELECT sum(v) FROM kv GROUP BY k LIMIT sum(v)

query error column "v" does not exist
SELECT sum(v) FROM kv GROUP BY k LIMIT 1 OFFSET sum(v)

query error aggregate functions are not allowed in VALUES
INSERT INTO kv (k, v) VALUES (99, count(1))

query error pgcode 42P10 GROUP BY position 5 is not in select list
SELECT count(*), k FROM kv GROUP BY 5

query error pgcode 42P10 GROUP BY position 0 is not in select list
SELECT count(*), k FROM kv GROUP BY 0

query error pgcode 42601 non-integer constant in GROUP BY
SELECT 1 GROUP BY 'a'

# Qualifying a name in the SELECT, the GROUP BY, both or neither should not affect validation.
query IT rowsort
SELECT count(*), kv.s FROM kv GROUP BY s
----
1 A
1 NULL
2 a
2 b

query IT rowsort
SELECT count(*), s FROM kv GROUP BY kv.s
----
1 A
1 NULL
2 a
2 b

query IT rowsort
SELECT count(*), kv.s FROM kv GROUP BY kv.s
----
1 A
1 NULL
2 a
2 b

query IT rowsort
SELECT count(*), s FROM kv GROUP BY s
----
1 A
1 NULL
2 a
2 b

# Grouping by more than one column works.
query III rowsort
SELECT v, count(*), w FROM kv GROUP BY v, w
----
2    1 2
2    2 3
4    1 2
4    1 5
NULL 1 5

# Grouping by more than one column using column numbers works.
query III rowsort
SELECT v, count(*), w FROM kv GROUP BY 1, 3
----
2    1 2
2    2 3
4    1 2
4    1 5
NULL 1 5

# Selecting and grouping on a function expression works.
query IT rowsort
SELECT count(*), upper(s) FROM kv GROUP BY upper(s)
----
1 NULL
2 B
3 A

# Selecting and grouping on a constant works.
query I
SELECT count(*) FROM kv GROUP BY 1+2
----
6

query I
SELECT count(*) FROM kv GROUP BY length('abc')
----
6

# Selecting a function of something which is grouped works.
query IT rowsort
SELECT count(*), upper(s) FROM kv GROUP BY s
----
1 A
1 NULL
2 A
2 B

# Selecting a value that is not grouped, even if a function of it it, does not work.
query error column "s" must appear in the GROUP BY clause or be used in an aggregate function
SELECT count(*), s FROM kv GROUP BY upper(s)

# Selecting and grouping on a more complex expression works.
query II rowsort
SELECT count(*), k+v FROM kv GROUP BY k+v
----
1 12
1 3
1 7
1 8
1 9
1 NULL


# Selecting a more complex expression, made up of things which are each grouped, works.
query II rowsort
SELECT count(*), k+v FROM kv GROUP BY k, v
----
1 12
1 3
1 7
1 8
1 9
1 NULL

query II rowsort
SELECT count(*), k+v FROM kv GROUP BY k
----
1  3
1  7
1  NULL
1  8
1  9
1  12

query error column "k" must appear in the GROUP BY clause or be used in an aggregate function
SELECT count(*), k+v FROM kv GROUP BY v

query error column "v" must appear in the GROUP BY clause or be used in an aggregate function
SELECT count(*), v/(k+v) FROM kv GROUP BY k+v

query error aggregate functions are not allowed in WHERE
SELECT k FROM kv WHERE avg(k) > 1

query error aggregate function calls cannot be nested
SELECT max(avg(k)) FROM kv

# Test case from #2761.
query II rowsort
SELECT count(kv.k) AS count_1, kv.v + kv.w AS lx FROM kv GROUP BY kv.v + kv.w
----
1  4
1  6
1  9
1  NULL
2  5

query TI rowsort
SELECT s, count(*) FROM kv GROUP BY s HAVING count(*) > 1
----
a 2
b 2

query TII rowsort
SELECT upper(s), count(DISTINCT s), count(DISTINCT upper(s)) FROM kv GROUP BY upper(s) HAVING count(DISTINCT s) > 1
----
A 2 1

query II rowsort
SELECT max(k), min(v) FROM kv HAVING min(v) > 2
----

query II rowsort
SELECT max(k), min(v) FROM kv HAVING max(v) > 2
----
8 2

query error pgcode 42803 aggregate function calls cannot be nested
SELECT max(k), min(v) FROM kv HAVING max(min(v)) > 2

query error argument of HAVING must be type bool, not type int
SELECT max(k), min(v) FROM kv HAVING k

# Expressions listed in the HAVING clause must conform to same validation as the SELECT clause (grouped or aggregated).
query error column "k" must appear in the GROUP BY clause or be used in an aggregate function
SELECT 3 FROM kv GROUP BY v HAVING k > 5

# Special case for grouping on primary key.
query I
SELECT 3 FROM kv GROUP BY k HAVING v > 2
----
3
3

query error column "k" must appear in the GROUP BY clause or be used in an aggregate function
SELECT k FROM kv HAVING k > 7

query error at or near ",": syntax error
SELECT count(*, 1) FROM kv

query I
SELECT count(*)
----
1

query I
SELECT count(k) from kv
----
6

query I
SELECT count(1)
----
1

query I
SELECT count(1) from kv
----
6

query error unknown signature: count\(int, int\)
SELECT count(k, v) FROM kv

query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY v
----
NULL 1
2 3
4 2

query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY v DESC
----
4 2
2 3
NULL 1

query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY count(k) DESC
----
2 3
4 2
NULL 1

query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY v-count(k)
----
NULL 1
2 3
4 2

query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY 1 DESC
----
4 2
2 3
NULL 1

query III colnames
SELECT count(*), count(k), count(kv.v) FROM kv
----
count  count  count
6      6      5

query I
SELECT count(kv.*) FROM kv
----
6

query III
SELECT count(DISTINCT k), count(DISTINCT v), count(DISTINCT (v)) FROM kv
----
6 2 2

query TIII rowsort
SELECT upper(s), count(DISTINCT k), count(DISTINCT v), count(DISTINCT (v)) FROM kv GROUP BY upper(s)
----
A    3 2 2
B    2 1 1
NULL 1 0 0


query I
SELECT count((k, v)) FROM kv
----
6

query I
SELECT count(DISTINCT (k, v)) FROM kv
----
6

query I
SELECT count(DISTINCT (k, (v))) FROM kv
----
6

query I
SELECT count(*) FROM kv a, kv b
----
36

query I
SELECT count(DISTINCT a.*) FROM kv a, kv b
----
6

query I
SELECT count((k, v)) FROM kv LIMIT 1
----
6

query I
SELECT count((k, v)) FROM kv OFFSET 1
----

query I
SELECT count(k)+count(kv.v) FROM kv
----
11

query II
SELECT count(NULL::int), count((NULL, NULL))
----
0 1

query IIII
SELECT min(k), max(k), min(v), max(v) FROM kv
----
1 8 2 4

# Even if no input rows match, we expect a row (of nulls).
query IIII
SELECT min(k), max(k), min(v), max(v) FROM kv WHERE k > 8
----
NULL NULL NULL NULL

query TT
SELECT array_agg(k), array_agg(s) FROM (SELECT k, s FROM kv ORDER BY k)
----
{1,3,5,6,7,8}  {a,a,NULL,b,b,A}

query T
SELECT array_agg(k) || 1 FROM (SELECT k FROM kv ORDER BY k)
----
{1,3,5,6,7,8,1}

query T
SELECT array_agg(s) FROM kv WHERE s IS NULL
----
{NULL}

query T
SELECT json_agg(s) FROM kv WHERE s IS NULL
----
[null]

query T
SELECT jsonb_agg(s) FROM kv WHERE s IS NULL
----
[null]

query RRRR
SELECT avg(k), avg(v), sum(k), sum(v) FROM kv
----
5 2.8 30 14

query TTTT
SELECT min(i), avg(i), max(i), sum(i) FROM kv
----
00:00:00.001  7 mons 6 days 19:12:12.4002  3 years  3 years 4 days 00:01:02.001

query TT
SELECT i, avg(i) OVER (ORDER BY i) FROM kv ORDER BY i
----
NULL          NULL
00:00:00.001  00:00:00.001
00:00:02      00:00:01.0005
00:01:00      00:00:20.667
4 days        1 day 00:00:15.50025
3 years       7 mons 6 days 19:12:12.4002

query RRRR
SELECT avg(k::decimal), avg(v::decimal), sum(k::decimal), sum(v::decimal) FROM kv
----
5 2.8 30 14

query RRRR
SELECT avg(DISTINCT k), avg(DISTINCT v), sum(DISTINCT k), sum(DISTINCT v) FROM kv
----
5 3 30 6

query R
SELECT avg(k) * 2.0 + max(v)::DECIMAL FROM kv
----
14.0

# Verify things work with distsql when some of the nodes emit no results in the
# local stage.
query R
SELECT avg(k) * 2.0 + max(v)::DECIMAL FROM kv WHERE w*2 = k
----
14.0

# Grouping columns can be eliminated, but should still return zero rows (i.e.
# shouldn't use scalar GroupBy).
query I
SELECT max(v) FROM kv GROUP BY k HAVING k=100
----

# Same query as above, but using scalar GroupBy (should return default row).
query I
SELECT max(v) FROM kv WHERE k=100
----
NULL

statement ok
CREATE TABLE abc (
  a VARCHAR PRIMARY KEY,
  b FLOAT,
  c BOOLEAN,
  d DECIMAL
)

statement ok
INSERT INTO abc VALUES ('one', 1.5, true, 5::decimal), ('two', 2.0, false, 1.1::decimal)

query TRBR
SELECT min(a), min(b), min(c), min(d) FROM abc
----
one 1.5 false 1.1

query TRBR
SELECT max(a), max(b), max(c), max(d) FROM abc
----
two 2 true 5

query RRRR
SELECT avg(b), sum(b), avg(d), sum(d) FROM abc
----
1.75 3.5 3.05 6.1

# Verify summing of intervals
statement ok
CREATE TABLE intervals (
  a INTERVAL PRIMARY KEY
)

statement ok
INSERT INTO intervals VALUES (INTERVAL '1 year 2 months 3 days 4 seconds'), (INTERVAL '2 year 3 months 4 days 5 seconds'), (INTERVAL '10000ms')

query T
SELECT sum(a) FROM intervals
----
3 years 5 mons 7 days 00:00:19


query error unknown signature: avg\(varchar\)
SELECT avg(a) FROM abc

query error unknown signature: avg\(bool\)
SELECT avg(c) FROM abc

query error unknown signature: avg\(tuple{varchar, bool}\)
SELECT avg((a,c)) FROM abc

query error unknown signature: sum\(varchar\)
SELECT sum(a) FROM abc

query error unknown signature: sum\(bool\)
SELECT sum(c) FROM abc

query error unknown signature: sum\(tuple{varchar, bool}\)
SELECT sum((a,c)) FROM abc

statement ok
CREATE TABLE xyz (
  x INT PRIMARY KEY,
  y INT,
  z FLOAT,
  w INT,
  INDEX xy (x, y),
  INDEX zyx (z, y, x),
  INDEX w (w),
  FAMILY (x),
  FAMILY (y),
  FAMILY (z)
)

statement ok
INSERT INTO xyz VALUES (1, 2, 3.0, NULL), (4, 5, 6.0, 2), (7, NULL, 8.0, 3)

query I
SELECT min(x) FROM xyz
----
1

query I
SELECT min(y) FROM xyz
----
2

query I
SELECT min(w) FROM xyz
----
2

query I
SELECT min(x) FROM xyz WHERE x in (0, 4, 7)
----
4

query I
SELECT max(x) FROM xyz
----
7

query I
SELECT min(y) FROM xyz WHERE x = 1
----
2

query I
SELECT max(y) FROM xyz WHERE x = 1
----
2

query I
SELECT min(y) FROM xyz WHERE x = 7
----
NULL

query I
SELECT max(y) FROM xyz WHERE x = 7
----
NULL

query I
SELECT min(x) FROM xyz WHERE (y, z) = (2, 3.0)
----
1

query I
SELECT max(x) FROM xyz WHERE (z, y) = (3.0, 2)
----
1

# VARIANCE/STDDEV

query RRR
SELECT variance(x), variance(y::decimal), round(variance(z), 14) FROM xyz
----
9 4.5 6.33333333333333

query R
SELECT variance(x) FROM xyz WHERE x = 10
----
NULL

query R
SELECT variance(x) FROM xyz WHERE x = 1
----
NULL

query RRR
SELECT stddev(x), stddev(y::decimal), round(stddev(z), 14) FROM xyz
----
3  2.1213203435596425732  2.51661147842358

query R
SELECT stddev(x) FROM xyz WHERE x = 1
----
NULL

# Numerical stability test for VARIANCE/STDDEV.
# See https://www.johndcook.com/blog/2008/09/28/theoretical-explanation-for-numerical-results.
# Avoid using random() since we do not have the deterministic option to specify a pseudo-random seed yet.
# Note under distsql, this is non-deterministic since the running variance/stddev algorithms depend on
# the local sum of squared difference values which depend on how the data is distributed across the distsql nodes.
statement ok
CREATE TABLE mnop (
  m INT PRIMARY KEY,
  n FLOAT,
  o DECIMAL,
  p BIGINT
)

statement ok
INSERT INTO mnop (m, n) SELECT i, (1e9 + i/2e4)::float FROM
  generate_series(1, 2e4) AS i(i)

statement ok
UPDATE mnop SET o = n::decimal, p = (n * 10)::bigint

query RRR
SELECT round(variance(n), 2), round(variance(n), 2), round(variance(p)) FROM mnop
----
0.08 0.08 8


query RRR
SELECT round(stddev(n), 2), round(stddev(n), 2), round(stddev(p)) FROM mnop
----
0.29 0.29 3

query RRR
SELECT avg(1::int)::float, avg(2::float)::float, avg(3::decimal)::float
----
1 2 3

query III
SELECT count(2::int), count(3::float), count(4::decimal)
----
1 1 1

query RRR
SELECT sum(1::int), sum(2::float), sum(3::decimal)
----
1 2 3

query RRR
SELECT variance(1::int), variance(1::float), variance(1::decimal)
----
NULL NULL NULL

query RRR
SELECT stddev(1::int), stddev(1::float), stddev(1::decimal)
----
NULL NULL NULL

# Ensure subqueries don't trigger aggregation.
query B
SELECT x > (SELECT avg(0)) FROM xyz LIMIT 1
----
true

statement ok
CREATE TABLE bits (b INT)

query II
SELECT bit_and(b), bit_or(b) FROM bits
----
NULL NULL

statement ok
INSERT INTO bits VALUES (12), (25)

query II
SELECT bit_and(b), bit_or(b) FROM bits
----
8 29

statement ok
INSERT INTO bits VALUES(105)

query II
SELECT bit_and(b), bit_or(b) FROM bits
----
8 125

statement ok
INSERT INTO bits VALUES(NULL)

query II
SELECT bit_and(b), bit_or(b) FROM bits
----
8 125

statement ok
CREATE TABLE bools (b BOOL)

query BB
SELECT bool_and(b), bool_or(b) FROM bools
----
NULL NULL

statement OK
INSERT INTO bools VALUES (true), (true), (true)

query BB
SELECT bool_and(b), bool_or(b) FROM bools
----
true true

statement OK
INSERT INTO bools VALUES (false), (false)

query BB
SELECT bool_and(b), bool_or(b) FROM bools
----
false true

statement OK
DELETE FROM bools WHERE b

query BB
SELECT bool_and(b), bool_or(b) FROM bools
----
false false

query T
SELECT concat_agg(s) FROM (SELECT s FROM kv ORDER BY k)
----
aabbA

query T
SELECT json_agg(s) FROM (SELECT s FROM kv ORDER BY k)
----
["a", "a", null, "b", "b", "A"]

query T
SELECT jsonb_agg(s) FROM (SELECT s FROM kv ORDER BY k)
----
["a", "a", null, "b", "b", "A"]

# Verify that FILTER works.

statement ok
CREATE TABLE filter_test (
  k INT,
  v INT,
  mark BOOL
)

statement OK
INSERT INTO filter_test VALUES
(1, 2, false),
(3, 4, true),
(5, NULL, true),
(6, 2, true),
(7, 2, true),
(8, 4, true),
(NULL, 4, true)

# FILTER should eliminate some results.
query II rowsort
SELECT v, count(*) FILTER (WHERE k > 5) FROM filter_test GROUP BY v
----
2 2
4 1
NULL 0

# Test multiple filters
query IBIII rowsort
SELECT v, mark, count(*) FILTER (WHERE k > 5), count(*), max(k) FILTER (WHERE k < 8) FROM filter_test GROUP BY v, mark
----
2 false 0 1 1
2 true 2 2 7
4 true 1 3 3
NULL true 0 1 5

query error FILTER specified but abs\(\) is not an aggregate function
SELECT k, abs(k) FILTER (WHERE k=1) FROM kv

query error at or near "filter": syntax error
SELECT k FILTER (WHERE k=1) FROM kv GROUP BY k

query error aggregate functions are not allowed in FILTER
SELECT v, count(*) FILTER (WHERE count(*) > 5) FROM filter_test GROUP BY v

# Tests with * inside GROUP BY.
query I
SELECT 1 FROM kv GROUP BY kv.*
----
1
1
1
1
1
1

query R rowsort
SELECT sum(abc.d) FROM kv JOIN abc ON kv.k >= abc.d GROUP BY kv.*
----
1.1
6.1
6.1
6.1
6.1

# opt_test is used for tests around the single-row optimization for MIN/MAX.
statement ok
CREATE TABLE opt_test (k INT PRIMARY KEY, v INT, INDEX v(v))

statement ok
INSERT INTO opt_test VALUES (1, NULL), (2, 10), (3, NULL), (4, 5)

# Verify that we correctly add the v IS NOT NULL constraint (which restricts the span).
# Without the "v IS NOT NULL" constraint, this result would incorrectly be NULL.
query I
SELECT min(v) FROM opt_test
----
5

# Cross-check against a query without this optimization.
query I
SELECT min(v) FROM opt_test@primary
----
5

# Repeat test when there is an existing filter.
query I
SELECT min(v) FROM opt_test WHERE k <> 4
----
10

# Verify that we don't use the optimization if there is a GROUP BY.
query I rowsort
SELECT min(v) FROM opt_test GROUP BY k
----
NULL
NULL
5
10

query I rowsort
SELECT max(v) FROM opt_test GROUP BY k
----
NULL
NULL
5
10

statement ok
CREATE TABLE xor_bytes (a bytes, b int, c int)

statement ok
INSERT INTO xor_bytes VALUES
  (b'\x01\x01', 1, 3),
  (b'\x02\x01', 1, 1),
  (b'\x04\x01', 2, -5),
  (b'\x08\x01', 2, -1),
  (b'\x10\x01', 2, 0)

query TI
SELECT to_hex(xor_agg(a)), xor_agg(c) FROM xor_bytes
----
1f01 6

query TII
SELECT to_hex(xor_agg(a)), b, xor_agg(c) FROM xor_bytes GROUP BY b ORDER BY b
----
0300  1   2
1c01  2   4

statement error arguments to xor must all be the same length
SELECT xor_agg(i) FROM (VALUES (b'\x01'), (b'\x01\x01')) AS a(i)

query BB
SELECT max(true), min(true)
----
true
true

# Grouping and rendering tuples.
statement OK
CREATE TABLE ab (
  a INT PRIMARY KEY,
  b INT,
  FAMILY (a),
  FAMILY (b)
)

statement ok
INSERT INTO ab(a,b) VALUES (1,2), (3,4);
  CREATE TABLE xy(x STRING, y STRING);
  INSERT INTO xy(x, y) VALUES ('a', 'b'), ('c', 'd')

# Grouping and rendering tuples.
query T rowsort
SELECT (b, a) FROM ab GROUP BY (b, a)
----
(2,1)
(4,3)

query TT rowsort
SELECT min(y), (b, a)
 FROM ab, xy GROUP BY (x, (a, b))
----
b  (2,1)
d  (2,1)
b  (4,3)
d  (4,3)

# Test that ordering on GROUP BY columns is maintained.
statement ok
CREATE TABLE group_ord (
  x INT PRIMARY KEY,
  y INT,
  z INT,
  INDEX foo(z)
)

statement ok
INSERT INTO group_ord VALUES
(1, 2, 3),
(3, 4, 5),
(5, NULL, 5),
(6, 2, 3),
(7, 2, 2),
(8, 4, 2)

# The ordering is on all the GROUP BY columns, and isn't preserved after the
# aggregation.
query II rowsort
SELECT x, max(y) FROM group_ord GROUP BY x
----
1  2
3  4
5  NULL
6  2
7  2
8  4

# The ordering is on all the GROUP BY columns, and is preserved after the
# aggregation.
query II
SELECT x, max(y) FROM group_ord GROUP BY x ORDER BY x
----
1  2
3  4
5  NULL
6  2
7  2
8  4

# The ordering is on some of the GROUP BY columns, and isn't preserved after
# the aggregation.
query III rowsort
SELECT z, x, max(y) FROM group_ord GROUP BY x, z
----
5  3  4
3  6  2
3  1  2
5  5  NULL
2  7  2
2  8  4

# The ordering is on some of the GROUP BY columns, and is preserved after
# the aggregation.
query III
SELECT z, x, max(y) FROM group_ord GROUP BY x, z ORDER BY x
----
3  1  2
5  3  4
5  5  NULL
3  6  2
2  7  2
2  8  4

# If the underlying ordering isn't from the primary index, it needs to be hinted
# for now.
query II rowsort
SELECT z, max(y) FROM group_ord@foo GROUP BY z
----
5  4
2  4
3  2

# Test that a merge join is used on two aggregate subqueries with orderings on
# the GROUP BY columns. Note that an ORDER BY is not necessary on the
# subqueries.
query IIII rowsort
SELECT * FROM (SELECT x, max(y) FROM group_ord GROUP BY x) JOIN (SELECT z, min(y) FROM group_ord@foo GROUP BY z) ON x = z
----
5  NULL  5  4
3  4     3  2

# Regression test for #23798 until #10495 is fixed.
statement error function reserved for internal use
SELECT final_variance(1.2, 1.2, 123) FROM kv

# Regression test for #25533 (crash when propagating filter through GROUP BY).
query I
SELECT 1 FROM kv GROUP BY v, w::DECIMAL HAVING w::DECIMAL > 1
----
1
1
1
1
1

# Regression test for distsql aggregator crash when using hash aggregation.
query IT rowsort
SELECT v, array_agg('a') FROM kv GROUP BY v
----
2     {a,a,a}
4     {a,a}
NULL  {a}

# Regression test for #26419
query I
SELECT 123 FROM kv ORDER BY max(v)
----
123

subtest string_agg

statement OK
CREATE TABLE string_agg_test (
  id INT PRIMARY KEY,
  company_id INT,
  employee STRING
)

query IT colnames
SELECT company_id, string_agg(employee, ',')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg

query IT colnames
SELECT company_id, string_agg(employee::BYTES, b',')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg

query IT colnames
SELECT company_id, string_agg(employee, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg

query IT colnames
SELECT company_id, string_agg(employee::BYTES, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg

statement OK
INSERT INTO string_agg_test VALUES
  (1, 1, 'A'),
  (2, 2, 'B'),
  (3, 3, 'C'),
  (4, 4, 'D'),
  (5, 3, 'C'),
  (6, 4, 'D'),
  (7, 4, 'D'),
  (8, 4, 'D'),
  (9, 3, 'C'),
  (10, 2, 'B')

query error pq: unimplemented: aggregate functions with multiple non-constant expressions are not supported
SELECT company_id, string_agg(employee, employee)
FROM string_agg_test
GROUP BY company_id;

query IT colnames
SELECT company_id, string_agg(employee, ',')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           A
2           B,B
3           C,C,C
4           D,D,D,D

query IT colnames
SELECT company_id, string_agg(DISTINCT employee, ',')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           A
2           B
3           C
4           D

query IT colnames
SELECT company_id, string_agg(employee::BYTES, b',')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           A
2           B,B
3           C,C,C
4           D,D,D,D

query IT colnames
SELECT company_id, string_agg(employee, '')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           A
2           BB
3           CCC
4           DDDD

query IT colnames
SELECT company_id, string_agg(employee::BYTES, b'')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           A
2           BB
3           CCC
4           DDDD

query IT colnames
SELECT company_id, string_agg(employee, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           A
2           BB
3           CCC
4           DDDD

query IT colnames
SELECT company_id, string_agg(employee::BYTES, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           A
2           BB
3           CCC
4           DDDD

query IT colnames
SELECT company_id, string_agg(NULL::STRING, ',')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           NULL
2           NULL
3           NULL
4           NULL

query IT colnames
SELECT company_id, string_agg(NULL::BYTES, b',')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           NULL
2           NULL
3           NULL
4           NULL

query IT colnames
SELECT company_id, string_agg(NULL::STRING, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           NULL
2           NULL
3           NULL
4           NULL

query IT colnames
SELECT company_id, string_agg(NULL::BYTES, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
company_id  string_agg
1           NULL
2           NULL
3           NULL
4           NULL

query error pq: ambiguous call: string_agg\(unknown, unknown\)
SELECT company_id, string_agg(NULL, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;

# Now test the window function version of string_agg.

query IT colnames
SELECT company_id, string_agg(employee, ',')
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           A
2           B
2           B,B
3           C
3           C,C
3           C,C,C
4           D
4           D,D
4           D,D,D
4           D,D,D,D

query IT colnames
SELECT company_id, string_agg(employee::BYTES, b',')
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           A
2           B
2           B,B
3           C
3           C,C
3           C,C,C
4           D
4           D,D
4           D,D,D
4           D,D,D,D

query IT colnames
SELECT company_id, string_agg(employee, '')
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           A
2           B
2           BB
3           C
3           CC
3           CCC
4           D
4           DD
4           DDD
4           DDDD

query IT colnames
SELECT company_id, string_agg(employee::BYTES, b'')
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           A
2           B
2           BB
3           C
3           CC
3           CCC
4           D
4           DD
4           DDD
4           DDDD

query IT colnames
SELECT company_id, string_agg(employee, NULL)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           A
2           B
2           BB
3           C
3           CC
3           CCC
4           D
4           DD
4           DDD
4           DDDD

query IT colnames
SELECT company_id, string_agg(employee::BYTES, NULL)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           A
2           B
2           BB
3           C
3           CC
3           CCC
4           D
4           DD
4           DDD
4           DDDD

query IT colnames
SELECT company_id, string_agg(NULL::STRING, employee)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           NULL
2           NULL
2           NULL
3           NULL
3           NULL
3           NULL
4           NULL
4           NULL
4           NULL
4           NULL

query IT colnames
SELECT company_id, string_agg(NULL::BYTES, employee::BYTES)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           NULL
2           NULL
2           NULL
3           NULL
3           NULL
3           NULL
4           NULL
4           NULL
4           NULL
4           NULL

query IT colnames
SELECT company_id, string_agg(NULL::STRING, NULL)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           NULL
2           NULL
2           NULL
3           NULL
3           NULL
3           NULL
4           NULL
4           NULL
4           NULL
4           NULL

query IT colnames
SELECT company_id, string_agg(NULL::BYTES, NULL)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           NULL
2           NULL
2           NULL
3           NULL
3           NULL
3           NULL
4           NULL
4           NULL
4           NULL
4           NULL

query IT colnames
SELECT company_id, string_agg(NULL, NULL::STRING)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           NULL
2           NULL
2           NULL
3           NULL
3           NULL
3           NULL
4           NULL
4           NULL
4           NULL
4           NULL

query IT colnames
SELECT company_id, string_agg(NULL, NULL::BYTES)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           NULL
2           NULL
2           NULL
3           NULL
3           NULL
3           NULL
4           NULL
4           NULL
4           NULL
4           NULL

query error pq: ambiguous call: string_agg\(unknown, unknown\)
SELECT company_id, string_agg(NULL, NULL)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;

query IT colnames
SELECT company_id, string_agg(employee, lower(employee))
OVER (PARTITION BY company_id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           A
2           BbB
2           BbB
3           CcCcC
3           CcCcC
3           CcCcC
4           DdDdDdD
4           DdDdDdD
4           DdDdDdD
4           DdDdDdD

query IT colnames
SELECT company_id, string_agg(lower(employee), employee)
OVER (PARTITION BY company_id)
FROM string_agg_test
ORDER BY company_id, id;
----
company_id  string_agg
1           a
2           bBb
2           bBb
3           cCcCc
3           cCcCc
3           cCcCc
4           dDdDdDd
4           dDdDdDd
4           dDdDdDd
4           dDdDdDd

statement error pq: unknown signature: string_agg\(string, string, string\)
SELECT company_id, string_agg(employee, employee, employee)
OVER (PARTITION BY company_id)
FROM string_agg_test
ORDER BY company_id, id;

query error pq: unknown signature: string_agg\(string\)
SELECT company_id, string_agg(employee)
OVER (PARTITION BY company_id)
FROM string_agg_test
ORDER BY company_id, id;

statement OK
TRUNCATE string_agg_test

statement OK
INSERT INTO string_agg_test VALUES
  (1, 1, 'A'),
  (2, 1, 'B'),
  (3, 1, 'C'),
  (4, 1, 'D')

query IT colnames
SELECT e.company_id, string_agg(e.employee, ', ')
FROM (
  SELECT employee, company_id
  FROM string_agg_test
  ORDER BY employee
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;
----
company_id  string_agg
1           A, B, C, D

query IT colnames
SELECT e.company_id, string_agg(e.employee, b', ')
FROM (
  SELECT employee::BYTES, company_id
  FROM string_agg_test
  ORDER BY employee
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;
----
company_id  string_agg
1           A, B, C, D

query IT colnames
SELECT e.company_id, string_agg(e.employee, ', ')
FROM (
  SELECT employee, company_id
  FROM string_agg_test
  ORDER BY employee DESC
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;
----
company_id  string_agg
1           D, C, B, A

query IT colnames
SELECT e.company_id, string_agg(e.employee, b', ')
FROM (
  SELECT employee::BYTES, company_id
  FROM string_agg_test
  ORDER BY employee DESC
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;
----
company_id  string_agg
1           D, C, B, A

query IT colnames
SELECT e.company_id, string_agg(e.employee, NULL)
FROM (
  SELECT employee, company_id
  FROM string_agg_test
  ORDER BY employee DESC
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;
----
company_id  string_agg
1           DCBA

query IT colnames
SELECT e.company_id, string_agg(e.employee, NULL)
FROM (
  SELECT employee::BYTES, company_id
  FROM string_agg_test
  ORDER BY employee DESC
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;
----
company_id  string_agg
1           DCBA

statement OK
DROP TABLE string_agg_test

# Regression test for #28836.

query T
SELECT string_agg('foo', CAST ((SELECT NULL) AS BYTES)) OVER ();
----
foo

# Regression test for #30166.
query T
SELECT array_agg(generate_series(1, 2))
----
{1,2}

# Regression test for #31882.

statement ok
CREATE TABLE uvw (u INT, v INT, w INT, INDEX uvw(u, v, w))

statement ok
INSERT INTO uvw VALUES (1, 2, 3), (1, 2, 3), (3, 2, 1), (3, 2, 3)

query IIT rowsort
SELECT u, v, array_agg(w) AS s FROM (SELECT * FROM uvw ORDER BY w) GROUP BY u, v
----
3  2  {1,3}
1  2  {3,3}

# Regression test for #36433: don't panic with count_agg if a post-render produces an error.

query error lpad
SELECT count(*)::TEXT||lpad('foo', 23984729388383834723984) FROM (VALUES(1));

statement ok
CREATE TABLE tab (
  col1 INT PRIMARY KEY,
  col2 INT,
  col3 STRING
)

# Ordered aggregations when there are no rows.
query I
SELECT array_agg(col1 ORDER BY col2) FROM TAB
----
NULL

statement ok
INSERT INTO tab VALUES (-3, 7, 'a'), (-2, 6, 'a'), (-1, 5, 'a'), (0, 7, 'b'), (1, 5, 'b'), (2, 6, 'b')

query T colnames
SELECT array_agg(col1 ORDER BY col1) FROM tab
----
array_agg
{-3,-2,-1,0,1,2}

query T colnames
SELECT array_agg(col1 ORDER BY col2*100+col1) FROM tab
----
array_agg
{-1,1,-2,2,-3,0}

query T colnames
SELECT json_agg(col1 ORDER BY col1) FROM tab
----
json_agg
[-3, -2, -1, 0, 1, 2]

query T colnames
SELECT jsonb_agg(col1 ORDER BY col1) FROM tab
----
jsonb_agg
[-3, -2, -1, 0, 1, 2]

query T colnames
SELECT jsonb_agg(col1 ORDER BY col2, col1) FROM tab
----
jsonb_agg
[-1, 1, -2, 2, -3, 0]

query T colnames
SELECT concat_agg(col3 ORDER BY col1) FROM tab
----
concat_agg
aaabbb

query T colnames
SELECT concat_agg(col3 ORDER BY col1 DESC) FROM tab
----
concat_agg
bbbaaa

query T colnames
SELECT string_agg(col3, ', ' ORDER BY col3) FROM tab
----
string_agg
a, a, a, b, b, b

query T colnames
SELECT string_agg(col3, ', ' ORDER BY col3 DESC) FROM tab
----
string_agg
b, b, b, a, a, a

query TTT colnames
SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2, col1), array_agg(col1 ORDER BY col3, col1) FROM tab
----
array_agg         array_agg         array_agg
{-3,-2,-1,0,1,2}  {-1,1,-2,2,-3,0}  {-3,-2,-1,0,1,2}

query TTT colnames
SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2, col1), col3 FROM tab GROUP BY col3 ORDER BY col3
----
array_agg   array_agg   col3
{-3,-2,-1}  {-1,-2,-3}  a
{0,1,2}     {1,2,0}     b

query TTII colnames
SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col2, col1), count(col3), count(*) FROM tab
----
array_agg         array_agg         count  count
{-3,-2,-1,0,1,2}  {-1,1,-2,2,-3,0}  6      6

query TT colnames
SELECT array_agg(col1 ORDER BY col1), array_agg(col1 ORDER BY col1) FILTER (WHERE col1 < 0) FROM tab
----
array_agg         array_agg
{-3,-2,-1,0,1,2}  {-3,-2,-1}

query TT colnames
SELECT array_agg(col1 ORDER BY col3, col1) FILTER (WHERE col1 < 0), array_agg(col1 ORDER BY col3, col1) FROM tab
----
array_agg   array_agg
{-3,-2,-1}  {-3,-2,-1,0,1,2}

query IT
SELECT count(1), concat_agg(col3 ORDER BY col1) from tab
----
6  aaabbb

# Testing pre-projections. Tests when the GroupBy clause has a projection.
query IIIT colnames
SELECT
    *
FROM
    (
        SELECT
            count(1) AS count_1,
            count(lower(col3)) AS count_lower,
            count(upper(col3)) AS count_upper,
            concat_agg(col3 ORDER BY col1) AS concat
        FROM
            tab
        GROUP BY
            upper(col3)
    )
ORDER BY
    concat
----
count_1  count_lower  count_upper  concat
3        3            3            aaa
3        3            3            bbb

# Tests for selecting any columns when grouping by the PK.
statement ok
DELETE FROM ab WHERE true;
INSERT INTO ab VALUES (1,1), (2,1), (3,3), (4, 7)

query I rowsort
SELECT b FROM ab GROUP BY a
----
1
1
3
7

query II rowsort
SELECT a+b, count(*) FROM ab JOIN tab ON b=col2 GROUP BY a
----
11 2

query IIII rowsort
SELECT a, col1, b+col2, count(*) FROM ab JOIN tab ON b=col2 GROUP BY a, col1
----
4  -3  14  1
4  0   14  1

query IIII rowsort
SELECT a, b, count(*), count(col2) FROM ab LEFT JOIN tab ON b=col2 GROUP BY a
----
1  1  1  0
2  1  1  0
3  3  1  0
4  7  2  2

query III rowsort
SELECT a, b, count(*) FROM ab RIGHT JOIN tab ON b=col2 GROUP BY a
----
NULL  NULL  4
4     7     2