MySQL Bugs: #43896: Qualified identifier values errors

Bug #43896	Qualified identifier values errors
Submitted:	26 Mar 2009 18:52	Modified:	26 Mar 2009 21:27
Reporter:	Marcin Kurzyna	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.1.30	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	identifier

Description:
While using MD5 values as identifiers we've run into some values that without any obvious reason can't be used unquoted. We found it when using ids for savepoints but the issue concerns all identifiers (table names, field names, etc).

The identifier that doesn't work is: 28e4f4bba1ff3b5ccf7d59e31c7d2c44
However changing 3rd character makes it fine: 28a4f4bba1ff3b5ccf7d59e31c7d2c44

We're talking about unquoted values.
When quoted `28e4f4bba1ff3b5ccf7d59e31c7d2c44` it works fine.

The server returns:

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 '28e4f4bba1ff3b5ccf7d59e31c7d2c44' at line 1

How to repeat:
SELECT NOW() AS 28e4f4bba1ff3b5ccf7d59e31c7d2c44;

Looks like it's being treated as a number in scientific notation, followed by an alias.

mysql> select 28e4f4bba1ff3b5ccf7d59e31c7d2c44;
+------------------------------+
| f4bba1ff3b5ccf7d59e31c7d2c44 |
+------------------------------+
|                       280000 | 
+------------------------------+
1 row in set (0.05 sec)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

See Paul's comment and http://dev.mysql.com/doc/refman/5.1/en/identifiers.html for details

It does indeed treat it as scientific notation, but why is that not a bug? Shuldn't this trigger parse error at least? Also the 28e4xxxx is not a valid scientific notation thus it shouldn't be treated as such.

Also why those are equivalent?

mysql> select 24e4field;
mysql> select 24e4 as field;

+--------+
| field  |
+--------+
| 240000 |
+--------+
1 row in set (0.00 sec)

Especially when this is not (both start with integer):

mysql> select 1field;
ERROR 1054 (42S22): Unknown column '1field' in 'field list'

Referring to the documentation:
http://dev.mysql.com/doc/refman/5.1/en/identifiers.html

md5 hash in question should be a qualified identifier (less then 64chars, contains only alphanumeric characters and isn't a valid number in any notation so can't be treated or cast to integer).

TIA

Ok, I see the explicit notion about md5() in docs, however i still think i have a point there; md5 in form of XeYzzzzzzzzzzz shoun't be ambiguous - it's clearly not a scientific notation.

Althought it's stated in documentation as a possibly invalid id so this ain't a bug after all.

Thanks for your time.

(although i'd like to hear your answer as to why this might me ambiguous just to satisfy my curiosity - TIA)