fornwall opened a new issue, #26508:
URL: https://github.com/apache/datafusion/issues/26508
### Describe the bug
An aggregate without `GROUP BY` always returns exactly one row, even when
its input is empty.
When such an aggregate is in a correlated scalar subquery, and another
aggregate sits above it, DataFusion behaves as if the inner aggregate returned
no row for outer rows with no matching inner rows. The outer aggregate then
computes its result over an empty input.
### To Reproduce
```shell
datafusion-cli -c "
SELECT x.a,
(SELECT count(*) FROM (SELECT max(y.b) FROM (VALUES (1, 10)) AS y(a,
b) WHERE y.a = x.a)) AS v1,
(SELECT max(n) FROM (SELECT count(*) AS n FROM (VALUES (1, 10)) AS
y(a, b) WHERE y.a = x.a)) AS v2
FROM (VALUES (1), (2)) AS x(a)
ORDER BY x.a"
```
This currently gives:
```
+---+----+------+
| a | v1 | v2 |
+---+----+------+
| 1 | 1 | 1 |
| 2 | 0 | NULL |
+---+----+------+
```
### Expected behavior
For `a = 2` no row of `y` matches, so the inner `max(y.b)` returns one row
with `NULL`, and the inner `count(*)` returns one row with `0`. The expected
result is `v1 = 1` and `v2 = 0`:
```
+---+----+----+
| a | v1 | v2 |
+---+----+----+
| 1 | 1 | 1 |
| 2 | 1 | 0 |
+---+----+----+
```
At least PostgreSQL, SQLite and BigQuery return this result.
### Additional context
- The result is correct when the subquery is uncorrelated (`WHERE y.a = 2`
instead of `WHERE y.a = x.a` gives `1, 0` for both rows).
- The result is also correct when only a projection sits above the inner
aggregate, for example `(SELECT n + 1 FROM (SELECT count(*) AS n ... WHERE y.a
= x.a))`.
- Related: #24960 covers the `EXISTS`/`IN` forms, where a correlated
groupless aggregate also loses its row (for example `EXISTS (SELECT max(y.b)
... WHERE y.a = x.a)` returns `false` instead of `true`).
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]
