MySQL Bugs: #18563: LEFT OUTER JOIN syntax generated by Crystal Reports 10 cannot be parsed

Bug #18563	LEFT OUTER JOIN syntax generated by Crystal Reports 10 cannot be parsed
Submitted:	27 Mar 2006 23:30	Modified:	23 May 2006 22:44
Reporter:	Maurice Maneschi	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.18-nt	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
Crystal reports 10 generated the following SQL when asked to show all defects that did not have a matching SG defect but should have (i.e. were assigned to 'QTHelp'):

 SELECT `sg_defect_numbers`.`caseNumber`, `defects`.`defect-number`, `defects`.`summary`, `names`.`name`

 FROM   {oj ((`testtrack`.`defects` `defects` LEFT OUTER JOIN `testtrack`.`sg_defect_numbers` `sg_defect_numbers` ON `defects`.`defect-number`=`sg_defect_numbers`.`defect-number`) INNER JOIN `testtrack`.`assignees` `assignees` ON (`defects`.`defect-number`=`assignees`.`defect-number`) AND (`defects`.`assignee-event-id`=`assignees`.`event-id`)) INNER JOIN `testtrack`.`names` `names` ON `assignees`.`assignee`=`names`.`name-id`}

 WHERE  `sg_defect_numbers`.`caseNumber` IS  NULL  AND `names`.`name`='QTHelp'

When this SQL is pasted into the MySQL command line, the parser fails at the closing "}":

mysql>  SELECT `sg_defect_numbers`.`caseNumber`, `defects`.`defect-number`, `def
ects`.`summary`, `names`.`name`
    ->
    ->  FROM   {oj ((`testtrack`.`defects` `defects` LEFT OUTER JOIN `testtrack`
.`sg_defect_numbers` `sg_defect_numbers` ON `defects`.`defect-number`=`sg_defect
_numbers`.`defect-number`) INNER JOIN `testtrack`.`assignees` `assignees` ON (`d
efects`.`defect-number`=`assignees`.`defect-number`) AND (`defects`.`assignee-ev
ent-id`=`assignees`.`event-id`)) INNER JOIN `testtrack`.`names` `names` ON `assi
gnees`.`assignee`=`names`.`name-id`}
    ->
    ->  WHERE  `sg_defect_numbers`.`caseNumber` IS  NULL  AND `names`.`name`='QT
Help'
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '}
 WHERE  `sg_defect_numbers`.`caseNumber` IS  NULL  AND `names`.`name`='QTHelp''
at line 2
mysql>

How to repeat:
In Crystal Reports, using the ODBC connector 3.51, include a table and left outer join it to another. The link in two other tables. View the SQL. Copy it and paste it into the MySQL command line.

Suggested fix:
I can fix this by restructing the SQL not to use the {OJ ... LEFT JOIN ... ON ...} form, and loading that SQL into the Crystal Reports command area. However, this is inhibiting my ability to introduce MySQL into the organisation.

Thank you for a problem report. Sorry, but the syntax generated does not really look like a proper one according to the manul, http://dev.mysql.com/doc/refman/5.0/en/join.html:

{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }

Looks like you have LEFT OUTER JOIN in one of the table_references... Please, check.

Hi Valeriy,

Thanks for the feedback. I agree the SQL is not in the form suggested in the manual. However, this SQL was generated by Crystal Reports v10, not me.

The thrust of this issue is that we may need to be able to parse the SQL generated by Crystal Reports 10 (or discover some configuration option to make CR 10 generate more standard SQL).

I may have found the solution. This document http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_left_outer_join.pdf shows the join syntax variations supported by Crystal Reports. However, I can not find any of the variation names used in the PDF when searching the MySQL reference manual. Has anyone solved this before?

That URL again:
http://support.businessobjects.com/
communityCS/TechnicalPapers/cr9_left_outer_join.pdf

I'll read the documents you presented and may come up with some ideas, but, anyway, if some tool generates wrong code for MySQL, it is not a MySQL bug. Do you agree with me?

I probably would agree that it is not a bug with the SQL implementation of MySQL. (To be certain, I would need to go and read the latest SQL standard.) Certainly MySQL is behaving exactly as documented.

If I get a chance today, I am going to try applying the "OpenIngres" option to the ODBC driver (as per the PDF). I'll let you know how it goes.

Please, reopen this report when you'll have any new results on this problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have experimented with the suggested fix from Crystal Reports without success. My problems are:
1) I cannot determine which form of Outer Join syntax MySQL uses from the list: OpenIngres, PlusEqual,OJSyntax,SQL2OuterJoin,StarEqual
2) The suggested fix is for version 9 of Crystal Reports.

The workaround (insert an SQL command into Crystal Reports) is sufficient for the present. If I ever make more progress on this this issue, I will update this item.

Do you think that there is a need to file a problem report about Crystal Reports (if it is supposed to support MySQL at all)? Do you have any progress on this report?

I have now raised the problem with Business Objects (Crystal Reports). If I get no traction there, I will close this item.

Please, reopen this bug report when you'll get any results from them.

Business Objects has solution in their knowledge base (c2016114) where a registry key is added to force Crystal not to use the { oj } syntax. 

However, it appears that this solution works fine with version 3.51.11-2 of the connector, but not with version 3.51.12. This is also noted in MySQL Bug #17818 which is currently listed as not a bug.

I reverted to 3.51.11-2, as too many of our reports require the mixed joins. Running Crystal 10 and XI versus MySQL 4.1.x.

Frank's answer works perfectly. Thank you!

We had the same problem with Crystal Reports XI and MySQl 5.0.27.

I'd like to add that the registry fix in Business Objects knowledge base article that Frank mentioned (c2016114)(http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docT...) worked for us using ODBC driver MySQL 3.51.12, 3.51.14 and 3.51.15.

Regards.
/Diego

This (and potentially other) issues with ODBC's Outer Join Escape Sequence have been fixed in Bug#28317.