sepuri sai krishna created SPARK-60248:
------------------------------------------
Summary: Nearest sort-merge AS-OF join can skip the nearest row
after right rows that share an as-of value
Key: SPARK-60248
URL: https://issues.apache.org/jira/browse/SPARK-60248
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.3.0, 4.4.0
Reporter: sepuri sai krishna
With {{spark.sql.join.sortMergeAsOfJoin.enabled=true}}, a nearest AS-OF join
can return a right row that is not the nearest one.
{{SortMergeAsOfJoinScanner.findNearest}} scans the sorted right rows of a group
and stops at the first row whose distance is not smaller than the best so far,
as the distance is V-shaped over the sorted rows. It is only V-shaped in the
non-strict sense: right rows that share an as-of value are the same distance
from the left row. So when two right rows share a value before the nearest row,
the scan stops at the second one and returns the first.
{code:scala}
val left = Seq((1, 10)).toDF("lid", "t")
val right = Seq((1, 1), (2, 1), (3, 9)).toDF("rid", "rt")
left.joinAsOf(right, left("t"), right("rt"), Seq.empty, "inner", null, true,
"nearest")
{code}
||spark.sql.join.sortMergeAsOfJoin.enabled||result||
|false (default)|[1,3,9] (distance 1)|
|true|[1,1,1] (distance 9)|
Without the duplicate (right values 1, 5, 9) both return rt=9.
This is reached through the nearest direction of the DataFrame AS-OF join, e.g.
{{pyspark.pandas.merge_asof(..., direction="nearest")}}. SQL ASOF JOIN has no
nearest direction, and backward and forward scans are not affected. The scan
has been like this since the operator was added in SPARK-56887.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
