Bug #40710 "USING HASH" ignored when creating indexes on InnoDB tables
Submitted: 13 Nov 2008 17:33 Modified: 13 Nov 2008 18:34
Reporter: Tim Soderstrom Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1.29rc OS:Any (OS X (from vanilla source), Gentoo)
Assigned to: CPU Architecture:Any
Tags: alter, hash, indexes
Triage: Triaged: D5 (Feature request)

[13 Nov 2008 17:33] Tim Soderstrom
Description:
MySQL is allowing the "USING HASH" modifier when adding an index using the ALTER TABLE command on an InnoDB table. It should fail, since InnoDB does not support HASH indexes.

How to repeat:
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string` text,
  PRIMARY KEY (`num1`),
  KEY `string_idx` (`string`(16)) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> ALTER TABLE t1 DROP INDEX string_idx;
Query OK, 9 rows affected (0.19 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string` text,
  PRIMARY KEY (`num1`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE t1 ADD INDEX string_idx USING HASH (string(8));
Query OK, 9 rows affected (0.12 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `string` text,
  PRIMARY KEY (`num1`),
  KEY `string_idx` (`string`(8)) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW INDEXES FROM t1\G
*************************** 1. row ***************************
       Table: t1
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: num1
   Collation: A
 Cardinality: 2
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: t1
  Non_unique: 1
    Key_name: string_idx
Seq_in_index: 1
 Column_name: string
   Collation: A
 Cardinality: 2
    Sub_part: 8
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
2 rows in set (0.00 sec)

Suggested fix:
MySQL should throw a warning or error and remove the USING HASH modifier. This is confusing and incorrect since the index is actually using a BTREE according to the SHOW INDEXES command.
[13 Nov 2008 17:36] Paul Dubois
http://dev.mysql.com/doc/refman/5.1/en/create-index.html says:

"If you specify an index type that is not legal for a given storage engine, but there is another index type available that the engine can use without affecting query results, the engine uses the available type."
[13 Nov 2008 17:38] Tim Soderstrom
Fair enough, but MySQL shouldn't fail silently. It should flag a warning if nothing else. The fact that it is documented doesn't make it right :)
[13 Nov 2008 18:34] Valeriy Kravchuk
Looks like a reasonable feature request for me.