Bug #121802 DATE_ADD/ADDDATE/SUBDATE/TIMESTAMPADD read an unsigned interval >= 2^63 as negative: ADDDATE(d, 18446744073709551614) =

Submitted: 17 Nov 7:07 Modified: 18 Nov 12:19
Reporter: Ke Han Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:26.10.0 (trunk 3b99be40) OS:Any
Assigned to: CPU Architecture:Any

[17 Nov 7:07] Ke Han
Description:
An integer interval value of 2^63 or more (an unsigned BIGINT literal, a
BIGINT UNSIGNED column, or a string such as '18446744073709551614') is read
as a negative number.  ADDDATE(d, 18446744073709551614) therefore returns
d minus 2 days, with no warning, and SUBDATE with the same value moves the
date forward.  The manual says that a negative interval is written with a
leading "-"; 18446744073709551614 has none.

A value between 2^63 and 2^64-1 whose signed reading is too large is caught:
ADDDATE('2000-01-01', 9223372036854775810) returns NULL with warning 1441
"datetime field overflow".  This is the result one would expect for every
value of 2^63 or more.  The values just below 2^64 become small negative
numbers, so they produce a plausible, wrong date.

Every unit is affected (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER,
YEAR, MICROSECOND), and so are the DATE_ADD / ADDDATE / SUBDATE /
DATE_SUB / TIMESTAMPADD forms and the "d + INTERVAL n unit" operator.

Cause: sql/item_timefunc.cc, get_interval_value(), on trunk 3b99be40:

    } else if (int_type <= INTERVAL_MICROSECOND) {
      value = args->val_int();
      if (args->null_value) return true;
      ...
      if (value == LLONG_MIN) return true;
      if (value < 0) {
        interval->neg = true;
        value = -value;
      }
    }

args->unsigned_flag is never consulted, so an unsigned argument above
LLONG_MAX is taken as its two's-complement negative.  MariaDB had the same
code and fixed it in 2024 (MDEV-30931, commit 79253261), by reading the
argument as a signed/unsigned hybrid.

How to repeat:
SELECT ADDDATE('2000-01-01', 2) AS plus2,
       ADDDATE('2000-01-01', 18446744073709551614) AS plus_2p64m2,
       ADDDATE('2000-01-01', 9223372036854775810) AS plus_2p63p2;
+------------+-------------+-------------+
| plus2      | plus_2p64m2 | plus_2p63p2 |
+------------+-------------+-------------+
| 2000-01-03 | 1999-12-30  | NULL        |
+------------+-------------+-------------+
1 row in set, 1 warning
Warning (Code 1441): Datetime function: datetime field overflow

SELECT DATE_ADD('2000-01-01', INTERVAL 18446744073709551614 SECOND) AS s,
       DATE_ADD('2000-01-01', INTERVAL 18446744073709551614 MONTH) AS m,
       DATE_ADD('2000-01-01', INTERVAL 18446744073709551614 YEAR) AS y;
+---------------------+------------+------------+
| s                   | m          | y          |
+---------------------+------------+------------+
| 1999-12-31 23:59:58 | 1999-11-01 | 1998-01-01 |
+---------------------+------------+------------+
1 row in set (no warnings)

SELECT DATE_ADD('2000-01-01', INTERVAL '18446744073709551614' DAY) AS str_day,
       SUBDATE('2000-01-01', 18446744073709551614) AS sub;
+------------+------------+
| str_day    | sub        |
+------------+------------+
| 1999-12-30 | 2000-01-03 |
+------------+------------+
1 row in set (no warnings)

CREATE TABLE t013 (n BIGINT UNSIGNED);
INSERT INTO t013 VALUES (2), (18446744073709551614);
SELECT n, DATE_ADD('2000-01-01', INTERVAL n DAY) AS d FROM t013 ORDER BY n;
+----------------------+------------+
| n                    | d          |
+----------------------+------------+
|                    2 | 2000-01-03 |
| 18446744073709551614 | 1999-12-30 |
+----------------------+------------+
2 rows in set (no warnings)

TIMESTAMPADD(DAY, 18446744073709551614, '2000-01-01') and
'2000-01-01' + INTERVAL 18446744073709551614 DAY also return 1999-12-30.

Expected: NULL with warning 1441 "datetime field overflow" for every interval
of 2^63 or more, as for 9223372036854775810 above.  MariaDB 13.2 and TiDB
return that for ADDDATE('2000-01-01', 18446744073709551614).

Suggested fix:
In get_interval_value(), check args->unsigned_flag: if the argument is
unsigned and val_int() is negative, the value is at least 2^63, which no
interval unit can represent, so return true (overflow) instead of negating
it.  The string route ('18446744073709551614') needs the same treatment,
for example by parsing it as an unsigned number with an overflow check.
[17 Nov 7:16] Ke Han
I apologize that this is a duplicated bug report.

Could you please help me close it? Thanks!
[17 Nov 14:21] Jean-François Gagné
Releated: Bug #121757.
[17 Nov 14:24] Jean-François Gagné
Is this not a duplicate of Bug #121508 ?
[18 Nov 5:53] Ke Han
Thanks! Yes it's a duplicated bug report, please go ahead an close it. (I didn't find any button to close it from my side
[18 Nov 12:19] Chaithra Marsur Gopala Reddy
Hi Ke han,

Thank you for the test case. Verified as described. We think this is the same issue reported in Bug#121508. So we are closing this as duplicate.

Thanks,
Chaithra

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.