MySQL Bugs: #23216: UPDATE/INSERT on ENUM columns fails unless column name is between ` (backquotes)

Bug #23216	UPDATE/INSERT on ENUM columns fails unless column name is between ` (backquotes)
Submitted:	12 Oct 2006 14:30	Modified:	13 Oct 2006 9:05
Reporter:	Marcello Golfieri	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Linux (Linux x86 (Fedora Core 5))
Assigned to:		CPU Architecture:	Any
Tags:	update insert backquote fail sql statement

Description:
UPDATE/INSERT sql statements on ENUM columns fails unless column name is between `` (backquotes).  Error given is:

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 '<COLUMN NAME> ....' at line 1

it can be a multicolumn INSERT or UPDATE, doesn't matter.  The ENUMerated column name has to be surrounded by backquotes to work (``).  Other columns to be updated in the same statement can be updated/inserted without needing them.

How to repeat:
CREATE TABLE `T_UTENTI` (
  	`CD_USER` int unsigned NOT NULL AUTO_INCREMENT,
	`USERNAME` varchar(20) UNIQUE NOT NULL,
	`PASSWORD` varchar(20) NOT NULL,
  	`FIRSTNAME` varchar(50) NOT NULL,
  	`LASTNAME` varchar(50) NOT NULL,
  	`JOINED` datetime NULL,
	`LASTACCESS` datetime NULL,
	`LASTMODIFIED` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  	`EMAIL` varchar(40) NOT NULL,
  	`ADDRESS` varchar(50) NOT NULL,
	`CITY` varchar(15) NOT NULL,
  	`ZIP` varchar(5) default NULL,
  	`PHONE` varchar(20) NOT NULL,
	`GROUP` enum('admin','powerusers','users') NOT NULL default 'users',
	`ACTIVE` boolean DEFAULT true,
	PRIMARY KEY  (`CD_USER`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT INTO T_UTENTI ( USERNAME,PASSWORD,FIRSTNAME,LASTNAME,EMAIL,ADDRESS,CITY,ZIP,PHONE,GROUP) VALUES('dummy','password','Caio','Massimo','my@email.com','Viale Trastevere 60','Roma','10000','1234567','admin');

------> gives:

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 'GROUP) VALUES('teddy','sunsuite','Roberto','Curci','sunsuite@ngi.it','Via Rosa A' at line 1

Same with UPDATE ... SET ... WHERE.

Suggested fix:
Put double quotes around the ENUMerated column (in this case `GROUP`):

INSERT INTO T_UTENTI ( USERNAME,PASSWORD,FIRSTNAME,LASTNAME,EMAIL,ADDRESS,CITY,ZIP,PHONE, `GROUP`) VALUES('dummy','password','Caio','Massimo','my@email.com','Viale Trastevere 60','Roma','10000','1234567','admin');

------> gives:

Query OK, 1 row affected (0.02 sec)

Same with UPDATE ... SET ... WHERE.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.26, and inform about the results.

As you read from the OS field, I'm using Fedora Core 5.  As of today (12-10-2006, h16.57 Rome Timezone) 'yum install mysql' gives me up-to-date status with 5.0.22.

I can't install latest mysql snapshot, 'cos I need to stick to official fedora repositories. If this machine i'm working were mine, I'd already tried by myself. This is a production server, the less I mess around, the better, you know...

Sorry about that.

apologies, for getting into here...

But, does that only apply if the column is called "group"?
Because this is a reserved word. see also http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

Case closed, you're SOOOO right

didn't think about it.

Actually, being the term GROUP inside () in the INSERT statement didn't even for a moment make me think of such a motivation.

Even if this is not a bug, it should be anyway be reworked, IMHO.

Thanks a lot.