Skip to content

Commit bb038a6

Browse files
fix: ScalarSubqueryToJoin fails when CTE inside correlated scalar subquery (#24363)
## Which issue does this PR close? - Closes #24139 ## Rationale for this change The `scalar_subquery_to_join` optimizer rule was failing with a `Schema error: No field named __always_true` when a CTE was used inside a correlated scalar subquery. This occurred because the `SubqueryAlias` logical plan node wasn't refreshing its schema after the `__always_true` column was injected by the correlated expression pull-up logic (`PullUpCorrelatedExpr`). Because the schema became stale, the parent `Projection` node would attempt to reference the newly injected `__always_true` column and fail with a schema error. ## What changes are included in this PR? - Updated `PullUpCorrelatedExpr::f_up` logic for `LogicalPlan::SubqueryAlias` in `datafusion/optimizer/src/decorrelate.rs`. - We now check if the `SubqueryAlias`'s input schema has a different number of fields than its own recorded schema. If so, we recreate the `SubqueryAlias` using `LogicalPlanBuilder` so that its schema correctly reflects any newly added columns (such as `__always_true`). ## Are these changes tested? Yes, they are covered by the existing sqllogictests and optimizer integration tests. Queries with CTEs inside correlated scalar subqueries that previously crashed the optimizer will now correctly optimize and execute. ## Are there any user-facing changes? No, this is strictly an internal optimizer bug fix.
1 parent 9a83691 commit bb038a6

2 files changed

Lines changed: 46 additions & 3 deletions

File tree

‎datafusion/optimizer/src/decorrelate.rs‎

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -350,15 +350,31 @@ impl TreeNodeRewriter for PullUpCorrelatedExpr {
350350
new_correlated_cols
351351
.insert(Column::new(Some(alias.alias.clone()), col.name.clone()));
352352
}
353+
354+
let new_plan = if alias.input.schema().fields().len()
355+
!= alias.schema.fields().len()
356+
{
357+
LogicalPlanBuilder::from((*alias.input).clone())
358+
.alias(alias.alias.clone())?
359+
.build()?
360+
} else {
361+
plan.clone()
362+
};
363+
353364
self.correlated_subquery_cols_map
354-
.insert(plan.clone(), new_correlated_cols);
365+
.insert(new_plan.clone(), new_correlated_cols);
355366
if let Some(input_map) =
356367
self.collected_count_expr_map.get(alias.input.deref())
357368
{
358369
self.collected_count_expr_map
359-
.insert(plan.clone(), input_map.clone());
370+
.insert(new_plan.clone(), input_map.clone());
371+
}
372+
373+
if new_plan != plan {
374+
Ok(Transformed::yes(new_plan))
375+
} else {
376+
Ok(Transformed::no(plan))
360377
}
361-
Ok(Transformed::no(plan))
362378
}
363379
LogicalPlan::Limit(limit) => {
364380
let input_expr_map = self

‎datafusion/sqllogictest/test_files/subquery.slt‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2657,3 +2657,30 @@ DROP TABLE nia_right_no_null;
26572657

26582658
statement ok
26592659
reset datafusion.optimizer.prefer_hash_join;
2660+
2661+
# Regression test for #24139: CTE inside a correlated scalar subquery causing a schema error
2662+
statement ok
2663+
CREATE TABLE metrics (host VARCHAR, bytes_sent DOUBLE, ts BIGINT) AS VALUES
2664+
('a', 100.0, 1),
2665+
('b', 200.0, 2),
2666+
('a', 300.0, 3),
2667+
('b', 400.0, 4);
2668+
2669+
query TR
2670+
SELECT m.host, m.bytes_sent
2671+
FROM metrics m
2672+
WHERE m.bytes_sent > (
2673+
WITH avg_per_host AS (
2674+
SELECT avg(bytes_sent) AS avg_bytes
2675+
FROM metrics
2676+
WHERE host = m.host
2677+
)
2678+
SELECT avg_bytes FROM avg_per_host
2679+
)
2680+
ORDER BY m.host;
2681+
----
2682+
a 300
2683+
b 400
2684+
2685+
statement ok
2686+
DROP TABLE metrics;

0 commit comments

Comments
 (0)