MySQL Bugs: #13730: Retrieving SUM() of INTEGER columns using ODBC fails after server upgrade

Bug #13730	Retrieving SUM() of INTEGER columns using ODBC fails after server upgrade
Submitted:	4 Oct 2005 4:13	Modified:	2 Oct 2007 12:18
Reporter:	Richard de Courtney	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:		OS:	Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
after upgrading from 4.1.14 to 5.0.13, any SUM() of columns that are of the type INTEGER fails, i.e. a SELECT SUM(column1) FROM tablename (if the table is populated) will return with no results. A dump of the table, and re-import does not fix the error, and the only way that SUM() appears to work is by converting the column to FLOAT or to a VARCHAR, converting the column to TINYINT, SMALLINT, DECIMAL etc does not resolve the problem.

The problem can only be reproduced here when conducting an ODBC call via ASP, conducting a query via Navicat works fine.

How to repeat:
as above

Suggested fix:
Ensure columntypes are returned as INTEGERS when set as INTEGERS (I have a feeling that the ODBC call is not returning the column type correctly and an update to the ODBC drivers are required to handle the column changes implemented in 5.0.x

Could you please provide any ASP test case of the bug?

I think this is related to another Bug #13691 that I've also logged. Although 2 different errors, I believe there is a problem with the ODBC driver and the return of INTEGER VALUES from MySQL 5.0.13 (I believe that the value type INTEGER was changed in this release of MySQL)

Changed category and synopsis to more appropriate ones. Please, inform about the version of ODBC driver used.

Richard - you're right.

SUM() of INTEGER's is returned as DOUBLE in 4.1 and as DECIMAL in 5.0
The new DECIMAL type of 5.0 is not recognized by 4.1 client library.

There're many related bugreports.
See e.g. BUG#11795 and BUG#11035

I've found a kind of workaround to force SUM returning a non empty resultset from ODBC connections and MySQL version 5.0.xx. But you need access to the source SQL containing the SUM function. Here's a code example returning a string value from the sum field:
SELECT REPLACE(FORMAT(SUM(anIntField),0), ',', '') FROM aTableName

In the above example, FORMAT will only convert the sum value to a string. The 0 in parameterlist is number of decimals. The bad thing is that FORMAT inserts commas on every 3 digits i.e. 1,647,104. I'm not sure if these comma separators are based on a locale setting. The wrapper REPLACE function deletes the separators.

Because the result from the sum now is a string value, you may need to modify the application code to accept this datatype.

A relatively painless workaroud is to use FORMAT function,
e.g. SELECT FORMAT(SUM(myColumn),0) FROM myTable
This will add thousands separators and return result as a string.

Jacek

Just downloaded 5.0.45 and this (pretty blinding) bug is STILL THERE! What's going on?

Hi Alex,

please, can you tell us, which ODBC version you used?

Regards,

Susanne

Everybody, please pay attention to what Serg said on changes in type returned ([7 Oct 2005 12:01] Sergei Golubchik). We are not talking about ODBC problem here nor we can discuss validity of changes made to client library. You can always simply work around in SQL:

Server version: 5.0.50-pb1046-log MySQL Pushbuild Edition, build 1046

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use solusd;
Database changed
mysql> SELECT SUM(Rbr) As RBRs FROM dnevnik WHERE Godina = 2005;
Field   1:  `RBRs`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL <<<<
Collation:  binary (63)
Length:     34
Max_length: 10
Decimals:   0
Flags:      BINARY

+------------+
| RBRs       |
+------------+
| 5372565183 |
+------------+
1 row in set (0.58 sec)

mysql> SELECT CAST(SUM(Rbr) AS SIGNED) AS RBRs FROM dnevnik WHERE Godina = 2005;

Field   1:  `RBRs`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG <<<<
Collation:  binary (63)
Length:     34
Max_length: 10
Decimals:   0
Flags:      BINARY NUM

+------------+
| RBRs       |
+------------+
| 5372565183 |
+------------+
1 row in set (0.09 sec)

mysql>

Same thing via generic MS ODBC client (odbcte32.exe):

	Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'myodbccln'.
SQLExecDirect:	In:	hstmt = 0x00852098, szSqlStr = "SELECT CAST(SUM(Rbr) AS SIGNED) AS RBRs FROM solusd.dn...", cbSqlStr = -3
	Return:	SQL_SUCCESS=0

Get Data All:
"RBRs"
5372565183
1 row fetched from 1 column.