bk commit into 5.0 tree (gkodinov:1.2491) BUG#28476

[kgeorge]

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-17 16:14:16+03:00, gkodinov@stripped +4 -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:
   - Ignoring disabled indexes in USE/FORCE index
   - issue warning if some of the USE/FORCE indexes is ignored.

  mysql-test/r/myisam.result@stripped, 2007-05-17 16:14:15+03:00, gkodinov@stripped +32 -0
    Bug #28476: test cases

  mysql-test/t/myisam.test@stripped, 2007-05-17 16:14:15+03:00, gkodinov@stripped +16 -0
    Bug #28476: test cases

  sql/share/errmsg.txt@stripped, 2007-05-17 16:14:15+03:00, gkodinov@stripped +2 -1
    Bug #28476: 
     issue warning if some of the USE/FORCE indexes is ignored.

  sql/sql_base.cc@stripped, 2007-05-17 16:14:15+03:00, gkodinov@stripped +19 -1
    Bug #28476: 
     - Ignore disabled indexes in USE/FORCE index
     - issue warning if some of the USE/FORCE indexes is ignored.

# 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-17 16:14:15 +03:00
@@ -4807,11 +4807,29 @@ bool setup_tables(THD *thd, Name_resolut
     table->used_keys= table->s->keys_for_keyread;
     if (table_list->use_index)
     {
-      key_map map;
+      key_map map, ignored_map;
       get_key_map_from_key_list(&map, table, table_list->use_index);
       if (map.is_set_all())
 	DBUG_RETURN(1);
+      /* 
+        Inersect USE|FORCE INDEX list with the available keys : some of them
+        may not be usable : for example disabled by ALTER TABLE .. DISABLE KEYS
+      */
+      ignored_map= map;
+      map.intersect(table->keys_in_use_for_query);
       table->keys_in_use_for_query=map;
+
+      ignored_map.subtract (map);
+      if (!ignored_map.is_clear_all())
+        for (uint i= 0; i < table->s->keys; i++)
+          if (ignored_map.is_set(i))
+            push_warning_printf (thd, MYSQL_ERROR::WARN_LEVEL_WARN, 
+                                 ER_WARN_IGNORED_INDEX_HINT,
+                                 ER(ER_WARN_IGNORED_INDEX_HINT),  
+                                 table_list->force_index ? "FORCE" : "USE",
+                                 table->key_info[i].name,
+                                 table_list->alias);
+
     }
     if (table_list->ignore_index)
     {

--- 1.78/sql/share/errmsg.txt	2007-02-23 18:36:00 +02:00
+++ 1.79/sql/share/errmsg.txt	2007-05-17 16:14:15 +03:00
@@ -5633,4 +5633,5 @@ ER_WRONG_STRING_LENGTH
 	eng "String '%-.70s' is too long for %s (should be no longer than %d)"
 ER_NON_INSERTABLE_TABLE  
 	eng "The target table %-.100s of the %s is not insertable-into"
-
+ER_WARN_IGNORED_INDEX_HINT
+	eng "%s INDEX (%s) for table %s is ignored because the key is not usable : probably
disabled by ALTER TABLE ... DISABLE KEYS"

--- 1.99/mysql-test/r/myisam.result	2007-03-28 11:25:49 +03:00
+++ 1.100/mysql-test/r/myisam.result	2007-05-17 16:14:15 +03:00
@@ -1780,4 +1780,36 @@ 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
+Warnings:
+Warning	1472	FORCE INDEX (inx) for table t1 is ignored because the key is not usable :
probably disabled by ALTER TABLE ... DISABLE KEYS
+SELECT a FROM t1 USE INDEX (inx) WHERE a=1;
+a
+1
+Warnings:
+Warning	1472	USE INDEX (inx) for table t1 is ignored because the key is not usable :
probably disabled by ALTER TABLE ... DISABLE KEYS
+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
+Warnings:
+Warning	1472	FORCE INDEX (inx) for table t1 is ignored because the key is not usable :
probably disabled by ALTER TABLE ... DISABLE KEYS
+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-17 16:14:15 +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