MySQL Bugs: #15294: Change BIGINT to int option not honored with SUM aggregates using 5.0.15

Bug #15294	Change BIGINT to int option not honored with SUM aggregates using 5.0.15
Submitted:	28 Nov 2005 19:04	Modified:	2 Dec 2005 16:06
Reporter:	Tom Price	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.10 and 5.0.15	OS:	Windows (Win2K SP4)
Assigned to:	Vasily Kishkin	CPU Architecture:	Any

Description:
I'm using Visual Foxpro 7 against a MySQL back end with MySQL Connector/ODBC.
I'm using MyISAM tables.

I've connected using OPTION=16385.

I'm executing the following query:

select sum(x) as n from estlvl

With MySQL 4.x, this query seems to return a numeric data type regardless of whether the "Change BIGINT to int" option is enabled. MySQL 5.0.15 returns a char.

How to repeat:
How to repeat:

1. Create a table containing at least one int field.
   CREATE TABLE estlvl (x int(11) NOT NULL default '0') TYPE=MyISAM;
2. Add a record to the table.
3. Connect to the database from Visual Foxpro. OPTION=16384 in the connect string should enable bigint to int conversion.
    nConn=SQLConnect() or nConn=SQLSTRINGCONNECT(....) 
4. Query the table using Visual Foxpro.
    rc=SQLEXEC(nConn,"select sum(x) as n from estlvl")
5. Display the type of the result in VFP.
    MESSAGEBOX(TYPE("sqlresult.n")) C=character,N=numeric

Suggested fix:
Honor the "change bigint to int" connection option for sum aggregates.

I created a test case on C but I was not able to reproduce the bug. Could you please build and try my test case ?

Test case

Attachment: test.c (text/plain), 4.32 KiB.

I compiled and ran your test case using VC++ 6.0. With MySQL 5.0.15, it returns type 0. But, when I changed it to connect to a MySQL 4.1.12a server, it returned type 8.

I then changed the program to query a BIGINT(20) field on the database. It returned type -5. When option=16384 was added to the connect string, it returned type 4.

I had originally assumed that this occurred because the aggregate computations had been moved to bigint data types on the MySQL server. But, I also tried the aggregate function on a decimal(8,3) field (whose sum could not be accurately represented by a bigint) and got the same result.

Could the MySQL server be not setting any SQL type for these sum aggregates and the ODBC driver is returning them as char as a last resort?

You can use: select cast(sum(x) as char) as n from estlvl. If it can help you. But I was not able to reproduce your problems. I've got only type 3. Could you please download new myodbc and try to reproduce the bug on 3.51.12 ?

Updating to version 3.51.12 corrected this.