MySQL Bugs: #28317: Left Outer Join with {oj outer-join}

Bug #28317	Left Outer Join with {oj outer-join}
Submitted:	9 May 2007 1:33	Modified:	19 Jun 2009 6:51
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S1 (Critical)
Version:	5.1.22	OS:	Windows (Any)
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	crystal reports, curly brackets, join, object, oj, qc, show sql

Description:
Hi, I dont know what "oj{}" syntax is, maybe it is a Microsoft SQL Server thing, but Crystal Reports 11 Release 2 is insistant that it be present when Left Outer Join exists in SQL of the report.

How to repeat:
1. I guest install my db attached
2. Run query attached
3. just for fun, you can remove the whole oj{..}, but leave the statements inside.

RESULT works when oj{} is ommitted, but fails when oj{} is included

Hi Jarred.
Weird but I don't think the problem is in MySQL... Will check when we renew our BO licenses but for now looks like something that *should* be parsed out in any case.

You shouldn't need a copy of BO to repo problem, you could always get 1 gig demo of internet if you need.  I have logged this as an issue with support@businessobject.com, so you may want to deactivate this bug since, you agree that it is not a MySQL problem.

Jared, I haven't dismiss this yet. Looking for more info on ODBC escape-clause syntax. However I still think this is not caused by MySQL.

AFAIS this is not a server/parser bug.

Jared, we *seem* to have shared bug here:
 - MyODBC is not processing "Outer Join Escape Sequence" as per specs in http://msdn2.microsoft.com/en-us/library/ms710299.aspx
 and
 - BO CR is not testing driver capabilities as per same specs: "To determine which parts of this statement are supported, an application calls SQLGetInfo with the SQL_OJ_CAPABILITIES information type."

In any case, this sequence if problematic even in MS drivers, http://support.microsoft.com/kb/104958 and they wrote the standard!

Now, I need you to check ODBC traces for SQL_OJ_CAPABILITIES and the driver's reply. I would also like to know which versions of c/ODBC are affected? Can you attach relevant part of general query log so we can examine what gets to server?

Upon receiving requested info I'm prepared to set this to "Verified" to notify the rest of the team of this problem.

1.  Can't get ODBC to trace driver calls, the log SQL query checkbox in ODBC debug tab works though.

2. All versions of ODBC effected.

Are you able to pursue this bug when you get crystal, I just don't know how to log driver calls.

ODBC tracing in DM

Attachment: 28317-odbc-tracing.jpg (image/jpeg, text), 39.40 KiB.

Jared, for your first question, please see attached image.

> Are you able to pursue this bug when you get crystal.
Yes but I don't know when will that be (it's out of my hands). So, ODBC trace from DM and start MySQL server with log option please. Waiting on logs.

I must say I'm more than interested in this report. Thanks!

crw32           be8-c94	ENTER SQLGetInfoW 
		HDBC                7FE584E0
		UWORD                      115 <SQL_OJ_CAPABILITIES>
		PTR                 7FDDAD80
		SWORD                        4 
		SWORD *             0x0012DB18

crw32           be8-c94	EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
		HDBC                7FE584E0
		UWORD                      115 <SQL_OJ_CAPABILITIES>
		PTR                 7FDDAD80
		SWORD                        4 
		SWORD *             0x0012DB18 (4)

Jared, even without server log I think it's safe to declare this report "Verified". Thanks for your help.

BTW, 
 - changing synopsis
 - changing OS details as there is no special execution path for Vista
 - changing version(s) as it's not a server bug

I dont understand why this is an ODBC issue.  My understanding of this issue is...

1. CR queries OJ capabilities through MyODBC
2. MyODBC reports OJ capabilities as true
3. CR throws on OJ{..} for Left Outer Joins
4. Query fails to get parsed

And even when I take SQL out of report and run it manually in QB, it stills fails with OJ{..}.  Do you mean that the synopsis is "MyODBC incorrectly report OJ caps"?

Jared, check again  - MyODBC is not processing "Outer Join Escape Sequence" as per specs in http://msdn2.microsoft.com/en-us/library/ms710299.aspx page. It does not relate to MySQL server at all... It is pure MS "invention" from God knows what reasons (http://support.microsoft.com/kb/136699).

That's why I asked server query log from you. I expect to find same "faulty" SQL syntax there since ODBC driver is the one that should have altered OJ syntax from "ODBC" way to "server" way.

Of course QB failed too, it's ODBC specific syntax...

Ah yes, never mind ODBC driver is reporting OJ capabilities wrongly, as that is the only way to deal with OJ inside specs. It should process them so the primary bug is in processing.

I still think it's the server/parser issue.
Our parser used to "support" this syntax, by simply ignoring it
(at least this seemed to produce adequate results in many cases).

Anyway the query should certainly work, and produce the same result as if {oj } would be removed.

Sergei, I don't know what are your plans for server parsing and you are welcome to treat this as parser bug but I need to see this syntax processed in driver as per ODBC specs mentioned above... Now, if server team plans to include this in parser, we should add note in manual that our ODBC drivers will relay on server to parse this out instead of following ODBC specs, right?

Any progress?  Our site has just encountered this.  It's encouraging that you know about it and it's "assigned", but I seriously disagree that this is given a  "Server:Parser" category.  The {oj ... } needs to be recognized by the ODBC driver, not the server.  This allows MyODBC to provide proper support for any version of MySQL that supports outer joins, not just the latest-greatest server with a "fixed" parser.

(If you *also* want to add {oj } support to the server parser, be my guest.  MS did that, probably so that they could provide escape sequence support using any connection method, not just ODBC.  But it should also be implemented by the ODBC driver for servers that don't receive this new parser feature.)

!!!!! BLOCKING ISSUE !!!!!
!!!!! BLOCKING ISSUE !!!!!

Jared, notified team leaders, will see what they have to say.

Jared, can you try without brackets [FROM   {oj ((((( <--] as Salle suggested in http://www.issociate.de/board/post/21347/%7B_OJ_%7D_fails_when_joining_more_than_2_tables....?

Davi, since MySQL did and still does support {oj } syntax (see join_outer.test) please investigate what is causing a failure to parser this query.
It is possible that the query itself is malformed.
Please also investigate whether this is a regression or not - LEFT JOIN was significantly rewritten in 5.0, so it might pass in 4.1.

Tonci, since it is a generated query, I am not sure it could be changed.

1. A single LOJ mixed with other joins will fail
2. CRXIR2 Doesn't allow SQL overrides
3. I can't invert table joins(as a workaround), cause I need to join on 2 possible NULL IDs

Dam, just missed ODBC x.20 release.

I have client who needs Crystal Report with a Left Outer Join in it, please raise this bug as high priority.

Snap for Business Object developers

Attachment: oj_bug.jpg (image/pjpeg, text), 115.38 KiB.

Business Object's official workaround:
======================================

http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do;jsessionid=91D564F...

Jared, thanks for your input and workaround provided! Many may profit from it.

The workaround I mentioned actually makes things worse if you use hyphened-named tables like me.  As for Crystal Reports 2008 and .NET Basic 2008 they both use 12.0.2000.549 Assemblies which have further issues on applying Record Selection, which is the main feature of Crystal Report, this has been ackowledge by BO as no supported 'VS intergration'.  I am seeking a software solution since double Left Outer Join from single table is not support by Crystal and MySQL still fails to parse OJ {}.

I wonder if it's a bug in MySQL. The reported query is:

 SELECT *
 FROM   {oj (((((t1
   INNER JOIN t2 ON t1.idlead=t2.id)
   INNER JOIN t3 ON t1.idillness=t3.id)
   INNER JOIN t4 ON t1.idpostcode=t4.id)
   INNER JOIN t5 ON t1.iduser=t5.id)
   LEFT OUTER JOIN t6 ON t1.id=t6.idclient)
   INNER JOIN t5 t5_1 ON t6.iduser=t5_1.id}

while the syntax for ODBC escape sequence is:

 ODBC-outer-join-escape ::=
     ODBC-esc-initiator oj outer-join ODBC-esc-terminator

  outer-join ::= table-name [correlation-name] {LEFT | RIGHT | FULL}
       OUTER JOIN {table-name [correlation-name] | outer-join} ON 
       search-condition

  correlation-name ::= user-defined-name

  ODBC-esc-initiator ::= {

  ODBC-esc-terminator ::= }

The query, obviously isn't "correct" for the syntax as above.

As mentioned in the changeset, the escape sequence is for left, right, and full outer join syntax, yet the parser only accepts for left outer join. Other databases also support it for inner join, but that's a extension and can be specified/controlled in Crystal Reports.

We have three options here: 1) Fix the ODBC connector to not advertise OJSC capabilities 2) Fix the ODBC connector to handle OJSC, 3) Fix the parser grammar rules for OJSC, 4) Ignore OJSC

I've decided to proceed with the approach 4). What do you suggest?

I agree with "ignore", I thought it was clear from my earlier comments.
(incidentally, I think it's the parser that should ignore them, not the lexer).

But my previous comment was to point out that query generated by Crystal Reports are breaking the standard syntax. Still, looking at SQLGetInfo() function, I see that INNER joins seems to be allowed - there's SQL_OJ_INNER flag that advertises support of inner joins. Which probably means that Crystal is ok, and it's MSDN that is wrong, or rather inconsistent.

> The query, obviously isn't "correct".. 

I can supply you with another "Show SQL" from another .rpt file

> but that's a extension and can be specified/controlled in Crystal
Reports.

No, I am experiencing RecordSelction issues when I am turning of OJSC view CR

1) Fix the ODBC connector to not advertise OJSC capabilities
2) Fix the ODBC connector to handle OJSC
3) Fix the parser grammar rules for OJSC
4) Ignore OJSC

1] No - "NoOuterJoinEscSeq"="MYODBC3, libmyodbc3" in registry causes issues for me in .NET CR objects when setting datefield from hyphened named table in RecordSelection
2] No - As a general programming rule, increasing logic for legacy standard is bad news
3] No - As a general programming rule, increasing logic for legacy standard is bad news
4] Yes - If this breaks different type of OJ{} statements on MySQL 3.x - too bad!

Good morning from Australia 6AM

Posting some more details from my analysis:

This bug is likely a duplicate of Bug#839, which has more details regarding a work around for the INNER JOIN issue. Bug#17818, Bug#18563, Bug#10153 are also duplicates of this bug, the last one contains yet another possible work around.

I want to stress the point that this is clearly a Crystal Reports bug, the application is trying to use a INNER JOIN in a outer join escape sequence, but that's not allowed per the standard OJES syntax.

We are working to make the OJES fully supported in the parser for all possible joins. If in the mean time the work around does not work for you, please post here why it does not work or open a new bug report is it hits another problem.

Incedently upgrading to CR2008 removes all the OJ {rubish}.

I will cool off my comments for time being as no action is needed by MySQL.

FYI, I can perform Left Outer Joins on CR2008 files but can't raise them on VB.NET 2005 crystal components as some other RecordSelection mutilation is occurring.  I know BO doesn’t support CR2008 via VS2008 users...further analysis required.

If CR2008 *EVER* puts on the OJ {stuff} again, I will re-open this bug.

Changed version: ODBC > MySQL Server RC 5.1.22

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/40238

ChangeSet@1.2643, 2007-12-19 20:59:57-02:00, davi@mysql.com +3 -0
  Bug#28317 Left Outer Join with {oj outer-join}
  
  Parser rejects ODBC's escape sequences for outer joins other
  than left outer join, yet the escape sequence BNF specifies
  that this syntax can be used for left, right, and full outer
  join syntax.
  
  The problem is that although the MySQL Connector/ODBC advertises
  "Outer Join Escape Sequence" capabilities, the parsing is done
  in the server and historically it only supported this syntax
  for left outer joins and applications such as Crystal Reports
  11 tries to use this syntax for inner joins.
  
  The chosen solution is to reorganize a couple of parser rules
  to ignore any kind of SQL escape sequence. Ignoring the escape
  sequences is harmless because the various SQL join clauses
  are supported by the server.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Noted in 5.1.24, 6.0.5 changelogs.

Previously, the parser accepted the ODBC { OJ ... LEFT OUTER JOIN 
...} syntax for writing left outer joins. The parser now allows { OJ
... } to be used to write other types of joins, such as INNER JOIN or
RIGHT OUTER JOIN. This helps with compatibility with some third-party
applications, but is not official ODBC syntax.

"The chosen solution is to reorganize a couple of parser rules to ignore any kind of SQL escape sequence."

Also documented for 6.1.23-ndb-6.2.14.

Also documented for 5.1.23-ndb-6.3.11.

Thanks for finally getting this fixed :)

This is still a bug in 5.1.34.

There is a problem in MySQL 5.1 (5.1.34 tested) with nested {OJ{OJ }}:

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.

Created a new bug #45602 for the nested {OJ {OJ

closing the bug

Addition to changelog entry:

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);