adriangb opened a new pull request, #25348:
URL: https://github.com/apache/datafusion/pull/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
--
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]
