From: Date: November 20 2007 3:03am Subject: bk commit into 5.1 tree (sergefp:1.2633) BUG#30573 List-Archive: http://lists.mysql.com/commits/38107 X-Bug: 30573 Message-Id: <20071120020302.7BEA4760E1F@foxhole.localdomain> Below is the list of changes that have just been committed into a local 5.1 repository of psergey. When psergey does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-11-20 05:02:49+03:00, sergefp@stripped +3 -0 BUG#30573: Ordered range scan over partitioned tables returns some rows twice The problem: ha_partition::read_range_first() could return a record that is outside of the scanned range. If that record happened to be in the next subsequent range, it would satisfy the WHERE and appear in the output twice. (we would get it the second time when scanning the next subsequent range) Fix: Made ha_partition::read_range_first() check if the returned recod is within the scanned range, like other read_range_first() implementations do. mysql-test/r/partition_range.result@stripped, 2007-11-20 05:02:43+03:00, sergefp@stripped +21 -1 BUG#30573: Ordered range scan over partitioned tables returns some rows twice - Testcase mysql-test/t/partition_range.test@stripped, 2007-11-20 05:02:43+03:00, sergefp@stripped +27 -1 BUG#30573: Ordered range scan over partitioned tables returns some rows twice - Testcase sql/ha_partition.cc@stripped, 2007-11-20 05:02:43+03:00, sergefp@stripped +1 -1 BUG#30573: Ordered range scan over partitioned tables returns some rows twice - Make ha_partition::read_range_first() check if the returned record is within the range. diff -Nrup a/mysql-test/r/partition_range.result b/mysql-test/r/partition_range.result --- a/mysql-test/r/partition_range.result 2007-06-13 19:28:57 +04:00 +++ b/mysql-test/r/partition_range.result 2007-11-20 05:02:43 +03:00 @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1, t2; create table t1 (a int unsigned) partition by range (a) (partition pnull values less than (0), @@ -709,3 +709,23 @@ WHERE (a >= '2004-07-01' AND a <= '2004- id select_type table partitions type possible_keys key key_len ref rows Extra 1 SIMPLE t1 p407,p408,p409,p507,p508,p509 ALL NULL NULL NULL NULL 18 Using where DROP TABLE t1; +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 ( +defid int(10) unsigned NOT NULL, +day int(10) unsigned NOT NULL, +count int(10) unsigned NOT NULL, +filler char(200), +KEY (defid,day) +) +PARTITION BY RANGE (day) ( +PARTITION p7 VALUES LESS THAN (20070401) , +PARTITION p8 VALUES LESS THAN (20070501)); +insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B; +insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B; +insert into t2 values(52, 20070321, 123, 'filler') ; +insert into t2 values(52, 20070322, 456, 'filler') ; +select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid; +sum(count) +579 +drop table t1, t2; diff -Nrup a/mysql-test/t/partition_range.test b/mysql-test/t/partition_range.test --- a/mysql-test/t/partition_range.test 2007-06-13 19:28:57 +04:00 +++ b/mysql-test/t/partition_range.test 2007-11-20 05:02:43 +03:00 @@ -6,7 +6,7 @@ -- source include/have_partition.inc --disable_warnings -drop table if exists t1; +drop table if exists t1, t2; --enable_warnings # @@ -757,3 +757,29 @@ DROP TABLE t1; # a = "C2345678901234567890"; #select * from t1 where a = "12345678901234567890"; #drop table t1; + + +# +# BUG#30573: get wrong result with "group by" on PARTITIONed table +# +create table t1 (a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 ( + defid int(10) unsigned NOT NULL, + day int(10) unsigned NOT NULL, + count int(10) unsigned NOT NULL, + filler char(200), + KEY (defid,day) +) +PARTITION BY RANGE (day) ( + PARTITION p7 VALUES LESS THAN (20070401) , + PARTITION p8 VALUES LESS THAN (20070501)); + +insert into t2 select 20, 20070311, 1, 'filler' from t1 A, t1 B; +insert into t2 select 20, 20070411, 1, 'filler' from t1 A, t1 B; +insert into t2 values(52, 20070321, 123, 'filler') ; +insert into t2 values(52, 20070322, 456, 'filler') ; + +select sum(count) from t2 ch where ch.defid in (50,52) and ch.day between 20070320 and 20070401 group by defid; +drop table t1, t2; + diff -Nrup a/sql/ha_partition.cc b/sql/ha_partition.cc --- a/sql/ha_partition.cc 2007-11-14 16:28:18 +03:00 +++ b/sql/ha_partition.cc 2007-11-20 05:02:43 +03:00 @@ -3838,7 +3838,7 @@ int ha_partition::read_range_first(const start_key->key, start_key->keypart_map, start_key->flag); } - DBUG_RETURN(error); + DBUG_RETURN (error? error: compare_key(end_range) <= 0 ? 0 : HA_ERR_END_OF_FILE); }