MySQL Bugs: #19661: sum function returning invalid results

Bug #19661	sum function returning invalid results
Submitted:	10 May 2006 4:11	Modified:	10 Jun 2006 15:36
Reporter:	Rudy Wells	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	MySql 5.0.18-nt	OS:	Windows (Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
mysql> select amount from po_invoice where po_record = 227;
+---------+
| amount  |
+---------+
| 14126.2 |
|   32.72 |
+---------+
2 rows in set (0.09 sec)

mysql> select sum(amount) from po_invoice where po_record = 227;
+-----------------+
| sum(amount)     |
+-----------------+
| 14158.880157471 |
+-----------------+
1 row in set (0.09 sec)

The amount column is a float.
The answer should have been 14158.92 not 14158.88

How to repeat:

I issue the above commands in the MySql client.

Rounding errors are expected when dealing with FLOAT values
due to binary<->decimal fraction conversion, if you need full
decimal precision you should use DECIMAL columns instead.

The rounding error you get is larger than expected though,
i tried to reproduce this using simple test data but failed
(see log below), are you able to provide a dump of your
table for testing?

mysql> create table t1 (i int primary key, d float);
Query OK, 0 rows affected (0.31 sec)

mysql> insert into t1 values (1, 14126.2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (2, 32.72);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---+---------+
| i | d       |
+---+---------+
| 1 | 14126.2 |
| 2 | 32.72   |
+---+---------+
2 rows in set (0.00 sec)

mysql> select sum(d) from t1;
+-----------------+
| sum(d)          |
+-----------------+
| 14158.920196533 |
+-----------------+
1 row in set (0.00 sec)

Could you please provide the dump requested by Hartmut?

Thanks in advance.

Where should I send the table dump?

The suggestion of the decimal column would make my table much larger and slower in performance as it is a ascii string and then has to be converted.

If I bring the rows back one at a time and add them up in my application, I get the correct answer. It is sad that sum cannot as that is much more network overhead.

depending on the dump size you can either just use the "Files" tab on this bug report or upload using ftp to ftp://ftp.mysql.com/pub/mysql/upload. When using FTP please use bug19661_ as the file prefix and post a message here when the upload is done.

This is a CSV file created with MySql Query Browser

Attachment: po_invoice.csv (application/vnd.ms-excel, text), 23.50 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".