Re: Proposal/design feedback needed: WITHIN GROUP (sql standard ordered set aggregate functions)

On Wed, Jul 24, 2013 at 04:16:28AM +0000, Andrew Gierth wrote:
> Noah Misch said:
> > Other aggregates based on this syntax might not desire such type unification.
>
> Then there would have to be some way to distinguish that. Maybe those could
> have -1 and the standard hypothetical set functions -2, with some flag in
> CREATE AGGREGATE to sort it out.

Sure.

> > But let's not make the
> > parser presume that an aggordnargs=-1 aggregate always wants its "any"
> > arguments handled in the manner of the standard hypothetical set functions.
>
> This has to happen in the parser because these are errors that should be
> caught before execution:
>
> rank(foo) within group (order by bar,baz)
> rank(integercol) within group (order by textcol)

Would be nice to have, but not an overriding concern.

> And collations have to be resolved (pairwise) before sorting can happen:
>
> rank(textval COLLATE "C") within group (order by foo) -- sorts in "C"
> rank(textval COLLATE "C") within group (order by bar COLLATE "en_US") -- error
>
> (basically, in rank(x) within group (order by y) where x and y are
> collatable, the collation rules apply exactly as though you were doing
> (x < y), with all the implicit vs. explicit stuff included)

This, though, makes direct parser support nigh inevitable.

The issue to resolve here is whether and to what extent we should implement
the SQL-standard hypothetical set functions as special cases of some
more-generic concept. Here's the declaration you proposed for the rank() HSF:

> create aggregate rank(variadic "any") within group (variadic "any") (

This would be the first place to my knowledge where "any" doesn't mean
unrestricted type acceptance at the parser level. If we need bespoke syntax
declaring a function as an HSF with the entailed call behavior nuances, fine.
Treating a declaration with this particular mix of "any" as a secret handshake
requesting those nuances is not the way to go.

> STYPE = boolean,
> INITCOND = 'f',
> SORTOP = >,
> FINALFUNC = rank_hypothetical_final
> );

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com