Negative `INTERVAL` offsets in RANGE window frames are accepted and panic at execution
- Dominant language
- Rust
- Stars
- 9.3k
- Forks
- 2.4k
- Avg merge
- 3d 7h
- Merged PRs (30d)
- 344
Description
### Describe the bug
ROWS and GROUPS frame offsets are parsed as `UInt64`, so `ROWS BETWEEN -1 PRECEDING ...` is a planning error ("frame offsets for ROWS / GROUPS must be non negative integers"). RANGE offsets are kept as strings until `coerce_window_frame` (`datafusion/optimizer/src/analyzer/type_coercion.rs`) casts them to the ORDER BY type, and an interval literal such as `INTERVAL '-1 day'` carries its sign inside the string, so a negative RANGE offset is never rejected.
The frame then starts after it ends. The window execution code assumes `start <= end` and panics in debug builds:
### To Reproduce
Debug build of `datafusion-cli`:
```sql
SELECT count(*) OVER (ORDER BY x RANGE BETWEEN INTERVAL '-1 month' PRECEDING AND CURRENT ROW)
FROM (VALUES (arrow_cast(1, 'Timestamp(Second, None)')), (arrow_cast(2, 'Timestamp(Second, None)'))) t(x);
```
```text
thread 'main' panicked at datafusion/physical-expr/src/window/sliding_aggregate.rs:216:33:
attempt to subtract with overflow
```
```sql
SELECT count(*) OVER (ORDER BY x RANGE BETWEEN INTERVAL '-1 day' PRECEDING AND INTERVAL '-1 day' FOLLOWING)
FROM (VALUES (now()), (now())) t(x);
```
```text
thread 'main' panicked at datafusion/expr/src/window_state.rs:65:18:
attempt to subtract with overflow
```
In release builds the same queries do not panic but return wrong results (e.g. `count(*)` of 0 for every row over three consecutive dates with `INTERVAL '-1 day' PRECEDING`).
### Expected behavior
A planning error, consistent with ROWS / GROUPS (and with PostgreSQL, which rejects the frame with "frame starting offset must not be negative").
### Additional context
Found while running a corpus of extreme-value literals against a debug build of `datafusion-cli`.
Contributor guide
Research direction
Reproduce the queries with a debug build of datafusion-cli, then inspect coerce_window_frame in datafusion/optimizer/src/analyzer/type_coercion.rs. Trace the resulting frame through datafusion/physical-expr/src/window/sliding_aggregate.rs and datafusion/expr/src/window_state.rs. Done means negative RANGE INTERVAL offsets produce a planning error like ROWS and GROUPS instead of reaching execution or returning incorrect results.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 75/100