| Bug #56271 | STR_TO_DATE in date compare incorrect results | ||
|---|---|---|---|
| Submitted: | 25 Aug 2010 20:32 | Modified: | 12 Nov 2010 0:52 |
| Reporter: | Tobin Cataldo | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.5.5-m3-log | OS: | Windows (XP Pro) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
| Tags: | date, regression, STR_TO_DATE | ||
[25 Aug 2010 23:20]
MySQL Verification Team
Thank you for the bug report.
[miguel@tikal ~]$ dbs/5.5/bin/mysql -uroot d2
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.99-m4-debug-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `datetest` (
-> `year` int(4) NOT NULL,
-> `month` int(2) NOT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.22 sec)
mysql>
mysql> INSERT INTO `datetest` VALUES
-> (2010,1),(2010,2),(2010,3),(2010,4),(2009,8),(2009,9),(2009,10),(2009,11),(2009,12);
Query OK, 9 rows affected (0.17 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> SELECT *
-> FROM datetest
-> WHERE STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') >=
-> STR_TO_DATE('1/1/2010', '%m/%d/%Y')
-> AND STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') <
-> DATE_ADD(STR_TO_DATE('2/1/2010', '%m/%d/%Y'), INTERVAL 1 MONTH)
-> ;
+------+-------+
| year | month |
+------+-------+
| 2010 | 1 |
| 2010 | 2 |
| 2009 | 8 |
| 2009 | 9 |
| 2009 | 10 |
| 2009 | 11 |
| 2009 | 12 |
+------+-------+
7 rows in set (0.00 sec)
[1 Sep 2010 17:29]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117362 3199 Evgeny Potemkin 2010-09-01 Bug#56271: Wrong comparison result with STR_TO_DATE function The Item_func_str_to_date class wasn't providing correct integer DATETIME representation as expected. This led to wrong comparison result and didn't allowed the STR_TO_DATE function to be used with indexes. val_int and result_as_longlong methods were added to the Item_func_str_to_date class. @ mysql-test/r/select.result A test case result adjusted after fixing bug#56271. @ mysql-test/r/type_datetime.result Added a test case for the bug#56271. @ mysql-test/t/type_datetime.test Added a test case for the bug#56271. @ sql/item_timefunc.cc Bug#56271: Wrong comparison result with STR_TO_DATE function val_int and result_as_longlong methods were added to the Item_func_str_to_date class. @ sql/item_timefunc.h Bug#56271: Wrong comparison result with STR_TO_DATE function val_int and result_as_longlong methods were added to the Item_func_str_to_date class.
[6 Sep 2010 7:50]
Øystein Grøvlen
Approved
[6 Sep 2010 13:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117617 3199 Evgeny Potemkin 2010-09-06 Bug#56271: Wrong comparison result with STR_TO_DATE function The Item_func_str_to_date class wasn't providing correct integer DATETIME representation as expected. This led to wrong comparison result and didn't allowed the STR_TO_DATE function to be used with indexes. Also, STR_TO_DATE function was inconsisted on throwing warnings/errors. Fixed now. val_int and result_as_longlong methods were added to the Item_func_str_to_date class. @ mysql-test/r/func_time.result Test case result adjusted after fixing bug#56271. @ mysql-test/r/parser.result Test case result adjusted after fixing bug#56271. @ mysql-test/r/select.result A test case result adjusted after fixing bug#56271. @ mysql-test/r/strict.result Test case result adjusted after fixing bug#56271. @ mysql-test/r/type_datetime.result Added a test case for the bug#56271. @ mysql-test/t/strict.test Test case adjusted after fixing bug#56271. @ mysql-test/t/type_datetime.test Added a test case for the bug#56271. @ sql-common/my_time.c Bug#56271: Wrong comparison result with STR_TO_DATE function Adjusted comments. @ sql/item_timefunc.cc Bug#56271: Wrong comparison result with STR_TO_DATE function val_int and result_as_longlong methods were added to the Item_func_str_to_date class. Item_func_str_to_date::get_date now throws the ER_WRONG_VALUE_FOR_TYPE warning on incorrect value. @ sql/item_timefunc.h Bug#56271: Wrong comparison result with STR_TO_DATE function val_int and result_as_longlong methods were added to the Item_func_str_to_date class. @ sql/sql_time.cc Bug#56271: Wrong comparison result with STR_TO_DATE function The date_add_interval function was adjusted to correctly work with MYSQL_TIME structure which was created from a TIME value.
[7 Sep 2010 6:46]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/117658 3199 Evgeny Potemkin 2010-09-07 Bug#56271: Wrong comparison result with STR_TO_DATE function The Item_func_str_to_date class wasn't providing correct integer DATETIME representation as expected. This led to wrong comparison result and didn't allowed the STR_TO_DATE function to be used with indexes. Also, STR_TO_DATE function was inconsisted on throwing warnings/errors. Fixed now. val_int and result_as_longlong methods were added to the Item_func_str_to_date class. @ mysql-test/r/func_time.result Test case result adjusted after fixing bug#56271. @ mysql-test/r/parser.result Test case result adjusted after fixing bug#56271. @ mysql-test/r/select.result A test case result adjusted after fixing bug#56271. @ mysql-test/r/strict.result Test case result adjusted after fixing bug#56271. @ mysql-test/r/type_datetime.result Added a test case for the bug#56271. @ mysql-test/t/strict.test Test case adjusted after fixing bug#56271. @ mysql-test/t/type_datetime.test Added a test case for the bug#56271. @ sql/item_timefunc.cc Bug#56271: Wrong comparison result with STR_TO_DATE function val_int and result_as_longlong methods were added to the Item_func_str_to_date class. Item_func_str_to_date::get_date now throws the ER_WRONG_VALUE_FOR_TYPE warning on incorrect value. @ sql/item_timefunc.h Bug#56271: Wrong comparison result with STR_TO_DATE function val_int and result_as_longlong methods were added to the Item_func_str_to_date class.
[2 Oct 2010 18:12]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alexander.nozdrin@oracle.com-20101002180948-852x1cuv7c6i85ea) (version source revid:alexander.nozdrin@oracle.com-20101002180857-an32jpuwzemsp4f2) (merge vers: 5.6.1-m4) (pib:21)
[2 Oct 2010 18:14]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101002181053-6iotvl26uurcoryp) (version source revid:alexander.nozdrin@oracle.com-20101002180917-h0n62akupm3z20nt) (pib:21)
[2 Oct 2010 18:16]
Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alexander.nozdrin@oracle.com-20101002180831-590ka2tuit9qoxbb) (version source revid:alexander.nozdrin@oracle.com-20101002180831-590ka2tuit9qoxbb) (merge vers: 5.5.7-rc) (pib:21)
[18 Oct 2010 9:44]
MySQL Verification Team
Evgeny, is it possible this fix causes bug #57512 ?
[19 Oct 2010 14:00]
MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=57564 marked as duplicate of this one.
[12 Nov 2010 0:52]
Paul DuBois
Noted in 5.5.7, 5.6.1 changelogs. Comparison of one STR_TO_DATE() result with another could return incorrect results.
Description: A STR_TO_DATE comparison to another STR_TO_DATE in the WHERE returns incorrect results unless another DATE function (i.e. DATE_SUB or DATE_ADD) is used. SQL_Mode is STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION How to repeat: Please examine this table : CREATE TABLE `datetest` ( `year` int(4) NOT NULL, `month` int(2) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `datetest` VALUES (2010,1),(2010,2),(2010,3),(2010,4),(2009,8),(2009,9),(2009,10),(2009,11),(2009,12); ============== The following query provides incorrect results SELECT * FROM datetest WHERE STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') >= STR_TO_DATE('1/1/2010', '%m/%d/%Y') AND STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') < DATE_ADD(STR_TO_DATE('2/1/2010', '%m/%d/%Y'), INTERVAL 1 MONTH) -- Output --- year month 2010 1 2010 2 2009 8 2009 9 2009 10 2009 11 2009 12 =============== However, if I add another DATE function to the STR_TO_DATE the results are then correct SELECT * FROM datetest WHERE STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') > DATE_SUB(STR_TO_DATE('1/1/2010', '%m/%d/%Y'), INTERVAL 1 MONTH) AND STR_TO_DATE(CONCAT_WS('/01/',`month`,`year`), '%m/%d/%Y') < DATE_ADD(STR_TO_DATE('2/1/2010', '%m/%d/%Y'), INTERVAL 1 MONTH) -- Output -- year month 2010 1 2010 2 ============ Using in STR_TO_DATE in the SELECT returns correct results. SELECT IF (STR_TO_DATE(CONCAT_WS('/01/','3','2009'), '%m/%d/%Y') >= STR_TO_DATE('1/1/2010', '%m/%d/%Y'), "yes", "no"); -- OUTPUT -- no