MySQL Bugs: #33647: WITH ROLLUP fails when using subqueries

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

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

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)

Just curious if the fix for this is even on the radar?

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.