Bug #61867 SUM and ROUND unexpected behaviour

• Submitted: 14 Jul 2011 14:35
• Modified: 14 Jul 2011 17:47
• Reporter: Jan Vernieuwe
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.1.55
• OS: FreeBSD
• CPU Architecture: Any
• Tags: ROUND SUM

[14 Jul 2011 14:35] Jan Vernieuwe

Description:
In the documentation it states that ROUND on half a value round up.

When performing a SUM operation on a table, the ROUND function does not behave as expected.

In the example below i expect the value to be 4.94 not 4.93.
I even pre-rounded the value to 3 point precision (which gives exactly 4.935), but this did not help either.

How to repeat:
CREATE TABLE `invoice_item` (
	`invoiceitemid` int(11) NOT NULL AUTO_INCREMENT,
	`invoiceid` int(11) DEFAULT NULL,
	`quantity` int(11) DEFAULT NULL,
	`amount` float(11,2) NOT NULL,
	`vat` float(11,2) NOT NULL COMMENT '0-1',
	PRIMARY KEY `pk_invoice_item_invoiceitemid` (`invoiceitemid`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO `invoice_item`
(`invoiceid`,`quantity`,`amount`,`vat`)
VALUES
(8,1,9.80,0.21),
(8,1,13.70,0.21);

mysql> SELECT invoiceitemid, ROUND(SUM(quantity*amount*vat),2) FROM invoice_item2 WHERE invoiceid = 8;
+---------------+-----------------------------------+
| invoiceitemid | ROUND(SUM(quantity*amount*vat),2) |
+---------------+-----------------------------------+
|             1 |                              4.93 |
+---------------+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUM(ROUND(quantity*amount*vat,3)) FROM invoice_item2 WHERE invoiceid = 8;
+-----------------------------------+
| SUM(ROUND(quantity*amount*vat,3)) |
+-----------------------------------+
|                             4.935 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(SUM(ROUND(quantity*amount*vat,3)),2) FROM invoice_item2 WHERE invoiceid = 8;
+--------------------------------------------+
| ROUND(SUM(ROUND(quantity*amount*vat,3)),2) |
+--------------------------------------------+
|                                       4.93 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(4.935,2);
+----------------+
| ROUND(4.935,2) |
+----------------+
|           4.94 |
+----------------+
1 row in set (0.00 sec)

[14 Jul 2011 15:06] MySQL Verification Team

Could you please read the Manual about float:

http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html

Thanks.

[14 Jul 2011 16:25] Jan Vernieuwe

okay, i found my workaround then, but it seems just weird to me that a pre-rounded float still causes the expected issues (the result was exactly 3.935).

But i guess thats the internal handling then.

workaround:

mysql> SELECT ROUND(SUM(CAST(quantity*amount*vat AS CHAR)),2) FROM invoice_item WHERE invoiceid = 8;
+-------------------------------------------------+
| ROUND(SUM(CAST(quantity*amount*vat AS CHAR)),2) |
+-------------------------------------------------+
|                                            4.94 | 
+-------------------------------------------------+
1 row in set (0.00 sec)

[14 Jul 2011 17:47] Sveta Smirnova

Thank you for the feedback.

Closing as "Not a Bug"