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: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.0.16 | OS: | Windows (windows/HP-UX) |
Assigned to: | CPU Architecture: | Any |
[5 Jan 2006 5:39]
Lu Tao
[5 Jan 2006 7:55]
Lu Tao
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
[5 Jan 2006 7:58]
Lu Tao
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
[5 Jan 2006 8:06]
Lu Tao
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
[6 Jan 2006 11:49]
Sergei Golubchik
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