MySQL Bugs: #68564: ROLLUP-NULL sometimes becomes other than NULL

Bug #68564	ROLLUP-NULL sometimes becomes other than NULL
Submitted:	4 Mar 2013 21:17	Modified:	6 Mar 2013 12:35
Reporter:	Programmer Old	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.5.8-log	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Sometimes the ROLLUP-NULL becomes not NULL but the otherwise last row s field

How to repeat:
mysql> CREATE TEMPORARY TABLE Q (e DATE);
Query OK, 0 rows affected (0.50 sec)

mysql> INSERT INTO Q VALUE ('2011/5/1'),('2011/8/9'),('2012/11/5'),('2012/1/1');
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT EXTRACT(YEAR_MONTH FROM e) AS E, COUNT(*) FROM Q GROUP BY EXTRACT(YEAR_MONTH FROM e);
+--------+----------+
| E      | COUNT(*) |
+--------+----------+
| 201105 |        1 |
| 201108 |        1 |
| 201201 |        1 |
| 201211 |        1 |
+--------+----------+
4 rows in set (0.06 sec)

mysql> SELECT EXTRACT(YEAR_MONTH FROM e) AS E, COUNT(*) FROM Q GROUP BY EXTRACT(YEAR_MONTH FROM e) WITH ROLLUP;
+--------+----------+
| E      | COUNT(*) |
+--------+----------+
| 201105 |        1 |
| 201108 |        1 |
| 201201 |        1 |
| 201211 |        1 |
|   NULL |        4 |
+--------+----------+
5 rows in set (0.03 sec)

mysql> SELECT DATE_FORMAT(e, '%Y%b') AS E, COUNT(*) FROM Q GROUP BY EXTRACT(YEAR_MONTH FROM e) WITH ROLLUP;
+---------+----------+
| E       | COUNT(*) |
+---------+----------+
| 2011May |        1 |
| 2011Aug |        1 |
| 2012Jan |        1 |
| 2012Nov |        1 |
| 2012Nov |        4 | not NULL
+---------+----------+
5 rows in set (0.00 sec)

mysql> SELECT DATE_FORMAT(e, '%Y%b') AS E, COUNT(*) FROM Q GROUP BY EXTRACT(YEAR_MONTH FROM e) DESC WITH ROLLUP;
+---------+----------+
| E       | COUNT(*) |
+---------+----------+
| 2012Nov |        1 |
| 2012Jan |        1 |
| 2011Aug |        1 |
| 2011May |        1 |
| 2011May |        4 | not NULL
+---------+----------+
5 rows in set (0.00 sec)

Suggested fix:
Keep the ROLLUP-NULL NULL

Thank you for the bug report. Verified as described.

I got the same bug. Can be repeated this way:

CREATE TABLE t (a INT, b INT, c INT);
INSERT INTO t VALUES (1,0,0),(1,0,1),(1,1,0),(1,1,1);
SELECT c,c FROM t GROUP BY b,c WITH ROLLUP;

I plan to implement CUBE feature and might also fix this bug in the same time.

I ran into this using rollup with a view as follows:

drop table bug_test;
create table bug_test (
	a int, b char, c char
);
insert into bug_test values (1, 'A', 'B');
insert into bug_test values (2, 'A', 'C');
insert into bug_test values (3, null, null);

create or replace view bug_test_view as select a as u, coalesce(b, 'bob') as w, c as v from bug_test;

select u as a, w as b, count(*) 
from bug_test_view
group by u, w with rollup