Bug #36455 index hints and incomplete/partial index names
Submitted: 1 May 2008 16:07 Modified: 6 May 2008 17:55
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0 bk / 4.1 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: INDEX, index hints, qc, use index

[1 May 2008 16:07] Martin Friebe
Description:
Mysql accepts a partial name for indexes in use/force/ignore index.

  create table t1 ( foo int(11), index searchme (foo) );
  select * from t1 force index (search);

An index named "search" does not exist, but it is found, as it is a partial match of "searchme"

If a partial match has more than one extension, then an error is returned. Therefore if doing:
  alter table t1 add index searchthis (foo);
  select * from t1 force index (search);
  ERROR HY000: Key 'search' doesn't exist in table 't1'
Which is at least unexpected, as no key has been removed.

If a complete match exists, it will be returned, never mind any possibilties of extension.

This may be a bug, or a documentation issue.

I could not find any mention at http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

From the source code, it would appear that this may have been done with intention?

index hints are handled in sql/sql_base.cc setup_tables (line 5238) and a key_map is build using get_key_map_from_key_list.

This is in sql/sql_base.cc (around line 5364) in function get_key_map_from_key_list

    if (table->s->keynames.type_names == 0 ||
        (pos= find_type(&table->s->keynames, name->ptr(),
                        name->length(), 1)) <=
        0)

The last argument to "find_type" is: 1
This is "bool part_match" (see ref to find_type below), so a part match is explicitly allowed?

sql/strfunc.cc line 107 
uint find_type(TYPELIB *lib, const char *find, uint length, bool part_match)

How to repeat:
drop table if exists t1;

create table t1 ( foo int(11), index searchme (foo) );
select * from t1 force index (search); # finds key by part-name

alter table t1 add index searchthis (foo);
select * from t1 force index (search); # now fails

Suggested fix:
-
[1 May 2008 21:33] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[6 May 2008 17:55] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Index hints can be unambiguous prefixes of index names. If a prefix is ambiguous, an error occurs.