LEAD/LAG IGNORE NULLS returns incorrect results across NULL gaps and with LIMIT #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

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

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