Bug #8846 Syntax error using column named x or y in an index with a specified length
Submitted: 28 Feb 2005 15:43 Modified: 1 Mar 2005 16:50
Reporter: Logan Hanks Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:
Assigned to: CPU Architecture:Any

[28 Feb 2005 15:43] Logan Hanks
Description:
Use of the names "x" or "y" for a column raises a syntax error if one attempts to specify an index length involving such columns.  Use of certain other single-letter names that I have tried, such as "a" or "z", does not raise any error.  I have not tried every single-letter combination.  Quoting the column name in the index specification works around the issue.

How to repeat:
The following query raises a syntax error:

mysql> create table t (x TEXT, UNIQUE (x(128)));
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 'x(128)))' at line 1

Yet these variations do not:

mysql> create table t (x TEXT, UNIQUE (`x`(128)));
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (z TEXT, UNIQUE (z(128)));
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (x TEXT, UNIQUE (x));
ERROR 1170 (42000): BLOB/TEXT column 'x' used in key specification without a key length
[expected]
[1 Mar 2005 16:50] MySQL Verification Team
Hi,

This is not a bug.
There are GIS functions X() and Y(), that is why you get syntax error.