Bug #15583 BIN()/OCT()/CONV() do not work with BIT values
Submitted: 8 Dec 2005 11:37 Modified: 21 Sep 2006 2:12
Reporter: Alexander Nozdrin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.17 OS:Linux (linux)
Assigned to: Chad MILLER CPU Architecture:Any

[8 Dec 2005 11:37] Alexander Nozdrin
Description:
Since BIT is a numeric type, so BIN(), OCT() and CONV() are expected
to work with it correctly. The problem now is that these functions
always return 0 (zero) for any BIT value.

How to repeat:
mysql> create table t1(b BIT(8), n INT);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(128, 128);
Query OK, 1 row affected (0.00 sec)

mysql> select hex(b), bin(b), oct(b), hex(n), bin(n), oct(n) from t1;
+--------+--------+--------+--------+----------+--------+
| hex(b) | bin(b) | oct(b) | hex(n) | bin(n)   | oct(n) |
+--------+--------+--------+--------+----------+--------+
| 80     | 0      | 0      | 80     | 10000000 | 200    |
+--------+--------+--------+--------+----------+--------+
1 row in set (0.00 sec)

mysql> select hex(b + 0), bin(b + 0), oct(b + 0), hex(n), bin(n), oct(n) from t1;
+------------+------------+------------+--------+----------+--------+
| hex(b + 0) | bin(b + 0) | oct(b + 0) | hex(n) | bin(n)   | oct(n) |
+------------+------------+------------+--------+----------+--------+
| 80         | 10000000   | 200        | 80     | 10000000 | 200    |
+------------+------------+------------+--------+----------+--------+
1 row in set (0.00 sec)

mysql> select conv(b, 10, 2), conv(b + 0, 10, 2) from t1;
+----------------+--------------------+
| conv(b, 10, 2) | conv(b + 0, 10, 2) |
+----------------+--------------------+
| 0              | 10000000           |
+----------------+--------------------+
1 row in set (0.00 sec)
[15 Aug 2006 21:18] Chad MILLER
The problem here, as far as I can tell is that we represent the bit field as a string and then try to transfer that into a number by iterating over the bytes, changing those to numbers as we go.

But, the string we create doesn't have characters '1' and '2' and '8', but one byte, ordinal 128, '\200', '\x80'.  So that byte isn't even found in the character-value lookup, and we change that into a number as expected.
[16 Aug 2006 14:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10549

ChangeSet@1.2235, 2006-08-16 16:21:49+02:00, cmiller@maint1.mysql.com +3 -0
  Bug#15583: BIN()/OCT()/CONV() do not work with BIT values
  
  Converting BIT to a string (an intermediate step in conversion) does 
  not yield an ASCII numeric string, so we skip that step for BIT and
  get the integer value directly from the item.
  
  This site in sql/item_strfunc.cc may be ripe for refactoring for
  other types as well, where converting to a string is a waste of time.
[18 Aug 2006 21:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10639

ChangeSet@1.2235, 2006-08-18 23:06:02+02:00, cmiller@maint1.mysql.com +3 -0
  Bug#15583: BIN()/OCT()/CONV() do not work with BIT values
  
  Converting BIT to a string (an intermediate step in conversion) does 
  not yield an ASCII numeric string, so we skip that step for BIT and
  get the integer value directly from the item.
  
  This site in sql/item_strfunc.cc may be ripe for refactoring for
  other types as well, where converting to a string is a waste of time.
[22 Aug 2006 15:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10733

ChangeSet@1.2235, 2006-08-22 17:51:00+02:00, cmiller@maint1.mysql.com +3 -0
  Bug#15583: BIN()/OCT()/CONV() do not work with BIT values
  
  Converting BIT to a string (an intermediate step in conversion) does 
  not yield an ASCII numeric string, so we skip that step for BIT and
  get the integer value directly from the item.
  
  This site in sql/item_strfunc.cc may be ripe for refactoring for
  other types as well, where converting to a string is a waste of time.
[13 Sep 2006 13:46] Chad MILLER
In 5.0.26 and 5.1.12-beta.
[15 Sep 2006 22:21] Chad MILLER
Available in 5.1.12-beta.
[21 Sep 2006 2:12] Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs.