You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "adriangb (via GitHub)" <gi...@apache.org> on 2026/10/13 17:30:15 UTC
[PR] feat: decide automatically when to materialize a CTE [datafusion]
adriangb opened a new pull request, #26276:
URL: https://github.com/apache/datafusion/pull/26276
## Which issue does this PR close?
- Closes apache/datafusion#26237.
Stacked on apache/datafusion#25580 and on #26269 (apache/datafusion#26238). Only the last commit is new.
## Rationale for this change
A CTE that has no directive is inlined at each reference, so its body runs one time for each reference:
```sql
set datafusion.optimizer.enable_automatic_cte_materialization = true;
EXPLAIN WITH c AS (SELECT k, sum(v) AS s FROM t GROUP BY k)
SELECT a.k FROM c a JOIN c b ON a.s = b.s;
```
With the option off, which is the default, `t` is aggregated two times. With the option on, the body is computed one time:
```
MaterializedCte: name=c, automatic=true
SubqueryAlias: c
Projection: t.k, sum(t.v) AS s
Aggregate: groupBy=[[t.k]], aggr=[[sum(CAST(t.v AS Int64))]]
TableScan: t projection=[k, v]
Projection: a.k
Inner Join: a.s = b.s
SubqueryAlias: a
MaterializedCteScan: name=c
SubqueryAlias: b
Projection: c.s
MaterializedCteScan: name=c
```
This works in each SQL dialect. A CTE with one reference, or with a cheap body, plans exactly as before.
## What changes are included in this PR?
- New option `datafusion.optimizer.enable_automatic_cte_materialization`, default `false`. With the default, each query plans as before.
- `MaterializedCte` has the new field `automatic`. When the option is on, the SQL planner plans each non-recursive CTE that has no directive as an automatic `MaterializedCte`.
- New optimizer rule `InlineAutomaticCtes`, the first rule of the optimizer. It replaces an automatic node with a copy of the body in place of each scan when one of these conditions is true:
1. The CTE has zero or one reference.
2. The body has a volatile expression. Materialization would change the result (apache/datafusion#10337).
3. The body has an outer reference.
4. The body is cheap: only projections, filters and aliases on one table scan, materialized CTE scan, `VALUES` list or empty relation, without a subquery expression.
- In all other cases the CTE stays materialized. The rule does not use statistics or a cost model.
- `AS MATERIALIZED` and `AS NOT MATERIALIZED` override the rule.
- A CTE in a `WITH RECURSIVE` block and a CTE with a correlated body are inlined by the SQL planner, as before. `SELECT INTO` with an automatic CTE inlines the CTE.
- An automatic CTE with an unbounded body cannot be buffered. The physical planner inlines it and plans the query again, so a query that worked before still works. This uses a private marker error that `create_initial_plan` catches; a different mechanism is possible.
Known limits:
- For an unbounded body, `EXPLAIN` shows the `MaterializedCte` node in the logical plan and the inlined query in the physical plan, and the inlined copies do not get logical filter pushdown.
- With the option on, a plan that is not optimized has `MaterializedCte` nodes for each CTE. The unparser and the Substrait producer return a "not implemented" error for these nodes.
- "Cheap" does not look at the cost of projection expressions. No benchmarks were run.
## Are these changes tested?
Yes. New file `cte_auto_materialize.slt`: two references to an aggregate body, one reference, no reference, cheap body, volatile body, scan in a subquery expression, CTE read by another CTE, correlated body, recursive CTE, `SELECT INTO`, the two directives, the MySQL dialect, and an unbounded body. Unit tests for `InlineAutomaticCtes` and for `MaterializedCte::inline`. The full sqllogictest suite also passes with the default of the option set to `true`, except for the tests that show the default value.
## Are there any user-facing changes?
Yes. There is a new configuration option, which is off by default, and a new optimizer rule. `MaterializedCte` has the new public field `automatic` and the new methods `with_automatic`, `is_automatic` and `inline`. The upgrade guide for 56.0.0 and the configuration documentation are updated.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: decide automatically when to materialize a CTE [datafusion]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #26276:
URL: https://github.com/apache/datafusion/pull/26276#issuecomment-6157069004
<!-- semver-check-comment -->
Thank you for opening this pull request!
Reviewer note: [cargo-semver-checks](https://github.com/obi1kenobi/cargo-semver-checks) reported the current version number is not SemVer-compatible with the changes in this pull request (compared against the base branch).
<details>
<summary>Details</summary>
```
Cloning apache/main
Building datafusion v55.1.0 (current)
Built [ 53.834s] (current)
Parsing datafusion v55.1.0 (current)
Parsed [ 0.031s] (current)
Building datafusion v55.1.0 (baseline)
Built [ 53.166s] (baseline)
Parsing datafusion v55.1.0 (baseline)
Parsed [ 0.032s] (baseline)
Checking datafusion v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.591s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 109.907s] datafusion
Building datafusion-common v55.1.0 (current)
Built [ 32.237s] (current)
Parsing datafusion-common v55.1.0 (current)
Parsed [ 0.061s] (current)
Building datafusion-common v55.1.0 (baseline)
Built [ 31.530s] (baseline)
Parsing datafusion-common v55.1.0 (baseline)
Parsed [ 0.063s] (baseline)
Checking datafusion-common v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.795s] 223 checks: 222 pass, 1 fail, 0 warn, 31 skip
--- failure constructible_struct_adds_field: struct exhaustively constructible through public API adds field ---
Description:
A pub struct that could be exhaustively constructed with a literal using only public API has a new pub field, breaking existing exhaustive literals.
ref: https://doc.rust-lang.org/reference/expressions/struct-expr.html
impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.50.0/src/lints/constructible_struct_adds_field.ron
Failed in:
field OptimizerOptions.enable_automatic_cte_materialization in /home/runner/work/datafusion/datafusion/datafusion/common/src/config.rs:1643
Summary semver requires new major version: 1 major and 0 minor checks failed
Finished [ 65.806s] datafusion-common
Building datafusion-expr v55.1.0 (current)
Built [ 28.953s] (current)
Parsing datafusion-expr v55.1.0 (current)
Parsed [ 0.082s] (current)
Building datafusion-expr v55.1.0 (baseline)
Built [ 28.790s] (baseline)
Parsing datafusion-expr v55.1.0 (baseline)
Parsed [ 0.085s] (baseline)
Checking datafusion-expr v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 1.294s] 223 checks: 222 pass, 1 fail, 0 warn, 31 skip
--- failure enum_variant_added: enum variant added on exhaustive enum ---
Description:
A publicly-visible enum without #[non_exhaustive] has a new variant.
ref: https://doc.rust-lang.org/cargo/reference/semver.html#enum-variant-new
impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.50.0/src/lints/enum_variant_added.ron
Failed in:
variant LogicalPlan:MaterializedCte in /home/runner/work/datafusion/datafusion/datafusion/expr/src/logical_plan/plan.rs:308
variant LogicalPlan:MaterializedCteScan in /home/runner/work/datafusion/datafusion/datafusion/expr/src/logical_plan/plan.rs:311
variant LogicalPlan:MaterializedCte in /home/runner/work/datafusion/datafusion/datafusion/expr/src/logical_plan/plan.rs:308
variant LogicalPlan:MaterializedCteScan in /home/runner/work/datafusion/datafusion/datafusion/expr/src/logical_plan/plan.rs:311
Summary semver requires new major version: 1 major and 0 minor checks failed
Finished [ 60.409s] datafusion-expr
Building datafusion-optimizer v55.1.0 (current)
Built [ 24.216s] (current)
Parsing datafusion-optimizer v55.1.0 (current)
Parsed [ 0.030s] (current)
Building datafusion-optimizer v55.1.0 (baseline)
Built [ 23.941s] (baseline)
Parsing datafusion-optimizer v55.1.0 (baseline)
Parsed [ 0.033s] (baseline)
Checking datafusion-optimizer v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.168s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 49.124s] datafusion-optimizer
Building datafusion-physical-plan v55.1.0 (current)
Built [ 34.215s] (current)
Parsing datafusion-physical-plan v55.1.0 (current)
Parsed [ 0.183s] (current)
Building datafusion-physical-plan v55.1.0 (baseline)
Built [ 34.241s] (baseline)
Parsing datafusion-physical-plan v55.1.0 (baseline)
Parsed [ 0.177s] (baseline)
Checking datafusion-physical-plan v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.621s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 70.616s] datafusion-physical-plan
Building datafusion-proto v55.1.0 (current)
Built [ 49.041s] (current)
Parsing datafusion-proto v55.1.0 (current)
Parsed [ 0.012s] (current)
Building datafusion-proto v55.1.0 (baseline)
Built [ 48.770s] (baseline)
Parsing datafusion-proto v55.1.0 (baseline)
Parsed [ 0.013s] (baseline)
Checking datafusion-proto v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.107s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 99.005s] datafusion-proto
Building datafusion-sql v55.1.0 (current)
Built [ 39.731s] (current)
Parsing datafusion-sql v55.1.0 (current)
Parsed [ 0.030s] (current)
Building datafusion-sql v55.1.0 (baseline)
Built [ 39.962s] (baseline)
Parsing datafusion-sql v55.1.0 (baseline)
Parsed [ 0.031s] (baseline)
Checking datafusion-sql v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.232s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 80.919s] datafusion-sql
Building datafusion-sqllogictest v55.1.0 (current)
Built [ 90.811s] (current)
Parsing datafusion-sqllogictest v55.1.0 (current)
Parsed [ 0.015s] (current)
Building datafusion-sqllogictest v55.1.0 (baseline)
Built [ 91.074s] (baseline)
Parsing datafusion-sqllogictest v55.1.0 (baseline)
Parsed [ 0.015s] (baseline)
Checking datafusion-sqllogictest v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.092s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 184.268s] datafusion-sqllogictest
Building datafusion-substrait v55.1.0 (current)
Built [ 306.770s] (current)
Parsing datafusion-substrait v55.1.0 (current)
Parsed [ 0.012s] (current)
Building datafusion-substrait v55.1.0 (baseline)
Built [ 309.532s] (baseline)
Parsing datafusion-substrait v55.1.0 (baseline)
Parsed [ 0.012s] (baseline)
Checking datafusion-substrait v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.228s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 618.156s] datafusion-substrait
```
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: decide automatically when to materialize a CTE [datafusion]
Posted by "codecov-commenter (via GitHub)" <gi...@apache.org>.
codecov-commenter commented on PR #26276:
URL: https://github.com/apache/datafusion/pull/26276#issuecomment-6156682489
## [Codecov](https://app.codecov.io/gh/apache/datafusion/pull/26276?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
:x: Patch coverage is `82.95381%` with `262 lines` in your changes missing coverage. Please review.
:white_check_mark: Project coverage is 82.84%. Comparing base ([`2c568b4`](https://app.codecov.io/gh/apache/datafusion/commit/2c568b478b3aff18de52609c7237f1c3f494d5b1?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)) to head ([`6e6a1c8`](https://app.codecov.io/gh/apache/datafusion/commit/6e6a1c8974b6637ac4d7d133ac33e4027816a60e?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)).
| [Files with missing lines](https://app.codecov.io/gh/apache/datafusion/pull/26276?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Patch % | Lines |
|---|---|---|
| [datafusion/physical-plan/src/materialized\_cte.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Fphysical-plan%2Fsrc%2Fmaterialized_cte.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9waHlzaWNhbC1wbGFuL3NyYy9tYXRlcmlhbGl6ZWRfY3RlLnJz) | 86.51% | [46 Missing and 36 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [...tafusion/expr/src/logical\_plan/materialized\_cte.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Fexpr%2Fsrc%2Flogical_plan%2Fmaterialized_cte.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9leHByL3NyYy9sb2dpY2FsX3BsYW4vbWF0ZXJpYWxpemVkX2N0ZS5ycw==) | 80.29% | [38 Missing and 16 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/optimizer/src/inline\_automatic\_ctes.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Foptimizer%2Fsrc%2Finline_automatic_ctes.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9vcHRpbWl6ZXIvc3JjL2lubGluZV9hdXRvbWF0aWNfY3Rlcy5ycw==) | 80.37% | [4 Missing and 48 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/core/src/physical\_planner.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Fcore%2Fsrc%2Fphysical_planner.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9jb3JlL3NyYy9waHlzaWNhbF9wbGFubmVyLnJz) | 83.82% | [10 Missing and 12 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/expr/src/logical\_plan/plan.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Fexpr%2Fsrc%2Flogical_plan%2Fplan.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9leHByL3NyYy9sb2dpY2FsX3BsYW4vcGxhbi5ycw==) | 60.60% | [11 Missing and 2 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/core/src/datasource/view\_test.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Fcore%2Fsrc%2Fdatasource%2Fview_test.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9jb3JlL3NyYy9kYXRhc291cmNlL3ZpZXdfdGVzdC5ycw==) | 76.19% | [3 Missing and 7 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/expr/src/logical\_plan/display.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Fexpr%2Fsrc%2Flogical_plan%2Fdisplay.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9leHByL3NyYy9sb2dpY2FsX3BsYW4vZGlzcGxheS5ycw==) | 0.00% | [8 Missing :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/optimizer/src/analyzer/type\_coercion.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Foptimizer%2Fsrc%2Fanalyzer%2Ftype_coercion.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9vcHRpbWl6ZXIvc3JjL2FuYWx5emVyL3R5cGVfY29lcmNpb24ucnM=) | 89.06% | [4 Missing and 3 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [...ion/substrait/src/logical\_plan/producer/rel/mod.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Fsubstrait%2Fsrc%2Flogical_plan%2Fproducer%2Frel%2Fmod.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9zdWJzdHJhaXQvc3JjL2xvZ2ljYWxfcGxhbi9wcm9kdWNlci9yZWwvbW9kLnJz) | 0.00% | [4 Missing :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/optimizer/src/decorrelate.rs](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&filepath=datafusion%2Foptimizer%2Fsrc%2Fdecorrelate.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9vcHRpbWl6ZXIvc3JjL2RlY29ycmVsYXRlLnJz) | 78.57% | [1 Missing and 2 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| ... and [5 more](https://app.codecov.io/gh/apache/datafusion/pull/26276?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | |
<details><summary>Additional details and impacted files</summary>
```diff
@@ Coverage Diff @@
## main #26276 +/- ##
==========================================
+ Coverage 82.83% 82.84% +0.01%
==========================================
Files 1149 1152 +3
Lines 452798 454319 +1521
Branches 452798 454319 +1521
==========================================
+ Hits 375082 376401 +1319
- Misses 54893 54955 +62
- Partials 22823 22963 +140
```
</details>
[:umbrella: View full report in Codecov by Harness](https://app.codecov.io/gh/apache/datafusion/pull/26276?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache).
<details><summary> :rocket: New features to boost your workflow: </summary>
- :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
- :package: [JS Bundle Analysis](https://docs.codecov.com/docs/javascript-bundle-analysis): Save yourself from yourself by tracking and limiting bundle sizes in JS merges.
</details>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org