Bug #50484 Mysql does not manage dates correctly when using DATE_ADD and STR_TO_DATE

• Submitted: 20 Jan 2010 19:20
• Modified: 21 Jan 2010 13:02
• Reporter: Syl Clyl
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.1.42
• OS: Any
• CPU Architecture: Any
• Tags: Dates;DATE_ADD;STR_TO_ADD

[20 Jan 2010 19:20] Syl Clyl

Description:
I guess I shouldn't get 'MYSQL SUCKS' with that request :

SELECT CASE WHEN STR_TO_DATE('01-04-2010 00:00:00', '%d-%m-%Y %H:%i:%S') <= DATE_ADD(STR_TO_DATE('01-04-2010 00:00:00', '%d-%m-%Y %H:%i:%S'), INTERVAL 3 YEAR) = 0 THEN 'MYSQL SUCKS' ELSE 'MYSQL ROCKS' END as DoesMysqlRocks

How to repeat:
Just execute the provided SQL

[21 Jan 2010 6:29] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You have not needed ' = 0' which creates FALSE result for WHEN.

Compare:

mysql> SELECT CASE WHEN STR_TO_DATE('01-04-2010 00:00:00', '%d-%m-%Y %H:%i:%S') <= DATE_ADD(STR_TO_DATE('01-04-2010 00:00:00', '%d-%m-%Y %H:%i:%S'), INTERVAL 3 YEAR)  THEN 'MYSQL SUCKS' ELSE 'MYSQL ROCKS' END as DoesMysqlRocks;
+----------------+
| DoesMysqlRocks |
+----------------+
| MYSQL ROCKS    |
+----------------+
1 row in set (0.00 sec)

[21 Jan 2010 13:02] Syl Clyl

You're right sorry for the incorrect example.
The bug exist I don't know in which circustomences the bug appears, I only know it appears when you use DATE_ADD around STR_TO_DATE, but we were able to handle it this way : CONVERT(DATE_ADD(STR_TO_DATE("", ""), INTERVAL 3 YEAR), DATE) 

Thank you anyway.