MySQL Bugs: #55307: MOD function returns imprecise values for fractional divisors of DOUBLE fields

Bug #55307	MOD function returns imprecise values for fractional divisors of DOUBLE fields
Submitted:	16 Jul 2010 2:17	Modified:	16 Aug 2010 4:25
Reporter:	Richard Dale	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.47	OS:	Linux (x64)
Assigned to:		CPU Architecture:	Any

Description:
Small fractional divisors used in the MOD function on DOUBLEs return imprecise results.

How to repeat:
Consider:

SELECT MOD(2,0.025);
+--------------+
| MOD(2,0.025) |
+--------------+
|        0.000 |
+--------------+

(as expected)

Then put these values into a table instead (along with a few more values):
CREATE TABLE `prices` (
  `price` double,
   `divisor` double );

INSERT INTO `test`.`prices` (
`price` ,
`divisor`
)
VALUES 
(2, 1),
(2, 0.5),
(2, 0.25),
(2, 0.125),
(2, 0.1),
(2, 0.05),
(2, 0.025),
(0.6881,0.0001),
(0.6718,0.0001);

mysql> SELECT prices.*,MOD(price,divisor) FROM prices;
+--------+---------+----------------------+
| price  | divisor | MOD(price,divisor)   |
+--------+---------+----------------------+
|      2 |       1 |                    0 |
|      2 |     0.5 |                    0 |
|      2 |    0.25 |                    0 |
|      2 |   0.125 |                    0 |
|      2 |     0.1 |   0.0999999999999999 |
|      2 |    0.05 |   0.0499999999999999 |
|      2 |   0.025 |   0.0249999999999999 |
| 0.6881 |  0.0001 | 1.19668814788088e-17 |
| 0.6718 |  0.0001 | 9.99999999999206e-05 |
+--------+---------+----------------------+

Workaround:
mysql> SELECT prices.*,MOD(1000000*price,1000000*divisor) FROM prices;
+--------+---------+------------------------------------+
| price  | divisor | MOD(1000000*price,1000000*divisor) |
+--------+---------+------------------------------------+
|      2 |       1 |                                  0 |
|      2 |     0.5 |                                  0 |
|      2 |    0.25 |                                  0 |
|      2 |   0.125 |                                  0 |
|      2 |     0.1 |                                  0 |
|      2 |    0.05 |                                  0 |
|      2 |   0.025 |                                  0 |
| 0.6881 |  0.0001 |                                  0 |
| 0.6718 |  0.0001 |                                  0 |
+--------+---------+------------------------------------+

I have more simple test case:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT MOD(2,0.025);
+--------------+
| MOD(2,0.025) |
+--------------+
|        0.000 |
+--------------+
1 row in set (0.44 sec)

mysql> SELECT MOD('2','0.025');
+--------------------+
| MOD('2','0.025')   |
+--------------------+
| 0.0249999999999999 |
+--------------------+
1 row in set (0.58 sec)

But this is because of impresize natura of operations with float/double values. I think http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html warns enough about potential problems.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".