Yuan Yusi created SPARK-59126:
---------------------------------
Summary: AQE adaptive join conversion drops a final ORDER BY
Key: SPARK-59126
URL: https://issues.apache.org/jira/browse/SPARK-59126
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.2.0
Reporter: Yuan Yusi
h1. AQE adaptive join conversion drops a final ORDER BY
h2. What is the issue?
On Apache Spark 4.2.0, adaptive execution changes the result order despite an
explicit final global {{{}ORDER BY{}}}. With AQE enabled,
sort-merge-to-broadcast join conversion returns the two rows reversed,
violating the SQL ordering contract.
h2. Reproduce query
{code:sql}
WITH top AS (
SELECT id, CAST(id % 1 AS INT) AS k FROM range(0, 3, 1, 2)
ORDER BY id ASC LIMIT 1 OFFSET 1
), b AS (
SELECT id AS bid, CAST(id % 1 AS INT) AS k FROM range(0, 2, 1, 2)
)
SELECT top.id, top.k, b.bid FROM top LEFT JOIN b USING (k)
ORDER BY top.id ASC, b.bid ASC
{code}
h2. Reproduction
Enable AQE in Spark, i.e.
{code:sql}
spark.conf.set("spark.sql.adaptive.enabled", "true")
{code}
Then run the SQL query above.
Record the observed result row order and compare it to the expected row order.
h2. Test oracle
Compare ordered row sequences, preserving duplicates. AQE-on must equal AQE-off
as a sequence.
h2. Expected behavior
with AQE off
{noformat}
(1, 0, 0)
(1, 0, 1)
{noformat}
h2. Actual behavior
AQE on: wrong output row order
{noformat}
(1, 0, 0)
(1, 0, 1)
{noformat}
AQE on, but no BroadcastJoin, i.e.
{code:sql}
spark.sql.adaptive.autoBroadcastJoinThreshold = -1
{code}
Correct output row order
{noformat}
(1, 0, 0)
(1, 0, 1)
{noformat}
h2. Plan evidence
The non-adaptive plan has a final global sort. The failing adaptive final plan
is a {{ResultQueryStage}} containing {{BroadcastHashJoin}} with no final global
{{{}Sort{}}}. Disabling adaptive broadcast conversion restores the sort and
correct order.
h2. Suspected root cause
Adaptive sort-merge-to-broadcast conversion fails to preserve the root global
ordering requirement. The replacement broadcast hash join is treated as
satisfying output distribution, so the final sort is removed; hash-table
iteration then exposes right-side rows in reverse insertion order. The
no-broadcast control isolates this conversion path.
h2. Environment
Apache Spark 4.2.0, PySpark 4.2.0, Java 17, macOS, {{{}local[2]{}}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
