You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "thobe (via GitHub)" <gi...@apache.org> on 2026/10/12 14:56:52 UTC
[I] Wrong results: Correlated subquery confuses same-named columns of two of its relations [datafusion]
thobe opened a new issue, #26234:
URL: https://github.com/apache/datafusion/issues/26234
### Describe the bug
When a correlated subquery refers to the outer query from two of its relations, through columns that have the same name, decorrelation maps both columns to the same column of the `__correlated_sq_N` alias. The condition written for one relation is then applied to the other, and the query silently returns wrong results.
This affects `EXISTS`, `NOT EXISTS`, `IN` and scalar subqueries.
### To Reproduce
```sql
CREATE TABLE test(k INT) AS VALUES (1), (2);
-- Rows whose successor also exists. Expected: 1. Actual: no rows.
SELECT outer.k FROM test outer
WHERE EXISTS (SELECT 1 FROM test one, test two WHERE one.k = outer.k AND two.k = outer.k + 1);
-- Expected: 2. Actual: 1, 2.
SELECT outer.k FROM test outer
WHERE NOT EXISTS (SELECT 1 FROM test one, test two WHERE one.k = outer.k AND two.k = outer.k + 1);
-- Expected: 1. Actual: no rows.
SELECT outer.k FROM test outer
WHERE outer.k IN (SELECT one.k FROM test one, test two WHERE one.k = outer.k AND two.k = outer.k + 1);
-- Expected: (1, 1), (2, 0). Actual: (1, 0), (2, 0).
SELECT outer.k, (SELECT count(*) FROM test one, test two WHERE one.k = outer.k AND two.k = outer.k + 1)
FROM test outer ORDER BY outer.k;
```
The optimized plan for the `EXISTS` query (`EXPLAIN`, logical plan) uses `__correlated_sq_1.k`, which is `two.k`, for both correlated conditions; `one.k` is not even read:
```text
LeftSemi Join: outer.k = __correlated_sq_1.k, CAST(outer.k AS Int64) + Int64(1) = CAST(__correlated_sq_1.k AS Int64)
SubqueryAlias: outer
TableScan: test projection=[k]
SubqueryAlias: __correlated_sq_1
Cross Join:
SubqueryAlias: one
TableScan: test projection=[]
SubqueryAlias: two
TableScan: test projection=[k]
```
### Expected behavior
The same results as the equivalent query without a subquery, which DataFusion answers correctly:
```sql
-- Returns 1.
SELECT DISTINCT outer.k FROM test outer, test one, test two WHERE one.k = outer.k AND two.k = outer.k + 1;
```
### Additional context
[`SubqueryAlias::try_new`](https://github.com/apache/datafusion/blob/334f29317c1040cc83ee0dd2954562f4fb3ba257/datafusion/expr/src/logical_plan/plan.rs#L2775-L2799) already keeps the decorrelated subquery's columns apart: it gives same-named columns unique aliases, so `one.k` and `two.k` become `k` and `k:1`.
But `build_join` in `decorrelate_predicate_subquery.rs` then rewrites the pulled-up join filter with [`replace_qualified_name`](https://github.com/apache/datafusion/blob/334f29317c1040cc83ee0dd2954562f4fb3ba257/datafusion/optimizer/src/utils.rs#L162-L175), which maps every correlated column to `<alias>.<column name>`. Both `one.k` and `two.k` become `__correlated_sq_1.k`, and `k:1` is never referenced. [`scalar_subquery_to_join.rs`](https://github.com/apache/datafusion/blob/334f29317c1040cc83ee0dd2954562f4fb3ba257/datafusion/optimizer/src/scalar_subquery_to_join.rs#L375-L380) uses the same helper.
A fix is to map each correlated column to the name it has in the `SubqueryAlias` schema, found by its position in the decorrelated subquery's schema. Columns that the schema only has under a derived table's alias can keep the current name-based mapping.
Reproduced on `main` (334f29317), 54.1.0 and 55.2.0.
### Willingness to contribute
I plan to contribute a fix for this bug independently
--
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: Correlated subquery confuses same-named columns of two of its relations [datafusion]
Posted by "Awesome06 (via GitHub) via github" <gi...@datafusion.apache.org>.
Awesome06 commented on issue #26234:
URL: https://github.com/apache/datafusion/issues/26234#issuecomment-6724640391
Following up on the discussion to make this pattern fail fast rather than applying speculative rewrite patches, I have integrated explicit error handling.
--
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
Re: [I] Wrong results: Correlated subquery confuses same-named columns of two of its relations [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #26234:
URL: https://github.com/apache/datafusion/issues/26234#issuecomment-6479353383
When fixing this bug, I think we should make the query error rather than trying to make it work / give correct results. To make it work and have confidence it will be correct, I think we need a more general approach than patches. See here for details
- https://github.com/apache/datafusion/issues/5492#issuecomment-6479090126
--
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