Description:
When an integer expression is converted to TIME, an unsigned value of 2^63
or more is taken as its two's-complement negative. 18446744073709551614
(2^64 - 2) becomes -00:00:02, with no warning. This affects
CAST(... AS TIME), TIME_TO_SEC(), ADDTIME(), SUBTIME() and
TIMESTAMP(expr1, expr2), with a literal or with a BIGINT UNSIGNED column.
The same value is rejected or flagged on every other route:
- written as a DECIMAL (18446744073709551614.0) or as a string, ADDTIME
returns NULL with warning 1292;
- stored into a TIME column (UPDATE t SET time_col = bigint_unsigned_col),
it fails with ERROR 1292 "Incorrect time value: '18446744073709551614'";
- CAST(9223372036854775808 AS TIME) returns NULL, but its warning reads
"Truncated incorrect time value: '-9223372036854775808'", i.e. the
message itself shows 2^63 read as negative;
- SEC_TO_TIME(18446744073709551614) is clipped to 838:59:59 with a warning
(SEC_TO_TIME was fixed by Bug #20927).
The manual (The TIME Type) says that values outside the TIME range "are
clipped to the closest endpoint of the range", and CAST(8385960 AS TIME)
already gives NULL with a warning. A large positive integer should never
become a small negative time.
Cause: sql/item.cc on trunk 3b99be40:
bool Item::get_time_from_int(Time_val *time) {
const longlong value = val_int();
if (null_value) return true;
return int_to_time_with_warn(value, time);
}
and sql/field.cc, Field_num::val_time():
return int_to_time_with_warn(val_int(), time);
Neither consults unsigned_flag (or Field_num::is_unsigned()), and
int_to_time_with_warn(longlong, ...) cannot tell 2^64 - 2 from -2.
How to repeat:
SELECT CAST(18446744073709551614 AS TIME) AS c_2p64m2,
CAST(9223372036854775808 AS TIME) AS c_2p63,
CAST(8385959 AS TIME) AS c_max,
CAST(8385960 AS TIME) AS c_max_plus1;
+-----------+--------+-----------+-------------+
| c_2p64m2 | c_2p63 | c_max | c_max_plus1 |
+-----------+--------+-----------+-------------+
| -00:00:02 | NULL | 838:59:59 | NULL |
+-----------+--------+-----------+-------------+
1 row in set, 2 warnings
Warning (Code 1292): Truncated incorrect time value: '-9223372036854775808'
Warning (Code 1292): Truncated incorrect time value: '8385960'
SELECT TIME_TO_SEC(18446744073709551614) AS tts, TIME_TO_SEC(-2) AS tts_m2,
SEC_TO_TIME(18446744073709551614) AS stt;
+------+--------+-----------+
| tts | tts_m2 | stt |
+------+--------+-----------+
| -2 | -2 | 838:59:59 |
+------+--------+-----------+
1 row in set, 1 warning
Warning (Code 1292): Truncated incorrect time value: '18446744073709551614'
(the warning is for SEC_TO_TIME)
SELECT ADDTIME('01:00:00', 18446744073709551614) AS a_int,
ADDTIME('01:00:00', 18446744073709551614.0) AS a_dec,
ADDTIME('01:00:00', '18446744073709551614') AS a_str;
+----------+-------+-------+
| a_int | a_dec | a_str |
+----------+-------+-------+
| 00:59:58 | NULL | NULL |
+----------+-------+-------+
1 row in set, 2 warnings
Warning (Code 1292): Truncated incorrect time value: '18446744073709551614.0'
Warning (Code 1292): Truncated incorrect time value: '18446744073709551614'
SELECT SUBTIME(3, 18446744073709551614) AS sub,
TIMESTAMP('2000-01-01 12:34:56', 18446744073709551614) AS ts2;
+----------+---------------------+
| sub | ts2 |
+----------+---------------------+
| 00:00:05 | 2000-01-01 12:34:54 |
+----------+---------------------+
CREATE TABLE t014 (n BIGINT UNSIGNED, t TIME);
INSERT INTO t014 (n) VALUES (18446744073709551614);
SELECT n, CAST(n AS TIME) AS as_time, TIME_TO_SEC(n) AS secs FROM t014;
+----------------------+-----------+------+
| n | as_time | secs |
+----------------------+-----------+------+
| 18446744073709551614 | -00:00:02 | -2 |
+----------------------+-----------+------+
UPDATE t014 SET t = n;
ERROR 1292 (22007): Incorrect time value: '18446744073709551614' for column 't' at row 1
Expected: for every integer of 2^63 or more, the same result as for the
DECIMAL/string forms and as for 8385960: NULL (or 838:59:59) with warning
1292. Never -00:00:02.
Suggested fix:
In Item::get_time_from_int() and Field_num::val_time(), check unsigned_flag:
an unsigned value above LLONG_MAX is out of the TIME range and should take
the same path as other out-of-range values (warning 1292, NULL or clipped).
This is what Bug #20927 did for SEC_TO_TIME.
Description: When an integer expression is converted to TIME, an unsigned value of 2^63 or more is taken as its two's-complement negative. 18446744073709551614 (2^64 - 2) becomes -00:00:02, with no warning. This affects CAST(... AS TIME), TIME_TO_SEC(), ADDTIME(), SUBTIME() and TIMESTAMP(expr1, expr2), with a literal or with a BIGINT UNSIGNED column. The same value is rejected or flagged on every other route: - written as a DECIMAL (18446744073709551614.0) or as a string, ADDTIME returns NULL with warning 1292; - stored into a TIME column (UPDATE t SET time_col = bigint_unsigned_col), it fails with ERROR 1292 "Incorrect time value: '18446744073709551614'"; - CAST(9223372036854775808 AS TIME) returns NULL, but its warning reads "Truncated incorrect time value: '-9223372036854775808'", i.e. the message itself shows 2^63 read as negative; - SEC_TO_TIME(18446744073709551614) is clipped to 838:59:59 with a warning (SEC_TO_TIME was fixed by Bug #20927). The manual (The TIME Type) says that values outside the TIME range "are clipped to the closest endpoint of the range", and CAST(8385960 AS TIME) already gives NULL with a warning. A large positive integer should never become a small negative time. Cause: sql/item.cc on trunk 3b99be40: bool Item::get_time_from_int(Time_val *time) { const longlong value = val_int(); if (null_value) return true; return int_to_time_with_warn(value, time); } and sql/field.cc, Field_num::val_time(): return int_to_time_with_warn(val_int(), time); Neither consults unsigned_flag (or Field_num::is_unsigned()), and int_to_time_with_warn(longlong, ...) cannot tell 2^64 - 2 from -2. How to repeat: SELECT CAST(18446744073709551614 AS TIME) AS c_2p64m2, CAST(9223372036854775808 AS TIME) AS c_2p63, CAST(8385959 AS TIME) AS c_max, CAST(8385960 AS TIME) AS c_max_plus1; +-----------+--------+-----------+-------------+ | c_2p64m2 | c_2p63 | c_max | c_max_plus1 | +-----------+--------+-----------+-------------+ | -00:00:02 | NULL | 838:59:59 | NULL | +-----------+--------+-----------+-------------+ 1 row in set, 2 warnings Warning (Code 1292): Truncated incorrect time value: '-9223372036854775808' Warning (Code 1292): Truncated incorrect time value: '8385960' SELECT TIME_TO_SEC(18446744073709551614) AS tts, TIME_TO_SEC(-2) AS tts_m2, SEC_TO_TIME(18446744073709551614) AS stt; +------+--------+-----------+ | tts | tts_m2 | stt | +------+--------+-----------+ | -2 | -2 | 838:59:59 | +------+--------+-----------+ 1 row in set, 1 warning Warning (Code 1292): Truncated incorrect time value: '18446744073709551614' (the warning is for SEC_TO_TIME) SELECT ADDTIME('01:00:00', 18446744073709551614) AS a_int, ADDTIME('01:00:00', 18446744073709551614.0) AS a_dec, ADDTIME('01:00:00', '18446744073709551614') AS a_str; +----------+-------+-------+ | a_int | a_dec | a_str | +----------+-------+-------+ | 00:59:58 | NULL | NULL | +----------+-------+-------+ 1 row in set, 2 warnings Warning (Code 1292): Truncated incorrect time value: '18446744073709551614.0' Warning (Code 1292): Truncated incorrect time value: '18446744073709551614' SELECT SUBTIME(3, 18446744073709551614) AS sub, TIMESTAMP('2000-01-01 12:34:56', 18446744073709551614) AS ts2; +----------+---------------------+ | sub | ts2 | +----------+---------------------+ | 00:00:05 | 2000-01-01 12:34:54 | +----------+---------------------+ CREATE TABLE t014 (n BIGINT UNSIGNED, t TIME); INSERT INTO t014 (n) VALUES (18446744073709551614); SELECT n, CAST(n AS TIME) AS as_time, TIME_TO_SEC(n) AS secs FROM t014; +----------------------+-----------+------+ | n | as_time | secs | +----------------------+-----------+------+ | 18446744073709551614 | -00:00:02 | -2 | +----------------------+-----------+------+ UPDATE t014 SET t = n; ERROR 1292 (22007): Incorrect time value: '18446744073709551614' for column 't' at row 1 Expected: for every integer of 2^63 or more, the same result as for the DECIMAL/string forms and as for 8385960: NULL (or 838:59:59) with warning 1292. Never -00:00:02. Suggested fix: In Item::get_time_from_int() and Field_num::val_time(), check unsigned_flag: an unsigned value above LLONG_MAX is out of the TIME range and should take the same path as other out-of-range values (warning 1292, NULL or clipped). This is what Bug #20927 did for SEC_TO_TIME.