adriangb opened a new pull request, #26271:
URL: https://github.com/apache/datafusion/pull/26271
## Which issue does this PR close?
- Closes apache/datafusion#26230.
Stacked on apache/datafusion#25580. Only the last commit is new.
## Rationale for this change
A reference to a `MATERIALIZED` CTE reported unknown statistics, no ordering
and unknown partitioning. A query could then get a worse plan than the same
query with the CTE inlined.
```sql
SET datafusion.sql_parser.dialect = 'PostgreSQL';
CREATE TABLE small AS SELECT value AS k FROM generate_series(1, 5);
CREATE TABLE huge AS SELECT value AS k FROM generate_series(1, 100000);
EXPLAIN WITH c AS MATERIALIZED (SELECT k FROM small)
SELECT count(*) FROM huge JOIN c ON huge.k = c.k;
```
Before, the hash table is built on the 100000 rows of `huge`:
```
HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(k@0, k@0)],
projection=[]
DataSourceExec: partitions=1, partition_sizes=[13]
MaterializedCteScanExec: name=c
```
After, it is built on the 5 rows of the CTE:
```
HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(k@0, k@0)],
projection=[]
MaterializedCteScanExec: name=c
DataSourceExec: partitions=1, partition_sizes=[13]
```
The continuation also sorted or repartitioned rows that the body already
sorted or partitioned. For `WITH c AS MATERIALIZED (SELECT g, count(*) AS n
FROM p GROUP BY g) SELECT g, sum(n) FROM c GROUP BY g`, the `RepartitionExec`
and the partial aggregate above the scan are removed, and the scan shows
`partitioning=Hash([g@0], 4)`.
## What changes are included in this PR?
The scan is a leaf and the body is a child of `MaterializedCteExec`, so the
planner copies the properties to the scan in
`DefaultPhysicalPlanner::optimize_physical_plan`. The EXPLAIN paths use the
same function.
**Statistics.** Each scan gets the statistics of the body of the initial
plan, computed with `StatisticsContext` before the optimizer runs. The
statistics API is not changed.
**Ordering and hash partitioning.** These are only known after the optimizer
has rewritten the body, and a wrong claim gives wrong results:
1. The plan is optimized with scans that have statistics only. This plan is
always correct.
2. If an optimized body has an output ordering or a hash partitioning, each
scan of the initial plan gets the partition count, ordering and partitioning of
that body, and the plan is optimized again.
3. The second plan is used only if each body in it has the properties that
its scans report. If not, step 2 is done again, at most 3 times. If no plan
passes the check, the plan of step 1 is used.
A scan with the properties of the body replays one body partition per
partition, and returns an internal error if the partition counts are not the
same at execution.
Limits:
- A query with a `MATERIALIZED` CTE whose body has an ordering or a hash
partitioning runs the physical optimizer two times, or up to four times for a
chain of CTEs. Queries without `MATERIALIZED` are not changed. If this cost is
not acceptable, the statistics part can be split out.
- An `ORDER BY` at the top of a body without `LIMIT` is removed by
`EnforceSorting`, as before, so the scan has no ordering in that case.
- `EXPLAIN VERBOSE` shows the rules of the optimizer run that made the final
plan.
Other plan changes that follow from the statistics: an exact row count of
the body can answer `count(*)`, and the body then does not run. One pinned plan
in `cte_materialized.slt` has a new join order.
## Are these changes tested?
Yes. `cte_materialized.slt`: the join-order plan, a sort that is removed, a
sort that is still needed, a repartition and a partial aggregate that are
removed, a partitioned join of two references, a chain of two CTEs, a scalar
subquery and a view. Unit tests for the statistics, the copied properties, the
replay order and the partition-count error. A planner test with an optimizer
rule that changes the body after the scans got its properties checks the
fallback.
## Are there any user-facing changes?
Plans of queries with `MATERIALIZED` CTEs can change.
`MaterializedCteScanExec` shows `partitioning=` and `output_ordering=` in
EXPLAIN when it has them. New public functions:
`MaterializedCteScanExec::with_body_statistics`, `with_body_properties`,
`body_properties_hold`, and `MaterializedCteExec::body`, `register`.
🤖 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]
