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