On 12/6/22 05:57, David G. Johnston wrote:
> On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing <vik@postgresfriends.org> wrote:
>
>> I can imagine an optimization that would remove an ORDER BY clause
>> because it isn't needed for any other aggregate.
>
>
> I'm referring to the query:
>
> select any_value(v order by v) from (values (2),(1),(3)) as vals (v);
> // produces 1, per the documented implementation-defined behavior.
Implementation-dependent. It is NOT implementation-defined, per spec.
We often loosen the spec rules when they don't make technical sense to
us, but I don't know of any example of when we have tightened them.
> Someone writing:
>
> select any_value(v) from (values (2),(1),(3)) as vals (v) order by v;
>
> Is not presently, nor am I saying, promised the value 1.
>
> I'm assuming you are thinking of the second query form, while the guarantee
> only needs to apply to the first.
I am saying that a theoretical pg_aggregate.aggorderdoesnotmatter could
bestow upon ANY_VALUE the ability to make those two queries equivalent.
If you care about which value you get back, use something else.
--
Vik Fearing