lyne7-sc opened a new issue, #25471:
URL: https://github.com/apache/datafusion/issues/25471
### Describe the bug
LEAD and negative-offset LAG with IGNORE NULLS can return NULL or the
default value even when the requested non-null row exists.
There are two cases: incomplete lookahead cache handling during stateful
evaluation, and unsafe window LIMIT pushdown.
### To Reproduce
### 1. Incorrect result across a NULL gap
```sql
SELECT id,
LEAD(v, 2) IGNORE NULLS OVER w AS lead_v,
LAG(v, -2, -1) IGNORE NULLS OVER w AS lag_v
FROM (VALUES
(1, 10), (2, 20), (3, 30), (4, 40),
(5, NULL), (6, NULL), (7, 70), (8, 80), (9, NULL)
) AS t(id, v)
WINDOW w AS (ORDER BY id)
ORDER BY id;
```
For `id = 3`:
- Expected: `(3, 70, 70)`
- Actual: `(3, NULL, -1)`
The next two non-null values are 40 and 70, so both expressions should
return 70.
### 2. Incorrect result with LIMIT pushdown
```sql
SET datafusion.optimizer.enable_window_limits = true;
SELECT id,
LEAD(v) IGNORE NULLS OVER w AS lead_v,
LAG(v, -1, -1) IGNORE NULLS OVER w AS lag_v
FROM (VALUES (1, 10), (2, NULL), (3, NULL), (4, 40)) AS t(id, v)
WINDOW w AS (
ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY id
LIMIT 1;
```
- Expected: `(1, 40, 40)`
- Actual: `(1, NULL, -1)`
Disabling `datafusion.optimizer.enable_window_limits` returns the expected
result.
### Expected behavior
The functions should return the requested non-null value when it exists.
enabling window LIMIT pushdown should not change query results.
### Additional context
_No response_
--
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]
