MySQL 5.5.32, Oracle 11g
MySQL returns strange results for queries involving mod()
with noninteger moduli. Why is this?
I have a column defined thus:
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| col | double(15,6) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
I have selected a known value from that column to use for testing – 5850. If I query the table, testing that column for whole numbers, I get these results:
mysql> SELECT thing_id,name FROM table WHERE col=5850 AND mod(col,1) != 0;
Empty set (1.07 sec)
This is expected. But this is not:
mysql> SELECT thing_id,name FROM table WHERE col=5850 AND mod(col,.1) != 0;
+-----------+-----------+
| thing_id | name |
+-----------+-----------+
| 4444444 | some |
| 4444445 | names |
...
| 55555555 | go |
| 55555556 | here |
+-----------+-----------+
416 rows in set (1.07 sec)
The same results from substituting col MOD .1
and col % .1
so it has to do with the computation not the semantics.
Compare this with the behavior of Oracle (using SQL Developer if that matters), where the type of col
is NUMBER
:
SELECT thing_id,name FROM table WHERE col=5850 AND mod(col,.1) != 0;
thing_id name
[NO RECORDS]
The precision on the MySQL column seems to make this difference, but I don't understand how. Conceptually, shouldn't integer_value % .1 = 0
?
Best Answer
I will start with a link: What Every Programmer Should Know About Floating-Point Arithmetic.
In short, float-point arithmetic types, like the
float
anddouble
mysql types should never be used for precise arithmetic. And yourcol mod 0.1
is trying to do exactly that, a precise arithmetic check. It is trying to find if the value incol
is an exact multiple of0.1
. And it fails miserably, as it is obvious from your results.The solution is to use a fixed type, like
decimal(m, n)
to do these types of checks.As for why you got different results in Oracle, it's because you used
NUMBER
which is implemented differently (with decimal precision). The equivalents in Oracle of float and double areBINARY_FLOAT
andBINARY_DOUBLE
and use binary precision. See the notes in Oracle docs: Overview of Numeric Datatypes:If you insist to use
double
(I see no reason why), a few more tests with themod
operator/function in mysql, reveals that:When you use integers for the arguments, say you do
x mod K
, the result is always an integer between0
andK-1
. The expected mathematical behaviour.When you use floats/doubles, say you do
x mod K
, the result is any number from0.0
toK
included. (as your tests which show5850.0 mod 0.1
to result in0.1
). I haven't done millions of tests, so there may be cases where the result can even be larger thanK
! Floating-point arithmetic can give weird results.So, a workaround (which may not always work!) would be to use this condition:
To be honest, I feel sad I even made the workaround suggestion. Please read again the What Every Programmer Should Know About Floating-Point Arithmetic and don't use the workaround.
Use fixed-precision types for precision arithmetic operations.