| 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: | |
| 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 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]
FN LN
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)
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;