sepuri sai krishna created SPARK-59859:
------------------------------------------
Summary: Fail on overflow in date_add and date_sub instead of
wrapping around
Key: SPARK-59859
URL: https://issues.apache.org/jira/browse/SPARK-59859
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: sepuri sai krishna
{{date_add}} and {{date_sub}} (and the {{date + int}} / {{date - int}}
operators, {{dateadd}}, {{try_add}} and {{try_subtract}} on dates) add the
number of days to the date with plain {{Int}} arithmetic, so a result outside
the {{Int}} range silently wraps around and returns a wrong date. This happens
with ANSI mode on and off, and in both codegen and interpreted mode:
{code:sql}
SELECT date_add(DATE'2020-01-01', 2147483647); -- -5877591-06-21
SELECT date_sub(DATE'1900-01-01', 2147483647); -- +5881510-07-14
SELECT try_add(DATE'2020-01-01', 2147483647); -- -5877591-06-21, not NULL
{code}
Adding a positive number of days returns a date millions of years in the past.
Since the wrapped value is still a valid {{DATE}}, nothing downstream notices.
Similar operations already fail on overflow instead, with ANSI mode on and off:
{code:sql}
SELECT add_months(DATE'2020-01-01', 2147483647); --
[ARITHMETIC_OVERFLOW]
SELECT DATE'2020-01-01' + make_dt_interval(2147483647, 0, 0, 0); --
[INTERVAL_ARITHMETIC_OVERFLOW]
{code}
{{DateAdd}} and {{DateSub}} should fail with {{DATETIME_OVERFLOW}} in this
case, as {{TIMESTAMPADD}} does since SPARK-38481. {{try_add}} and
{{try_subtract}} then return NULL, as documented.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
