Bug #16980 Can't use a column named RELEASE anymore since v5.x update.

• Submitted: 31 Jan 2006 15:52
• Modified: 31 Jan 2006 16:09
• Reporter: Maketsi
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.18
• OS: Linux (Suse Linux 9.2, kernel 2.6.8-24)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[31 Jan 2006 15:52] Maketsi

Description:
Can't use a column named RELEASE anymore on 5.x version.

Database was created with 4.x server, and used successfully on it. Now mysql complains about sql syntax when seeing the word "RELEASE", even when used correctly on select statement (manual doesn't say this should do something special).

# mysql -V
mysql  Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0
# mysqld -V
mysqld  Ver 5.0.18-standard for pc-linux-gnu on i686 (MySQL Community Edition - Standard (GPL))

How to repeat:
Simplified table structure follows:
mysql> desc movie;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| title   | char(50) | YES  |     | NULL    |       |
| release | char(50) | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+

Statement to be executed: SELECT title, release FROM movie;
Error message got:
"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 'release FROM movie' at line 1"

Also, I can't create that kind of table anymore:
create table movie ( title char(50), release char(50) );

"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 'release char(50) )' at line 1"

This is how it still works, but the functionality of all old programs is already broken:
create table movie ( title char(50), a.release char(50) );
SELECT title, movie.release FROM movie;

Suggested fix:
Following statement should still work as it did on a 4.x version. SQL standard doesn't know a "select... release..." statement either.

SELECT title, release FROM movie;

[31 Jan 2006 16:09] MySQL Verification Team

Please read:

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

and you should notice RELEASE is now a reserved word so you
need to handle it how reserved word:

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db34
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db34
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-debug

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

mysql> create table movie ( title char(50), `release` char(50) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into movie (title, `release`) values ("aaa", "aaa-21"); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from movie;
+-------+---------+
| title | release |
+-------+---------+
| aaa   | aaa-21  |
+-------+---------+
1 row in set (0.00 sec)

mysql>