Sunninsky opened a new issue, #27284:
URL: https://github.com/apache/datafusion/issues/27284
### Describe the bug
`lead(v, 0, d)` and `lag(v, 0, d)` return the current row's own value.
Without `IGNORE NULLS` a NULL row stays NULL. With `IGNORE NULLS` the default
`d` replaces it.
### To Reproduce
```sql
SELECT id,
lead(v, 0, -7) OVER (ORDER BY id) AS lead_plain,
lead(v, 0, -7) IGNORE NULLS OVER (ORDER BY id) AS lead_ignore_nulls,
lag(v, 0, -7) OVER (ORDER BY id) AS lag_plain,
lag(v, 0, -7) IGNORE NULLS OVER (ORDER BY id) AS lag_ignore_nulls
FROM (VALUES (1, 10), (2, NULL), (3, 30)) AS t(id, v)
ORDER BY id;
```
On 55.2.0 (datafusion-python 55.0.0):
```
| id | lead_plain | lead_ignore_nulls | lag_plain | lag_ignore_nulls |
| 1 | 10 | 10 | 10 | 10 |
| 2 | NULL | -7 | NULL | -7 |
| 3 | 30 | 30 | 30 | 30 |
```
### Expected behavior
Row 2 should be NULL in all four columns. Offset 0 refers to the current row
itself, so there is no row to skip and nothing out of range that would call for
the default. Apache Spark 4.0 returns the row's own value (NULL) for `lead(v,
0, -7) IGNORE NULLS`, the same as without `IGNORE NULLS`.
### Additional context
In `datafusion/functions-window/src/lead_lag.rs` the two code paths disagree
on this input as written:
- `evaluate_all_with_ignore_null` starts with `if offset == 0 { return
Ok(Arc::clone(array)) }`, so a NULL stays NULL.
- `WindowShiftEvaluator::evaluate` ends with "Set the default value if ...
ignore nulls mode and current value is null", which turns a NULL current row
into the default when the offset is 0.
Offsets of 1 or more are not affected by this; the offset 0 case is only
reached by an explicit `0`.
Separately, #25471 (fixed on main by #25472) covers `LEAD` with an offset of
2 or more returning wrong rows across NULL gaps. The published 55.2.0 crate
still has the old code for that.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
