MySQL Bugs: #39615: WITH Rollup fails

Bug #39615	WITH Rollup fails
Submitted:	23 Sep 2008 18:54	Modified:	13 Oct 2008 18:51
Reporter:	Sam Yuen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.0.67, 4.1, 5.0, 5.1, 6.0 bzr	OS:	Any (MS Windows, Linux)
Assigned to:		CPU Architecture:	Any
Tags:	with rollup

Description:
I am trying to create a query that get all the total users and unique users by month.

How to repeat:
CREATE TABLE `user_usage` (                                 
              `id` int(10) unsigned NOT NULL auto_increment,            
              `username` varchar(50) NOT NULL default '',               
              `action` varchar(15) NOT NULL default '',                 
              `dt` datetime NOT NULL default '0000-00-00 00:00:00',     
              PRIMARY KEY  (`id`)                                   
            ) ENGINE=InnoDB AUTO_INCREMENT=4735 DEFAULT CHARSET=latin1
-----------------------------------------------------------------------

SELECT monthname(dt) as `month`,  count(username) AS `users`,
count(distinct username) AS `unique_users`
FROM `user_usage` 
WHERE action='login' AND username 
NOT LIKE '%unknown' AND year(dt) = 2008 
GROUP BY month(dt) with rollup;

1. run the above query with rollup.
2. run the above query without rollup.

Forgot to mention. The rollup line the first field should show null but instead, it shows the last month on the list which is "august" on my instance.

Thank you for the report.

Please show what you mean query with rollup fails. Better if you are able to dump of several rows and result of queries with these rows which shows the problem.

insert into `user_usage` (`id`, `username`, `action`, `dt`) values('1','oooo','login','2008-03-03');
insert into `user_usage` (`id`, `username`, `action`, `dt`) values('2','ooow','login','2008-03-02');
insert into `user_usage` (`id`, `username`, `action`, `dt`) values('3','eeeee','login','2008-04-01');
insert into `user_usage` (`id`, `username`, `action`, `dt`) values('4','oooo','login','2008-04-23');
insert into `user_usage` (`id`, `username`, `action`, `dt`) values('5','ooow','login','2008-03-03');

I noticed that if you change monthname(dt) to month(dt), you get the null in the first field as expected.

Thank you for the feedback.

Verified as described:

SELECT monthname(dt) as `month`,  count(username) AS `users`,
count(distinct username) AS `unique_users`
FROM `user_usage` 
WHERE action='login' AND username 
NOT LIKE '%unknown' AND year(dt) = 2008 
GROUP BY month(dt) with rollup;
month   users   unique_users
March   3       2
April   2       2
April   5       3

This bug also affects dayname() as well.

I was under the impression that this is the expected result.

 select foo, 'some constant', count(*) from tbl group by foo with rollup;

rollup is affecting foo (and foo will be null), but some constant is not affected by rollup (question is: should it?)

The same is, if a query as non-aggregate fields that are not in group-by neither (only in non strict mode) (field2 would not be affected by rollup)
 select foo, field2, count(*) from tbl group by foo with rollup;

Same in the original query:

SELECT monthname(dt) as `month`,  count(username) AS `users`,
count(distinct username) AS `unique_users`
FROM `user_usage` 
WHERE action='login' AND username 
NOT LIKE '%unknown' AND year(dt) = 2008 
GROUP BY month(dt) with rollup;

The field in the group-by "month(dt)" is a hidden field (it is not present in the select-field-list.
The hidden field will be be null in rollup lines, but the field  "monthname(dt) as `month`" is not a field being grouped-by.

from the doc http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html
>>>
The NULL indicators in each super-aggregate row are produced when the row is sent to the client. The server looks at the columns named in the GROUP BY  clause following the leftmost one that has changed value. For any column in the result set with a name that is a lexical match to any of those names, its value is set to NULL. 
<<<

"month(dt)" and  "monthname(dt) as `month`"  are not a lexical match

If that were the case should I not be seeing:

month   users   unique_users
March   3       2
April   2       2
April   5       3
(null)   5       4 <-- this row

The complainer is asking for
a total for an invisible column, not the
"monthname(dt) as `month`" column. So we deliver an
invisible result. Not a bug.