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

Dean Rasheed said:
> To recap, the options currently on offer are:
>
> 1). Make FILTER a new partially reserved keyword, accepting that that
> might break some users' application code.
>
> 2). Make FILTER unreserved, accepting that that will lead to syntax
> errors rather than more specific error messages if the user tries to
> use an aggregate/window function with FILTER or OVER in the FROM
> clause of a query, or as an index expression.
>
> 3). Adopt a non-standard syntax for this feature, accepting that that
> might conflict with other databases, and that we can never then claim
> to have implemented T612, "Advanced OLAP operations".
>
> 4). Some other parser hack that will offer a better compromise?
>
> My preference is for (2) as the lesser of several evils --- it's a
> fairly narrow case where the quality of the error message is reduced.

Possibly significant in this context is that there is a proof-of-concept
patch in development for another part of T612, namely inverse
distribution functions (e.g. percentile_disc and percentile_cont) which
should be available by the next CF, and which will require a similar
decision with respect to the keyword WITHIN (to support doing:
select percentile_cont(0.5) within group (order by x) from ...;
which returns the median value of x).

This syntax is much more widely supported than FILTER, including by both
Oracle and MSSQL, so a non-standard alternative is much less attractive.
A solution which suits both (i.e. either option 1 or option 2) would make
a whole lot more sense than trying to handle them differently.

--
Andrew (irc:RhodiumToad)