Bug #46228 Inconsistent key_part definition for index creation
Submitted: 16 Jul 2009 14:45 Modified: 28 Jul 2009 6:56
Reporter: Tim Clark Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Storage Engine API Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D4 (Minor) / R4 (High) / E2 (Low)

[16 Jul 2009 14:45] Tim Clark
Description:
Inside of a storage engine (IBMDB2I) that supports online index creation I have noticed that the key_part structure appears to be constructed slightly differently depending on whether it is passed in to handler::create() or to handler::add_index(). Most significantly, fieldnr appears to be one-based through create() and zero-based through add_index(). This inconsistency seems incorrect and requires additional logic inside of the storage engine to normalize fieldnr.

See http://lists.mysql.com/internals/37146 for more background and an explanation of this bug's significance.

How to repeat:
With a storage engine that supports online index creation:

Set a breakpoint in ha_*::create(), then
CREATE TABLE t1 (c char(10), index(c(5)))
Note that table_arg->key_info[0].key_part[0].fieldnr = 1

Then set a breakpoint in ha_*::add_index() and
DROP TABLE t1;
CREATE TABLE t1 (c char(10));
ALTER TABLE t1 ADD INDEX(c(5));
Note that key->key_info[0].key_part[0].fieldnr = 0.

Suggested fix:
Make the fieldnr value consistent between table creation and online index creation. It appears that the behavior of add_index() is anomalous. Most references to fieldnr throughout the server code assume that fieldnr begins at 1.
[28 Jul 2009 6:56] Susanne Ebrecht
Many thanks for writing a bug report.

Verified by reading source code (source code analysis) of actual 5.1 bzr tree.