Bug #2309 Inconsistency in {USING|TYPE} {HASH|BTREE|RTREE}
Submitted: 7 Jan 2004 8:22 Modified: 1 Apr 2004 10:49
Reporter: Paul DuBois Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.x OS:
Assigned to: Paul DuBois CPU Architecture:Any

[7 Jan 2004 8:22] Paul DuBois
4.1.x allows index types to be specified, for storage engines
that provide different index implementations.

HEAP tables allow HASH or BTREE, but not RTREE indexes.
MyISAM tables allow BTREE, but not HASH or RTREE indexes.
InnoDB - same as MyISAM.

However, handling of disallowed index types is not consistent.

If you specify RTREE for HEAP, MyISAM, or InnoDB, an error occurs:
ERROR 1210: Wrong arguments to RTREE INDEX

If you specify HASH for MyISAM or InnoDB, no error occurs.  The
index is created as a BTREE index, and this happens silently with
not even a warning.

How to repeat:
See above.

Suggested fix:
I guess that error 1210 should be issued in all cases
when the type specifier is invalid for the storage engine.
[8 Jan 2004 2:24] Ramil Kalimullin
hmm, we cannot just disable using HASH for non-HEAP table keys.
Otherwise the following might not work:

create table t1(a int, key (a)) type=heap;
alter table t1 type=myisam;
[8 Jan 2004 2:30] Ramil Kalimullin
Sorry, I meant:

create table t1(a int, key USING HASH(a)) type=heap;
alter table t1 type=myisam;

will not work (because we have HA_KEY_ALG_UNDEF - default key type).
[30 Mar 2004 3:46] Michael Widenius
I think the current conversions are ok.  We should be able to do a conversion when there is no user visible changes in functionallity between the index.

RTREE is different from BTREE becasue you need this to be able to use spatial data.

Paul, can you be kind and document this
[1 Apr 2004 10:49] 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