fornwall opened a new issue, #26505:
URL: https://github.com/apache/datafusion/issues/26505
### Describe the bug
When a correlated scalar subquery aggregates without `GROUP BY` (for example
with `sum` or `max`), its `HAVING` clause is ignored if the predicate is true
for an empty input, such as `HAVING sum(y.b) IS NULL` or `HAVING count(*) = 0`.
When the `HAVING` clause should filter out the aggregate row, the subquery
returns the aggregate value instead of `NULL`.
### To Reproduce
```shell
datafusion-cli -c "
SELECT x.a,
(SELECT sum(y.b) FROM (VALUES (1, 10)) AS y(a, b) WHERE y.a = x.a
HAVING sum(y.b) IS NULL) AS v
FROM (VALUES (1), (2)) AS x(a)
ORDER BY x.a"
```
This currently gives:
```
+---+------+
| a | v |
+---+------+
| 1 | 10 |
| 2 | NULL |
+---+------+
```
### Expected behavior
`v` should be `NULL` for both rows, which is what PostgreSQL 17 and SQLite
return:
- `a = 1`: `sum(y.b)` is 10, so `sum(y.b) IS NULL` is false and the subquery
returns no row, giving `NULL`.
- `a = 2`: there are no matching rows, so `sum(y.b)` is `NULL`. The `HAVING`
passes and the subquery returns `NULL`.
`EXPLAIN` shows no filter for the `HAVING` anywhere in the plan.
### Additional context
- Also happens with `max`/`min`, and with `HAVING count(*) = 0` while the
subquery selects `sum(y.b)`.
- Correct results when the subquery is uncorrelated (`WHERE y.a = 1`), when
the `HAVING` is false for empty input (for example `HAVING sum(y.b) > 100`),
and when the subquery selects `count(*)` instead (`HAVING count(*) = 0` gives
`NULL, 0`).
- Related but different: #26503 (`count(*)` with an outer column referenced
in `HAVING`).
Found with DataFusion CLI v55.1.0, `main` at 7e102d74c.
### Willingness to contribute
None
--
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]
