Formula for the Oracle MOD Function

There’s a lot of confusion over how Oracle calculates the return value for its MOD function, and most people are surprised at the result when the dividend and/or the divisor are negative.

The Oracle function is called like this, where m is the dividend and n is the divisor:

MOD(m, n)

The Oracle documentation explains that MOD returns a remainder rather than a modulus, and if the divisor n is zero it just returns the dividend m. It then goes on to explain how the modulus isn’t calculated:

This function behaves differently from the classical mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:
m - n * FLOOR(m/n)

The same formula is repeated on techonthenet, but it’s described as how the Oracle modulus is calculated:

The MOD is calculated as:
m - n * floor(m/n)

The formula above matches the result from Oracle’s MOD function if m and n are both positive, but if either or both are negative the results nearly always differ.

After some experimentation, I came up with the correct formula:

  • For n ≠ 0:  SIGN(m) * (ABS(m) - FLOOR(ABS(m / n)) * ABS(n))
  • For n = 0:  m

As a one-liner, the formula would be one of the following:

  • COALESCE(SIGN(m) * (ABS(m) - FLOOR(ABS(m / NULLIF(n, 0))) * ABS(n)), m), or
  • DECODE(m, 0, m, SIGN(m) * (ABS(m) - FLOOR(ABS(m / n)) * ABS(n)))

I prefer the first one because I haven’t used DECODE in years. I don’t think it’s superior to the second; it’s just the one I’m more comfortable with. The NULLIF(n, 0) will return null if n is zero, which turns the entire inner calculation to null. When that happens, the COALESCE will change the null result to the value of m.

I verified the formula by writing a program to calculate about 10,000,000 modulus values, comparing its results to Oracle’s MOD result. The operands for my formula were a mixture of positive/negative and integer/decimal for both the dividend and the divisor. Every one of the 10M calculations using the formula matched the Oracle modulus.

The same 10M calculations using the m - n * FLOOR(m/n) formula differed nearly 50% of the time.

For the curious, the program used to test both formulas is here.

Leave a Reply

Your email address will not be published. Required fields are marked *