MySQL Bugs: #290: Create index with 4.1a

Bug #290	Create index with 4.1a
Submitted:	14 Apr 2003 14:13	Modified:	14 Apr 2003 15:55
Reporter:	Norbert van Nobelen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	mysql-4.1.0-alpha-standard (Official MyS	OS:	Linux (Linux (SuSE 8.0 Professional))
Assigned to:		CPU Architecture:	Any

Description:
CREATE INDEX fails with and error:

CREATE TABLE user (ukey INT NOT NULL AUTO_INCREMENT,username VARCHAR(16),password CHAR(16) NOT NULL, PRIMARY KEY(ukey));
CREATE INDEX user_username ON user(username);

Results in:
mysql> CREATE INDEX user_username ON user(username);
ERROR 1064: 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 'user(username)' at line 1

It does this every time. 

Output mysqlbug:

SEND-PR: -*- send-pr -*-
SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as
SEND-PR: will all comments (text enclosed in `<' and `>').
SEND-PR:
From: root
To: mysql@lists.mysql.com
Subject: [50 character or so descriptive subject here (for reference)]

>Description:
        <precise description of the problem (multiple lines)>
>How-To-Repeat:
        <code/input/activities to reproduce the problem (multiple lines)>
>Fix:
        <how to correct or work around the problem, if known (multiple lines)>

>Submitter-Id:  <submitter ID>
>Originator:    root
>Organization:
 <organization of PR author (multiple lines)>
>MySQL support: [none | licence | email support | extended email support ]
>Synopsis:      <synopsis of the problem (one line)>
>Severity:      <[ non-critical | serious | critical ] (one line)>
>Priority:      <[ low | medium | high ] (one line)>
>Category:      mysql
>Class:         <[ sw-bug | doc-bug | change-request | support ] (one line)>
>Release:       mysql-4.1.0-alpha-standard (Official MySQL-standard binary)

>C compiler:    2.95.3
>C++ compiler:  2.95.3
>Environment:
        <machine, os, target, libraries (multiple lines)>
System: Linux server 2.4.18-4GB #1 Wed Mar 27 13:56:33 UTC 2002 i586 unknown
Architecture: i586

Some paths:  /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc'  CFLAGS='-O2 -mcpu=pentiumpro'  CXX='gcc'  CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors'  LDFLAGS=''  ASFLAGS=''
LIBC:
-rwxr-xr-x    1 root     root      1394238 Jul 12  2002 /lib/libc.so.6
-rw-r--r--    1 root     root     25361424 Jul 12  2002 /usr/lib/libc.a
-rw-r--r--    1 root     root          178 Mar 23  2002 /usr/lib/libc.so
-rw-r--r--    1 root     root       869190 Mar 23  2002 /usr/lib/libc-client.a
lrwxrwxrwx    1 root     root           20 Jul  4  2002 /usr/lib/libc-client.so -> libc-client.so.2001a
-rwxr-xr-x    1 root     root       725296 Mar 23  2002 /usr/lib/libc-client.so.2001a
Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'

How to repeat:
Use the CREATE INDEX on the already created table:
CREATE INDEX user_username ON user(username);

This wil result in:
mysql> CREATE INDEX user_username ON user(username);
ERROR 1064: 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 'user(username)' at line 1

Suggested fix:
ALTER TABLE user ADD INDEX user_username(username);
Works good.

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

This is expected and docummented behaviour. 
As manual says syntax is:

CREATE [UNIQUE|FULLTEXT] INDEX index_name
       ON tbl_name (col_name[(length)],... )

Note the space after tbl_name and (                  
This space is mandatory because without it MySQL treats

user(username) 

as function call 

btw. there is function USER()

mysql> CREATE INDEX user_username ON user (username);
Query OK, 0 rows affected (0.28 sec)