Bug #53144 GROUP BY with ROLLUP includes previous data in rollup

• Submitted: 25 Apr 2010 17:09
• Modified: 25 Apr 2010 17:29
• Reporter: John Larsen
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.88
• OS: Any
• CPU Architecture: Any

[25 Apr 2010 17:09] John Larsen

Description:
The following table 
TEST
number, Text, othernumber
1, "One", 1
1, "One", 2
2, "Two", 2
3, "Three", 3

With the following query
SELECT ID, Text, SUM(othernumber)FROM TEST GROUP BY number;
number, Text, othernumber
1, "One", 3
2, "Two", 2
3, "Three", 3

With rollup
SELECT ID, Text, SUM(othernumber) FROM TEST GROUP BY number with rollup;
1, "One", 3
2, "Two", 2
3, "Three", 3
NULL, "Three", 8"

My concern is where did the "Three" come from, it takes the last element of Text (I ran into this issue with a much bigger dataset, it is the last element of Text, not the MAX or Min element.)

How to repeat:
create table test (number int, Text varchar(10), othernumber int);

insert into test values (1, "one", 1), (1, 'one', 2), (2, 'two', 2), (3, 'three', 3);

select number, Text, sum(othernumber) from test group by number with rollup;

[25 Apr 2010 17:29] Valeriy Kravchuk

This result is easily repeatable. But as you do NOT use Text column in the GROUP BY clause while different rows in the group has different values in this column, MySQL is free to return any one of them, like last one in your case. Read http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html.

I think this is not a bug.