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: [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]
