On Wed, Oct 25, 2023 at 10:34:10PM -0700, David G. Johnston wrote:
> I would reword the existing note to be something like:
>
> The SQL Standard defines specific aggregates and their properties, including
> which of DISTINCT and/or ORDER BY is allowed. Due to the extensible nature of
> PostgreSQL it accepts either or both clauses for any aggregate.
Uh, is this something in my patch or somewhere else? I don't think
PostgreSQL extensible is an example of syntax flexibility.
> From the most recent patch:
>
> <para>
> - If <literal>DISTINCT</literal> is specified in addition to an
> - <replaceable>order_by_clause</replaceable>, then all the <literal>ORDER BY
> </literal>
> - expressions must match regular arguments of the aggregate; that is,
> - you cannot sort on an expression that is not included in the
> - <literal>DISTINCT</literal> list.
> + If <literal>DISTINCT</literal> is specified with an
> + <replaceable>order_by_clause</replaceable>, <literal>ORDER
> + BY</literal> expressions can only reference columns in the
> + <literal>DISTINCT</literal> list. For example:
> +<programlisting>
> +WITH vals (v1, v2) AS ( VALUES (1,'Z'),(3,'D'),(4,'R'),(3,'A'),(2,'T') )
> +SELECT array_agg(DISTINCT v2 ORDER BY v2 DESC) FROM vals;
> + array_agg
> +-------------
> + {Z,T,R,D,A}
> +</programlisting>
>
> The change to a two-column vals was mostly to try and find corner-cases that
> might need to be addressed. If we don't intend to show the error case of
> DISTINCT v1 ORDER BY v2 then we should go back to the original example and just
> add ORDER BY v DESC. I'm fine with not using string_agg here.
>
> + For example:
> +<programlisting>
> +WITH vals (v) AS ( VALUES (1),(3),(4),(3),(2) )
> +SELECT array_agg(DISTINCT v ORDER BY v DESC) FROM vals;
> + array_agg
> +-----------
> + {4,3,2,1}
> +</programlisting>
Okay, good, switched in the attached patch.
> We get enough complaints regarding "apparent ordering" that I would like to
> add:
>
> As a reminder, while some DISTINCT processing algorithms produce sorted output
> as a side-effect, only by specifying ORDER BY is the output order guaranteed.
Well, we need to create a new email thread for this and look at all the
areas is applies to since this is a much larger issue.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
