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

[1 Mar 2006 6:28] Todd Lind
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.
[1 Mar 2006 10:11] Valeriy Kravchuk
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.
[1 Mar 2006 14:13] Todd Lind
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.
[1 Mar 2006 17:46] Valeriy Kravchuk
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.
[23 May 2006 20:54] Frank Salamon
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.
[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.