Bug #47925 regression of range optimizer and date comparison in 5.1.39!
Submitted: 8 Oct 2009 20:50 Modified: 12 Mar 2010 17:17
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S1 (Critical)
Version:5.1.39 OS:Any
Assigned to: Martin Hansson CPU Architecture:Any
Tags: regression

[8 Oct 2009 20:50] Shane Bester
Description:
5.1.39 returned different results for testcase, which involves comparing an indexed date column of innodb table and converting a date value in where clause from yyyymmdd to yyyy-mm-dd format.

5.1.39:
mysql>
mysql> select * from `t1` force  index(`a`)
    -> where `a`>='20090923' and `a`<= '20090929' and `b`=2;
Empty set (0.00 sec)

mysql>
mysql> select * from `t1` force  index(`a`)
    -> where `a`>='2009-09-23' and `a`<= '2009-09-29' and `b`=2;
+------------+------+
| a          | b    |
+------------+------+
| 2009-09-23 |    2 |
+------------+------+
1 row in set (0.00 sec)

5.1.38:
mysql> select * from `t1` force  index(`a`)
    -> where `a`>='20090923' and `a`<= '20090929' and `b`=2;
+------------+------+
| a          | b    |
+------------+------+
| 2009-09-23 |    2 |
+------------+------+
1 row in set (0.00 sec)

mysql>
mysql> select * from `t1` force  index(`a`)
    -> where `a`>='2009-09-23' and `a`<= '2009-09-29' and `b`=2;
+------------+------+
| a          | b    |
+------------+------+
| 2009-09-23 |    2 |
+------------+------+
1 row in set (0.00 sec)

myisam tables worked as expected for this testcase.

How to repeat:
drop table if exists `t1`;
create table `t1` (`a` date,`b` int,key `a`(`a`)) engine=innodb;
insert into `t1` values ('2009-09-23',2);

select * from `t1` force  index(`a`)  
where `a`>='20090923' and `a`<= '20090929' and `b`=2;

select * from `t1` force  index(`a`)  
where `a`>='2009-09-23' and `a`<= '2009-09-29' and `b`=2;

alter table t1 engine=myisam;

select * from `t1` force  index(`a`)  
where `a`>='20090923' and `a`<= '20090929' and `b`=2;

select * from `t1` force  index(`a`)  
where `a`>='2009-09-23' and `a`<= '2009-09-29' and `b`=2;
[8 Oct 2009 21:18] MySQL Verification Team
Another testcase showing similar problems in 5.1.39...

drop table if exists `t1`;
create table `t1` ( `dt` date, key `dt` (`dt`)) engine=myisam;

insert into `t1` values ('2009-09-22');
insert into `t1` values ('2009-09-22');
insert into `t1` values ('2009-09-22');
insert into `t1` values ('2009-09-23');
insert into `t1` values ('2009-09-23');
insert into `t1` values ('2009-09-23');

select count(*) from `t1` where dt >= '2009/09/23';  #returns 0
select count(*) from `t1` where dt >= '2009-09-23';  #returns 3
select count(*) from `t1` where dt >= '20090923';    #returns 0
[9 Oct 2009 12:03] NA NA
Same problem here.
[13 Oct 2009 15:34] 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/86698

3162 Martin Hansson	2009-10-13
      Bug#47925: regression of range optimizer and date comparison in 5.1.39!
      
      When a query was using a DATE or DATETIME value formatted
      using other separator characters than the most commonly used
      hyphen '-', a query with a greater-or-equal '>=' condition
      matching only the greatest value in an indexed column, the
      result was empty if index range scan was employed.
      
      The range optimizer got a new feature between 5.1.38 and
      5.1.39 that changes a greater-or-equal condition to a
      greater-than if the value matching that in the query was not
      present in the table. But the value comparison function
      compared the dates as strings instead of dates.
      
      The bug was fixed by parsing the date strings and comparing
      the integer representation of the dates. 
     @ mysql-test/r/range.result
        Bug#47925: Test result
     @ mysql-test/t/range.test
        Bug#47925: Test case and the obligatory "End of 5.1 tests"
     @ sql/item.cc
        Bug#47925: Fix + some edit on the comments
[15 Oct 2009 12: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/86959

3171 Martin Hansson	2009-10-15
      Bug#47925: regression of range optimizer and date comparison in 5.1.39!
      
      When a query was using a DATE or DATETIME value formatted
      using other separator characters than the most commonly used
      hyphen '-', a query with a greater-or-equal '>=' condition
      matching only the greatest value in an indexed column, the
      result was empty if index range scan was employed.
      
      The range optimizer got a new feature between 5.1.38 and
      5.1.39 that changes a greater-or-equal condition to a
      greater-than if the value matching that in the query was not
      present in the table. But the value comparison function
      compared the dates as strings instead of dates.
      
      The bug was fixed by parsing the date strings and comparing
      the integer representation of the dates.
     @ mysql-test/r/range.result
        Bug#47925: Test result
     @ mysql-test/t/range.test
        Bug#47925: Test case and the obligatory "End of 5.1 tests"
     @ sql/item.cc
        Bug#47925: Fix + some edit on the comments
[19 Oct 2009 19:24] MySQL Verification Team
bug #48168 was marked as a duplicate of this
[23 Oct 2009 11:36] 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/87925

3192 Martin Hansson	2009-10-23
      Bug#47925: regression of range optimizer and date comparison in 5.1.39!
      
      Intermediate patch for discussion.
[26 Oct 2009 10:42] 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/88131

3192 Martin Hansson	2009-10-26
      Bug#47925: regression of range optimizer and date comparison in 5.1.39!
      
      When a query was using a DATE or DATETIME value formatted
      using other separator characters than the most commonly used
      hyphen '-', a query with a greater-or-equal '>=' condition
      matching only the greatest value in an indexed column, the
      result was empty if index range scan was employed.
      
      The range optimizer got a new feature between 5.1.38 and
      5.1.39 that changes a greater-or-equal condition to a
      greater-than if the value matching that in the query was not
      present in the table. But the value comparison function
      compared the dates as strings instead of dates.
      
      Fixed by splitting get_date_from_str so that MYSQL_TIME objects can be
      compared.
     @ mysql-test/r/range.result
        Bug#47925: Test result
     @ mysql-test/t/range.test
        Bug#47925: Test case and the obligatory "End of 5.1 tests"
     @ sql/item.cc
        Bug#47925: Fix + some edit on the comments
     @ sql/item.h
        Bug#47925: Changed function signature
     @ sql/item_cmpfunc.cc
        Bug#47925: Split function into two
     @ sql/item_cmpfunc.h
        Bug#47925: Declaration of new function
     @ sql/opt_range.cc
        Bug#47925: Added THD to function call
     @ sql/time.cc
        Bug#47925: Added microsecond comparison
[27 Oct 2009 11:10] 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/88318

3200 Martin Hansson	2009-10-27
      Bug#47925: regression of range optimizer and date comparison in 5.1.39!
      
      When a query was using a DATE or DATETIME value formatted
      using any other separator characters beside hyphen '-', a
      query with a greater-or-equal '>=' condition matching only
      the greatest value in an indexed column, the result was
      empty if index range scan was employed.
      
      The range optimizer got a new feature between 5.1.38 and
      5.1.39 that changes a greater-or-equal condition to a
      greater-than if the value matching that in the query was not
      present in the table. But the value comparison function
      compared the dates as strings instead of dates.
      
      The bug was fixed by splitting the function
      get_date_from_str in two: One part that parses and does
      error checking. This function is now visible outside the
      module. The old get_date_from_str now calls the new
      function.
     @ mysql-test/r/range.result
        Bug#47925: Test result
     @ mysql-test/r/select.result
        Bug#47925: Wrong test result turned correct. When an endpoint is missing, the only way to be sure that a value is in the range is if it is equal to the present endpoint.
     @ mysql-test/t/range.test
        Bug#47925: Test case
     @ sql/item.cc
        Bug#47925: Fix + some edit on the comments
     @ sql/item.h
        Bug#47925: Changed function signature
     @ sql/item_cmpfunc.cc
        Bug#47925: Split function in two
     @ sql/item_cmpfunc.h
        Bug#47925: Declaration of new function
     @ sql/opt_range.cc
        Bug#47925: Added THD to function call
     @ sql/time.cc
        Bug#47925: Added microsecond comparison
[27 Oct 2009 14:05] 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/88376

3200 Martin Hansson	2009-10-27
      Bug#47925: regression of range optimizer and date comparison in 5.1.39!
      
      When a query was using a DATE or DATETIME value formatted
      using any other separator characters beside hyphen '-', a
      query with a greater-or-equal '>=' condition matching only
      the greatest value in an indexed column, the result was
      empty if index range scan was employed.
      
      The range optimizer got a new feature between 5.1.38 and
      5.1.39 that changes a greater-or-equal condition to a
      greater-than if the value matching that in the query was not
      present in the table. But the value comparison function
      compared the dates as strings instead of dates.
      
      The bug was fixed by splitting the function
      get_date_from_str in two: One part that parses and does
      error checking. This function is now visible outside the
      module. The old get_date_from_str now calls the new
      function.
     @ mysql-test/r/range.result
        Bug#47925: Test result
     @ mysql-test/t/range.test
        Bug#47925: Test case
     @ sql/item.cc
        Bug#47925: Fix + some edit on the comments
     @ sql/item.h
        Bug#47925: Changed function signature
     @ sql/item_cmpfunc.cc
        Bug#47925: Split function in two
     @ sql/item_cmpfunc.h
        Bug#47925: Declaration of new function
     @ sql/opt_range.cc
        Bug#47925: Added THD to function call
     @ sql/time.cc
        Bug#47925: Added microsecond comparison
[30 Oct 2009 10:33] 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/88723

3166 Martin Hansson	2009-10-30
      Bug#47925: regression of range optimizer and date comparison in 5.1.39!
      
      When a query was using a DATE or DATETIME value formatted
      using any other separator characters beside hyphen '-', a
      query with a greater-or-equal '>=' condition matching only
      the greatest value in an indexed column, the result was
      empty if index range scan was employed.
      
      The range optimizer got a new feature between 5.1.38 and
      5.1.39 that changes a greater-or-equal condition to a
      greater-than if the value matching that in the query was not
      present in the table. But the value comparison function
      compared the dates as strings instead of dates.
      
      The bug was fixed by splitting the function
      get_date_from_str in two: One part that parses and does
      error checking. This function is now visible outside the
      module. The old get_date_from_str now calls the new
      function.
     @ mysql-test/r/range.result
        Bug#47925: Test result
     @ mysql-test/t/range.test
        Bug#47925: Test case
     @ sql/item.cc
        Bug#47925: Fix + some edit on the comments
     @ sql/item.h
        Bug#47925: Changed function signature
     @ sql/item_cmpfunc.cc
        Bug#47925: Split function in two
     @ sql/item_cmpfunc.h
        Bug#47925: Declaration of new function
     @ sql/opt_range.cc
        Bug#47925: Added THD to function call
     @ sql/time.cc
        Bug#47925: Added microsecond comparison
[2 Nov 2009 10:51] 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/88916

3160 Martin Hansson	2009-11-02
      Bug#47925: regression of range optimizer and date comparison in 5.1.39!
      
      When a query was using a DATE or DATETIME value formatted
      using any other separator characters beside hyphen '-', a
      query with a greater-or-equal '>=' condition matching only
      the greatest value in an indexed column, the result was
      empty if index range scan was employed.
      
      The range optimizer got a new feature between 5.1.38 and
      5.1.39 that changes a greater-or-equal condition to a
      greater-than if the value matching that in the query was not
      present in the table. But the value comparison function
      compared the dates as strings instead of dates.
      
      The bug was fixed by splitting the function
      get_date_from_str in two: One part that parses and does
      error checking. This function is now visible outside the
      module. The old get_date_from_str now calls the new
      function.
     @ mysql-test/r/range.result
        Bug#47925: Test result
     @ mysql-test/t/range.test
        Bug#47925: Test case
     @ sql/item.cc
        Bug#47925: Fix + some edit on the comments
     @ sql/item.h
        Bug#47925: Changed function signature
     @ sql/item_cmpfunc.cc
        Bug#47925: Split function in two
     @ sql/item_cmpfunc.h
        Bug#47925: Declaration of new function
     @ sql/opt_range.cc
        Bug#47925: Added THD to function call
     @ sql/time.cc
        Bug#47925: Added microsecond comparison
[2 Nov 2009 12:24] 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/88926

3161 Martin Hansson	2009-11-02
      Bug#47925: regression of range optimizer and date comparison in 5.1.39!
      
      When a query was using a DATE or DATETIME value formatted
      using any other separator characters beside hyphen '-', a
      query with a greater-or-equal '>=' condition matching only
      the greatest value in an indexed column, the result was
      empty if index range scan was employed.
      
      The range optimizer got a new feature between 5.1.38 and
      5.1.39 that changes a greater-or-equal condition to a
      greater-than if the value matching that in the query was not
      present in the table. But the value comparison function
      compared the dates as strings instead of dates.
      
      The bug was fixed by splitting the function
      get_date_from_str in two: One part that parses and does
      error checking. This function is now visible outside the
      module. The old get_date_from_str now calls the new
      function.
     @ mysql-test/r/range.result
        Bug#47925: Test result
     @ mysql-test/t/range.test
        Bug#47925: Test case
     @ sql/item.cc
        Bug#47925: Fix + some edit on the comments
     @ sql/item.h
        Bug#47925: Changed function signature
     @ sql/item_cmpfunc.cc
        Bug#47925: Split function in two
     @ sql/item_cmpfunc.h
        Bug#47925: Declaration of new function
     @ sql/opt_range.cc
        Bug#47925: Added THD to function call
     @ sql/time.cc
        Bug#47925: Added microsecond comparison
[3 Nov 2009 9:04] 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/89039

3653 Martin Hansson	2009-11-03 [merge]
      Merge of fix for Bug#47925
[4 Nov 2009 9:25] Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)
[11 Nov 2009 6:51] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 2009 6:59] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105110316-pn162zoddxj89lbh) (merge vers: 5.5.0-beta) (pib:13)
[22 Nov 2009 0:21] Paul Dubois
Noted in 5.1.41, 5.5.0, 6.0.14 changelogs.

When a query used a DATE or DATETIME value formatted using any
separator characters other than hyphen ('-') and a >= condition
matching only the greatest value in an indexed column, the result was
empty if an index range scan was employed.
[7 Dec 2009 16:43] Paul Dubois
Noted in 5.1.40sp1 changelog.
[8 Dec 2009 9:30] Bugs System
Pushed into 5.1.43 (revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (version source revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (merge vers: 5.1.43) (pib:13)
[16 Dec 2009 8:35] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091215065750-5m04ogppd5l0pol5) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:42] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alik@sun.com-20091211070127-kl8uvlrv9cr11kva) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:49] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 10:27] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:43] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 10:58] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:13] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[12 Mar 2010 14:07] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:23] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:37] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)