Skip to content

Commit 23c1b59

Browse files
authored
Add regression tests for nullable NOT IN expressions (apache#25994)
## Which issue does this PR close? Closes apache#25473. Closes apache#25474. ## Rationale for this change Issues apache#25473 and apache#25474 reported wrong results for `NOT IN (subquery)` when the subquery column is `NOT NULL` but the value being compared can evaluate to `NULL`. This includes NULL constants such as `CAST(NULL AS INT)` and nullable expressions over `NOT NULL` columns, such as a `CASE` expression without an `ELSE`. The fix itself landed in apache#25338, which determines null-awareness from the full `IN` operand expressions. This PR adds the missing regression coverage for both issues so this behavior does not regress. ## What changes are included in this PR? This is a test-only change. There are no production code or public API changes. It adds optimizer unit tests that verify: - A NULL constant compared against a non-nullable subquery column produces a projected `__correlated_sq_1_value` and a `null_aware` `LeftAnti` join. - A non-nullable expression such as `test.c + 1`, where `test.c` is non-nullable, continues to use a regular `LeftAnti` join without `null_aware`. It adds SQL logic tests for null-aware anti joins covering: - `CAST(NULL AS INT)`, untyped `NULL`, and `NULLIF(1, 1)`. - Nullable expressions over `NOT NULL` columns. - Nullable expressions on the subquery side. - NULL values against an empty subquery. - A non-NULL constant control. - A non-nullable `x + 1` control. - Logical and physical `EXPLAIN` plans for the NULL constant, nullable `CASE`, and non-nullable `x + 1` cases. - The NULL constant and nullable `CASE` cases with `datafusion.execution.target_partitions = 1`. It also adds SQL logic tests for the mark-join path, including the `... NOT IN (...) OR x = 99` form and a `SELECT`-list control that verifies the mark evaluates to `NULL`. ## Are these changes tested? Yes. This PR adds regression tests in: - `datafusion/optimizer/src/decorrelate_predicate_subquery.rs` - `datafusion/sqllogictest/test_files/null_aware_anti_join.slt` - `datafusion/sqllogictest/test_files/null_aware_mark_join.slt` The new tests verify both result correctness and expected logical/physical plan shapes, including that nullable operands use null-aware joins while non-nullable operands remain non-null-aware. Verification: ```bash cargo test -p datafusion-optimizer decorrelate_predicate_subquery cargo test -p datafusion-sqllogictest --test sqllogictests -- null_aware cargo test -p datafusion-sqllogictest --test sqllogictests -- subquery_projection ``` ## Are there any user-facing changes? No new user-facing behavior is introduced by this PR. The underlying correctness fix already landed in apache#25338. This PR only adds regression coverage for that behavior and does not change any public APIs. ## LLM-generated code disclosure This PR includes LLM-generated code and comments. All LLM-generated content has been manually reviewed.
1 parent 11c8c3d commit 23c1b59

3 files changed

Lines changed: 284 additions & 0 deletions

File tree

‎datafusion/optimizer/src/decorrelate_predicate_subquery.rs‎

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2050,6 +2050,60 @@ mod tests {
20502050
)
20512051
}
20522052

2053+
/// A NULL constant has no column, but it is still nullable. The join must
2054+
/// be null-aware although the subquery column `sq.c` is not nullable
2055+
/// (<https://github.com/apache/datafusion/issues/25473>).
2056+
#[test]
2057+
fn null_constant_not_in_non_nullable_subquery_is_null_aware() -> Result<()> {
2058+
let plan = LogicalPlanBuilder::from(test_table_scan()?)
2059+
.filter(not_in_subquery(
2060+
lit(ScalarValue::UInt32(None)),
2061+
test_subquery_with_name("sq")?,
2062+
))?
2063+
.project(vec![col("test.b")])?
2064+
.build()?;
2065+
2066+
assert_optimized_plan_equal!(
2067+
plan,
2068+
@r"
2069+
Projection: test.b [b:UInt32]
2070+
Projection: test.a, test.b, test.c [a:UInt32, b:UInt32, c:UInt32]
2071+
LeftAnti Join: Filter: __correlated_sq_1_value = __correlated_sq_1.c null_aware [a:UInt32, b:UInt32, c:UInt32, __correlated_sq_1_value:UInt32;N]
2072+
Projection: test.a, test.b, test.c, UInt32(NULL) AS __correlated_sq_1_value [a:UInt32, b:UInt32, c:UInt32, __correlated_sq_1_value:UInt32;N]
2073+
TableScan: test [a:UInt32, b:UInt32, c:UInt32]
2074+
SubqueryAlias: __correlated_sq_1 [c:UInt32]
2075+
Projection: sq.c [c:UInt32]
2076+
TableScan: sq [a:UInt32, b:UInt32, c:UInt32]
2077+
"
2078+
)
2079+
}
2080+
2081+
/// `test.c + 1` over a non-nullable `test.c` cannot be NULL, and neither can
2082+
/// `sq.c`, so the anti join stays plain
2083+
/// (<https://github.com/apache/datafusion/issues/25474>).
2084+
#[test]
2085+
fn non_nullable_key_expr_not_in_is_not_null_aware() -> Result<()> {
2086+
let plan = LogicalPlanBuilder::from(test_table_scan()?)
2087+
.filter(not_in_subquery(
2088+
col("test.c") + lit(1u32),
2089+
test_subquery_with_name("sq")?,
2090+
))?
2091+
.project(vec![col("test.b")])?
2092+
.build()?;
2093+
2094+
assert_optimized_plan_equal!(
2095+
plan,
2096+
@r"
2097+
Projection: test.b [b:UInt32]
2098+
LeftAnti Join: Filter: test.c + UInt32(1) = __correlated_sq_1.c [a:UInt32, b:UInt32, c:UInt32]
2099+
TableScan: test [a:UInt32, b:UInt32, c:UInt32]
2100+
SubqueryAlias: __correlated_sq_1 [c:UInt32]
2101+
Projection: sq.c [c:UInt32]
2102+
TableScan: sq [a:UInt32, b:UInt32, c:UInt32]
2103+
"
2104+
)
2105+
}
2106+
20532107
#[test]
20542108
fn correlated_not_in_mark_join_is_null_aware_for_hashable_filter() -> Result<()> {
20552109
let outer_scan = nullable_scalar_mark_scan("outer_t")?;

‎datafusion/sqllogictest/test_files/null_aware_anti_join.slt‎

Lines changed: 172 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -942,3 +942,175 @@ DROP TABLE nai_res_outer;
942942

943943
statement ok
944944
DROP TABLE nai_res_inner;
945+
946+
#############
947+
## Regression: a NULL `NOT IN` value over a `NOT NULL` subquery column
948+
##
949+
## `NULL NOT IN (non-empty set)` is UNKNOWN, so a `WHERE` clause must remove
950+
## every row, even when no column in the comparison is nullable. The join must
951+
## be null-aware when the value *expression* can be NULL: a NULL constant
952+
## (https://github.com/apache/datafusion/issues/25473) or a nullable expression
953+
## over `NOT NULL` columns (https://github.com/apache/datafusion/issues/25474).
954+
#############
955+
956+
statement ok
957+
CREATE TABLE nanull_o(x INT NOT NULL) AS VALUES (1), (2);
958+
959+
statement ok
960+
CREATE TABLE nanull_on(x INT) AS VALUES (1), (2);
961+
962+
statement ok
963+
CREATE TABLE nanull_i(id INT NOT NULL) AS VALUES (1), (3);
964+
965+
statement ok
966+
CREATE TABLE nanull_ie(id INT NOT NULL) AS SELECT * FROM (VALUES (1)) WHERE false;
967+
968+
# NULL constants: UNKNOWN for every row, so no rows.
969+
query I
970+
SELECT x FROM nanull_o WHERE CAST(NULL AS INT) NOT IN (SELECT id FROM nanull_i);
971+
----
972+
973+
query I
974+
SELECT x FROM nanull_o WHERE NULL NOT IN (SELECT id FROM nanull_i);
975+
----
976+
977+
query I
978+
SELECT x FROM nanull_o WHERE NULLIF(1, 1) NOT IN (SELECT id FROM nanull_i);
979+
----
980+
981+
# `x + NULL` is simplified to a NULL constant before decorrelation.
982+
query I
983+
SELECT x FROM nanull_o WHERE x + CAST(NULL AS INT) NOT IN (SELECT id FROM nanull_i);
984+
----
985+
986+
query I
987+
SELECT x FROM nanull_o WHERE NOT (CAST(NULL AS INT) NOT IN (SELECT id FROM nanull_i));
988+
----
989+
990+
# The nullability of the outer column does not matter.
991+
query I
992+
SELECT x FROM nanull_on WHERE CAST(NULL AS INT) NOT IN (SELECT id FROM nanull_i);
993+
----
994+
995+
# Control: against an empty subquery, a NULL value gives TRUE.
996+
query I rowsort
997+
SELECT x FROM nanull_o WHERE CAST(NULL AS INT) NOT IN (SELECT id FROM nanull_ie);
998+
----
999+
1
1000+
2
1001+
1002+
# Control: a non-NULL constant absent from the subquery gives TRUE.
1003+
query I rowsort
1004+
SELECT x FROM nanull_o WHERE 5 NOT IN (SELECT id FROM nanull_i);
1005+
----
1006+
1
1007+
2
1008+
1009+
# Assert decorrelation and null-awareness, not physical join choices.
1010+
statement ok
1011+
SET datafusion.explain.logical_plan_only = true;
1012+
1013+
# The NULL constant is projected as the key of a null-aware anti join, and no
1014+
# filter is pushed into the subquery.
1015+
query TT
1016+
EXPLAIN SELECT x FROM nanull_o WHERE CAST(NULL AS INT) NOT IN (SELECT id FROM nanull_i);
1017+
----
1018+
logical_plan
1019+
01)Projection: nanull_o.x
1020+
02)--LeftAnti Join: __correlated_sq_1_value = __correlated_sq_1.id null_aware
1021+
03)----Projection: nanull_o.x, Int32(NULL) AS __correlated_sq_1_value
1022+
04)------TableScan: nanull_o projection=[x]
1023+
05)----SubqueryAlias: __correlated_sq_1
1024+
06)------TableScan: nanull_i projection=[id]
1025+
1026+
# Nullable expressions over `NOT NULL` columns: NULL for every row, so no rows.
1027+
query I
1028+
SELECT x FROM nanull_o WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nanull_i);
1029+
----
1030+
1031+
# Not folded: the key keeps its column reference, so null-awareness comes from
1032+
# the nullability of the whole expression.
1033+
query I
1034+
SELECT x FROM nanull_o WHERE x + (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nanull_i);
1035+
----
1036+
1037+
# Control: the same expression over a nullable column.
1038+
query I
1039+
SELECT x FROM nanull_on WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nanull_i);
1040+
----
1041+
1042+
# Control: against an empty subquery, a NULL value gives TRUE.
1043+
query I rowsort
1044+
SELECT x FROM nanull_o WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nanull_ie);
1045+
----
1046+
1
1047+
2
1048+
1049+
# Nullable expression on the subquery side: every inner value is NULL, so no
1050+
# rows.
1051+
query I
1052+
SELECT x FROM nanull_o WHERE x NOT IN (SELECT CASE WHEN id > 100 THEN id END FROM nanull_i);
1053+
----
1054+
1055+
query TT
1056+
EXPLAIN SELECT x FROM nanull_o WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nanull_i);
1057+
----
1058+
logical_plan
1059+
01)LeftAnti Join: CASE WHEN nanull_o.x > Int32(100) THEN Int64(1) END = __correlated_sq_1.nanull_i.id null_aware
1060+
02)--TableScan: nanull_o projection=[x]
1061+
03)--SubqueryAlias: __correlated_sq_1
1062+
04)----Projection: CAST(nanull_i.id AS Int64)
1063+
05)------TableScan: nanull_i projection=[id]
1064+
1065+
# `x + 1` over `NOT NULL x` cannot be NULL, so the anti join stays plain.
1066+
query I
1067+
SELECT x FROM nanull_o WHERE x + 1 NOT IN (SELECT id FROM nanull_i);
1068+
----
1069+
1
1070+
1071+
query TT
1072+
EXPLAIN SELECT x FROM nanull_o WHERE x + 1 NOT IN (SELECT id FROM nanull_i);
1073+
----
1074+
logical_plan
1075+
01)LeftAnti Join: CAST(nanull_o.x AS Int64) + Int64(1) = __correlated_sq_1.nanull_i.id
1076+
02)--TableScan: nanull_o projection=[x]
1077+
03)--SubqueryAlias: __correlated_sq_1
1078+
04)----Projection: CAST(nanull_i.id AS Int64)
1079+
05)------TableScan: nanull_i projection=[id]
1080+
1081+
statement ok
1082+
RESET datafusion.explain.logical_plan_only;
1083+
1084+
# The same results with a single partition.
1085+
statement ok
1086+
SET datafusion.execution.target_partitions = 1;
1087+
1088+
query I
1089+
SELECT x FROM nanull_o WHERE CAST(NULL AS INT) NOT IN (SELECT id FROM nanull_i);
1090+
----
1091+
1092+
query I
1093+
SELECT x FROM nanull_o WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nanull_i);
1094+
----
1095+
1096+
query I rowsort
1097+
SELECT x FROM nanull_o WHERE CAST(NULL AS INT) NOT IN (SELECT id FROM nanull_ie);
1098+
----
1099+
1
1100+
2
1101+
1102+
# The SLT runner sets target_partitions to 4, so restore that value explicitly.
1103+
statement ok
1104+
SET datafusion.execution.target_partitions = 4;
1105+
1106+
statement ok
1107+
DROP TABLE nanull_o;
1108+
1109+
statement ok
1110+
DROP TABLE nanull_on;
1111+
1112+
statement ok
1113+
DROP TABLE nanull_i;
1114+
1115+
statement ok
1116+
DROP TABLE nanull_ie;

‎datafusion/sqllogictest/test_files/null_aware_mark_join.slt‎

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -998,3 +998,61 @@ DROP TABLE nam_guard_outer;
998998

999999
statement ok
10001000
DROP TABLE nam_guard_inner;
1001+
1002+
#############
1003+
## Regression: a NULL `NOT IN` value over a `NOT NULL` subquery column in a
1004+
## mark join (https://github.com/apache/datafusion/issues/25473,
1005+
## https://github.com/apache/datafusion/issues/25474).
1006+
##
1007+
## The mark must be NULL, not FALSE, so `NOT mark OR x = 99` keeps no row.
1008+
#############
1009+
1010+
statement ok
1011+
CREATE TABLE nmnull_o(x INT NOT NULL) AS VALUES (1), (2);
1012+
1013+
statement ok
1014+
CREATE TABLE nmnull_i(id INT NOT NULL) AS VALUES (1), (3);
1015+
1016+
# NULL constant.
1017+
query I
1018+
SELECT x FROM nmnull_o WHERE CAST(NULL AS INT) NOT IN (SELECT id FROM nmnull_i) OR x = 99;
1019+
----
1020+
1021+
# Assert decorrelation and null-awareness, not physical join choices.
1022+
statement ok
1023+
SET datafusion.explain.logical_plan_only = true;
1024+
1025+
# The NULL constant is the key of a null-aware mark join.
1026+
query TT
1027+
EXPLAIN SELECT x FROM nmnull_o WHERE CAST(NULL AS INT) NOT IN (SELECT id FROM nmnull_i) OR x = 99;
1028+
----
1029+
logical_plan
1030+
01)Projection: nmnull_o.x
1031+
02)--Filter: NOT __correlated_sq_1.mark OR nmnull_o.x = Int32(99)
1032+
03)----Projection: nmnull_o.x, __correlated_sq_1.mark
1033+
04)------LeftMark Join: __correlated_sq_1_value = __correlated_sq_1.id null_aware
1034+
05)--------Projection: nmnull_o.x, Int32(NULL) AS __correlated_sq_1_value
1035+
06)----------TableScan: nmnull_o projection=[x]
1036+
07)--------SubqueryAlias: __correlated_sq_1
1037+
08)----------TableScan: nmnull_i projection=[id]
1038+
1039+
statement ok
1040+
RESET datafusion.explain.logical_plan_only;
1041+
1042+
# Nullable expression over a `NOT NULL` column.
1043+
query I
1044+
SELECT x FROM nmnull_o WHERE (CASE WHEN x > 100 THEN 1 END) NOT IN (SELECT id FROM nmnull_i) OR x = 99;
1045+
----
1046+
1047+
# Control: the `SELECT`-list form gives a NULL mark.
1048+
query IB rowsort
1049+
SELECT x, CAST(NULL AS INT) NOT IN (SELECT id FROM nmnull_i) AS m FROM nmnull_o;
1050+
----
1051+
1 NULL
1052+
2 NULL
1053+
1054+
statement ok
1055+
DROP TABLE nmnull_o;
1056+
1057+
statement ok
1058+
DROP TABLE nmnull_i;

0 commit comments

Comments
 (0)