amitvijapur opened a new pull request, #25630:
URL: https://github.com/apache/datafusion/pull/25630
## Which issue does this PR close?
- Closes #25480.
## Rationale for this change
`x NOT IN (SELECT y FROM inner WHERE y = x)` returns no rows when `inner.y`
holds a NULL. `PullUpCorrelatedExpr` removes the correlation filter because it
duplicates the `IN` predicate, and `build_join` then plans the uncorrelated `x
NOT IN (SELECT y FROM inner)` as a null-aware join, which is UNKNOWN for every
row once the subquery holds a NULL.
The removal is right for `IN`, but the filter `y = x` still constrains the
rows that reach the subquery output: a NULL `y` never passes it, so the result
is either `{x}` or empty and `NOT IN` over it is never UNKNOWN. It is FALSE
when `x` is in `inner.y` and TRUE otherwise, including for a NULL `x`, whose
subquery is empty. A plain anti or mark join with `NullEqualsNothing` computes
exactly that.
## What changes are included in this PR?
`PullUpCorrelatedExpr` records when `remove_duplicated_filter` removed a
conjunct, and `build_join` skips the null-aware plan in that case for both the
`LeftAnti` (WHERE) and `LeftMark` (OR / IS NULL / projected) paths. The join
keeps the `IN` equality as its key, so the shape is the same hash join as today
without the `null_aware` flag.
The analyzer only allows outer references on the non-null-extended input of
a join inside a subquery, and correlated `UNION ALL` and `LIMIT` shapes are not
decorrelated at all, so the pulled-up filter always applies to the rows that
reach the subquery output.
## Are these changes tested?
New blocks in `null_aware_anti_join.slt` and `null_aware_mark_join.slt`
cover the issue's Q1 to Q5, the swapped operand order, an extra residual
correlation in both directions, a value expression on both sides, and `EXPLAIN`
for the anti and mark plans. All twelve expected results were checked against
DuckDB 1.5.5. Nine of them fail on `main` with the wrong results from the
issue; the other three (`IN`, the projected form, and `IS FALSE`) already
passed and guard against regressions. No existing `EXPLAIN` snapshot changed in
`subquery.slt`, `joins.slt`, `mark_join_matrix.slt` or
`subquery_projection.slt`, and `cargo test -p datafusion-optimizer` passes.
The `#25336` cases pinned in the same files are a different shape (a
non-equality correlation that stays as a residual filter) and are unchanged;
#25339 addresses those.
## Are there any user-facing changes?
No API change. Correlated `NOT IN` whose correlation is the `IN` equality
now returns the correct rows.
Written with AI assistance (Claude Code); I traced the removal in
`PullUpCorrelatedExpr`, checked the analyzer invariant and the UNION / LIMIT
shapes myself, and verified the expected results against DuckDB.
--
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]
