Bug #11391 Create table 2e2 fails, not documented
Submitted: 16 Jun 2005 17:03 Modified: 23 Jun 2005 18:26
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:All OS:
Assigned to: Stefan Hinz CPU Architecture:Any

[16 Jun 2005 17:03] Jeremy Cole
Description:
The parser correctly or incorrectly parses numbers of the for XeY in the context of a table name.  This causes certain table names to be illegal when other (very similar) names are legal.

http://dev.mysql.com/doc/mysql/en/legal-names.html hints at the problem, but if this can't or won't be fixed in the parser, this should be documented.  Currently the documentation states:

"It is recommended that you do not use names like 1e, because an expression like 1e+1 is ambiguous. It might be interpreted as the expression 1e + 1 or as the number 1e+1, depending on context."

Specifically, it might make sense to mention that an MD5 by itself is not good for a table name, because for some strings it will produce a string of the form XeY which will be disallowed as a table name without quoting.

How to repeat:
mysql> create table 2a2 (i int);
Query OK, 0 rows affected (0.12 sec)

mysql> create table 2e2 (i int);
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 '2e2 (i int)' at line 1

mysql> create table 2a2a (i int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table 2e2a (i int);
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 '2e2a (i int)' at line 1 

Suggested fix:
Fix the parser to ignore XeY in table names, or update documentation to reflect that they are disallowed.
[16 Jun 2005 17:19] Kolbe Kegel
Either behavior needs to be changed (preferable) or documentation needs to be updated to explicitly outline this case.
[23 Jun 2005 18:26] Mike Hillyer
Documentation updated as follows, should hopefully make things a bit more clear:

 <para>
   It is recommended that you do not use names of the pattern
   <literal><replaceable>X</replaceable>e<replaceable>X</replaceable></literal>,
   such as <literal>1e</literal> or <literal>2e2</literal>, because an
   expression like <literal>1e+1</literal> is ambiguous. It might be
   interpreted as the expression <literal>1e + 1</literal> or as the
   number <literal>1e+1</literal>, depending on context.
  </para>

  <para>
   Be careful when using <literal>MD5</literal> to produce table names,
   as it can produce illegal tables names such as the ones listed above.
  </para>