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

["adriangb (via GitHub)" <gi...@apache.org>]

adriangb opened a new issue, #25414:
URL: https://github.com/apache/datafusion/issues/25414

   ### 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` (22f9a92178):
   
   ```sql
   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:
   
   ```sql
   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:
   - https://github.com/apache/datafusion/pull/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.
   - https://github.com/apache/datafusion/pull/25388 changes how these rules place `BinaryExpr` and similar expressions. Test this problem again after it merges.
   - https://github.com/apache/datafusion/issues/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.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Wrong results: leaf expression pushdown removes a computed column that has the same name as its input column [datafusion]

["adriangb (via GitHub)" <gi...@apache.org>]

adriangb closed issue #25414: Wrong results: leaf expression pushdown removes a computed column that has the same name as its input column
URL: https://github.com/apache/datafusion/issues/25414


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org