adriangb opened a new issue, #26230: URL: https://github.com/apache/datafusion/issues/26230
### Is your feature request related to a problem or challenge? Follow-up to https://github.com/apache/datafusion/pull/25580 (`WITH ... AS MATERIALIZED`), part of https://github.com/apache/datafusion/issues/17737. A reference to a materialized CTE (`MaterializedCteScanExec`) reports unknown statistics, no ordering and `UnknownPartitioning`. This can make a plan worse than the plan of 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; ``` With `MATERIALIZED`, the hash table is built on the 100000 rows of `huge`: ``` HashJoinExec: mode=CollectLeft, join_type=Inner, on=[(k@0, k@0)] DataSourceExec: partitions=1, partition_sizes=[13] <- huge, build side MaterializedCteScanExec: name=c <- 5 rows, probe side ``` With `NOT MATERIALIZED`, `JoinSelection` puts the 5 rows of `small` on the build side. Thanks to @zhuqi-lucas for the example (https://github.com/apache/datafusion/pull/25580#discussion_r4244985570). `cte_materialized.slt` pins both plans, so a fix shows as a plan change. ### Describe the solution you'd like The scan should report the properties of the CTE body: - **Statistics.** The scan is a leaf, and the body is a child of `MaterializedCteExec`, not of the scan. A leaf cannot ask another node for statistics through `StatisticsContext`. The deprecated `ExecutionPlan::partition_statistics` does not help: `DataSourceExec` returns unknown from it. This needs either a way for a leaf to resolve statistics of a plan that it refers to, or statistics that the planner copies to the scan when it binds the scan to its buffer. - **Ordering.** The replay keeps the row order of each body partition, so the scan can report the output ordering of the body when it has the same partition count. - **Partitioning.** The same applies to a hash partitioning of the body. The body can change after the scan is created (physical optimizer rules rewrite it), so the properties must come from the final body. ### Describe alternatives you've considered _No response_ ### Additional context _No response_ -- 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]
