DataFusion-Federation: Union Flattening Across Executors

[vikrantmehta123]

I was experimenting with union pushdown behavior in DataFusion Federation and noticed something I wanted to clarify.

Setup:

• Two independent SQL executors (separate DB containers)
• Executor 1 exposes tables t1, t2
• Executor 2 exposes table t3

The initial logical plan looks like:

Projection
  Union
    Union
      TableScan(t1)
      TableScan(t2)
    TableScan(t3)

With optimize_unions enabled, this is flattened into:

Projection
  Union
    TableScan(t1)
    TableScan(t2)
    TableScan(t3)

This results in three independent SELECTs being executed, with the UNION performed in-memory.

If I disable optimize_unions optimizer rule, Executor 1 instead receives a single pushed-down SQL query:

SELECT ... FROM t1
UNION ALL
SELECT ... FROM t2

while Executor 2 executes its own SELECT, followed by one final in-memory UNION.

---

Is flattening unions across executor boundaries here an intentional optimizer choice? Would pushing down UNIONs be better?

[ryux1]

Yes, the flattening is intentional in core DataFusion, but the loss of this particular pushdown opportunity is a federation-planner limitation rather than a reason that UNION flattening is generally wrong.

OptimizeUnions explicitly flattens nested LogicalPlan::Union inputs. Later, FederationOptimizerRule searches for the largest subtree whose scans all resolve to one FederationProvider. Once the union is flat, the A/A/B union is ambiguous at the parent and each child is considered separately, so the two A inputs are no longer presented as one subtree to the SQL federation optimizer.

This behavior is currently pinned by datafusion-federation/src/sql/mod.rs::basic_sql_federation_test: its A/A/B query expects three final SQL statements, including separate statements for table_a1 and table_a2.

Disabling optimize_unions preserves the accidental binary-tree grouping and therefore enables the A-side pushdown, but it is fragile because grouping then depends on parser plan shape. The durable fix belongs in datafusion-federation: when processing a Union, group compatible inputs by federation provider / compute_context, build a union subtree for each compatible group, federate those groups, and leave a final local union across different executors. That would produce one remote UNION ALL for A and one query for B regardless of whether core DataFusion flattened the input first.

So: pushing the same-executor union down is better when that executor supports it, but it should be implemented as federation-aware regrouping with capability/error handling, not by globally disabling the core union optimization.