ON condition in JOIN must be between parenthesis

[GoogleCodeExporter]

What steps will reproduce the problem?
1. define query :
"select t1.* from t1 left outer join t2 on left(t1.c1,6) = t2.c2"

2. subsequent parse and create will produce :
SELECT t1.* FROM t1 LEFT JOIN t2 ON (t1.c1 6 = t2.c2) 

What is the expected output? What do you see instead?
expected :
select t1.* from t1 left outer join t2 on left(t1.c1,6) = t2.c2

What version of the product are you using? On what operating system?
checkout after r1086

Please provide any additional information below.
when the "ON" condition is like "on (left(t1.c1,6) = t2.c2)", it runs as 
expected.

Original issue reported on code.google.com by Henk.Blo...@gmail.com on 22 Feb 2014 at 2:03

[GoogleCodeExporter]

That's a parser problem, not as simple as the last issue. I think the reason is 
the left() function, which could be misinterpreted as join type.

Original comment by pho...@gmx.de on 22 Feb 2014 at 7:28

• Changed state: Accepted

[GoogleCodeExporter]

The FromProcessor has a simple token recognition, is there a token "LEFT", it 
will be part of the join-type. 

I have looked into the MySQL manual 5.7, the FROM clause is very complex, only 
some parts have been included into the parser. 

So the best way for the moment is to use parentheses, to prevent a single token 
"LEFT" on the wrong place. If the left() function is part of a longer 
expression (i.e. within parentheses), the processor will see only the complete 
expression as token and will not use it as join-type. The same procedure should 
be used for other functions, which collide with the keywords RIGHT, LEFT, JOIN, 
OUTER, NATURAL, CROSS and INNER. 

I will set this issue to "enhancement", because it needs some larger 
refactorings within the FromProcessor.

Original comment by pho...@gmx.de on 22 Feb 2014 at 8:11

• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

Ls,
thanks for the information.
Unfortunately, I don't have control over the SQL statements,
so I'll have to wait until this is fixed.
Regards and keep up the good work.
Henk

Original comment by Henk.Blo...@gmail.com on 22 Feb 2014 at 8:20

[GoogleCodeExporter]

If you don't have a fix yet, may it could be a good idea to replace all "left(" 
with a simple String replace, i.e. with "_left" or "tmpleft", then parse the 
statement and replace the temporary function names with "left" again (within 
the parser output array). After that you can build the SQL statement with the 
Creator.

Original comment by pho...@gmx.de on 2 Apr 2014 at 6:30

• Changed state: Started

[GoogleCodeExporter]

Perhaps detect if the next token after LEFT or RIGHT is OUTER or JOIN, as those 
are the only two keywords that may legally follow LEFT or RIGHT in the context 
of the FROM clause.

Original comment by greenlion@gmail.com on 3 Apr 2014 at 8:58

[GoogleCodeExporter]

He, good idea. I have added some code, it seems to work. See r1278.

Original comment by pho...@gmx.de on 4 Apr 2014 at 1:41

[GoogleCodeExporter]

Original comment by pho...@gmx.de on 15 Sep 2014 at 7:07

• Changed state: Fixed