Wrong results: constant NOT IN (subquery) in a WHERE clause ignores NULLs in the subquery

[adriangb]

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

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:

-- 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), 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. fix: don't infer join predicates for null-aware joins in push_down_filter #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). 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 #25336). Steps 2 and 3 then apply in the same way.

#25339 (the fix for #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.