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

Just doing a drive-by...

On Fri, May 20, 2016 at 3:50 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> Old thread link:
>
> http://www.postgresql.org/message-id/CA+=vxNa5_N1q5q5OkxC0aQnNdbo2Ru6GVw+86wk+oNsUNJDLig@mail.gmail.com
>
> On Thu, Apr 14, 2016 at 1:29 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > Jeff
> >
> > (Reviving an old thread for 2014...)

>
> > Would you have time to work on this for 9.7..? I came across a
> > real-world use case for exactly this capability and was sorely
> > disappointed to discover we didn't support it even though there had been
> > discussion for years on it, which quite a few interested parties.
> >

> First, I think the syntax is still implemented in a bad way. Right now
> it's part of the OVER clause, and the IGNORE NULLS gets put into the
> frame options. It doesn't match the way the spec defines the grammar,
> and I don't see how it really makes sense that it's a part of the
> frame options or the window object at all.

​How does the relatively new FILTER clause play into this, if at all?

I think we need a need catalog support to say
> whether a function honors IGNORE|RESPECT NULLS or not, which means we
> also need support in CREATE FUNCTION.
>

We already have "STRICT" for deciding whether a function processes nulls.
Wouldn't this need to exist on the "CREATE AGGREGATE"

Rhetorical question: I presume we are going to punt on the issue, since it
is non-standard, but what is supposed to happen with a window invocation
that ignores nulls but has a non-strict function that returns a non-null on
null input?

David J.