MySQL Bugs: #1499: INSERT/UPDATE into decimal field rounding problem

Bug #1499	INSERT/UPDATE into decimal field rounding problem
Submitted:	7 Oct 2003 11:04	Modified:	30 Jan 2004 22:00
Reporter:	Rumen Telbizov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	3.23.x 4.0.x	OS:	FreeBSD (FreeBSD 4.8-STABLE)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
When inserting or updating a table with a decimal field
the inserted number is NOT rounded by the way that ROUND()
function would round it!

The manual says:
When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is rounded to that scale.

This assumes somehow that rounding similar to ROUND() is done.

The bug can only be seen if we have 5 as a number
that has to be checked when rounding. Otherwise the INSERT/UPDATE
works (i.e. rounds) just fine.

Note:
The Release and Operating system are the one that I have tested this.
I do not claim that this could be seen on other OSes/versions

How to repeat:
mysql> select ROUND(0.985, 2);
+-----------------+
| ROUND(0.985, 2) |
+-----------------+
|            0.98 |
+-----------------+

mysql> select ROUND(0.975, 2);
+-----------------+
| ROUND(0.975, 2) |
+-----------------+
|            0.98 |
+-----------------+

Ok ROUND() works perfect but ...

mysql> CREATE TABLE a (rnd_num decimal(9,2));
mysql> INSERT INTO a VALUES(0.975), (0.985);
mysql> INSERT INTO a VALUES(0.975), (0.985);

mysql> select * from a;
+---------+
| rnd_num |
+---------+
|    0.97 |
|    0.98 |
+---------+

Which shows that 0.975 is not rounded as ROUND() would do it!

Suggested fix:
I suggest that INSERT/UPDATE operators are fixed in a way
that they put the number after a ROUND() call.
Or simply change the documentation and explain that rounding
in this filed is not the same as ROUND().

I get the same result on Suse 8.2.

you are right - on INSERT values are not rounded the same way as ROUND does - we'll clarify this in the manual.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've updated the passage to say:

When such a column is assigned a value with more digits following the decimal point than are 
allowed by the specified scale, the value is converted to that scale. (The precise behavior is 
operating-system specific, but generally the effect is truncation to the allowable number of digits.)