You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "fornwall (via GitHub)" <gi...@apache.org> on 2026/10/19 21:02:41 UTC
[I] Wrong results: correlated scalar subquery with nested aggregates [datafusion]
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: github-unsubscribe@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Wrong results: correlated scalar subquery with nested aggregates [datafusion]
Posted by "comphead (via GitHub)" <gi...@apache.org>.
comphead commented on issue #26508:
URL: https://github.com/apache/datafusion/issues/26508#issuecomment-6300932888
Thanks @fornwall for reporting the issue
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Wrong results: correlated scalar subquery with nested aggregates [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #26508:
URL: https://github.com/apache/datafusion/issues/26508#issuecomment-6479351847
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
- https://github.com/apache/datafusion/issues/5492#issuecomment-6479090126
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] Wrong results: correlated scalar subquery with nested aggregates [datafusion]
Posted by "raghavyadav01 (via GitHub)" <gi...@apache.org>.
raghavyadav01 commented on issue #26508:
URL: https://github.com/apache/datafusion/issues/26508#issuecomment-6506156703
Checked on current main (14aa185dd), this now errors instead of returning wrong results:
```sql
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 feature is not implemented: Physical plan does not support logical expression ScalarSubquery(<subquery>)
```
DuckDB 1.5.6 gives `(1, 1, 1), (2, 1, 0)`. So this can probably move to the unsupported list.
--
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: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org