abp002 opened a new pull request, #26942:
URL: https://github.com/apache/datafusion/pull/26942
## Which issue does this PR close?
- Closes #26505.
## Rationale for this change
A correlated scalar subquery without `GROUP BY` ignores its `HAVING` when
the `HAVING` is true for an empty input and the subquery returns an aggregate
that is NULL for an empty input (`sum`, `max`, `min`):
```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)
-- main: (1, 10), (2, NULL)
-- PostgreSQL: (1, NULL), (2, NULL)
```
`PullUpCorrelatedExpr` pulls such a `HAVING` out of the subquery, and
`ScalarSubqueryToJoin` only applies it again through the count bug compensation
`CASE`. That `CASE` is not built for expressions that are NULL on empty input,
so the `HAVING` is dropped.
Following
https://github.com/apache/datafusion/issues/5492#issuecomment-6479090126, this
makes the query error instead of trying to return the right rows.
## What changes are included in this PR?
`build_join` keeps the subquery correlated when a `HAVING` was pulled up and
the subquery's output is NULL on empty input. The query then fails with
`Physical plan does not support logical expression ScalarSubquery`, the same
way #26544 does.
Only the output column is checked. A `count(*)` subquery whose `HAVING`
reads `sum` or `max` still gets the `HAVING` from the compensation and keeps
working.
## What is the testing strategy for this PR?
New cases in `subquery.slt`: the query from the issue, `max`, and `sum` with
`HAVING count(*) = 0` now error. A `count(*)` subquery with a `HAVING` on
`count(*)` or on `sum`, and a `HAVING` that is false on empty input, still
return the same rows as before. All `sqllogictest` files, the
`datafusion-optimizer` unit tests and the `core_integration` `sql::` tests pass
locally.
## Are there any user-facing changes?
These queries now return a "not implemented" error instead of wrong results.
No API changes.
--
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]
