From 190efb06e7c9fe96bb4f4294f3b0ddbc4eef12de Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 29 Jan 2007 15:07:11 +0100 Subject: 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(). Before setup_tables is called, however, keys that are disabled in the aforementioned way are not included in TABLE::keys_in_use_for_query. The provided patch changes the code that updates 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. mysql-test/r/key.result: 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: The minimal test case that reveals the bug. The optimizer for aggregates relies on keys disabled with ALTER TABLE ... DISABLE KEYS not being in the set TABLE::keys_in_use_for_query. When the execution engine tries to use a disabled index, MyISAM returns an error. sql/sql_base.cc: Exclude the keys disabled by ALTER TABLE ... DISABLE_KEYS from TABLE::keys_in_use_for_query, and in general, don't introduce any new keys. We may not know why keys have been removed at previous stages. sql/sql_select.cc: The intersection operation 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: Added comments to TABLE_SHARE::keys_in_use and TABLE::keys_in_use_for_query. --- mysql-test/r/key.result | 7 +++++++ 1 file changed, 7 insertions(+) (limited to 'mysql-test/r/key.result') diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index ea8d4338d08..853b837c46e 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -482,3 +482,10 @@ alter table t1 drop index i3, drop index i2, drop index i1; 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; -- cgit v1.2.1