Nikolina Vraneš created SPARK-60517:
---------------------------------------
Summary: 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: 5.0.0
Reporter: Nikolina Vraneš
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]
