Bug #78685 Wrong results when retrieving the value of a BIT column as an integer
Submitted: 2 Oct 2015 18:15 Modified: 6 Sep 2016 22:34
Reporter: Davi Arnaut (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.36 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any
Tags: BIT(32), getInt

[2 Oct 2015 18:15] Davi Arnaut
Description:
Retrieving the value of a BIT column as an integer (using getInt/getLong) will return a wrong value if the BIT-value happens to be equivalent to the decimal value of an ASCII digit.

When using the text protocol, the BIT-value is actually sent by the server as a binary-encoded integer, but JDBC first attempts to parse the value as a string-encoded integer and if it fails to parse, the value is interpreted as a binary-encoded integer.

The problem is that if the BIT-value in binary-encoded integer form is equivalent to a string-encoded integer, the latter form is used.

See ResultSetImpl::getInt for reference.

How to repeat:
mysql> CREATE TABLE t1 (a BIT(32));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t1 VALUES ('1');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT a + 0 FROM t1;
+-------+
| a + 0 |
+-------+
|    49 |
+-------+
1 row in set (0.00 sec)

$ java JDBCBitTest 
a = 1

Suggested fix:
Always assume that a BIT-value is binary encoded.
[2 Oct 2015 18:17] Davi Arnaut
Java test case

Attachment: JDBCBitTest.java (application/octet-stream, text), 619 bytes.

[2 Oct 2015 18:21] Davi Arnaut
Another example:

mysql> INSERT INTO t1 VALUES (46),(47),(48),(49),(50);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

$ java JDBCBitTest 
a = 46
a = 47
a = 0
a = 1
a = 2
[2 Oct 2015 18:24] Davi Arnaut
Java test case

Attachment: JDBCBitTest1.java (application/octet-stream, text), 628 bytes.

[2 Oct 2015 23:40] Filipe Silva
Hi Davi,

Thank you for this bug report. Verified as described.
[6 Sep 2016 22:34] Daniel So
Posted by developer:
 
Added the following entry to the Connector/J 5.1.40 changelog:

"When Connector/J retrieved the value of a BIT column as an integer using, for example, getInt() or getLong()), it returned a wrong value if the BIT value happened to be equivalent to the decimal value of some ASCII digit. This was because Connector/J attempted to parse the value as a string-encoded integer; thus, for example, the BIT value “1” was interpreted as the string “1,” whose ASCII value in decimal is “49,” so the numerical value of “49” was returned. This fix corrected the parsing behavior of Connector/J on BIT values, so they are always interpreted as binary coded."
[30 Sep 2016 17:00] Daniel So
Posted by developer:
 
The changelog entry has been corrected to the following:

"When Connector/J retrieved the value of a BIT column as an integer using, for example, getInt() or getLong(), it returned a wrong value if the BIT value happened to be equivalent to the decimal value of some ASCII digit. This was because Connector/J attempted to parse the value as a string-encoded integer; thus, for example,the BIT value “110001” (decimal 49 in binary) was interpreted as the string “1” (whose ASCII value in decimal is 49), so the numerical value of “1” was returned. This fix corrected the parsing behavior of Connector/J on BIT values, so they are always interpreted as binary coded."
[14 Oct 2016 15:00] Daniel So
The fix has been included in Connector/J 6.0.5. The entry for the 5.1.40
changelog has been included into the 6.0.5 changelog