Re: Questionable result from lead(0) IGNORE NULLS

On Tue, Oct 7, 2025 at 8:41 AM Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:

>
> 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.