MySQL Bugs: #24130: empty values for bit field

Bug #24130	empty values for bit field
Submitted:	9 Nov 2006 9:18	Modified:	30 Nov 2009 14:54
Reporter:	Maciej Pilichowski	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	5.0.26, 5.1	OS:	Linux (opensuse 10.0)
Assigned to:		CPU Architecture:	Any

Description:
I designed table with simple bit(1) field to serve as boolean flag. I can filter out the values with such field, no problem, but when I try to display the values, I see header and... nothing. Just empty values (_NOT_ null!), just nothing.

How to repeat:
create table t (f bit(1));
insert into t values (1),(0);
select * from t;

Suggested fix:
Show raw number, i.e.
3 for 11
7 for 111
etc.

I guess everybody can do bit-arithmetic.

Thank you for a problem report. I think, you just have to use hex() function if you need to see "printable" bit values:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.30-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t (f bit(1));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values (1),(0);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

smysql> select * from t;
+------+
| f    |
+------+
| ☺    |
|      |
+------+
2 rows in set (0.00 sec)

mysql> select hex(f) from t;
+--------+
| hex(f) |
+--------+
| 1      |
| 0      |
+--------+
2 rows in set (0.00 sec)

Thanks for the reply. I still think there is something wrong with bit fields. 
Bit fields are usually treated as numeric data and I can use them so in MySQL while inserting data. But as I can see now I am forced to treat them as characters when fetching data.
But it is in contrary to character field -- you cannot insert numeric value. 

So in my opinion bit fields in MySQL are flawed. Once you have to treat them as numbers (or force conversion with b'....') and second time you have to treat them as opposites -- characters and convert them back to numbers.
My suggestion -- make clear decision, either bit fields are numbers (yes!) or characters. Otherwise it leads only to confusion. 

I reset status to open/feature request since it would be good for MySQL community to see things straighten up.

While current way to work with BIT data clearly described in details in our manual (http://dev.mysql.com/doc/refman/5.1/en/bit-field-values.html etc), I think it indeed makes sense to either clearly distinguish BIT type from numeric types or make it works as other numeric types some day.