jayzhan211 opened a new issue, #26692:
URL: https://github.com/apache/datafusion/issues/26692
### Describe the bug
Decorrelating a subquery can lose a correlated column when the subquery
contains two equal subplans: the same table scanned twice, or the same table
alias used twice. Depending on the shape, the query returns wrong rows or fails
to plan.
### To Reproduce
```sql
CREATE TABLE t(a INT, b INT) AS VALUES (1,1),(2,2);
CREATE TABLE t2(a INT, b INT) AS VALUES (1,1),(3,30);
-- 1. Wrong result: returns 1 once, expected 1 twice
SELECT t.a FROM t, t2 AS s
WHERE EXISTS (
SELECT 1 FROM (SELECT * FROM t2 AS s WHERE s.a = t.a) x
CROSS JOIN t2 AS s
);
-- 2. Schema error: No field named s.a. Expected 1
SELECT t.a FROM t
WHERE EXISTS (
SELECT 1 FROM (SELECT * FROM t2 AS s WHERE s.a = t.a) x
CROSS JOIN t2 AS s
);
-- 3. Schema error: No field named l.a. Expected 1
SELECT t.a FROM t
WHERE EXISTS (
SELECT 1 FROM (SELECT a FROM t2 WHERE t2.a = t.a) x
CROSS JOIN LATERAL (SELECT b FROM t2 WHERE t2.b = x.a) l
);
```
### Expected behavior
The results in the comments, which DuckDB returns.
### Additional context
`PullUpCorrelatedExpr` passes what it learns about each node (the correlated
columns it must output, and the values for the count bug) from the node to its
parent through two maps keyed by the `LogicalPlan` value,
`correlated_subquery_cols_map` and `collected_count_expr_map`. Equal subplans
at different places share one entry, so one overwrites or picks up the other's
columns:
- In 1 and 2, the two `SubqueryAlias s` are equal plans. The second one's
entry replaces the first one's, so `s.a` is never renamed to the subquery
alias. In 1 the join filter then reads the outer `s.a`.
- In 3, the two scans of `t2` are equal plans, and the LATERAL side is given
the correlated column of the other scan.
#26504 (fix in #26551) is another case of the same mechanism: a removed
`Filter` rebuilds to a plan equal to its input and overwrites its entry. #22313
fixed a similar collision in `ScalarSubqueryToJoin`.
A fix that removes the cause, instead of patching each case, is to pass
these facts from each node to its parent along the rewrite itself, rather than
through maps keyed by plan.
### 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]
