Bug #8616 distinct with group by, suppresses rollup (and/or parts of result)
Submitted: 19 Feb 2005 1:56 Modified: 21 Mar 2005 20:04
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10 OS:
Assigned to: Igor Babaev CPU Architecture:Any

[19 Feb 2005 1:56] Martin Friebe
Description:
please see below:

How to repeat:
create table x (a integer, b integer);
insert into x values (1,4), (2,2),(2,2), (4,1),(4,1),(4,1),(4,1);

# without distinct, all works fine
 select  sum(b), count(distinct b) from x group by a with rollup;
+--------+-------------------+
| sum(b) | count(distinct b) |
+--------+-------------------+
|      4 |                 1 |
|      4 |                 1 |
|      4 |                 1 |
|     12 |                 3 |
+--------+-------------------+
4 rows in set (0.00 sec)

#with  distinct, the rollup (which is clearly different) dissapears
select distinct  sum(b), count(distinct b) from x group by a with rollup;
+--------+-------------------+
| sum(b) | count(distinct b) |
+--------+-------------------+
|      4 |                 1 |
+--------+-------------------+

# evenw worse, in the followinfg example all rows are different
select  sum(b), count(distinct b), count(*) from x group by a with rollup;
+--------+-------------------+----------+
| sum(b) | count(distinct b) | count(*) |
+--------+-------------------+----------+
|      4 |                 1 |        1 |
|      4 |                 1 |        2 |
|      4 |                 1 |        4 |
|     12 |                 3 |        7 |
+--------+-------------------+----------+
# with distinct
 select  distinct  sum(b), count(distinct b), count(*) from x group by a with rollup;
+--------+-------------------+----------+
| sum(b) | count(distinct b) | count(*) |
+--------+-------------------+----------+
|      4 |                 1 |        1 |
+--------+-------------------+----------+
1 row in set (0.00 sec)

Suggested fix:
-
[16 Mar 2005 6:51] 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/internals/23072
[20 Mar 2005 8:01] Igor Babaev
This fix is very important for the full fix of the case reported in bug #6215

The fix for this bug will appear in 4.1.11 and in 5.0.3.
The fix was merged from 4.1 into 5.0

ChangeSet
  1.2121 05/03/15 22:50:54 igor@rurik.mysql.com +5 -0
  olap.result, olap.test:
    Added a test case for bug #8616.
  item.h:
    Fixed bug #8616.
    Added class Item_null_result used in rollup processing.
  sql_select.h, sql_select.cc:
    Fixed bug #8616.
    Added JOIN::rollup_write_data to cover rollup queries
    with DISTINCT. Modified other rollup methods.
[21 Mar 2005 20:04] Paul Dubois
Noted in 4.1.11, 5.0.4 changelogs.