Bug #30324 Grouping queries with COUNT(DISTINCT bit column) return wrong results
Submitted: 8 Aug 2007 23:31 Modified: 23 Oct 2007 16:34
Reporter: Igor Babaev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0-bk, 5.1-bk OS:Any
Assigned to: Sergey Petrunya CPU Architecture:Any

[8 Aug 2007 23:31] Igor Babaev
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.
[8 Aug 2007 23:57] Miguel Solorzano
Thank you for the bug report.
[8 Sep 2007 16:25] 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/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).
[14 Sep 2007 10:54] 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/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).
[24 Sep 2007 8:31] Bugs System
Pushed into 5.0.50
[24 Sep 2007 8:35] Bugs System
Pushed into 5.1.23-beta
[23 Oct 2007 16:34] Paul Dubois
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.