ODBC SQL syntax issue

Hi there,

I have a problem using MySQL 3.23.51 as database in my Crystal Reports
9.2. I'm using MyODBC 2.50.39 as there is no native support for MySQL in
Crystal Reports. It works perfect except for one thing - the outer
joins. When the whole JOIN statement is not situated right after the
FROM, MyODBC returns error in the SQL syntax (near the opening bracket).
Here is an example:

SELECT `incontacts`.`FirstNameBG`, `incontacts`.`LastNameBG`,
`incompanies`.`CompanyID`, `indepartment`.`NameBG`
FROM `inchier` `inchier`, (`incompanies` `incompanies` LEFT OUTER
JOIN `incontacts` `incontacts` ON
`incontacts`.`CompanyID`=`incompanies`.`CompanyID`), `indepartment`
`indepartment`
WHERE (`inchier`.`CompanyID`=`incompanies`.`CompanyID`) AND
(`incontacts`.`DepartmentID`=`indepartment`.`DepartmentID`)

But when I place "`inchier` `inchier`" after the JOIN statement
everything works just fine:

SELECT `incontacts`.`FirstNameBG`, `incontacts`.`LastNameBG`,
`incompanies`.`CompanyID`, `indepartment`.`NameBG`
FROM (`incompanies` `incompanies` LEFT OUTER JOIN `incontacts`
`incontacts` ON `incontacts`.`CompanyID`=`incompanies`.`CompanyID`),
`inchier` `inchier`, `indepartment` `indepartment`
WHERE (`inchier`.`CompanyID`=`incompanies`.`CompanyID`) AND
(`incontacts`.`DepartmentID`=`indepartment`.`DepartmentID`)

I thing this issue is somehow connected to the brackets, because without
brackets the join works well no matter where it is situated. Here is the
first query without the brackets and working perfectly as well:

SELECT `incontacts`.`FirstNameBG`, `incontacts`.`LastNameBG`,
`incompanies`.`CompanyID`, `indepartment`.`NameBG`
FROM `inchier` `inchier`, `incompanies` `incompanies` LEFT OUTER JOIN
`incontacts` `incontacts` ON
`incontacts`.`CompanyID`=`incompanies`.`CompanyID`, `indepartment`
`indepartment`
WHERE (`inchier`.`CompanyID`=`incompanies`.`CompanyID`) AND
(`incontacts`.`DepartmentID`=`indepartment`.`DepartmentID`)

Since there is no problem with other databases accessed through ODBC
with this syntax I consider this as a bug in the ODBC SQL syntax parsing
(especially the brackets). There is no way or sence to correct the SQL
query in Crystal Reports so I thing the best solution is to fix this
problem in MySQL. Automatic removal of these specific brackets should
solve the problem ad hoc. You can include a new flag in MyODBC for this
bracket removal or something else, but you know better.

I'll be thankful if you inform me on the future development on this
issue.

How-To-Repeat:
Just try to execute SELECT query on multiple tables using LEFT OUTER
JOIN, where the whole JOIN expression is enclosed in brackets ( '(' and
')' ) and is not situated right after FROM (just as shown in the first
example).


Ivan Vazharov
System Integrator
Alen Mak


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread12625@lists.mysql.com
To unsubscribe, e-mail