Bug #79915 Function NULLIF + CAST is not working as expected
Submitted: 11 Jan 2016 8:18 Modified: 11 Jan 2016 9:32
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8, 5.6.28, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[11 Jan 2016 8:18] Su Dylan
Description:
Output:
=====
mysql>  SELECT CAST( NULLIF('c', 'e' ) AS DATETIME ), CAST( 'c' AS DATETIME );
+---------------------------------------+-------------------------+
| CAST( NULLIF('c', 'e' ) AS DATETIME ) | CAST( 'c' AS DATETIME ) |
+---------------------------------------+-------------------------+
| 0000-00-00 00:00:00                   | NULL                    |
+---------------------------------------+-------------------------+
1 row in set, 2 warnings (0.00 sec)

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

Problem:
=====
NULL is expected for "CAST( NULLIF('c', 'e' ) AS DATETIME )".

How to repeat:
SELECT CAST( NULLIF('c', 'e' ) AS DATETIME ), CAST( 'c' AS DATETIME );

Suggested fix:
NULL is returned for "CAST( NULLIF('c', 'e' ) AS DATETIME )".
[11 Jan 2016 9:32] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.6.28/5.7.10 are affected.

Thanks,
Umesh