MySQL Bugs: #32821: Gives wrong value fetching from a bit column

Bug #32821	Gives wrong value fetching from a bit column
Submitted:	28 Nov 2007 16:32	Modified:	24 Mar 2010 15:39
Reporter:	John Water	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.21.00 and 5.01.00.00	OS:	Windows (XP)
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any
Tags:	bit column, ODBC driver

Description:
The MyODBC driver, v3.51.21.00 and v5.01.00.00 will give a value of zero for non-zero values in a column with a datatype of bit, if SQL_C_USHORT is used in SQLBindCol.

Here is the sample source code (the full source code, executable for Windows, and an ODBC tracing file will be attached in a zip file:

------------------------------------------------------------------------
....
    char *	create_table	= "create table test (pk int primary key, c1 bit)";
    char *	insert_stmt1	= "insert into test values( 1, 0 )";
    char *	insert_stmt2	= "insert into test values( 2, 1 )";
    char *	select_table	= "select pk, c1 from test";
....
    ret = SQLPrepare( stmt, (SQLCHAR *)select_table, SQL_NTS  );
    ret = SQLExecute( stmt );
    ret = SQLBindCol( stmt, 1, SQL_C_SLONG, &pk, sizeof( pk ), &pk_ind );
    ret = SQLBindCol( stmt, 2, SQL_C_USHORT, &c1, sizeof( c1 ), &c1_ind );
    while( TRUE ) {
	ret = SQLFetchScroll( stmt, SQL_FETCH_NEXT, 0 );
	if( SQL_SUCCEEDED( ret ) ) {
	    printf( "SQLFetch: ret = %d: pk = %d, c1 = %d, pk_ind %ld, c1_ind = %ld\n",
		ret, pk, c1, pk_ind, c1_ind );
....
----------------------------------------------------------------------------
The results will be
SQLFetch: ret = 0: pk = 1, c1 = 0, pk_ind 4, c1_ind = 2
SQLFetch: ret = 0: pk = 2, c1 = 0, pk_ind 4, c1_ind = 2

This is wrong: c1 should be 1 in the second row.

By the way, it works well if you run the repro against Microsoft SQL Server and Sybase ASE.

How to repeat:
The steps to repro:
1) unzip the attached file;
2) run
          odbcbug "dsn=your_dsn;uid=your_uid;pwd=your_pwd"
then you'll see something like
SQLFetch: ret = 0: pk = 1, c1 = 0, pk_ind 4, c1_ind = 2
SQLFetch: ret = 0: pk = 2, c1 = 0, pk_ind 4, c1_ind = 2
Fetch completed!
on your command window.

Patch v.1.0

Attachment: bug32821.patch (application/octet-stream, text), 4.27 KiB.

Forgot to remove couple of lines in previous variant of the patch

Attachment: bug32821_2.patch (application/octet-stream, text), 3.92 KiB.

reworked patch. now i like it better. thought would post it along w/ 30349, but it takes longer.

Attachment: bug32821v2.patch (application/octet-stream, text), 9.21 KiB.

What is the purpose of using binary2numeric() and then (numericValue & (SQLSCHAR)(-1))?

We shouldn't have SQL_C_BIT in isNumericCType().

Also, we can remove this: if (value[0] == 1 || anyNonZeroByte(value, length))
It will be fixed in Bug#39644 (however we decide to do it).

> What is the purpose of using binary2numeric() and then (numericValue & 
> (SQLSCHAR)(-1))?

hmm, it's been so long time ago :)
I think it's to avoid casting warnings, zero-ing all bits what we don't need for every particular c-type.

> We shouldn't have SQL_C_BIT in isNumericCType().

well, i've just checked - in my local version it's alredy been commented out

> Also, we can remove this: if (value[0] == 1 || anyNonZeroByte(value, length))

doesn't it fix that bug you refer to? because doing this I was aware of it too.

Revisited patch

Attachment: bug32821.diff (text/x-diff), 13.10 KiB.

Patch has been pushed as rev#869 and if it is lucky enough - will be released in 5.1.7.

An entry has been added to the 5.1.7 changelog:

MySQL Connector/ODBC returned a value of zero for a column with a non-zero value. This happened when the column had a datatype of BIT, and SQL_C_USHORT was used in SQLBindCol.

Changelog entry has been updated:

MySQL Connector/ODBC returned a value of zero for a column with a non-zero value. This happened when the column had a datatype of BIT, and any numeric type was used in SQLBindCol.