Bug #8616 Submitted: distinct with group by, suppresses rollup (and/or parts of result) 19 Feb 2005 1:56 21 Mar 2005 20:04 Martin Friebe (Gold Quality Contributor) (OCA) Closed None MySQL Server S3 (Non-critical) 4.1.10 Igor Babaev Any

[19 Feb 2005 1:56] Martin Friebe
```Description:

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.`