adriangb opened a new pull request, #25339: URL: https://github.com/apache/datafusion/pull/25339
## Which issue does this PR close? - Closes https://github.com/apache/datafusion/issues/25336. ## Rationale for this change A correlated `NOT IN` subquery gives wrong results when the correlation is not an equality and the subquery column contains NULL. There is no error and no warning. ```sql CREATE TABLE t1(id INT, z INT) AS VALUES (1,10), (2,20), (NULL,30), (4,40); CREATE TABLE t2(id INT, z INT) AS VALUES (1,5), (NULL,50); SELECT id FROM t1 WHERE id NOT IN (SELECT t2.id FROM t2 WHERE t2.z < t1.z) ORDER BY id; -- main: (no rows) this PR, DuckDB, PostgreSQL: 2, 4 SELECT id FROM t1 WHERE NOT (id IN (SELECT t2.id FROM t2 WHERE t2.z < t1.z)) OR id = 4 ORDER BY id; -- main: 2, 4, NULL this PR, DuckDB, PostgreSQL: 2, 4 ``` The same gap also makes an equality-correlated `NOT IN` in a `WHERE` clause fail to plan: ```sql SELECT id FROM t1 WHERE id NOT IN (SELECT t2.id FROM t2 WHERE t2.z = t1.z); -- main: Error during planning: null_aware LeftAnti joins only support single column join key, got 2 columns ``` The fix sketch in the issue (turn off `null_aware` for a `LeftAnti` join that has a join filter) does not work. I tried it: the plain anti join ignores NULLs completely, so queries that are correct today start to return rows. For example, `id NOT IN (SELECT t2.id FROM t2 WHERE t2.z > t1.z)` must return no rows, and returns `1, 2, 4, NULL` with that change. ## What changes are included in this PR? The hash join already had the right mechanism for correlated `NOT IN` mark joins with equality correlation keys: a per-build-row bitmap that records "this row's `NOT IN` is UNKNOWN". This PR uses that mechanism for every correlated null-aware join and makes it apply the join filter. The commits are split for review: 1. `HashJoinExec`: a null-aware `LeftAnti` or `LeftMark` join is correlated when it has correlation scope keys or a join filter. For a NULL value on either side, the join finds the candidate (build, probe) row pairs through the scope key hash map, or takes all pairs when there are no scope keys. The join filter then decides which pairs make a build row UNKNOWN. The `LeftAnti` final stage drops those rows. The extra work is only for rows that have a NULL value key, so it is zero when the data has no NULLs. `JoinSelection` swaps a null-aware `LeftAnti` only when it has a single key and no filter. 2. `DecorrelatePredicateSubquery`: a `NOT IN` mark join with a non-equality correlation is now planned as null-aware. Four `EXPLAIN` results in `subquery.slt` change: the mark join now shows `null_aware`, and in one of them the join is no longer swapped to `RightMark`, because null-aware mark joins are never swapped. 3. Tests. ## What is the testing strategy for this PR? - New sqllogictest cases in `null_aware_anti_join.slt` and `null_aware_mark_join.slt`: the queries from the issue, NULL outer values with empty and non-empty subquery results, equality plus non-equality correlation, a filter on the subquery value itself, the positive `IN` form, the mark column through `IS NULL` / `IS TRUE` / `IS FALSE` / `NOT ... OR` and directly in a `SELECT` list, and runs with `batch_size = 1`. I checked all expected results with DuckDB 1.5.2 and PostgreSQL 17.11. 14 of these cases fail on `main`. - New `HashJoinExec` unit tests for a null-aware `LeftAnti` and `LeftMark` join that has a join filter and no scope keys, at all batch sizes. ## Are there any user-facing changes? Queries that returned wrong results now return correct results, and correlated `NOT IN` with an equality correlation in a `WHERE` clause no longer fails to plan. There are no public API changes. 🤖 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]
