SQL: Nested aggregate functions

[philrz]

SuperSQL is currently accepting nested aggregate functions, but it looks like they're not supposed to be permitted in SQL. Therefore it seems we should either stop supporting them or document why SuperSQL's approach is somehow valid/appropriate in contrast to others.

Details

Repro is with super commit 9fcbd22. This was found via a query from a sqllogictest.

Applying the data and query from the test in Postgres:

$ psql postgres
psql (17.5 (Homebrew))
Type "help" for help.

postgres=# select * from test;
 a  | b  
----+----
 11 | 22
 13 | 22
 12 | 21
(3 rows)

postgres=# SELECT b % 2 AS f, COUNT(SUM(a)) FROM test GROUP BY f;
ERROR:  aggregate function calls cannot be nested
LINE 1: SELECT b % 2 AS f, COUNT(SUM(a)) FROM test GROUP BY f;

Whereas in SuperDB:

$ cat test.sup
{a:11::int32,b:22::int32}
{a:13::int32,b:22::int32}
{a:12::int32,b:21::int32}

$ super -version
Version: 9fcbd222b

$ super -c "SELECT b % 2 AS f, COUNT(SUM(a)) FROM 'test.sup' GROUP BY f;"
{f:1,"COUNT(SUM(a))":1::uint64}
{f:0,"COUNT(SUM(a))":2::uint64}

I wasn't previously familiar with the concept of nested aggregate functions, but the tl;dr of what I see in web searches is that they're supposedly not permitted in SQL, and users that think they want them should instead use multi-stage aggregations with subqueries or CTEs.

The fact SuperDB is producing a result at all is curious. If the query did work in basic SQL, I'm not sure what the expected result would have been. But taking my own stab at it, if I drop the COUNT and make it a valid SQL query we get:

postgres=# SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f;
 f | sum 
---+-----
 0 |  24
 1 |  12
(2 rows)

So since the SUM is producing single values as we'd expect, I'd assume the COUNT of each would have been expected to be 1, making SuperDB's result of 2 incorrect. If it's actually doing something smart and I'm not seeing it, then we probably should document it and defend it. 😄