| Bug #93600 | Setting out of range fractional part produces incorrect timestamps | ||
|---|---|---|---|
| Submitted: | 13 Dec 2018 21:58 | Modified: | 16 Jan 2019 18:09 |
| Reporter: | Evgeny Firsov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.6, 5.7, 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | fractional timestamp, golang, nanosecond, out of range | ||
[14 Dec 2018 14:02]
MySQL Verification Team
Hi, First of all, floating point number format, as defined by IEEE standard, is not used for TIMESTAMP data type. Second, your test case is not correct. Current timestamp is changing, so please try using a constant value for the DATETIME domain in order to prove that rounding is not correct.
[14 Dec 2018 21:05]
Evgeny Firsov
>> First of all, floating point number format, as defined by IEEE standard, is not used for TIMESTAMP data type.
Double precision format is used to handle timestamps "internally", meaning in the MySQL code. For example:
static bool update_timestamp(THD *thd, set_var *var){
if (var->value) {
double fl= floor(var->save_result.double_value); // Truncate integer part
struct timeval tmp;
tmp.tv_sec= static_cast<long>(fl);
/* Round nanoseconds to nearest microsecond */
tmp.tv_usec=
static_cast<long>(rint((var->save_result.double_value - fl) * 1000000)); <-- **this is the line with the bug**
thd->set_time(&tmp);
}
...
}
>> Second, your test case is not correct. Current timestamp is changing, so >> please try using a constant value for the DATETIME domain in order to prove that rounding is not correct.
SET SESSION TIMESTAMP is there exactly to provide a way to have "not changing timestamp", required for idempotency in such use cases like statement replication.
[17 Dec 2018 14:14]
MySQL Verification Team
Hi, First of all, your setting of the session.time_stamp is wrong, As you can read in our Reference Manual, chapter on "Data Types", TIMESTAMP has a range of values. Your value is out of the range, so no bug can be tested with that range. Second, CURRENT_TIMESTAMP is a synonym for NOW(), so it is definitely not idempotent !!! Last, but not least, DATETIME types are stored in decimal format in the tables. However, some calculus is done with floating point. Hence, we can not even start testing your test case.
[17 Dec 2018 17:43]
MySQL Verification Team
HI,
I tested your presumption about possible out-of-range fractional part and everything works fine.
First when dealing with 6 digits in the fractional part of the table as you specified it:
INSERT INTO t11 VALUES ("2018-12-17 19:35:33.999999","2018-12-17 19:35:33.999999")
"SELECT * FROM t11"
+----------------------------+----------------------------+
| ts | dt |
+----------------------------+----------------------------+
| 2018-12-17 19:35:33.999999 | 2018-12-17 19:35:33.999999 |
+----------------------------+----------------------------+
And then with more digits in the fractional part:
'INSERT INTO t11 VALUES ("2018-12-17 19:35:33.999999999","2018-12-17 19:35:33.999999999")'
"SELECT * FROM t11"
+----------------------------+----------------------------+
| ts | dt |
+----------------------------+----------------------------+
| 2018-12-17 19:35:34.000000 | 2018-12-17 19:35:34.000000 |
+----------------------------+----------------------------+
Everything is just fine.
[18 Dec 2018 4:55]
Ovais Tariq
Sinisa Milivojevic would you mind testing with the test case provided in the bug report. The test case in bug report first set the timestamp variable. If you don’t think the right value is specified for the variable even then it’s bug as setting of incorrect values should be rejected.
[18 Dec 2018 13:40]
MySQL Verification Team
Hi,
I was not able to get the wrong CURRENT_TIMESTAMP() in 8.0, but I did manage to get an assert() in the debug mode.
Here is a shortest possible test case:
------------------------------------------------
mysql> SET SESSION TIMESTAMP=1.9999996;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 1970-01-01 02:00:01 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP(6);
^C^C -- query aborted
ERROR 2013 (HY000): Lost connection to MySQL server during query
----------------------------------------------------------------------------
Here is where assert happens:
---------------------------------------------------------------------------
longlong TIME_to_longlong_datetime_packed(const MYSQL_TIME *ltime) {
longlong ymd = ((ltime->year * 13 + ltime->month) << 5) | ltime->day;
longlong hms = (ltime->hour << 12) | (ltime->minute << 6) | ltime->second;
longlong tmp = MY_PACKED_TIME_MAKE(((ymd << 17) | hms), ltime->second_part);
DBUG_ASSERT(!check_datetime_range(ltime)); /* Make sure no overflow */
return ltime->neg ? -tmp : tmp;
}
---------------------------------------------------------
Verified for debug builds, which might also be a cause for wrong CURRENT_TIMESTAMP in the previous versions.
[18 Dec 2018 17:52]
Evgeny Firsov
Server version: 8.0.13 MySQL Community Server - GPL
mysql> set session timestamp=1.9999996;
Query OK, 0 rows affected (0.00 sec)
mysql> select current_timestamp(6);
+-----------------------------+
| current_timestamp(6) |
+-----------------------------+
| 1969-12-31 16:00:01.1000000 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> set sql_mode='TIME_TRUNCATE_FRACTIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> set session timestamp=1.9999996;
Query OK, 0 rows affected (0.00 sec)
mysql> select current_timestamp(6);
+-----------------------------+
| current_timestamp(6) |
+-----------------------------+
| 1969-12-31 16:00:01.1000000 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> create table t1(ts timestamp(6));
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t1 values(current_timestamp(6));
Query OK, 1 row affected (0.09 sec)
mysql> select * from t1;
+-----------------------------+
| ts |
+-----------------------------+
| 1969-12-31 16:00:01.1000000 |
+-----------------------------+
1 row in set (0.00 sec)
[20 Dec 2018 15:40]
Tor Didriksen
Posted by developer: Values to 'SET SESSION TIMESTAMP' have always been handled as doubles, and then converted using rounding. Thus it seems more logically correct to me, to handle 1.9999996 as 2.0 We could of course truncate any microsecond value >= 1000000 to 999999 and keep rounding for other values.
[20 Dec 2018 16:43]
MySQL Verification Team
Thank you, very much, Tor. This is one of the reasons why this bug is verified. Changing also the Severity.
[9 Jan 2019 16:04]
Tor Didriksen
Posted by developer: SET SESSION TIMESTAMP=1.9999996; CREATE TABLE t0( ts TIMESTAMP(0) not null, dt DATETIME(0) not null); CREATE TABLE t1( ts TIMESTAMP(1) not null, dt DATETIME(1) not null); CREATE TABLE t2( ts TIMESTAMP(2) not null, dt DATETIME(2) not null); CREATE TABLE t3( ts TIMESTAMP(3) not null, dt DATETIME(3) not null); CREATE TABLE t4( ts TIMESTAMP(4) not null, dt DATETIME(4) not null); CREATE TABLE t5( ts TIMESTAMP(5) not null, dt DATETIME(5) not null); CREATE TABLE t6( ts TIMESTAMP(6) not null, dt DATETIME(6) not null); INSERT INTO t0 values (CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)); INSERT INTO t1 values (CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)); INSERT INTO t2 values (CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)); INSERT INTO t3 values (CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)); INSERT INTO t4 values (CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)); INSERT INTO t5 values (CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)); INSERT INTO t6 values (CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)); In default mode (round microseconds), only timestamp(6) / datetime(6) are affected: SELECT * FROM t0; ts dt 1970-01-01 00:00:02 1970-01-01 00:00:02 SELECT * FROM t1; ts dt 1970-01-01 00:00:02.0 1970-01-01 00:00:02.0 SELECT * FROM t2; ts dt 1970-01-01 00:00:02.00 1970-01-01 00:00:02.00 SELECT * FROM t3; ts dt 1970-01-01 00:00:02.000 1970-01-01 00:00:02.000 SELECT * FROM t4; ts dt 1970-01-01 00:00:02.0000 1970-01-01 00:00:02.0000 SELECT * FROM t5; ts dt 1970-01-01 00:00:02.00000 1970-01-01 00:00:02.00000 SELECT * FROM t6; ts dt 1970-01-01 00:00:01.1000000 1970-01-01 00:00:01.1000000 with sql_mode=time_truncate_fractional; SELECT * FROM t0; ts dt 1970-01-01 00:00:01 1970-01-01 00:00:01 SELECT * FROM t1; ts dt 1970-01-01 00:00:01.10 1970-01-01 00:00:01.10 SELECT * FROM t2; ts dt 1970-01-01 00:00:01.100 1970-01-01 00:00:01.100 SELECT * FROM t3; ts dt 1970-01-01 00:00:01.1000 1970-01-01 00:00:01.1000 SELECT * FROM t4; ts dt 1970-01-01 00:00:01.10000 1970-01-01 00:00:01.10000 SELECT * FROM t5; ts dt 1970-01-01 00:00:01.100000 1970-01-01 00:00:01.100000 SELECT * FROM t6; ts dt 1970-01-01 00:00:01.1000000 1970-01-01 00:00:01.1000000
[16 Jan 2019 18:09]
Paul DuBois
Posted by developer: Fixed in 8.0.16. An out-of-range fractional part could produce incorrect timestamps in SET SESSION timestamp statements.
[17 Jan 2019 15:35]
Paul DuBois
Posted by developer: Fixed in 8.0.15, not 8.0.16.
[17 Jan 2019 15:41]
Tor Didriksen
Posted by developer: Note that the patch only fixed the update_timestamp() funciton. The bug reporter suggested fixing non-normalized data when reading from disk, in functions my_datetime_packed_from_binary() and my_timestamp_from_binary(). After conferring with our replication team, I decided not to implement this.
Description: The number of fractional digits is unrestricted when setting timestamp using SET SESSION TIMESTAMP. This leads to incorrect timestamps when nanoseconds are present and fractional part needs to be rounded up to microseconds. Fractional part contains 7 digits in that case (.1000000), which is incorrect and out of range (000000-999999) Because timestamps are handled internally using double precision floating point format, the range which leads to incorrect value varies and depends on number of digits in the value. For example, when integer part contains 10 digits, fractional part range, which produces incorrect result, is from .999999404 to .999999880 How to repeat: mysql> SET SESSION TIMESTAMP=1.9999996; Query OK, 0 rows affected (0.00 sec) mysql> SELECT CURRENT_TIMESTAMP(6); +-----------------------------+ | CURRENT_TIMESTAMP(6) | +-----------------------------+ | 1969-12-31 16:00:01.1000000 | +-----------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE t1( ts TIMESTAMP(6), dt DATETIME(6) ); Query OK, 0 rows affected (0.08 sec) mysql> INSERT INTO t1 values (CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6)); Query OK, 1 row affected (0.10 sec) mysql> SELECT * FROM t1; +-----------------------------+-----------------------------+ | ts | dt | +-----------------------------+-----------------------------+ | 1969-12-31 16:02:03.1000000 | 1969-12-31 16:02:03.1000000 | +-----------------------------+-----------------------------+ 1 row in set (0.00 sec) Suggested fix: Ideally we should get rid of using double format, when handling timestamps, to avoid rounding issues and support nanosecond precision in the future. But, because nanosecond behavior is undefined currently, we can implement quick fix and just truncate timestamps to microsecond precision. Meaning just add: if (frac > 999999) { frac= 999999; } In sys_vars.cc::update_timestamp to fix write path. In my_datetime_packed_from_binary and my_timestamp_from_binary to repair existing data on read.