Questionable result from lead(0) IGNORE NULLS

Hi Oliver,

I noticed a questionable result from "lead(0) IGNORE NULLS".

CREATE TEMP TABLE g(x INT, y INT);
CREATE TABLE
INSERT INTO g (VALUES(NULL,1),(NULL,2),(1,3));
INSERT 0 3
SELECT * FROM g;
 x | y 
---+---
   | 1
   | 2
 1 | 3
(3 rows)

SELECT x, y, lead(x, 0) RESPECT NULLS OVER w FROM g
WINDOW w AS (ORDER BY y);
 x | y | lead 
---+---+------
   | 1 |     
   | 2 |     
 1 | 3 |    1
(3 rows)

SELECT x, y, lead(x, 0) IGNORE NULLS OVER w FROM g
WINDOW w AS (ORDER BY y);
 x | y | lead 
---+---+------
   | 1 |     
   | 2 |     
 1 | 3 |    1
(3 rows)

As you can see, "lead(x, 0) IGNORE NULLS" shows the same result as
"lead(x, 0) RESPECT NULLS". IMO "lead(x, 0) IGNORE NULLS" should show
something like:

 x | y | lead 
---+---+------
   | 1 |    1 
   | 2 |    1 
 1 | 3 |    1
(3 rows)

The same thing can be said to lag().

Looking into the code, in
WinGetFuncArgInPartition(src/backend/executor/nodeWindowAgg.c) I see
this:
    if (winobj->ignore_nulls == IGNORE_NULLS && relpos != 0)
    {
        null_treatment = true;

Here, if the caller is lead(0), then relpos == 0, thus
"null_treatment" is not set to true and falls into the code later on:

    if (!null_treatment)        /* IGNORE NULLS is not specified */
    {
        /* get tupple and evaluate in a partition */
        datum = gettuple_eval_partition(winobj, argno,

and runs through the same code path as RESPECT NULLS. I think this is
the reason why "lead(0, x) IGNORE NULLS" showed the same result as
"lead(0, x) RESPECT NULLS". "relpos != 0" part was originally in your
patch. Oliver, what's the reason why you excluded relpose==0 case? Can
we eliminate the restriction and let "lead(0) IGNORE NULLS" case run
the same code path as relpos!=0 (of course with proper adjustment in
related code)?

Best regards,

[1] https://www.postgresql.org/message-id/CAGMVOduHcfhh7Wo9W1Tff0DH_ccPuQGc8D_f5S2_y4OHFOjn%3DA%40mail.gmail.com
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp