fornwall opened a new issue, #26503:
URL: https://github.com/apache/datafusion/issues/26503
### Describe the bug
A correlated scalar subquery `(SELECT count(*) ... HAVING <predicate using
an outer column>)` returns `0` for outer rows where the `HAVING` predicate is
false. The subquery returns no row in that case, so its value should be `NULL`.
### To Reproduce
```shell
datafusion-cli -c "
SELECT x.a, x.d,
(SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING
count(*) >= x.a) AS v
FROM (VALUES (1, 1), (2, 1), (1, 9)) AS x(a, d)
ORDER BY x.d, x.a"
```
This currently gives:
```
+---+---+---+
| a | d | v |
+---+---+---+
| 1 | 1 | 1 |
| 2 | 1 | 0 |
| 1 | 9 | 0 |
+---+---+---+
```
### Expected behavior
`v` should be `1, NULL, NULL`, which is what PostgreSQL 17 returns:
- `a = 2, d = 1`: `count(*)` is 1, `1 >= 2` is false, so the subquery
returns no row.
- `a = 1, d = 9`: `count(*)` is 0, `0 >= 1` is false, so the subquery
returns no row.
### Additional context
The same thing happens when the predicate is in a filter above the aggregate
instead of in `HAVING`. This returns `0`, but should return `NULL`:
```shell
datafusion-cli -c "
SELECT (SELECT n FROM (SELECT count(*) AS n FROM (VALUES (1)) AS y(c) WHERE
y.c = x.d) WHERE x.a > 1) AS v
FROM (VALUES (1, 1)) AS x(a, d)"
```
The first query returns correct results if `count(*)` is replaced by
`sum(y.c)`, or if `x.a` in the `HAVING` is replaced by a constant.
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]
