adriangb opened a new issue, #25340:
URL: https://github.com/apache/datafusion/issues/25340
### Describe the bug
`<constant> NOT IN (<subquery>)` in a `WHERE` clause gives wrong results
when the subquery result contains NULL.
`3 NOT IN (1, NULL)` is UNKNOWN, so a `WHERE` clause must remove the row.
DataFusion keeps every row. DuckDB 1.5.2 and PostgreSQL 17.11 return no rows.
This is a silent wrong-results bug. There is no error and no warning.
The same expression in a `SELECT` list is correct (it returns `NULL`). The
bug occurs only when the optimizer rewrites the subquery to a join.
Tested on commit `a0631edb77` (`datafusion-cli` 55.1.0, release build).
### To Reproduce
```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;
```
`datafusion-cli`:
```
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 row(s) fetched.
```
Expected: no rows. The subquery result is `{1, NULL}`. `3 NOT IN {1, NULL}`
is UNKNOWN for every row of `t1`.
More queries on the same tables:
```sql
-- Q2
SELECT id FROM t1 WHERE NOT (3 IN (SELECT id FROM t2)) ORDER BY id;
-- Q3
SELECT id FROM t1 WHERE 3 NOT IN (SELECT id FROM t2) OR id = 1 ORDER BY id;
-- Q4
SELECT id FROM t1 WHERE (3 NOT IN (SELECT id FROM t2)) IS NULL ORDER BY id;
-- Q5
SELECT 3 NOT IN (SELECT id FROM t2) AS r;
-- C1 (the constant is in the subquery result)
SELECT id FROM t1 WHERE 1 NOT IN (SELECT id FROM t2) ORDER BY id;
-- C2 (no NULL in the subquery result)
SELECT id FROM t1 WHERE 3 NOT IN (SELECT id FROM t2 WHERE id IS NOT NULL)
ORDER BY id;
-- C3 (the value expression refers to a column)
SELECT id FROM t1 WHERE id + 2 NOT IN (SELECT id FROM t2) ORDER BY id;
```
| Query | DataFusion `a0631edb77` | DuckDB 1.5.2 | PostgreSQL 17.11 |
Correct |
| --- | --- | --- | --- | --- |
| Q1 `WHERE 3 NOT IN (...)` | 1, 2 | (no rows) | (no rows) | (no rows) |
| Q2 `WHERE NOT (3 IN (...))` | 1, 2 | (no rows) | (no rows) | (no rows) |
| Q3 `WHERE 3 NOT IN (...) OR id = 1` | 1, 2 | 1 | 1 | 1 |
| Q4 `WHERE (3 NOT IN (...)) IS NULL` | (no rows) | 1, 2 | 1, 2 | 1, 2 |
| Q5 `SELECT 3 NOT IN (...)` | NULL | NULL | NULL | NULL |
| C1 | (no rows) | (no rows) | (no rows) | (no rows) |
| C2 | 1, 2 | 1, 2 | 1, 2 | 1, 2 |
| C3 | (no rows) | (no rows) | (no rows) | (no rows) |
Q1 to Q4 are wrong. Q5 and the controls are correct.
### Expected behavior
Q1 and Q2 return no rows. Q3 returns `1`. Q4 returns `1` and `2`.
### Additional context
`EXPLAIN` for Q1 (`datafusion.explain.format = 'indent'`):
```
logical_plan
LeftAnti Join: null_aware
TableScan: t1 projection=[id]
SubqueryAlias: __correlated_sq_1
Projection:
Filter: t2.id = Int32(3)
TableScan: t2 projection=[id]
physical_plan
NestedLoopJoinExec: join_type=RightAnti
FilterExec: id@0 = 3, projection=[]
DataSourceExec: partitions=1, partition_sizes=[1]
DataSourceExec: partitions=1, partition_sizes=[1]
```
`EXPLAIN` for Q3:
```
logical_plan
Projection: t1.id
Filter: NOT __correlated_sq_1.mark OR t1.id = Int32(1)
LeftMark Join:
TableScan: t1 projection=[id]
SubqueryAlias: __correlated_sq_1
Projection: CAST(t2.id AS Int64)
Filter: t2.id = Int32(3)
TableScan: t2 projection=[id]
physical_plan
FilterExec: NOT mark@1 OR id@0 = 1, projection=[id@0]
RepartitionExec: partitioning=RoundRobinBatch(12), input_partitions=1
NestedLoopJoinExec: join_type=RightMark
CoalescePartitionsExec
ProjectionExec: expr=[CAST(id@0 AS Int64) as t2.id]
RepartitionExec: partitioning=RoundRobinBatch(12),
input_partitions=1
FilterExec: id@0 = 3
DataSourceExec: partitions=1, partition_sizes=[1]
DataSourceExec: partitions=1, partition_sizes=[1]
```
#### Suspected root cause
1. `DecorrelatePredicateSubquery` rewrites Q1 to `LeftAnti Join: Filter:
Int64(3) = __correlated_sq_1.id null_aware`. The value expression `3` has no
column, so this predicate is not an equi-join key. It stays in the join filter.
2. `push_down_filter` moves that join filter into the subquery side as
`Filter: t2.id = 3`. In `push_down_all_join`
([`push_down_filter.rs`](https://github.com/apache/datafusion/blob/a0631edb77/datafusion/optimizer/src/push_down_filter.rs#L449-L459)),
`on_lr_is_preserved(LeftAnti)` lets a right-only join filter conjunct go to
the right input. That is correct for a normal anti join. For a null-aware join
it is not correct: the pushed filter removes the NULL rows before the join can
see them. https://github.com/apache/datafusion/pull/23901 already stopped
`push_down_filter` from *inferring* predicates for null-aware joins (the
`join.null_aware` check in `infer_join_predicates`), but the pushdown of the
join's own filter has no such check.
3. The join now has no equi-join keys, so the physical planner creates a
`NestedLoopJoinExec`
([`physical_planner.rs`](https://github.com/apache/datafusion/blob/a0631edb77/datafusion/core/src/physical_planner.rs#L1608)).
`NestedLoopJoinExec::try_new` has no `null_aware` parameter, so a null-aware
join without equi-join keys cannot keep `NOT IN` semantics. The flag is dropped
without an error.
For the mark join in Q3 and Q4, step 1 already gives a join that is not
null-aware, because `build_join` only sets `null_aware` on a `LeftMark` join
when the whole join filter is hashable (the same gate as in
https://github.com/apache/datafusion/issues/25336). Steps 2 and 3 then apply in
the same way.
https://github.com/apache/datafusion/pull/25339 (the fix for
https://github.com/apache/datafusion/issues/25336) does not fix this bug. It
removes the `LeftMark` gate, but Q1 to Q4 give the same results on that branch,
because steps 2 and 3 still apply.
#### Fix sketch
- In `push_down_all_join`, do not push join filter conjuncts into the right
input of a `null_aware` join.
- Keep the value predicate as a hash join key when the value expression has
no column, for example by projecting the value expression as a column on the
outer side. Then the existing null-aware hash join handles it.
- In the physical planner, do not silently drop `null_aware`: return an
error instead of a nested loop join that gives wrong results.
--
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]
