Bug #92501 FROM_UNIXTIME accepts an out of range parameter through rounding
Submitted: 19 Sep 2018 12:47 Modified: 8 Jan 3:54
Reporter: Alexander Barkov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7, 5.7.23, 8.0.12, 5.6.41 OS:Any
Assigned to: CPU Architecture:Any

[19 Sep 2018 12:47] Alexander Barkov
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)
[19 Sep 2018 13:01] Umesh Shastry
Hello Alexander,

Thank you for the report and feedback!

regards,
Umesh
[8 Jan 3:54] Paul Dubois
Posted by developer:
 
Fixed in 8.0.15.

FROM_UNIXTIME() returned an out-of-range value if passed an argument
that, when rounded up, exceeded the epoch value. Now it returns NULL
as for other out-of-range values.