{ OJ } fails when joining more than 2 tables

I've been playing around with it for a while now, and it appears as though
MySQL doesn't properly support { OJ }. It works when you join just 2
tables, for example:

SELECT * FROM { OJ t1 INNER JOIN t2 ON t1.a = t2.a }

However, if you try to join 3 tables, like so:

SELECT * FROM { OJ (t1 INNER JOIN t2 ON t1.a = t2.a) INNER JOIN t3 ON t1.b
= t3.b}

Then you get a Syntax error. This is using MySQL 4.0.12 and MyODBC 3.51.06

Taking out the { OJ } will make the select statement work just fine, which
tells me it's not something to do with the syntax of the command, but
rather with the way MySQL is parsing the { OJ }. If I read correctly, it
could/should just drop the { OJ }, as it is simply used as a
deliminator. Unfortunately I cannot easily drop it from the query on my
side as I am using generated SQL statements.

FYI, that SQL statement works on MS SQL server, and is supposed to be ANSI
92 SQL (I cant' say for sure because I cannot find the actual ANSI 92 SQL
specs anywhere).


--
Brian Harris
http://www.binaryc.com
1011100101100100101111b


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: { OJ } fails when joining more than 2 tables

Brian,

Brian wrote:
> I've been playing around with it for a while now, and it appears as
> though MySQL doesn't properly support { OJ }. It works when you join
> just 2 tables, for example:
>
> SELECT * FROM { OJ t1 INNER JOIN t2 ON t1.a = t2.a }
>
> However, if you try to join 3 tables, like so:
>
> SELECT * FROM { OJ (t1 INNER JOIN t2 ON t1.a = t2.a) INNER JOIN t3 ON
> t1.b = t3.b}
>
> Then you get a Syntax error. This is using MySQL 4.0.12 and MyODBC 3.51.06

Actually the problem is with enclosing tables and join definition with parenthesis.

This query works
SELECT * FROM { OJ t1 INNER JOIN t2 ON t1.a = t2.a }

This query fails.
SELECT * FROM { OJ t1 INNER JOIN t2 ON t1.a = t2.a }


Because of that you are not able to join more tables that way as MySQL does not
parse combination on { OJ } and ()


> Taking out the { OJ } will make the select statement work just fine,
> which tells me it's not something to do with the syntax of the command,
> but rather with the way MySQL is parsing the { OJ }. If I read
> correctly, it could/should just drop the { OJ }, as it is simply used as
> a deliminator. Unfortunately I cannot easily drop it from the query on
> my side as I am using generated SQL statements.
>
> FYI, that SQL statement works on MS SQL server, and is supposed to be
> ANSI 92 SQL (I cant' say for sure because I cannot find the actual ANSI
> 92 SQL specs anywhere).

No it is not ANSI 92. It is an ODBC shorthand escape sequence.
ODBC documenation specifies it for LEFT|RIGHT| FULL joins only, but as MySQL can
simply ignore it it is reasonable to support it for INNER JOIN too.

However MySQL supports this syntax:

SELECT * FROM { OJ t1 LEFT OUTER JOIN t2 ON t1.a = t2.a } LEFT OUTER JOIN t3 ON
t1.b = t3.b;

but does not support this one:

SELECT * FROM { OJ t1 LEFT OUTER JOIN t2 ON t1.a = t2.a LEFT OUTER JOIN t3 ON
t1.b = t3.b };

Manual says in chapter 6.4.1.1 JOIN Syntax

{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
....
The last LEFT OUTER JOIN syntax shown above exists only for compatibility with ODBC.


As it is more ODBC than SQL issue we have to discuss it first before deciding if
it is bug we are going to fix at server side or is something ODBC driver must take
care of.

Best regards

--
I'm MySQL certified. Are you? -- http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: { OJ } fails when joining more than 2 tables

Correction.

Alexander Keremidarski wrote:

>
> This query works
> SELECT * FROM { OJ t1 INNER JOIN t2 ON t1.a = t2.a }
>


This query fails.
SELECT * FROM { OJ ( t1 INNER JOIN t2 ON t1.a = t2.a ) }

I missed () first time.

Best regards

--
I'm MySQL certified. Are you? -- http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org