MySQL Bugs: #65605: Column names should not allow reserved words

Bug #65605	Column names should not allow reserved words
Submitted:	13 Jun 2012 17:21	Modified:	14 Jul 2012 5:30
Reporter:	Emanuel London	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S4 (Feature request)
Version:	5.x	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It seems you can create a table with a column name using a reserved word and insert data into that table as long as you don't insert into the column with the reserved name.

This seems completely backward, especially since there is no error that indicates that the problem with your query is that one of the column names is a reserved word.

How to repeat:
Create a table with several rows - and one called "key". Try to insert data first without inserting into the "key" column, then try inserting into the key column.

Suggested fix:
Fixes can be one of two things:

On table/column creation, and error should be generated saying a certain name is not allowed.
OR

If you try to insert data into a column that has a reserved word name, there should be an error that is more specific than "syntax error", which can drive a sane person mad if they don't suspect that they are using a reserved word (especially if in 5 years of using MySQL they had never had that problem)

Can you, please, provide some example (like the one below), that demonstrates the problem.

I see the following:

mysql> create table tk(c1 int, key int);
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 'int)'
 at line 1

So, you can NOT use reserved word "as is" in CREATE. But you can quote it:

mysql> create table tk(c1 int, `key` int);
Query OK, 0 rows affected (0.33 sec)

mysql> insert into tk values(1,1);
Query OK, 1 row affected (0.09 sec)

mysql> insert into tk(c1, key) values(1,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 'key)
values(1,1)' at line 1
mysql> insert into tk(c1, `key`) values(1,1);
Query OK, 1 row affected (0.05 sec)

Surely you have to quote reserved word whenever it is used as column name. All these is expected, documented and more or less the same in all RDBMSes I know.

So, what is the problem?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".