Materialize/test/sqllogictest/cockroach/aggregate.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/aggregate
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

subtest other

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

statement ok
CREATE TABLE kv (
  k INT PRIMARY KEY,
  v INT,
  w INT,
  s TEXT
)

# Aggregate functions return NULL if there are no rows.
query IIIRRRR
SELECT min(1), max(1), count(1), avg(1), sum(1), stddev(1), variance(1) FROM kv
----
NULL NULL 0 NULL NULL NULL NULL

# Regression test for materialize#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

statement error db error: ERROR: function "json_agg" does not exist
SELECT json_agg(1) FROM kv
----
NULL

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

query IIIRRRR
SELECT min(v), max(v), count(v), avg(v), sum(v), stddev(v), variance(v) FROM kv
----
NULL NULL 0 NULL NULL NULL NULL

query B
SELECT bool_and(v = 1) FROM kv
----
NULL

query B
SELECT bool_or(v = 1) FROM kv
----
NULL

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

statement error db error: ERROR: function "json_agg" does not exist
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 IIIRRRR
SELECT min(1), count(1), max(1), avg(1)::float, sum(1), stddev(1), variance(1)
----
1 1 1 1 1 NULL NULL

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

statement error db error: ERROR: function "json_agg" does not exist
SELECT json_agg(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

statement error db error: ERROR: function "json_agg" does not exist
SELECT json_agg(NULL)

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

query error db error: ERROR: function array_agg\(unknown\) is not unique
SELECT array_agg(NULL)

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

# Regression test for materialize#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

# 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. (materialize#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'),
(3, 4, 5, 'a'),
(5, NULL, 5, NULL),
(6, 2, 3, 'b'),
(7, 2, 2, 'b'),
(8, 4, 2, 'A')

# Aggregate functions triggers aggregation and computation for every row even when applied to a constant.
query IIIRRRR
SELECT min(1), count(1), max(1), avg(1)::float, sum(1), stddev(1)::float, variance(1)::float FROM kv
----
1 6 1 1 6 0 0

# 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}

statement error db error: ERROR: function "json_agg" does not exist
SELECT json_agg(1) FROM kv

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 column "kv.k" must appear in the GROUP BY clause or be used in an aggregate function
SELECT count(*), k FROM kv

# database-issues#1036
# 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 clause \(function pg_catalog.sum\)
INSERT INTO kv (k, v) VALUES (99, 100) RETURNING sum(v)

query error aggregate functions are not allowed in LIMIT \(function pg_catalog.sum\)
SELECT sum(v) FROM kv GROUP BY k LIMIT sum(v)

query error db error: ERROR: aggregate functions are not allowed in OFFSET \(function pg_catalog\.sum\)
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 column reference 5 in GROUP BY clause is out of range \(1 - 2\)
SELECT count(*), k FROM kv GROUP BY 5

query error pgcode 42P10 column reference 0 in GROUP BY clause is out of range \(1 - 2\)
SELECT count(*), k FROM kv GROUP BY 0

# unsure about spec, but this is consistent with our stance of always treating GROUP BY as an expr
# 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(*), length(s) FROM kv GROUP BY length(s)
----
1 NULL
5 1

# 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(*), length(s) FROM kv GROUP BY s
----
1 1
1 NULL
2 1
2 1

# Selecting a value that is not grouped, even if a function of it it, does not work.
query error column "kv.s" must appear in the GROUP BY clause or be used in an aggregate function
SELECT count(*), s FROM kv GROUP BY length(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 error column "kv.v" must appear in the GROUP BY clause or be used in an aggregate function
SELECT count(*), k+v FROM kv GROUP BY k

query error column "kv.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 "kv.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 nested aggregate functions are not allowed
SELECT max(avg(k)) FROM kv

# Test case from materialize#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
  length(s),
  count(DISTINCT s),
  count(DISTINCT length(s))
FROM kv GROUP BY length(s) HAVING count(DISTINCT s) > 1
----
1 3 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 nested aggregate functions are not allowed
SELECT max(k), min(v) FROM kv HAVING max(min(v)) > 2

query error column "kv.k" must appear in the GROUP BY clause or be used in an aggregate function
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 "kv.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

# pg has a special case for grouping on primary key, which would allow this, but we do not.
# See http://www.postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY
query error column "kv.v" must appear in the GROUP BY clause or be used in an aggregate function
SELECT 3 FROM kv GROUP BY k HAVING v > 2

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

query error Expected right parenthesis, found comma
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 db error: ERROR: function count\(integer, integer\) does not exist
SELECT count(k, v) FROM kv

# Note: Result differs from Cockroach but matches Postgres.
query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY v
----
2 3
4 2
NULL 1

# Note: Result differs from Cockroach but matches Postgres.
query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY v DESC
----
NULL 1
4 2
2 3

# Note: Result differs from Cockroach but matches Postgres.
query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY count(k) DESC
----
2 3
4 2
NULL 1

# Note: Result differs from Cockroach but matches Postgres.
query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY v-count(k)
----
2 3
4 2
NULL 1

# Note: Result differs from Cockroach but matches Postgres.
query II
SELECT v, count(k) FROM kv GROUP BY v ORDER BY 1 DESC
----
NULL 1
4 2
2 3

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 length(s), count(DISTINCT k), count(DISTINCT v), count(DISTINCT (v)) FROM kv GROUP BY length(s)
----
1    5 2 2
NULL 1 0 0

# database-issues#414
# 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((k, v)) FROM kv LIMIT 1
# ----
# 6
#
# query I
# SELECT count((k, v)) FROM kv OFFSET 1
# ----
#
# query II
# SELECT count(NULL::int), count((NULL, NULL))
# ----
# 0 1


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)+count(kv.v) FROM kv
----
11

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,a,b,b,NULL}

query error db error: ERROR: operator does not exist: integer\[\] \|\| integer
SELECT array_agg(k) || 1 FROM (SELECT k FROM kv ORDER BY k)

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

query error db error: ERROR: function "json_agg" does not exist
SELECT json_agg(s) FROM kv WHERE s IS NULL

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

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

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

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

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

# 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)::FLOAT FROM kv WHERE w*2 = k
----
14

# 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
)

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

query TRB
SELECT min(a), min(b), min(c) FROM abc
----
one 1.5 false

query TRB
SELECT max(a), max(b), max(c) FROM abc
----
two 2 true

query RR
SELECT avg(b), sum(b) FROM abc
----
1.75 3.5

# not supported yet
# 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 db error: ERROR: function sum\(varchar\) does not exist
SELECT avg(a) FROM abc

query error db error: ERROR: function sum\(boolean\) does not exist
SELECT avg(c) FROM abc

query error db error: ERROR: function sum\(record\(f1: varchar,f2: boolean\?\)\) does not exist
SELECT avg((a,c)) FROM abc

query error db error: ERROR: function sum\(varchar\) does not exist
SELECT sum(a) FROM abc

query error db error: ERROR: function sum\(boolean\) does not exist
SELECT sum(c) FROM abc

query error db error: ERROR: function sum\(record\(f1: varchar,f2: boolean\?\)\) does not exist
SELECT sum((a,c)) FROM abc

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

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

# database-issues#414
# 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)::FLOAT, variance(y::decimal)::FLOAT, variance(z)::DECIMAL(38, 14) FROM xyz
----
9  4.5  6.33333333333334

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)::FLOAT, stddev(y::decimal)::FLOAT, stddev(z)::DECIMAL(38, 14) FROM xyz
----
3  2.1213203435596424  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
)

# not supported yet
# 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 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 error concat_agg not yet supported
SELECT concat_agg(s) FROM (SELECT s FROM kv ORDER BY k)

query error db error: ERROR: function "json_agg" does not exist
SELECT json_agg(s) FROM (SELECT s FROM kv ORDER BY k)

# This ORDER BY doesn't have to be respected, because the relation returned by a subquery is an inherently unordered
# thing. Postgres docs: https://www.postgresql.org/docs/13/functions-aggregate.html
# which says that "supplying the input values from a sorted subquery will usually work", which sounds like this is
# indeed not mandated.
query T
SELECT jsonb_agg(s) FROM (SELECT s FROM kv ORDER BY k)
----
["A","a","a","b","b",null]

# 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 Expected end of statement, found left parenthesis
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.b) FROM kv JOIN abc ON kv.v > abc.b GROUP BY kv.*
----
1.5
1.5
1.5
3.5
3.5

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

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

statement ok
INSERT INTO ab(a,b) VALUES (1,2), (3,4);

statement ok
CREATE TABLE xy(x TEXT, y TEXT);

statement ok
INSERT INTO xy(x, y) VALUES ('a', 'b'), ('c', 'd')

# database-issues#414
# 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
)

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

# Regression test for materialize#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 error db error: ERROR: function array_agg\(unknown\) is not unique
SELECT v, array_agg('a') FROM kv GROUP BY v

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 TEXT
)

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

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

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 TT
SELECT company_id, string_agg(employee, employee)
FROM string_agg_test
GROUP BY company_id;
----
1  A
2  BBB
3  CCCCC
4  DDDDDDD

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

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

query error type "b" does not exist
SELECT company_id, string_agg(employee::BYTEA, b',')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;

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

query error type "b" does not exist
SELECT company_id, string_agg(employee::BYTEA, b'')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;

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

query error supported
SELECT company_id, string_agg(employee::BYTEA, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;

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

query error supported
SELECT company_id, string_agg(NULL::BYTEA, ',')
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;

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

query error supported
SELECT company_id, string_agg(NULL::BYTEA, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;

query TT
SELECT company_id, string_agg(NULL, NULL)
FROM string_agg_test
GROUP BY company_id
ORDER BY company_id;
----
1  NULL
2  NULL
3  NULL
4  NULL

# Now test the window function version of string_agg.

query IT
SELECT company_id, string_agg(employee, ',')
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;
----
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 error type "b" does not exist
SELECT company_id, string_agg(employee::BYTEA, b',')
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;

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

query error type "b" does not exist
SELECT company_id, string_agg(employee::BYTEA, b'')
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;

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

query error string_agg on BYTEA not yet supported
SELECT company_id, string_agg(employee::BYTEA, NULL)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;

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

query error string_agg on BYTEA not yet supported
SELECT company_id, string_agg(NULL::BYTEA, employee::BYTEA)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;

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

query error string_agg on BYTEA not yet supported
SELECT company_id, string_agg(NULL::BYTEA, NULL)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;

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

query error string_agg on BYTEA not yet supported
SELECT company_id, string_agg(NULL, NULL::BYTEA)
OVER (PARTITION BY company_id ORDER BY id)
FROM string_agg_test
ORDER BY company_id, id;

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

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

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

query error db error: ERROR: function string_agg\(text, text, text\) does not exist
SELECT company_id, string_agg(employee, employee, employee)
OVER (PARTITION BY company_id)
FROM string_agg_test
ORDER BY company_id, id;

query error db error: ERROR: function string_agg\(text\) does not exist
SELECT company_id, string_agg(employee)
OVER (PARTITION BY company_id)
FROM string_agg_test
ORDER BY company_id, id;

statement OK
CREATE TABLE string_agg_test2 (
  id INT PRIMARY KEY,
  company_id INT,
  employee TEXT
)

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

query TT
SELECT e.company_id, string_agg(e.employee, ', ')
FROM (
  SELECT employee, company_id
  FROM string_agg_test2
  ORDER BY employee
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;
----
1  A,␠B,␠C,␠D

query error type "b" does not exist
SELECT e.company_id, string_agg(e.employee, b', ')
FROM (
  SELECT employee::BYTEA, company_id
  FROM string_agg_test2
  ORDER BY employee
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;

# This will differ from PG until we close https://github.com/MaterializeInc/database-issues/issues/843
query TT
SELECT e.company_id, string_agg(e.employee, ', ')
FROM (
  SELECT employee, company_id
  FROM string_agg_test2
  ORDER BY employee DESC
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;
----
1  A,␠B,␠C,␠D

query error type "b" does not exist
SELECT e.company_id, string_agg(e.employee, b', ')
FROM (
  SELECT employee::BYTEA, company_id
  FROM string_agg_test2
  ORDER BY employee DESC
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;

# This will differ from PG until we close https://github.com/MaterializeInc/database-issues/issues/843
query TT
SELECT e.company_id, string_agg(e.employee, NULL)
FROM (
  SELECT employee, company_id
  FROM string_agg_test2
  ORDER BY employee DESC
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;
----
1  ABCD

query error supported
SELECT e.company_id, string_agg(e.employee, NULL)
FROM (
  SELECT employee::BYTEA, company_id
  FROM string_agg_test2
  ORDER BY employee DESC
  ) AS e
GROUP BY e.company_id
ORDER BY e.company_id;

statement OK
DROP TABLE string_agg_test2

# Regression test for materialize#28836.

query error supported
SELECT string_agg('foo', CAST ((SELECT NULL) AS BYTEA)) OVER ();

query error table functions are not allowed in aggregate function calls
SELECT array_agg(generate_series(1, 2))

# Regression test for cockroach#31882.

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

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

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

# Regression test for cockroach#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));