neilconway commented on code in PR #19857: URL: https://github.com/apache/datafusion/pull/19857#discussion_r3100490162
########## datafusion/sqllogictest/test_files/null_aware_anti_join.slt: ########## @@ -394,16 +394,202 @@ query II rowsort SELECT * FROM test_table WHERE (c1 NOT IN (SELECT c2 FROM test_table)) = true; ---- -# NOTE: The correlated subquery version from issue #10583: -# SELECT * FROM test_table t1 WHERE c1 NOT IN (SELECT c2 FROM test_table t2 WHERE t1.c1 = t2.c1) -# is not yet supported because it creates a multi-column join (correlation + NOT IN condition). -# This is a known limitation - currently only supports single column null-aware anti joins. -# This will be addressed in next Phase (multi-column support). +############# +## Test 19: Multi-column NOT IN with NULL in subquery +############# + +statement ok +CREATE TABLE multi_col_outer(a INT, b INT, value TEXT) AS VALUES +(1, 2, 'x'), +(3, 4, 'y'), +(5, 6, 'z'), +(NULL, 8, 'w'); + +statement ok +CREATE TABLE multi_col_inner_with_null(x INT, y INT) AS VALUES +(1, 2), +(NULL, 4); + +# Should return empty because subquery has NULL +query IIT rowsort +SELECT * FROM multi_col_outer +WHERE (a, b) NOT IN (SELECT x, y FROM multi_col_inner_with_null); +---- + +############# +## Test 20: Multi-column NOT IN without NULL +############# + +statement ok +CREATE TABLE multi_col_inner_no_null(x INT, y INT) AS VALUES +(1, 2); + +# Should return (3, 4, 'y'), (5, 6, 'z') +# The row (NULL, 8, 'w') is not returned because NULL is not comparable +query IIT rowsort +SELECT * FROM multi_col_outer +WHERE (a, b) NOT IN (SELECT x, y FROM multi_col_inner_no_null); +---- +3 4 y +5 6 z Review Comment: This is wrong; compare with the output of Postgres, which returns ``` | a | b | value | | --- | --- | ----- | | 3 | 4 | y | | 5 | 6 | z | | | 8 | w | ``` `(NULL, 8, 'w')` *should* be returned, because we know that `b=8` does not match any row in the subquery result set. -- 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]
