Bug #47355 Cannot compute sum of values with 1 or 2 decimal place if type is Float.

• Submitted: 16 Sep 2009 8:53
• Modified: 16 Sep 2009 9:19
• Reporter: Reuben S. Valaydon Pillay
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: mySQL server 4.1
• OS: Windows (SP2)
• CPU Architecture: Any
• Tags: FLOAT, sum

[16 Sep 2009 8:53] Reuben S. Valaydon Pillay

Description:
Hello,

An incorrect result is produced during sums on fields with type float.

For example sum of 2.9, 2.6, 2.1, 0.4 should give 8 but it gives 7.99999910593.

Tried it with 2.88, 2.6, 2.1, 0.42 as well...

Regards, 

Reuben

How to repeat:
Launched from mySQL browser 1.2.3 beta

create table test (wl float);

insert into test values(2.1);
insert into test values(2.9);
insert into test values(2.6);
insert into test values(0.4);

SELECT * FROM test t;

SELECT sum(wl) FROM test t;

Suggested fix:
No idea, I'm changing my table to Decimal(10,2)

[16 Sep 2009 9:19] Valeriy Kravchuk

This is not a bug. Flating-point values are approximate. Check http://dev.mysql.com/doc/refman/4.1/en/problems-with-float.html.

If you'll try to check more decimal places you will see the reason for SUM() result:

mysql> select wl*1000000 from test;
+------------------+
| wl*1000000       |
+------------------+
| 2099999.90463257 |
| 2900000.09536743 |
| 2599999.90463257 |
| 400000.005960464 |
+------------------+
4 rows in set (0.03 sec)