MySQL Bugs: #2309: Inconsistency in {USING|TYPE} {HASH|BTREE|RTREE}

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.x	OS:
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
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.

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;

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).

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

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
product(s).