Skip to content

Commit 1c21ce9

Browse files
[branch-54] fix: = ANY (SELECT ...) / <> ALL (SELECT ...) schema error (backport #22915) (#23575)
## Which issue does this PR close? - Backport of #22915 to `branch-54` (for 54.1.0, tracked in #22547). - Fixes planning failure #22477 (regression introduced in 54.0.0 by #21265). ## Rationale for this change `= ANY (SELECT ...)` and `<> ALL (SELECT ...)` fail to plan in 54.0.0 with `duplicate unqualified field name mark`. Empirically verified working on the published 53.1.0 crate. No API changes, so it fits the backport criteria. ## What changes are included in this PR? Clean cherry-pick of #22915. No adaptation required. ## Are these changes tested? Yes. Carries the original regression coverage (`subquery.slt`), all tests pass. ## Are there any user-facing changes? These subquery forms plan again. No API changes. Co-authored-by: H <[email protected]>
1 parent 2ec55f9 commit 1c21ce9

2 files changed

Lines changed: 87 additions & 2 deletions

File tree

‎datafusion/optimizer/src/optimize_projections/mod.rs‎

Lines changed: 68 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -410,10 +410,20 @@ fn optimize_projections(
410410
let right_len = join.right.schema().fields().len();
411411
let (left_req_indices, right_req_indices) =
412412
split_join_requirements(left_len, right_len, indices, &join.join_type);
413-
let left_indices =
413+
let mut left_indices =
414414
left_req_indices.with_plan_exprs(&plan, join.left.schema())?;
415-
let right_indices =
415+
let mut right_indices =
416416
right_req_indices.with_plan_exprs(&plan, join.right.schema())?;
417+
// Ensure an empty mark join still has a column to qualify mark
418+
match join.join_type {
419+
JoinType::LeftMark if right_indices.indices().is_empty() => {
420+
right_indices = right_indices.append(&[0]);
421+
}
422+
JoinType::RightMark if left_indices.indices().is_empty() => {
423+
left_indices = left_indices.append(&[0]);
424+
}
425+
_ => {}
426+
}
417427
// Joins benefit from "small" input tables (lower memory usage).
418428
// Therefore, each child benefits from projection:
419429
vec![
@@ -2446,6 +2456,62 @@ mod tests {
24462456
)
24472457
}
24482458

2459+
// Stacked filter-less LeftMark joins (from `= ANY` / `<> ALL`) must keep
2460+
// each `mark` qualified so they don't collide.
2461+
#[test]
2462+
fn optimize_projections_stacked_mark_joins_keep_qualified_mark() -> Result<()> {
2463+
let person = test_table_scan_with_name("person")?;
2464+
2465+
let aliased_scan = |table: &str, alias: &str| -> Result<LogicalPlan> {
2466+
LogicalPlanBuilder::from(test_table_scan_with_name(table)?)
2467+
.project(vec![col(format!("{table}.a"))])?
2468+
.alias(alias)?
2469+
.build()
2470+
};
2471+
2472+
let plan = LogicalPlanBuilder::from(person)
2473+
.join_on(
2474+
aliased_scan("s1", "__correlated_sq_1")?,
2475+
JoinType::LeftMark,
2476+
vec![lit(true)],
2477+
)?
2478+
.join_on(
2479+
aliased_scan("s2", "__correlated_sq_2")?,
2480+
JoinType::LeftMark,
2481+
vec![lit(true)],
2482+
)?
2483+
.join_on(
2484+
aliased_scan("s3", "__correlated_sq_3")?,
2485+
JoinType::LeftMark,
2486+
vec![lit(true)],
2487+
)?
2488+
.filter(
2489+
col("__correlated_sq_1.mark")
2490+
.or(col("__correlated_sq_2.mark"))
2491+
.and(not(col("__correlated_sq_3.mark"))),
2492+
)?
2493+
.project(vec![col("person.a")])?
2494+
.build()?;
2495+
2496+
assert_optimized_plan_equal!(
2497+
plan,
2498+
@r"
2499+
Projection: person.a
2500+
Filter: (__correlated_sq_1.mark OR __correlated_sq_2.mark) AND NOT __correlated_sq_3.mark
2501+
LeftMark Join: Filter: Boolean(true)
2502+
LeftMark Join: Filter: Boolean(true)
2503+
LeftMark Join: Filter: Boolean(true)
2504+
TableScan: person projection=[a]
2505+
SubqueryAlias: __correlated_sq_1
2506+
TableScan: s1 projection=[a]
2507+
SubqueryAlias: __correlated_sq_2
2508+
TableScan: s2 projection=[a]
2509+
SubqueryAlias: __correlated_sq_3
2510+
TableScan: s3 projection=[a]
2511+
"
2512+
)
2513+
}
2514+
24492515
fn observe(_plan: &LogicalPlan, _rule: &dyn OptimizerRule) {}
24502516

24512517
fn optimize(plan: LogicalPlan) -> Result<LogicalPlan> {

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1598,6 +1598,25 @@ logical_plan
15981598
21)----------Projection: column1 AS v
15991599
22)------------Values: (Int64(5)), (Int64(NULL))
16001600

1601+
# same-table `= ANY` / `<> ALL` must plan without
1602+
# "duplicate unqualified field name mark".
1603+
statement ok
1604+
create table set_cmp_self(id int, age int) as values (1, 20), (2, 30), (3, 40);
1605+
1606+
query I rowsort
1607+
select id from set_cmp_self where age = any(select age from set_cmp_self);
1608+
----
1609+
1
1610+
2
1611+
3
1612+
1613+
query I
1614+
select id from set_cmp_self where age <> all(select age from set_cmp_self);
1615+
----
1616+
1617+
statement count 0
1618+
drop table set_cmp_self;
1619+
16011620
# correlated_recursive_scalar_subquery_with_level_3_exists_subquery_referencing_level1_relation
16021621
query TT
16031622
explain select c_custkey from customer

0 commit comments

Comments
 (0)