MySQL Bugs: #77350: BIT type returns inconsistent string data

Bug #77350	BIT type returns inconsistent string data
Submitted:	14 Jun 2015 23:36	Modified:	25 Jan 2016 9:27
Reporter:	Eion Robb	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.3.2	OS:	Windows (32bit ANSI)
Assigned to:		CPU Architecture:	Any

Description:
The BIT data type will return incorrect data when calling from PHP.  Instead of a '1' or a '0' value, it returns a '\0' or '\1' string.

This looks to be because of a conversion in sql_get_data() in results.c:541 which outputs '\1' or '\0'.

This ends up with different behaviour to other ODBC drivers (MSSQL, Postgres, SQLite) which return '1' or '0' for SQL_BIT types, and provides consistent behaviour with other datatypes in the MySQL ODBC driver, eg TINYINT

How to repeat:
From PHP (eg using odbc_exec()/odbc_result_all()):

CREATE TABLE test123 (testcol BIT, testcol2 TINYINT);
INSERT INTO test123 (testcol, testcol2) VALUES (1, 1);
SELECT * FROM test123;

Outputs:
'\1', '1'
Expected:
'1', '1'

Suggested fix:
Change (line 548)
*((char *)rgbValue)= numericValue > 0 ? '\1' : '\0';
to
*((char *)rgbValue)= numericValue > 0 ? '1' : '0';

and (line 552)
 '\1' : '\0';
to
 '1' : '0';

Hello Eion Robb,

Thank you for the bug report.
I tried to reproduce the issue at my end using PHP and Connector / ODBC 5.3.2 / 5.3.4 32 bit ANSI but couldn't trace out any issue reading BIT values.
here is clearly described in the manual about BIT fields https://dev.mysql.com/doc/refman/5.6/en/bit-field-literals.html

Thanks,
Chiranjeevi.

screenshot

Attachment: 77350.PNG (image/png, text), 2.34 KiB.

What version of PHP were you testing with?
We're testing on PHP 5.2 and 5.3.

Also, you should have pointed me to https://dev.mysql.com/doc/refman/5.6/en/boolean-literals.html - the 'BIT' type in ODBC would be equivalent to a Boolean in MySQL - see https://msdn.microsoft.com/en-us/library/ms710150(v=vs.85).aspx

So digging some more, I guess the problem is:
The driver reports (via SQLGetTypeInfo) that the MySQL 'BIT' type is the ODBC 'BIT' type, which is incorrect, it's closer to a binary than what ODBC defines a 'BIT' as.  The driver also doesn't provide MySQL's 'BOOLEAN' type as a 'BIT' which would be a closer match.
(See the footnote [8] of https://msdn.microsoft.com/en-us/library/ms710150(v=vs.85).aspx which says "The SQL_BIT data type has different characteristics than the BIT type in SQL-92." )

In our particular use-case, the flow-on effect is that boolean data stored in this field is converted to a binary string by the copy_binary_result() function, instead of returned as a number/boolean format as it went in.

Hello Eion Robb,

Thank you for your feedback.
Verified based on internal discussion with dev's, take it as a Feature Request.

Thanks,
Chiranjeevi.