MySQL Bugs: #48393: BIT datatype -- incorrect display of value

Bug #48393	BIT datatype -- incorrect display of value
Submitted:	28 Oct 2009 22:07	Modified:	28 Oct 2009 22:33
Reporter:	John David Duncan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In a test of the BIT datatype from the mysql client, the data is stored correctly, but it is not displayed correctly. 

How to repeat:
use test;

CREATE TABLE typ9 (
  i int not null primary key,
  b1 bit(9) null,
  b2 bit(17) not null,
  b3 bit(1) not null 
);

INSERT INTO typ9 
VALUES ( 1, NULL , 17   , 0 ),
       ( 2, 1    , 1025 , 1 ),
       ( 3, 2    , 11   , 1 ) ;

mysql> select * from typ9 ;
+---+------+-----+----+
| i | b1   | b2  | b3 |
+---+------+-----+----+
| 3 |     |   
               |   | 
| 1 | NULL |    |    | 
| 2 |     |   |   | 
+---+------+-----+----+
3 rows in set (0.00 sec)

Now a closer look:

mysql> select i, b1 from typ9 ;
+---+------+
| i | b1   |
+---+------+
| 1 | NULL | 
| 2 |     | 
| 3 |     | 
+---+------+

mysql> select i, b1 + .5 from typ9 ;
+---+---------+
| i | b1 + .5 |
+---+---------+
| 3 |     2.5 | 
| 1 |    NULL | 
| 2 |     1.5 | 
+---+---------+

Adding .5 reveals the values!

One more test:

mysql> select b1 from typ9 where i = 2 into @b1 ;
Query OK, 1 row affected (0.49 sec)

mysql> select @b1 ;
+------+
| @b1  |
+------+
|    1 | 
+------+

Thank you for the bug report. Please read:

http://dev.mysql.com/doc/refman/5.1/en/bit-field-values.html

"Bit values are returned as binary values. To display them in printable form, add 0 or use a conversion function such as BIN(). High-order 0 bits are not displayed in the converted....."

Thanks in advance.

"Add 0" does not work, but "OR zero does:  SELECT bit_col | 0, ...