| 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 | ||
| Category: | Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.3-alpha-debug | OS: | Linux (SuSE 9.1) |
| Assigned to: | Alexey Botchkov | Target Version: | |
[10 Feb 2005 23:32]
Miguel Solorzano
Thank you for the bug report.
[10 Feb 2005 23:41]
Trudy Pelzer
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.
[2 Apr 2005 11:32]
Alexey Botchkov
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
[8 Jun 2005 8:23]
Bugs System
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
[8 Jun 2005 9:31]
Alexey Botchkov
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
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