adriangb commented on PR #25338: URL: https://github.com/apache/datafusion/pull/25338#issuecomment-5744065037
Thank you, this is a real regression and I fixed it in 7bb69a5. All three of your queries now give the base results, and the third one, which is wrong on `main` too, gives the correct three rows. That third case is https://github.com/apache/datafusion/issues/25480. Your diagnosis is right, and I took your `in_predicate_is_correlation` flag. I added one thing to it: the flag on its own is not sufficient, because a node above the dropped filter can put a NULL back into the value column, and the result is UNKNOWN again. Two shapes reach that, and the flag alone turns both from correct into `false`: ```sql -- ROLLUP adds a NULL row for `i.k`, so a miss is UNKNOWN SELECT o.k, o.k IN (SELECT i.k FROM i WHERE i.k = o.k GROUP BY ROLLUP(i.k)) AS m FROM o; -- an unmatched row of `a` gives a NULL `b.y`, so a miss is UNKNOWN SELECT o.k, o.k IN (SELECT b.y FROM a LEFT JOIN (SELECT * FROM b WHERE b.y = o.k) AS b ON a.id = b.id) AS m FROM o; ``` So the flag is cleared again when the pull up passes an outer join, a union or a grouping set. `f_up` walks the subquery bottom up, so every such node above the filter is reached after it: ```rust fn f_up(&mut self, plan: LogicalPlan) -> Result<Transformed<LogicalPlan>> { if self.in_predicate_is_correlation && plan_may_add_null_rows(&plan) { self.in_predicate_is_correlation = false; } ``` Tests, as asked, in `subquery_projection.slt`: your two projection queries, your `WHERE ... NOT IN` query, an `EXPLAIN` guard that shows the join is no longer null-aware, and the `ROLLUP` guard. Two unit tests cover the mark join and the anti join. Every expected value agrees with DuckDB 1.5.2. I also ran a differential fuzz against DuckDB over 18 correlated `IN` / `NOT IN` shapes and 24 random table pairs: 0 mismatches on this branch, and it finds this bug within 3 trials on the merge base. One note on the second shape above. Pulling a correlated filter out from under the nullable side of an outer join is wrong on `main` as well, and the guard only keeps that shape where this branch already had it; it does not fix it. For example with `a(id) = (1),(2)` and `b(id, y) = (1,1),(2,2)`, `o.k = 5` gives `false` on `main` and on this branch, where DuckDB and PostgreSQL give NULL. Filed separately as https://github.com/apache/datafusion/issues/25507; the `EXISTS` form there is wrong too, so it is not a three-valued-logic problem. -- 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]
