[jira] [Created] (SPARK-59829) Fix overflow in forward ASOF join by keeping the first match

["Luka Zdravic (Jira)" <ji...@apache.org>]

Luka Zdravic created SPARK-59829:
------------------------------------

             Summary: Fix overflow in forward ASOF join by keeping the first match
                 Key: SPARK-59829
                 URL: https://issues.apache.org/jira/browse/SPARK-59829
             Project: Spark
          Issue Type: Bug
          Components: SQL
    Affects Versions: 5.0.0
            Reporter: Luka Zdravic


Forward ASOF joins ({{t.k <= r.k}} or {{t.k < r.k}}) in {{SortMergeAsOfJoinExec}} compute {{right - left}} for every candidate row to find the closest one. This overflows when the values are far apart.

  {code:sql}
  SET spark.sql.join.asofJoin.enabled=true;
  SELECT r.tag
  FROM VALUES (-100) AS t(k)
  ASOF JOIN VALUES (-50, 'near'), (2147483647, 'far') AS r(k, tag)
    MATCH_CONDITION (t.k <= r.k);
  {code}
  
  * ANSI on: fails with ARITHMETIC_OVERFLOW.
  * ANSI off: the subtraction wraps to a negative number, so it returns {{far}}.
  * Expected: {{near}}.
  
The same bug hits BIGINT, DECIMAL(38, 0), and both interval types, as an error or a wrong result.
  
The subtraction is not needed. The right rows are sorted ascending, so the first matching row is always the closest. Backward joins already work this way: they keep the last match and never subtract. The fix makes Forward keep the first match. Nearest keeps the distance loop, because it needs it.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org