MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Georgi Kodinov Date:November 29 2008 1:36pm
Subject:bzr commit into mysql-5.1 branch (kgeorge:2703) Bug#37742
View as plain text  
#At file:///home/kgeorge/mysql/work/B37742-5.1-bugteam/ based on revid:kgeorge@stripped

 2703 Georgi Kodinov	2008-11-29
      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 not adding a primary key part to the list of columns that can be used 
      for index read of the secondary keys when the primary key part is a partial 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: don't add the primary key part to the list of covering key parts
    of a secondary key if it's a partial key part.
=== 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-29 13:36:17 +0000
@@ -1677,3 +1677,87 @@ 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;
+EXPLAIN SELECT c FROM bar WHERE b>2;;
+id	1
+select_type	SIMPLE
+table	bar
+type	ALL
+possible_keys	b
+key	NULL
+key_len	NULL
+ref	NULL
+rows	6
+Extra	Using where
+EXPLAIN SELECT c FROM foo WHERE b>2;;
+id	1
+select_type	SIMPLE
+table	foo
+type	ALL
+possible_keys	b
+key	NULL
+key_len	NULL
+ref	NULL
+rows	6
+Extra	Using where
+EXPLAIN SELECT c FROM foo2 WHERE b>2;;
+id	1
+select_type	SIMPLE
+table	foo2
+type	range
+possible_keys	b
+key	b
+key_len	5
+ref	NULL
+rows	3
+Extra	Using where; Using index
+EXPLAIN SELECT c FROM bar WHERE c>2;;
+id	1
+select_type	SIMPLE
+table	bar
+type	ALL
+possible_keys	PRIMARY
+key	NULL
+key_len	NULL
+ref	NULL
+rows	6
+Extra	Using where
+EXPLAIN SELECT c FROM foo WHERE c>2;;
+id	1
+select_type	SIMPLE
+table	foo
+type	ALL
+possible_keys	PRIMARY
+key	NULL
+key_len	NULL
+ref	NULL
+rows	6
+Extra	Using where
+EXPLAIN SELECT c FROM foo2 WHERE c>2;;
+id	1
+select_type	SIMPLE
+table	foo2
+type	index
+possible_keys	PRIMARY
+key	b
+key_len	5
+ref	NULL
+rows	6
+Extra	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-29 13:36:17 +0000
@@ -43,3 +43,39 @@ 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-29 13:36:17 +0000
@@ -1416,7 +1416,9 @@ static int open_binary_frm(THD *thd, TAB
           */
           if (ha_option & HA_PRIMARY_KEY_IN_READ_INDEX)
           {
-            field->part_of_key= share->keys_in_use;
+            if (field->key_length() == key_part->length &&
+                !(field->flags & BLOB_FLAG))
+              field->part_of_key= share->keys_in_use;
             if (field->part_of_sortkey.is_set(key))
               field->part_of_sortkey= share->keys_in_use;
           }

Thread
bzr commit into mysql-5.1 branch (kgeorge:2703) Bug#37742Georgi Kodinov29 Nov
  • Re: bzr commit into mysql-5.1 branch (kgeorge:2703) Bug#37742Sergei Golubchik3 Dec