Re: Request for Patch Feedback: Lag & Lead Window Functions Can Ignore Nulls

On Fri, 2013-06-21 at 09:21 -0400, Robert Haas wrote:
> The other question here is - do we actually have the grammar right?
> As in, is this actually the syntax we're supposed to be implementing?
> It looks different from what's shown here, where the IGNORE NULLS is
> inside the function's parentheses, rather than afterwards:
>
> http://rwijk.blogspot.com/2010/06/simulating-laglead-ignore-nulls.html
>
> IBM seems to think it's legal either inside or outside the parentheses:
>
> http://pic.dhe.ibm.com/infocenter/informix/v121/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_2594.htm

The spec seems pretty clear that it falls outside of the parentheses,
unless I am missing something.

> Regardless of what syntax we settle on, we should also make sure that
> the conflict is intrinsic to the grammar and can't be factored out, as
> Tom suggested upthread. It's not obvious to me what the actual
> ambiguity is here. If you've seen "select lag(num,0)" and the
> lookahead token is "respect", what's the problem? It sort of looks
> like it could be a column label, but not even unreserved keywords can
> be column labels, so that's not it. Probably deserves a bit more
> investigation...

I think the problem is when the function is used as a table function;
e.g.:

SELECT * FROM generate_series(1,10) respect;

> We could just add additional, optional Boolean argument to the
> existing functions. It's non-standard, but we avoid adding keywords.

I thought about that, but it is awkward because the argument would have
to be constant (or, if not, it would be quite strange).

Regards,
Jeff Davis