Repository navigation
Unsupported: correlated scalar subquery with nested aggregates #26508
Description
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
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 = 2instead ofWHERE y.a = x.agives1, 0for 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: Wrong results: correlated
EXISTS/INsubqueries over a groupless aggregate get the wrong outer rows #24960 covers theEXISTS/INforms, where a correlated groupless aggregate also loses its row (for exampleEXISTS (SELECT max(y.b) ... WHERE y.a = x.a)returnsfalseinstead oftrue).
Found with DataFusion CLI v55.1.0, main at 7e102d7.
Willingness to contribute
None
Reacted by Oleks V
Activity
comphead commentedon Oct 20, 2026
Thanks @fornwall for reporting the issue
fix: keep a subquery correlated when an aggregate sits above a pulled…
alamb commentedon Oct 28, 2026
When fixing this bug, I think we should make the query error rather than trying to make it work / give correct results. To make it work and have confidence it will be correct, I think we need a more general approach than patches. See here for details
raghavyadav01 commentedon Oct 29, 2026
Checked on current main (14aa185), this now errors instead of returning wrong results:
DuckDB 1.5.6 gives
(1, 1, 1), (2, 1, 0). So this can probably move to the unsupported list.[-]Wrong results: correlated scalar subquery with nested aggregates[/-][+]Unsupported: correlated scalar subquery with nested aggregates[/+]