MySQL Bugs: #30324: Grouping queries with COUNT(DISTINCT bit column) return wrong results

Bug #30324	Grouping queries with COUNT(DISTINCT bit column) return wrong results
Submitted:	9 Aug 2007 1:31	Modified:	23 Oct 2007 18:34
Reporter:	Igor Babaev
Status:	Closed
Category:	Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0-bk, 5.1-bk	OS:	Any
Assigned to:	Sergey Petrunia	Target Version:	5.1.23

Description:
A grouping query with COUNT(DISTINCT bit column) in the SELECT list
mostly returns a wrong result set: it always returns 1 for each group.

For example for the table:
CREATE TABLE t1 (a int, b bit(2));
that is populated by the statement
INSERT INTO t1 VALUES (3, 2), (2, 3), (2, 0), (3, 2), (3, 1);

MySQL returns:

mysql> SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;
+-------------------+
| COUNT(DISTINCT b) |
+-------------------+
|                 1 |
|                 1 |
+-------------------+
2 rows in set (0.00 sec)

How to repeat:
Use the following commands:

CREATE TABLE t1 (a int, b bit(2));

INSERT INTO t1 VALUES (3, 2), (2, 3), (2, 0), (3, 2), (3, 1);

SELECT COUNT(DISTINCT b) FROM t1 GROUP BY a;

Suggested fix:
The cause of this bug differs from the cause of the problem reported in bug entry #30219
that has been recently fixed (and patch queued). 
The fact is the Unique::unique_add method employed by the current implementation
of the COUNT(DISTINCT) operation assumes that the argument of the operation is always
contiguous which is nor correct for BIT colums.

Thank you for the bug report.

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/33963

ChangeSet@1.2513, 2007-09-08 20:23:52+04:00, sergefp@mysql.com +3 -0
  BUG#30324: Grouping queries with COUNT(DISTINCT bit column) return wrong results
  - The bug was caused by COUNT(DISTINCT ...) code using Unique object in 
    a way that assumed that BIT(N) column occupies a continous space in
    temp_table->record[0] buffer. 
  - The fix is to make COUNT(DISTINCT ...) code instruct create_tmp_table to
    create temporary table with column of type BIGINT, not BIT(N).

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/34262

ChangeSet@1.2513, 2007-09-14 14:53:13+04:00, sergefp@mysql.com +3 -0
  BUG#30324: Grouping queries with COUNT(DISTINCT bit column) return wrong results
  - The bug was caused by COUNT(DISTINCT ...) code using Unique object in 
    a way that assumed that BIT(N) column occupies a contiguous space in
    temp_table->record[0] buffer. 
  - The fix is to make COUNT(DISTINCT ...) code instruct create_tmp_table to
    create temporary table with column of type BIGINT, not BIT(N).

Pushed into 5.0.50

Pushed into 5.1.23-beta

Noted in 5.0.50, 5.1.23 changelogs.

Queries that had a GROUP BY clause and selected COUNT(DISTINCT
bit_column) returned incorrect results.