distsql_agg

# LogicTest: 5node-default-configs

statement ok
CREATE TABLE data (a INT, b INT, c FLOAT, d DECIMAL, PRIMARY KEY (a, b, c, d))

# Split into ten parts.
statement ok
ALTER TABLE data SPLIT AT SELECT i FROM generate_series(1, 9) AS g(i)

# Relocate the ten parts to the five nodes.
statement ok
ALTER TABLE data EXPERIMENTAL_RELOCATE
  SELECT ARRAY[i%5+1], i FROM generate_series(0, 9) AS g(i)

# Generate all combinations of values 1 to 10.
statement ok
INSERT INTO data SELECT a, b, c::FLOAT, d::DECIMAL FROM
   generate_series(1, 10) AS a(a),
   generate_series(1, 10) AS b(b),
   generate_series(1, 10) AS c(c),
   generate_series(1, 10) AS d(d)

# Verify data placement.
query TTTI colnames,rowsort
SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE data]
----
start_key  end_key  replicas  lease_holder
NULL       /1       {1}       1
/1         /2       {2}       2
/2         /3       {3}       3
/3         /4       {4}       4
/4         /5       {5}       5
/5         /6       {1}       1
/6         /7       {2}       2
/7         /8       {3}       3
/8         /9       {4}       4
/9         NULL     {5}       5

query RI
SELECT sum(a), sum_int(a) FROM data
----
55000 55000

query R
SELECT sum((a-1)*1000 + (b-1)*100 + (c::INT-1)*10 + (d-1)) FROM data
----
49995000

query I
SELECT sum_int((a-1)*1000 + (b-1)*100 + (c::INT-1)*10 + (d::INT-1)) FROM data
----
49995000

query RIII
SELECT sum(a), sum_int(a), count(a), max(a) FROM data
----
55000 55000 10000 10

query RIII
SELECT sum(a+b), sum_int(a+b), count(a+b), max(a+b) FROM data
----
110000 110000 10000 20

query R
SELECT sum((a-1)*1000) + sum((b-1)*100) + sum((c::INT-1)*10) + sum(d-1) FROM data
----
49995000

query I
SELECT sum_int((a-1)*1000) + sum_int((b-1)*100) + sum_int((c::INT-1)*10) + sum_int(d::INT-1) FROM data
----
49995000

query RIRI
SELECT sum(a), min(b), max(c), count(d) FROM data
----
55000 1 10 10000

query R
SELECT avg(a+b+c::INT+d) FROM data
----
22

query RR
SELECT sum(a), round(stddev(b), 1) FROM data
----
55000 2.9

query RR
SELECT sum(a), round(variance(b), 1) FROM data
----
55000 8.3

query R
SELECT stddev(a+b+c::INT+d) FROM data
----
5.7448498962142608187

query R
SELECT variance(a+b+c::INT+d) FROM data
----
33.0033003300330033

query RIRRRRRR
SELECT sum(a), sum_int(a), avg(b), sum(c), avg(d), stddev(a), variance(b), sum(a+b+c::INT+d) FROM data
----
55000 55000 5.5 55000 5.5 2.8724249481071304094 8.2508250825082508251 220000

query RIRIRRR
SELECT sum(a), min(b), max(c), count(d), avg(a+b+c::INT+d), stddev(a+b), variance(c::INT+d) FROM data
----
55000 1 10 10000 22 4.0622223185119375800 16.50165016501650165

query RRRIRRRR
SELECT sum(a), stddev(a), avg(a) FILTER (WHERE a > 5), count(b), avg(b), variance(b) FILTER (WHERE b < 8), sum(b) FILTER (WHERE b < 8), stddev(b) FILTER (WHERE b > 2) FROM data
----
55000 2.8724249481071304094 8 10000 5.5 4.0005715102157451064 28000 2.2914310663953007487

query RRR
SELECT sum(a), avg(DISTINCT a), variance(a) FILTER (WHERE a > 0) FROM data
----
55000 5.5 8.2508250825082508251

query RRIRR
SELECT sum(a), avg(a), count(a), stddev(a), variance(a) FROM data
----
55000 5.5 10000 2.8724249481071304094 8.2508250825082508251

query RRRRR
SELECT sum(a), avg(b), sum(a), sum(a), avg(b) FROM data
----
55000 5.5 55000 55000 5.5

query RRIRR
SELECT avg(c), sum(c), sum_int(c::INT), avg(d), sum(d) FROM data
----
5.5 55000 55000 5.5 55000

query II
SELECT max(a), min(b) FROM data HAVING min(b) > 2
----


query I rowsort
SELECT DISTINCT (a) FROM data
----
1
2
3
4
5
6
7
8
9
10

query RI
SELECT sum(DISTINCT a), sum_int(DISTINCT a) FROM data
----
55 55

query RIRI
SELECT sum(DISTINCT a), sum_int(DISTINCT a), sum(DISTINCT b), sum_int(DISTINCT b) from data
----
55 55 55 55

query II
SELECT DISTINCT a, b FROM data WHERE (a + b + c::INT) = 27 ORDER BY a,b
----
7   10
8   9
8   10
9   8
9   9
9   10
10  7
10  8
10  9
10  10

query II
SELECT DISTINCT a, b FROM data WHERE (a + b + c::INT) = 27 ORDER BY b,a
----
10  7
9   8
10  8
8   9
9   9
10  9
7  10
8  10
9  10
10 10

query RRRI
SELECT c, d, sum(a+c::INT) + avg(b+d), sum_int(a+c::INT) + avg(b+d)::INT FROM data GROUP BY c, d ORDER BY c, d
----
1   1   656.5   657
1   2   657.5   658
1   3   658.5   659
1   4   659.5   660
1   5   660.5   661
1   6   661.5   662
1   7   662.5   663
1   8   663.5   664
1   9   664.5   665
1   10  665.5   666
2   1   756.5   757
2   2   757.5   758
2   3   758.5   759
2   4   759.5   760
2   5   760.5   761
2   6   761.5   762
2   7   762.5   763
2   8   763.5   764
2   9   764.5   765
2   10  765.5   766
3   1   856.5   857
3   2   857.5   858
3   3   858.5   859
3   4   859.5   860
3   5   860.5   861
3   6   861.5   862
3   7   862.5   863
3   8   863.5   864
3   9   864.5   865
3   10  865.5   866
4   1   956.5   957
4   2   957.5   958
4   3   958.5   959
4   4   959.5   960
4   5   960.5   961
4   6   961.5   962
4   7   962.5   963
4   8   963.5   964
4   9   964.5   965
4   10  965.5   966
5   1   1056.5  1057
5   2   1057.5  1058
5   3   1058.5  1059
5   4   1059.5  1060
5   5   1060.5  1061
5   6   1061.5  1062
5   7   1062.5  1063
5   8   1063.5  1064
5   9   1064.5  1065
5   10  1065.5  1066
6   1   1156.5  1157
6   2   1157.5  1158
6   3   1158.5  1159
6   4   1159.5  1160
6   5   1160.5  1161
6   6   1161.5  1162
6   7   1162.5  1163
6   8   1163.5  1164
6   9   1164.5  1165
6   10  1165.5  1166
7   1   1256.5  1257
7   2   1257.5  1258
7   3   1258.5  1259
7   4   1259.5  1260
7   5   1260.5  1261
7   6   1261.5  1262
7   7   1262.5  1263
7   8   1263.5  1264
7   9   1264.5  1265
7   10  1265.5  1266
8   1   1356.5  1357
8   2   1357.5  1358
8   3   1358.5  1359
8   4   1359.5  1360
8   5   1360.5  1361
8   6   1361.5  1362
8   7   1362.5  1363
8   8   1363.5  1364
8   9   1364.5  1365
8   10  1365.5  1366
9   1   1456.5  1457
9   2   1457.5  1458
9   3   1458.5  1459
9   4   1459.5  1460
9   5   1460.5  1461
9   6   1461.5  1462
9   7   1462.5  1463
9   8   1463.5  1464
9   9   1464.5  1465
9   10  1465.5  1466
10  1   1556.5  1557
10  2   1557.5  1558
10  3   1558.5  1559
10  4   1559.5  1560
10  5   1560.5  1561
10  6   1561.5  1562
10  7   1562.5  1563
10  8   1563.5  1564
10  9   1564.5  1565
10  10  1565.5  1566

# Test plans with empty streams.
statement ok
CREATE TABLE one (k INT PRIMARY KEY, v INT)

statement ok
ALTER TABLE one SPLIT AT VALUES (0), (99)

statement ok
ALTER TABLE one EXPERIMENTAL_RELOCATE VALUES (ARRAY[1], 0)

statement ok
INSERT INTO one VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,10)

statement ok
CREATE TABLE two (k INT PRIMARY KEY, v INT);

statement ok
ALTER TABLE two SPLIT AT VALUES (0), (99)

statement ok
ALTER TABLE two EXPERIMENTAL_RELOCATE VALUES (ARRAY[2], 0)

statement ok
INSERT INTO two VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,10)

query TTTI colnames,rowsort
SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE one]
----
start_key  end_key  replicas  lease_holder
NULL       /0       {5}       5
/0         /99      {1}       1
/99        NULL     {5}       5

query TTTI colnames,rowsort
SELECT start_key, end_key, replicas, lease_holder FROM [SHOW RANGES FROM TABLE two]
----
start_key  end_key  replicas  lease_holder
NULL       /0       {5}       5
/0         /99      {2}       2
/99        NULL     {5}       5

query I
SELECT count(*) FROM one AS a, one AS b, two AS c
----
1000

query RRR
SELECT sum(a), sum(b), sum(c) FROM data GROUP BY d HAVING sum(a+b) > 10
----
5500  5500  5500
5500  5500  5500
5500  5500  5500
5500  5500  5500
5500  5500  5500
5500  5500  5500
5500  5500  5500
5500  5500  5500
5500  5500  5500
5500  5500  5500


query RR rowsort
SELECT avg(a+b), c FROM data GROUP BY c, d HAVING c = d
----
11  1
11  2
11  3
11  4
11  5
11  6
11  7
11  8
11  9
11  10

query RRIR rowsort
SELECT sum(a+b), sum(a+b) FILTER (WHERE a < d), sum_int(a+b) FILTER (WHERE a < d), sum(a+b) FILTER (WHERE a = c) FROM data GROUP BY d
----
11000  NULL  NULL 1100
11000  650   650  1100
11000  1400  1400 1100
11000  3200  3200 1100
11000  2250  2250 1100
11000  4250  4250 1100
11000  5400  5400 1100
11000  6650  6650 1100
11000  8000  8000 1100
11000  9450  9450 1100

# Same query but restricted to a single range; no local aggregation stage.
query RRIR rowsort
SELECT sum(a+b), sum(a+b) FILTER (WHERE a < d), sum_int(a+b) FILTER (WHERE a < d), sum(a+b) FILTER (WHERE a = c) FROM data WHERE a = 1 GROUP BY d
----
650  NULL  NULL 65
650  650   650  65
650  650   650  65
650  650   650  65
650  650   650  65
650  650   650  65
650  650   650  65
650  650   650  65
650  650   650  65
650  650   650  65

query IIRT
VALUES (1, 2, 1.0, 'string1'), (4, 3, 2.3, 'string2')
----
1 2 1.0 string1
4 3 2.3 string2

query IIR
SELECT max(t.a), min(t.b), avg(t.c) FROM (VALUES (1, 2, 3), (4, 5, 6), (7, 8, 0)) AS t(a, b, c) WHERE b > 3
----
7 5 3

query ITIR
SELECT * FROM (VALUES (1, '222'), (2, '444')) t1(a,b) JOIN (VALUES (1, 100.0), (3, 32.0)) t2(a,b) ON t1.a = t2.a
----
1 222 1 100.0

statement ok
CREATE TABLE nullables (a INT, b INT, c INT, PRIMARY KEY (a))

statement ok
INSERT INTO nullables VALUES (1,1,1)

statement ok
INSERT INTO nullables VALUES (2,NULL,1)

query II
SELECT c, count(*) FROM nullables GROUP BY c;
----
1 2

query T
SELECT array_agg(a) FROM (SELECT a FROM data WHERE b = 1 AND c = 1.0 AND d = 1.0 ORDER BY a)
----
{1,2,3,4,5,6,7,8,9,10}

query T
SELECT array_agg(ab) FROM (SELECT a*b AS ab FROM data WHERE c = 1.0 AND d = 1.0 ORDER BY a*b)
----
{1,2,2,3,3,4,4,4,5,5,6,6,6,6,7,7,8,8,8,8,9,9,9,10,10,10,10,12,12,12,12,14,14,15,15,16,16,16,18,18,18,18,20,20,20,20,21,21,24,24,24,24,25,27,27,28,28,30,30,30,30,32,32,35,35,36,36,36,40,40,40,40,42,42,45,45,48,48,49,50,50,54,54,56,56,60,60,63,63,64,70,70,72,72,80,80,81,90,90,100}

query T
SELECT json_agg(a) FROM (SELECT a FROM data WHERE b = 1 AND c = 1.0 AND d = 1.0 ORDER BY a)
----
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

query T
SELECT jsonb_agg(a) FROM (SELECT a FROM data WHERE b = 1 AND c = 1.0 AND d = 1.0 ORDER BY a)
----
[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

# Test that orderings on GROUP BY columns are propagated through aggregations.
statement ok
CREATE TABLE sorted_data (a INT PRIMARY KEY, b INT, c FLOAT, INDEX foo(b))

statement ok
INSERT INTO sorted_data VALUES
(1, 4, 5.0),
(2, 3, 3.4),
(3, 9, 2.2),
(4, 13, 1.99),
(5, 2, 5.7),
(6, 7, 6.2),
(7, 9, 8.9),
(8, 1, 1.22),
(9, -2, 23.0),
(10, 100, -3.1)

# Split into ten parts.
statement ok
ALTER TABLE sorted_data SPLIT AT SELECT i FROM generate_series(1, 9) AS g(i)

# Relocate the ten parts to the five nodes.
statement ok
ALTER TABLE sorted_data EXPERIMENTAL_RELOCATE
  SELECT ARRAY[i%5+1], i FROM generate_series(0, 9) AS g(i)

# The ordering is on all the GROUP BY columns, and isn't preserved after the
# aggregation.
query II rowsort
SELECT a, max(b) FROM sorted_data GROUP BY a
----
6   7
7   9
10  100
1   4
2   3
3   9
4   13
5   2
8   1
9   -2

# The ordering is on all the GROUP BY columns, and is preserved after the
# aggregation.
query II
SELECT a, max(b) FROM sorted_data GROUP BY a ORDER BY a
----
1   4
2   3
3   9
4   13
5   2
6   7
7   9
8   1
9   -2
10  100

# The ordering is on some of the GROUP BY columns, and isn't preserved after
# the aggregation.
query RII rowsort
SELECT c, min(b), a FROM sorted_data GROUP BY a, c
----
8.9   9    7
1.99  13   4
1.22  1    8
3.4   3    2
2.2   9    3
-3.1  100  10
23    -2   9
5     4    1
5.7   2    5
6.2   7    6

# The ordering is on some of the GROUP BY columns, and is preserved after
# the aggregation.
query RII
SELECT c, min(b), a FROM sorted_data GROUP BY a, c ORDER BY a
----
5     4    1
3.4   3    2
2.2   9    3
1.99  13   4
5.7   2    5
6.2   7    6
8.9   9    7
1.22  1    8
23    -2   9
-3.1  100  10

# If the underlying ordering isn't from the primary index, it needs to be hinted
# for now.
query IR rowsort
SELECT b, max(c) FROM sorted_data@foo GROUP BY b
----
-2   23
1    1.22
2    5.7
3    3.4
4    5
7    6.2
9    8.9
13   1.99
100  -3.1

# 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 IRIR rowsort
SELECT * FROM (SELECT a, max(c) FROM sorted_data GROUP BY a) JOIN (SELECT b, min(c) FROM sorted_data@foo GROUP BY b) ON a = b
----
1  5     1  1.22
2  3.4   2  5.7
3  2.2   3  3.4
4  1.99  4  5
9  23    9  2.2
7  8.9   7  6.2

# Test that zeroNode is being handled correctly.
query R
SELECT sum(a) FROM data WHERE FALSE
----
NULL

# Test that statistics aggregate functions.
statement ok
CREATE TABLE statistics_agg_test (y INT, x INT)

statement ok
INSERT INTO statistics_agg_test SELECT y, y%10 FROM generate_series(1, 100) AS y

query FFF
SELECT corr(y, x)::decimal, covar_pop(y, x)::decimal, covar_samp(y, x)::decimal FROM statistics_agg_test
----
0.045228963191363145 3.75 3.787878787878788

query FFF
SELECT regr_intercept(y, x), regr_r2(y, x), regr_slope(y, x) FROM statistics_agg_test
----
48.4545454545455 0.00204565911136568 0.454545454545455

query FFF
SELECT regr_sxx(y, x), regr_sxy(y, x), regr_syy(y, x) FROM statistics_agg_test
----
825 375 83325

query I
SELECT regr_count(y, x) FROM statistics_agg_test
----
100

query FF
SELECT regr_avgx(y, x), regr_avgy(y, x) FROM statistics_agg_test
----
4.5 50.5

# Regression test for #37211 (incorrect ordering between aggregator stages).
statement ok
CREATE TABLE uv (u INT PRIMARY KEY, v INT);
INSERT INTO uv SELECT x, x*10 FROM generate_series(2, 8) AS g(x);

query R
SELECT sum(v) FROM data INNER LOOKUP JOIN uv ON (a=u) GROUP BY u ORDER BY u
----
20000
30000
40000
50000
60000
70000
80000

statement ok
CREATE TABLE t55837 (
  i  int primary key,
  y  float,
  x  float
);
INSERT INTO t55837 (i, y, x) VALUES
  (1, 1.0,   1),
  (2, 1.0,   1),
  (3, 2.0,   3),
  (4, 3.0,   3);
ALTER TABLE t55837 SPLIT AT VALUES (3);
ALTER TABLE t55837 EXPERIMENTAL_RELOCATE VALUES (ARRAY[3], 2);
SELECT * FROM t55837 -- make sure that the range cache is populated

# Regression test for incorrectly planning a local distinct stage (#55837).
query FI
SELECT corr(DISTINCT y, x), count(y) FROM t55837
----
0.866025403784439 4

# Regression test for incorrectly populating the type schema produced by the
# final stage of aggregators (#58683).
statement ok
CREATE TABLE table58683_1 (col1 INT8 PRIMARY KEY);
INSERT INTO table58683_1 SELECT i FROM generate_series(1, 5) AS g(i);
ALTER TABLE table58683_1 SPLIT AT SELECT i FROM generate_series(1, 5) AS g(i);
ALTER TABLE table58683_1 EXPERIMENTAL_RELOCATE SELECT ARRAY[i], i FROM generate_series(1, 5) AS g(i);
CREATE TABLE table58683_2 (col2 BOOL);
ALTER TABLE table58683_2 EXPERIMENTAL_RELOCATE SELECT ARRAY[2], 2;
SELECT every(col2) FROM table58683_1 JOIN table58683_2 ON col1 = (table58683_2.tableoid)::INT8 GROUP BY col2 HAVING bool_and(col2);

# Test muti-stage aggregate functions for #58347 (add support for multi-stage execution of aggregate functions).
statement ok
CREATE TABLE t (a INT, b INT, c FLOAT, d DECIMAL, PRIMARY KEY (a, b, c, d))

# Generate all combinations of values 1 to 10.
statement ok
INSERT INTO t SELECT a, b, c::FLOAT, d::DECIMAL FROM
  generate_series(1, 10) AS a(a),
  generate_series(1, 10) AS b(b),
  generate_series(1, 10) AS c(c),
  generate_series(1, 10) AS d(d)

# Test aggregrate functions without muti-stage.
query FFF
select sqrdiff(d), stddev_pop(d), var_pop(d) from t
----
82499.99999999999999999976 2.8722813232690143299 8.25

query IFFFF
select regr_count(c,d), regr_sxx(c,d), regr_syy(c,d), regr_avgx(c,d), regr_avgy(c,d) from t
----
10000 82499.99999999996 82500.00000000012 5.5 5.5

# Split into ten parts.
statement ok
ALTER TABLE t SPLIT AT SELECT i FROM generate_series(1, 9) AS g(i)

# Relocate the ten parts to the five nodes.
statement ok
ALTER TABLE t EXPERIMENTAL_RELOCATE
  SELECT ARRAY[i%5+1], i FROM generate_series(0, 9) AS g(i)

# Test aggregrate functions in muti-stage.
query FFF
select sqrdiff(d), stddev_pop(d), var_pop(d) from t
----
82499.99999999999999999952 2.8724249481071304094 8.25

query IFFFF
select regr_count(c,d), regr_sxx(c,d), regr_syy(c,d), regr_avgx(c,d), regr_avgy(c,d) from t
----
10000 82499.99999999996 82500.00000000012 5.5 5.5