Repository navigation
Commit 9d15cf0
fix: return a query error for correlated scalar subquery with outer-column HAVING over count aggregates (#26723)
> [!IMPORTANT]
>
> **Fork CI setup:** For pull requests from a fork to `main`, enable
workflows in your fork's **Actions** tab before you push.
> See the [fork CI setup
guide](https://datafusion.apache.org/contributor-guide/#pull-requests-from-forks).
## Which issue does this PR close?
- Closes #26503.
## Rationale for this change
A correlated scalar subquery whose aggregate is `count(*)` returns `0`
for outer rows where the subquery produced no row. When the subquery
also has a `HAVING` clause (or a filter above the aggregate) that
references an outer column, an unmatched row can mean that condition
rejected the aggregate output, and the correct answer is `NULL`, not
`0`.
The first version of this PR tried to repair the results by checking the
condition in the compensation `CASE`. As discussed in review, that
approach is hard to trust: the condition has to be evaluated against the
empty-input defaults for an unmatched row and against the actual
aggregate output for a matched one, and neither evaluation is right for
the rows the other one covers. Following the review discussion, this PR
now returns a planning error for these queries instead of returning
numbers that may be wrong.
For example (from the issue):
```sql
SELECT x.a, x.d,
(SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING count(*) >= x.a) AS v
FROM (VALUES (1, 1), (2, 1), (1, 9)) AS x(a, d)
ORDER BY x.d, x.a;
```
PostgreSQL returns `1, NULL, NULL`; DataFusion returns `1, 0, 0` today,
because the empty-input default is used unconditionally when the LEFT
JOIN is unmatched. With this PR the query fails at planning with:
```text
Unsupported correlated scalar subquery: a HAVING or filter above a count-like aggregate reads outer columns, and this rewrite cannot evaluate it against both the group and the empty input without risking wrong results. Move the condition outside the subquery
```
## What changes are included in this PR?
- `decorrelate.rs`: `PullUpCorrelatedExpr` gains a
`having_like_join_filters` field. It records the outer-reference
conditions of a `Filter` only when it sits directly above a count-bug
`Aggregate` (a `HAVING` clause, or an outer-only filter over the
aggregate output), so `WHERE` clauses below the aggregate, which only
prune input rows, are not affected.
- `scalar_subquery_to_join.rs`: in `build_join`, the recorded conditions
are checked when the subquery's output column needs count-bug
compensation, and the query is rejected instead of rewritten. This
covers exactly the queries where the old compensation would have
applied. An output whose own empty-input default is `NULL` (a subquery
projecting `max`, say) needs no compensation, so those queries keep
filtering in the `ON` as before, and a `HAVING` that reads no outer
column is still handled by the existing `having` arm.
- `subquery.slt`: the issue queries now assert the error; two
local-`HAVING` cases still assert values.
## What is the testing strategy for this PR?
Six cases in `datafusion/sqllogictest/test_files/subquery.slt` now
assert the rejection: both queries from the issue, a stacked filter
above the aggregate that reads an outer column, and the three `HAVING`
shapes from the review discussion (`count(*) < x.a`, `max(y.c) < x.a`,
`max(y.c) IS NULL OR count(*) < x.a`). Two local-`HAVING` cases (`HAVING
count(*) >= 0` and `HAVING count(*) > 0`, neither reading an outer
column) still assert their values, which guard the boundary of the
check.
Also ran `cargo test -p datafusion-optimizer` plus `cargo fmt`/`clippy`
and the subquery and lateral join sqllogictest files.
## Are there any user-facing changes?
Yes. Scalar subqueries in the affected shape now fail at planning with
`Unsupported correlated scalar subquery: ...` instead of returning a
wrong `0`; the fix is to evaluate the condition outside the subquery. No
API changes, so no `api change` label or documentation updates are
needed.
Co-authored-by: Jay Zhan <[email protected]>1 parent e9d45f3 commit 9d15cf0
3 files changed
Lines changed: 119 additions & 0 deletions
File tree
- datafusion
- optimizer/src
- sqllogictest/test_files
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
68 | 68 | | |
69 | 69 | | |
70 | 70 | | |
| 71 | + | |
| 72 | + | |
| 73 | + | |
| 74 | + | |
| 75 | + | |
| 76 | + | |
| 77 | + | |
| 78 | + | |
71 | 79 | | |
72 | 80 | | |
73 | 81 | | |
| |||
124 | 132 | | |
125 | 133 | | |
126 | 134 | | |
| 135 | + | |
127 | 136 | | |
128 | 137 | | |
129 | 138 | | |
| |||
415 | 424 | | |
416 | 425 | | |
417 | 426 | | |
| 427 | + | |
| 428 | + | |
| 429 | + | |
| 430 | + | |
| 431 | + | |
| 432 | + | |
| 433 | + | |
| 434 | + | |
| 435 | + | |
| 436 | + | |
| 437 | + | |
| 438 | + | |
| 439 | + | |
| 440 | + | |
| 441 | + | |
| 442 | + | |
418 | 443 | | |
419 | 444 | | |
420 | 445 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
349 | 349 | | |
350 | 350 | | |
351 | 351 | | |
| 352 | + | |
| 353 | + | |
| 354 | + | |
| 355 | + | |
| 356 | + | |
| 357 | + | |
| 358 | + | |
| 359 | + | |
| 360 | + | |
| 361 | + | |
| 362 | + | |
| 363 | + | |
| 364 | + | |
| 365 | + | |
| 366 | + | |
| 367 | + | |
| 368 | + | |
| 369 | + | |
| 370 | + | |
| 371 | + | |
| 372 | + | |
| 373 | + | |
| 374 | + | |
| 375 | + | |
| 376 | + | |
| 377 | + | |
| 378 | + | |
| 379 | + | |
| 380 | + | |
352 | 381 | | |
353 | 382 | | |
354 | 383 | | |
| |||
| Original file line number | Diff line number | Diff line change | |
|---|---|---|---|
| |||
2219 | 2219 | | |
2220 | 2220 | | |
2221 | 2221 | | |
| 2222 | + | |
| 2223 | + | |
| 2224 | + | |
| 2225 | + | |
| 2226 | + | |
| 2227 | + | |
| 2228 | + | |
| 2229 | + | |
| 2230 | + | |
| 2231 | + | |
| 2232 | + | |
| 2233 | + | |
| 2234 | + | |
| 2235 | + | |
| 2236 | + | |
| 2237 | + | |
| 2238 | + | |
| 2239 | + | |
| 2240 | + | |
| 2241 | + | |
| 2242 | + | |
| 2243 | + | |
| 2244 | + | |
| 2245 | + | |
| 2246 | + | |
| 2247 | + | |
| 2248 | + | |
| 2249 | + | |
| 2250 | + | |
| 2251 | + | |
| 2252 | + | |
| 2253 | + | |
| 2254 | + | |
| 2255 | + | |
| 2256 | + | |
| 2257 | + | |
| 2258 | + | |
| 2259 | + | |
| 2260 | + | |
| 2261 | + | |
| 2262 | + | |
| 2263 | + | |
| 2264 | + | |
| 2265 | + | |
| 2266 | + | |
| 2267 | + | |
| 2268 | + | |
| 2269 | + | |
| 2270 | + | |
| 2271 | + | |
| 2272 | + | |
| 2273 | + | |
| 2274 | + | |
| 2275 | + | |
| 2276 | + | |
| 2277 | + | |
| 2278 | + | |
| 2279 | + | |
| 2280 | + | |
| 2281 | + | |
| 2282 | + | |
| 2283 | + | |
| 2284 | + | |
| 2285 | + | |
| 2286 | + | |
2222 | 2287 | | |
2223 | 2288 | | |
2224 | 2289 | | |
| |||
0 commit comments