Skip to content

Commit 836b4dd

Browse files
fix(optimizer): preserve correlated columns when removing filter above SubqueryAlias (#26551)
## Which issue does this PR close? - Closes #26504. ## Rationale for this change When a correlated subquery contains an aliased derived table (`SubqueryAlias`) with a correlated predicate inside it and a `Filter` directly above the `SubqueryAlias` whose predicates are all pulled up as join filters (for example `WHERE x.d > 0`), `PullUpCorrelatedExpr::f_up` eliminates the `Filter` and rebuilds `new_plan` equal to `*plan_filter.input` (`SubqueryAlias`). Previously, `self.correlated_subquery_cols_map.insert(new_plan.clone(), correlated_subquery_cols)` overwrote the entry that `SubqueryAlias` had already recorded for `new_plan`. Because `collect_local_correlated_cols` treats `SubqueryAlias` as a leaf node and does not recurse into its input, any `Projection` or `Aggregate` above the eliminated `Filter` lost the correlated columns from the derived table and failed with `Schema error: No field named __correlated_sq_1.c`. ## What changes are included in this PR? - Use `.entry(new_plan.clone()).or_default().extend(correlated_subquery_cols)` in `PullUpCorrelatedExpr::f_up` when handling `LogicalPlan::Filter` so existing correlated columns recorded on `new_plan` are preserved when the `Filter` is removed. - Add optimizer unit tests in `decorrelate_predicate_subquery` and `scalar_subquery_to_join` and sqllogictest coverage in `subquery.slt`. ## What is the testing strategy for this PR? Covered by unit tests in `datafusion-optimizer` (`exists_subquery_filter_above_correlated_derived_table` and `correlated_scalar_subquery_filter_above_correlated_derived_table`) and sqllogictest cases in `subquery.slt`. ## Are there any user-facing changes? Correlated `EXISTS`, `IN`, and scalar subqueries that filter an aliased derived table on an outer column now plan and execute instead of failing with a schema error.
1 parent 702803d commit 836b4dd

4 files changed

Lines changed: 102 additions & 2 deletions

File tree

‎datafusion/optimizer/src/decorrelate.rs‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -494,7 +494,9 @@ impl TreeNodeRewriter for PullUpCorrelatedExpr {
494494
LogicalPlanBuilder::from((*plan_filter.input).clone())
495495
.build()?;
496496
self.correlated_subquery_cols_map
497-
.insert(new_plan.clone(), correlated_subquery_cols);
497+
.entry(new_plan.clone())
498+
.or_default()
499+
.extend(correlated_subquery_cols);
498500
Ok(Transformed::yes(new_plan))
499501
}
500502
(None, _) => {
@@ -506,7 +508,9 @@ impl TreeNodeRewriter for PullUpCorrelatedExpr {
506508
}
507509
let new_plan = plan.build()?;
508510
self.correlated_subquery_cols_map
509-
.insert(new_plan.clone(), correlated_subquery_cols);
511+
.entry(new_plan.clone())
512+
.or_default()
513+
.extend(correlated_subquery_cols);
510514
Ok(Transformed::yes(new_plan))
511515
}
512516
}

‎datafusion/optimizer/src/decorrelate_predicate_subquery.rs‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3507,4 +3507,38 @@ mod tests {
35073507
"
35083508
)
35093509
}
3510+
3511+
#[test]
3512+
fn exists_subquery_filter_above_correlated_derived_table() -> Result<()> {
3513+
let sq_scan = test_table_scan_with_name("sq")?;
3514+
let derived = LogicalPlanBuilder::from(sq_scan)
3515+
.filter(col("sq.c").eq(out_ref_col(DataType::UInt32, "test.a")))?
3516+
.project(vec![col("sq.c")])?
3517+
.alias("t")?
3518+
.build()?;
3519+
let subquery = LogicalPlanBuilder::from(derived)
3520+
.filter(out_ref_col(DataType::UInt32, "test.b").gt(lit(0u32)))?
3521+
.project(vec![lit(1)])?
3522+
.build()?;
3523+
3524+
let outer_scan = test_table_scan()?;
3525+
let plan = LogicalPlanBuilder::from(outer_scan)
3526+
.filter(exists(Arc::new(subquery)))?
3527+
.project(vec![col("test.a")])?
3528+
.build()?;
3529+
3530+
assert_optimized_plan_equal!(
3531+
plan,
3532+
@r"
3533+
Projection: test.a [a:UInt32]
3534+
LeftSemi Join: Filter: __correlated_sq_1.c = test.a AND test.b > UInt32(0) [a:UInt32, b:UInt32, c:UInt32]
3535+
TableScan: test [a:UInt32, b:UInt32, c:UInt32]
3536+
SubqueryAlias: __correlated_sq_1 [Int32(1):Int32, c:UInt32]
3537+
Projection: Int32(1), t.c [Int32(1):Int32, c:UInt32]
3538+
SubqueryAlias: t [c:UInt32]
3539+
Projection: sq.c [c:UInt32]
3540+
TableScan: sq [a:UInt32, b:UInt32, c:UInt32]
3541+
"
3542+
)
3543+
}
35103544
}

‎datafusion/optimizer/src/scalar_subquery_to_join.rs‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1276,4 +1276,46 @@ mod tests {
12761276

12771277
Ok(())
12781278
}
1279+
1280+
#[test]
1281+
fn correlated_scalar_subquery_filter_above_correlated_derived_table() -> Result<()> {
1282+
let derived = LogicalPlanBuilder::from(scan_tpch_table("orders"))
1283+
.filter(
1284+
col("orders.o_custkey")
1285+
.eq(out_ref_col(DataType::Int64, "customer.c_custkey")),
1286+
)?
1287+
.aggregate(
1288+
Vec::<Expr>::new(),
1289+
vec![max(col("orders.o_custkey")).alias("n")],
1290+
)?
1291+
.project(vec![col("n")])?
1292+
.alias("t")?
1293+
.build()?;
1294+
1295+
let sq = Arc::new(
1296+
LogicalPlanBuilder::from(derived)
1297+
.filter(out_ref_col(DataType::Int64, "customer.c_custkey").gt(lit(0i64)))?
1298+
.project(vec![col("t.n")])?
1299+
.build()?,
1300+
);
1301+
1302+
let plan = LogicalPlanBuilder::from(scan_tpch_table("customer"))
1303+
.project(vec![col("customer.c_custkey"), scalar_subquery(sq)])?
1304+
.build()?;
1305+
1306+
assert_optimized_plan_equal!(
1307+
plan,
1308+
@r"
1309+
Projection: customer.c_custkey, __scalar_sq_1.n AS n [c_custkey:Int64, n:Int64;N]
1310+
Left Join: Filter: __scalar_sq_1.o_custkey = customer.c_custkey AND customer.c_custkey > Int64(0) [c_custkey:Int64, c_name:Utf8, n:Int64;N, o_custkey:Int64;N, __always_true:Boolean;N]
1311+
TableScan: customer [c_custkey:Int64, c_name:Utf8]
1312+
SubqueryAlias: __scalar_sq_1 [n:Int64;N, o_custkey:Int64, __always_true:Boolean]
1313+
Projection: t.n, t.o_custkey, t.__always_true [n:Int64;N, o_custkey:Int64, __always_true:Boolean]
1314+
SubqueryAlias: t [n:Int64;N, o_custkey:Int64, __always_true:Boolean]
1315+
Projection: n, orders.o_custkey, __always_true [n:Int64;N, o_custkey:Int64, __always_true:Boolean]
1316+
Aggregate: groupBy=[[orders.o_custkey, Boolean(true) AS __always_true]], aggr=[[max(orders.o_custkey) AS n]] [o_custkey:Int64, __always_true:Boolean, n:Int64;N]
1317+
TableScan: orders [o_orderkey:Int64, o_custkey:Int64, o_orderstatus:Utf8, o_totalprice:Float64;N]
1318+
"
1319+
)
1320+
}
12791321
}

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4195,3 +4195,23 @@ DROP TABLE emp;
41954195

41964196
statement ok
41974197
RESET datafusion.explain.logical_plan_only;
4198+
4199+
# Regression test for #26504: correlated subquery filtering an aliased derived
4200+
# table on an outer column must preserve the derived table's correlated columns.
4201+
query I
4202+
SELECT * FROM (VALUES (1)) AS x(d)
4203+
WHERE EXISTS (SELECT 1 FROM (SELECT * FROM (VALUES (1)) AS y(c) WHERE y.c = x.d) AS t WHERE x.d > 0);
4204+
----
4205+
1
4206+
4207+
query I
4208+
SELECT (SELECT n FROM (SELECT sum(y.c) AS n FROM (VALUES (1)) AS y(c) WHERE y.c = x.d) AS t WHERE x.d > 0) AS v
4209+
FROM (VALUES (1)) AS x(d);
4210+
----
4211+
1
4212+
4213+
query I
4214+
SELECT * FROM (VALUES (1)) AS x(d)
4215+
WHERE x.d IN (SELECT t.c FROM (SELECT * FROM (VALUES (1)) AS y(c) WHERE y.c = x.d) AS t WHERE x.d > 0);
4216+
----
4217+
1

0 commit comments

Comments
 (0)