Description:
SET time_zone='+00:00';
SELECT
FROM_UNIXTIME(2147483647) AS c1,
FROM_UNIXTIME(2147483648) AS c2,
FROM_UNIXTIME(2147483647.9999999) AS c3;
+---------------------+------+----------------------------+
| c1 | c2 | c3 |
+---------------------+------+----------------------------+
| 2038-01-19 03:14:07 | NULL | 2038-01-19 03:14:08.000000 |
+---------------------+------+----------------------------+
The value for c3 looks wrong. It should be NULL.
According to the manual, TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC,
which corresponds to Unix timestamp range 1 to 2147483647.
For consistency with the supported TIMESTAMP range, FROM_UNIXTIME() returns NULL if
the timestamp value is equal or greater than 2147483648.
This works fine when 2147483648 is directly passed.
However, FROM_UNIXTIME() does not return NULL when 2147483647.9999999 (notice 7 digits) is passed.
The value in the column c3 gets rounded outside of the supported TIMESTAMP range.
Summary:
1. Values in the range (1 <= X <= 2147483647.999999)
correctly convert to a valid non-NULL value.
2. Values in the range (2147483647.9999995 <= X < 2147483648)
erroneously convert to an invalid datetime outside of the supported TIMESTAMP range
3. Values in the range (2147483648 < X)
correctly convert to NULL.
This has a bad side effect. The result of FROM_TIMESTAMP() should be
stored to a TIMESTAMP field without errors. This work find for values
descrined in ranges #1 and #3.
For example:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIMESTAMP(6) NULL);
INSERT INTO t1 VALUES (FROM_UNIXTIME(2147483648));
INSERT INTO t1 VALUES (FROM_UNIXTIME(2147483648.9));
SELECT * FROM t1;
+------+
| a |
+------+
| NULL |
| NULL |
+------+
Notice, all values outside of the supported range were converted to NULL.
However, it does not work for valus on the range #2.
This script returns an error instead of storing NULL:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a TIMESTAMP(6) NULL);
INSERT INTO t1 VALUES (FROM_UNIXTIME(2147483647.9999999));
ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 07:14:08.000000' for column 'a' at row 1
How to repeat:
SET time_zone='+00:00';
SELECT
FROM_UNIXTIME(2147483647) AS c1,
FROM_UNIXTIME(2147483648) AS c2,
FROM_UNIXTIME(2147483647.9999999) AS c3;
Suggested fix:
Please fix FROM_UNIXTIME() to return NULL for values in the range
(2147483647.9999995 <= X < 2147483648)
