Skip to content

Commit 9d15cf0

Browse files
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/decorrelate.rs‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,14 @@ pub struct PullUpCorrelatedExpr {
6868
pub collected_count_expr_map: HashMap<LogicalPlan, ExprResultMap>,
6969
/// pull up having expr, which must be evaluated after the Join
7070
pub pull_up_having_expr: Option<Expr>,
71+
/// Outer-reference conditions from a `Filter` that sits directly above a
72+
/// count-bug `Aggregate`: a `HAVING` clause (or an outer-only filter)
73+
/// that filters the aggregate *output*. Unlike `join_filters`, these do
74+
/// not merely correlate the two sides: an unmatched LEFT JOIN row may
75+
/// mean one of them rejected the group rather than that no group
76+
/// matched, and a scalar subquery must yield NULL for a rejected group.
77+
/// `build_join` uses them to reject such queries up front.
78+
pub having_like_join_filters: Vec<Expr>,
7179
/// whether we have converted a scalar aggregation into a group aggregation. When unnesting
7280
/// lateral joins, we need to produce a left outer join in such cases.
7381
///
@@ -124,6 +132,7 @@ impl PullUpCorrelatedExpr {
124132
need_handle_count_bug: false,
125133
collected_count_expr_map: HashMap::new(),
126134
pull_up_having_expr: None,
135+
having_like_join_filters: Vec::new(),
127136
pulled_up_scalar_agg: false,
128137
correlated_filters: Vec::new(),
129138
column_refs_above: Vec::new(),
@@ -415,6 +424,22 @@ impl TreeNodeRewriter for PullUpCorrelatedExpr {
415424
// in_predicate may be already included in the join filters, remove it from the join filters first.
416425
join_filters = remove_duplicated_filter(join_filters, in_predicate)?;
417426
}
427+
// A `Filter` directly above a count-bug `Aggregate` is a
428+
// HAVING clause (or an outer-only filter above the aggregate):
429+
// its outer-reference conditions filter the aggregate *output*,
430+
// so a LEFT JOIN row can be unmatched either because the
431+
// correlation matched no group or because one of them rejected
432+
// the group. Record them so the scalar rewrite can tell the
433+
// two apart; where the output needs count-bug compensation it
434+
// rejects the query instead of guessing. A `Filter` below the
435+
// aggregate only prunes input rows and is left out.
436+
if self
437+
.collected_count_expr_map
438+
.contains_key(&*plan_filter.input)
439+
{
440+
self.having_like_join_filters
441+
.extend(join_filters.iter().cloned());
442+
}
418443
let correlated_subquery_cols =
419444
collect_subquery_cols(&join_filters, subquery_schema)?;
420445
for expr in join_filters {

‎datafusion/optimizer/src/scalar_subquery_to_join.rs‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -349,6 +349,35 @@ fn build_join(
349349
.alias(subquery_alias.to_string())?
350350
.build()?;
351351

352+
// A HAVING or outer-only filter recorded from above the aggregate cannot
353+
// be rewritten into the join when the output column gets a compensation
354+
// CASE below. Kept in the ON, a row the condition rejects looks unmatched
355+
// and falls back to the empty-input default, which is the issue's wrong 0. Checked
356+
// in the compensation CASE instead, the condition would have to be
357+
// evaluated twice, against the defaults for an unmatched row and against
358+
// the actual output for a matched one, and neither evaluation is right
359+
// for the rows the other one covers. The gate looks at the output column
360+
// alone because the map also holds aggregates that appear only in the
361+
// HAVING, whose NULL defaults need no CASE; an output whose own default
362+
// is NULL (the subquery projects `max`, say) keeps filtering in the ON as
363+
// before, which is correct there because an unmatched row is NULL either
364+
// way. Rather than guess, reject the query.
365+
let output_name = subquery_plan.schema().field(0).name();
366+
let output_needs_compensation = match collected_count_expr_map
367+
.as_ref()
368+
.and_then(|expr_map| expr_map.get(output_name))
369+
{
370+
Some(default_expr) => {
371+
!evaluates_to_null(default_expr.clone(), default_expr.column_refs())?
372+
}
373+
None => false,
374+
};
375+
if output_needs_compensation && !pull_up.having_like_join_filters.is_empty() {
376+
return plan_err!(
377+
"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"
378+
);
379+
}
380+
352381
let join_filter_opt = build_join_filter(
353382
pull_up.join_filters,
354383
pull_up.correlated_subquery_cols_map.values().flatten(),

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2219,6 +2219,71 @@ WHERE t1_int + 2 NOT IN (SELECT max(t2_int) FROM t2 WHERE t2.t2_id = t1.t1_id)
22192219
query error Physical plan does not support logical expression InSubquery
22202220
SELECT t1_id, t1_int + 2 IN (SELECT max(t2_int) FROM t2 WHERE t2.t2_id = t1.t1_id) FROM t1
22212221

2222+
# https://github.com/apache/datafusion/issues/26503
2223+
# A correlated scalar subquery whose HAVING reads an outer column is rejected:
2224+
# left in the join ON it returns the empty-input 0 where NULL is correct.
2225+
statement error .*Unsupported correlated scalar subquery: a HAVING or filter above a count-like aggregate reads outer columns
2226+
SELECT x.a, x.d,
2227+
(SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING count(*) >= x.a) AS v
2228+
FROM (VALUES (1, 1), (2, 1), (1, 9)) AS x(a, d)
2229+
ORDER BY x.d, x.a
2230+
2231+
# Same shape with the predicate in a filter above the aggregate rather than in
2232+
# HAVING: the filter references only an outer column, so it is rejected too.
2233+
statement error .*Unsupported correlated scalar subquery: a HAVING or filter above a count-like aggregate reads outer columns
2234+
SELECT (SELECT n FROM (SELECT count(*) AS n FROM (VALUES (1)) AS y(c) WHERE y.c = x.d) WHERE x.a > 1) AS v
2235+
FROM (VALUES (1, 1)) AS x(a, d)
2236+
2237+
# A local HAVING that holds on the empty-input default still yields the
2238+
# default (0), not NULL, when no inner row matches the correlation. Only
2239+
# conditions that read outer columns are rejected.
2240+
query I
2241+
SELECT (SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING count(*) >= 0) AS v
2242+
FROM (VALUES (1, 9)) AS x(a, d)
2243+
----
2244+
0
2245+
2246+
# A local HAVING that fails on the empty-input default yields NULL.
2247+
query I
2248+
SELECT (SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING count(*) > 0) AS v
2249+
FROM (VALUES (1, 9)) AS x(a, d)
2250+
----
2251+
NULL
2252+
2253+
# A stacked filter above the aggregate that reads an outer column is rejected
2254+
# for the same reason, whether or not a HAVING keyword is written.
2255+
statement error .*Unsupported correlated scalar subquery: a HAVING or filter above a count-like aggregate reads outer columns
2256+
SELECT x.n, x.d,
2257+
(SELECT s.n FROM (SELECT count(*) AS n FROM (VALUES (1)) AS y(c) WHERE y.c = x.d) AS s
2258+
WHERE s.n < x.n) AS v
2259+
FROM (VALUES (5, 9), (0, 9), (5, 1)) AS x(n, d)
2260+
ORDER BY x.d, x.n
2261+
2262+
# A group the HAVING rejects would fall back to the empty-input default when
2263+
# the condition stays in the ON, so this shape is rejected as well.
2264+
statement error .*Unsupported correlated scalar subquery: a HAVING or filter above a count-like aggregate reads outer columns
2265+
SELECT x.a, x.d,
2266+
(SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING count(*) < x.a) AS v
2267+
FROM (VALUES (1, 1), (2, 1), (1, 9)) AS x(a, d)
2268+
ORDER BY x.d, x.a
2269+
2270+
# max appears only in the HAVING, but the subquery outputs count(*), so the
2271+
# output still needs compensation and the query is rejected.
2272+
statement error .*Unsupported correlated scalar subquery: a HAVING or filter above a count-like aggregate reads outer columns
2273+
SELECT x.a, x.d,
2274+
(SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING max(y.c) < x.a) AS v
2275+
FROM (VALUES (1, 1), (2, 1), (1, 9)) AS x(a, d)
2276+
ORDER BY x.d, x.a
2277+
2278+
# The same shape, but here the HAVING does hold on the empty input
2279+
# (max(y.c) IS NULL). The join cannot tell which side decides the unmatched
2280+
# row, so this one is rejected too.
2281+
statement error .*Unsupported correlated scalar subquery: a HAVING or filter above a count-like aggregate reads outer columns
2282+
SELECT x.a, x.d,
2283+
(SELECT count(*) FROM (VALUES (1)) AS y(c) WHERE y.c = x.d HAVING max(y.c) IS NULL OR count(*) < x.a) AS v
2284+
FROM (VALUES (1, 1), (2, 1), (1, 9)) AS x(a, d)
2285+
ORDER BY x.d, x.a
2286+
22222287
statement ok
22232288
DROP TABLE limit_zero_outer;
22242289

0 commit comments

Comments
 (0)