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)
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.10
• 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 [e-mail] +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.