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:17 UTC
(datafusion) branch gh-readonly-queue/main/pr-25348-c4f5a9e0f202af0231287ec77774b2e7601e018d created (now f48b5d135d)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a change to branch gh-readonly-queue/main/pr-25348-c4f5a9e0f202af0231287ec77774b2e7601e018d
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at f48b5d135d fix: `NOT IN (subquery)` with a constant value ignores NULLs in the subquery (#25348)
This branch includes the following new commits:
new f48b5d135d fix: `NOT IN (subquery)` with a constant value ignores NULLs in the subquery (#25348)
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
(datafusion) 01/01: fix: `NOT IN (subquery)` with a constant value ignores NULLs in the subquery (#25348)
Posted by gi...@apache.org.
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