Bug #32171 ODBC Driver incorrectly parses large Unsigned Integers
Submitted: 7 Nov 2007 20:38 Modified: 30 Nov 2007 22:30
Reporter: Bob Hansen Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.20 OS:MacOS (10.4.10)
Assigned to: Jim Winstead CPU Architecture:Any
Tags: odbc driver mac unsigned integer long sqlbindcol

[7 Nov 2007 20:38] Bob Hansen
Description:
When reading in a large unsigned integer (such as 4255080020), the value retrieved back from the driver is always the same, 2147483647. Interestingly, that is exactly one less than half of what an unsigned integer can hold (2^32 - 1). Unsigned integers smaller than 2^32 work fine. It seems as though the driver treats unsigned integers as signed as they pass through.

How to repeat:
MySQL server version: 5.0.19-nt on Windows XP SP2
ODBC Driver: 3.51.21 on PowerPC iMac
and also 3.41.20 on x86 Mac Pro

Create a table with one column of data type Unsigned Integer. Create a row with 4255080020 in the field. Create a C project and connect to the database to Fetch data. Call SqlBindCol with targettype of SQL_C_ULONG, size 4, and send in a pointer to an unsigned long. After SqlFetch is called, the unsigned long is populated with the value 2147483647. One example of a "large" unsigned integer being used is 4255080020.

Using Xcode for development which is treating long, unsigned long, int, and unsigned int as 4 Bytes. This matches Integer data type in SQL. Connection is being made accross LAN with full access to database.

Note: Other ODBC functions for SQL are called in the process but are omitted above due to the strong doubt of their importance to the problem. (such as SqlAllocHandle and SqlExecDirect)

Suggested fix:
Seems to be possibly a bug in Mac versions of the ODBC driver that somehow converts outgoing unsigned integer to a signed integer, and concatinates it to be the largest possible signed integer (aka 2147483647).
[7 Nov 2007 20:41] Bob Hansen
I forgot to add I also tried the newest mysql server but still no dice.

mysql-5.0.45-win32

Server is using Windows XP SP2.
Client is using Intel MacOS 10.4.10 or PowerPC MacOS 10.4.10.
[8 Nov 2007 7:27] Tonci Grgin
Hi Bob and thanks for your report. It looks rather important so I'll give it a priority. But before that I would need you to follow on your line: "Create a C project and connect to the database to Fetch data" and attach simple but complete test case demonstrating this error. Something like:
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>

void main()
{
  SQLHENV env;
  HSTMT stmt, hstmt_prep;
  HSTMT stmt_ins;
  SQLHDBC dbc;
   
   SQLRETURN rc;
   SQLCHAR     connOut[255], Val[255];/* buffer for connection output */
   SQLSMALLINT szConnOut; /* num bytes returned in connOut */

   SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,(SQLHANDLE*)&env);
   SQLSetEnvAttr(env,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
   SQLAllocHandle(SQL_HANDLE_DBC,env,(SQLHANDLE*)&dbc);
  
   rc = SQLDriverConnect(dbc, NULL, (SQLCHAR *)"DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3306;UID=root;PWD=*****;Database=test;OPTION=3",
	   _countof("DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3306;UID=root;PWD=*****;Database=test;OPTION=3"), connOut, 255, &szConnOut, SQL_DRIVER_NOPROMPT);

  SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
  SQLAllocHandle(SQL_HANDLE_STMT, dbc, &hstmt_prep);
/*************************************************************************************/
    SQLExecDirect(hstmt_prep, (SQLCHAR *)"USE test", SQL_NTS);
    SQLExecDirect(hstmt_prep, (SQLCHAR *)"DROP TABLE IF EXISTS `test`.`bug32171`", SQL_NTS);
    SQLExecDirect(hstmt_prep, (SQLCHAR *)"CREATE TABLE `test`.`bug32171` (`col1` INTEGER UNSIGNED NOT NULL)",SQL_NTS);
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt_prep);
etc...
[12 Nov 2007 15:13] Bob Hansen
Test case to show incorrect ODBC parsing

Attachment: main.c (text/plain), 4.18 KiB.

[12 Nov 2007 20:58] Jim Winstead
What happens if you fix the type of valueIntoServer and valueFromServer to be SQLUINTEGER?

Depending on the platform, sizeof(unsigned long) may not be the same as sizeof(SQLUINTEGER), and ODBC expects to be passed a SQLUINTEGER for values identified as SQL_C_ULONG.
[12 Nov 2007 21:07] Jim Winstead
Never mind. Found the problem.
[12 Nov 2007 21:10] Jim Winstead
Don't use atol() on an unsigned value

Attachment: bug32171.diff (text/plain), 2.04 KiB.

[12 Nov 2007 21:24] Bob Hansen
Yeah, same behavior with SQLINTEGER. Both SQLINTEGER and unsigned long show up as 4 bytes using sizeof.

Thanks for the verification. How soon do you expect the change to be made into a release driver? Also, make sure to fix code for both Intel and PowerPC Mac versions. We haven't tried the test on Windows.
[13 Nov 2007 17:05] Jim Winstead
The fix for this bug has been committed, and will be in 3.51.22.
[13 Nov 2007 17:11] Bob Hansen
Wonderful, thanks. Make sure to also add to 3.41.22.

On a side note, this code example broke on SqlBindCol. I had same trouble with SqlGetData. Does that call the same underlying code?
[13 Nov 2007 17:16] Jim Winstead
There's no such thing as 3.41.22. And yes, this bug would have shown up in SQLGetData as well as SQLBindCol.
[13 Nov 2007 18:12] Bob Hansen
By 3.41.22 I mean the Intel Mac version not the PowerPC Mac version. You have two different downloads depending on your CPU architecture. We had the same problem with both versions.
[15 Nov 2007 6:14] MC Brown
A note has been added to the 3.51.22 changelog: 

Unsigned integer values greater than the maximum value of a signed integer would be handled incorrectly.
[30 Nov 2007 22:30] Bob Hansen
Just wanted to let you know I tried the new driver today (22) and it works perfectly with unsigned integers. Thanks!
[2 Dec 2007 19:37] Tonci Grgin
Bob, thanks for your feedback and your interest in MySQL.
[24 Feb 2010 14:47] Tonci Grgin
Due to Bug#41800 asked Eric to recheck on this.