You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Commit cee7bae
Browse filesBrowse the repository at this point in the historyBrowse files
Support predicate subqueries in projections (apache#24972)
## Which issue does this PR close?
- Closesapache#23022.
## Rationale for this change
Predicate subqueries such as `EXISTS`, `IN`, and `NOT IN` are valid
scalar expressions in a `SELECT` list, but DataFusion currently only
decorrelates them when they appear in filters. Projection queries
therefore reach physical planning with an unsupported logical subquery
expression.
This continues the work from the now-closed stale apache#23039 and covers
multiple and correlated projection subqueries as well as SQL null
semantics.
## What changes are included in this PR?
- decorrelate `EXISTS`, `NOT EXISTS`, `IN`, and `NOT IN` expressions
inside projections into mark joins
- materialize projected `IN`/`NOT IN` values with SQL three-valued logic
by tracking matches, nulls, and empty subqueries
- preserve projection output names and avoid duplicate columns in
correlated subquery projections
## What is the testing strategy for this PR?
Optimizer plan tests cover projected `IN` rewriting. New SQLLogicTest
cases cover uncorrelated and correlated `EXISTS`/`IN`, multiple
subqueries in one projection, empty inputs, null inputs, and `NOT IN`
three-valued logic.
Locally validated with:
- `cargo test -p datafusion-optimizer decorrelate_predicate_subquery`
(53 passed)
- `cargo test -p datafusion-sqllogictest --test sqllogictests --
subquery_projection`
- `cargo clippy -p datafusion-optimizer --all-targets --all-features --
-D warnings`
- `cargo fmt --all -- --check`
## Are there any user-facing changes?
Yes. Predicate subqueries can now be evaluated as projected scalar
expressions. There are no public API changes.
Projection: CASE WHEN __correlated_sq_1.mark THEN Boolean(true) WHEN __correlated_sq_2.mark OR test.c IS NULL AND __correlated_sq_3.mark THEN Boolean(NULL) ELSE Boolean(false) END AS is_present [is_present:Boolean;N]
Copy file name to clipboardExpand all lines: datafusion/sqllogictest/test_files/predicates.slt
+3-1Lines changed: 3 additions & 1 deletion
Original file line number
Diff line number
Diff line change
@@ -1008,12 +1008,14 @@ explain select x from t where x NOT IN (1,2,3,4,5) AND x IN (1,2,3);
1008
1008
logical_plan EmptyRelation: rows=0
1009
1009
physical_plan EmptyExec
1010
1010
1011
-
query error DataFusion error: This feature is not implemented: Physical plan does not support logical expression InSubquery\(InSubquery \{ expr: Literal\(Int64\(NULL\), None\), subquery: <subquery>, negated: false \}\)
1011
+
query BB
1012
1012
WITH empty AS (SELECT 10 WHERE false)
1013
1013
SELECT
1014
1014
NULL IN (SELECT * FROM empty), -- should be false, as the right side is empty relation
1015
1015
NULL NOT IN (SELECT * FROM empty) -- should be true, as the right side is empty relation
# Schema contains duplicate unqualified field name __datafusion_extracted_1.
2298
-
# With the fix, the generator starts at 2, as the plan below shows.
2298
+
# With the fix, generated aliases remain distinct, as the plan below shows.
2299
2299
#
2300
-
# This is `EXPLAIN` under `logical_plan_only` rather than an executed query
2301
-
# because a surviving `InSubquery` expression has no physical plan; the logical
2302
-
# plan is both the observable result and exactly what regressed.
2300
+
# Keep this as `EXPLAIN` under `logical_plan_only`: the logical plan exposes both
2301
+
# the collision-free extracted aliases and the mark joins used to preserve the
2302
+
# three-valued semantics of `IN` in a projection.
2303
2303
#####################
2304
2304
2305
2305
statement ok
@@ -2321,13 +2321,22 @@ SELECT
2321
2321
FROM simple_struct;
2322
2322
----
2323
2323
logical_plan
2324
-
01)Projection: simple_struct.id, simple_struct.id IN (<subquery>) AS has_matching_label
2325
-
02)--Subquery:
2326
-
03)----Projection: simple_struct.id
2327
-
04)------Filter: __datafusion_extracted_2 > Int64(120) AND __datafusion_extracted_1 != Utf8("delta")
2328
-
05)--------Projection: get_field(simple_struct.s, Utf8("value")) AS __datafusion_extracted_2, simple_struct.id, get_field(simple_struct.s, Utf8("label")) AS __datafusion_extracted_1
01)Projection: simple_struct.id, __correlated_sq_1.mark IS NOT DISTINCT FROM Boolean(true) OR __correlated_sq_2.mark IS NOT DISTINCT FROM Boolean(true) AND __correlated_sq_1.mark IS DISTINCT FROM Boolean(true) AND Boolean(NULL) AS has_matching_label
08)------------Filter: __datafusion_extracted_4 > Int64(120) AND __datafusion_extracted_1 != Utf8("delta")
2332
+
09)--------------Projection: get_field(simple_struct.s, Utf8("value")) AS __datafusion_extracted_4, simple_struct.id, get_field(simple_struct.s, Utf8("label")) AS __datafusion_extracted_1
14)--------Filter: __datafusion_extracted_6 > Int64(120) AND __datafusion_extracted_1 != Utf8("delta")
2338
+
15)----------Projection: get_field(simple_struct.s, Utf8("value")) AS __datafusion_extracted_6, simple_struct.id, get_field(simple_struct.s, Utf8("label")) AS __datafusion_extracted_1
0 commit comments