Materialized CTE scans report unknown statistics, ordering and partitioning #26230

Is your feature request related to a problem or challenge?

Follow-up to #25580 (WITH ... AS MATERIALIZED), part of #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.

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 (#25580 (comment)).

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