#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)))
{
