jayzhan211 opened a new issue, #27215:
URL: https://github.com/apache/datafusion/issues/27215
### Describe the bug
An inner join whose `ON` is only `IS NOT DISTINCT FROM` keys, with an
ordinary `=` between its two inputs written in an outer `WHERE` (above a
subquery, view or CTE), returns rows where that `=` compares `NULL` to `NULL`.
These rows should be filtered out, because `NULL = NULL` is not true.
### To Reproduce
```sql
CREATE TABLE t1(a INT, b INT) AS VALUES (NULL, 1), (5, 2), (6, NULL);
CREATE TABLE t2(a INT, b INT) AS VALUES (NULL, 1), (5, 2), (6, NULL);
-- Query 1
SELECT *
FROM (SELECT t1.a a1, t1.b b1, t2.b b2
FROM t1 JOIN t2 ON t1.a IS NOT DISTINCT FROM t2.a) s
WHERE s.b1 = s.b2
ORDER BY a1;
```
### Expected behavior
| Query | DataFusion returns | Expected |
|---|---|---|
| 1 | `(5, 2, 2)`, `(6, NULL, NULL)`, `(NULL, 1, 1)` | `(5, 2, 2)`, `(NULL,
1, 1)` (`b1 = b2` is NULL for the `a1 = 6` row) |
DuckDB returns the expected rows.
### Additional context
The physical plan is a single `HashJoinExec` with `NullsEqual: true, on: (a
= a), (b = b)`, so `b = b` matches NULLs.
`ExtractEquijoinPredicate` runs twice:
1. The first pass sees only `t1.a IS NOT DISTINCT FROM t2.a`, so it moves it
to `on` and sets `NullEqualsNull`
([`extract_equijoin_predicate.rs:78-104`](https://github.com/apache/datafusion/blob/3f4a37fbabdcb07705db6341268e6c0b34da0d34/datafusion/optimizer/src/extract_equijoin_predicate.rs#L78-L104)).
2. `PushDownFilter` moves `t2.b = t1.b` from the `WHERE` into the join's
filter.
3. The second pass finds that `=` key and appends it to the existing `on`,
keeping the join's `NullEqualsNull`
([L111-L121](https://github.com/apache/datafusion/blob/3f4a37fbabdcb07705db6341268e6c0b34da0d34/datafusion/optimizer/src/extract_equijoin_predicate.rs#L111-L121)).
`EXPLAIN VERBOSE` shows the `on` growing from `t1.a = t2.a` to `t1.a = t2.a,
t1.b = t2.b` in the second `extract_equijoin_predicate` step.
Without the subquery, the `WHERE` sits directly on the join and
`EliminateCrossJoin` rebuilds it instead. That path also returns the `NULL` row
on main; the open PR #27066 fixes it there but does not change Query 1.
Fix direction: a join holds one `NullEquality` for all its keys, so `=` keys
should only be added to `on` when the join is `NullEqualsNothing`. Otherwise
they stay in the filter (or the existing NULL-safe keys move to the filter as
`IS NOT DISTINCT FROM`).
### Willingness to contribute
I'm not planning to fix this myself, so anyone is welcome to pick it up.
Feel free to ping me with questions about the cause or when you'd like a review.
--
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]
