MySQL Bugs: #17818: Since upgrading .11 to .12 - Crystal Reports XI have stopped working

Bug #17818	Since upgrading .11 to .12 - Crystal Reports XI have stopped working
Submitted:	1 Mar 2006 6:28	Modified:	1 Mar 2006 17:46
Reporter:	Todd Lind	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.12	OS:	Windows (XP or 2000)
Assigned to:		CPU Architecture:	Any

Description:
Since Upgrading from ODBC 3.51.11 to 3.51.12 my Crystal reports have quite working.

I'm running agains 5.0.16 standard edition.

I have had the registry key for NoOuterJoinEsc MYODBC3, which as resolved the problem in the past.

What is different this time is I'm getting a "Failed to retrieve data from the database"
Details:[23000]:[MySQL][ODBC 3.51 driver][mysqld-5.0.16-standard] You have an error in your sql syntax.

The reports contain 13 tables, which have a mix of regular and left joins.  I can bypass the problem if I change all joins to Left instead of both Inner and Left.  This problem has just begun when I upgraded.  I can not currently find a .11 to downgrade to.

It seems that the ODBC driver can not accomidate mixed joins.... this means a major re-write on many users reports (well atleast mine).

Thanks,

~todd

How to repeat:
install 3.51.12 ODBC on 2000 or XP.

Install Crystal XI

Create a report with mixed Join types.

Suggested fix:
Compare to .11 to see why the syntax requirements have changed, since it was not an issue before.... or I suppose it could be in the server... I had upgraded to .12 on another development PC, because of odd problems when inserting via odbc after upgrading the server to 5.0.16.  

The ODBC upgrade fixed the inserts... but have broken the same reports I have been using.

Thank you for a problem report. Please, specify the exact version of MySQL server you used before upgrading to 5.0.16. I think, the problem is server-related.

We had upgraded from 4.1.x to 5.0.16 on the production box.  It's on a Linux Box running Redhat 3.0ES.

Then it is not a bug. Join behaviour changed in 5.0.12 to conform to SQL 2003 Standard. Please, read the manual (version 5.0!) on JOIN syntax.

We experienced the same problem going from version 3.51.11-2 to 3.51.12 without the server change. 

MySQL 4.1.3-beta, "NoOuterJoinEscSeq" key works on version 3.51.11-2, but not for version 3.51.12. 

This would appear to indicate that it is not related to the change in Join behavior.

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