Bug #9888 incorrect results with division

• Submitted: 14 Apr 2005 6:13
• Modified: 14 Apr 2005 14:01
• Reporter: Joseph Adler
• Status: Can't repeat
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.03-beta-max
• OS: MacOS (Macintosh OS X, Windows XP)
• CPU Architecture: Any

[14 Apr 2005 6:13] Joseph Adler

Description:
MySQL Server is not correctly performing division operations. Here is a simple example:

mysql> select 1.1/1.2;
+---------+
| 1.1/1.2 |
+---------+
| 0       |
+---------+
1 row in set (0.00 sec)

mysql> select 1.2/1.1;
+-----------+
| 1.2/1.1   |
+-----------+
| 1.0909091 |
+-----------+
1 row in set (0.00 sec)

How to repeat:
These are the exact calculations I was doing when I found this problem. Notice that the second example is the reciprocal of the first expression (the numerator and denominator are reversed).

mysql> select ((362/712) + (381/749)) / ((373/749) + (447/710));
+---------------------------------------------------+
| ((362/712) + (381/749)) / ((373/749) + (447/710)) |
+---------------------------------------------------+
| 0                                                 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select ((373/749) + (447/710)) / ((362/712) + (381/749));
+---------------------------------------------------+
| ((373/749) + (447/710)) / ((362/712) + (381/749)) |
+---------------------------------------------------+
| 1.108611763733156                                 |
+---------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
There appears to be some type of problem in the way that MySQL is dealing with determining the correct precision for returning a number. Notice that for operations where the result is less than 1, zero is returned, but for operations greater than 1, the value is returned.

Notice, for whatever reason, that this

mysql> select 1.1 * 10 /1.2 / 10; 
+-----------------------+
| 1.1 * 10 /1.2 / 10    |
+-----------------------+
| 0.9166666666666666666 |
+-----------------------+
1 row in set (0.00 sec)

Does return the correct answer

[14 Apr 2005 14:01] Jorge del Conde

Hi!

I was unable to reproduce this bug using 5.0.4 from bk:

mysql> select 1.1/1.2;
+-----------+
| 1.1/1.2   |
+-----------+
| 0.9166667 |
+-----------+
1 row in set (0.02 sec)

mysql> select 1.2/1.1;
+-----------+
| 1.2/1.1   |
+-----------+
| 1.0909091 |
+-----------+
1 row in set (0.00 sec)

mysql> select ((362/712) + (381/749)) / ((373/749) + (447/710));
+---------------------------------------------------+
| ((362/712) + (381/749)) / ((373/749) + (447/710)) |
+---------------------------------------------------+
| 0.902029035514277                                 |
+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> select ((373/749) + (447/710)) / ((362/712) + (381/749));
+---------------------------------------------------+
| ((373/749) + (447/710)) / ((362/712) + (381/749)) |
+---------------------------------------------------+
| 1.108611763733156                                 |
+---------------------------------------------------+
1 row in set (0.00 sec)

[14 Apr 2005 14:31] Joseph Adler

OK, glad that this was fixed in 5.04. (I looked for other division related bugs and they seemed a little different... I guess there was a common cause to these problems.)