Stevo Mitric created SPARK-60929:
------------------------------------
Summary: next_day and last_day wrap around instead of failing when
the result is later than the maximum DATE
Key: SPARK-60929
URL: https://issues.apache.org/jira/browse/SPARK-60929
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.4.0
Reporter: Stevo Mitric
Apache JIRA renders wiki markup, so the block below uses {{monospace}} and
{code}, not backticks. Paste it as is.
```
Spark stores a DATE as the Int number of days since 1970-01-01, so the
supported range is -5877641-06-23 (Int.MinValue) to +5881580-07-11
(Int.MaxValue). {{next_day}} and {{last_day}} compute their result with plain
Int arithmetic in {{DateTimeUtils.getNextDateForDayOfWeek}} and
{{DateTimeUtils.getLastDayOfMonth}}, so near those limits they silently return
a wrong date. This happens with ANSI mode on and off, in both interpreted and
generated code, and never raises an error.
{code:sql}
-- The result does not fit in the DATE range: it wraps around to the far end of
the range.
SELECT last_day(DATE'+5881580-07-11'); -- -5877641-07-12;
+5881580-07-31 is past the maximum DATE
SELECT next_day(DATE'+5881580-07-11', 'MO'); -- -5877641-06-25;
+5881580-07-14 is past the maximum DATE
-- The result fits, but `dayOfWeek - 1 - startDay` overflows: a Thursday
instead of the Monday.
SELECT next_day(DATE'-5877641-06-23', 'MO'); -- -5877641-06-25; correct:
-5877641-06-29
{code}
Affected inputs: {{last_day}} wraps for the last 11 days of the range
(+5881580-07-01 .. +5881580-07-11). {{next_day}} wraps for the last 7 days (for
the days of week whose next occurrence is past the maximum) and returns the
wrong weekday for the first 6 days (-5877641-06-23 .. -5877641-06-28).
{{trunc(d, 'WEEK')}} shares the helper and is wrong for the first days of the
range too.
Since the wrapped value is still a valid DATE, nothing downstream notices.
Verified on the released Spark 4.2.0 and on current master (69a934425b0).
Proposed fix: compute in Long, return the correct date where it fits, and throw
{{DATETIME_OVERFLOW}} (SQLSTATE 22008) where it does not, regardless of
{{spark.sql.ansi.enabled}}, as SPARK-59859 proposes for {{date_add}} /
{{date_sub}} (https://github.com/apache/spark/pull/59135, which does not touch
these two functions) and as {{timestampadd}} does since SPARK-38481. PR:
https://github.com/apache/spark/pull/60130
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
