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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
