Aggregate ORDER BY patch
in [PgSql]
|
From: Peter Eisentraut on 13 Nov 2009 10:44 On fre, 2009-11-13 at 10:01 -0500, Tom Lane wrote: > Peter Eisentraut <peter_e(a)gmx.net> writes: > > On fre, 2009-11-13 at 03:16 +0000, Andrew Gierth wrote: > >> Caveat: as discussed earlier, this patch changes the behaviour of > >> array_agg(DISTINCT x) when applied to NULL inputs. Formerly, the NULLs > >> were unconditionally skipped; now, they are treated just like DISTINCT > >> or GROUP BY normally do. > > > The right answer to that should be in the SQL standard. > > It's not. The standard defines the behavior of certain specific > aggregates; it doesn't provide general rules that would apply to > user-defined aggregates. But array_agg is in the standard. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Peter Eisentraut on 13 Nov 2009 10:44 On fre, 2009-11-13 at 10:35 -0500, Tom Lane wrote: > I'm not entirely convinced that adding ORDER BY here is a good idea, > partly because it goes so far beyond the spec This is exactly the syntax that is in the spec AFAICT. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 13 Nov 2009 11:14 On Fri, Nov 13, 2009 at 10:35 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Greg Stark <gsstark(a)mit.edu> writes: >> On Fri, Nov 13, 2009 at 7:54 AM, Heikki Linnakangas >> <heikki.linnakangas(a)enterprisedb.com> wrote: >>> Andrew Gierth wrote: >>>> Herewith a patch to implement agg(foo ORDER BY bar) with or without >>>> DISTINCT, etc. >>> >>> What does that mean? Aggregate functions are supposed to be commutative, >>> right? > >> We certainly have non-commutative agggregates currently, notably array_agg() > > Right. The fact that none of the standard aggregates are > order-sensitive doesn't mean that it's not useful to have user-defined > ones that are. Currently we suggest fetching from an ordered sub-select > if you want to use an aggregate that is input order sensitive. This > patch just provides an alternative (and equally nonstandard) notation > for that. > > I'm not entirely convinced that adding ORDER BY here is a good idea, > partly because it goes so far beyond the spec and partly because it's > not going to be easily optimizable. But I can see that there is a > use-case. Yeah, for sure. I currently handle this, when necessary, by using subselects, but it would sure be nice to have a more compact notation, if there's a good way to do that. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Gierth on 13 Nov 2009 11:32 >>>>> "Heikki" == Heikki Linnakangas <heikki.linnakangas(a)enterprisedb.com> writes: >> Herewith a patch to implement agg(foo ORDER BY bar) with or >> without DISTINCT, etc. Heikki> What does that mean? Aggregate functions are supposed to be Heikki> commutative, right? The SQL spec defines two non-commutative aggregates that we implement: array_agg(x ORDER BY ...) xmlagg(x ORDER BY ...) In addition, of course, we allow user-defined aggregates, which are perfectly free to be non-commutative. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Gierth on 13 Nov 2009 11:44
>>>>> "Peter" == Peter Eisentraut <peter_e(a)gmx.net> writes: >> I'm not entirely convinced that adding ORDER BY here is a good idea, >> partly because it goes so far beyond the spec Peter> This is exactly the syntax that is in the spec AFAICT. Right. The spec defines this syntax for array_agg and xmlagg (only). The patch goes beyond the spec in that it allows ORDER BY for any aggregate at all, and also allows combination of ORDER BY and DISTINCT (the spec only allows DISTINCT with <general set operation> which doesn't include array_agg or xmlagg, so there is nowhere that both are allowed). But it would be entirely unreasonable, the way postgres works, to implement ORDER BY for only specific aggregates. (Note also that combining ORDER BY and DISTINCT can change the behaviour of DISTINCT. e.g. foo(distinct x order by x using <<<) will use whatever definition of equality is implied by the hypothetical <<< operator when comparing values of x for distinctness.) As for the null handling, the spec is no help there for this reason: it allows DISTINCT only for <general set operation>, and for all <general set operation>s whether DISTINCT or not, NULLs are always discarded from the input before processing (i.e. the behaviour we implement for strict aggregates). So even the fact that we allow array_agg(distinct x) at all is going beyond the spec and therefore doesn't have a defined result. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |