viirya commented on code in PR #19635: URL: https://github.com/apache/datafusion/pull/19635#discussion_r2666841263
########## datafusion/sqllogictest/test_files/null_aware_anti_join.slt: ########## @@ -0,0 +1,414 @@ +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at + +# http://www.apache.org/licenses/LICENSE-2.0 + +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +############# +## Null-Aware Anti Join Tests +## Tests for automatic null-aware semantics in NOT IN subqueries +############# + +statement ok +CREATE TABLE outer_table(id INT, value TEXT) AS VALUES +(1, 'a'), +(2, 'b'), +(3, 'c'), +(4, 'd'), +(NULL, 'e'); + +statement ok +CREATE TABLE inner_table_no_null(id INT, value TEXT) AS VALUES +(2, 'x'), +(4, 'y'); + +statement ok +CREATE TABLE inner_table_with_null(id INT, value TEXT) AS VALUES +(2, 'x'), +(NULL, 'y'); + +############# +## Test 1: NOT IN with no NULLs - should behave like regular anti join +############# + +query IT rowsort +SELECT * FROM outer_table WHERE id NOT IN (SELECT id FROM inner_table_no_null); +---- +1 a +3 c + +# Verify the plan uses LeftAnti join +query TT +EXPLAIN SELECT * FROM outer_table WHERE id NOT IN (SELECT id FROM inner_table_no_null); +---- +logical_plan +01)LeftAnti Join: outer_table.id = __correlated_sq_1.id +02)--TableScan: outer_table projection=[id, value] +03)--SubqueryAlias: __correlated_sq_1 +04)----TableScan: inner_table_no_null projection=[id] +physical_plan +01)HashJoinExec: mode=CollectLeft, join_type=LeftAnti, on=[(id@0, id@0)] +02)--DataSourceExec: partitions=1, partition_sizes=[1] +03)--DataSourceExec: partitions=1, partition_sizes=[1] + +############# +## Test 2: NOT IN with NULL in subquery - should return 0 rows (null-aware semantics) +############# + +query IT rowsort +SELECT * FROM outer_table WHERE id NOT IN (SELECT id FROM inner_table_with_null); +---- + +# Verify the result is empty even though there are rows in outer_table +# that don't match the non-NULL value (2) in the subquery. +# This is correct null-aware behavior: if subquery contains NULL, result is unknown. + +############# +## Test 3: NOT IN with NULL in outer table but not in subquery +## NULL rows from outer should not appear in output +############# + +query IT rowsort +SELECT * FROM outer_table WHERE id NOT IN (SELECT id FROM inner_table_no_null) AND id IS NOT NULL; +---- +1 a +3 c + +############# +## Test 4: Test with all NULL subquery +############# + +statement ok +CREATE TABLE all_null_table(id INT) AS VALUES (NULL), (NULL); + +query IT rowsort +SELECT * FROM outer_table WHERE id NOT IN (SELECT id FROM all_null_table); +---- + +############# +## Test 5: Test with empty subquery - should return all rows +############# + +statement ok +CREATE TABLE empty_table(id INT, value TEXT); + +query IT rowsort +SELECT * FROM outer_table WHERE id NOT IN (SELECT id FROM empty_table); +---- +1 a +2 b +3 c +4 d + +############# +## Test 6: NOT IN with complex expression +############# + +query IT rowsort +SELECT * FROM outer_table WHERE id + 1 NOT IN (SELECT id FROM inner_table_no_null); +---- +2 b +4 d + +############# +## Test 7: NOT IN with complex expression and NULL in subquery +############# + +query IT rowsort +SELECT * FROM outer_table WHERE id + 1 NOT IN (SELECT id FROM inner_table_with_null); +---- + +############# +## Test 8: Multiple NOT IN conditions (AND) +############# + +statement ok +CREATE TABLE inner_table2(id INT) AS VALUES (1), (3); + +query IT rowsort +SELECT * FROM outer_table +WHERE id NOT IN (SELECT id FROM inner_table_no_null) + AND id NOT IN (SELECT id FROM inner_table2); +---- + +############# +## Test 9: Multiple NOT IN conditions (OR) +############# + +query IT rowsort +SELECT * FROM outer_table +WHERE id NOT IN (SELECT id FROM inner_table_no_null) + OR id NOT IN (SELECT id FROM inner_table2); +---- +1 a +2 b +3 c +4 d +NULL e Review Comment: Why We Cannot Simply Use LeftAnti Joins Short Answer: Because LeftAnti joins filter rows immediately, while OR conditions need to evaluate boolean expressions from multiple subqueries simultaneously. The Fundamental Difference: 1. LeftAnti Join (filtering): SELECT * FROM outer_table WHERE id NOT IN (SELECT id FROM subquery) - The join filters out matching rows directly - Result: rows that don't match 2. OR Condition (boolean evaluation): SELECT * FROM outer_table WHERE id NOT IN (SELECT id FROM subquery1) OR id NOT IN (SELECT id FROM subquery2) - Need boolean values from BOTH subqueries - Then evaluate: NOT match1 OR NOT match2 - Can't do this with filtering joins alone Why Mark Joins Are Used: - Mark joins add a boolean column instead of filtering - This allows complex boolean expressions like OR, AND, NOT to be evaluated in a subsequent Filter operator - Example: WHERE (NOT mark1 OR NOT mark2) AND other_condition The Current Problem: - Mark joins don't support null-aware semantics - They set mark = FALSE when no match, but should set mark = NULL when join key is NULL Why It's Complex to Fix: - The mark column is created deep in the join execution code (build_batch_from_indices) - That function doesn't currently have access to: - The null_aware flag - The join key columns (to check if they're NULL) - Would require threading these through multiple layers of the codebase We can't use LeftAnti because it filters instead of producing boolean values, and implementing null-aware mark joins requires significant refactoring of the join execution internals. I will leave it to future work. -- 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]
