From: Date: November 11 2008 11:40am Subject: bzr commit into mysql-5.1 branch (kgeorge:2703) Bug#37442 List-Archive: http://lists.mysql.com/commits/58435 X-Bug: 37442 Message-Id: <200811111040.mABAeb45015723@magare.gmz> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #At file:///home/kgeorge/mysql/bzr/B37742-5.1-bugteam/ 2703 Georgi Kodinov 2008-11-11 Bug #37442: 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 #37442: test case mysql-test/t/innodb_mysql.test Bug #37442: test case sql/table.cc Bug #37442: 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:40:11 +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:40:11 +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:40:11 +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))) {