Bug #30219 GROUP BY with DISTINCT broken with BIT type in MyISAM in many cases
Submitted: 3 Aug 2007 3:40 Modified: 25 Aug 2007 16:19
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0 (all) OS:Any
Assigned to: Igor Babaev CPU Architecture:Any
Tags: bit, distict, GROUP BY, myisam

[3 Aug 2007 3:40] Jeremy Cole
Description:
The combination of GROUP BY on a BIT column and COUNT(DISTINCT ...) in the query seems to be inexplicably broken in MyISAM.  Everything seems OK in at least InnoDB and MEMORY, however.

The result of all the tests in the "How to repeat" should be two rows.  In the "BROKEN" cases, we see one row.  If we GROUP BY bit_field+0 instead, it works as expected.  GROUP BY bit_field alone shows the problems.

How to repeat:
# BROKEN, fixed width table, BIT alone
DROP TABLE IF EXISTS bit_test;
CREATE TABLE bit_test (b BIT) ENGINE=MyISAM;
INSERT INTO bit_test (b) VALUES (1), (0);
SELECT b+0, COUNT(DISTINCT b) FROM bit_test GROUP BY b;

# BROKEN, variable width table, BIT before VARCHAR
DROP TABLE IF EXISTS bit_test;
CREATE TABLE bit_test (b BIT, z VARCHAR(5)) ENGINE=MyISAM;
INSERT INTO bit_test (b, z) VALUES (1, "x"), (0, "y");
SELECT b+0, COUNT(DISTINCT z) FROM bit_test GROUP BY b;

# BROKEN, fixed width table with CHAR before BIT
DROP TABLE IF EXISTS bit_test;
CREATE TABLE bit_test (z CHAR(5), b BIT) ENGINE=MyISAM;
INSERT INTO bit_test (b, z) VALUES (1, "x"), (0, "y");
SELECT b+0, COUNT(DISTINCT z) FROM bit_test GROUP BY b;

# BROKEN, fixed width table with BIT before INT
DROP TABLE IF EXISTS bit_test;
CREATE TABLE bit_test (z INT, b BIT) ENGINE=MyISAM;
INSERT INTO bit_test (b, z) VALUES (1, 1), (0, 2);
SELECT b+0, COUNT(DISTINCT z) FROM bit_test GROUP BY b;

# OKAY, fixed width table with BIT before CHAR
DROP TABLE IF EXISTS bit_test;
CREATE TABLE bit_test (b BIT, z CHAR(5)) ENGINE=MyISAM;
INSERT INTO bit_test (b, z) VALUES (1, "x"), (0, "y");
SELECT b+0, COUNT(DISTINCT z) FROM bit_test GROUP BY b;

# OKAY, fixed width table with INT
DROP TABLE IF EXISTS bit_test;
CREATE TABLE bit_test (b BIT, z INT) ENGINE=MyISAM;
INSERT INTO bit_test (b, z) VALUES (1, 1), (0, 2);
SELECT b+0, COUNT(DISTINCT z) FROM bit_test GROUP BY b;

Suggested fix:
Fix it. :)
[3 Aug 2007 8:03] Sveta Smirnova
Thank you for the report.

Verified as described.
[5 Aug 2007 9:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32115

ChangeSet@1.2501, 2007-08-05 02:27:33-07:00, igor@olga.mysql.com +3 -0
  Fix bug #30219.
  This bug manifested itself for queries with grouping by columns of
  the BIT type. It led to wrong comparisons of bit-field values and
  wrong result sets.
  Bit-field values never cannot be compared as binary values. Yet
  the class Field_bit had an implementation of the cmp method that
  compared bit-fields values as binary values. 
  Also the get_image and set_image methods of the base class Field 
  cannot be used for objects of the Field_bit class. 
  Now these methods are declared as virtual and specific implementations
  of the methods are provided for the class Field_bit.
[5 Aug 2007 21:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32122

ChangeSet@1.2501, 2007-08-05 14:16:49-07:00, igor@olga.mysql.com +3 -0
  Fix bug #30219.
  This bug manifested itself for queries with grouping by columns of
  the BIT type. It led to wrong comparisons of bit-field values and
  wrong result sets.
  Bit-field values never cannot be compared as binary values. Yet
  the class Field_bit had an implementation of the cmp method that
  compared bit-fields values as binary values. 
  Also the get_image and set_image methods of the base class Field 
  cannot be used for objects of the Field_bit class. 
  Now these methods are declared as virtual and specific implementations
  of the methods are provided for the class Field_bit.
[15 Aug 2007 9:48] Bugs System
Pushed into 5.1.22-beta
[15 Aug 2007 9:51] Bugs System
Pushed into 5.0.48
[25 Aug 2007 16:19] Paul Dubois
Noted in 5.0.48, 5.1.22 changelogs.

GROUP BY on BIT columns produced incorrect results.
[31 Aug 2007 17:03] Paul Dubois
This was pushed to 5.1.23, not 5.1.22.