bzr commit into mysql-5.1 branch (kgeorge:2703) Bug#37742

[Georgi Kodinov]

#At file:///home/kgeorge/mysql/bzr/B37742-5.1-bugteam/

 2703 Georgi Kodinov	2008-11-11
      Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of requested
      column
      
      When the storage engine uses secondary keys clustered with the primary key MySQL was
      adding the primary key parts to each secondary key.
      In doing so it was not checking whether the index was on full columns and this
      resulted in the secondary keys being added to the list of covering keys even if 
      they have partial columns.
      Fixed by cleaning up the list of covering keys if the primary key has a partial
      column as a key part.
modified:
  mysql-test/r/innodb_mysql.result
  mysql-test/t/innodb_mysql.test
  sql/table.cc

per-file messages:
  mysql-test/r/innodb_mysql.result
    Bug #37742: test case
  mysql-test/t/innodb_mysql.test
    Bug #37742: test case
  sql/table.cc
    Bug #37742: no covering indices when the primary key has a partial column
        for SEs that have secondary indices clustered with the primary key.
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2008-11-03 17:46:47 +0000
+++ b/mysql-test/r/innodb_mysql.result	2008-11-11 10:44:43 +0000
@@ -1677,3 +1677,51 @@ select @@session.sql_log_bin, @@session.
 CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
 INSERT INTO t1 VALUES(1);
 DROP TABLE t1;
+CREATE TABLE foo (a int, b int, c char(10),
+PRIMARY KEY (c(3)),
+KEY b (b)
+) engine=innodb;
+CREATE TABLE foo2 (a int, b int, c char(10),
+PRIMARY KEY (c),
+KEY b (b)
+) engine=innodb;
+CREATE TABLE bar (a int, b int, c char(10),
+PRIMARY KEY (c(3)),
+KEY b (b)
+) engine=myisam;
+INSERT INTO foo VALUES
+(1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
+(4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe');
+INSERT INTO bar SELECT * FROM foo;
+INSERT INTO foo2 SELECT * FROM foo;
+;
+ERROR 42000: Query was empty
+EXPLAIN SELECT c FROM bar WHERE b>2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	bar	ALL	b	NULL	NULL	NULL	6	Using where
+;
+ERROR 42000: Query was empty
+EXPLAIN SELECT c FROM foo WHERE b>2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	foo	ALL	b	NULL	NULL	NULL	6	Using where
+;
+ERROR 42000: Query was empty
+EXPLAIN SELECT c FROM foo2 WHERE b>2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	foo2	range	b	b	5	NULL	3	Using where; Using index
+;
+ERROR 42000: Query was empty
+EXPLAIN SELECT c FROM bar WHERE c>2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	bar	ALL	PRIMARY	NULL	NULL	NULL	6	Using where
+;
+ERROR 42000: Query was empty
+EXPLAIN SELECT c FROM foo WHERE c>2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	foo	ALL	PRIMARY	NULL	NULL	NULL	6	Using where
+;
+ERROR 42000: Query was empty
+EXPLAIN SELECT c FROM foo2 WHERE c>2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	foo2	index	PRIMARY	b	5	NULL	6	Using where; Using index
+DROP TABLE foo, bar, foo2;

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2008-11-03 17:46:47 +0000
+++ b/mysql-test/t/innodb_mysql.test	2008-11-11 10:44:43 +0000
@@ -43,3 +43,45 @@ CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
 INSERT INTO t1 VALUES(1);
 DROP TABLE t1;
 
+#
+# Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of
+# requested column
+#
+
+CREATE TABLE foo (a int, b int, c char(10),
+                  PRIMARY KEY (c(3)),
+                  KEY b (b)
+) engine=innodb;
+
+CREATE TABLE foo2 (a int, b int, c char(10),
+                  PRIMARY KEY (c),
+                  KEY b (b)
+) engine=innodb;
+
+CREATE TABLE bar (a int, b int, c char(10),
+                  PRIMARY KEY (c(3)),
+                  KEY b (b)
+) engine=myisam;
+
+INSERT INTO foo VALUES
+   (1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
+   (4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe');
+
+INSERT INTO bar SELECT * FROM foo;
+INSERT INTO foo2 SELECT * FROM foo;
+
+--query_vertical
+EXPLAIN SELECT c FROM bar WHERE b>2;
+--query_vertical
+EXPLAIN SELECT c FROM foo WHERE b>2;
+--query_vertical
+EXPLAIN SELECT c FROM foo2 WHERE b>2;
+
+--query_vertical
+EXPLAIN SELECT c FROM bar WHERE c>2;
+--query_vertical
+EXPLAIN SELECT c FROM foo WHERE c>2;
+--query_vertical
+EXPLAIN SELECT c FROM foo2 WHERE c>2;
+
+DROP TABLE foo, bar, foo2;

=== modified file 'sql/table.cc'
--- a/sql/table.cc	2008-10-07 21:52:49 +0000
+++ b/sql/table.cc	2008-11-11 10:44:43 +0000
@@ -667,6 +667,7 @@ static int open_binary_frm(THD *thd, TAB
   enum legacy_db_type legacy_db_type;
   my_bitmap_map *bitmaps;
   DBUG_ENTER("open_binary_frm");
+  bool no_covering_keys= FALSE;
 
   new_field_pack_flag= head[27];
   new_frm_ver= (head[2] - FRM_VER);
@@ -1419,6 +1420,16 @@ static int open_binary_frm(THD *thd, TAB
             field->part_of_key= share->keys_in_use;
             if (field->part_of_sortkey.is_set(key))
               field->part_of_sortkey= share->keys_in_use;
+            if (field->key_length() != key_part->length ||
+                (field->flags & BLOB_FLAG))
+            {
+              /* 
+                This primary key field cannot be used to retrieve the 
+                column from the index alone.
+                So we must clear the keys_for_keyread set.
+              */
+              no_covering_keys= TRUE;
+            }
           }
         }
         if (field->key_length() != key_part->length)
@@ -1478,6 +1489,8 @@ static int open_binary_frm(THD *thd, TAB
           (ha_option & HA_ANY_INDEX_MAY_BE_UNIQUE))
         set_if_bigger(share->max_unique_length,keyinfo->key_length);
     }
+    if (no_covering_keys)
+      share->keys_for_keyread.clear_all();
     if (primary_key < MAX_KEY &&
 	(share->keys_in_use.is_set(primary_key)))
     {