Bug #8874 Precision math: SUM(x*x) fails with large decimals and grouping
Submitted: 1 Mar 2005 20:20 Modified: 15 Jun 2005 7:18
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SUSE 9.2)
Assigned to: Alexey Botchkov CPU Architecture:Any

[1 Mar 2005 20:20] Peter Gulutzan
Description:
SUM gives me a very wrong answer if:
- the argument data type is DECIMAL with many post-decimal digits
- there is a GROUP BY
- there is some additional operation, such as multiplication.

Since MySQL needs to do similar calculations for VARIANCE and STDDEV,
this error is more important than it might seem at first glance.

How to repeat:
mysql> create table t42 (s1 decimal(32,30), s2 char);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t42 values (77,null),(77,null);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select sum(s1*s1) from t42 group by s2\G
*************************** 1. row ***************************
sum(s1*s1): 999.999999999999999999999999999999999999999999999999999999999999
1 row in set (0.00 sec)