Skip to content

Commit 4b7bc8b

Browse files
fix: reject correlated IN/NOT IN over a groupless aggregate (apache#26677)
## Which issue does this PR close? <!-- We generally require a GitHub issue to be filed for all bug fixes and enhancements and this helps us generate change logs for our releases. You can link an issue to this PR using the GitHub syntax. For example `Closes apache#123` indicates that this PR will close issue apache#123. --> Part of apache#24960. This PR adds a safe rejection until `IN`/`NOT IN` count-bug compensation is implemented. ## Rationale for this change <!-- Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed. Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes. Please explain the problem you are trying to solve in terms of the user-visible behavior, rather than the implementation. For example, "The code in `foo.rs` doesn't handle nulls" is a symptom of the implementation. "COUNT(DISTINCT) returns wrong results when the column contains nulls" is the user-visible problem. --> A correlated `IN`/`NOT IN` over an aggregate without `GROUP BY` returns wrong rows when an outer row has no match (see apache#24960 (comment)). apache#25391 fixed `EXISTS`/`NOT EXISTS`. `IN`/`NOT IN` will be covered in a follow-up, so until then these queries should error instead of returning wrong results. ## What changes are included in this PR? <!-- There is no need to duplicate the description in the issue here, but it is sometimes worth providing a summary of the individual changes in this PR. --> `build_join` in `decorrelate_predicate_subquery.rs` now keeps these subqueries correlated, so they fail to plan with a not-implemented error. The exception is an `IN` in a `WHERE` over an aggregate that is NULL on empty input (e.g. `max`, `sum`), which the current rewrite already answers correctly. ## What is the testing strategy for this PR? <!-- We typically require tests for all PRs in order to: 1. Prevent the code from being accidentally broken by subsequent changes 2. Serve as another way to document the expected behavior of the code Briefly describe how this PR is tested, and point to the specific tests you added. For example: 'This new feature is covered by the `sqllogictest` cases added in `foo.slt`'. If this PR does not add tests, explain why. For example, if the change is already covered by existing tests, please mention it. You should also check the `codecov` bot reply on this PR to confirm the changed code is exercised. --> - The existing IN over max test still passes unchanged. - The existing NOT IN over count test now expects the error. - New tests expect the error for IN over count in a filter and in a projection, NOT IN over max, and IN over max in a projection. ## Are there any user-facing changes? <!-- If there are user-facing changes then we may require documentation to be updated before approving the PR. If there are any breaking changes to public APIs, please add the `api change` label. --> These queries now return a not-implemented error instead of wrong results.
1 parent 63c6ea7 commit 4b7bc8b

2 files changed

Lines changed: 64 additions & 5 deletions

File tree

‎datafusion/optimizer/src/decorrelate_predicate_subquery.rs‎

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -854,6 +854,35 @@ fn build_join(
854854
if !pull_up.can_pull_up {
855855
return Ok(None);
856856
}
857+
// TODO(#24960): stopgap until count-bug compensation covers `IN`/`NOT IN`.
858+
// The subquery stays correlated unless an outer row with no match gets the
859+
// right answer anyway.
860+
if !exists_sub_query && pull_up.pulled_up_scalar_agg {
861+
let null_reads_as_false = match join_type {
862+
JoinType::LeftSemi => true,
863+
JoinType::LeftMark => !needs_null_aware_mark,
864+
_ => false,
865+
};
866+
let count_bug_is_harmless = match rewrite(true) {
867+
Ok((plan, pull_up)) => pull_up
868+
.collected_count_expr_map
869+
.get(&plan)
870+
.filter(|m| !m.is_empty())
871+
.is_none_or(|empty_input_values| {
872+
null_reads_as_false
873+
&& empty_input_values
874+
.get(subquery.schema().field(0).name())
875+
.is_some_and(|value| {
876+
matches!(value, Expr::Literal(v, _) if v.is_null())
877+
})
878+
}),
879+
// Count-bug handling errors on some shapes it cannot compensate.
880+
Err(_) => false,
881+
};
882+
if !count_bug_is_harmless {
883+
return Ok(None);
884+
}
885+
}
857886

858887
let sub_query_alias = LogicalPlanBuilder::from(new_plan)
859888
.alias(alias.to_string())?

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 35 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2124,14 +2124,44 @@ WHERE t1_int + 2 IN (SELECT max(t2_int) FROM t2 WHERE t2.t2_id = t1.t1_id)
21242124
----
21252125
11
21262126

2127-
# correlated_not_in_groupless_count_agg_is_not_count_bug_compensated
2128-
query I rowsort
2127+
# correlated_in_groupless_max_agg_embedded_in_or_is_not_count_bug_compensated
2128+
query I
21292129
SELECT t1_id FROM t1
2130-
WHERE t1_int NOT IN (SELECT count(*) FROM t2 WHERE t2.t2_id = t1.t1_id)
2130+
WHERE t1_int + 2 IN (SELECT max(t2_int) FROM t2 WHERE t2.t2_id = t1.t1_id) OR t1_id = 33
2131+
ORDER BY t1_id
21312132
----
2132-
22
2133+
11
21332134
33
2134-
44
2135+
2136+
# correlated_in_groupless_count_agg_stays_correlated
2137+
# Errors rather than returning wrong rows. Expected `33`.
2138+
query error Physical plan does not support logical expression InSubquery
2139+
SELECT t1_id FROM t1
2140+
WHERE 0 IN (SELECT count(*) FROM t2 WHERE t2.t2_id = t1.t1_id)
2141+
2142+
# correlated_not_in_groupless_count_agg_stays_correlated
2143+
# Errors rather than returning wrong rows. Expected `11, 22, 44`.
2144+
query error Physical plan does not support logical expression InSubquery
2145+
SELECT t1_id FROM t1
2146+
WHERE 0 NOT IN (SELECT count(*) FROM t2 WHERE t2.t2_id = t1.t1_id)
2147+
2148+
# correlated_in_groupless_count_agg_in_projection_stays_correlated
2149+
# Errors rather than returning wrong rows.
2150+
# Expected `(11, false), (22, false), (33, true), (44, false)`.
2151+
query error Physical plan does not support logical expression InSubquery
2152+
SELECT t1_id, 0 IN (SELECT count(*) FROM t2 WHERE t2.t2_id = t1.t1_id) FROM t1
2153+
2154+
# correlated_not_in_groupless_max_agg_stays_correlated
2155+
# Errors rather than returning wrong rows. Expected `22, 44`.
2156+
query error Physical plan does not support logical expression InSubquery
2157+
SELECT t1_id FROM t1
2158+
WHERE t1_int + 2 NOT IN (SELECT max(t2_int) FROM t2 WHERE t2.t2_id = t1.t1_id)
2159+
2160+
# correlated_in_groupless_max_agg_in_projection_stays_correlated
2161+
# Errors rather than returning wrong rows.
2162+
# Expected `(11, true), (22, false), (33, NULL), (44, false)`.
2163+
query error Physical plan does not support logical expression InSubquery
2164+
SELECT t1_id, t1_int + 2 IN (SELECT max(t2_int) FROM t2 WHERE t2.t2_id = t1.t1_id) FROM t1
21352165

21362166
statement ok
21372167
DROP TABLE limit_zero_outer;

0 commit comments

Comments
 (0)