[
https://issues.apache.org/jira/browse/SPARK-60319?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-60319:
-----------------------------------
Labels: pull-request-available (was: )
> Wrong rows when projecting subqueries over sorted LIMIT or OFFSET
> -----------------------------------------------------------------
>
> Key: SPARK-60319
> URL: https://issues.apache.org/jira/browse/SPARK-60319
> Project: Spark
> Issue Type: Bug
> Components: Optimizer, SQL
> Affects Versions: 4.1.3
> Reporter: Metin Hatipogullari
> Priority: Major
> Labels: pull-request-available
>
> h3. Problem
> Spark SQL can silently return incorrect rows when a projection containing a
> correlated scalar, EXISTS or IN subquery is evaluated over a sorted LIMIT or
> OFFSET.
> The optimizer rule {{PushProjectionThroughLimitAndOffset}} can move the
> projection below the LIMIT/OFFSET boundary before the subquery is rewritten
> into a join. The join may change row ordering, causing the row boundary to
> select incorrect rows.
> h3. Reproduction
> Test data:
> {code:sql}
> CREATE OR REPLACE TEMP VIEW p AS
> SELECT * FROM VALUES
> (1, 'a', 30), (2, 'b', 20), (3, 'c', 40),
> (4, 'd', 60), (5, 'e', 10), (6, 'f', 50)
> AS p(id, name, age);
> CREATE OR REPLACE TEMP VIEW k AS
> SELECT * FROM VALUES
> (1, 2), (1, 3), (1, 5), (2, 3)
> AS k(a, b);
> WITH s AS (
> SELECT id, name
> FROM p
> ORDER BY age DESC
> LIMIT 3
> )
> SELECT name,
> (SELECT count(*) FROM k WHERE k.a = s.id) AS c
> FROM s;
> {code}
> Expected rows (order independent):
> {code}
> (c, 0)
> (d, 0)
> (f, 0)
> {code}
> Observed on Spark 4.1.3:
> {code}
> (a, 3)
> (b, 1)
> (c, 0)
> {code}
> The query returns incorrect results without an exception.
> Similar failures were reproduced with OFFSET, EXISTS, IN and correlated
> scalar subqueries under multiple execution configurations.
> h3. Root cause
> The projection pushdown rule originally checked only expression determinism.
> Subquery expressions could pass this check and subsequently be rewritten into
> joins below LIMIT or OFFSET.
> Join execution may repartition rows, invalidating the ordering on which
> LIMIT/OFFSET depends.
> h3. Proposed fix
> Prevent projection pushdown for subqueries that may introduce joins, while
> retaining pushdown for safe uncorrelated scalar and EXISTS subqueries.
> Add deterministic logical-plan regression tests for all three optimizer rule
> branches and retain end-to-end SQL correctness tests.
> h3. References
> Detailed bug report and reproduction:
> https://github.com/apache/spark/issues/59438
> Pull request with proposed fix:
> https://github.com/apache/spark/pull/59445
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
