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]