bzr commit into mysql-5.1 branch (holyfoot:2708) Bug#38005

[Alexey Botchkov]

#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;
 }