Re: ANY_VALUE aggregate
| От | David G. Johnston |
|---|---|
| Тема | Re: ANY_VALUE aggregate |
| Дата | |
| Msg-id | CAKFQuwYK_UeLFUrfzJxsdmzMv-0uYQO8v3X96jYp=vkzRzwvmg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: ANY_VALUE aggregate (Vik Fearing <vik@postgresfriends.org>) |
| Ответы |
Re: ANY_VALUE aggregate
|
| Список | pgsql-hackers |
On Wed, Dec 7, 2022 at 10:00 PM Vik Fearing <vik@postgresfriends.org> wrote:
On 12/7/22 04:22, David G. Johnston wrote:
> On Mon, Dec 5, 2022 at 10:40 PM Vik Fearing <vik@postgresfriends.org> wrote:
>
>> 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.
>
> 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>
Demoable only by you and a few others...
We should update our documentation - the source of SQL Standard knowledge for mere mortals.
"Note: The ability to specify both DISTINCT and ORDER BY in an aggregate function is a PostgreSQL extension."
Apparently only DISTINCT remains as our extension.
> 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.
Yes, both statements seem true. At least until we decide to start ignoring a user's explicit order by clause.
>> 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".
Semantically, yes, in terms of performance, not so much, for any non-trivial sized group.
I'm done, and apologize for getting too emotionally invested in this. I hope to get others to voice enough +1s to get a first_value function into core along-side this one (which makes the above discussion either moot or deferred until there is a concrete use case for ignoring an explicit ORDER BY). If that doesn't happen, well, it isn't going to make or break us either way.
David J.
В списке pgsql-hackers по дате отправления: