MySQL Bugs: #59871: MySQL claims error in query when field name = 'maxValue'

Bug #59871	MySQL claims error in query when field name = 'maxValue'
Submitted:	1 Feb 2011 15:13	Modified:	2 Feb 2011 11:44
Reporter:	Manuel Ruetz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S2 (Serious)
Version:	5.5.8	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	column name, error 1064, Field Name, maxvalue

Description:
MySQL claims "You have an error in your SQL syntax..." when inserting or querying a table where a column is named 'maxValue'

How to repeat:
Create a table with a field named 'maxValue':

CREATE TABLE `test`.`test`(
`index` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`maxValue` INT UNSIGNED, 
PRIMARY KEY (`index`));

Try to insert some data:

INSERT INTO test (INDEX,  maxValue) VALUES (1,2)

Try to query data:

SELECT INDEX, maxvalue FROM test;

Suggested fix:
When quotes are used in the query for the field names (`),
the query is executed without any exceptions.

I cannot find any hints about 'maxValue' is a reserved keyword or
cannot be used as a field name.
Even 'max' works as a field name.

It is listed here:
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
(but not here: http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html)

peter
(not a MySQL person)

Both INDEX and MAXVALUE are listed at http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html. This is not a bug.

Look:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.8 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table mv (
    -> `index` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `maxValue` INT UNSIGNED,
    -> PRIMARY KEY (`index`));
Query OK, 0 rows affected (0.22 sec)

mysql> INSERT INTO mv (INDEX,  maxValue) VALUES (1,2);
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 'INDEX
,  maxValue) VALUES (1,2)' at line 1
mysql> INSERT INTO mv (`index`,  `maxValue`) VALUES (1,2);
Query OK, 1 row affected (0.03 sec)

mysql> select index, maxValue from mv;
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 'index
, maxValue from mv' at line 1
mysql> select `index`, maxValue from mv;
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 'maxVa
lue from mv' at line 1
mysql> select `index`, `maxValue` from mv;
+-------+----------+
| index | maxValue |
+-------+----------+
|     1 |        2 |
+-------+----------+
1 row in set (0.00 sec)

So, just quote reserved words if you use them as column names...

I wonder why other reserved keywords can be used without quotes...

Please, provide some example when other keyword from that manual page can be used in the same context without quotes.

We have tables with "MAX" as identifier, but I saw that this is not
listed in the keywords list. 
Sorry, I think the status can be set to closed.

CREATE TABLE `test_2`(
`max` INT UNSIGNED NOT NULL,
PRIMARY KEY (`max`)
);

INSERT INTO test_2 (MAX) VALUES (123);