MySQL Bugs: #56801: Rounding error when insert decimal value

Bug #56801	Rounding error when insert decimal value
Submitted:	15 Sep 2010 17:29	Modified:	15 Sep 2010 18:49
Reporter:	Filipe Martins	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.38-community and probably ohters	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello.
I was trying to figure out what the hell the "length" parameter means in numerical fields definition and I was surprised when I noticed a strange (IMHO) MySQL behavior.
If inserting a value with more decimals then allowed on the field definition, MySQL rounds (not truncates) to the right number of decimal places. That's all fine but it's rounding 5 down. For example, it rounds 101.125 to 101.12.

How to repeat:
Just run this SQL and look at the results.

USE test;
DELIMITER ;;
-- Set up test table.
DROP TABLE IF EXISTS xxx;
CREATE TABLE xxx (
	    f    FLOAT(5, 2),
	    i    INT(2)
	);

-- Insert non-truncating values.
INSERT INTO xxx (f, i)
 	VALUES (101.12, 99);
SELECT * FROM xxx;

-- Insert truncating values: MySQL rounds it up and I don't disagree.
DELETE FROM xxx;
INSERT INTO xxx (f, i)
	VALUES (101.1251, 65536*16384);
SELECT * FROM xxx;

-- Insert truncating values: MySQL rounds down but shouldn't.
DELETE FROM xxx;
INSERT INTO xxx (f, i)
	VALUES (101.125, 65536*16384);
SELECT *, ROUND(101.125, 2) FROM xxx;

;;

I think it should always round to "101.13" but it rounds "101.125" to "101.12", (though it rounds "101.1251" to "101.13").

Suggested fix:
Follow the norm and round up when last decimals are 5 or more.

The rounding behavior for floating-point values is platform dependent.

See example 3 in http://dev.mysql.com/doc/refman/5.1/en/precision-math-examples.html

For "round half up", use a DECIMAL column instead.

Ok, I didn't knew that. It's documented, so it is acceptable.
But is this wise? What's to gain in adding this uncertainty?
MySQL runs on a wide range of systems so I don't see how this kind of fuzziness helps. Is there a good reason for this? Please share...

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

We can not explain why MySQL works in particular way here too.