Skip to content

Commit fe408e3

Browse files
adriangbclaude
adriangb
and
claude
committed
fix: keep the column null test for a correlated NOT IN with two keys
The previous commit made a `LeftAnti` join for `NOT IN` null-aware when a key expression can be NULL. Most scalar functions report a nullable result, so a key such as `upper(s)` over a `NOT NULL` column made the join null-aware. A correlated `NOT IN` has two keys (the value and the correlation), and a null-aware `LeftAnti` hash join supports one key only. Thus this query failed to plan: SELECT * FROM t1 WHERE upper(t1.s) NOT IN (SELECT t2.s FROM t2 WHERE t2.k = t1.k); Error during planning: null_aware LeftAnti joins only support single column join key, got 2 columns For a `LeftAnti` join with more than one key, use the column test on the whole join filter again, as on `main`. The expression test stays for one key and for all mark joins. Also add tests for an empty subquery with a `NULLIF` key on the single mark join path, and for the correlated `NOT IN` above. Co-Authored-By: Claude Opus 5 <[email protected]>
1 parent b44d847 commit fe408e3

2 files changed

Lines changed: 94 additions & 6 deletions

File tree

‎datafusion/optimizer/src/decorrelate_predicate_subquery.rs‎

Lines changed: 23 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -706,12 +706,29 @@ fn build_join(
706706
left.schema(),
707707
sub_query_alias.schema(),
708708
)?;
709-
join_keys_may_be_null(
710-
&equijoin_keys,
711-
residual_filter.as_ref(),
712-
left.schema(),
713-
sub_query_alias.schema(),
714-
)?
709+
if equijoin_keys.len() > 1 {
710+
// A null-aware `LeftAnti` hash join supports one key only. A
711+
// correlated `NOT IN` has two or more keys (the value and the
712+
// correlation), so keep the column test on the whole filter here.
713+
// A function key such as `upper(s)` over a non-nullable column
714+
// then does not make the join null-aware and fail to plan. The
715+
// column test misses a NULL that only the key expression makes,
716+
// as in `NULLIF(id, 1)`: see
717+
// https://github.com/apache/datafusion/issues/25347.
718+
join_keys_may_be_null(
719+
&[],
720+
Some(&join_filter),
721+
left.schema(),
722+
sub_query_alias.schema(),
723+
)?
724+
} else {
725+
join_keys_may_be_null(
726+
&equijoin_keys,
727+
residual_filter.as_ref(),
728+
left.schema(),
729+
sub_query_alias.schema(),
730+
)?
731+
}
715732
} else {
716733
false
717734
};

‎datafusion/sqllogictest/test_files/subquery_projection.slt‎

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -328,6 +328,77 @@ SELECT id FROM nn WHERE NULLIF(id, 1) NOT IN (SELECT id FROM r3);
328328
----
329329
4
330330

331+
# An empty subquery gives `false` also for a NULL key, and the plan stays one
332+
# null-aware mark join.
333+
statement ok
334+
CREATE TABLE r_empty(id INT NOT NULL) AS SELECT * FROM r3 WHERE false;
335+
336+
query TT
337+
EXPLAIN SELECT id, NULLIF(id, 1) IN (SELECT id FROM r_empty) AS m FROM nn;
338+
----
339+
logical_plan
340+
01)Projection: nn.id, __correlated_sq_1.mark AS m
341+
02)--LeftMark Join: nullif(CAST(nn.id AS Int64), Int64(1)) = __correlated_sq_1.r_empty.id null_aware
342+
03)----TableScan: nn projection=[id]
343+
04)----SubqueryAlias: __correlated_sq_1
344+
05)------Projection: CAST(r_empty.id AS Int64)
345+
06)--------TableScan: r_empty projection=[id]
346+
physical_plan
347+
01)ProjectionExec: expr=[id@0 as id, mark@1 as m]
348+
02)--HashJoinExec: mode=CollectLeft, join_type=LeftMark, on=[(nullif(nn.id,Int64(1))@1, r_empty.id@0)], projection=[id@0, mark@2], null_aware
349+
03)----ProjectionExec: expr=[id@0 as id, nullif(CAST(id@0 AS Int64), 1) as nullif(nn.id,Int64(1))]
350+
04)------DataSourceExec: partitions=1, partition_sizes=[1]
351+
05)----ProjectionExec: expr=[CAST(id@0 AS Int64) as r_empty.id]
352+
06)------DataSourceExec: partitions=1, partition_sizes=[0]
353+
354+
query IB rowsort
355+
SELECT id, NULLIF(id, 1) IN (SELECT id FROM r_empty) AS m FROM nn;
356+
----
357+
1 false
358+
2 false
359+
4 false
360+
361+
query IB rowsort
362+
SELECT id, NULLIF(id, 1) NOT IN (SELECT id FROM r_empty) AS m FROM nn;
363+
----
364+
1 true
365+
2 true
366+
4 true
367+
368+
statement ok
369+
DROP TABLE r_empty;
370+
371+
# A correlated `NOT IN` filter builds a `LeftAnti` join with two keys: the
372+
# value and the correlation. A null-aware `LeftAnti` hash join supports one key
373+
# only, so a function key over non-nullable columns must not make this join
374+
# null-aware.
375+
statement ok
376+
CREATE TABLE t1(k INT NOT NULL, s VARCHAR NOT NULL) AS VALUES (1, 'a'), (2, 'b');
377+
378+
statement ok
379+
CREATE TABLE t2(k INT NOT NULL, s VARCHAR NOT NULL) AS VALUES (1, 'B'), (2, 'B');
380+
381+
query IT rowsort
382+
SELECT * FROM t1 WHERE upper(t1.s) NOT IN (SELECT t2.s FROM t2 WHERE t2.k = t1.k);
383+
----
384+
1 a
385+
386+
# `NULLIF(k, 1)` is NULL for `k = 1`, and that group of `t2` is not empty, so
387+
# the correct result has no row for `k = 1`. The two key join is not
388+
# null-aware, so this row is wrong. See
389+
# https://github.com/apache/datafusion/issues/25347.
390+
query I rowsort
391+
SELECT k FROM t1 WHERE NULLIF(t1.k, 1) NOT IN (SELECT t2.k + 10 FROM t2 WHERE t2.k = t1.k);
392+
----
393+
1
394+
2
395+
396+
statement ok
397+
DROP TABLE t1;
398+
399+
statement ok
400+
DROP TABLE t2;
401+
331402
statement ok
332403
DROP TABLE nn;
333404

0 commit comments

Comments
 (0)