optimize_projections fails with "No field named ..." after propagate_empty_relation removes the first branch of a UNION #26516

Describe the bug

When propagate_empty_relation removes all but one input of a Union, it puts a Projection over the remaining input. It builds that projection with Projection::new_from_schema(child, union_schema), which makes Expr::Column references from the union's field names. The union takes its field names from its first input. If the remaining input is not the first one, and its field names are different, the projection refers to columns that its input does not have. optimize_projections then fails with a schema error.

To Reproduce

On main (a7c7c08):

SELECT 1 WHERE false UNION ALL (SELECT 2 LIMIT 1);

Optimizer rule 'optimize_projections' failed
caused by
Schema error: No field named "Int64(1)". Did you mean '"Int64(2)"'?
Valid fields are "Int64(2)".

The problem is not specific to literals. Named columns fail in the same way:

SELECT a FROM (VALUES (1)) t(a) WHERE false
UNION ALL
(SELECT b FROM (VALUES (2)) s(b) LIMIT 1);

Optimizer rule 'optimize_projections' failed
caused by
Schema error: No field named a.
Valid fields are s.b.

The optimizer trace for the first query (EXPLAIN VERBOSE output, cut down) shows the invalid plan after propagate_empty_relation:

logical_plan after eliminate_filter
  Union
    Projection: Int64(1)
      EmptyRelation: rows=0
    Limit: skip=0, fetch=1
      Projection: Int64(2)
        EmptyRelation: rows=1

logical_plan after propagate_empty_relation
  Projection: Int64(1)          <-- Column("Int64(1)"), not in the input schema
    Limit: skip=0, fetch=1
      Projection: Int64(2)
        EmptyRelation: rows=1

We found this through the Substrait consumer. Our planner pushes a filter and a fetch into each UNION ALL branch. The first branch then has a constant-false filter, and the optimizer removes it.

Expected behavior

+----------+
| Int64(1) |
+----------+
| 2        |
+----------+

Additional context

• The Limit in the remaining branch is necessary. SELECT 1 WHERE false UNION ALL SELECT 2 returns the correct result. We did not check why. Our guess is that, without the Limit, a later rule merges the two projections before the bad reference is resolved.

• A possible fix is to map the remaining input's columns to the union's output names by position, and to build the projection with Projection::try_new. We tested this locally on main: both queries above then return the correct result. Three snapshot tests in propagate_empty_relation change their expected text (Projection: a, b, c becomes Projection: test.a AS a, test.b AS b, test.c AS c). All other datafusion-optimizer tests pass.

  let exprs = child
      .schema()
      .columns()
      .into_iter()
      .zip(plan.schema().iter())
      .map(|(col, (qualifier, field))| {
          Expr::Column(col).alias_qualified(qualifier.cloned(), field.name())
      })
      .collect::<Vec<_>>();
  Ok(Transformed::yes(LogicalPlan::Projection(
      Projection::try_new(exprs, Arc::new(child))?,
  )))

• Related: Resolve columns by schema index, not by name, in ExtractLeafExpressions and PushDownLeafProjections #25448 (other rules that resolve columns by name instead of by schema index).

Willingness to contribute

I plan to contribute a fix for this bug independently