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: | |
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
[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.