MySQL Bugs: #1132: Create table fails when certain table name structure

Bug #1132	Create table fails when certain table name structure
Submitted:	25 Aug 2003 4:47	Modified:	28 Aug 2003 2:52
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	Ver 8.40 Distrib 4.0.12, for pc-linux	OS:	Linux (Red Hat Linux 9.0)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:

When one creates table which starts with any amount of numbers and then character 'e' and then a number again, MySQL reports for error:

create table 20e09923item (id int unsigned not null primary key);
ERROR 1064: 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 '20e09923item (id int unsigned not null primary key)' at line 1

It seems that it doesn't matter how the table name continues and only with letter 'e' table creation failes. 

Tested with binary and source-versions of MySQL 4.0.12.

It's a problem for example when md5-hashes are used for table names.

How to repeat:

Repeat with:

create table 20e09923item (id int unsigned not null primary key);
ERROR 1064: 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 '20e09923item (id int unsigned not null primary key)' at line 1

or some other create with numbers-'e'-number-start.

MySQL supports some table names that start with a digit without need to
quote the name, but not in all cases. MySQL requires backticks around
table names that start with <digit>{e|E}<digit>[<string>], because this
is considered a number by MySQL. MeN is same as M * pow(10,N), which is
a shortened syntax for that, same as many pocket calculators use.

Since SQL standard requires backticks around table names that start with
a digit and since we don't want to make MySQL parser slower just to support
table names such as 123e456abc without quoting, backticks will be required
around the table name.

Example:

CREATE TABLE `1e1a` (i int);

will work.

Regards,
Jani