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/07/26 00:06:45 UTC
(datafusion) branch gh-readonly-queue/main/pr-23901-6d764823764bb4063d95c82ce0dac385a6cb1fc5 created (now 0fcf6284fd)
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-23901-6d764823764bb4063d95c82ce0dac385a6cb1fc5
in repository https://gitbox.apache.org/repos/asf/datafusion.git
at 0fcf6284fd fix: don't infer join predicates for null-aware joins in push_down_filter (#23901)
This branch includes the following new commits:
new 0fcf6284fd fix: don't infer join predicates for null-aware joins in push_down_filter (#23901)
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: don't infer join predicates for null-aware joins in push_down_filter (#23901)
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-23901-6d764823764bb4063d95c82ce0dac385a6cb1fc5
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 0fcf6284fd81deddf77b04b818e26f8fa6023bd3
Author: Liang-Chi Hsieh <vi...@gmail.com>
AuthorDate: Sun Jul 26 08:06:23 2026 +0800
fix: don't infer join predicates for null-aware joins in push_down_filter (#23901)
## Which issue does this PR close?
- Closes #23900.
## Rationale for this change
`push_down_filter` infers equi-key predicates across a join's ON keys
and pushes them to the opposite side. For a null-aware join (the
`LeftAnti` join produced by `NOT IN` with a nullable subquery), an outer
predicate on the left key like `outer.id > 5` is rewritten to `sub.id >
5` and pushed onto the subquery input. Since the inferred predicate must
be null-rejecting to be pushed, this drops the subquery's NULL rows and
breaks the three-valued `NOT IN` semantics — a NULL in the subquery key
must reach the join so the result is empty.
Same class of bug as #23848, in a different rule.
## What changes are included in this PR?
- Skip predicate inference in `infer_join_predicates` when
`join.null_aware` is set (mirrors the #23848 guard on
`FilterNullJoinKeys`).
- A `push_down_filter` unit test asserting no predicate is inferred onto
the subquery side of a null-aware `LeftAnti` join.
- SLT coverage for the failing query, plus a `prefer_hash_join = false`
/ multi-partition variant.
## Are these changes tested?
Yes — new unit test (verified it fails without the guard) and SLT cases.
The full optimizer lib suite passes.
## Are there any user-facing changes?
No, aside from the correctness fix.
---
datafusion/optimizer/src/push_down_filter.rs | 56 ++++++++++++++++++++++
.../test_files/null_aware_anti_join.slt | 45 +++++++++++++++++
2 files changed, 101 insertions(+)
diff --git a/datafusion/optimizer/src/push_down_filter.rs b/datafusion/optimizer/src/push_down_filter.rs
index f30b1187b7..cf54ae2547 100644
--- a/datafusion/optimizer/src/push_down_filter.rs
+++ b/datafusion/optimizer/src/push_down_filter.rs
@@ -576,6 +576,17 @@ fn infer_join_predicates(
predicates: &[Expr],
on_filters: &[Expr],
) -> Result<Vec<Expr>> {
+ // Null-aware joins (e.g. `NOT IN` with a nullable subquery) rely on SQL
+ // three-valued logic: a NULL join key on the right/subquery side makes the
+ // predicate UNKNOWN and empties the result, so those NULLs must reach the
+ // join. Inferring an equi-key predicate here would rewrite a left-side
+ // predicate onto the right side and, because the inferred predicate must be
+ // null-rejecting, drop the subquery's NULL rows and produce wrong results.
+ // Skip inference entirely for null-aware joins.
+ if join.null_aware {
+ return Ok(vec![]);
+ }
+
// Only allow both side key is column.
let join_col_keys = join
.on
@@ -3826,6 +3837,51 @@ mod tests {
)
}
+ /// Regression test: for a null-aware LeftAnti join (the shape produced by
+ /// `NOT IN` with a nullable subquery), a right-side predicate must NOT be
+ /// inferred onto the join. Inference would push a null-rejecting predicate
+ /// to the subquery side, dropping its NULL rows and breaking the
+ /// three-valued `NOT IN` semantics.
+ #[test]
+ fn null_aware_left_anti_join_no_inferred_pushdown() -> 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::from_qualified_name("test1.a")],
+ vec![Column::from_qualified_name("test2.a")],
+ ),
+ None,
+ datafusion_common::NullEquality::NullEqualsNothing,
+ true,
+ )?
+ .filter(col("test1.a").gt(lit(2u32)))?
+ .build()?;
+
+ // The left-side filter is pushed to the left input, but — unlike the
+ // non-null-aware `left_anti_join` test — no `test2.a > 2` predicate is
+ // inferred onto the right/subquery side.
+ assert_optimized_plan_equal!(
+ plan,
+ @r"
+ LeftAnti Join: test1.a = test2.a null_aware
+ Projection: test1.a, test1.b
+ TableScan: test1, full_filters=[test1.a > UInt32(2)]
+ 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 6061458b13..bdb56cf220 100644
--- a/datafusion/sqllogictest/test_files/null_aware_anti_join.slt
+++ b/datafusion/sqllogictest/test_files/null_aware_anti_join.slt
@@ -530,3 +530,48 @@ RESET datafusion.execution.parquet.pushdown_filters;
statement ok
RESET datafusion.optimizer.enable_join_dynamic_filter_pushdown;
+
+#############
+## Regression: null-aware NOT IN with an outer predicate on the join key
+##
+## `push_down_filter` used to infer the outer predicate `id > 5` onto the
+## subquery side (as `eid > 5`), dropping the subquery's NULL row and wrongly
+## returning outer rows. The subquery NULL must reach the join so that
+## `NOT IN` stays UNKNOWN for every row.
+#############
+
+statement ok
+CREATE TABLE nai_outer(id INT) AS VALUES (3), (7);
+
+statement ok
+CREATE TABLE nai_inner(id INT) AS VALUES (NULL);
+
+# Expected: zero rows (subquery contains NULL => NOT IN is UNKNOWN for all).
+query I
+SELECT id FROM nai_outer WHERE id > 5 AND id NOT IN (SELECT id FROM nai_inner) ORDER BY id;
+----
+
+# Same query under SortMergeJoin + multiple partitions: null-aware joins must
+# be planned as a CollectLeft HashJoin, not a plain anti SortMergeJoin.
+statement ok
+SET datafusion.optimizer.prefer_hash_join = false;
+
+statement ok
+SET datafusion.execution.target_partitions = 4;
+
+query I
+SELECT id FROM nai_outer WHERE id NOT IN (SELECT id FROM nai_inner) ORDER BY id;
+----
+
+statement ok
+SET datafusion.optimizer.prefer_hash_join = true;
+
+# The SLT runner sets target_partitions to 4, so restore that value explicitly.
+statement ok
+SET datafusion.execution.target_partitions = 4;
+
+statement ok
+DROP TABLE nai_outer;
+
+statement ok
+DROP TABLE nai_inner;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org