From: Date: October 6 2008 3:15pm
Subject: bzr commit into mysql-5.1 branch (holyfoot:2690) Bug#38005
List-Archive: http://lists.mysql.com/commits/55458
X-Bug: 38005
Message-Id: <20081006131541.4F5A42C380C0@hfmain.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///d2/hf/mysql_common/51mrg/

 2690 Alexey Botchkov	2008-10-06
      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_engine::index_first and
              ha_engine::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-08-15 18:26:25 +0000
+++ b/mysql-test/r/partition.result	2008-10-06 13:14:20 +0000
@@ -1637,4 +1637,74 @@ select count(*) from t1, t2 where t1.cre
 count(*)
 1
 drop table t1, t2;
+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-08-15 18:26:25 +0000
+++ b/mysql-test/t/partition.test	2008-10-06 13:14:20 +0000
@@ -1791,4 +1791,53 @@ select count(*) from t1, t2 where t1.cre
 
 drop table t1, t2;
 
+#
+# 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-10-03 09:30:54 +0000
+++ b/sql/ha_partition.cc	2008-10-06 13:14:20 +0000
@@ -4290,6 +4290,17 @@ 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() when indexes disabled */
+      /* that happens if the table is empty. */
+      /* Here we use file->stats.records instead of file->records() because */
+      /* file->records() is supposed to return an EXACT count, and it can be   */
+      /* possibly slow. We don't need an exact number, an approximate one- from*/
+      /* the last ::info() call - is sufficient. */
+      if (file->stats.records == 0)
+      {
+        error= HA_ERR_END_OF_FILE;
+        break;
+      }
       error= file->index_first(buf);
       break;
     case partition_index_first_unordered:
@@ -4377,10 +4388,32 @@ 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() when indexes disabled */
+      /* that happens if the table is empty. */
+      /* Here we use file->stats.records instead of file->records() because */
+      /* file->records() is supposed to return an EXACT count, and it can be   */
+      /* possibly slow. We don't need an exact number, an approximate one- from*/
+      /* the last ::info() call - is sufficient. */
+      if (file->stats.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() when indexes disabled */
+      /* that happens if the table is empty. */
+      /* Here we use file->stats.records instead of file->records() because */
+      /* file->records() is supposed to return an EXACT count, and it can be   */
+      /* possibly slow. We don't need an exact number, an approximate one- from*/
+      /* the last ::info() call - is sufficient. */
+      if (file->stats.records == 0)
+      {
+        error= HA_ERR_END_OF_FILE;
+        break;
+      }
       error= file->index_last(rec_buf_ptr);
       reverse_order= TRUE;
       break;