Bug #79908 date comparison has inconsistent behavior between = and CASE WHEN

• Submitted: 11 Jan 2016 4:47
• Modified: 9 Dec 2019 21:21
• Reporter: Su Dylan
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8, 5.5.48, 5.6.28, 5.7.10
• OS: Any
• CPU Architecture: Any

[11 Jan 2016 4:47] Su Dylan

Description:
Output:
=====
mysql> create table t1(c1 date);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('2016-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select c1, STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s'), c1 = STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s'), CASE c1 WHEN STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s') THEN 'EQUAL' ELSE 'NOT-EQUAL' END from t1;
+------------+---------------------------------------+--------------------------------------------+--------------------------------------------------------------------------------------+
| c1         | STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s') | c1 = STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s') | CASE c1 WHEN STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s') THEN 'EQUAL' ELSE 'NOT-EQUAL' END |
+------------+---------------------------------------+--------------------------------------------+--------------------------------------------------------------------------------------+
| 2016-01-01 | 2016-01-01 00:00:00                   |                                          1 | NOT-EQUAL                                                                            |
+------------+---------------------------------------+--------------------------------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

Problem:
=====
"CASE c1 WHEN STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s') THEN 'EQUAL' ELSE 'NOT-EQUAL' END" is expected to return 'EQUAL'.

How to repeat:

drop table if exists t1;
create table t1(c1 date);
insert into t1 values('2016-01-01');
select c1, STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s'), c1 = STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s'), CASE c1 WHEN STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s') THEN 'EQUAL' ELSE 'NOT-EQUAL' END from t1;

Suggested fix:
"CASE c1 WHEN STR_TO_DATE(c1 , '%Y-%m-%d %H:%i:%s') THEN 'EQUAL' ELSE 'NOT-EQUAL' END" returns 'EQUAL'.

[11 Jan 2016 7:17] MySQL Verification Team

Hello Su Dylan,

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

Thanks,
Umesh

[9 Dec 2019 21:21] Roy Lyseng

Posted by developer:
 
Fixed in 5.7.29