Quotient & Remainder Function Returns Incorrect Result with Floating Point Numbers

Issue Details

The Quotient & Remainder function does not seem to work as expected. The Quotient & Remainder function returns incorrect values for inputs where the representation is Double-precision (DBL), floating-point numeric value 64-bit IEEE double-precision format. 

Solution

The root cause of this issue is the general problem of numerical mathematics respecitvely computer mathematics that fractional numbers can only be represented exactly in finite binary, if their only denominator is the number 2 and multiples of it.

Therefore, the floating point data types cannot provide exact representations of all numbers. The main problem is that certain numerical values (ie. 1/10 or 1/3) cannot be accurately represented in binary with the IEEE format. For instance 1/0.1 = 9.9999999999999994, with a floor value of 9. This division result will create an inaccurate solution for the Quotient & Remainder function.  In some particular cases, using less precision actually helps cover up the inaccuracies of the calculation, but in general this is not true.

If you encounter this situation, you can use a less precise data type such as Single-precision (SGL).

One simple workaround is to use the integer data type. Integer data does not suffer from the trailing data that floating point numbers do, and will always work with a Quotient and Remainder operation. In this example, the decimal values were multiplied by a power of ten and then converted to I32 data type for conversion.

Additional Information

If you are looking for exact results when doing floating point arithmetic, then you can use the regular Divide function. If you want to achieve a rounded result, then you can use the regular Divide and then Round to Nearest functions.


The non-exact representation of fractional numbers needs alsways to be taken into account when comparing floating-point numbers.