MySQL Bugs: #42437: SUM() returns string

Bug #42437	SUM() returns string
Submitted:	28 Jan 2009 19:35	Modified:	4 Feb 2009 19:16
Reporter:	BJ Quinn	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Solaris
Assigned to:		CPU Architecture:	Any

Description:
I'm upgrading from mysql 4.1.16 on Linux to 5.0.45 on OpenSolaris, and I'm using the ODBC Connector v3.51. One of the things I've noticed in the switchover process is that SUM() now no longer returns a numeric value, it returns a string. I'd really rather it return a numeric value of some kind, and the MySQL documentation on SUM() seems to indicate that it ought to.

I've found a few posts (like http://lists.mysql.com/java/8984) that point to the idea that MySQL does in fact return a string, but I can't really find much out there about that. Also, the driver hasn't changed, just the backend server. Any ideas on how to get a numeric value back from SUM()?

Thanks!

How to repeat:
Simply SUM a numeric column and the answer is correct, but it's a number contained in a string data type.

Thank you for the bug report. Indeed the aggregate function SUM() returns a NEWDECIMAL data type:

c:\dbs>5.0\bin\mysql -uroot --port=3500 -T test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.77-nt-log Source distribution

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

mysql> select sum(1+1);
Field   1:  `sum(1+1)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     25
Max_length: 1
Decimals:   0
Flags:      BINARY

+----------+
| sum(1+1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> select cast(sum(1+1) as signed);
Field   1:  `cast(sum(1+1) as signed)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     25
Max_length: 1
Decimals:   0
Flags:      BINARY NUM

+--------------------------+
| cast(sum(1+1) as signed) |
+--------------------------+
|                        2 |
+--------------------------+
1 row in set (0.00 sec)

That was reported with bug: http://bugs.mysql.com/bug.php?id=14140 and then documented behavior.

So do I need to upgrade my mysql ODBC connector, because through the ODBC connector it does *NOT* recognize it as any sort of numeric value at all.  It returns as a string.

I'm reopening this bug.  Just because it's documented doesn't make it a good idea to return a STRING from an AGGREGATE NUMERIC function!

Can anyone give me a good reason WHY it's a good idea to break compatibility with existing SQL code?  Or why it's a good idea for a NUMERIC function with only NUMERIC inputs to return a STRING equivalent data type?

BJ, you are not reading what my colleagues wrote...

NEWDECIMAL is in fact string (and I will not elaborate here why as that discussion ended good 3-4 years ago) but that's not the reason connectors have problems with aggregate functions. True reason is you can't work reliably around fact that type returned is string and flags indicate BINARY:

create table bug_26097 ( x int, y double );
insert into bug_26097 values (1,1),(2,2),(3,3),(4,4),(5,5);
insert into bug_26097 values (1,1.1),(2,2.2),(3,3.3),(4,4.4),(5,5.5);
alter table bug_26097 change y y DECIMAL;

mysql> SELECT SUM(x) AS c, AVG(y) AS d, COUNT(x) AS e FROM bug_26097;
Field   1:  `c`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     33
Max_length: 2
Decimals:   0
Flags:      BINARY

Field   2:  `d`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)  <<
Length:     16
Max_length: 6
Decimals:   4
Flags:      BINARY       <<

Field   3:  `e`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     21
Max_length: 2
Decimals:   0
Flags:      NOT_NULL BINARY NUM

+------+--------+----+
| c    | d      | e  |
+------+--------+----+
|   30 | 3.1000 | 10 |
+------+--------+----+
1 row in set (0.00 sec)

Now, if you take a closer look at:
Type:       NEWDECIMAL
Collation:  binary (63)  <<
Length:     16
Max_length: 6
Decimals:   4
Flags:      BINARY       <<
everything but "Decimals:   4" points out it's a string... Luckily, our connectors guys worked around this server metadata (not Types nor ODBC!) problem and both latest versions of c/ODBC (3.51.27 and 5.1.5) work correctly:

Successfully connected to DSN '5-1-5-localhost-32'.
SQLExecDirect:
	In:	hstmt = 0x00702F90, szSqlStr = "SELECT SUM(x) AS c, AVG(y) AS d, COUNT(x) AS e FROM bu...", cbSqlStr = -3
	Return:	SQL_SUCCESS=0

Get Data All:
"c", "d", "e"
30, 3.1000, 10
1 row fetched from 3 columns.

SQLExecDirect:
	In:	hstmt = 0x00702F90, szSqlStr = "SELECT SUM(x) AS c, AVG(y)+5 AS d, COUNT(x) AS e FROM ...", cbSqlStr = -3
	Return:	SQL_SUCCESS=0

Get Data All:
"c", "d", "e"
30, 8.1000, 10
1 row fetched from 3 columns.

and (with c/ODBC 5.1.5)

SQLDescribeCol:
	In:	StatementHandle = 0x008812F0, ColumnNumber = 2, ColumnName = 0x003612B8, BufferLength = 600, NameLengthPtr = 0x00358878,DataTypePtr = 0x00361A70, ColumnSizePtr = 0x003610C8, DecimalDigits = 0x003610E0, NullablePtr = 0x003610F8
	Return:	SQL_SUCCESS=0
	Out:	*ColumnName = "d", *NameLengthPtr = 1, *DataTypePtr = SQL_DECIMAL=3,*ColumnSizePtr=14,*DecimalDigits=4,*NullablePtr=SQL_NULLABLE=1
^^^^^^^^^^^^^
So you can fetch result as number and do math on it. I remember this was fixed quite some time ago so I wonder which version of c/ODBC are you using?

It's pretty old.  3.51.17 looks like what I'm using most places.  I'll try the newer one you suggested.  It's still too bad how many places I'll have to go upgrade that ODBC connector in order to roll out a server with a new version of MySQL, but it's much better than having to rewrite and retest a bunch of code!!!

Thanks for your help.

If you don't mind, maybe you could point me to one of those NEWDECIMAL discussions?  I'm really curious as to what motivated what seems like such a bizarre decision, at least on the surface (workaround or not, it still broke a bunch of existing code).

Tonci, you confused this with a different problem. When a number is casted to a string it gets "binary" charset, yes, it's a bug, but a different one.

Here no number is cast to a string, the result type is "NEWDECIMAL" and it's returned as "NEWDECIMAL", not as a string. There's nothing to work around, NEWDECIMAL is a number, VARCHAR is a string.

The original bugreport is about the fact that for NEWDECIMAL we use the textual representation of a number, not binary. But there is no universally accepted standard for arbitrary precision fixed point numbers, and also, DECIMAL type was always sent as a string. It's up to the connector to convert the string representation of a NEWDECIMAL to the native type for arbitrary precision fixed point numbers, SQL_DECIMAL, Math::BigFloat, or something else.

Thanks Serg, you're right as always. And I'm partially brain-dead recently, but that's my problem. And, as shown, connectors do the trick properly now.