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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
