Bug #1456 cast with union and group by with rollup displays incorrect results

• Submitted: 1 Oct 2003 8:23
• Modified: 11 Dec 2003 12:03
• Reporter: Georg Richter
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.1
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[1 Oct 2003 8:23] Georg Richter

Description:
cast in union with group by with rollup displays wrong results

How to repeat:
mysql> create table a (year int, profit int);
Query OK, 0 rows affected (0.45 sec)

mysql> insert into a values (2000,100),(2001,200),(2003,400),(2002,500);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select year, sum(profit)*1.16 from a group by year  union all select year, cast(sum(profit) as signed) from a group by year;
+-------------+---------------------+
| year        | sum(profit)*1.16    |
+-------------+---------------------+
|        2000 |              116.00 |
|        2001 |              232.00 |
|        2002 |              580.00 |
|        2003 |              464.00 |
|        2000 |              100.00 |
|        2001 |              200.00 |
|        2002 |              500.00 |
|        2003 |              400.00 |
+-------------+---------------------+
8 rows in set (0.01 sec)

mysql> select year, sum(profit)*1.16 from a group by year  union all select year, cast(sum(profit) as signed) from a group by year with rollup;
+-------------+---------------------+
| year        | sum(profit)*1.16    |
+-------------+---------------------+
|        2000 |              116.00 |
|        2001 |              232.00 |
|        2002 |              580.00 |
|        2003 |              464.00 |
|        2000 |              100.00 |
|        2001 |                0.00 |
|        2002 |                0.00 |
|        2003 |                0.00 |
|        NULL |             1200.00 |
+-------------+---------------------+

[11 Dec 2003 9:36] MySQL Verification Team

Worked just fine for me with latest 4.1.2:

mysql> select year, sum(profit)*1.16 from t1 group by year  union all select year, cast(sum(profit) as signed) from t1 group by year with rollup;
+------+------------------+
| year | sum(profit)*1.16 |
+------+------------------+
| 2000 |           116.00 |
| 2001 |           232.00 |
| 2002 |           580.00 |
| 2003 |           464.00 |
| 2000 |           100.00 |
| 2001 |           200.00 |
| 2002 |           500.00 |
| 2003 |           400.00 |
| NULL |          1200.00 |
+------+------------------+
9 rows in set (0.01 sec)

[11 Dec 2003 12:03] MySQL Verification Team

Fixed in 4.1.2.