Skip to content

Commit 3af8737

Browse files
adriangbclaude
andcommitted
fix: keep the column null test for a NOT IN anti join with a residual filter
A null-aware `LeftAnti` join does not apply the residual join filter when it decides whether a NULL makes the result UNKNOWN (apache#25336). It looks only at whether the probe side holds any row. For a correlated `NOT IN` with a non-equality correlation this is wrong in one direction: a build row whose key is NULL is dropped although the correlated subquery result for that row is empty, and `NULL NOT IN (<empty set>)` is TRUE. The column test that this branch replaces never reached that state, because a key expression such as `NULLIF(k, 1)` over `NOT NULL` columns holds no nullable column. The expression test does reach it, so it is a new wrong result: ```sql CREATE TABLE ra(k INT NOT NULL, z INT NOT NULL) AS VALUES (1, 10), (2, 20); CREATE TABLE rb(k INT NOT NULL, z INT NOT NULL) AS VALUES (5, 50); SELECT k FROM ra WHERE NULLIF(ra.k, 1) NOT IN (SELECT rb.k FROM rb WHERE rb.z < ra.z); -- correct, and `main`: 1, 2. before this commit: 2 ``` The expression test thus applies only when the split gives one key and no residual. A residual now keeps the column test, as more than one key already does. Every result of this branch is then the same as `main`'s or better. Co-Authored-By: Claude Opus 5 <[email protected]>
1 parent e501c78 commit 3af8737

2 files changed

Lines changed: 50 additions & 7 deletions

File tree

‎datafusion/optimizer/src/decorrelate_predicate_subquery.rs‎

Lines changed: 17 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -763,13 +763,23 @@ fn build_join(
763763
left.schema(),
764764
sub_query_alias.schema(),
765765
)?;
766-
if equijoin_keys.len() > 1 {
767-
// A null-aware `LeftAnti` hash join supports one key only. A
768-
// correlated `NOT IN` has two or more keys (the value and the
769-
// correlation), so keep the column test on the whole filter here.
770-
// A function key such as `upper(s)` over a non-nullable column
771-
// then does not make the join null-aware and fail to plan. The
772-
// column test misses a NULL that only the key expression makes,
766+
if equijoin_keys.len() > 1 || residual_filter.is_some() {
767+
// Keep the column test on the whole filter for these two shapes.
768+
//
769+
// More than one key: a null-aware `LeftAnti` hash join supports one
770+
// key only. A correlated `NOT IN` has two or more keys (the value
771+
// and the correlation), and a key expression that the column test
772+
// misses would make the join null-aware and fail to plan.
773+
//
774+
// A residual filter: the null-aware `LeftAnti` executor does not
775+
// apply the residual when it decides whether a NULL makes the
776+
// result UNKNOWN (https://github.com/apache/datafusion/issues/25336).
777+
// It would thus drop a row whose correlated subquery result is
778+
// empty, and `<NULL> NOT IN (<empty set>)` is TRUE. The column test
779+
// keeps such a join out of the null-aware path, exactly as on
780+
// `main`.
781+
//
782+
// The column test misses a NULL that only the key expression makes,
773783
// as in `NULLIF(id, 1)`: see
774784
// https://github.com/apache/datafusion/issues/25347.
775785
join_keys_may_be_null(

‎datafusion/sqllogictest/test_files/subquery_projection.slt‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -399,6 +399,39 @@ DROP TABLE t1;
399399
statement ok
400400
DROP TABLE t2;
401401

402+
# A non-equality correlation leaves one key and a residual join filter. The
403+
# null-aware `LeftAnti` executor does not apply the residual when it decides
404+
# whether a NULL makes the result UNKNOWN, so a function key over non-nullable
405+
# columns must not make this join null-aware either. For `k = 1` the key is
406+
# NULL and the correlated subquery result is empty, and
407+
# `NULL NOT IN (<empty set>)` is TRUE. Both rows are correct.
408+
statement ok
409+
CREATE TABLE ra(k INT NOT NULL, z INT NOT NULL) AS VALUES (1, 10), (2, 20);
410+
411+
statement ok
412+
CREATE TABLE rb(k INT NOT NULL, z INT NOT NULL) AS VALUES (5, 50);
413+
414+
query I rowsort
415+
SELECT k FROM ra WHERE NULLIF(ra.k, 1) NOT IN (SELECT rb.k FROM rb WHERE rb.z < ra.z);
416+
----
417+
1
418+
2
419+
420+
# The same shape where the correlated subquery result is not empty for the NULL
421+
# key. The correct result is `2` only. The plain anti join also gives `1`, which
422+
# is the gap that https://github.com/apache/datafusion/issues/25336 closes.
423+
query I rowsort
424+
SELECT k FROM ra WHERE NULLIF(ra.k, 1) NOT IN (SELECT rb.k FROM rb WHERE rb.z > ra.z);
425+
----
426+
1
427+
2
428+
429+
statement ok
430+
DROP TABLE ra;
431+
432+
statement ok
433+
DROP TABLE rb;
434+
402435
statement ok
403436
DROP TABLE nn;
404437

0 commit comments

Comments
 (0)