Bug #11462 Problem with ROUND() function
Submitted: 20 Jun 2005 16:13 Modified: 23 Jun 2005 8:12
Reporter: Josep Maria Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.12 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[20 Jun 2005 16:13] Josep Maria 
Description:
Function ROUND does not work fine 

We have a table with three fields  CODIGO CHAR(5), CAMPO1 DECIMAL(20,4),    CAMPO2 DECIMAL(20,4)

We execute the insert command:

INSERT INTO tabla VALUES("01",1490.2300,1490.2300)
INSERT INTO tabla VALUES("01",1390.1100,1390.1100)
INSERT INTO tabla VALUES("01",1290.2200,1290.2200)

If we execute the query:

SELECT SUM(CAMPO1-CAMPO2) AS SALDO FROM tabla GROUP BY CODIGO HAVING SALDO <>0

It return 1 row with the result:

0.0

It's not the good result, it should be no row result, because SUM(CAMPO1-CAMPO2) we think is equal to 0

IF we change the query by:

SELECT ROUND(SUM(CAMPO1),15), ROUND(SUM(CAMPO2),15),ROUND(SUM(CAMPO1-CAMPO2),15) AS SALDO FROM tabla GROUP BY CODIGO HAVING SALDO <> 0

whe obtain the next result:

4170.56,4170.56, 0.0000000000534534

Why 0.0000000000534534 ... if the first column is 4170.56 and the second column is 4170.56 too ?

How to repeat:
Sorry by my english
[23 Jun 2005 8:12] MySQL Verification Team 
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please, read about floating-point comparison at:
http://dev.mysql.com/doc/mysql/en/problems-with-float.html