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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.