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

On Thu, Jun 27, 2013 at 8:52 PM, Nicholas White <n(dot)j(dot)white(at)gmail(dot)com> wrote:
>> The result of the current patch using lead
>
> Actually, I think I agree with you (and, FWIW, so does Oracle:
> http://docs.oracle.com/cd/E11882_01/server.112/e25554/analysis.htm#autoId18).
> I've refactored the window function's implementation so that (e.g.) lead(5)
> means the 5th non-null value away in front of the current row (the previous
> implementation was the last non-null value returned if the 5th rows in front
> was null). These semantics are slower, as the require the function to scan
> through the tuples discarding non-null ones. I've made the implementation
> use a bitmap in the partition context to cache whether or not a given tuple
> produces a null. This seems correct (it passes the regression tests) but as
> it stores row offsets (which are int64s) I was careful not to use bitmap
> methods that use ints to refer to set members. I've added more explanation
> in the code's comments. Thanks -

The documentation you've added reads kind of funny to me:

+ [respect nulls]|[ignore nulls]

Wouldn't we normally write that as [ [ RESPECT | IGNORE ] NULLS ] ?

I've committed the changes from Troels to avoid the grammar conflicts,
and I also took the opportunity to make OVER unreserved, as allowed by
his refactoring and per related discussion on other threads. This
patch will need to be rebased over those changes (sorry), but
hopefully it'll help the review of this patch focus in on the issues
that are specific to RESPECT/IGNORE NULLS.

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