Metin Hatipogullari created SPARK-60319:
-------------------------------------------
Summary: 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
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
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
