Bug #57920 Inconsistency in syntax and example for CREATE INDEX

• Submitted: 2 Nov 2010 11:41
• Modified: 14 Feb 2011 15:18
• Reporter: Magne Mæhre
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.1, 5.5
• OS: Any
• Assigned to: Paul DuBois
• CPU Architecture: Any

[2 Nov 2010 11:41] Magne Mæhre

Description:
In the documentation for both 5.1 and 5.5, there is an inconsistency in the syntax and an example.

  http://dev.mysql.com/doc/refman/5.5/en/create-index.html

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_type:
    USING {BTREE | HASH}

This is a deprecated syntax, and should not be used in the documentation.
<index_type> should be moved after the ON-clause

There is also an SQL example in the text
  CREATE TABLE lookup (id INT) ENGINE = MEMORY;
  CREATE INDEX id_index USING BTREE ON lookup (id);

which also uses the deprecated syntax.  The example should be rewritten to use the correct syntax   "... ON lookup(id) USING BTREE"

See also WL#5185 and Bug#36140

How to repeat:
Documentation issue

[2 Nov 2010 11:42] Magne Mæhre

Note that the documentation even says:
"Use of this option before the ON tbl_name clause is deprecated; support for use of the option in this position is to be dropped in a future MySQL release. If an index_type option is given in both the earlier and later positions, the final option applies."

[2 Nov 2010 21:02] Sveta Smirnova

Thank you for the report.

Verified as described.

[13 Nov 2010 16:11] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (merge vers: 5.6.99-m5) (pib:21)

[13 Nov 2010 16:32] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20101113160336-atmtmfb3mzm4pz4i) (version source revid:[e-mail]-20101113152540-gxro4g0v29l27f5x) (pib:21)

[14 Feb 2011 15:18] 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 products.

I updated the example not to use the deprecated syntax.

The appearance of index_type before ON in the syntax description is correct until that usage has been removed from the parser. It might be deprecated but it is still legal.