Skip to content

Commit c27ad59

Browse files
viiryaclaude
andcommitted
Add test case from GitHub issue apache#10583 for null-aware anti join
This test verifies that NOT IN with NULL in the subquery result correctly returns an empty result set. The query tests the three-valued logic semantics: Query: SELECT * FROM test_table WHERE (c1 NOT IN (SELECT c2 FROM test_table)) = true Since the subquery result contains NULL, the NOT IN predicate evaluates to UNKNOWN (not TRUE) for all rows, resulting in an empty output. Test data: - test_table: (1,1), (2,2), (3,3), (4,NULL), (NULL,0) - Subquery returns: 1, 2, 3, NULL, 0 - Expected result: empty (because NULL in subquery makes all comparisons UNKNOWN) Fixes apache#10583 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Sonnet 4.5 <[email protected]>
1 parent ac06033 commit c27ad59

1 file changed

Lines changed: 22 additions & 0 deletions

File tree

‎datafusion/sqllogictest/test_files/null_aware_anti_join.slt‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -375,10 +375,32 @@ WHERE NOT EXISTS (
375375
3 Charlie
376376
NULL Dave
377377

378+
#############
379+
## Test from GitHub issue #10583
380+
## Tests NOT IN with NULL in subquery result - should return empty result
381+
#############
382+
383+
statement ok
384+
CREATE TABLE test_table(c1 INT, c2 INT) AS VALUES
385+
(1, 1),
386+
(2, 2),
387+
(3, 3),
388+
(4, NULL),
389+
(NULL, 0);
390+
391+
# When subquery contains NULL, NOT IN should return empty result
392+
# because NULL NOT IN (values including NULL) is UNKNOWN for all rows
393+
query II rowsort
394+
SELECT * FROM test_table WHERE (c1 NOT IN (SELECT c2 FROM test_table)) = true;
395+
----
396+
378397
#############
379398
## Cleanup
380399
#############
381400

401+
statement ok
402+
DROP TABLE test_table;
403+
382404
statement ok
383405
DROP TABLE outer_table;
384406

0 commit comments

Comments
 (0)