Rounding in mysql doesn't work as expected

Question

I can't understand why my results are different:

I have table orders and column price (type double in mysql).

The price value in database is 13.5.

query:

SELECT ROUND(price * 0.09, 2) FROM orders where id = 1;

result is: 1.21

query:

SELECT ROUND(13.5 * 0.09, 2);

the result is 1.22

without rounding: SELECT 13.5 * 0.09 result is 1.215

so the correct result after round is 1.22.

Why query SELECT ROUND(price * 0.09, 2) FROM orders where id = 1; gives me wrong result (1.21)?

I can't understand what's wrong, I guess something with casting.

Could someone explain me it?

This may help you http://stackoverflow.com/questions/10446581/mysql-round-weird-bug — Pep Lainez, Oct 11 '16 at 14:28
This url can be helpful about MySQL ROUND function (http://www.w3resource.com/mysql/mathematical-functions/mysql-round-function.php) — Sumon Sarker, Oct 11 '16 at 14:31
It's highly unlikely that you would want DOUBLE for 'price'. See DECIMAL. — Strawberry, Oct 11 '16 at 15:32

score 2 · Answer 1 · answered Oct 11 '16 at 14:58

I can't comment, so I write you an answer.

Read this: http://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html

Float and Double data, don't store exact numbers, so It's possible to find weird issues.

Try to CEIL as AT-2016 told you, hope you find a solution.

score 2 · Answer 2 · answered Aug 18 '17 at 13:02

2

If datatype is float use cast SELECT round(cast(qty as decimal(10,2))*0.99,2) FROM orders WHERE 1

answered Aug 18 '17 at 13:02

Jatin

56
6

score 1 · Accepted Answer · edited May 23 '17 at 11:48

1

I the end I converted my columns to decimal 15,2. I was a bit afraid about the risk with converting, after few tests there is no difference in total sum of prices. I recommend to do it for everyone who has this problem.

Best data type to store money values in MySQL

edited May 23 '17 at 11:48

Community

1
1

answered Oct 12 '16 at 07:20

Maciej

76
9

Rounding in mysql doesn't work as expected

3 Answers3