vboo123 opened a new pull request, #58186: URL: https://github.com/apache/spark/pull/58186
### What changes were proposed in this pull request? `RewritePredicateSubquery.rewriteExistentialExprWithAttrs` replaced an `IN`/`NOT IN` subquery predicate with a non-nullable `exists` attribute produced by an `ExistenceJoin`, applied via an unrestricted `transformDown` at any depth. Since `exists` is two-valued, the three-valued `IN` result NULL was mapped to FALSE. This restricts the traversal to positions where NULL and FALSE are indistinguishable - the condition root and below `And`/`Or` (an `Alias` only names the result, so it is transparent). In an observable position an uncorrelated `IN`/`NOT IN` is left in place; `PlanSubqueries` plans it as `InSubqueryExec`, which evaluates three-valued `IN` semantics. A correlated subquery is still rewritten, since it requires decorrelation. The `Project` wrapper in the nested-predicate branch is now added only when an existence join was actually introduced. It exists solely to prune the join's `exists` attribute, and re-adding it while leaving the subquery in place made the rule rewrite its own output indefinitely. This mirrors the position-restricted recursion in `ReplaceNullWithFalseInPredicate`, the same approach used to fix the sibling bug SPARK-58384 (#57791). ### Why are the changes needed? Wrong results. With `tn(c)` containing `(1), (2), (NULL)` and `t(c)` containing a NULL: ```sql SELECT c FROM tn WHERE (c IN (SELECT c FROM t)) IS NULL; ``` `IN` evaluates to NULL for every row, so `IS NULL` is TRUE and all rows should be returned. Before this change the rewrite collapsed NULL to FALSE, `NullPropagation` then folded `isnull(exists)` to `false`, and the query returned no rows. The collapse is sound at a condition root and under `AND`/`OR`, since NULL and FALSE both reject the row, which is why this went unnoticed. It is observable under `IS NULL`, `IS NOT NULL` and `<=>`. SPARK-43413 fixed the related nullability metadata on `ListQuery` and explicitly noted this rewrite as a separate remaining bug. Two limitations are intentionally left out of scope: value positions such as `SELECT c IN (...)` (below an `Alias`) still collapse, and a correlated subquery in an observable position keeps the current behaviour because it must be decorrelated into a join. ### Does this PR introduce _any_ user-facing change? Yes. Queries with an `IN`/`NOT IN` subquery under a null-observing operator now return correct results instead of silently wrong ones. Plans for `IN` at a condition root or under `AND`/`OR` are unchanged. ### How was this patch tested? New tests: - `RewriteSubquerySuite`: an `IN` subquery under `IS NULL`, `NOT(IS NULL)` and `<=>` is not rewritten to a join; and it is still rewritten to an `ExistenceJoin` under `OR`. - `SubquerySuite`: end-to-end results for the reported repro plus the `NOT IN`, `<=>` and `OR` variants. ### Was this patch authored or co-authored using generative AI tooling? Generated using Kiro (Claude Opus 4.8) -- 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]
