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

# name: test/sql/aggregate/aggregates/test_avg.test
# description: Test AVG operator
# feature SQL E091-01(Set functions. AVG)
# group: [aggregates]

# scalar average
query RR
SELECT AVG(3), AVG(NULL)
----
3
NULL

statement ok
CREATE TABLE integers(i INTEGER);

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

query RRRR
SELECT AVG(i), AVG(1), AVG(DISTINCT i), AVG(NULL) FROM integers
----
2
1
2
NULL

query R
SELECT AVG(i) FROM integers WHERE i > 100
----
NULL

# invalid use of average
statement error: Invalid number of arguments to function 'AVG'. Was expecting 1 arguments
SELECT AVG()

statement error: Invalid number of arguments to function 'AVG'. Was expecting 1 arguments
SELECT AVG(1, 2, 3)

statement error: Aggregate expressions cannot be nested
SELECT AVG(AVG(1))

statement error: Cannot apply 'AVG' to arguments of type 'AVG(<CHAR(5)>)'
SELECT AVG('cbcde')

statement error: Cannot apply 'AVG' to arguments of type 'AVG(<BINARY(1)>)'. Supported form(s): 'AVG(<NUMERIC>)
SELECT AVG(x'00')

query I
SELECT * FROM integers GROUP BY i HAVING AVG(i) = 1
----
1

statement error: Cannot apply 'AVG' to arguments of type 'AVG(<CHAR(3)>)'
SELECT AVG('100')

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

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

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