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
Triage: Needs Triage: D2 (Serious)

[28 Nov 2011 16:29] Sergio Bobillier
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                |
+----------+----------+-----------------+------------------+
[28 Nov 2011 16:47] Miguel Solorzano
Verified on Windows too.
[28 Nov 2011 21:31] Peter Laursen
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)
[29 Nov 2011 9:45] Peter Laursen
Also see http://bugs.mysql.com/bug.php?id=63470
[13 Dec 2011 15:34] Armando Lopez Valencia
Verified as reported.
Ubuntu 11.04x64
WB 5.2.36 rev 8542
[30 Aug 2012 22:37] Philip Olson
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.