Bug #20604 FORCE INDEX uses keys disabled by ALTER TABLE

Description:
Having indexes disabled by ALTER TABLE and FORCE INDEX in the SELECT query results in the error from storage engine for some queries.

How to repeat:
There are at least two cases that I was able to discover:
CREATE TABLE t1(a TINYINT, b VARCHAR(255), KEY(a), FULLTEXT(b)) ENGINE=MyISAM;
INSERT INTO t1 VALUES(1, 'abcde');
ALTER TABLE t1 DISABLE KEYS;
SELECT * FROM t1 FORCE INDEX(b) WHERE MATCH(b) AGAINST('abcde' IN BOOLEAN MODE);
SELECT MAX(a) FROM t1 FORCE INDEX(a);

Suggested fix:
Fall to table scan when indexes are disabled and FORCE INDEX is used in the query. The problem seems to be around keys_in_use_for_query bitmap which includes disabled indexes.

This bug should be fixed probably after fix for BUG#20357 is pushed. There is another problem with min/max and disabled indexes.

Thank you for the bug report. Verified as described.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17963

ChangeSet@1.2359, 2007-01-11 10:46:47+01:00, mhansson@linux-st28.site +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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17964

ChangeSet@1.2359, 2007-01-11 12:18:06+01:00, mhansson@linux-st28.site +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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17965

ChangeSet@1.2359, 2007-01-11 09:59:49+01:00, mhansson@linux-st28.site +3 -0
  Fixed 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(), 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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18540

ChangeSet@1.2359, 2007-01-22 15:40:44+01:00, mhansson@linux-st28.site +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
  are not to be found in table->keys_in_use_for_query.
  
  The bug is that disabled keys still appear in keys_in_use_for_query,
  in the normal case. 
  
  The assumption for keys_is_use_for_query is as of now that disabled
  keys are _not_ included. This has also been put as a comment
  above the field.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18602

ChangeSet@1.2359, 2007-01-23 08:57:53+01:00, mhansson@linux-st28.site +6 -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
  are not to be found in table->keys_in_use_for_query.
  
  The bug is that disabled keys still appear in keys_in_use_for_query,
  in the normal case. 
  
  The assumption for keys_is_use_for_query is as of now that disabled
  keys are _not_ included. This has also been put as a comment
  above the field.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18762

ChangeSet@1.2392, 2007-01-25 09:25:59+01:00, mhansson@dl145s.mysql.com +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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18775

ChangeSet@1.2409, 2007-01-25 14:45:48+01:00, mhansson@linux-st28.site +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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18785

ChangeSet@1.2409, 2007-01-25 16:58:30+01:00, mhansson@linux-st28.site +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(). 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 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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18841

ChangeSet@1.2409, 2007-01-26 12:36:29+01:00, mhansson@linux-st28.site +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(). 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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18946

ChangeSet@1.2409, 2007-01-29 15:07:11+01:00, mhansson@linux-st28.site +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(). 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.

The fix has been pushed into 5.1.16-beta main tree.

Noted in 5.1.16 changelog.

Indexes disabled with ALTER TABLE ... DISABLE KEYS could in some
cases be used by specifying FORCE INDEX.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21691

ChangeSet@1.2480, 2007-03-11 14:31:43+02:00, gkodinov@magare.gmz +3 -0
  WL3527: post-merge updates
  sql_yacc.yy:
    WL3527: updated the diff to use correct parser words
  table.cc:
    WL3527: exteneded the fix for bug #20604 to fit the new variables
  sql_select.cc:
    WL3527: renamed used_keys to covering_keys