Bug #82582 inconsistent and ambiguous results with cast decimal to datetime
Submitted: 15 Aug 2016 11:05 Modified: 15 Aug 2016 13:32
Reporter: 帅 Bang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6/5.7 OS:Linux
Assigned to: CPU Architecture:Any

[15 Aug 2016 11:05] 帅 Bang
Description:
mysql> drop table if exists t2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (c1 datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sb values(123.45);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

But, when we cast 123.45 to datetime, we get:

mysql> select cast(123.45 as datetime);
+--------------------------+
| cast(123.45 as datetime) |
+--------------------------+
| 2000-01-23 00:00:00      |
+--------------------------+
1 row in set (0.00 sec)

How to repeat:
drop table if exists t2;
create table t2 (c1 datetime);
insert into sb values(123.45);
select cast(123.45 as datetime);

Suggested fix:
insert into sb values(123.45); succeed and 2000-01-23 00:00:00 is inserted
[15 Aug 2016 13:23] MySQL Verification Team
The error happens on 5.7 due to default sql_mode which converts the warnings on error:

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.15 Source distribution 2016-JUL-09

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > set @@sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > drop table if exists sb;
Query OK, 0 rows affected (0.19 sec)

mysql 5.7 > create table sb (c1 datetime);
Query OK, 0 rows affected (0.27 sec)

mysql 5.7 > insert into sb values(123.45);
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql 5.7 > select cast(123.45 as datetime);
+--------------------------+
| cast(123.45 as datetime) |
+--------------------------+
| 2000-01-23 00:00:00      |
+--------------------------+
1 row in set (0.00 sec)

mysql 5.7 > select * from sb;
+---------------------+
| c1                  |
+---------------------+
| 2000-01-23 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql 5.7 >
[15 Aug 2016 13:32] MySQL Verification Team
Verifiying since 5.6/5.7 changed behavior with a warning which wan't done by early 5.0/5.5 version:

C:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.97-Win X64 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > drop table if exists sb;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > create table sb (c1 datetime);
Query OK, 0 rows affected (0.04 sec)

mysql 5.0 > insert into sb values(123.45);
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > select cast(123.45 as datetime);
+--------------------------+
| cast(123.45 as datetime) |
+--------------------------+
| 2000-01-23 00:00:00      |
+--------------------------+
1 row in set (0.00 sec)

mysql 5.0 > select * from sb;
+---------------------+
| c1                  |
+---------------------+
| 2000-01-23 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.52 Source distribution 2016-JUL-09

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test
Database changed
mysql 5.5 > drop table if exists sb;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql 5.5 > create table sb (c1 datetime);
Query OK, 0 rows affected (0.10 sec)

mysql 5.5 > insert into sb values(123.45);
Query OK, 1 row affected (0.06 sec)

mysql 5.5 > select cast(123.45 as datetime);
+--------------------------+
| cast(123.45 as datetime) |
+--------------------------+
| 2000-01-23 00:00:00      |
+--------------------------+
1 row in set (0.05 sec)

mysql 5.5 > select * from sb;
+---------------------+
| c1                  |
+---------------------+
| 2000-01-23 00:00:00 |
+---------------------+
1 row in set (0.09 sec)