Run selected DataFusion optimizer rules in SQL lowering; a rule-list API for ASAPPlanner rewrites #670

Idea

DataFusion lets a caller run an optimizer with an explicit list of rules:

let plan = datafusion::optimizer::Optimizer::with_rules(vec![Arc::new(
    datafusion::optimizer::simplify_expressions::SimplifyExpressions::new(),
)])
.optimize(plan, &ctx.state(), |_, _| {})?;

Two uses:

1. In the SQL frontend: run a chosen set of DataFusion optimizer rules once during lowering, before the plan is converted to the unified IR.
2. In ASAPPlanner: expose a similar API for our own rewrite rules, i.e. register rules by name and run a pipeline built from them. Canonicalization and the Stage 1 rewrite rules would be the first candidates.

The resulting order would be:

SQL ──► DataFusion LogicalPlan ──► selected DataFusion optimizer rules (once)
    ──► lowering to the unified IR (OperatorNode / QueryRoot)
    ──► ASAPPlanner canonicalization
    ──► ASAP primitive optimization (Stage 1 Pass 1 / Pass 2, Stage 2, Stage 3)

Today

crates/frontend-sql/src/sql/mod.rs lower() already calls individual DataFusion analyzer rules directly:

• ApplyFunctionRewrites, carrying ClickHouseBuiltinRewrite;
• TypeCoercion;
• then our drop_integer_decimal_widening.

It deliberately avoids Analyzer::execute_and_check, because that runs an unconditional check_plan. No DataFusion optimizer rules run; we lower the unoptimized plan.

Questions to settle

• Which rules are safe. A rule must keep the semantics the unified IR can express, and must not produce constructs that lowering rejects. Some rules need a closer look before adopting:

  • subquery decorrelation, which turns subqueries into joins;
  • CommonSubexprEliminate, which may conflict with our own sub-DAG sharing (Pass 2);
  • projection push-down / OptimizeProjections;
  • EliminateCrossJoin.

  Likely safe starting points: SimplifyExpressions, constant folding, and EliminateFilter / EliminateLimit.

• Overlap with Pass 1: push down filters and HAVING across levels to create more candidates (and reuse DataFusion's filter push-down) #664. DataFusion's PushDownFilter may already cover part of the multi-level filter/HAVING push-down tracked there, including the shared-aggregate caveat. Decide whether it runs in the frontend or is reimplemented as an ASAPPlanner rule on the unified IR, which would also cover PromQL.

• Order versus our canonicalization. Which of our canonicalization steps become unnecessary after the DataFusion rules, and which must stay because PromQL and MetricsQL don't go through DataFusion.

• Our rule API. What the ASAPPlanner equivalent looks like, e.g. Rewriter::with_rules(vec![...]).rewrite(dag). Keep it minimal: a rule is a function from DAG to DAG plus a name. No new framework.

• Testing. Keep the DataFusion-equivalence checks from the feat(sql): lower SQL to unified operator and scalar IR #539 review. The optimized plan must lower to an IR whose semantics match DataFusion's.

Related: #664 (multi-level filter/HAVING push-down and DataFusion rules), #509 (planning stages).