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:Microsoft Windows (XP)
Assigned to: Lawrenty Novitsky
Tags: bit column, ODBC driver
Triage: D3 (Medium)

[28 Nov 2007 16:32] John Water
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.
[19 Feb 2008 20:56] Lawrenty Novitsky
Patch v.1.0

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

[20 Feb 2008 15:28] Lawrenty Novitsky
Forgot to remove couple of lines in previous variant of the patch

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

[30 Mar 2008 21:00] Lawrenty Novitsky
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.

[20 Oct 2008 17:54] Jess Balint
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).
[21 Oct 2008 21:05] Lawrenty Novitsky
> 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.
[1 Dec 2009 17:06] Lawrenty Novitsky
Revisited patch

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

[11 Mar 2010 16:19] Lawrenty Novitsky
Patch has been pushed as rev#869 and if it is lucky enough - will be released in 5.1.7.
[24 Mar 2010 15:39] Tony Bedford
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.
[24 Mar 2010 17:42] Tony Bedford
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.