MySQL STR_TO_DATE gives invalid results?

Question

I'm using MySQL 5.1.49 on Win64. We're seeing the following behaviour on Solaris machines as well. Here's my test table:

CREATE TABLE `date_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date1` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Index_2` (`date1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

mysql> select * from date_test;
+----+---------------------+
| id | date1               |
+----+---------------------+
|  1 | 2010-09-01 01:00:00 |
|  2 | 2010-09-06 23:59:59 |
|  3 | 2010-09-07 01:00:00 |
+----+---------------------+
3 rows in set (0.00 sec)

The following query runs correctly:

mysql> SELECT * FROM date_test WHERE
           date1 > DATE_SUB('2010-10-06 23:59:59', interval 1 month);
+----+---------------------+
| id | date1               |
+----+---------------------+
|  3 | 2010-09-07 01:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

However, if I throw a STR_TO_DATE() in there, the query does not function as expected:

mysql> SELECT * FROM date_test WHERE
         date1 > DATE_SUB(
            STR_TO_DATE('10/06/2010 23:59:59', '%m/%d/%Y %T'), interval 1 month
         );
+----+---------------------+
| id | date1               |
+----+---------------------+
|  1 | 2010-09-01 01:00:00 |
|  2 | 2010-09-06 23:59:59 |
|  3 | 2010-09-07 01:00:00 |
+----+---------------------+
3 rows in set (0.00 sec)

There is very bizarre behaviour happening here. You can use any date there with the STR_TO_DATE() function and that query will return all records in the table (something far in the future even). The documentation indicates that STR_TO_DATE() should be returning a DATETIME, which should be a valid input to DATE_SUB(), but something is obviously amiss.

As a side note, running SELECT STR_TO_DATE('10/06/2010 23:59:59', '%m/%d/%Y %T') returns the exact input of the first query, 2010-10-06 23:59:59. Also, if you wrap the results of the STR_TO_DATE() function in the problem query with the TIMESTAMP() function or a CAST(STR_TO_DATE() AS DATETIME), then the results are returned as expected. But should this really be necessary?

I'd like to think I'm missing something here. Can anyone shed any light?

UPDATE

This is definitely looking like a bug. On version 5.1.36:

-- interval of month or day does not work:
SELECT '1999-04-01 23:34:12' > DATE_SUB(STR_TO_DATE('09/06/2010 23:59:59', '%m/%d/%Y %T'), INTERVAL 1 MONTH);
--->  1   (incorrect)
-- using 720 hours (30 days) works:
SELECT '1999-04-01 23:34:12' > DATE_SUB(STR_TO_DATE('09/06/2010 23:59:59', '%m/%d/%Y %T'), INTERVAL 720 HOUR);
--->  0
-- wrapping in TIMESTAMP( ) works:
SELECT '1999-04-01 23:34:12' > DATE_SUB(TIMESTAMP(STR_TO_DATE('09/06/2010 23:59:59', '%m/%d/%Y %T')), INTERVAL 1 MONTH);
--->  0

On version 5.0.51a-log, all work as expected:

SELECT '1999-04-01 23:34:12' > DATE_SUB(STR_TO_DATE('09/06/2010 23:59:59', '%m/%d/%Y %T'), INTERVAL 1 MONTH);
--->  0  
SELECT '1999-04-01 23:34:12' > DATE_SUB(STR_TO_DATE('09/06/2010 23:59:59', '%m/%d/%Y %T'), INTERVAL 720 HOUR);
--->  0
SELECT '1999-04-01 23:34:12' > DATE_SUB(TIMESTAMP(STR_TO_DATE('09/06/2010 23:59:59', '%m/%d/%Y %T')), INTERVAL 1 MONTH);
--->  0

Answer 1

This is really strange and seems to be a bug.

I managed to boil it down to this:

SELECT  '0000-00-00 00:00:20' > STR_TO_DATE('10/06/2010 23:59:59', '%m/%d/%Y %T') - INTERVAL 1 MONTH,
        '0000-00-00 00:00:21' > STR_TO_DATE('10/06/2010 23:59:59', '%m/%d/%Y %T') - INTERVAL 1 MONTH

, which means that the year in the second expression gets compared to seconds in the first one.

There is probably some weird DATE to INTEGER cast in between.

Note that if you add + INTERVAL 0 SECONDS, the expression gets casted into a proper DATETIME and works well.

I'll post it as a bug to MySQL.

Update:

This has already been submitted as a bug and a patch was released a week ago to fix it.