From: Date: January 11 2007 12:18pm
Subject: bk commit into 5.1 tree (mhansson:1.2359) BUG#20604
List-Archive: http://lists.mysql.com/commits/17964
X-Bug: 20604
Message-Id: <20070111111812.7012721B1D@linux-st28.site>

Below is the list of changes that have just been committed into a local
5.1 repository of martin. When martin 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-11 12:18:06+01:00, mhansson@stripped +3 -0
  BUG # 20604
  
  Fixed
  
  The function that checks whether we can use keys for aggregates,
  find_key_for_maxmin(), fails to check that keys are not disabled by
  ALTER TABLE.
  
  The fact that keys are disabled does not reveal itself in
  keys_in_use_for_query, but rather in a field in its "table share", called
  keys_in_use. The reasoning behind this is most likely that
  a DISABLE KEYS is set on table level, as opposed to a
  particular projection of a table. 
  
  Table level objects are known as "table shares" inside the code.
  
  Projections of tables are known in the code as a "table".
  
  Hence the set of indexes that can be used are the intersection of
  those that this particular "table" object permits using and those that
  the "table share" permits using.

  mysql-test/r/key.result@stripped, 2007-01-11 12:18:04+01:00, mhansson@stripped +7 -0
    This is the correct output for this query. It isn't actually that important because this 
    set of commands will actually cause a failure to be reported from MyISAM when the bug 
    is active.

  mysql-test/t/key.test@stripped, 2007-01-11 12:18:04+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 crash.

  sql/opt_sum.cc@stripped, 2007-01-11 12:18:04+01:00, mhansson@stripped +3 -1
    This shows where the aggregate optimizer decides which indexes it is free to use. 
    
    The comment preceding "if (!enabled_keys.is_set(idx))" was actually incorrect, because the algorithm 
    did not look at whether keys where disabled via ALTER TABLE or not.

# 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:	linux-st28.site
# Root:	/home/martin/mysql/src/5.1o-dbg-bug20604

--- 1.58/sql/opt_sum.cc	2006-10-18 10:47:17 +02:00
+++ 1.59/sql/opt_sum.cc	2007-01-11 12:18:04 +01:00
@@ -743,6 +743,8 @@
   uint idx= 0;
 
   KEY *keyinfo,*keyinfo_end;
+  key_map enabled_keys= table->s->keys_in_use;
+  enabled_keys.intersect(table->keys_in_use_for_query);
   for (keyinfo= table->key_info, keyinfo_end= keyinfo+table->s->keys ;
        keyinfo != keyinfo_end;
        keyinfo++,idx++)
@@ -753,7 +755,7 @@
       Perform a check if index is not disabled by ALTER TABLE
       or IGNORE INDEX.
     */
-    if (!table->keys_in_use_for_query.is_set(idx))
+    if (!enabled_keys.is_set(idx))
       continue;
     uint jdx= 0;
     *prefix_len= 0;

--- 1.40/mysql-test/r/key.result	2006-08-11 23:06:20 +02:00
+++ 1.41/mysql-test/r/key.result	2007-01-11 12:18:04 +01:00
@@ -482,3 +482,10 @@
 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;
+SELECT MAX(a) FROM t1 FORCE INDEX(a);
+MAX(a)
+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-11 12:18:04 +01:00
@@ -442,3 +442,14 @@
 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;
+SELECT MAX(a) FROM t1 FORCE INDEX(a);
+
+drop table t1;
+