shehabgamin opened a new pull request, #27228: URL: https://github.com/apache/datafusion/pull/27228
## Which issue does this PR close? - Closes #XXXXX. - Merges after #27227. It touches the same files, so I'll rebase once that lands. ## Rationale for this change In `SELECT o.id, (SELECT i.price FROM items i WHERE i.order_id = o.id) FROM orders o` SQL requires at most one item per order and an error otherwise. DataFusion rejects the query at planning time, and a plain join would duplicate the order row. ## What changes are included in this PR? - A `single` flag on the logical `Join`, `HashJoinExec` and `NestedLoopJoinExec`. 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. - `HashJoinExec` supports `Left` and `Right` single joins in `CollectLeft` and `Partitioned` mode. `NestedLoopJoinExec` supports `Right` single joins, for correlations without an equality. - The planner and optimizer rules keep the flag and never move a single join to an implementation or a shape that loses the check. - `ScalarSubqueryToJoin` plans a non-aggregated correlated scalar subquery as a single join. - Proto support. Substrait and the SQL unparser reject single joins. ## What is the testing strategy for this PR? Unit tests for both hash join orientations and modes, the nested loop join, the optimizer rules, `JoinSelection` and proto round trips, plus `single_join.slt`. ## Are there any user-facing changes? Non-aggregated correlated scalar subqueries now run, and fail only when a row really has two matches. With `enable_physical_uncorrelated_scalar_subquery = false`, a multi-row uncorrelated subquery now fails instead of duplicating rows. Code that builds `Join` with a struct literal must set `single`. -- 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]
