Federico <cfederico87@xxxxxxxxx> 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
