[EPIC] Extract remaining physical optimizer out of core

[jayzhan211]

Is your feature request related to a problem or challenge?

Given the research from #10782, we need to pull out physical optimizer first before extracting catalog.

Describe the solution you'd like

Pull physical optimizer rules to datafusion-physical-optimizer.

There are some tests that have dependencies on datasource, which they are not possible to move out of core at this moment. We may need to leave those tests inside core for now.

• PhysicalOptimizerRule Create datafusion-physical-optimizer crate #11507
• OutputRequirements
• AggregateStatistics
• JoinSelection
• LimitedDistinctAggregation
• EnforceDistribution
• CombinePartialFinalAggregate
• EnforceSorting
• OptimizeAggregateOrder
• ProjectionPushdown
• CoalesceBatches
• OutputRequirements
• TopKAggregation
• SanityCheckPlan

Describe alternatives you've considered

No response

Additional context

No response

[jonathanc-n]

@jayzhan211 @lewiszlw How do we plan on moving the rest of the functions. If we move the tests separately many of the tests depend on the traits in the functions that are being moved. Due to this it would make sense to import together, but then problems with the datasource and other util dependencies will pop up again. Do you guys have any solutions in mind?

[jayzhan211]

We may need to move datasource out of core.

We probably need to revisit this #10782

[jayzhan211]

Try to move optimization that doesn't have datasource dependency like

These are good first issues

• enforce_distribution
• enforce_sorting
• join_selection
• pruning
• replace_with_order_preserving_variants
• sanity_checker
• sort_pushdown
• enforce_distribution
• join_selection

Require datasource

• projection_pushdown (Require CsvExec)

If we only need datasource in test, leave the test in core

[jonathanc-n]

@jayzhan211 Would you like to open a ticket for moving datasource out of core, so others are more aware of this transition?

[jayzhan211]

@jayzhan211 Would you like to open a ticket for moving datasource out of core, so others are more aware of this transition?

Not sure how do achieve this yet, the high level idea is tracked in #10782

[josh0yeh]

take