MySQL Bugs: #33808: MyODBC 51/Access ODBC CONVERT() causes error

Bug #33808	MyODBC 51/Access ODBC CONVERT() causes error
Submitted:	10 Jan 2008 21:52	Modified:	14 Mar 2008 19:43
Reporter:	Eric MaLossi
Status:	Closed
Category:	Connector/ODBC	Severity:	S2 (Serious)
Version:	5.1.1	OS:	Microsoft Windows (xp2 sp2)
Assigned to:	Jim Winstead	Target Version:	5.1
Triage:	D3 (Medium)

Description:
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:
SETUP:
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

dump file for bug database

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

access database file

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

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().

Don't report that CAST() and CONVERT() are supported

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

approved

The fix is committed, and will be included in the 5.1.3 release.

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.