Bug #80046 Inconsistent result returned for equivalent string input in CAST(str AS DATE)

• Submitted: 19 Jan 2016 9:18
• Modified: 19 Jan 2016 10:21
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any
• Tags: regression

[19 Jan 2016 9:18] Su Dylan

Description:
Output:
=====
mysql> SELECT CAST( NULLIF( SUBSTR('a',1), 'b' ) AS DATE ), CAST( 'a' AS DATE );
+----------------------------------------------+---------------------+
| CAST( NULLIF( SUBSTR('a',1), 'b' ) AS DATE ) | CAST( 'a' AS DATE ) |
+----------------------------------------------+---------------------+
| 0000-00-00                                   | NULL                |
+----------------------------------------------+---------------------+
1 row in set, 1 warning (0.00 sec)

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

Problem:
=====
NULL is expected for both.

How to repeat:
SELECT CAST( NULLIF( SUBSTR('a',1), 'b' ) AS DATE ), CAST( 'a' AS DATE );

Suggested fix:
NULL is returned for both.

[19 Jan 2016 10:21] MySQL Verification Team

Hello Su Dylan,

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

Thanks,
Umesh