Re: Add proper planner support for ORDER BY / DISTINCT aggregates

 

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.

Thanks
Richard