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

On Wed, Jan 11, 2023 at 12:13 PM David Rowley <dgrowleyml@gmail.com> wrote:

> 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.

Thanks
Richard