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.
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.