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:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.5-m3-log OS:Microsoft Windows (XP Pro)
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: date, regression, STR_TO_DATE

[25 Aug 2010 20:32] Tobin Cataldo
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
[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.