zhuqi-lucas opened a new issue, #26364:
URL: https://github.com/apache/datafusion/issues/26364
### Describe the bug
A window that references a named window and adds a frame, such as `OVER (w
ROWS BETWEEN ...)`, keeps the frame but silently drops the named window's
`PARTITION BY` and `ORDER BY`. The function then runs over all rows as one
partition, so values leak across partitions. There is no error.
`OVER w` works. Only the form with the name inside the parentheses is
affected.
### To Reproduce
```sql
CREATE TABLE t(ticker VARCHAR, ts INT, price DOUBLE) AS VALUES
('BTC', 1, 100.0), ('BTC', 2, 105.0), ('BTC', 3, 102.0),
('DOGE', 1, 0.1), ('DOGE', 2, 0.2), ('DOGE', 3, 0.15);
SELECT ticker, ts, price,
MAX(price) OVER (w ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS
named_plus_frame,
MAX(price) OVER (PARTITION BY ticker ORDER BY ts ROWS BETWEEN 1 PRECEDING
AND CURRENT ROW) AS inline
FROM t
WINDOW w AS (PARTITION BY ticker ORDER BY ts)
ORDER BY ticker, ts;
```
```
+--------+----+-------+------------------+--------+
| ticker | ts | price | named_plus_frame | inline |
+--------+----+-------+------------------+--------+
| BTC | 1 | 100.0 | 100.0 | 100.0 |
| BTC | 2 | 105.0 | 105.0 | 105.0 |
| BTC | 3 | 102.0 | 105.0 | 105.0 |
| DOGE | 1 | 0.1 | 102.0 | 0.1 |
| DOGE | 2 | 0.2 | 0.2 | 0.2 |
| DOGE | 3 | 0.15 | 0.2 | 0.2 |
+--------+----+-------+------------------+--------+
```
The first DOGE row gets BTC's price (102.0).
### Expected behavior
`OVER (w ROWS BETWEEN ...)` should inherit `w`'s `PARTITION BY` and `ORDER
BY` and add the frame, as in the SQL standard and PostgreSQL, so both columns
above match. Per the standard (and PostgreSQL), it should be an error if the
referencing window also specifies `PARTITION BY`, specifies `ORDER BY` when `w`
already has one, or if `w` itself has a frame.
### Additional context
`match_window_definitions` in `datafusion/sql/src/select.rs` only rewrites
`WindowType::NamedWindow` (`OVER w`). For `OVER (w ...)`, sqlparser produces a
`WindowType::WindowSpec` with `window_name = Some(w)`, and `window_name` is
never read when planning, so `w` is dropped. A `WINDOW w2 AS (w1 ...)`
definition that references another window likely has the same problem.
Reproduced with datafusion-cli 49.0.2; the same code path is unchanged on
main.
--
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]
