Wrong results: leaf expression pushdown removes a computed column that has the same name as its input column

[adriangb]

Describe the bug

DataFusion returns incorrect values when a query reads a struct field from a subquery, and the subquery also computes a column with the same name as a table column. The optimizer removes the computation and keeps the original table column.

This occurs with the default configuration. It is not a planning error: the query completes and shows incorrect data. If the computation changes the data type, the query fails with an internal error.

The setting datafusion.optimizer.enable_leaf_expression_pushdown controls the optimizer rules that cause the problem. When you set it to false, the results are correct.

To Reproduce

Use datafusion-cli on main (22f9a92):

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

-- 1. Filter in the subquery
select a, s['b'] from (select -a as a, s from t where a > 0);

-- 2. Limit in the subquery
select a, s['b'] from (select -a as a, s from t limit 10);

-- 3. Limit in the subquery, and the computation changes the type (Int32 -> Int64)
select a, s['b'] from (select a * 10 as a, s from t limit 10);

Query	DataFusion	DuckDB 1.5.2	PostgreSQL 17.6
1	1, x	-1, x	-1, x
2	1, x	-1, x	-1, x
3	internal error (see below)	10, x	10, x

For PostgreSQL, I used a composite type (create type st1 as (b text)) and (s).b in place of s['b']. The queries are otherwise the same.

With set datafusion.optimizer.enable_leaf_expression_pushdown = false;, DataFusion returns -1, x, -1, x and 10, x.

The plan of query 1 shows that the negation is not there. The top projection reads t.a directly:

Projection: t.a AS a, __datafusion_extracted_1 AS t.s[b]
  Filter: t.a > Int32(0)
    Projection: get_field(t.s, Utf8("b")) AS __datafusion_extracted_1, t.a
      TableScan: t projection=[a, s]

The plan of query 2 has the same problem:

Projection: t.a AS a, __datafusion_extracted_1 AS t.s[b]
  Limit: skip=0, fetch=10
    Projection: get_field(t.s, Utf8("b")) AS __datafusion_extracted_1, t.a
      TableScan: t projection=[a, s], fetch=10

Query 3 fails because the schema check after the rule finds the type change:

Error: Optimizer rule 'push_down_leaf_projections' failed
caused by
Check optimizer-specific invariants after optimizer rule: push_down_leaf_projections
caused by
Internal error: Assertion failed: compatible: Failed due to a difference in schemas: original schema: DFSchema { inner: Schema { fields: [Field { name: "a", data_type: Int64, ... }, Field { name: "t.s[b]", data_type: Utf8View, ... }] ... }, new schema: DFSchema { inner: Schema { fields: [Field { name: "a", data_type: Int32, ... }, Field { name: "t.s[b]", data_type: Utf8View, ... }] ... }.

A larger example with CTEs gives the same incorrect result:

create table t1(a int, b int, s struct<a int, b varchar>) as values
  (0, 1, {a: 1, b: 'x'}), (0, 2, {a: 2, b: 'y'}), (0, 3, {a: 3, b: 'z'}),
  (2, 4, {a: 4, b: 'w'}), (2, 5, {a: 5, b: 'v'});

with src as (select a + a as a, b, s from t1),
     l as (select a as b, s from (select * from src limit 100))
select b, count(s['b']) from l group by b order by b;

DataFusion returns (0, 3), (2, 2). DuckDB and PostgreSQL return (0, 3), (4, 2).

Expected behavior

DataFusion returns the same results as when enable_leaf_expression_pushdown is false. The computed column (-a, a * 10, a + a) stays in the plan above the table column.

Additional context

The rules are ExtractLeafExpressions and PushDownLeafProjections in datafusion/optimizer/src/extract_leaf_expressions.rs. The problem occurs when the extraction projection for s['b'] moves through a projection that has an output column with the same name as one of its input columns (-t.a AS a). I did not find the root cause. Possibly, the rules resolve columns by name, and thus the reference to the computed a becomes a reference to the input column t.a.

Related, but not the same problem:

• fix: resolve pass-through columns against the input when merging an extraction projection #25412 fixes a planning error ("ambiguous" schema) in the same merge function. It does not fix this problem. Before that change, some queries of this shape failed with the planning error. With that change, they complete and show this problem.
• Avoid duplicating keep-in-place expressions #25388 changes how these rules place BinaryExpr and similar expressions. Test this problem again after it merges.
• Incorrect results: leaf expression pushdown duplicates volatile expressions (random() evaluated twice) #24678 was also a wrong-results problem in these rules (a volatile expression evaluated two times). It is fixed.

A random test of plans and SQL found this problem.