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

[19 Jun 2009 4:46] Bogdan Degtyariov
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.
[25 Sep 2010 20:30] Davi Arnaut
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.
[17 May 2011 11:49] Nacho Estrada
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.
[17 May 2011 22:46] Jared S
I would backup your queries and Search & replace..
OJ --> ""
{ --> ""
} --> ""
[4 Oct 2011 15:05] Hartmut Holzgraefe
Could this change in behavior at least be documented on the 5.1+ manuals?
[5 Oct 2011 21:54] Hartmut Holzgraefe
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 ...
[5 Oct 2011 21:56] Hartmut Holzgraefe
suggested grammer patch to restore the old nestable behavior

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

[5 Oct 2011 22:02] Davi Arnaut
"Easily", hah!

-%expect 168
+%expect 175
[6 Oct 2011 15:35] Paul DuBois
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);
[16 Oct 2012 5:16] Erlend Dahl
Documented, as per Paul's comment. No further work will be done on this.