MySQL Bugs: #43670: Values for BIT don't appear in resultset

Bug #43670	Values for BIT don't appear in resultset
Submitted:	16 Mar 2009 9:47	Modified:	19 Oct 2009 6:53
Reporter:	Olivier VILLEMAIN	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	5.0 r-17	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	CHECKED, regression

Description:
In resultset tab, BIT values don't appear.
Boolean data can be viewed only with a right click and "View Field in Popup Editor".

In 5.0 r-12, the grid had "b'0'" or "b'1'" value.

How to repeat:
CREATE TABLE `my_table` (
  `id` int NOT NULL,
  `flag` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `my_table` (`id`, `flag`) VALUES
(1, b'0'),
(2, b'1');

SELECT * FROM my_table m;

In resultset, we only can see : "b"

Suggested fix:
Show like in 5.0 r-12 (my previous installed version).

Thank you for the bug report. Please read the Manual item:

http://dev.mysql.com/doc/refman/5.0/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 value"

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.80-Win32 bzr revno 2773-log Source distribution

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

mysql 5.0 > use test
Database changed
mysql 5.0 > CREATE TABLE `my_table` (
    ->   `id` int NOT NULL,
    ->   `flag` bit(1) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql 5.0 > INSERT INTO `my_table` (`id`, `flag`) VALUES
    -> (1, b'0'),
    -> (2, b'1');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.0 > SELECT * FROM my_table;
+----+------+
| id | flag |
+----+------+
|  1 |      |
|  2 | ☺    |
+----+------+
2 rows in set (0.00 sec)

mysql 5.0 > SELECT id, flag+0 FROM my_table;
+----+--------+
| id | flag+0 |
+----+--------+
|  1 |      0 |
|  2 |      1 |
+----+--------+
2 rows in set (0.00 sec)

mysql 5.0 >

Actually MySQL is returned a BIT type, and it's perfect like this... BUT the representation in MySQL Query Browser prevents reading (with a simple "SELECT *") and editing (always).

The documentation of this application ("9.7. Editing Result Sets Within the Result Area") don't says that BIT columns aren't editable.

Previously (5.0 r-12) that shows "b'1'" or "b'0'" and that was too a bit value.
Earlier this allowed reading AND editing bit columns.

MySQL returns a binary type for a bit and it's not a bug, it's true.
But the display and editing in Query Browser is a regression, so it's a bug.

Thank you for the bug report. Similar to bug http://bugs.mysql.com/bug.php?id=28422 but this applied to command client.

Verified as described by using Query Browser 1.2.12 and 1.2.17.

Bug #36239 is a duplicate of this bug here

Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. We won't fix this anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/