MySQL Bugs: #63457: MySQL Workbench gives the wrong result when doing a MAX over a BIT field

Bug #63457	MySQL Workbench gives the wrong result when doing a MAX over a BIT field
Submitted:	28 Nov 2011 16:29	Modified:	30 Aug 2012 22:37
Reporter:	Sergio Bobillier	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.2.35 rev 7915	OS:	Linux (Ubuntu 11.04 x64)
Assigned to:		CPU Architecture:	Any
Tags:	bit, MAX, workbench

Description:
When performing a MAX() aggregation function over a BIT field MySQL workbench shows a clearly wrong result. It shows a number that is out of the BIT field range and is not one of the values in the table.

How to repeat:
1. Create a table with a BIT field:

CREATE TABLE t(b BIT NOT NULL DEFAULT 0);

2. Insert some data into the table:

INSERT INTO t(b) VALUES(1), (0), (1), (1), (0), (0);

3. Perform a SELECT with a MAX aggregation function over the BIT field:

SELECT MAX(b) FROM t;

MySQL Workbench shows this:

# | MAX(b)
1 | 3211306

while doing the same query on the console client gives:

+--------+
| MAX(b) |
+--------+
| 1      |
+--------+

So it is not a server error.

It also happens with more complex tables as long as they have a BIT field on them, for example when I perform the next query over a table that has the images from an album to get all the album covers:

SELECT album_id, image_id, file_name, MAX(album_cover) FROM Image GROUP BY album_id;

(album_cover is the BIT field)

album_id | image_id | file_name   | MAX(album_cover)
1        | 1        | test-01.jpg | 3145746
5        | 2        | test-02.jpg | 3145729
6        | 3        | test-03.jpg | 3211366

while the console gives:

+------------------+-----------+-------------+-------------+
| album_id | image_id | file_name       | MAX(album_cover) |
+----------+----------+-----------------+------------------+
|        1 |        1 | test-01.jpg     | 0                |
|        5 |        2 | test-02.jpg     | 0                |
|        6 |        3 | test-03.jpg     | 1                |
+----------+----------+-----------------+------------------+

Verified on Windows too.

Try framing the statememt as "SELECT CAST(MAX(b) AS SIGNED) FROM t;" or "SELECT CAST(MAX(b) AS CHAR) FROM t;" or "SELECT BIN(MAX(b)) FROM t;" or "SELECT MAX(b) + 0 FROM t;". All four return "1" also in WB - right? That is the correct way(s) to write such statement.

Understand this docs: http://dev.mysql.com/doc/refman/5.5/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()."

The returns from WB is weird indeed, but it is actually unclear (to me at least) what type "SELECT MAX(BIT-value);" returns?  A BIT-type? A string? An UNSIGNED? Anything else? 

I suspect that there is a server bug involved here too when using aggregates on BIT. 

(and BIT in MySQL is hopeless BTW.  Better use TINYINT(1) instead)

Peter
(not a MySQL person)

Also see http://bugs.mysql.com/bug.php?id=63470

Verified as reported.
Ubuntu 11.04x64
WB 5.2.36 rev 8542

Fixed as of the upcoming 5.2.43, and here's the changelog entry:

 MySQL Workbench would yield incorrect results while displaying
 results from the "MAX" function on a bit field.

Thank you for the report.