abp002 opened a new pull request, #26608:
URL: https://github.com/apache/datafusion/pull/26608
## Which issue does this PR close?
- Closes #26505.
## Rationale for this change
A correlated scalar subquery without `GROUP BY` ignores its `HAVING` when
the aggregate is NULL for an empty input (`sum`, `max`, `min`) and the `HAVING`
is true for an empty input:
```sql
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)
```
For `a = 1` this returns `10` instead of `NULL`.
When the correlated filter is pulled up, such a `HAVING` is removed from the
subquery and applied again in the count bug `CASE` that `build_join` adds over
the join. But `build_join` skips that `CASE` for aggregates that are NULL for
an empty input, so the `HAVING` was never applied.
## What changes are included in this PR?
`build_join` only skips the `CASE` for those aggregates when there is no
pulled up `HAVING`.
The `HAVING` reads the aggregates by their unqualified names, so it is now
qualified with the subquery alias. Otherwise two such subqueries in the same
query, or an outer column with the same name, fail with an ambiguous reference.
## What is the testing strategy for this PR?
New `sqllogictest` cases in `subquery.slt` with `sum`, `max`, `HAVING
count(*) = 0`, a `HAVING` that keeps some matched rows and two subqueries with
the same aggregate. The expected results match SQLite.
## Are there any user-facing changes?
No, only the wrong results are fixed.
--
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]
