MySQL Bugs: #8425: Precision math: division by column value has insufficient post-decimal places

Bug #8425	Precision math: division by column value has insufficient post-decimal places
Submitted:	10 Feb 2005 22:27	Modified:	8 Jun 2005 9:31
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:
According to our agreement with SAP, MySQL
will handle rounding the same way Oracle does.
This affects the precision and number of calculated 
decimal places returned when math operations
take place.

Currently, if I divide a DECIMAL(30,25) column value
by a large integer, the correct result is returned. But
when I reverse the operands, the result is not calculated
to at least 15 post-decimal digits, as is required.

How to repeat:
mysql> create table t (col1 int, col2 decimal(30,25), col3 numeric(30,25));
mysql> insert into t values (1,0.0123456789012345678912345,0.0123456789012345678912345);

mysql> select col2/9999999999 from t where col1=1;
+------------------------------------------------------------------------------------+
| col2/9999999999                                                                                               |
+------------------------------------------------------------------------------------+
| 0.0000000000012345678902469135781481410000000000000000000 |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- This is the correct result (although I would prefer that we
cut the result off after 15 post-decimal places): Oracle returns  
0.000000000001235

mysql> select 9999999999/col2 from t where col1=1;
+--------------------------+
| 9999999999/col2        |
+--------------------------+
| 810000007209.00007 |
+--------------------------+
1 row in set (0.00 sec)
-- This result gets rounded too soon. As with the first example, 
the result should contain at least 15 post-decimal places: Oracle 
returns  810000007209.000065537105051

Thank you for the bug report.

Here's another example of the same problem, that shows
it is not limited just to column values.

mysql> select 77777777/7777777;
+------------------+
| 77777777/7777777 |
+------------------+
| 10.00000         |
+------------------+
1 row in set (0.01 sec)
-- This is the incorrect result. The correct result
is 10.00000090000009. The rounding is occurring
too soon.

This one is going to be just partially fixed in 5.0 as Oracle supports 38 digits after the decimal point which we can't handle until 5.1

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/25746

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