public inbox for [email protected]
help / color / mirror / Atom feedFrom: Vik Fearing <[email protected]>
To: David G. Johnston <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: ANY_VALUE aggregate
Date: Thu, 8 Dec 2022 06:00:54 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwYXaQ9nY-drJ0fd-qDiaKqTfVUpEjxj=56FXxOF6vFogg@mail.gmail.com>
References: <[email protected]>
<CAKFQuwau9pyMB=4S-RUEZ6_Nf_=BuSVb+7FKhjJqPyaSROjreQ@mail.gmail.com>
<[email protected]>
<CAKFQuwbkPTX-EwKwMNFkQwTisMWVmt5isTgYhWpnSEG_xhSYsQ@mail.gmail.com>
<[email protected]>
<CAKFQuwZio6maUZ_SxzudV9vP+DF8+fYWNAn0M-fxh=iKnAnSYA@mail.gmail.com>
<[email protected]>
<CAKFQuwYXaQ9nY-drJ0fd-qDiaKqTfVUpEjxj=56FXxOF6vFogg@mail.gmail.com>
On 12/7/22 04:22, David G. Johnston wrote:
> On Mon, Dec 5, 2022 at 10:40 PM Vik Fearing <[email protected]> wrote:
>
>> On 12/6/22 05:57, David G. Johnston wrote:
>>> On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing <[email protected]>
>> 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.
>
> I really don't care all that much about the spec here given that ORDER BY
> in an aggregate call is non-spec.
Well, this is demonstrably wrong.
<array aggregate function> ::=
ARRAY_AGG <left paren>
<value expression>
[ ORDER BY <sort specification list> ]
<right paren>
>> 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.
>
> The function has to choose some row from among its inputs,
True.
> and the system has to obey an order by specification added to the function call.
False.
> You are de-facto creating a first_value aggregate (which is by definition
> non-standard) whether you like it or not.
I am de jure creating an any_value aggregate (which is by definition
standard) whether you like it or not.
> I'm just saying to be upfront
> and honest about it - our users do want such a capability so maybe accept
> that there is a first time for everything. Not that picking an
> advantageous "implementation-dependent" implementation should be considered
> deviating from the spec.
>
>
>>> 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.
>>
>
> That theoretical idea should not be entertained. Removing a user's
> explicitly added ORDER BY should be off-limits. Any approach at
> optimization here should simply look at whether an ORDER BY is specified
> and pass that information to the function. If the function itself really
> believes that ordering matters it can emit its own runtime exception
> stating that fact and the user can fix their query.
It absolutely should be entertained, and I plan on doing so in an
upcoming thread. Whether it errors or ignores is something that should
be discussed on that thread.
>> If you care about which value you get back, use something else.
>
> There isn't a "something else" to use so that isn't presently an option.
The query
SELECT proposed_first_value(x ORDER BY y) FROM ...
is equivalent to
SELECT (ARRAY_AGG(x ORDER BY y))[1] FROM ...
so I am not very sympathetic to your claim of "no other option".
> I suppose it comes down to what level of belief and care you have that
> people will simply mis-use this function if it is added in its current form
> to get the desired first_value effect that it produces.
People who rely on explicitly undefined behavior get what they deserve
when the implementation changes.
--
Vik Fearing
view thread (42+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: ANY_VALUE aggregate
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox