Description:
While researching the effects of the fix for Bug#25162, I ran
into some inconsistencies with how the parser handles the
index_type option for index creation.
Some relevant manual pages are:
http://dev.mysql.com/doc/refman/5.1/en/create-index.html
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
As of MySQL 5.1.10, an index_type option (such as USING
BTREE) can be given in either of two locations for index
definitions. This affects CREATE INDEX, CREATE TABLE, ALTER
TABLE, such that all of these statements are legal:
CREATE INDEX i USING BTREE ON t (i);
CREATE TABLE t (i INT, INDEX USING BTREE (i));
ALTER TABLE t ADD INDEX USING BTREE (i);
CREATE INDEX i ON t (i) USING BTREE;
CREATE TABLE t (i INT, INDEX (i) USING BTREE);
ALTER TABLE t ADD INDEX (i) USING BTREE;
The later index_type position (following the column list)
now is the preferred position.
Problem 1:
The original implementation used TYPE rather than USING,
and TYPE is still accepted for backward compatibility.
Probably TYPE should be formally deprecated and support
for it removed at some point. But in the meantime, there
is an inconsistency in that TYPE is sometimes accepted and
sometimes rejected.
These statements work:
CREATE TABLE t (i INT, INDEX (i) TYPE BTREE);
CREATE INDEX i TYPE BTREE ON t (i);
CREATE INDEX i ON t (i) TYPE BTREE;
ALTER TABLE t ADD INDEX (i) TYPE BTREE;
But these statements result in a syntax error at 'BTREE':
CREATE TABLE t (i INT, INDEX TYPE BTREE (i));
ALTER TABLE t ADD INDEX TYPE BTREE (i);
Three possible fixes:
1) Accept TYPE consistently in both places as a synonym for
USING
2) Remove support for TYPE as a synonym for USING
3) Accept TYPE consistently but issue a deprecation warning,
and schedule a release (6.0.x?) in which support for it will
be removed
I'd prefer to see 3) because TYPE is old syntax that should
be junked.
Problem 2:
The manual says that index_type in the earlier position is
now deprecated and support for index_type in that position
will be removed in a future version. But no deprecation
warning is generated if you use index_type in the earlier
position.
Note: index_type was first allowed following the column list
in 5.1.10, but this parser change has been backported to
5.0.60, so the problems reported here are a 5.0-and-up
issue, not a 5.1-and-up issue.
How to repeat:
Test scripts to demonstrate the effects described above:
These sets of statements work:
DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT);
CREATE INDEX i USING BTREE ON t (i);
SHOW CREATE TABLE t\G
ALTER TABLE t DROP INDEX i;
CREATE INDEX i ON t (i) USING BTREE;
SHOW CREATE TABLE t\G
DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT, INDEX (i) USING BTREE);
SHOW CREATE TABLE t\G
DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT, INDEX USING BTREE (i));
SHOW CREATE TABLE t\G
DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT);
ALTER TABLE t ADD INDEX (i) USING BTREE;
SHOW CREATE TABLE t\G
ALTER TABLE t DROP INDEX i;
ALTER TABLE t ADD INDEX USING BTREE (i);
SHOW CREATE TABLE t\G
These sets of statements show that TYPE is
accepted inconsistently:
DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT);
CREATE INDEX i TYPE BTREE ON t (i);
SHOW CREATE TABLE t\G
ALTER TABLE t DROP INDEX i;
CREATE INDEX i ON t (i) TYPE BTREE;
SHOW CREATE TABLE t\G
DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT, INDEX (i) TYPE BTREE);
SHOW CREATE TABLE t\G
DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT, INDEX TYPE BTREE (i));
SHOW CREATE TABLE t\G
DROP TABLE IF EXISTS t;
CREATE TABLE t (i INT);
ALTER TABLE t ADD INDEX (i) TYPE BTREE;
SHOW CREATE TABLE t\G
ALTER TABLE t DROP INDEX i;
ALTER TABLE t ADD INDEX TYPE BTREE (i);
SHOW CREATE TABLE t\G
