Bug #25619 Rounding error in SUM on column type FLOAT

• Submitted: 15 Jan 2007 5:42
• Modified: 15 Jan 2007 9:52
• Reporter: Peter Tebault
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S1 (Critical)
• Version: 5.0.27-community
• OS: Windows (XP Pro)
• CPU Architecture: Any

[15 Jan 2007 5:42] Peter Tebault

Description:
Mysql: 5.0.27-community 
Platform: Windows XP (win32)
MySQL charset:  UTF-8 Unicode (utf8)
MySQL connection collation: utf8_unicode_ci

SUM on column type float with one row/value returns (not that value).

How to repeat:
CREATE TABLE `a` (
  `a` FLOAT DEFAULT 0
);

insert into `a` values (1.4);

select sum(a) from a;

result should be 1.4, is 1.3999999761581

Suggested fix:
Make it return (that value)

[15 Jan 2007 9:52] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about Problems with Floating-Point Comparisons at http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html Also read http://dev.mysql.com/doc/refman/5.0/en/no-matching-rows.html

[13 Mar 2008 15:55] Dave Schlaegel

If what you are trying to do is have a 2 char float as the result just use something like:

select round(sum(a),2) from a;

----------------------------
Thanks, 
Dave Schlaegel