Below is the list of changes that have just been committed into a local
5.1 repository of mhansson. When mhansson does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-01-25 09:25:59+01:00, mhansson@stripped +5 -0
BUG#20604: FORCE INDEX uses keys disabled by ALTER TABLE
The function that checks whether we can use keys for aggregates,
find_key_for_maxmin(), assumes that keys disabled by ALTER TABLE
... DISABLE KEYS are not in the set table->keys_in_use_for_query.
I.E., if a key is in this set, the optimizer assumes it is free to
use it.
The bug is that keys disabled with ALTER TABLE ... DISABLE KEYS still
appear in table->keys_in_use_for_query When the TABLE object has been
initialized with setup_tables(). In the cases when the TABLE has been
initialized any other way, for instance open_table(), the keys disabled
in the aforementioned way are not in the set.
The provided patch does the following:
- changes the code that uses keys_is_use_for_query so that it assumes that
keys_is_use_for_query already takes into account all disabled keys, and
generally all keys that should be used by the query.
- subtracts all disabled keys from keys_is_use_for_query early, during
setup_tables, so that later phases can rely on consistent contents of
keys_is_use_for_query.
mysql-test/r/key.result@stripped, 2007-01-25 09:25:54+01:00, mhansson@stripped +7 -0
Test for BUG#20604.
The important part of the test is the explain output that tests
what indexes are used.
mysql-test/t/key.test@stripped, 2007-01-25 09:25:54+01:00, mhansson@stripped +11 -0
The minimal test case that reveals the bug. The optimizer for
aggregates did not take into account that keys might be the
disabled on a table. The execution engine then tries to proceed
and use the index, causing MyISAM to produce an error.
sql/sql_base.cc@stripped, 2007-01-25 09:25:54+01:00, mhansson@stripped +1 -0
Exclude the keys disabled by ALTER TABLE ... DISABLE_KEYS
from TABLE::keys_in_use_for_query
sql/sql_select.cc@stripped, 2007-01-25 09:25:55+01:00, mhansson@stripped +8 -3
The intersection between table->s->keys_in_use and
table->keys_in_use_for_query is no longer necessary.
We can trust that the latter is a subset of the former.
sql/table.h@stripped, 2007-01-25 09:25:55+01:00, mhansson@stripped +20 -2
Added comments.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: mhansson
# Host: dl145s.mysql.com
# Root: /dev/shm/5.1o-bug20604
--- 1.374/sql/sql_base.cc 2007-01-12 11:47:55 +01:00
+++ 1.375/sql/sql_base.cc 2007-01-25 09:25:54 +01:00
@@ -5474,6 +5474,7 @@ bool setup_tables(THD *thd, Name_resolut
DBUG_RETURN(1);
table->keys_in_use_for_query.subtract(map);
}
+ table->keys_in_use_for_query.intersect(table->s->keys_in_use);
table->used_keys.intersect(table->keys_in_use_for_query);
}
if (tablenr > MAX_TABLES)
--- 1.478/sql/sql_select.cc 2007-01-10 11:06:19 +01:00
+++ 1.479/sql/sql_select.cc 2007-01-25 09:25:55 +01:00
@@ -12232,10 +12232,15 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
/*
Check which keys can be used to resolve ORDER BY.
We must not try to use disabled keys.
+ We should not consider keys that are disabled by IGNORE INDEX.
*/
- usable_keys= table->s->keys_in_use;
- /* we must not consider keys that are disabled by IGNORE INDEX */
- usable_keys.intersect(table->keys_in_use_for_query);
+ usable_keys= table->keys_in_use_for_query;
+
+ /*
+ Keys disabled by ALTER TABLE ... DISABLE KEYS should have already
+ been taken into account during setup_tables.
+ */
+ DBUG_ASSERT(usable_keys.is_subset(table->s->keys_in_use));
for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next)
{
--- 1.158/sql/table.h 2007-01-15 10:39:27 +01:00
+++ 1.159/sql/table.h 2007-01-25 09:25:55 +01:00
@@ -158,7 +158,12 @@ typedef struct st_table_share
LEX_STRING path; /* Path to .frm file (from datadir) */
LEX_STRING normalized_path; /* unpack_filename(path) */
LEX_STRING connect_string;
- key_map keys_in_use; /* Keys in use for table */
+
+ /*
+ Set of keys in use, implemented as a Bitmap.
+ Excludes keys disabled by ALTER TABLE ... DISABLE KEYS.
+ */
+ key_map keys_in_use;
key_map keys_for_keyread;
ha_rows min_rows, max_rows; /* create information */
ulong avg_row_length; /* create information */
@@ -313,7 +318,20 @@ struct st_table {
byte *write_row_record; /* Used as optimisation in
THD::write_row */
byte *insert_values; /* used by INSERT ... UPDATE */
- key_map quick_keys, used_keys, keys_in_use_for_query, merge_keys;
+
+ /*
+ A set of keys that can be used in the query that references this
+ table
+
+ All indexes disabled on the table's TABLE_SHARE (see TABLE::s)
+ will be subtracted from this set upon instantiation and during
+ setup_tables, i.e. for any st_table t it holds that
+ t.keys_in_use_for_query is a subset of t.s.keys_in_use.
+
+ The set is implemented as a bitmap.
+ */
+ key_map keys_in_use_for_query;
+ key_map quick_keys, used_keys, merge_keys;
KEY *key_info; /* data of keys in database */
Field *next_number_field; /* Set if next_number is activated */
--- 1.40/mysql-test/r/key.result 2006-08-11 23:06:20 +02:00
+++ 1.41/mysql-test/r/key.result 2007-01-25 09:25:54 +01:00
@@ -482,3 +482,10 @@ alter table t1 drop index i3, drop index
alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
ERROR 23000: Duplicate entry '1' for key 'i1'
drop table t1;
+CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES( 1 );
+ALTER TABLE t1 DISABLE KEYS;
+EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1
+drop table t1;
--- 1.31/mysql-test/t/key.test 2006-07-20 20:41:52 +02:00
+++ 1.32/mysql-test/t/key.test 2007-01-25 09:25:54 +01:00
@@ -442,3 +442,14 @@ alter table t1 drop index i3, drop index
alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
drop table t1;
+
+#
+# Bug #20604: Test for disabled keys with aggregate functions and FORCE INDEX.
+#
+
+CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM;
+INSERT INTO t1 VALUES( 1 );
+ALTER TABLE t1 DISABLE KEYS;
+EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
+
+drop table t1;
| Thread |
|---|
| • bk commit into 5.1 tree (mhansson:1.2392) BUG#20604 | mhansson | 25 Jan |
