From: Date: August 7 2008 6:58am Subject: bzr commit into mysql-5.1 branch (holyfoot:2708) Bug#38005 List-Archive: http://lists.mysql.com/commits/51073 X-Bug: 38005 Message-Id: <20080807045848.CB2132C380BB@hfmain.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #At file:///home/hf/work/mysql_common/38005/ 2708 Alexey Botchkov 2008-08-07 Bug#38005 Partitions: error with insert select. MyISAM blocks index usage for bulk insert into zero-records tables. See ha_myisam::start_bulk_insert() lines from ... if (file->state->records == 0 ... ... That causes problems for partition engine when some partitions have records some not as the engine uses same access method for all partitions. Fixed by returning EOF from ha_myisam::index_first for empty tables. per-file comments: mysql-test/r/partition.result Bug#38005 Partitions: error with insert select. test result mysql-test/t/partition.test Bug#38005 Partitions: error with insert select. test case storage/myisam/ha_myisam.cc Bug#38005 Partitions: error with insert select. ha_myisam::index_first and ha_myisam::index_last modified to return EOF if the table has no records. modified: mysql-test/r/partition.result mysql-test/t/partition.test storage/myisam/ha_myisam.cc === modified file 'mysql-test/r/partition.result' --- a/mysql-test/r/partition.result 2008-07-07 20:42:19 +0000 +++ b/mysql-test/r/partition.result 2008-08-07 04:51:43 +0000 @@ -1604,4 +1604,74 @@ t1 CREATE TABLE `t1` ( PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (id) SUBPARTITION BY HASH (id) SUBPARTITIONS 2 (PARTITION pa1 VALUES LESS THAN (10) ENGINE = MyISAM, PARTITION pa2 VALUES LESS THAN (20) ENGINE = MyISAM, PARTITION pa11 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ drop table t1; +create table t1 (s1 int) partition by hash(s1) partitions 2; +create index i on t1 (s1); +insert into t1 values (1); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +s1 +1 +1 +1 +1 +1 +1 +1 +1 +drop table t1; +create table t1 (s1 int) partition by range(s1) +(partition pa1 values less than (10), +partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (1); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +s1 +1 +1 +1 +1 +1 +1 +1 +1 +drop table t1; +create table t1 (s1 int) partition by range(s1) +(partition pa1 values less than (10), +partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (20); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +s1 +20 +20 +20 +20 +20 +20 +20 +20 +drop table t1; +create table t1 (s1 int) partition by range(s1) +(partition pa1 values less than (10), +partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +insert into t1 select s1 from t1 where s1=3; +select count(*) from t1; +count(*) +288 +drop table t1; End of 5.1 tests === modified file 'mysql-test/t/partition.test' --- a/mysql-test/t/partition.test 2008-06-17 10:41:06 +0000 +++ b/mysql-test/t/partition.test 2008-08-07 04:51:43 +0000 @@ -1767,4 +1767,54 @@ while ($n) --enable_query_log show create table t1; drop table t1; + +# +# Bug #38005 Partitions: error with insert select +# + +create table t1 (s1 int) partition by hash(s1) partitions 2; +create index i on t1 (s1); +insert into t1 values (1); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +drop table t1; + +create table t1 (s1 int) partition by range(s1) + (partition pa1 values less than (10), + partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (1); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +drop table t1; + +create table t1 (s1 int) partition by range(s1) + (partition pa1 values less than (10), + partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (20); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +select * from t1; +drop table t1; + +create table t1 (s1 int) partition by range(s1) + (partition pa1 values less than (10), + partition pa2 values less than MAXVALUE); +create index i on t1 (s1); +insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8); +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1; +insert into t1 select s1 from t1 order by s1 desc; +insert into t1 select s1 from t1 where s1=3; +select count(*) from t1; +drop table t1; + --echo End of 5.1 tests === modified file 'storage/myisam/ha_myisam.cc' --- a/storage/myisam/ha_myisam.cc 2008-03-28 10:14:27 +0000 +++ b/storage/myisam/ha_myisam.cc 2008-08-07 04:51:43 +0000 @@ -1653,7 +1653,9 @@ int ha_myisam::index_first(uchar *buf) { DBUG_ASSERT(inited==INDEX); ha_statistic_increment(&SSV::ha_read_first_count); - int error=mi_rfirst(file, buf, active_index); + + int error= (file->state->records == 0) ? + HA_ERR_END_OF_FILE : mi_rfirst(file, buf, active_index); table->status=error ? STATUS_NOT_FOUND: 0; return error; } @@ -1662,7 +1664,8 @@ int ha_myisam::index_last(uchar *buf) { DBUG_ASSERT(inited==INDEX); ha_statistic_increment(&SSV::ha_read_last_count); - int error=mi_rlast(file, buf, active_index); + int error= (file->state->records == 0) ? + HA_ERR_END_OF_FILE : mi_rlast(file, buf, active_index); table->status=error ? STATUS_NOT_FOUND: 0; return error; }