[
https://issues.apache.org/jira/browse/SPARK-60517?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Wenchen Fan reassigned SPARK-60517:
-----------------------------------
Assignee: Nikolina Vraneš
> Fix mixed timestamp ordering in SQL ASOF joins
> ----------------------------------------------
>
> Key: SPARK-60517
> URL: https://issues.apache.org/jira/browse/SPARK-60517
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.3.0, 5.0.0, 4.4.0
> Reporter: Nikolina Vraneš
> Assignee: Nikolina Vraneš
> Priority: Major
> Labels: pull-request-available
>
> SQL ASOF compares mixed TIMESTAMP_LTZ and TIMESTAMP_NTZ keys after converting
> them to a common LTZ type, but sorts each input using its original key type.
> NTZ-to-LTZ conversion can change the order across a daylight-saving
> transition, so the scan can select the wrong row. The same issue affects
> timestamp fields inside tuples, structs, and arrays.
> {code:sql}
> SET spark.sql.join.asofJoin.enabled = true;
> SET spark.sql.session.timeZone = America/Los_Angeles;
> SELECT r.id
> FROM VALUES (TIMESTAMP_LTZ '2024-03-10 10:05:00Z') AS l(ts)
> ASOF JOIN VALUES
> (TIMESTAMP_NTZ '2024-03-10 02:50:00', 1),
> (TIMESTAMP_NTZ '2024-03-10 03:00:00', 2),
> (TIMESTAMP_NTZ '2024-03-10 03:15:00', 3) AS r(ts, id)
> MATCH_CONDITION (l.ts <= r.ts);
> {code}
> The query returns id 1; the correct match is id 3. In America/Los_Angeles,
> 02:50 converts to 10:50Z, while 03:00 and 03:15 convert to 10:00Z and 10:15Z.
> Sorting the NTZ values therefore puts the latest instant first. Backward
> matching and mixed keys with NTZ on the left are also affected.
> Fix: normalize mixed timestamp keys, including nested timestamp fields, to
> the comparison's common timestamp type before deriving the SQL ASOF sort
> keys. Preserve other field types and the null semantics of composite keys.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
