Bug #77805 NULLIF cause wrong value with cast datetime
Submitted: 23 Jul 2015 2:01 Modified: 23 Jul 2015 4:58
Reporter: James Fan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.6.25 MySQL Community Server (GPL) OS:Ubuntu (Ubuntu 14.04.2 LTS x86_64)
Assigned to: CPU Architecture:Any
Tags: cast, datetime, NULLIF

[23 Jul 2015 2:01] James Fan
Description:
Expression like below will get NULL value:
CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) 

How to repeat:
-- run sql below :
select CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME );
-- will get NULL:
+-----------------------------------------------------+
| CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
[23 Jul 2015 4:58] Umesh Shastry
Hello James,

Thank you for the report.

Thanks,
Umesh
[23 Jul 2015 5:01] Umesh Shastry
// 5.1.76

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.1.76: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.76 Source distribution

Copyright (c) 2000, 2013, 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> select CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME );
+-----------------------------------------------------+
| CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) |
+-----------------------------------------------------+
| 2015-07-04 00:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.02 sec)

mysql> CREATE TABLE c_test (col DATETIME);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into c_test values('2015-07-04 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> select * from c_test;
+---------------------+
| col                 |
+---------------------+
| 2015-07-04 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> select CAST(NULLIF(col,'') AS DATETIME ) from c_test;
+-----------------------------------+
| CAST(NULLIF(col,'') AS DATETIME ) |
+-----------------------------------+
| 2015-07-04 00:00:00               |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

// 5.5.46

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.46: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.46 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> select CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME );
+-----------------------------------------------------+
| CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) |
+-----------------------------------------------------+
| 2015-07-04 00:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

// 5.6.25

[root@cluster-repo ~]# mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> select CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME );
+-----------------------------------------------------+
| CAST(NULLIF('2015-07-04 00:00:00','') AS DATETIME ) |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> CREATE TABLE c_test (col DATETIME);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into c_test values('2015-07-04 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> select * from c_test;
+---------------------+
| col                 |
+---------------------+
| 2015-07-04 00:00:00 |
+---------------------+
1 row in set (0.01 sec)

mysql> select CAST(NULLIF(col,'') AS DATETIME ) from c_test;
+-----------------------------------+
| CAST(NULLIF(col,'') AS DATETIME ) |
+-----------------------------------+
| 2015-07-04 00:00:00               |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '' for column 'col' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

Quoting from manual "Prior to MySQL 5.6.4, when using an explicit CAST() on a TIMESTAMP value in a statement that does not select from any tables, the value is treated by MySQL 5.6 as a string prior to performing any conversion. This results in the value being truncated when casting to a numeric type" - https://dev.mysql.com/doc/refman/5.6/en/cast-functions.html#function_convert

I'm not sure whether this applies to DATE/DATETIME types too.