MySQL Bugs: #16112: Inconsistent treatment of reserved words

Bug #16112	Inconsistent treatment of reserved words
Submitted:	1 Jan 2006 5:56	Modified:	3 Jan 2006 18:44
Reporter:	Ken Resander	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	MySQL 5.0.16	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
The following is accepted is valid:

   CREATE TABLE tb1 (day INT);  -- day is a reserved word

but not this

   CREATE TABLE tb2 (starting INT);  -- starting is a reserved word

   getting syntax error 'near starting int' 

Most reserved words in MySQL are useful, common words in English.
A programmer should be allowed to use these as column names.

Best regards
Ken Resander

How to repeat:
Not applicable

Suggested fix:
Not applicable

Could you please point me where you found that DAY is reserved word,
searching the Manual:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

I was unable to find it, instead I verified that DAY is reserved word for
MAxDB server.

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> CREATE TABLE tb1 (day INT);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE tb2 (starting INT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that correspond
mysql> CREATE TABLE tb2 (`starting` INT);
Query OK, 0 rows affected (0.06 sec)

mysql>

http://dev.mysql.com/doc/refman/5.0/en/legal-names.html

An identifier may be quoted or unquoted. If an identifier is a reserved word or contains special characters, you must quote it whenever you refer to it. For a list of reserved words, see Section 9.6, “Treatment of Reserved Words in MySQL”. Special characters are those outside the set of alphanumeric characters from the current character set, ‘_’, and ‘$’. 

The identifier quote character is the backtick (‘`’): 

Thanks in advance.

DAY is not listed as a reserved word here:

http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

The DAY keyword is listed in Treatment of Reserved words in
the Compiled HTML documentation that came with MySQL 5.0.16
NOINSTALLER version.

To allow or not allow reserved words as column names?

This is a no-win situation. Allowing them violates the SQL standard
(although I am not sure where in the standard to look for this).
Disallowing them can produce puzzling error messages, such as
the one received for create table xx (starting TIME).

Suggestion:

Output of error message  'use of reserved word as column 
name is not allowed' would be helpful.

Ken Resander

I verified in the Manual shipped with 5.0.18 and the word day not makes
part of the reserved words.