adriangb opened a new pull request, #25558: URL: https://github.com/apache/datafusion/pull/25558
## Which issue does this PR close? - Part of https://github.com/apache/datafusion/issues/25336. Closes nothing on its own. ## Rationale for this change Correlated `NOT IN` returns wrong results in several shapes, and a few do not plan at all. The fixes span two layers — the hash join executor and decorrelation — and touch different shapes. Landing the coverage first, pinned to what DataFusion does today, makes each fix's diff show exactly which behaviour it changes instead of burying the flips in a large change. Nothing here changes behaviour. Every expectation is what `main` produces right now, and the wrong ones carry a note and a link to the issue. Concretely, this is wrong today (DuckDB 1.5.2 and PostgreSQL 17.11 both return three rows): ```sql CREATE TABLE oc(id INT, g INT) AS VALUES (1,5),(2,5),(3,0),(4,NULL),(NULL,5),(NULL,0); CREATE TABLE ic(id INT) AS VALUES (1),(NULL); SELECT id, g FROM oc WHERE oc.id NOT IN (SELECT ic.id FROM ic WHERE oc.g > 0); -- returns no rows ``` ## What changes are included in this PR? sqllogictest, in `null_aware_anti_join.slt` and `null_aware_mark_join.slt`: - correlated `NOT IN` with a non-equality correlation, which stays a residual join filter; - a correlation naming only outer columns, so it cannot become an equi-join key; - a constant value expression, with and without a correlation; - a subquery inside the `IN` value, both spellings of the outer `IN`; - `IS NOT NULL` over a subquery predicate, and a comparison between two marks — the contexts that can tell a NULL mark from a FALSE mark; - plan pins for the join the planner picks in each case. Benchmarks: - **Q09**, a correlated non-negated `IN`. It must *not* use a null-aware join. Q01–Q08 all cover the direction where null-awareness is required, so a regression that adds it where it is not needed is invisible to them. Q09 passes today. - correctness canaries on Q05–Q08, each comparing the `NOT IN` result against a reference that does not use `NOT IN`. All four disagree today, so they are pinned to `false`. ## What is the testing strategy for this PR? This PR is tests. Expected results were verified against DuckDB 1.5.2 and PostgreSQL 17.11. The whole suite is green on `main`, including all nine benchmarks. ## Are there any user-facing changes? No. 🤖 Generated with [Claude Code](https://claude.com/claude-code) -- 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]
