Luka Zdravic created SPARK-60261:
------------------------------------
Summary: Sort-merge ASOF join returns the wrong row when the as-of
columns are swapped
Key: SPARK-60261
URL: https://issues.apache.org/jira/browse/SPARK-60261
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.4.0
Reporter: Luka Zdravic
The sort-merge ASOF join (spark.sql.join.sortMergeAsOfJoin.enabled=true)
returns the wrong row when the as-of columns are swapped. Swapped means the
left as-of column comes from the right side, and the right as-of column comes
from the left side.
Repro (Scala, from a test in package org.apache.spark.sql):
{code:scala}
val t1 = Seq((1, 10), (1, 20)).toDF("k", "lt")
val t2 = Seq((1, 5), (1, 12), (1, 15), (1, 25), (1, 30)).toDF("k", "rt")
t1.joinAsOf(t2, t2("rt"), t1("lt"), t1("k") === t2("k"), "left",
null, allowExactMatches = true, direction = "backward")
.select("lt", "rt").orderBy("lt")
{code}
Results:
* sortMergeAsOfJoin.enabled=false (default): [10,12], [20,25]. This is the
closest row.
* sortMergeAsOfJoin.enabled=true: [10,30], [20,30]. This is the farthest row.
Cause: with swapped columns, the as-of condition becomes rt >= lt, but the
direction stays Backward. For Backward, the sort-merge operator keeps the last
matching row, so it picks the farthest row. Nothing checks that asOfCondition,
orderExpression, and direction agree.
Reach: the public APIs (pandas-on-Spark merge_asof and SQL ASOF JOIN) always
pass the columns in the right order. The internal PySpark DataFrame._joinAsOf
and the Spark Connect AsOfJoin relation can pass them swapped.
Possible fix: reject as-of columns that come from the wrong side, with a clear
error.
Related: SPARK-60040.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
