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
