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
• Status: Closed
• 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)