Re: FILTER for aggregates [was Re: Department of Redundancy Department: makeNode(FuncCall) division]

On Mon, Jul 15, 2013 at 06:56:17PM +0000, Andrew Gierth wrote:
> Noah Misch said:
>
> > I twitched upon reading this, because neither ORDER BY nor FILTER preclude
> > the aggregate being MIN or MAX. Perhaps Andrew can explain why he put
> > aggorder there back in 2009.
>
> The bottom line is that I intentionally avoided assuming that an agg with an
> aggsortop could only be min() or max() and that having an order by clause
> would always be harmless in such cases. I can't think of an actual use case
> where it would matter, but I've seen people define some pretty strange aggs
> recently.
>
> So I mildly object to simply throwing away the ORDER BY clause in such cases.

I can't think of another use for aggsortop as defined today. However, on
further reflection, min(x ORDER BY y) is not identical to min(x) when the
B-tree operator class of the aggsortop can find non-identical datums to be
equal. This affects at least min(numeric) and min(interval). min(x) chooses
an unspecified x among those equal to the smallest x, while min(x ORDER BY y)
can be used to narrow the choice. I will update the comments along those
lines and not change semantics after all.

Thanks,
nm

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