[
https://issues.apache.org/jira/browse/SPARK-60929?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Stevo Mitric updated SPARK-60929:
---------------------------------
Description:
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
was:
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
> 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
> Priority: Major
>
> 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]
