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:Microsoft Windows (Windows 2003)
Assigned to: CPU Architecture:Any

[4 Oct 2005 4:13] Richard de Courtney
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
[4 Oct 2005 7:31] Vasily Kishkin
Could you please provide any ASP test case of the bug?
[4 Oct 2005 19:46] Richard de Courtney
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)
[5 Oct 2005 8:50] Valeriy Kravchuk
Changed category and synopsis to more appropriate ones. Please, inform about the version of ODBC driver used.
[7 Oct 2005 10:01] Sergei Golubchik
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
[31 May 2006 8:44] Atle Markeng
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.
[21 Jul 2006 21:38] Jacek Becla
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
[27 Sep 2007 20:01] Alex Roberts
Just downloaded 5.0.45 and this (pretty blinding) bug is STILL THERE! What's going on?
[2 Oct 2007 12:01] Susanne Ebrecht
Hi Alex,

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

Regards,

Susanne
[2 Oct 2007 12:18] Tonci Grgin
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.