modules/sql-engine/src/integrationTest/sql/group1/aggregate/aggregates/test_sum.test

# name: test/sql/aggregate/aggregates/test_sum.test
# description: Test sum aggregate
# group: [aggregates]

statement ok
CREATE TABLE integers(i INTEGER);

statement ok
INSERT INTO integers SELECT * FROM table(system_range(0, 999, 1));

# positive numbers
query I
SELECT SUM(i) FROM integers;
----
499500

# negative numbers
statement ok
INSERT INTO integers SELECT * FROM table(system_range(0, -999, -1));

query I
SELECT SUM(i) FROM integers;
----
0

# more negative numbers
statement ok
INSERT INTO integers SELECT * FROM table(system_range(0, -999, -1));

query I
SELECT SUM(i) FROM integers;
----
-499500

# now perform sum of a constant
query I
SELECT SUM(1) FROM integers;
----
3000

# negative constant
query I
SELECT SUM(-1) FROM integers;
----
-3000

# negative constant with a low amount of values
query I
SELECT SUM(-1) FROM integers WHERE i=-1;
----
-2

# no values
query I
SELECT SUM(-1) FROM integers WHERE i>10000;
----
NULL

# bigint sum
statement ok
CREATE TABLE bigints(b BIGINT);

# a bunch of huge values
statement ok
INSERT INTO bigints SELECT * FROM table(system_range(4611686018427387904, 4611686018427388903, 1));

# sum them up
query I
SELECT SUM(b) FROM bigints
----
4611686018427388403500

# sum them up
query T
SELECT typeof(SUM(b)) FROM bigints
----
DECIMAL(38, 0)

# this is too big for a bigint
statement error
SELECT SUM(b)::BIGINT FROM bigints

statement error: Cannot apply 'SUM' to arguments of type 'SUM(<DATE>)'. Supported form(s): 'SUM(<NUMERIC>)'
SELECT SUM('2011-01-01'::DATE)

statement error: Cannot apply 'SUM' to arguments of type 'SUM(<UUID>)'. Supported form(s): 'SUM(<NUMERIC>)'
SELECT SUM('c4a0327c-44be-416d-ae90-75c05079789f'::UUID)

statement error: Cannot apply 'SUM' to arguments of type 'SUM(<UUID>)'. Supported form(s): 'SUM(<NUMERIC>)'
SELECT SUM(c) FROM (VALUES ('c4a0327c-44be-416d-ae90-75c05079789f'::UUID)) t(c)