MySQL Bugs: #15509: INSERT INTO Version(.....) Fails with Spurious Syntax Error

Bug #15509	INSERT INTO Version(.....) Fails with Spurious Syntax Error
Submitted:	6 Dec 2005 3:07	Modified:	6 Dec 2005 10:42
Reporter:	Paul Coldrey	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.16	OS:	Linux (linux)
Assigned to:	Aleksey Kishkin	CPU Architecture:	Any

Description:
When I try to insert into a table called Version I get the error:  "ERROR 1064 (42000) at line 5: 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 'Version(Id INTEGER)' at line 1".

It seems to be specific to the table name, but Version seems to be acceptable in other commands like CREATE TABLE, DROP TABLE, SELECT and it is not listed in the reserved words.

My my.ini file is:

-- Start of my.ini --
[mysqld]
set-variable    = lower_case_table_names=OFF
-- End of my.ini --

>mysql --version
mysql  Ver 14.7 Distrib 4.1.8, for Win95/Win98 (i32)

The behaviour is the same in version 4.1.8

How to repeat:
DROP DATABASE IF EXISTS bugme8;
CREATE DATABASE bugme8;
USE bugme8;

CREATE TABLE Version(Id INTEGER);
INSERT INTO Version(Id) VALUES (1);

-- Note that if you replace the above two lines with the two below then it works 
-- as I would have expected (ie no error)
-- CREATE TABLE Wersion(Id INTEGER);
-- INSERT INTO Wersion(Id) VALUES (1);

Apologies, I copied the version string from before I upgraded to the latest release. Actual version is:

>mysql --version
mysql  Ver 14.7 Distrib 4.1.15, for Win32 (ia32)

Paul your error message looks like from 'create table' command. Could you confirm it appears after 'insert' command?

Thanks for the prompt feedback. Mea Culpa. Indeed my test case did not show the behaviour I was trying to show. Here is a slightly more wordy version which elicits the issue:

DROP DATABASE IF EXISTS bugme8;
CREATE DATABASE bugme8;
USE bugme8;

CREATE TABLE Version (
	Id INTEGER PRIMARY KEY AUTO_INCREMENT,
	Data VARCHAR(255)
);
INSERT INTO Version(Id, Data) VALUES (1, 'test');

The Resulting error is "ERROR 1064 (42000) at line 9: 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 'Version(Id, Data) VALUES (1, 'test')' at line 1"

Interestingly it seems my previous test case might also show an anomolous result. It is not clear to me where the error is in the CREATE TABLE command.

checked against mysql on linux - the same behavior

I see. It's known thing..

The point is - if you dont have space after version and write it like
create table version( .....

version treats like function name.
if you do have space after version like
create table version ( ...
'version' treats as just name that can be used for table  or field name.
(the same behaviour for insert etc etc etc)

It's documented here: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

as a workaround I'd suggest to use ` as separator of table names. Like

create table `version`( -- doesn't matter is we have space or not before (