MySQL Bugs: #11795: DECIMAL fields returned incorrectly over ODBC

Bug #11795	DECIMAL fields returned incorrectly over ODBC
Submitted:	7 Jul 2005 13:34	Modified:	24 Oct 2005 17:46
Reporter:	Lee Willis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	MySQL 5.0.6, MyODBC3.51.11	OS:	Windows (Windows/Linux)
Assigned to:		CPU Architecture:	Any

Description:
Fields defined as DECIMAL on a 5.0.6 server are returned incorrectly over ODBC causing the requesting application to interpret them incorrectly. No workaround available as far as I can tell.

It seems that the ODBC layer is returning the decimal field as a non-numeric type (Char or similar?)

The same process when run with the same client (MyODBC 3.51.11), but against a different server (4.0.23) gives the correct results so I'm assuming that this is either a bug in the 5.0.6 server, or some incompatability between server 5.0.6 and MyODBC 3.51.11

How to repeat:
> create table test (value decimal(8,2));
> insert into test values ("100.50");
> insert into test values ("100.50");

Start a blank copy of excel and do Data->Import External Data->New Database Query
Select your ODBC data source that points to the table created above
When Microsoft Query starts set up the SQL as "SELECT * FROM test"
Return the data from Microsoft Query and create a pivot table from the data source
Drag the "value" field into the data section of the pivot table
Change the data field to be a "Sum", rather than "Count"

Actual results:
  [Excel defaults to "Count of value" when creating the pivot]
  [Excel presents a result of 0 when changing to "Sum"]

Expected results:
 [Excel defaults to "Sum of value" when creating the pivot (This is default behaviour for numeric fields)]
 [Excel presents the correct sum (201)]

Thanks for your bug report.  I verified this bug using the latest version of myodbc.

Attatched screenshot

bug scrshot

Attachment: Image1.png (image/x-png, text), 27.06 KiB.

This looks like it may be the same problem as bug 11035, but I can't tell from that when the patch is likely to become available - it seems that there hasn't been a MyODBC release since February.

This can also be worked around now by using the following patch. This may not be the right fix (Might clients wish to treat NEWDECIMALs different from DECIMALs?) - however we already do this for VARCHAR vs. VAR_STRING so maybe it is ok.

If you're having this problem this patch seems to resolve it at the server level [Previously linked bug resolves it at the ODBC driver level]

Patch to map MYSQL_TYPE_NEWDECIMAL to MYSQL_TYPE_DECIMAL

Attachment: decimal.patch (text/x-patch), 404 bytes.

Patch attached.

I have the same problem with SAS/ACCESS to MySQL and the MySQL server version 5 together with the client version 5:

DECIMAL() fields appear as character fields in SAS.
So this is also a problem in the client.

See also bug #9739

Following given test using excel with c/odbc v3.51.12 and it works fine. This is not surprising as some fixs dealing with numeric types are included in this release.