viirya opened a new issue, #26089: URL: https://github.com/apache/datafusion/issues/26089
### Is your feature request related to a problem or challenge? A multi-column `(a, b) NOT IN (SELECT x, y ...)` (#25737, #19857) plans as a null-aware `LeftAnti` (or `LeftMark`) hash join, and only a single-key null-aware join is swapped by `JoinSelection`. So the outer side is always the build side and is held in memory, even when the subquery is small. @comphead showed the effect in https://github.com/apache/datafusion/pull/19857#discussion_r4106477526: ```sql CREATE TABLE big AS SELECT CASE WHEN v < 0 THEN NULL ELSE v END AS a, v % 1000 AS b FROM generate_series(1, 2000000) t(v); CREATE TABLE small AS SELECT CASE WHEN v < 0 THEN NULL ELSE v END AS x, v % 1000 AS y FROM generate_series(1, 100) t(v); -- datafusion-cli --memory-limit 64M SELECT count(*) FROM big WHERE a NOT IN (SELECT x FROM small); -- RightAnti, builds on `small`: runs SELECT count(*) FROM big WHERE (a, b) NOT IN (SELECT x, y FROM small); -- LeftAnti, builds on `big`: Resources exhausted ... Failed to allocate additional 75.6 MB for HashJoinInput ``` The NULL path also pairs every NULL-valued row with every row on the other side (see the related issue on treating NOT NULL elements as scope keys). ### Describe the solution you'd like A multi-key null-aware `RightAnti` that builds on the subquery side, deduplicates it, and decides each outer (probe) row as it streams, the same shape as PostgreSQL's `findPartialMatch`. Each probe row is TRUE unless some build row in its correlation scope matches it exactly or has no definite mismatch with it. ### Describe alternatives you've considered Keeping the outer side as the build side and only narrowing the candidate pairs (see the related issue on NOT NULL elements as scope keys). That helps the NULL path but not the memory use. ### Additional context The `null_aware_join` benchmark suite has Q10 to Q12 for the multi-column shapes. -- 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]
