Bug #81950 when cast failed mysql return different results
Submitted: 21 Jun 2016 11:06 Modified: 27 Jul 2016 9:41
Reporter: 帅 Bang Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6 OS:Linux
Assigned to: CPU Architecture:Any

[21 Jun 2016 11:06] 帅 Bang 
Description:
mysql> create table sb(a datetime);
Query OK, 0 rows affected (0.01 sec)

mysql> set @@sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sb values("abc");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from sb;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

"abc" is not a valid timestamp, so when the cast failed, it returns all-zero values instead. this is acceptable.

while, when we type this:

mysql> select cast("abc" as datetime);
+-------------------------+
| cast("abc" as datetime) |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

it returns NULL

How to repeat:
 create table sb(a datetime);
 set @@sql_mode = '';
 insert into sb values("abc");
 select * from sb;

 select cast("abc" as datetime);

Suggested fix:
when cast to datetime failed, not matter implicitly or explicitly, both return NULL(or all-zero values)
[5 Jul 2016 2:14] 帅 Bang 
Excuse me, is there any progress on this issue?

thx
[27 Jul 2016 9:41] MySQL Verification Team 
Hello Bang,

Thank you for the bug report and test case.
Imho this is not a bug, as per manual 'Inserting a value into a date or time column that is illegal for the data type. The column is set to the appropriate zero value for the type' See https://dev.mysql.com/doc/refman/5.6/en/insert.html. 

In case of CAST - If you try to convert an invalid expression to a date, CAST() returns NULL and which is expected behavior.

Thanks,
Umesh