alamb opened a new issue, #26506:
URL: https://github.com/apache/datafusion/issues/26506
### Describe the bug
Using a window function with a named window (`OVER w`) in a `HAVING` clause
does not produce an error. The query runs and returns rows as if the `OVER w`
were not there, planning `MAX(price) OVER w` as an ordinary grouped
`MAX(price)`.
The same query written with an inline window (`OVER (PARTITION BY ...)`) is
correctly rejected with `Window function calls are not allowed in HAVING`.
A window name that is not defined at all (`OVER no_such_window`) is also
accepted in `HAVING` without an error.
### To Reproduce
Run the following in `datafusion-cli`:
```sql
CREATE TABLE p(ticker VARCHAR, ts INT, price DOUBLE) AS VALUES
('BTC', 1, 100.0), ('BTC', 2, 105.0),
('DOGE', 1, 0.1), ('DOGE', 2, 0.2);
-- 1. Inline window in HAVING
SELECT ticker, price FROM p GROUP BY ticker, price
HAVING MAX(price) OVER (PARTITION BY ticker) > 1;
-- 2. Same query using a named window
SELECT ticker, price FROM p GROUP BY ticker, price
HAVING MAX(price) OVER w > 1
WINDOW w AS (PARTITION BY ticker);
-- 3. Window name that is not defined
SELECT ticker, price FROM p GROUP BY ticker, price
HAVING MAX(price) OVER no_such_window > 1;
```
Query 1 (inline window) fails as expected:
```
Error during planning: Window function calls are not allowed in HAVING:
'max(p.price) PARTITION BY [p.ticker] ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING'
```
Query 2 (named window) returns rows:
```
+--------+-------+
| ticker | price |
+--------+-------+
| BTC | 100.0 |
| BTC | 105.0 |
+--------+-------+
2 row(s) fetched.
```
Query 3 (undefined window name) also returns rows:
```
+--------+-------+
| ticker | price |
+--------+-------+
| BTC | 105.0 |
| BTC | 100.0 |
+--------+-------+
2 row(s) fetched.
```
`EXPLAIN` of query 2 shows no window operator, only an aggregate and a
filter on `max(p.price) > 1`:
```
FilterExec: max(p.price) > 1
AggregateExec: aggr: max(p.price), group_by: ticker, price
```
### Expected behavior
- Query 2 should fail with the same error as query 1, since it is the same
query with the window written by name: `Window function calls are not allowed
in HAVING`.
- Query 3 should fail because the window is not defined, as it does in the
`SELECT` list (`Error during planning: The window no_such_window is not
defined!`).
### Additional context
Reproduced with `datafusion-cli` 55.1.0 and with a build of the branch for
https://github.com/apache/datafusion/pull/26400 (based on `main` at cdc2fc041b).
Found while reviewing https://github.com/apache/datafusion/pull/26400.
--
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]
