MySQL Bugs: #10779: table names with certain numerical character sequences not accepted

Bug #10779	table names with certain numerical character sequences not accepted
Submitted:	20 May 2005 21:55	Modified:	21 May 2005 11:53
Reporter:	Stephen Brueckner	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	4.1.12	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Apparently there are certain table names that MySQL will not accept, 
and it throws a syntax error "ERROR 1064 (42000)."

However, there does not appear to be any error in syntax, and experimentation 
has shown me that MySQL simply won't accept table names that include certain 
number sequences.

This is bad for me since my table names are automatically 
generated and I can't just change them as a workaround.

My DOS session excerpt below shows one such table name being rejected, 
and then a similar table name with a substring replaced being accepted.

My machine is a DELL GX270.
I'm running Windows XP v5.1.2600 patched with SP2.
The bug is repeatable on two separate machines.
The problem happens on both MySQL 4.1.12 and on 4.0.20a.
mysqld is configured to start automatically as a service.
The bug appears even after a fresh install of MySQL from binary.

How to repeat:
mysql> create database oris;
Query OK, 1 row affected (0.03 sec)

mysql> use oris;
Database changed

mysql> create table foo ( col1 varchar(50) );
Query OK, 0 rows affected (0.16 sec)

mysql> create table 8981e56cce5d ( col1 varchar(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 '8981e
56cce5d ( col1 varchar(50) )' at line 1

mysql> create table 8984444cce5d ( col1 varchar(50) );
Query OK, 0 rows affected (0.09 sec)

Suggested fix:
I wish I knew.  This is probably deep in the internals of MySQL.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.12-nt

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

mysql> create database oris;
Query OK, 1 row affected (0.05 sec)

mysql> use oris;
Database changed
mysql> create table foo ( col1 varchar(50) );
Query OK, 0 rows affected (0.13 sec)

mysql> create table `8981e56cce5d` ( col1 varchar(50) );
Query OK, 0 rows affected (0.05 sec)

mysql> create table `8984444cce5d` ( col1 varchar(50) );
Query OK, 0 rows affected (0.06 sec)

mysql>

Right, you can quote identifiers, as a workaround.

The problem is that when you write 8981e56cce5d, MySQL starts parsing the string, and see
8981e56 and thinks it's a floating point number in the scientific notation. When it reads the next character 'c' it's a syntax error.

In general any name that starts with digits, and 'e' will be a problem.