[I] Wrong results: decorrelation loses correlated columns when a subquery contains two equal subplans [datafusion]

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

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: 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: decorrelation loses correlated columns when a subquery contains two equal subplans [datafusion]

["raghavyadav01 (via GitHub) via github" <gi...@datafusion.apache.org>]

raghavyadav01 commented on issue #26692:
URL: https://github.com/apache/datafusion/issues/26692#issuecomment-6587539672

   I'd like to take this one, making these shapes error as you suggested. The cause is two equal `SubqueryAlias` nodes (e.g. `t2 AS s` twice) sharing one entry in `correlated_subquery_cols_map`. I have a fix that stops the pull up when that happens, so q1 and its NOT EXISTS / IN / scalar forms error instead of returning wrong rows. PR shortly.
   


-- 
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: decorrelation loses correlated columns when a subquery contains two equal subplans [datafusion]

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

alamb commented on issue #26692:
URL: https://github.com/apache/datafusion/issues/26692#issuecomment-6479152526

   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