On Wed, Dec 7, 2022 at 10:00 PM Vik Fearing <v...@postgresfriends.org> wrote:
> On 12/7/22 04:22, David G. Johnston wrote: > > On Mon, Dec 5, 2022 at 10:40 PM Vik Fearing <v...@postgresfriends.org> > wrote: > > > >> On 12/6/22 05:57, David G. Johnston wrote: > >>> On Mon, Dec 5, 2022 at 9:48 PM Vik Fearing <v...@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. https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES "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.
