Bug #33647 WITH ROLLUP fails when using subqueries
Submitted: 3 Jan 2008 10:16 Modified: 16 May 2013 6:10
Reporter: Christian Caesar Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.22 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Tags: GROUP BY, SELECT, subquery, with rollup

[3 Jan 2008 10:16] Christian Caesar
Description:
select statements that contain sub-selects and a GROUP BY...WITH ROLLUP syntax
should render results like this:

col1   | sum col2 | sum col3
----------------------------
txt1   |       12 |       34
txt2   |       56 |       78
NULL   |       68 |      112 

however, the result looks like this:

col1   | sum col2 | sum col3
----------------------------
txt1   |       12 |       34
txt2   |       56 |       78
NULL   |       56 |       78 

How to repeat:
you need a table like this

CREATE TABLE  `myschema`.`my_demo_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` varchar(45) NOT NULL,
  `col2` varchar(45) NOT NULL,
  `col3` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=805 DEFAULT CHARSET=latin1;

then you fill the table with some data.

then you use a select statement like this:

SELECT m.col1 as 'col1',
    (select count(*) from my_demo_table m2 where m2.col2 = '' and m2.col1 = m.col1) as 'sum col2',
    (select count(*) from my_demo_table m3 where m3.col2 != '' and m3.col1 = m.col1 ) as 'sum col3'
FROM my_demo_table m
group by col1
with rollup
[4 Jan 2008 15:57] Susanne Ebrecht
Verified as described.

mysql> create table t(id serial, col1 text not null, col2 text not null, primary key(id));

mysql> insert into t(col1, col2) values ('abc','a'),('abc', 'a'),('abc','a'),('def','a'),('def','a');

mysql> insert into t(col1, col2) values ('abc','b'),('abc', 'b'),('abc','b'),('def','b'),('def','b');

mysql> select t1.col1 as c1, (select count(*) from t as t2 where t2.col2='a' and t2.col1=t1.col1) as c2, (select count(*) from t as t3 where t3.col2='b' and t3.col1=t1.col1) as c3 from t as t1 group by c1 with rollup\G
*************************** 1. row ***************************
c1: abc
c2: 3
c3: 3
*************************** 2. row ***************************
c1: def
c2: 2
c3: 2
*************************** 3. row ***************************
c1: NULL
c2: 2
c3: 2
3 rows in set (0.00 sec)
[26 Oct 2012 13:56] Joe Grasse
Just curious if the fix for this is even on the radar?
[16 May 2013 6:10] Chaithra Marsur Gopala Reddy
As per the standard, since sub-query is not part of the grouping column, it would be incorrect to expect it to work like other aggregated columns.
For ex: select a, count(*) from t1 group by a with rollup is not equivalent to select a, (sub-query which does count(*) for the group) from t1 group by a with rollup.

This is because , the count(*) in the sub-query is not performed within the original grouping, rather performed after the grouping is done. So the result from sub-query is no more than a constant value per group.