WINDOW clause gives wrong results with negative RANGE PRECEDING #10885
Description
What happens?
WINDOW clause gives wrong results with negative RANGE PRECEDING, as if the offset was capped below at zero.
It should instead either throw (like PostgreSQL15, SQLite3.39 and ClickHouse do) or give the "correct" result (like duckdb does for ROWS PRECEDING, ROWS FOLLOWING, and RANGE FOLLOWING)
To Reproduce
CREATE OR REPLACE TABLE test(i INTEGER, v FLOAT);
INSERT INTO test VALUES (0, 1), (1, 2), (2, 3),;
SELECT i, v, sum(v) OVER (ORDER BY i RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING) FROM testprints
┌───────┬───────┬─────────────────────────────────────────────────────────────────────┐
│ i │ v │ sum(v) OVER (ORDER BY i RANGE BETWEEN -1 PRECEDING AND 1 FOLLOWING) │
│ int32 │ float │ double │
├───────┼───────┼─────────────────────────────────────────────────────────────────────┤
│ 0 │ 1.0 │ 3.0 │
│ 1 │ 2.0 │ 5.0 │
│ 2 │ 3.0 │ 3.0 │
└───────┴───────┴─────────────────────────────────────────────────────────────────────┘
but should print same as
SELECT i, v, sum(v) OVER (ORDER BY i ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING) FROM test┌───────┬───────┬────────────────────────────────────────────────────────────────────┐
│ i │ v │ sum(v) OVER (ORDER BY i ROWS BETWEEN -1 PRECEDING AND 1 FOLLOWING) │
│ int32 │ float │ double │
├───────┼───────┼────────────────────────────────────────────────────────────────────┤
│ 0 │ 1.0 │ 2.0 │
│ 1 │ 2.0 │ 3.0 │
│ 2 │ 3.0 │ NULL │
└───────┴───────┴────────────────────────────────────────────────────────────────────┘
OS:
Linux
DuckDB Version:
0.10.1
DuckDB Client:
Python
Full Name:
Soeren Wolfers
Affiliation:
G-Research
Have you tried this on the latest nightly build?
I have tested with a nightly build
Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?
- Yes, I have