raghavyadav01 opened a new issue, #27566:
URL: https://github.com/apache/datafusion/issues/27566

   ### Describe the bug
   
   A `LATERAL` count over a correlated derived table cross joined with the same 
table returns one row per pair of `a` values instead of one row per outer row. 
The aggregate groups by both `a` columns (`group_by: a, a`), the derived 
table's and the plain table's, so each group counts 1.
   
   ### To Reproduce
   
   ```sql
   CREATE TABLE t (a INT, b INT) AS VALUES (1, 1), (2, 2), (3, NULL);
   CREATE TABLE t2 (a INT, b INT) AS VALUES (1, 1), (3, 30), (2, NULL);
   
   SELECT t.a, l.c FROM t CROSS JOIN LATERAL (
     SELECT count(*) c FROM (SELECT * FROM t2 WHERE t2.a = t.a) x CROSS JOIN t2
   ) l ORDER BY 1;
   ```
   
   DataFusion main returns 9 rows, `(1, 1)`, `(1, 1)`, `(1, 1)`, `(2, 1)`, ... 
`(3, 1)`.
   
   ### Expected behavior
   
   3 rows, `(1, 3)`, `(2, 3)`, `(3, 3)` (DuckDB 1.5.6). An error would also be 
better than the wrong rows.
   
   ### Additional context
   
   The scalar subquery form of the same query now errors through #27244 
("correlated column x.a has the same name as an earlier column of the subquery 
output"). The `LATERAL` path goes through `decorrelate_lateral_join` and 
doesn't hit that check. Found while working on #26692 / #27019.
   


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

Reply via email to