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)
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)?
The result looks wrong. So I've just tried removing the "&& relpos != 0" and I get:
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 |
(3 rows)
Nothing appears for lead at all. So it was doing something but doesn't look like it handles the lead(x, 0) case, but it does handle lead(x) - which is the same as lead(x, 1):
SELECT x, y, lead(x) IGNORE NULLS OVER w FROM g
WINDOW w AS (ORDER BY y);
x | y | lead
---+---+------
| 1 | 1
| 2 | 1
1 | 3 |
(3 rows)
Without Ignore Nulls, lead(x,0) is just the row itself so you'd never use that function. But yes this case needs to be handled, I'll look through the code again, handle this for lead/lag, and add tests.
