MySQL Bugs: #28422: mysql client does not recognize bit values

Bug #28422	mysql client does not recognize bit values
Submitted:	14 May 2007 18:35	Modified:	5 Dec 2007 18:56
Reporter:	Mark Leith	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.40, 5.5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	bit, mysql client

Description:
The mysql command line client does not display values within BIT fields correctly:

mysql> create table foo (i bit);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into foo values (true), (false);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from foo;
+------+
| i    |
+------+
|     | 
|      | 
+------+
2 rows in set (0.00 sec)

mysql> select i, hex(i) from foo;
+------+--------+
| i    | hex(i) |
+------+--------+
|     | 1      | 
|      | 0      | 
+------+--------+
2 rows in set (0.08 sec)

mysql> desc foo;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| i     | bit(1) | YES  |     | NULL    |       | 
+-------+--------+------+-----+---------+-------+
1 row in set (0.06 sec)

How to repeat:
drop table if exists foo;
create table foo (i bit);
insert into foo values (true), (false);
select * from foo;
select i, hex(i) from foo;

Suggested fix:
Show values as 1 for true, 0 for false, within table output

This is not quite as simple as the 0 or 1 suggestion, but jesus yes, the client MUST understand BIT fields, this is just stupid.

As additional information:

The way you display bit values is described here:

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

Also, a note for use within the C API:

http://dev.mysql.com/doc/refman/5.0/en/c-api-prepared-statement-datatypes.html

(near end, for discussion of MYSQL_BIT_TYPE)

See bug: http://bugs.mysql.com/bug.php?id=43670.

Bug #57965 was marked as a duplicate of this one.