Inline ORDER BY in aggregate function calls (e.g., ARRAY_AGG)

[philrz]

The initial ARRAY_AGG added in #7165 follows the typical SQL behavior that simple usage does not guarantee data is processed in a particular order. However, SQL conformance feature S098 allegedly says that implementations of ARRAY_AGG should include an optional ORDER BY within the call to provide an ordering guarantee where desired, so at minimum SuperSQL should support this at some point.

Details

At the time this issue is being opened, super is at commit 974d11c.

Implementations such as Postgres provide the ORDER BY with ARRAY_AGG as described above, e.g.,

$ psql --version &&
  psql postgres -c "
SELECT
  array_agg(name)                     AS unordered,      -- order NOT guaranteed
  array_agg(name ORDER BY score DESC) AS by_score_desc,
  array_agg(name ORDER BY score ASC)  AS by_score_asc,
  array_agg(name ORDER BY name)       AS by_name
FROM (VALUES
  ('alice', 30),
  ('bob',   10),
  ('carol', 20)
) AS t(name, score);"

psql (PostgreSQL) 17.10 (Homebrew)

     unordered     |   by_score_desc   |   by_score_asc    |      by_name      
-------------------+-------------------+-------------------+-------------------
 {alice,carol,bob} | {alice,carol,bob} | {bob,carol,alice} | {alice,bob,carol}
(1 row)

It should be noted, however, that the SQL spec and Postgres cover this a little differently. In addition to what's linked above, Postgres also has these docs on Aggregate Expressions which imply that ORDER BY can be expected as valid syntax across many of their aggregate functions, including those for whom order, by definition, should have no meaning. For instance, this works:

$ psql postgres -c "
SELECT
  sum(x)                 AS plain,
  sum(x ORDER BY x ASC)  AS ordered_asc,
  sum(x ORDER BY x DESC) AS ordered_desc
FROM (VALUES (10),(20),(30)) AS t(x);"

 plain | ordered_asc | ordered_desc 
-------+-------------+--------------
    60 |          60 |           60
(1 row)

To me this reads as a consistency/convenience vs. implementation/perf trade-off: Rather than have the user consider where the ORDER BY is strictly needed and the docs reflect this, it's widely accepted syntax in Postgres and maybe the implementation burns cycles doing unnecessary work in a situation like this SUM or there's extra code in their optimizer that recognizes and drops it when it's not needed (note: Claude informs me the SUM here might still have significance when floating point values are involved, but I don't claim to understand why, so I'll just mention in passing). We often make our own judgement calls about where to cover only the SQL spec (which in this case seems to describe ORDER BY within the context of each individual aggregate function that may benefit from it, such as ARRAY_AGG) or we want to go further by being more like Postgres to benefit users that have familiarity with it. So when we take up this issue, we might want to consider that same trade-off for SuperSQL.

Scope

Scope note: This covers only the inline form. The WITHIN GROUP (ORDER BY …) form used by ordered-set aggregates (percentiles, LISTAGG) is a distinct feature with different semantics and is out of scope here.