MySQL Bugs: #16212: create index can't use part of the column

Bug #16212	create index can't use part of the column
Submitted:	5 Jan 2006 5:39	Modified:	6 Jan 2006 11:49
Reporter:	Lu Tao	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.16	OS:	Windows (windows/HP-UX)
Assigned to:		CPU Architecture:	Any

Description:
create index can't use part of the column,while the manual says it can.

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

index_col_name:
    col_name [(length)] [ASC | DESC]

In MySQL 5.0, CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.2, “ALTER TABLE Syntax”. 

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 13.1.5, “CREATE TABLE Syntax”. CREATE INDEX allows you to add indexes to existing tables. 

A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns. 

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index a prefix consisting of the first length characters of each column value. BLOB and TEXT columns also can be indexed, but a prefix length must be given. 

The statement shown here creates an index using the first 10 characters of the name column: 

CREATE INDEX part_of_name ON customer (name(10));

How to repeat:
$ cd /usr/local/mysql/bin
$ ./mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.16-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql>  create table a1(uuid varchar(32),code char(2));
Query OK, 0 rows affected (0.09 sec)

mysql> create unique index ui  on a1(uuid(10),code(1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid(10),code(1))' at line 1
mysql> create unique index ui  on a1(uuid,code);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> create unique index ui2 on a1(uuid(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid(10))' at line 1

Suggested fix:
create table a1(uuid varchar(32),code char(2)); should work

Suggested fix:
create table a1(uuid varchar(32),code char(2)); should work
is a bug too.
it should be
create index ui2 on a1(uuid(10))  should work

It seems work when the column type is char but not work when the column type is varchar 
mysql> create unique index ui2 on a1(code(1));
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

oracle 9i supports create index ui3 on a1(substr(uuid,1,10));
while 
in MySQL
create index ui3 on a1(substring(uuid,1,10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'substring(uuid,1,10))' at line 1

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

It's not about "part of the column" in the index, see where you got the syntax error, near 'uuid(10)' not near '(10)'. The problem is in the word 'uuid'.

UUID() is a  built-in MySQL function. So you need to write a space beween "UUID" and "(" if you don't mean a function in this context. See

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html