Re: ANY_VALUE aggregate

On 12/6/22 05:57, David G. Johnston wrote:
> On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing <vik(at)postgresfriends(dot)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