Bug #75489 Workbench reports Syntax error incorrectly
Submitted: 13 Jan 2015 11:17 Modified: 25 Feb 2015 9:38
Reporter: ian sherrington Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S2 (Serious)
Version:6.2.4 OS:Windows (Microsoft Windows 7 Ultimate)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[13 Jan 2015 11:17] ian sherrington 
Description:
CREATE PROCEDURE `new_procedure` ()
BEGIN

select * from mtable where mtable.1_select = 'X';

END

Produces syntax error on mtable.1_select. When I change column name to start with non-numeric character, syntax error is resolved. However, I have used column names beginning with numerals in many places in this project and this was not an error until I just upgraded to latest Workbench.

How to repeat:
Just enter the code into SQL Editor
[13 Jan 2015 11:30] MySQL Verification Team 
Hello ian sherrington,

Thank you for he report.

Thanks,
Umesh
[13 Jan 2015 11:32] MySQL Verification Team 
How to repeat:

use test;
create table t1(1_id int not null);
insert into t1 values(1),(2),(3);
select * from t1 where t1.1_id = 1;

^^ falsely shows syntax error

From CLI, valid SQL:

[test]> use test;
Database changed
[test]> create table t1(1_id int not null);
Query OK, 0 rows affected (0.09 sec)

[test]> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

[test]> select * from t1 where t1.1_id = 1;
+------+
| 1_id |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
[13 Jan 2015 11:32] MySQL Verification Team 
Screenshot..

Attachment: 75489.png (image/png, text), 35.17 KiB.
[13 Jan 2015 13:40] Peter Laursen 
Server documentation http://dev.mysql.com/doc/refman/5.6/en/identifiers.html states "Identifiers may begin with a digit but unless quoted may not consist solely of digits".

This the WB parser got wrong.

-- Peter
-- not a MYSQL/Oracle person
[25 Feb 2015 9:38] Mike Lischke 
Yes, right, MySQL allows identifiers to start with a digit (a very bad decision actually). In order to make this possible the server parser does some special handling in its manually written lexer, which we cannot do in the WB parser. We don't use a handwritten lexer (but a generated one, which is way better to maintain) which would be the only solution to make parsing such a construct possible. That's why we cannot fix it.

Some additional info: the problem stems from the fact that .1 is parsed as float instead of a dot and a following identifier. By inserting a space after the dot you can "fix" it. My general advice here: don't use identifiers starting with a digit.