`push_down_leaf_projections` fails with an ambiguous schema when a sub-query projection renames columns

[adriangb]

Describe the bug

A query fails to plan when a sub-query projection renames a column to the name of a different column of the same input, and the query also reads a struct field. datafusion.optimizer.enable_leaf_expression_pushdown is true by default, so this is a plain planning failure for a valid statement. A rename such as select t.a as b, t.b as a, ... or a swap such as select c as a, a as c, ... is what produces the shape, and query generators emit it.

A grid of 900 generated statements over a three column table with one struct column gave this error 207 times.

To Reproduce

Run these statements in datafusion-cli at main (3a647e49dd).

create table t(a int, b int, s struct<x varchar>) as values (1, 10, {x: 'p'}), (2, 20, {x: 'q'});

-- 1. rename beside a same-name alias, under a filter
select b, a, s['x'] from (select t.a as b, t.b as a, s from t) where a > 0;

-- 2. the same shape under a limit
select b, a, s['x'] from (select t.a as b, t.b as a, s from t) limit 10;

-- 3. a swap of two column names
select a, c, s['x'] from (select t.b as a, t.a as c, s from t) where a > 0;

All three give:

Optimizer rule 'push_down_leaf_projections' failed
caused by
Schema error: Schema contains qualified field name t.a and unqualified field name a which would be ambiguous

order by and group by above the rename fail the same way. The group by shape reports the first pass, extract_leaf_expressions, instead of the second.

The failure needs a renamed column in the parent's own column list. This plans and runs:

explain select s['x'] from (select t.a as b, t.b as a, s from t) limit 10;

Expected behavior

All three statements return rows. With set datafusion.optimizer.enable_leaf_expression_pushdown = false; they do:

+---+----+--------+        +----+---+--------+
| b | a  | t.s[x] |        | a  | c | t.s[x] |
+---+----+--------+        +----+---+--------+
| 1 | 10 | p      |        | 10 | 1 | p      |
| 2 | 20 | q      |        | 20 | 2 | q      |
+---+----+--------+        +----+---+--------+
   statements 1 and 2         statement 3

Additional context

Root cause. The pass-through loop of build_extraction_projection_impl in datafusion/optimizer/src/extract_leaf_expressions.rs merges the extraction projection into the projection below it. For every column the parent still needs, it resolves the name through the projection's rename map and then pushes the input column that the name resolves to:

let input_schema = existing.input.schema();
for col in columns_needed {
    let col_expr = Expr::Column(col.clone());
    let resolved = replace_cols_by_name(col_expr, &replace_map)?;
    if let Expr::Column(resolved_col) = &resolved
        && !existing_cols.contains(resolved_col)
        && input_schema.has_column(resolved_col)
    {
        proj_exprs.push(Expr::Column(resolved_col.clone()));
    }
}

existing_cols holds only the bare Expr::Column entries of the projection. A rename such as t.a AS b is an Expr::Alias, so the loop does not see that the projection already supplies that value. It appends the input column t.a beside the output field a that the other rename produces. Projection::try_new then rejects the schema, because it holds t.a and an unqualified a together.

Statement 2 shows the mechanism directly. The parent asks for b, b resolves to t.a, t.a collides with the output field a. Ask only for a instead and the error names t.b and b.

This is not the same defect as #25412. That PR resolves both sides of the same comparison into one name space. I built its head and ran all three statements above. All three still fail with the identical message. The PR normalizes qualifiers. It does not make a rename count as a pass-through.

This is not #25414 either. That issue is in the needs_recovery check of split_and_push_projection, and it drops a computed column whose name equals its input. The statements here use pure renames, and no expression is dropped. In the same grid of 900 statements, wrong results appeared only for the -a AS a and a + 1 AS a shapes, which are 25414.

Code pointers.

• datafusion/optimizer/src/extract_leaf_expressions.rs, build_extraction_projection_impl, the columns_needed loop.
• datafusion/optimizer/src/extract_leaf_expressions.rs, build_projection_replace_map. The map is keyed on Column::flat_name() of the projection output.

Related.

• fix: resolve pass-through columns against the input when merging an extraction projection #25412
• Wrong results: leaf expression pushdown removes a computed column that has the same name as its input column #25414
• push_down_leaf_projections fails for self joins in some scenarios #24241
• optimize_projections fails with "No field named ..." when join keys contain get_field (ExtractLeafExpressions) #22895

Tracked in the leaf-pushdown EPIC: #25459