MySQL Bugs: #45602: Left Outer Join with nested {OJ {OJ ... } }

Bug #45602	Left Outer Join with nested {OJ {OJ ... } }
Submitted:	19 Jun 2009 4:46	Modified:	16 Oct 2012 5:16
Reporter:	Bogdan Degtyariov	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S1 (Critical)
Version:	5.0.83, 5.1.35, 5.1.53, 6.0.9	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	curly brackets, join, oj, regression

Description:
This bug report is derived from the bug #28317 (http://bugs.mysql.com/bug.php?id=28317).
Though the bug with parsing {OJ ...} has been fixed, there is a problem in MySQL 5.1 (5.1.34 tested) with nested {OJ{OJ }}.

How to repeat:
create table a(a1 int);
create table b(a1 int, b1 int);
create table c(b1 int, c1 int);

This SELECT fails in 5.1.34, but works in 5.0.75:

SELECT * FROM  {OJ {OJ  a LEFT OUTER JOIN b ON a.a1=b.a1 } LEFT OUTER JOIN c ON b.b1 =
c.b1}

If remove one {OJ it does work in 5.1.34, but fails in 5.0.75:

SELECT * FROM  {OJ  a LEFT OUTER JOIN b ON a.a1=b.a1 LEFT OUTER JOIN c ON b.b1 = c.b1}

If removing one {OJ in other way fails in 5.1.34, but works in 5.0.75:

SELECT * FROM  {OJ  a LEFT OUTER JOIN b ON a.a1=b.a1 } LEFT OUTER JOIN c ON b.b1 = c.b1

So, it is not only about nested {OJ, but also about the position, where the statements has
closing }. Versions 5.1 and 5.0 treat it in different way.

I have performed the same test with MySQL 6.0.9-alpha. It works exactly as the version
5.1.34 does.

Can anyone provide a authoritative source on the OJ escape syntax which support this nesting? The only authoritative source I found, which is related to ODBC, at http://msdn.microsoft.com/en-us/library/ms714641(VS.85).aspx, states that:

The escape sequence for outer joins is

	{oj outer-join}

where outer-join is

	table-reference {LEFT | RIGHT | FULL} OUTER JOIN {table-reference | outer-join} ON search-condition

table-reference specifies a table name, and search-condition specifies the join condition between the table-references.

Which does not support the nesting of the outer join escape sequence, only the nesting of the outer-join itself.

Any chances to resolve this bug?? We are stuck upgrading mysql from 5.0.77 to 5.1 or 5.5. 
We have hundreds of queries with nested {oj's. 

Any comments are welcome. 
Thanks in advance!!
Nacho.

I would backup your queries and Search & replace..
OJ --> ""
{ --> ""
} --> ""

Could this change in behavior at least be documented on the 5.1+ manuals?

The old behavior could easily be restored by slightly shuffling parser grammer rules, it would come at the cost of adding a few more shift/reduce conflicts to the grammer ... not really sure whether that's worth it ...

Anyway, patch is attached ...

suggested grammer patch to restore the old nestable behavior

Attachment: bug-45602.patch (text/x-patch), 2.37 KiB.

"Easily", hah!

-%expect 168
+%expect 175

Addition to changelog entry for Bug#28317 (this is also included in the OJ discussion in the SQL syntax chapter):

A consequence of this change is that the parser no longer permits
nested { OJ ... } constructs (which are not legal ODBC syntax,
anyway). Queries that use such constructs should be rewritten. For
example, this query is now produces an error:

SELECT * FROM
   {OJ
      {OJ a LEFT OUTER JOIN b ON a.a1=b.a1}
      LEFT OUTER JOIN c ON b.b1 = c.b1};

That can be replaced by any of the following rewrites:

SELECT * FROM
    {OJ a LEFT OUTER JOIN b
          LEFT OUTER JOIN c ON b.b1 = c.b1 ON a.a1=b.a1};

SELECT * FROM
   {OJ a LEFT OUTER JOIN b ON a.a1=b.a1
         LEFT OUTER JOIN c ON b.b1 = c.b1};

SELECT * FROM
     a LEFT OUTER JOIN b ON a.a1=b.a1 LEFT OUTER JOIN c ON b.b1 = c.b1;

The first two are legal according to ODBC, and you nest the joins
inside a single { OJ ...} clause. The third is standard SQL syntax,
without ODBC decoration. It can be used with parentheses to emphasize
the evaluation order:

SELECT * FROM
     ((a LEFT OUTER JOIN b ON a.a1=b.a1)
         LEFT OUTER JOIN c ON b.b1 = c.b1);

Documented, as per Paul's comment. No further work will be done on this.