You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/09/17 22:15:18 UTC
(datafusion) 01/01: fix: `NOT IN (subquery)` with a constant value ignores NULLs in the subquery (#25348)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-25348-c4f5a9e0f202af0231287ec77774b2e7601e018d
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit f48b5d135dea2a010eaa44cae9765ad0c1887dc1
Author: Adrian Garcia Badaracco <17...@users.noreply.github.com>
AuthorDate: Thu Sep 17 22:14:52 2026 +0000
fix: `NOT IN (subquery)` with a constant value ignores NULLs in the subquery (#25348)
## Which issue does this PR close?
- Closes #25340.
A follow-up issue covers the remaining `NOT IN` problems. See
**Follow-up work** at the end.
## Rationale for this change
`3 NOT IN (1, NULL)` is UNKNOWN. A `WHERE` clause must remove the row.
DataFusion kept every row. There was no error and no warning.
```sql
CREATE TABLE t1(id INT) AS VALUES (1), (2);
CREATE TABLE t2(id INT) AS VALUES (1), (NULL);
SELECT id FROM t1 WHERE 3 NOT IN (SELECT id FROM t2) ORDER BY id;
```
| | result |
|---|---|
| DataFusion (before) | `1, 2` ❌ |
| DataFusion (after) | *(no rows)* ✅ |
| DuckDB 1.5.2 | *(no rows)* |
| PostgreSQL 17.11 | *(no rows)* |
The subquery gives `{1, NULL}`. The value `3` is not `NULL`, but `3` is
also not known to be absent. Therefore the answer is UNKNOWN for every
row of `t1`.
The same expression in a `SELECT` list was already correct. Only the
`WHERE` clause was wrong.
### Why it happened
The value `3` holds no column. Therefore it could not become a join key.
It stayed as a join filter. Two problems followed.
```
BEFORE AFTER
WHERE 3 NOT IN (SELECT id FROM t2) WHERE 3 NOT IN (SELECT id FROM t2)
│ │
▼ ▼
anti join, no key anti join, key = (3, t2.id)
filter: 3 = t2.id no filter
│ │
├─ (1) the filter moves into the ├─ (1) nothing to move
│ subquery: WHERE t2.id = 3 │
│ the NULL row disappears │
│ │
└─ (2) no key, so the join becomes a └─ (2) the join has a key, so it
nested loop join, which cannot stays a hash join, which
do null-aware work. The flag is does null-aware work
dropped without an error correctly
│ │
▼ ▼
1, 2 ❌ (no rows) ✅
```
## What changes are included in this PR?
Three changes.
**1. Make the constant a join key**
(`datafusion/optimizer/src/decorrelate_predicate_subquery.rs`)
Add the constant to the outer side as a column. The comparison then
becomes a true equality of two columns, so the existing null-aware hash
join does the work.
```
Before: LeftAnti Join: Filter: Int64(3) = __correlated_sq_1.id null_aware
→ NestedLoopJoinExec (the null_aware flag is lost)
After: LeftAnti Join: __correlated_sq_1_value = __correlated_sq_1.id null_aware
Projection: t1.id, Int64(3) AS __correlated_sq_1_value
→ HashJoinExec ... null_aware
```
This applies only to uncorrelated subqueries. A correlated subquery
needs a second key, and a null-aware anti join accepts only one key.
**2. Keep the filter out of the subquery**
(`datafusion/optimizer/src/push_down_filter.rs`)
Do not move a predicate into the subquery side of a null-aware join. The
NULLs must reach the join. `infer_join_predicates` has the same rule
already.
**3. Report an error instead of a wrong answer**
(`datafusion/core/src/physical_planner.rs`)
Only a hash join can do null-aware work, and it needs a key. If a
null-aware join has no key, report an error. Do not build a nested loop
join that gives wrong results without a warning.
## What is the testing strategy for this PR?
New `sqllogictest` cases in
`datafusion/sqllogictest/test_files/null_aware_anti_join.slt` and
`null_aware_mark_join.slt`. They cover:
- the four queries of the issue, and its controls;
- a subquery with no NULL, which must keep all rows;
- the `OR` and `IS NULL` forms, which use a mark join;
- a user column with the same name as the new column, which must not be
ambiguous;
- the one shape that is not supported, which must report an error.
New unit tests in `decorrelate_predicate_subquery.rs` and
`push_down_filter.rs` hold the new plans.
Results:
| check | result |
|---|---|
| full `sqllogictest` suite | pass, and **no plan changes** anywhere
else |
| `datafusion-optimizer` unit tests | 853 pass |
| extended workspace suite | 10,784 tests pass, 0 fail, 66 crates |
| `cargo fmt --all` | clean |
| `cargo clippy` | clean for the changed crates |
The extended workspace figure comes from an earlier run of the same
commit on a slightly older base. The full `sqllogictest` suite and the
optimizer tests were re-run after the rebase onto current `main`.
## Are there any user-facing changes?
Yes. Two.
**1. `<constant> NOT IN (<subquery>)` in a `WHERE` clause now gives
correct results.** This is the fix.
**2. One shape now reports an error.** A constant with a correlation
that is not an equality:
```sql
SELECT id FROM t1 WHERE 3 NOT IN (SELECT id FROM t2 WHERE t2.g > t1.g);
```
```
Error during planning: null_aware LeftAnti join requires equi-join keys, but the join has none
```
This query gave wrong results before. No correct result is lost. An
error is better than a wrong answer that a user cannot see.
There are no API changes.
## Follow-up work
This PR makes every **uncorrelated** `NOT IN` correct. **Correlated**
`NOT IN` still has problems. They come from the join operator, not from
the code this PR changes.
```
NOT IN (subquery)
│
├── no outer column ──────────────▶ CORRECT (this PR)
│
└── reads an outer column
├── equality condition ───────▶ WRONG or ERROR (follow-up, parts 1 and 2)
└── other condition ──────────▶ WRONG (follow-up, part 3)
```
A test matrix of 18 query shapes gives these totals:
| | wrong or error |
|---|---|
| before this PR | 13 of 18 |
| after this PR | 10 of 18 |
The 10 remaining shapes are all correlated. They fall into three parts:
1. A null-aware anti join accepts only one key, but a correlated `NOT
IN` needs two.
2. A constant does not become a key when the subquery is correlated.
3. A null-aware join looks for NULLs only in the key, not in a leftover
filter.
Part 3 must come first, as an error. A prototype showed that part 2
alone turns a clear error into a silent wrong answer.
🤖 Generated with [Claude Code](https://claude.com/claude-code)
https://claude.ai/code/session_01VCeMPyJNgAiFpGXaz5CxF3
Co-authored-by: Claude <no...@anthropic.com>
---
datafusion/core/src/physical_planner.rs | 11 ++
.../src/decorrelate_predicate_subquery.rs | 119 ++++++++++++++++++++-
datafusion/optimizer/src/push_down_filter.rs | 61 ++++++++++-
.../test_files/null_aware_anti_join.slt | 102 ++++++++++++++++++
.../test_files/null_aware_mark_join.slt | 66 ++++++++++++
5 files changed, 355 insertions(+), 4 deletions(-)
diff --git a/datafusion/core/src/physical_planner.rs b/datafusion/core/src/physical_planner.rs
index 77997c619e..11387f2fcb 100644
--- a/datafusion/core/src/physical_planner.rs
+++ b/datafusion/core/src/physical_planner.rs
@@ -1604,6 +1604,17 @@ impl DefaultPhysicalPlanner {
&& session_state.config().repartition_joins()
&& !*null_aware;
+ // Only `HashJoinExec` implements null-aware semantics, and it
+ // needs equi-join keys to do so. Without them the join would be
+ // planned as a nested loop (or piecewise merge) join, which
+ // silently ignores the flag and returns wrong results for
+ // `NOT IN` over a nullable subquery. Fail loudly instead.
+ if *null_aware && join_on.is_empty() {
+ return plan_err!(
+ "null_aware {join_type} join requires equi-join keys, but the join has none"
+ );
+ }
+
// TODO: Allow PWMJ to deal with residual equijoin conditions
let join: Arc<dyn ExecutionPlan> = if join_on.is_empty() {
if join_filter.is_none() && *join_type == JoinType::Inner {
diff --git a/datafusion/optimizer/src/decorrelate_predicate_subquery.rs b/datafusion/optimizer/src/decorrelate_predicate_subquery.rs
index 4a12b4ab7b..0ad8b44c40 100644
--- a/datafusion/optimizer/src/decorrelate_predicate_subquery.rs
+++ b/datafusion/optimizer/src/decorrelate_predicate_subquery.rs
@@ -471,7 +471,14 @@ fn build_join(
replace_qualified_name(filter, &all_correlated_cols, &alias).map(Some)
})?;
- let join_filter = match (join_filter_opt, in_predicate_opt.cloned()) {
+ // The outer value expression of an `IN`/`NOT IN` predicate whose join filter
+ // is nothing but that predicate, recorded together with the subquery column
+ // it is compared against and a name for the column it can be projected as.
+ // Correlated subqueries are excluded on purpose: their correlation predicate
+ // is a second join key, and null-aware hash joins accept only a single key.
+ let mut in_value_expr = None;
+
+ let mut join_filter = match (join_filter_opt, in_predicate_opt.cloned()) {
(
Some(join_filter),
Some(Expr::BinaryExpr(BinaryExpr {
@@ -493,14 +500,56 @@ fn build_join(
right,
})),
) => {
+ let value_name = format!("{alias}_value");
let right_col = create_col_from_scalar_expr(&right, alias)?;
+ let value = left.deref().clone();
+ in_value_expr = Some((value.clone(), right_col.clone(), value_name));
- Expr::eq(left.deref().clone(), Expr::Column(right_col))
+ Expr::eq(value, Expr::Column(right_col))
}
(None, None) => lit(true),
_ => return Ok(None),
};
+ // `<constant> IN/NOT IN (<subquery>)`: the outer value expression holds no
+ // column reference, so `<constant> = __correlated_sq.col` is not a valid
+ // equi-join key (see `find_valid_equijoin_key_pair`) and stays in the join
+ // filter. Two things then go wrong for a null-aware join: the filter is
+ // right-only, so `push_down_filter` moves it into the subquery and drops the
+ // very NULLs that make `NOT IN` UNKNOWN, and a join without equi-join keys
+ // is planned as a nested loop join, which has no null-aware implementation.
+ // Projecting the constant as a column of the outer side turns the predicate
+ // into a real equi-join key so the null-aware hash join handles it.
+ let mut projected_left = None;
+ if let Some((value, right_col, mut value_name)) = in_value_expr
+ && value.column_refs().is_empty()
+ && matches!(join_type, JoinType::LeftAnti | JoinType::LeftMark)
+ && join_keys_may_be_null(&join_filter, left.schema(), sub_query_alias.schema())?
+ {
+ // The projected column is unqualified, so a left field that already has
+ // this name — however unlikely — would make the reference ambiguous.
+ let left_schema = left.schema();
+ while left_schema.fields().iter().any(|f| f.name() == &value_name) {
+ value_name.push('_');
+ }
+ let value_col = Column::new_unqualified(value_name);
+ let projections = left_schema
+ .columns()
+ .into_iter()
+ .map(Expr::from)
+ .chain(std::iter::once(value.alias(value_col.name())))
+ .collect::<Vec<_>>();
+ projected_left = Some(
+ LogicalPlanBuilder::from(left.clone())
+ .project(projections)?
+ .build()?,
+ );
+ // `in_value_expr` is only set when the `IN` equality is the whole join
+ // filter, so it can simply be rebuilt against the projected column.
+ join_filter = Expr::eq(Expr::Column(value_col), Expr::Column(right_col));
+ }
+ let left = projected_left.as_ref().unwrap_or(left);
+
if matches!(join_type, JoinType::LeftMark | JoinType::RightMark) {
let right_schema = sub_query_alias.schema();
@@ -1413,6 +1462,72 @@ mod tests {
)
}
+ /// A constant value expression has no column, so `Int32(3) = inner_t.id`
+ /// cannot be an equi-join key on its own. The rule projects the constant as
+ /// a column of the outer side; `ExtractEquijoinPredicate` (not run here)
+ /// then turns the filter into a real key for the null-aware hash join.
+ #[test]
+ fn constant_not_in_subquery_projects_value_as_join_key() -> Result<()> {
+ let outer_scan = nullable_scalar_mark_scan("outer_t")?;
+ let inner_scan = nullable_scalar_mark_scan("inner_t")?;
+
+ let subquery = Arc::new(
+ LogicalPlanBuilder::from(inner_scan)
+ .project(vec![col("inner_t.id")])?
+ .build()?,
+ );
+
+ let plan = LogicalPlanBuilder::from(outer_scan)
+ .filter(not_in_subquery(lit(3i32), subquery))?
+ .build()?;
+
+ assert_optimized_plan_equal!(
+ plan,
+ @r"
+ Projection: outer_t.id, outer_t.grp [id:Int32;N, grp:Int32;N]
+ LeftAnti Join: Filter: __correlated_sq_1_value = __correlated_sq_1.id null_aware [id:Int32;N, grp:Int32;N, __correlated_sq_1_value:Int32]
+ Projection: outer_t.id, outer_t.grp, Int32(3) AS __correlated_sq_1_value [id:Int32;N, grp:Int32;N, __correlated_sq_1_value:Int32]
+ TableScan: outer_t [id:Int32;N, grp:Int32;N]
+ SubqueryAlias: __correlated_sq_1 [id:Int32;N]
+ Projection: inner_t.id [id:Int32;N]
+ TableScan: inner_t [id:Int32;N, grp:Int32;N]
+ "
+ )
+ }
+
+ /// The same rewrite must not fire for a correlated subquery: the
+ /// correlation predicate is a second equi-join key, and null-aware hash
+ /// joins accept only one.
+ #[test]
+ fn constant_not_in_correlated_subquery_is_not_rewritten() -> Result<()> {
+ let outer_scan = nullable_scalar_mark_scan("outer_t")?;
+ let inner_scan = nullable_scalar_mark_scan("inner_t")?;
+
+ let subquery = Arc::new(
+ LogicalPlanBuilder::from(inner_scan)
+ .filter(
+ out_ref_col(DataType::Int32, "outer_t.grp").eq(col("inner_t.grp")),
+ )?
+ .project(vec![col("inner_t.id")])?
+ .build()?,
+ );
+
+ let plan = LogicalPlanBuilder::from(outer_scan)
+ .filter(not_in_subquery(lit(3i32), subquery))?
+ .build()?;
+
+ assert_optimized_plan_equal!(
+ plan,
+ @r"
+ LeftAnti Join: Filter: Int32(3) = __correlated_sq_1.id AND outer_t.grp = __correlated_sq_1.grp null_aware [id:Int32;N, grp:Int32;N]
+ TableScan: outer_t [id:Int32;N, grp:Int32;N]
+ SubqueryAlias: __correlated_sq_1 [id:Int32;N, grp:Int32;N]
+ Projection: inner_t.id, inner_t.grp [id:Int32;N, grp:Int32;N]
+ TableScan: inner_t [id:Int32;N, grp:Int32;N]
+ "
+ )
+ }
+
#[test]
fn correlated_not_in_mark_join_is_null_aware_for_hashable_filter() -> Result<()> {
let outer_scan = nullable_scalar_mark_scan("outer_t")?;
diff --git a/datafusion/optimizer/src/push_down_filter.rs b/datafusion/optimizer/src/push_down_filter.rs
index fdbc3275c4..b34827a87b 100644
--- a/datafusion/optimizer/src/push_down_filter.rs
+++ b/datafusion/optimizer/src/push_down_filter.rs
@@ -406,7 +406,25 @@ fn push_down_all_join(
) -> Result<Transformed<LogicalPlan>> {
let is_inner_join = join.join_type == JoinType::Inner;
// Get pushable predicates from current optimizer state
- let (left_preserved, right_preserved) = lr_is_preserved(join.join_type);
+ let (left_preserved, mut right_preserved) = lr_is_preserved(join.join_type);
+ let (on_left_preserved, mut on_right_preserved) = on_lr_is_preserved(join.join_type);
+
+ // Null-aware joins (e.g. `NOT IN` with a nullable subquery) implement SQL
+ // three-valued logic: a NULL join key on the right/subquery side makes the
+ // predicate UNKNOWN and empties the result. Anything pushed into the right
+ // input runs before the join can observe those NULLs, so a null-rejecting
+ // predicate would drop them and silently produce wrong results.
+ // `infer_join_predicates` skips null-aware joins for the same reason.
+ //
+ // `on_right_preserved` is what actually matters here: it is what lets a
+ // right-only join filter — the shape `<constant> NOT IN (<subquery>)`
+ // produces — reach the subquery. `right_preserved` is already false for
+ // every join type that can carry `null_aware` today, so clearing it is
+ // defence in depth.
+ if join.null_aware {
+ right_preserved = false;
+ on_right_preserved = false;
+ }
// The predicates can be divided to three categories:
// 1) can push through join to its children(left or right)
@@ -447,7 +465,6 @@ fn push_down_all_join(
}
let mut on_filter_join_conditions = vec![];
- let (on_left_preserved, on_right_preserved) = on_lr_is_preserved(join.join_type);
for on in on_filter {
if on_left_preserved && checker.is_left_only(&on) {
left_push.push(on)
@@ -3891,6 +3908,46 @@ mod tests {
)
}
+ /// Regression test for a null-aware LeftAnti join whose join filter only
+ /// references the subquery side, the shape produced by
+ /// `<constant> NOT IN (<subquery>)`. Pushing that filter into the right
+ /// input would drop the subquery's NULL rows before the join can observe
+ /// them, so `NOT IN` would wrongly evaluate to TRUE instead of UNKNOWN.
+ #[test]
+ fn null_aware_left_anti_join_keeps_right_only_join_filter() -> Result<()> {
+ let table_scan = test_table_scan_with_name("test1")?;
+ let left = LogicalPlanBuilder::from(table_scan)
+ .project(vec![col("a"), col("b")])?
+ .build()?;
+ let right_table_scan = test_table_scan_with_name("test2")?;
+ let right = LogicalPlanBuilder::from(right_table_scan)
+ .project(vec![col("a"), col("b")])?
+ .build()?;
+ let plan = LogicalPlanBuilder::from(left)
+ .join_detailed_with_options(
+ right,
+ JoinType::LeftAnti,
+ (Vec::<Column>::new(), Vec::<Column>::new()),
+ Some(lit(3u32).eq(col("test2.a"))),
+ datafusion_common::NullEquality::NullEqualsNothing,
+ true,
+ )?
+ .build()?;
+
+ // `UInt32(3) = test2.a` stays on the join: it must not become a
+ // `TableScan: test2, full_filters=[...]`.
+ assert_optimized_plan_equal!(
+ plan,
+ @r"
+ LeftAnti Join: Filter: UInt32(3) = test2.a null_aware
+ Projection: test1.a, test1.b
+ TableScan: test1
+ Projection: test2.a, test2.b
+ TableScan: test2
+ "
+ )
+ }
+
#[test]
fn left_anti_join_with_filters() -> Result<()> {
let table_scan = test_table_scan_with_name("test1")?;
diff --git a/datafusion/sqllogictest/test_files/null_aware_anti_join.slt b/datafusion/sqllogictest/test_files/null_aware_anti_join.slt
index 709332f0ac..8023684ac3 100644
--- a/datafusion/sqllogictest/test_files/null_aware_anti_join.slt
+++ b/datafusion/sqllogictest/test_files/null_aware_anti_join.slt
@@ -564,3 +564,105 @@ DROP TABLE nai_outer;
statement ok
DROP TABLE nai_inner;
+
+#############
+## Regression: constant (non-column) value expression in `NOT IN (subquery)`
+##
+## `3 NOT IN (1, NULL)` is UNKNOWN, so a `WHERE` clause must remove every row.
+## The value expression `3` contains no column, so on its own it cannot be an
+## equi-join key; decorrelation projects it as a column of the outer side so
+## the null-aware hash join gets a real key. Otherwise the predicate stays in
+## the join filter, where it is pushed into the subquery (dropping the NULL
+## rows before the join can observe them) and leaves a keyless join that no
+## null-aware operator can execute.
+#############
+
+statement ok
+CREATE TABLE naconst_t1(id INT) AS VALUES (1), (2);
+
+statement ok
+CREATE TABLE naconst_t2(id INT) AS VALUES (1), (NULL);
+
+# Q1: expected no rows (subquery yields {1, NULL} => `3 NOT IN {1, NULL}` is UNKNOWN)
+query I
+SELECT id FROM naconst_t1 WHERE 3 NOT IN (SELECT id FROM naconst_t2) ORDER BY id;
+----
+
+# Q2: `NOT (x IN (...))` is the same predicate spelled differently
+query I
+SELECT id FROM naconst_t1 WHERE NOT (3 IN (SELECT id FROM naconst_t2)) ORDER BY id;
+----
+
+# C1: the constant is present in the subquery => FALSE, no rows (already correct)
+query I
+SELECT id FROM naconst_t1 WHERE 1 NOT IN (SELECT id FROM naconst_t2) ORDER BY id;
+----
+
+# C2: no NULL in the subquery => plain TRUE, all rows survive
+query I
+SELECT id FROM naconst_t1 WHERE 3 NOT IN (SELECT id FROM naconst_t2 WHERE id IS NOT NULL) ORDER BY id;
+----
+1
+2
+
+# C3: column value expression (an equi-join key exists) => already correct
+query I
+SELECT id FROM naconst_t1 WHERE id + 2 NOT IN (SELECT id FROM naconst_t2) ORDER BY id;
+----
+
+# A constant that is absent from a NULL-free subquery still yields TRUE even
+# when other outer predicates are present.
+query I
+SELECT id FROM naconst_t1
+WHERE id > 1 AND 3 NOT IN (SELECT id FROM naconst_t2 WHERE id IS NOT NULL)
+ORDER BY id;
+----
+2
+
+# The projected value column is named after the subquery alias. A user column
+# that happens to share that name must not make the reference ambiguous.
+statement ok
+CREATE TABLE naconst_clash("__correlated_sq_1_value" INT) AS VALUES (1), (2);
+
+query I
+SELECT "__correlated_sq_1_value" FROM naconst_clash
+WHERE 3 NOT IN (SELECT id FROM naconst_t2)
+ORDER BY 1;
+----
+
+query I
+SELECT "__correlated_sq_1_value" FROM naconst_clash
+WHERE 3 NOT IN (SELECT id FROM naconst_t2 WHERE id IS NOT NULL)
+ORDER BY 1;
+----
+1
+2
+
+statement ok
+DROP TABLE naconst_clash;
+
+# A constant value expression with a non-equality correlation leaves the
+# null-aware join without any equi-join key. Only `HashJoinExec` implements
+# null-aware semantics and it needs a key, so the planner reports the gap
+# instead of falling back to a nested loop join that ignores the NULLs and
+# silently returns wrong results.
+statement ok
+CREATE TABLE naconst_corr_t1(id INT, g INT) AS VALUES (1, 1), (2, 2);
+
+statement ok
+CREATE TABLE naconst_corr_t2(id INT, g INT) AS VALUES (1, 1), (NULL, 2);
+
+query error DataFusion error: Error during planning: null_aware LeftAnti join requires equi\-join keys, but the join has none
+SELECT id FROM naconst_corr_t1 WHERE 3 NOT IN (SELECT id FROM naconst_corr_t2 WHERE naconst_corr_t2.g > naconst_corr_t1.g);
+
+statement ok
+DROP TABLE naconst_corr_t1;
+
+statement ok
+DROP TABLE naconst_corr_t2;
+
+statement ok
+DROP TABLE naconst_t1;
+
+statement ok
+DROP TABLE naconst_t2;
diff --git a/datafusion/sqllogictest/test_files/null_aware_mark_join.slt b/datafusion/sqllogictest/test_files/null_aware_mark_join.slt
index 9d606bbe9b..dfaa4f23cb 100644
--- a/datafusion/sqllogictest/test_files/null_aware_mark_join.slt
+++ b/datafusion/sqllogictest/test_files/null_aware_mark_join.slt
@@ -571,3 +571,69 @@ DROP TABLE outer_corr_table;
statement ok
DROP TABLE outer_table;
+
+#############
+## Regression: constant (non-column) value expression in `NOT IN (subquery)`
+##
+## When the `NOT IN` predicate is not the whole `WHERE` clause, decorrelation
+## produces a mark join instead of an anti join. `3 NOT IN (1, NULL)` is
+## UNKNOWN, so the mark column must be NULL for every outer row: `NOT mark` is
+## then UNKNOWN and the row only survives via the other disjunct.
+#############
+
+statement ok
+CREATE TABLE nmconst_t1(id INT) AS VALUES (1), (2);
+
+statement ok
+CREATE TABLE nmconst_t2(id INT) AS VALUES (1), (NULL);
+
+# Q3: `NOT mark` is UNKNOWN for both rows, so only `id = 1` passes.
+query I
+SELECT id FROM nmconst_t1 WHERE 3 NOT IN (SELECT id FROM nmconst_t2) OR id = 1 ORDER BY id;
+----
+1
+
+# Q4: the predicate is UNKNOWN for every row, so `IS NULL` is TRUE for both.
+query I
+SELECT id FROM nmconst_t1 WHERE (3 NOT IN (SELECT id FROM nmconst_t2)) IS NULL ORDER BY id;
+----
+1
+2
+
+# Control: no NULL in the subquery => `3 NOT IN {1}` is TRUE, never UNKNOWN.
+query I
+SELECT id FROM nmconst_t1
+WHERE (3 NOT IN (SELECT id FROM nmconst_t2 WHERE id IS NOT NULL)) IS NULL
+ORDER BY id;
+----
+
+query I
+SELECT id FROM nmconst_t1
+WHERE 3 NOT IN (SELECT id FROM nmconst_t2 WHERE id IS NOT NULL) OR id = 1
+ORDER BY id;
+----
+1
+2
+
+# Control: the constant matches a subquery row => FALSE (not UNKNOWN).
+query I
+SELECT id FROM nmconst_t1 WHERE (1 NOT IN (SELECT id FROM nmconst_t2)) IS NULL ORDER BY id;
+----
+
+query I
+SELECT id FROM nmconst_t1 WHERE 1 NOT IN (SELECT id FROM nmconst_t2) OR id = 1 ORDER BY id;
+----
+1
+
+# Positive `IN` form: `3 IN (1, NULL)` is UNKNOWN, so the mark is NULL.
+query I
+SELECT id FROM nmconst_t1 WHERE (3 IN (SELECT id FROM nmconst_t2)) IS NULL ORDER BY id;
+----
+1
+2
+
+statement ok
+DROP TABLE nmconst_t1;
+
+statement ok
+DROP TABLE nmconst_t2;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org