Skip to content

Commit 063402b

Browse files
adriangbclaude
andcommitted
feat: decide automatically when to materialize a CTE
Add the option `datafusion.optimizer.enable_automatic_cte_materialization`, which is off by default. With the default, each query plans as before. When the option is on, the SQL planner plans each non-recursive CTE that has no directive as a `MaterializedCte` with `automatic = true`, in each dialect. The new optimizer rule `InlineAutomaticCtes` replaces an automatic node with `MaterializedCte::inline` when the CTE has zero or one reference, or when its body is volatile, has an outer reference, or is only projections and filters on one table, or when a `LIMIT` with a fetch is above all references in the chain of one-input nodes from the root of the query. All other automatic CTEs stay materialized and are computed one time. `AS MATERIALIZED` and `AS NOT MATERIALIZED` override the rule. This commit completes the feature: the physical planner inlines an automatic CTE that has an unbounded body, because such a body cannot be buffered and the query worked before. Planning such a body returns an internal `UnboundedAutomaticCte` error, and `create_initial_plan` catches it, inlines that CTE and plans again. An explicit `AS MATERIALIZED` CTE with an unbounded body still returns a planning error. The diff of `cte_auto_materialize.slt` shows the unbounded cases that now plan and run. Closes apache#26237. Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent abe5f5d commit 063402b

2 files changed

Lines changed: 119 additions & 3 deletions

File tree

‎datafusion/core/src/physical_planner.rs‎

Lines changed: 80 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -316,6 +316,47 @@ fn renumber_materialized_ctes(plan: &LogicalPlan) -> Result<Option<LogicalPlan>>
316316
.map(|t| Some(t.data))
317317
}
318318

319+
/// The body of the automatic [`MaterializedCte`] with this id is unbounded, so
320+
/// the CTE must be inlined. This error does not reach the user:
321+
/// `create_initial_plan` handles it.
322+
#[derive(Debug)]
323+
struct UnboundedAutomaticCte(MaterializedCteId);
324+
325+
impl std::fmt::Display for UnboundedAutomaticCte {
326+
fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
327+
write!(
328+
f,
329+
"The automatic materialized CTE {} reads an unbounded source",
330+
self.0
331+
)
332+
}
333+
}
334+
335+
impl std::error::Error for UnboundedAutomaticCte {}
336+
337+
/// Replace each occurrence of the [`MaterializedCte`] `id` in `plan` with its
338+
/// continuation, in which each scan is a copy of the body. A copy of the body
339+
/// can hold another materialized CTE, so the CTEs get new ids.
340+
fn inline_materialized_cte(
341+
plan: &LogicalPlan,
342+
id: MaterializedCteId,
343+
) -> Result<LogicalPlan> {
344+
let inlined = plan
345+
.clone()
346+
.transform_up_with_subqueries(|node| match node {
347+
LogicalPlan::MaterializedCte(MaterializedCtePlan::Cte(cte))
348+
if cte.id == id =>
349+
{
350+
Ok(Transformed::yes(cte.inline()?))
351+
}
352+
_ => Ok(Transformed::no(node)),
353+
})?;
354+
if !inlined.transformed {
355+
return internal_err!("The materialized CTE {id} is not in the plan");
356+
}
357+
Ok(renumber_materialized_ctes(&inlined.data)?.unwrap_or(inlined.data))
358+
}
359+
319360
/// Points every [`MaterializedCteScanExec`] at the buffer of the
320361
/// [`MaterializedCteExec`] with the same id, and gives it the statistics of
321362
/// the CTE body.
@@ -574,6 +615,38 @@ impl DefaultPhysicalPlanner {
574615
&'a self,
575616
logical_plan: &'a LogicalPlan,
576617
session_state: &'a dyn Session,
618+
) -> BoxFuture<'a, Result<Arc<dyn ExecutionPlan>>> {
619+
Box::pin(async move {
620+
// An automatic materialized CTE with an unbounded body cannot be
621+
// buffered. The query worked before the CTE was materialized, so
622+
// inline the CTE and plan again.
623+
let mut inlined: Option<LogicalPlan> = None;
624+
loop {
625+
let plan = inlined.as_ref().unwrap_or(logical_plan);
626+
let result = self.create_initial_plan_once(plan, session_state).await;
627+
let id = match &result {
628+
Err(e) => match e.find_root() {
629+
DataFusionError::External(e) => {
630+
e.downcast_ref::<UnboundedAutomaticCte>().map(|e| e.0)
631+
}
632+
_ => None,
633+
},
634+
Ok(_) => None,
635+
};
636+
let Some(id) = id else {
637+
return result;
638+
};
639+
inlined = Some(inline_materialized_cte(plan, id)?);
640+
}
641+
})
642+
}
643+
644+
/// Plans `logical_plan` and its uncorrelated scalar subqueries. See
645+
/// [`Self::create_initial_plan`].
646+
fn create_initial_plan_once<'a>(
647+
&'a self,
648+
logical_plan: &'a LogicalPlan,
649+
session_state: &'a dyn Session,
577650
) -> BoxFuture<'a, Result<Arc<dyn ExecutionPlan>>> {
578651
Box::pin(async move {
579652
// When `enable_physical_uncorrelated_scalar_subquery` is disabled, the
@@ -1941,6 +2014,13 @@ impl DefaultPhysicalPlanner {
19412014
// The body is fully buffered before a scan yields a row, so an
19422015
// unbounded body would never produce output.
19432016
if body.boundedness().is_unbounded() {
2017+
// The user did not ask for this CTE to be materialized.
2018+
// `create_initial_plan` inlines it and plans again.
2019+
if cte.is_automatic() {
2020+
return Err(DataFusionError::External(Box::new(
2021+
UnboundedAutomaticCte(cte.id),
2022+
)));
2023+
}
19442024
return plan_err!(
19452025
"MATERIALIZED CTE {} reads an unbounded source, which cannot be materialized",
19462026
cte.name

‎datafusion/sqllogictest/test_files/cte_auto_materialize.slt‎

Lines changed: 39 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -638,7 +638,22 @@ logical_plan
638638
08)--Union
639639
09)----MaterializedCteScan: name=u
640640
10)----MaterializedCteScan: name=u
641-
physical_plan_error Error during planning: MATERIALIZED CTE u reads an unbounded source, which cannot be materialized
641+
physical_plan
642+
01)UnionExec
643+
02)--UnionExec
644+
03)----ProjectionExec: expr=[CAST(c1@0 AS Int64) as c1]
645+
04)------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
646+
05)--------StreamingTableExec: partition_sizes=1, projection=[c1], infinite_source=true
647+
06)----ProjectionExec: expr=[CAST(c1@0 AS Int64) + 1 as c1]
648+
07)------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
649+
08)--------StreamingTableExec: partition_sizes=1, projection=[c1], infinite_source=true
650+
09)--UnionExec
651+
10)----ProjectionExec: expr=[CAST(c1@0 AS Int64) as c1]
652+
11)------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
653+
12)--------StreamingTableExec: partition_sizes=1, projection=[c1], infinite_source=true
654+
13)----ProjectionExec: expr=[CAST(c1@0 AS Int64) + 1 as c1]
655+
14)------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
656+
15)--------StreamingTableExec: partition_sizes=1, projection=[c1], infinite_source=true
642657

643658
# The same with one reference in a scalar subquery.
644659
query TT
@@ -658,12 +673,33 @@ logical_plan
658673
10)------Limit: skip=0, fetch=1
659674
11)--------MaterializedCteScan: name=u
660675
12)----MaterializedCteScan: name=u
661-
physical_plan_error Error during planning: MATERIALIZED CTE u reads an unbounded source, which cannot be materialized
676+
physical_plan
677+
01)ScalarSubqueryExec: subqueries=1
678+
02)--UnionExec
679+
03)----FilterExec: c1@0 = scalar_subquery(<pending>)
680+
04)------ProjectionExec: expr=[CAST(c1@0 AS Int64) as c1]
681+
05)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
682+
06)----------StreamingTableExec: partition_sizes=1, projection=[c1], infinite_source=true
683+
07)----FilterExec: c1@0 = scalar_subquery(<pending>)
684+
08)------ProjectionExec: expr=[CAST(c1@0 AS Int64) + 1 as c1]
685+
09)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
686+
10)----------StreamingTableExec: partition_sizes=1, projection=[c1], infinite_source=true
687+
11)--CoalescePartitionsExec: fetch=1
688+
12)----UnionExec
689+
13)------ProjectionExec: expr=[CAST(c1@0 AS Int64) as c1]
690+
14)--------LocalLimitExec: fetch=1
691+
15)----------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
692+
16)------------StreamingTableExec: partition_sizes=1, projection=[c1], infinite_source=true
693+
17)------ProjectionExec: expr=[CAST(c1@0 AS Int64) + 1 as c1]
694+
18)--------LocalLimitExec: fetch=1
695+
19)----------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
696+
20)------------StreamingTableExec: partition_sizes=1, projection=[c1], infinite_source=true
662697

663698
# The query also runs. The file has no rows.
664-
query error DataFusion error: Error during planning: MATERIALIZED CTE u reads an unbounded source, which cannot be materialized
699+
query I
665700
WITH u AS (SELECT c1 FROM unbounded_t UNION ALL SELECT c1 + 1 FROM unbounded_t)
666701
SELECT c1 FROM u WHERE c1 = (SELECT c1 FROM u LIMIT 1)
702+
----
667703

668704
statement ok
669705
DROP TABLE unbounded_t;

0 commit comments

Comments
 (0)