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: [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]
