Bug #9235 Rollup of a calculated column has wrong values
Submitted: 16 Mar 2005 23:37 Modified: 31 May 2005 15:35
Reporter: Peter Gulutzan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SUSE 9.2)
Assigned to: Assigned Account CPU Architecture:Any

[16 Mar 2005 23:37] Peter Gulutzan
Description:
If I use a calculated column -- an expression containing the grouping column and an aggregate of a non-grouping column -- then the value in it is incorrect if I use ROLLUP.
Also the grand total is wrong.

How to repeat:
mysql> create table t10 (s1 int, s2 int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t10 values (1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select s1,s1+sum(s2) from t10 group by s1;
+------+------------+
| s1   | s1+sum(s2) |
+------+------------+
|    1 | 2          |
|    2 | 4          |
|    3 | 6          |
+------+------------+
3 rows in set (0.00 sec)

mysql> select s1,s1+sum(s2) from t10 group by s1 with rollup;
+------+------------+
| s1   | s1+sum(s2) |
+------+------------+
|    1 | 3          |
|    2 | 5          |
|    3 | 6          |
| NULL | 9          |
+------+------------+
4 rows in set (0.01 sec)
[31 May 2005 15:35] Igor Babaev
This bug was fixed by the patch for bug #7894 (for 4.1 and 5.0) 

Now we have:

mysql> select s1,s1+sum(s2) from t10 group by s1;
+------+------------+
| s1   | s1+sum(s2) |
+------+------------+
|    1 |          2 |
|    2 |          4 |
|    3 |          6 |
+------+------------+
3 rows in set (0.40 sec)

mysql> select s1,s1+sum(s2) from t10 group by s1 with rollup;
+------+------------+
| s1   | s1+sum(s2) |
+------+------------+
|    1 |          2 |
|    2 |          4 |
|    3 |          6 |
| NULL |       NULL |
+------+------------+
4 rows in set (0.00 sec)