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:Microsoft Windows (Windows XP)
Assigned to:

[27 Mar 2006 23:30] Maurice Maneschi
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.
[28 Mar 2006 12:01] Valeriy Kravchuk
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.
[29 Mar 2006 0:05] Maurice Maneschi
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).
[29 Mar 2006 0:39] Maurice Maneschi
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?
[29 Mar 2006 0:40] Maurice Maneschi
That URL again:
http://support.businessobjects.com/
communityCS/TechnicalPapers/cr9_left_outer_join.pdf
[29 Mar 2006 16:46] Valeriy Kravchuk
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?
[29 Mar 2006 21:43] Maurice Maneschi
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.
[30 Mar 2006 14:48] Valeriy Kravchuk
Please, reopen this report when you'll have any new results on this problem.
[30 Apr 2006 23:00] Bugs System
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".
[30 Apr 2006 23:34] Maurice Maneschi
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.
[12 May 2006 8:47] Valeriy Kravchuk
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?
[14 May 2006 9:55] Maurice Maneschi
I have now raised the problem with Business Objects (Crystal Reports). If I get no traction there, I will close this item.
[14 May 2006 14:14] Valeriy Kravchuk
Please, reopen this bug report when you'll get any results from them.
[23 May 2006 20:32] Frank Salamon
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.
[23 May 2006 22:44] Maurice Maneschi
Frank's answer works perfectly. Thank you!
[4 Jun 2007 18:08] Diego Pomatta
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
[19 Dec 2007 23:11] Davi Arnaut
This (and potentially other) issues with ODBC's Outer Join Escape Sequence have been fixed in Bug#28317.