viirya opened a new issue, #23900: URL: https://github.com/apache/datafusion/issues/23900
### Describe the bug `push_down_filter` infers equi-key predicates across a join's ON keys and pushes them to the opposite input. For a **null-aware** join — the `LeftAnti` join produced by `NOT IN` with a nullable subquery — an outer predicate on the left join key is rewritten onto the right/subquery side. Because an inferred predicate must be null-rejecting to be pushed through a non-inner join, this drops the subquery's NULL rows, which breaks the three-valued `NOT IN` semantics: a NULL in the subquery key must reach the join so the result is empty (`NOT IN` is UNKNOWN for every outer row). This is the same class of bug as #23847 / #23848 (`FilterNullJoinKeys`), in a different rule. ### To Reproduce ```sql CREATE TABLE outer_t(id INT) AS VALUES (3), (7); CREATE TABLE sub_t(id INT) AS VALUES (NULL); SELECT id FROM outer_t WHERE id > 5 AND id NOT IN (SELECT id FROM sub_t); ``` ### Expected behavior **0 rows.** The subquery contains a NULL, so `id NOT IN (SELECT id FROM sub_t)` evaluates to UNKNOWN for every row, and `WHERE` keeps only TRUE rows. (Matches PostgreSQL.) ### Actual behavior Returns **`7`**. `push_down_filter` infers `sub_t.id > 5` from the outer predicate `outer_t.id > 5` (via the `outer_t.id = sub_t.id` join key) and pushes it onto the subquery input, dropping its NULL row. The anti-join then sees an empty subquery and keeps `7`. Confirmed by disabling the fix and re-running: the query returns `7` without the guard and `0 rows` with it. ### Additional context The offending transform is in `infer_join_predicates` (`datafusion/optimizer/src/push_down_filter.rs`), which does not check `join.null_aware`. -- 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]
