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

On Fri, Jun 21, 2013 at 12:18 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Thu, 2013-06-20 at 10:03 -0400, Robert Haas wrote:
>> I think the question is whether this feature is really worth adding
>> new reserved keywords for. I have a hard time saying we shouldn't
>> support something that's part of the SQL standard, but personally,
>> it's not something I've seen come up prior to this thread.
>
> What's the next step here?

Well, ideally, some other people weigh in on the value of the feature
vs. the pain of reserving the keywords.

> The feature sounds useful to me.

...and there's one person with an opinion now! :-)

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

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...

> If the grammar is unacceptable, does
> someone have an alternative idea, like using new function names instead
> of grammar? If so, what are reasonable names to use?

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

> Also, I think someone mentioned this already, but what about
> first_value() and last_value()? Shouldn't we do those at the same time?

Not sure.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company