Bug #33808 MyODBC 51/Access ODBC CONVERT() causes error
Submitted: 10 Jan 2008 20:52 Modified: 14 Mar 2008 18:43
Reporter: Erica Moss Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1.1 OS:Windows (xp2 sp2)
Assigned to: Jim Winstead CPU Architecture:Any

[10 Jan 2008 20:52] Erica Moss
When attempting to run the following query...

SELECT [Order Details].OrderID AS Expr1,
Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount]))) AS Subtotal
FROM [Order Details]
GROUP BY [Order Details].OrderID;

This error is received:
ODBC--call failed.
[MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]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 'SQL_FLOAT)}) FROM `order details` GROUP BY `OrderID`' at line 1 (#1064)

The SQL which is passed by the driver to the server (5.0.45) is...

SELECT `OrderID` ,SUM({fn convert(((`UnitPrice` * `Quantity` ) * (1 - `Discount` ) ) ,SQL_FLOAT)}) FROM `order details` GROUP BY `OrderID`

In contrast the SQL sent for the same query using MyODBC 3.51 is...

SELECT `OrderID` ,`UnitPrice` ,`Quantity` ,`Discount` FROM `order details`

How to repeat:
mysql> create database northwind;
c:\>mysql -uroot -ppassword northwind < northwind.dump

1. Create a DSN named "Access ODBC Test"
2. open the attached database bug.mdb
3. make sure that the table in Access that is linked to the mysql table "order details" can open
4. try to run the query "order subtotals"

error is received
[10 Jan 2008 20:54] Erica Moss
dump file for bug database

Attachment: northwind.dump (application/octet-stream, text), 330.18 KiB.

[11 Jan 2008 19:31] Erica Moss
access database file

Attachment: bug.mdb (application/msaccess, text), 420.00 KiB.

[31 Jan 2008 23:26] Jim Winstead
this is probably because we're reporting we support SQL_FN_CVT_CONVERT in SQL_CONVERT_FUNCTIONS, but MySQL's CONVERT() is not like that mandated by the ODBC spec. SQL_FN_CVT_CAST needs to be confirmed. it may also not be okay because of the limited types that MySQL allows in CAST().
[28 Feb 2008 21:01] Jim Winstead
Don't report that CAST() and CONVERT() are supported

Attachment: bug33808.diff (text/plain), 946 bytes.

[12 Mar 2008 15:32] Lawrenty Novitsky
[12 Mar 2008 15:53] Jim Winstead
The fix is committed, and will be included in the 5.1.3 release.
[14 Mar 2008 18:39] MC Brown
A note has been added to the 5.1.3 changelog: 

Connector/ODBC erroneously reported that it supported the CAST() and CONVERT() ODBC functions for parsing values in SQL statements, which could lead to bad SQL generation during a query.