postgres=# set enable_presorted_aggregate=0; SET postgres=# select string_agg(random()::text, ',' order by random()) from generate_series(1,3); string_agg ----------------------------------------------------------- 0.8659110018246505,0.15612649559563474,0.2022878955613403 (1 row)
I'd have expected those random numbers to be concatenated in ascending order.
select string_agg( random()::text, -- position 1 ',' order by random() -- position 2 ) from generate_series(1,3);
I traced this query a bit and found that when executing the aggregation the random() function in the aggregate expression (position 1) and in the order by clause (position 2) are calculated separately. And the sorting is performed based on the function results from the order by clause. In the final output, what we see is the function results from the aggregate expression. Thus we'll notice the output is not sorted.
I'm not sure if this is expected or broken though.
BTW, if we explicitly add ::text for random() in the order by clause, as
select string_agg( random()::text, ',' order by random()::text ) from generate_series(1,3);
The random() function will be calculated only once for each tuple, and we can get a sorted output.
