Re: WITHIN GROUP patch

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
> The examples I've thought of which would return collatable types are
> all ones that would be implemented as plain ordered set functions even
> if their logic was in some sense hypothetical. For example you could
> envisage a value_prec(x) within group (order by y) that returns the
> value of y which sorts immediately before x, but this would just be
> declared as value_prec(anyelement) within group (anyelement) rather
> than engaging the hypothetical argument stuff. (It's this sort of
> thing that suggested pushing down the collation into the sort column
> even for non-hypothetical ordered set functions.)

Meh. I see no very good reason that we shouldn't insist that the
collation be set on the sort column rather than the other input.
That is, if the alternatives are

value_prec(x collate "foo") within group (order by y)
value_prec(x) within group (order by y collate "foo")

which one makes it clearer that the ordering is to use collation foo?
The first one is at best unnatural, and at worst action-at-a-distance.
If you think of the sorting as an operation done in advance of
applying value_prec(), which is something the syntax rather encourages
you to think, there's no reason that it should absorb a collation
from a collate clause attached to a higher-level operation.

So I think the spec authors arguably got it wrong for hypotheticals,
and I'm uneager to extrapolate their choice of behavior into
situations where we don't know for certain that the collations of two
arguments must get unified.

More practically, I'm dubious about your assumption that an aggregate
like this needn't be marked hypothetical. I see that use of
anyelement would be enough to constrain the types to be the same,
but it doesn't ordinarily constrain collations, and I don't think
it should start doing so. So my reaction to this example is not
that we should hack the behavior for plain ordered-set aggregates,
but that we ought to find a rule that allows result-collation
determination for hypotheticals. We speculated upthread about
"merge the collations normally, but ignore inputs declared ANY"
and "merge the collations normally, but ignore variadic inputs".
Either of those would get the job done in this example. I kinda
think we should pick one of these rules and move on.

regards, tom lane