MySQL STR_TO_DATE函数返回无效结果？

Question

我正在使用Win64上的MySQL 5.1.49。我们在Solaris机器上也看到了以下行为。这是我的测试表：

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)

以下查询正确运行：

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)

然而，如果我在其中加入一个STR_TO_DATE()，查询的结果就不如预期：

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)

这里出现了非常奇怪的行为。您可以使用任何日期和STR_TO_DATE()函数，这个查询将返回表格中的所有记录（甚至是未来很久以后的记录）。文档显示STR_TO_DATE()应该返回一个DATETIME，这应该是DATE_SUB()的有效输入，但显然有些问题。

顺便说一下，运行SELECT STR_TO_DATE('10/06/2010 23:59:59', '%m/%d/%Y %T')会返回第一个查询的确切输入2010-10-06 23:59:59。另外，如果您在问题查询中将STR_TO_DATE()函数的结果包装在TIMESTAMP()函数或CAST(STR_TO_DATE() AS DATETIME)中，则结果如预期返回。但这么做真的有必要吗？

我认为我可能漏掉了什么关键点。是否有人可以阐明一下？

更新：这绝对看起来像是一个错误。在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

在版本5.0.51a-log中，一切都按预期工作：

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

- zombat

---

请问您能否发布 SHOW CREATE TABLE date_test 的输出结果？ - Quassnoi

是的，这是一个 bug。请看我的帖子更新。 - Quassnoi

Answer 1

这真的很奇怪，似乎是一个 bug。

我设法将其简化为以下内容：

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

这意味着第二个表达式中的年份会与第一个表达式中的秒进行比较。

可能在两者之间进行了一些奇怪的DATE转换为INTEGER。

请注意，如果添加+ INTERVAL 0 SECONDS，则该表达式将被转换为适当的DATETIME并正常工作。

我将其提交为MySQL的错误报告。

更新：

这已经作为错误提交，并发布了一个补丁来修复它。

- Quassnoi