shehabgamin opened a new issue, #27229: URL: https://github.com/apache/datafusion/issues/27229
### Is your feature request related to a problem or challenge? Three subquery shapes are planned badly or rejected today. 1. There is no null-aware `RightMark`, so this always builds the hash table on the big outer table: ```sql SELECT id, id IN (SELECT id FROM small) AS m FROM big ``` 2. Every null-aware join is pinned to `CollectLeft`, so a large correlated `NOT IN` collects one whole side, although a NULL only affects its own correlation scope: ```sql SELECT * FROM a WHERE a.x NOT IN (SELECT b.y FROM b WHERE b.k = a.k) ``` 3. A scalar subquery that may return several rows fails at planning time (`Correlated scalar subquery must be aggregated to return at most one row`). SQL says it should run and fail only if an outer row really gets two rows. A plain left join would silently duplicate the row instead. ```sql SELECT o.id, (SELECT i.price FROM items i WHERE i.order_id = o.id) FROM orders o ``` ### Describe the solution you'd like Two PRs that follow the existing `null_aware` pattern. - #27227 adds a null-aware `RightMark` that builds on the subquery side, and allows `Partitioned` null-aware joins that are hash partitioned on the correlation scope keys. - #27228 adds a `single` flag. The join fails with `Scalar subquery returned more than one row` when an outer row has two matches after all join conditions. A row is emitted only after all its matches are seen, so a `LIMIT` can't hide the error. `ScalarSubqueryToJoin` uses it, so non-aggregated correlated scalar subqueries run instead of failing at planning time. The `single` check lives in the join because an operator above it would need a row id, which DataFusion doesn't have. And when the outer side is the build side, it would also have to block or sort the whole output. In the join, the visited bitmap already has this. Spark and DuckDB also check in the join. ### Describe alternatives you've considered - A check operator or a counting aggregate above the join, see above. - Partitioning null-aware joins on all keys. That is wrong when a `NULL` value key lands in another partition than its scope. ### Additional context How these flags work with the different join implementations: | Implementation | `null_aware` | `single` | |---|---|---| | `HashJoinExec` | yes, with #27227 adding `RightMark` and `Partitioned` | `Left` and `Right` | | `NestedLoopJoinExec` | no | `Right`, for joins without equality keys | | `SortMergeJoinExec` | single key `LeftAnti` only, other forms rejected | no | | others | no | no | The physical planner never builds the other implementations for flagged joins, and `JoinSelection` only swaps a flagged join into an orientation that supports it. So the other joins need no changes. One gap where `JoinSelection` converted a null-aware hash join to `SymmetricHashJoinExec` is tracked in #27225 and fixed in #27226. ### Willingness to contribute I plan to contribute to this feature 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]
