Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge 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-05-22 13:28:02+03:00, gkodinov@stripped +7 -0
Bug #28476:
When processing the USE/FORCE index hints
the optimizer was not checking if the indexes
specified are enabled (see ALTER TABLE).
Fixed by:
Backporting the fix for bug 20604 to 5.0
mysql-test/r/key.result@stripped, 2007-05-22 13:28:01+03:00, gkodinov@stripped +8 -0
Test for BUG 20604.
The important part of the test is the explain output that
tests what indexes are used.
mysql-test/r/myisam.result@stripped, 2007-05-22 13:28:01+03:00, gkodinov@stripped +26 -0
Bug #28476: test cases
mysql-test/t/key.test@stripped, 2007-05-22 13:28:01+03:00, gkodinov@stripped +12 -0
Bug 20604:
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.
mysql-test/t/myisam.test@stripped, 2007-05-22 13:28:01+03:00, gkodinov@stripped +16 -0
Bug #28476: test cases
sql/sql_base.cc@stripped, 2007-05-22 13:28:01+03:00, gkodinov@stripped +6 -1
Bug #28476:
- Ignore disabled indexes in USE/FORCE index
sql/sql_select.cc@stripped, 2007-05-22 13:28:01+03:00, gkodinov@stripped +4 -3
Bug 20604 : 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@stripped, 2007-05-22 13:28:01+03:00, gkodinov@stripped +21 -2
Bug 20604:
Added comments to TABLE_SHARE::keys_in_use and
TABLE::keys_in_use_for_query.
# 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: gkodinov
# Host: magare.gmz
# Root: /home/kgeorge/mysql/work/B28476-5.0-opt
--- 1.377/sql/sql_base.cc 2007-04-24 21:34:26 +03:00
+++ 1.378/sql/sql_base.cc 2007-05-22 13:28:01 +03:00
@@ -4811,7 +4811,12 @@ bool setup_tables(THD *thd, Name_resolut
get_key_map_from_key_list(&map, table, table_list->use_index);
if (map.is_set_all())
DBUG_RETURN(1);
- table->keys_in_use_for_query=map;
+ /*
+ Don't introduce keys in keys_in_use_for_query that weren't there
+ before. FORCE/USE INDEX should not add keys, it should only remove
+ all keys except the key(s) specified in the hint.
+ */
+ table->keys_in_use_for_query.intersect(map);
}
if (table_list->ignore_index)
{
--- 1.521/sql/sql_select.cc 2007-05-15 23:16:08 +03:00
+++ 1.522/sql/sql_select.cc 2007-05-22 13:28:01 +03:00
@@ -12246,10 +12246,11 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
LINT_INIT(ref_key_parts);
/*
- Check which keys can be used to resolve ORDER BY.
- We must not try to use disabled keys.
+ Keys disabled by ALTER TABLE ... DISABLE KEYS should have already
+ been taken into account.
*/
- usable_keys= table->s->keys_in_use;
+ usable_keys= table->keys_in_use_for_query;
+ DBUG_ASSERT(usable_keys.is_subset(table->s->keys_in_use));
for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next)
{
--- 1.141/sql/table.h 2007-03-31 13:36:46 +03:00
+++ 1.142/sql/table.h 2007-05-22 13:28:01 +03:00
@@ -137,7 +137,12 @@ typedef struct st_table_share
const char *table_name; /* Table name (for open) */
const char *path; /* Path to .frm file (from datadir) */
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;
ulong avg_row_length; /* create information */
ulong raid_chunksize;
@@ -206,7 +211,21 @@ struct st_table {
byte *record[2]; /* Pointer to records */
byte *insert_values; /* used by INSERT ... UPDATE */
- key_map quick_keys, used_keys, keys_in_use_for_query;
+ key_map quick_keys, used_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. Thus for any TABLE t it holds
+ that t.keys_in_use_for_query is a subset of t.s.keys_in_use. Generally we
+ must not introduce any new keys here (see setup_tables).
+
+ The set is implemented as a bitmap.
+ */
+ key_map keys_in_use_for_query;
+ key_map merge_keys;
KEY *key_info; /* data of keys in database */
Field *next_number_field, /* Set if next_number is activated */
--- 1.36/mysql-test/r/key.result 2007-04-20 12:01:51 +03:00
+++ 1.37/mysql-test/r/key.result 2007-05-22 13:28:01 +03:00
@@ -455,3 +455,11 @@ ORDER BY c.b, c.d
a b c d e f g h i j a b c d
2 2 1 2004-11-30 12:00:00 1 0 0 0 0 0 2 3388000 -553000 NULL
DROP TABLE t1, t2;
+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;
+End of 5.0 tests.
--- 1.29/mysql-test/t/key.test 2007-04-20 12:01:51 +03:00
+++ 1.30/mysql-test/t/key.test 2007-05-22 13:28:01 +03:00
@@ -432,3 +432,15 @@ ORDER BY c.b, c.d
;
DROP TABLE t1, t2;
+
+
+#
+# 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;
+
+--echo End of 5.0 tests.
--- 1.99/mysql-test/r/myisam.result 2007-03-28 11:25:49 +03:00
+++ 1.100/mysql-test/r/myisam.result 2007-05-22 13:28:01 +03:00
@@ -1780,4 +1780,30 @@ create table t3 (c1 int) engine=myisam p
create table t4 (c1 int) engine=myisam pack_keys=2;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2' at line 1
drop table t1, t2, t3;
+CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)) ENGINE=MyISAM;
+INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
+SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
+a
+1
+ALTER TABLE t1 DISABLE KEYS;
+SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
+a
+1
+SELECT a FROM t1 USE INDEX (inx) WHERE a=1;
+a
+1
+SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1;
+b
+1
+SELECT b FROM t1 USE INDEX (uinx) WHERE b=1;
+b
+1
+SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1;
+a
+1
+ALTER TABLE t1 ENABLE KEYS;
+SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
+a
+1
+DROP TABLE t1;
End of 5.0 tests
--- 1.79/mysql-test/t/myisam.test 2007-03-28 11:22:20 +03:00
+++ 1.80/mysql-test/t/myisam.test 2007-05-22 13:28:01 +03:00
@@ -1145,4 +1145,20 @@ create table t3 (c1 int) engine=myisam p
create table t4 (c1 int) engine=myisam pack_keys=2;
drop table t1, t2, t3;
+#
+# Bug#28476: force index on a disabled myisam index gives error 124
+#
+CREATE TABLE t1(a INT, b INT, KEY inx (a), UNIQUE KEY uinx (b)) ENGINE=MyISAM;
+INSERT INTO t1(a,b) VALUES (1,1),(2,2),(3,3),(4,4),(5,5);
+SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
+ALTER TABLE t1 DISABLE KEYS;
+SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
+SELECT a FROM t1 USE INDEX (inx) WHERE a=1;
+SELECT b FROM t1 FORCE INDEX (uinx) WHERE b=1;
+SELECT b FROM t1 USE INDEX (uinx) WHERE b=1;
+SELECT a FROM t1 FORCE INDEX (inx,uinx) WHERE a=1;
+ALTER TABLE t1 ENABLE KEYS;
+SELECT a FROM t1 FORCE INDEX (inx) WHERE a=1;
+DROP TABLE t1;
+
--echo End of 5.0 tests
| Thread |
|---|
| • bk commit into 5.0 tree (gkodinov:1.2491) BUG#28476 | kgeorge | 22 May |
