MySQL Bugs: #15583: BIN()/OCT()/CONV() do not work with BIT values

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

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)

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.

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.

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.

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.

In 5.0.26 and 5.1.12-beta.

Available in 5.1.12-beta.

Noted in 5.0.26, 5.1.12 changelogs.