MySQL Bugs: #63897: round function problem in update query

Bug #63897	round function problem in update query
Submitted:	2 Jan 2012 16:17	Modified:	14 Jan 2012 10:01
Reporter:	Bikram Choudhury	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.56	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	round function problem in update query

Description:
I have created a TABLE salary with field type DOUBLE.

Say that field name "basic_pay" and its value is USD 26930 ( Numeric)

Say in that table you have another field called DA (Dearness Allowances) which is 45% of basic_pay, DA field type DOUBLE.

I want to update it by query 

update salary set da = round(basic_pay * 45/100,0) ;

You will see rounded da is 12118 in database.

But actual round should be 12119.

Thanks

How to repeat:
create a table and follow the same mentioned above.

Suggested fix:
Fixing round is highly necessary.

What exact server version, 5.x.y, do you use? Look:

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select round(26930*45/100.0);
+-----------------------+
| round(26930*45/100.0) |
+-----------------------+
|                 12119 |
+-----------------------+
1 row in set (0.00 sec)

MySQL version : 5.1.56-log

I have checked using select statement and it works good as it ought to be- SELECT ROUND(26930*45/100,0); etc which you have did.

But it fails if you update records using column name

I have attached whole MySQL table in SQL format, size 1.3 MB. You can try all here on this MySQL table.

I have attached a SQL file called salary.sql

You will get basic_pay , da etc fields with data. 

Apply the following query :
update salary_january_2012 set da = round(basic_pay * 45/100,0);

or 

update salary_january_2012 set da = round(basic_pay * 45/100,0);
You will see the row with teacher_id = 0100111001 having basic_pay 26930 DA will be 12118.

it ought to be 12119.
Email me : webseos@gmail.com

I am in trouble with update query after rounding and assigning new value.
How to recover ?

Any Idea?

I have made a mistake, in the SQL file attached :
The table name is SALARY. The Query will be as follows.

update salary set da = round(basic_pay * 45/100,0);

According to http://dev.mysql.com/doc/refman/5.1/en/mathematical-functions.html#function_round this is what you would expect. In particular, the example given is illustrative:

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+

This is the case where where the value to be rounded is 12118.5

That's how binary floats work, get over it ...

If you want do deal with monetary values and want to prevent rounding errors then use DECIMAL as column type instead of FLOAT/DOUBLE ...

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 Philip's and Hartmut's comments.