$mod rounding to integer
Szaniszlo Szöke
Testing function mod() with MongoDB, I found that the field value is rounded to an integer.
Searching field d1 this way :
{d1: {$mod: [5, 3.5]} }
returns both records with d1 = 13.5 and d1 = 3
For example, MySql returns only d1 = 13.5
This rounding effect could be added to the documentation of the mod() function, or maybe mod() could deal with double values...
Thanks
William Byrne III
Szaniszlo,
It is not only the field values which are rounded to integers. The $mod conditional operator casts all the variables into LONGs which truncates everything to the right of the decimal point. That means when you filter with { “f1”: {$mod:[divisor, match]} } the integer part of the f1 value is divided by the integer part of the divisor value. That produces an integer remainder which is then compared to the integer part of the match value, and if they are equal the document is returned.
This is necessary because (for all versions up to 3.2) decimal values in MongoDB are stored in DOUBLEs, which are binary floating point values. Floating point arithmetic for fractional values is notoriously susceptible to rounding errors like this:
> a1 = 3.3
3.3
> a2 = 1.1 + 1.1 + 1.1
3.3000000000000003
> if (a1 == a2) {print("a1 == a2")} else {print("a1 != a2")}
a1 != a2
so trimming off the decimal places is how MongoDB avoids failed equality matches like the above.
The good news is that support for DECIMAL is currently planned for MongoDB 3.4. Depending upon your deployment, you might need a MongoDB driver that also supports DECIMAl too.
Finally, I have created DOCS-7792 to suggest that the manual entry for $mod should explain some of this.
Regards,
III
William Byrne III