MySQL Bugs: #30219: GROUP BY with DISTINCT broken with BIT type in MyISAM in many cases

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

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. :)

Thank you for the report.

Verified as described.

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.

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.

Pushed into 5.1.22-beta

Pushed into 5.0.48

Noted in 5.0.48, 5.1.22 changelogs.

GROUP BY on BIT columns produced incorrect results.

This was pushed to 5.1.23, not 5.1.22.