| Bug #33647 | WITH ROLLUP fails when using subqueries | ||
|---|---|---|---|
| Submitted: | 3 Jan 2008 11:16 | Modified: | 4 Jan 2008 16:57 |
| Reporter: | Christian Caesar | ||
| Status: | Verified | ||
| Category: | Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1.22 | OS: | Any |
| Assigned to: | Evgeny Potemkin | Target Version: | |
| Tags: | with rollup, subquery, SELECT, GROUP BY | ||
| Triage: | Triaged: D2 (Serious) / R2 (Low) / E4 (High) | ||
[4 Jan 2008 16: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)
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