You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by "vboo123 (via GitHub)" <gi...@apache.org> on 2026/08/21 02:09:46 UTC
[PR] [SPARK-58442][SQL] Don't rewrite IN subquery to ExistenceJoin where NULL is observable [spark]
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-58442][SQL] Don't rewrite IN subquery to ExistenceJoin where NULL is observable [spark]
Posted by "uros-b (via GitHub)" <gi...@apache.org>.
uros-b commented on code in PR #58186:
URL: https://github.com/apache/spark/pull/58186#discussion_r3842003915
##########
sql/core/src/main/scala/org/apache/spark/sql/execution/subquery.scala:
##########
@@ -125,7 +125,9 @@ case class InSubqueryExec(
@transient private lazy val inSet = InSet(child, result.toSet)
- override def nullable: Boolean = child.nullable
+ // A NULL in the subquery result makes a non-matching probe evaluate to NULL, so the left side's
+ // nullability alone understates it. See SPARK-58442.
+ override def nullable: Boolean = child.nullable || plan.output.exists(_.nullable)
Review Comment:
For InSubqueryExec.nullable - please be aware of possible conflicts with PR #58077, which modifies the same line with a broader implementation (CreateNamedStruct LHS field nullability + LEGACY_IN_SUBQUERY_NULLABILITY config).
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
Re: [PR] [SPARK-58442][SQL] Don't rewrite IN subquery to ExistenceJoin where NULL is observable [spark]
Posted by "vboo123 (via GitHub)" <gi...@apache.org>.
vboo123 commented on PR #58186:
URL: https://github.com/apache/spark/pull/58186#issuecomment-5373596169
cc @JoshRosen — flagging one deliberate behaviour change for your judgement, since it edits assertions added by another correctness ticket.
Two assertions in `SubquerySuite`, "SPARK-38132: Not IN subquery correctness checks", expected the collapsed result and are updated in this PR. With `c2 = {1, 2, 3, NULL, 0}`:
```sql
SELECT * FROM t WHERE NOT((c1 NOT IN (SELECT c2 FROM t)) <=> false)
```
For `c1 = 4`: `4 IN (...)` has no match and the set contains NULL, so it is NULL; `NOT NULL` is NULL; `NULL <=> false` is FALSE; and `NOT FALSE` is TRUE. So the row should be returned. The same holds for `c1 = NULL`. The previous expectation of no rows only holds if `NOT IN` yields FALSE rather than NULL, which is exactly the collapse this PR removes.
The other six assertions in that test are unaffected: `= true`, `<=> true` and `!= false` all reject NULL and FALSE alike, so only the two using `NOT(... <=> false)` can observe the difference.
Happy to revert these two expectations and scope the fix to plain `IN` only if you would rather not change them here.
--
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: reviews-unsubscribe@spark.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org