WINDOW clause gives wrong results with negative RANGE PRECEDING

[soerenwolfers]

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 test

prints

┌───────┬───────┬─────────────────────────────────────────────────────────────────────┐
│   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

[hawkfish]

Not a duplicate of #10809

[hawkfish]

We clamp preceding range values to precede the current row, so this is currently expected behaviour. It is obscure, however and we may decide throw at some point.