From: Date: August 20 2008 3:42pm Subject: bzr commit into mysql-5.1 branch (holyfoot:2683) Bug#38005 List-Archive: http://lists.mysql.com/commits/52060 X-Bug: 38005 Message-Id: <20080820134215.BF7F52C380C0@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/ 2683 Alexey Botchkov 2008-08-20 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. Now partition engine doesn't call index_first/index_last 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 sql/ha_partition.cc Bug#38005 Partitions: error with insert select. ha_myisam::index_first and ha_myisam::index_last not called for empty tables. modified: mysql-test/r/partition.result mysql-test/t/partition.test sql/ha_partition.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-20 13:37:23 +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-20 13:37:23 +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 'sql/ha_partition.cc' --- a/sql/ha_partition.cc 2008-07-07 20:42:19 +0000 +++ b/sql/ha_partition.cc 2008-08-20 13:37:23 +0000 @@ -4103,6 +4103,14 @@ int ha_partition::handle_unordered_scan_ break; case partition_index_first: DBUG_PRINT("info", ("index_first on partition %d", i)); + /* MyISAM engine can fail if we call index_first() on empty table */ + if ((file->ha_table_flags() & HA_HAS_RECORDS|HA_STATS_RECORDS_IS_EXACT) && + !file->info(HA_STATUS_VARIABLE) && + file->records() == 0) + { + error= HA_ERR_END_OF_FILE; + break; + } error= file->index_first(buf); break; case partition_index_first_unordered: @@ -4195,10 +4203,26 @@ int ha_partition::handle_ordered_index_s m_start_key.flag); break; case partition_index_first: + /* MyISAM engine can fail if we call index_first() on empty table */ + if ((file->ha_table_flags() & HA_HAS_RECORDS|HA_STATS_RECORDS_IS_EXACT) && + !file->info(HA_STATUS_VARIABLE) && + file->records() == 0) + { + error= HA_ERR_END_OF_FILE; + break; + } error= file->index_first(rec_buf_ptr); reverse_order= FALSE; break; case partition_index_last: + /* MyISAM engine can fail if we call index_last() on empty table */ + if ((file->ha_table_flags() & HA_HAS_RECORDS|HA_STATS_RECORDS_IS_EXACT) && + !file->info(HA_STATUS_VARIABLE) && + file->records() == 0) + { + error= HA_ERR_END_OF_FILE; + break; + } error= file->index_last(rec_buf_ptr); reverse_order= TRUE; break;