raghavyadav01 opened a new pull request, #26874: URL: https://github.com/apache/datafusion/pull/26874
## Which issue does this PR close? - Part of #21198. It does not add `HAVING` support. Following https://github.com/apache/datafusion/issues/5492#issuecomment-6479090126, it makes the shapes that return wrong rows error instead. ## Rationale for this change - A `LATERAL` subquery over an aggregate without `GROUP BY`, with a `HAVING`, returns wrong rows today instead of an error: ```sql CREATE TABLE a(k INT) AS VALUES (1), (2), (3); CREATE TABLE b(k INT, v INT) AS VALUES (1, 10), (1, 20), (2, 30); SELECT a.k, s.c FROM a, LATERAL (SELECT count(*) AS c FROM b WHERE b.k = a.k HAVING count(*) > 1) s; -- main: (1, 2), (2, NULL), (3, NULL) -- DuckDB: (1, 2) ``` - The existing check only refuses a `HAVING` that `PullUpCorrelatedExpr` pulls up, which happens when it is true on an empty group. Two cases get past it: - a `HAVING` that is false on an empty group stays above the aggregate. The rewrite turns the inner join into an outer join, so an outer row whose row the `HAVING` removes comes back with NULLs - a `HAVING` that reads an outer column is moved into the join condition, and the count bug handling turns its unmatched rows into a count of 0. This is wrong for a left lateral join too (`(2, 0)` where the answer is `(2, NULL)`) ## What changes are included in this PR? - `DecorrelateLateralJoin` looks for a `HAVING` over an ungrouped aggregate in the original subquery, and leaves the join correlated (so it errors) when the join is inner, or when the `HAVING` reads an outer column. - A filter below the aggregate is not a `HAVING` and is not affected, and neither is a left lateral join with a `HAVING` that reads only the subquery. ## What is the testing strategy for this PR? - New `lateral_join.slt` cases: - inner join with `HAVING count(*) > 1` and with `HAVING max(t2.id) > 15`: now an error - inner and left join with `HAVING count(*) > t1.id`: now an error - left join with `HAVING count(*) > 1`, and an inner join with `GROUP BY ... HAVING`: still decorrelated, with the same rows as before - Without the change, the four error cases return wrong rows instead of failing. - I compared 20 lateral aggregate shapes against DuckDB on main and on this branch. The 8 that return wrong rows on main now error, the 8 that return correct rows still return the same rows, and the 4 that already errored still do. - Optimizer unit tests and the full sqllogictest suite pass. ## Are there any user-facing changes? - Queries that returned wrong rows now fail with `OuterReferenceColumn`. Every query that returned correct rows still does. 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- 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]
