Re: Order by in a sub query when aggregating the main query

Federico <cfederico87(at)gmail(dot)com> writes:
> A basic example of the type of query in question is the following (see
> below for the actual query):

> select w, array_agg(x)
> from (
> select v, v / 10 as w
> from pg_catalog.generate_series(25, 0, -1) as t(v)
> order by v
> ) as t(x)
> group by w

> This query will return an ordered array as specified by the order by
> clause.in the subquery.
> Can this behaviour be relied upon?

No, not really. It might always work given a particular set of
circumstances. As long as the planner chooses to do the outer
query's grouped aggregation as a HashAgg, there'd be no reason
for it to reshuffle the subquery output before feeding that to
array_agg. However, if it decided that sort-group-and-aggregate
was better, it'd insert a sort by w above the subquery, and then
you'd lose any certainty of the ordering by v continuing to hold.
(Maybe the sort by w would be stable for equal keys, but that's
not guaranteed.)

What you really ought to do is write

select w, array_agg(x order by x)
from ...

to be in the clear per SQL standard. I think that right now that'd
incur additional sorting overhead, which is annoying. But work is
ongoing to recognize when the input is already correctly sorted
for an aggregate, so it should get better in PG 16 or so.

regards, tom lane