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 12:37	Modified:	21 Sep 2006 4:12
Reporter:	Alexander Nozdrin
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.17	OS:	Linux (linux)
Assigned to:	Bugs System	Target Version:

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.