MySQL Bugs: #8482: Precision math: Rounding of post-decimal digits yields incorrect value

Bug #8482	Precision math: Rounding of post-decimal digits yields incorrect value
Submitted:	12 Feb 2005 21:12	Modified:	9 Jun 2005 8:57
Reporter:	Trudy Pelzer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SuSE 9.1)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
A DECIMAL or NUMERIC column that is the target of a value
which has more post-decimal digits than the data type
definition can accept the value: MySQL rounds the value 
to have the correct scale. But the rounding is not always
producing the correct result. The problem appears when
the rounded value is too big for the defined precision.

How to repeat:
mysql> set sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (col1 decimal(5,2), col2 numeric(5,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (999.999,999.999);
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> insert into t1 values (-999.999,-999.999);
Query OK, 1 row affected, 2 warnings (0.00 sec)
-- This appears to be the correct response, since the server 
is allowed to round a value with a too-large scale.

mysql> select * from t1;
+-------+-------+
| col1  | col2  |
+-------+-------+
| 0.00  | 0.00  |
| -0.00 | -0.00 |
+-------+-------+
2 rows in set (0.00 sec)
-- However, the result is incorrect. The problem appears to be that,
since the rounded value (999.999 would normally be rounded to
1000.00) is too big to fit in a DECIMAL/NUMERIC(5,2) column, it gets
changed to zero instead. 

The following shows that the same problem exists even when
sql_mode is not 'traditional'.

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (col1 decimal(5,2), col2 numeric(5,2));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values (999.999,999.999);
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> insert into t2 values (-999.999,-999.999);
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> select * from t2;
+-------+-------+
| col1  | col2  |
+-------+-------+
| 0.00  | 0.00  |
| -0.00 | -0.00 |
+-------+-------+
2 rows in set (0.00 sec)

Suggested fix:
There are two options:

1)  Refuse to accept the INSERT, because the rounded value 
is out of range for the data type definition; i.e. return 
SQLSTATE 22003 numeric value out of range

2) When the rounded value is at the maximum size of the
data type definition, truncate the source value rather than
rounding it.

I suggest the first option.

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25800

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html