Bug #79804 Inconsistent results when inserting 0.1 into datetime(6) and timestamp(6)

• Submitted: 29 Dec 2015 12:36
• Modified: 30 Dec 2015 16:54
• Reporter: Su Dylan
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8
• OS: Any
• CPU Architecture: Any

[29 Dec 2015 12:36] Su Dylan

Description:
Output:
=======
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(c1 datetime(6), c2 timestamp(6));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(c1) values(0.1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----------------------------+----------------------------+
| c1                         | c2                         |
+----------------------------+----------------------------+
| 0000-00-00 00:00:00.100000 | 2015-12-29 20:20:05.038229 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> insert into t1(c2) values(0.1);
ERROR 1292 (22007): Incorrect datetime value: '0.1' for column 'c2' at row 1
mysql> select * from t1;
+----------------------------+----------------------------+
| c1                         | c2                         |
+----------------------------+----------------------------+
| 0000-00-00 00:00:00.100000 | 2015-12-29 20:20:05.038229 |
+----------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
When inserting 0.1 into datetime(6), it succeeds, while it fails when inserting 0.1 into timestamp(6).

How to repeat:

set sql_mode='STRICT_ALL_TABLES';
drop table if exists t1;
create table t1(c1 datetime(6), c2 timestamp(6));
insert into t1(c1) values(0.1);
select * from t1;
insert into t1(c2) values(0.1);
select * from t1;

Suggested fix:
When inserting 0.1 into timestamp(6), it succeeds.

[29 Dec 2015 12:56] Peter Laursen

Doesn't this eplain:

http://dev.mysql.com/doc/refman/5.7/en/datetime.html "TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC."

-- Peter
-- not a MySQL/Oracle person.

[30 Dec 2015 16:54] MySQL Verification Team

Thank you for the bug report.

http://dev.mysql.com/doc/refman/5.7/en/datetime.html

"MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00'. "