txwyy123 opened a new pull request, #59529:
URL: https://github.com/apache/spark/pull/59529
### What changes were proposed in this pull request?
`PushProjectionThroughLimitAndOffset` pushes a `Project` below
`Limit`/`Offset` when all project expressions are deterministic. However,
subquery expressions (scalar subquery, EXISTS, IN) are deterministic but get
rewritten into joins by later optimizer rules. A join placed below a Limit can
destroy the sort order that the Limit depends on, returning wrong rows.
This patch adds a `SubqueryExpression.hasSubquery` guard to the three match
cases so that any `Project` containing a subquery expression is not pushed
below `Limit` or `Offset`.
### Why are the changes needed?
The query below returns wrong results because the scalar subquery gets
pushed below the Limit, then rewritten into a join that repartitions inputs and
destroys the sort order:
```sql
WITH s AS (SELECT id, name FROM p ORDER BY age DESC LIMIT 3)
SELECT name, (SELECT count(*) FROM k WHERE k.a = id) AS c FROM s;
```
This is especially visible when broadcast join is disabled (forcing
SortMergeJoin), but can also surface with BroadcastHashJoin on larger tables
where the build side doesn't fit in broadcast threshold.
### Does this PR introduce _any_ user-facing change?
Yes. Queries that combine sorted LIMIT/OFFSET with scalar subqueries,
EXISTS, or IN subqueries in the SELECT list now return correct results.
### How was this patch tested?
1. **Unit tests** in `PushProjectionThroughLimitAndOffsetSuite`: verify the
optimized plan shape — Project with subquery stays above Limit/Offset, while
simple deterministic projections are still pushed (regression guard).
2. **End-to-end SQL tests** in `SubquerySuite`:
- Scalar subquery + sorted LIMIT (the core repro from the issue)
- Scalar subquery + OFFSET
- EXISTS + sorted LIMIT
- Forced SortMergeJoin (`autoBroadcastJoinThreshold=-1`) to ensure the
fix works even when BHJ can't mask the bug
### Was this patch authored or co-authored using generative AI tooling?
Yes.
--
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]
