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:13 UTC
[PR] feat: push filters that all references share into the body of a materialized CTE [datafusion]
adriangb opened a new pull request, #26275:
URL: https://github.com/apache/datafusion/pull/26275
## Which issue does this PR close?
- Closes apache/datafusion#26239.
Stacked on apache/datafusion#25580 and on #26269 (apache/datafusion#26238). Only the last commit is new.
## Rationale for this change
The body of a materialized CTE is shared by all references, so `PushDownFilter` keeps the filter of a reference above that reference. The body is computed and buffered in full, also when each reference reads a small part of it.
```sql
WITH c AS MATERIALIZED (SELECT * FROM events)
SELECT * FROM c WHERE day = '2026-01-01'
UNION ALL
SELECT * FROM c WHERE day = '2026-01-02';
```
Before, all of `events` is read and buffered:
```text
MaterializedCte: name=c
SubqueryAlias: c
TableScan: events projection=[day, n]
Union
Filter: c.day = Utf8View("2026-01-01")
MaterializedCteScan: name=c
Filter: c.day = Utf8View("2026-01-02")
MaterializedCteScan: name=c
```
After, the body reads only the two days, and the filter goes down to the Parquet scan:
```text
MaterializedCte: name=c
SubqueryAlias: c
Filter: events.day = Utf8View("2026-01-01") OR events.day = Utf8View("2026-01-02")
TableScan: events projection=[day, n], partial_filters=[events.day = Utf8View("2026-01-01") OR events.day = Utf8View("2026-01-02")]
Union
Filter: c.day = Utf8View("2026-01-01")
MaterializedCteScan: name=c
Filter: c.day = Utf8View("2026-01-02")
MaterializedCteScan: name=c
```
## What changes are included in this PR?
- A new logical optimizer rule, `PushDownMaterializedCteFilter`, which runs directly after `PushDownFilter`.
- If each scan of a materialized CTE has a filter directly above it (or above its `SubqueryAlias` nodes), the rule puts the disjunction of these filters on top of the body.
- Each scan keeps its filter. If one scan has no filter, the plan does not change.
- Scans in subquery expressions and in the bodies of other materialized CTEs are counted.
- From each filter, the rule uses only the terms that are deterministic and have no subquery and no outer reference.
- `PushDownFilter` moves the new filter further in the subsequent pass, and does not move it through a limit or a `RecursiveQuery`.
- A body with a volatile expression is not changed. A filter above the volatile projection is correct in the logical plan, but the physical filter pushdown gives a copy of the filter, with the volatile expression in the place of the column, to the data source. With `datafusion.execution.parquet.pushdown_filters = true` the scan then evaluates the volatile expression a second time and removes rows. This problem was also seen without a CTE (`SELECT count(*) FROM (SELECT n, random() AS x FROM t) WHERE x < 0.5 OR x >= 0.5`), and is not corrected in this PR.
- `MaterializedCte` has a new public field `pushed_filter: Option<Expr>`. It records the filter that the rule added, so that the rule does nothing when the new disjunction is equal to the record. If the filters of the scans change between two passes, the rule adds the new disjunction above the previous one. The two filters together are correct, and the rule adds a maximum of one filter to a CTE in each optimizer pass.
Known limit: when the filter uses a computed column of the body, the body computes that expression two times.
## Are these changes tested?
Yes. Unit tests for the rule (13 cases, including the fixed point with `PushDownFilter`, changed filters between passes, a scan in the body of another CTE, a recursive body and a volatile body). `cte_materialized.slt`: the example of the issue with `EXPLAIN` on a Parquet table, one reference without a filter, a volatile filter, aliases, references in subqueries, two CTEs, a volatile body, a body with a limit and a recursive body, each with a result check. `explain.slt` has the new rule name.
## Are there any user-facing changes?
A query with a materialized CTE whose references all have a filter can read and buffer fewer rows, and its `EXPLAIN` output changes. Results do not change. `EXPLAIN VERBOSE` shows the new rule `push_down_materialized_cte_filter`. `MaterializedCte` has the new public field `pushed_filter`.
🤖 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: push filters that all references share into the body of a materialized CTE [datafusion]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #26275:
URL: https://github.com/apache/datafusion/pull/26275#issuecomment-6156567080
<!-- 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 [ 36.696s] (current)
Parsing datafusion v55.1.0 (current)
Parsed [ 0.023s] (current)
Building datafusion v55.1.0 (baseline)
Built [ 36.901s] (baseline)
Parsing datafusion v55.1.0 (baseline)
Parsed [ 0.023s] (baseline)
Checking datafusion v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.453s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 76.014s] datafusion
Building datafusion-expr v55.1.0 (current)
Built [ 19.128s] (current)
Parsing datafusion-expr v55.1.0 (current)
Parsed [ 0.059s] (current)
Building datafusion-expr v55.1.0 (baseline)
Built [ 18.726s] (baseline)
Parsing datafusion-expr v55.1.0 (baseline)
Parsed [ 0.062s] (baseline)
Checking datafusion-expr v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.966s] 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 [ 40.015s] datafusion-expr
Building datafusion-optimizer v55.1.0 (current)
Built [ 15.976s] (current)
Parsing datafusion-optimizer v55.1.0 (current)
Parsed [ 0.024s] (current)
Building datafusion-optimizer v55.1.0 (baseline)
Built [ 16.246s] (baseline)
Parsing datafusion-optimizer v55.1.0 (baseline)
Parsed [ 0.024s] (baseline)
Checking datafusion-optimizer v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.119s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 33.364s] datafusion-optimizer
Building datafusion-physical-plan v55.1.0 (current)
Built [ 23.961s] (current)
Parsing datafusion-physical-plan v55.1.0 (current)
Parsed [ 0.131s] (current)
Building datafusion-physical-plan v55.1.0 (baseline)
Built [ 24.605s] (baseline)
Parsing datafusion-physical-plan v55.1.0 (baseline)
Parsed [ 0.134s] (baseline)
Checking datafusion-physical-plan v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.449s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 50.329s] datafusion-physical-plan
Building datafusion-proto v55.1.0 (current)
Built [ 35.387s] (current)
Parsing datafusion-proto v55.1.0 (current)
Parsed [ 0.010s] (current)
Building datafusion-proto v55.1.0 (baseline)
Built [ 35.293s] (baseline)
Parsing datafusion-proto v55.1.0 (baseline)
Parsed [ 0.010s] (baseline)
Checking datafusion-proto v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.081s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 72.047s] datafusion-proto
Building datafusion-sql v55.1.0 (current)
Built [ 28.472s] (current)
Parsing datafusion-sql v55.1.0 (current)
Parsed [ 0.023s] (current)
Building datafusion-sql v55.1.0 (baseline)
Built [ 29.710s] (baseline)
Parsing datafusion-sql v55.1.0 (baseline)
Parsed [ 0.024s] (baseline)
Checking datafusion-sql v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.172s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 59.461s] datafusion-sql
Building datafusion-sqllogictest v55.1.0 (current)
Built [ 65.239s] (current)
Parsing datafusion-sqllogictest v55.1.0 (current)
Parsed [ 0.012s] (current)
Building datafusion-sqllogictest v55.1.0 (baseline)
Built [ 66.283s] (baseline)
Parsing datafusion-sqllogictest v55.1.0 (baseline)
Parsed [ 0.012s] (baseline)
Checking datafusion-sqllogictest v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.067s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 134.378s] datafusion-sqllogictest
Building datafusion-substrait v55.1.0 (current)
Built [ 217.178s] (current)
Parsing datafusion-substrait v55.1.0 (current)
Parsed [ 0.008s] (current)
Building datafusion-substrait v55.1.0 (baseline)
Built [ 219.142s] (baseline)
Parsing datafusion-substrait v55.1.0 (baseline)
Parsed [ 0.010s] (baseline)
Checking datafusion-substrait v55.1.0 -> v55.1.0 (no change; assume patch)
Checked [ 0.152s] 223 checks: 223 pass, 31 skip
Summary no semver update required
Finished [ 438.185s] 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: push filters that all references share into the body of a materialized CTE [datafusion]
Posted by "codecov-commenter (via GitHub)" <gi...@apache.org>.
codecov-commenter commented on PR #26275:
URL: https://github.com/apache/datafusion/pull/26275#issuecomment-6156650170
## [Codecov](https://app.codecov.io/gh/apache/datafusion/pull/26275?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.72346%` with `288 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 ([`04a8eb5`](https://app.codecov.io/gh/apache/datafusion/commit/04a8eb5ec59766c5db1bee777faf416ec72eccb2?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/26275?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Patch % | Lines |
|---|---|---|
| [...optimizer/src/push\_down\_materialized\_cte\_filter.rs](https://app.codecov.io/gh/apache/datafusion/pull/26275?src=pr&el=tree&filepath=datafusion%2Foptimizer%2Fsrc%2Fpush_down_materialized_cte_filter.rs&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#diff-ZGF0YWZ1c2lvbi9vcHRpbWl6ZXIvc3JjL3B1c2hfZG93bl9tYXRlcmlhbGl6ZWRfY3RlX2ZpbHRlci5ycw==) | 77.86% | [23 Missing and 93 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26275?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| [datafusion/physical-plan/src/materialized\_cte.rs](https://app.codecov.io/gh/apache/datafusion/pull/26275?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/26275?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/26275?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==) | 84.97% | [23 Missing and 9 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26275?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/26275?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) | 85.71% | [5 Missing and 8 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26275?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/26275?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/26275?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/26275?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/26275?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/26275?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==) | 75.00% | [5 Missing and 2 partials :warning: ](https://app.codecov.io/gh/apache/datafusion/pull/26275?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/26275?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/26275?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/26275?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/26275?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/26275?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/26275?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) |
| ... and [4 more](https://app.codecov.io/gh/apache/datafusion/pull/26275?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 #26275 +/- ##
=========================================
Coverage 82.83% 82.84%
=========================================
Files 1149 1152 +3
Lines 452798 454449 +1651
Branches 452798 454449 +1651
=========================================
+ Hits 375082 376488 +1406
- Misses 54893 54965 +72
- Partials 22823 22996 +173
```
</details>
[:umbrella: View full report in Codecov by Harness](https://app.codecov.io/gh/apache/datafusion/pull/26275?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