Recently a PL/SQL blog post was illustrating the difference between MOD() and REMAINDER() and the results from REMAINDER looked … suspicious. The example, modified here to provide some context in the output, shows the ‘problem’ that some remainders are negative. The mathematical definition of remainder states:
In mathematics, the remainder is the amount "left over" after performing some computation. In arithmetic, the remainder
is the integer "left over" after dividing one integer by another to produce an integer quotient (integer division).
This definition assumes that the divisor does not evenly divide the dividend and that the integer portion of the division, multiplied by the divisor, results in a product less than the dividend. For 15 divided by 2 the remainder is 1, since 15 divided by 2 is 7.5, making the integer quotient 7, leaving 1 remaining. Since remainders should
SQL>
SQL> --
SQL> -- REMAINDER doesn't return the correct remainder for
SQL> -- divisors that are powers of 2
SQL> --
SQL> -- The following example returns correct remainders
SQL> -- for all listed divisors of 15 except the
SQL> -- powers of 2, where the formula generates 16 as the
SQL> -- (n*X) value, thus displaying a negative remainder:
SQL> --
SQL> BEGIN
2 DBMS_OUTPUT.put_line ('MOD(15,2):'||MOD (15, 2));
3 DBMS_OUTPUT.put_line ('REMAINDER(15,2):'||REMAINDER (15, 2));
4 DBMS_OUTPUT.put_line ('MOD(15,3):'||MOD (15, 3));
5 DBMS_OUTPUT.put_line ('REMAINDER(15,3):'||REMAINDER (15, 3));
6 DBMS_OUTPUT.put_line ('MOD(15,4):'||MOD (15, 4));
7 DBMS_OUTPUT.put_line ('REMAINDER(15,4):'||REMAINDER (15, 4));
8 DBMS_OUTPUT.put_line ('MOD(15,5):'||MOD (15, 5));
9 DBMS_OUTPUT.put_line ('REMAINDER(15,5):'||REMAINDER (15, 5));
10 DBMS_OUTPUT.put_line ('MOD(15,6):'||MOD (15, 6));
11 DBMS_OUTPUT.put_line ('REMAINDER(15,6):'||REMAINDER (15, 6));
12 DBMS_OUTPUT.put_line ('MOD(15,7):'||MOD (15, 7));
13 DBMS_OUTPUT.put_line ('REMAINDER(15,7):'||REMAINDER (15, 7));
14 DBMS_OUTPUT.put_line ('MOD(15,8):'||MOD (15, 8));
15 DBMS_OUTPUT.put_line ('REMAINDER(15,8):'||REMAINDER (15, 8));
16 END;
17 /
MOD(15,2):1
REMAINDER(15,2):-1
MOD(15,3):0
REMAINDER(15,3):0
MOD(15,4):3
REMAINDER(15,4):-1
MOD(15,5):0
REMAINDER(15,5):0
MOD(15,6):3
REMAINDER(15,6):3
MOD(15,7):1
REMAINDER(15,7):1
MOD(15,8):7
REMAINDER(15,8):-1
PL/SQL procedure successfully completed.
To understand why -1 remainders are generated it is necessary to know how Oracle computes the remainder values. The following calculation does the deed:
R = m - (n*X)
where R is the remainder, m is the dividend, n is the divisor and X is an integer where n*X should be <= m
The problem with Oracle’s implementation is that n*X is sometimes greater than m. When that condition occurs the calculated remainder is negative. Oracle actually calculates X by performing ROUND(m/n,0), which in the example shown above results in (n*X) equaling 16, not some lesser number. In these cases (all in this example are associated with powers of 2) the remainder is reported as -1, and by the definition of remainder that result is, well, impossible. One possibility (outside of Oracle actually including a check of (n*X) being less than or equal to m or changing production code to use MOD() instead) is to create a function that DOES perform that check; that code is shown below:
SQL>
SQL> --
SQL> -- Create a function to return the correct remainder - does not
SQL> -- generate values greater than the supplied target so the remainders
SQL> -- are not negative
SQL> --
SQL> create or replace function remainder_func(p_num1 in number, p_num2 in number)
2 return number is
3 v_rmdr number;
4 v_x number:=1;
5 begin
6 v_x := trunc(p_num1/p_num2, 0);
7 v_rmdr := p_num1 - (v_x * p_num2);
8 return(v_rmdr);
9 end;
10 /
Function created.
SQL>
Using the new function in place of the Oracle-supplied REMAINDER provides correct results; notice the new function uses TRUNC() in place of ROUND():
SQL> BEGIN
2 DBMS_OUTPUT.put_line ('MOD(15,2):'||MOD (15, 2));
3 DBMS_OUTPUT.put_line ('REMAINDER_func(15,2):'||REMAINDER_func (15, 2));
4 DBMS_OUTPUT.put_line ('MOD(15,3):'||MOD (15, 3));
5 DBMS_OUTPUT.put_line ('REMAINDER_func(15,3):'||REMAINDER_func (15, 3));
6 DBMS_OUTPUT.put_line ('MOD(15,4):'||MOD (15, 4));
7 DBMS_OUTPUT.put_line ('REMAINDER_func(15,4):'||REMAINDER_func (15, 4));
8 DBMS_OUTPUT.put_line ('MOD(15,5):'||MOD (15, 5));
9 DBMS_OUTPUT.put_line ('REMAINDER_func(15,5):'||REMAINDER_func (15, 5));
10 DBMS_OUTPUT.put_line ('MOD(15,6):'||MOD (15, 6));
11 DBMS_OUTPUT.put_line ('REMAINDER_func(15,6):'||REMAINDER_func (15, 6));
12 DBMS_OUTPUT.put_line ('MOD(15,7):'||MOD (15, 7));
13 DBMS_OUTPUT.put_line ('REMAINDER_func(15,7):'||REMAINDER_func (15, 7));
14 DBMS_OUTPUT.put_line ('MOD(15,8):'||MOD (15, 8));
15 DBMS_OUTPUT.put_line ('REMAINDER_func(15,8):'||REMAINDER_func (15, 8));
16 END;
17 /
MOD(15,2):1
REMAINDER_func(15,2):1
MOD(15,3):0
REMAINDER_func(15,3):0
MOD(15,4):3
REMAINDER_func(15,4):3
MOD(15,5):0
REMAINDER_func(15,5):0
MOD(15,6):3
REMAINDER_func(15,6):3
MOD(15,7):1
REMAINDER_func(15,7):1
MOD(15,8):7
REMAINDER_func(15,8):7
PL/SQL procedure successfully completed.
Such a function could be created in an application schema with a synonym calling it REMAINDER so that existing code will generate correct results. Going one step further it’s a good idea to compare the output of ROUND() and TRUNC() with the ‘suspect’ data to see why ROUND() generates the incorrect results:
SQL>
SQL> --
SQL> -- ROUND() doesn't work for this example as it
SQL> -- generates values that are powers of 2
SQL> --
SQL>
SQL> create or replace procedure remainder_test(p_num1 in number, p_num2 in number)
2 is
3 v_t_rmdr number;
4 v_r_rmdr number;
5 v_tx number:=1;
6 v_rx number:=1;
7 begin
8 dbms_output.put_line('=====================================================================');
9 v_tx := trunc(p_num1/p_num2, 0);
10 v_rx := round(p_num1/p_num2, 0);
11 v_t_rmdr := p_num1 - (v_tx * p_num2);
12 v_r_rmdr := p_num1 - (v_rx * p_num2);
13 dbms_output.put_line('Rounded: '||v_rx||' (n*X): '||v_rx*p_num2||' Remainder: '||v_r_rmdr);
14 dbms_output.put_line('Truncated: '||v_tx||' (n*X): '||v_tx*p_num2||' Remainder: '||v_t_rmdr);
15 dbms_output.put_line('=====================================================================');
16 end;
17 /
Procedure created.
SQL>
SQL> BEGIN
2 REMAINDER_test (15, 2);
3 REMAINDER_test (15, 3);
4 REMAINDER_test (15, 4);
5 REMAINDER_test (15, 5);
6 REMAINDER_test (15, 6);
7 REMAINDER_test (15, 7);
8 REMAINDER_test (15, 8);
9 END;
10 /
=====================================================================
Rounded: 8 (n*X): 16 Remainder: -1
Truncated: 7 (n*X): 14 Remainder: 1
=====================================================================
=====================================================================
Rounded: 5 (n*X): 15 Remainder: 0
Truncated: 5 (n*X): 15 Remainder: 0
=====================================================================
=====================================================================
Rounded: 4 (n*X): 16 Remainder: -1
Truncated: 3 (n*X): 12 Remainder: 3
=====================================================================
=====================================================================
Rounded: 3 (n*X): 15 Remainder: 0
Truncated: 3 (n*X): 15 Remainder: 0
=====================================================================
=====================================================================
Rounded: 3 (n*X): 18 Remainder: -3
Truncated: 2 (n*X): 12 Remainder: 3
=====================================================================
=====================================================================
Rounded: 2 (n*X): 14 Remainder: 1
Truncated: 2 (n*X): 14 Remainder: 1
=====================================================================
=====================================================================
Rounded: 2 (n*X): 16 Remainder: -1
Truncated: 1 (n*X): 8 Remainder: 7
=====================================================================
PL/SQL procedure successfully completed.
SQL>
Notice that the examples processed by ROUND() bring X up to a number greater than the integer portion of the division of m by n (in this example the fractional part of the division result is 1/2 which always rounds to the next highest number), and in this case results in n*X returning 16 (as mentioned earlier in this article). This is something that should have been caught in testing, in my opinion. This example was posted on Twitter and generated several comments because of the negative remainder. It also generated some discussion on whether ROUND() has a bug (it doesn’t); the issue is using ROUND() to generate the integer quotient rather than TRUNC().
An interesting point to consider is that MOD() uses FLOOR(), which rounds results down to the largest integer not greater than the supplied expression, to generate its integer quotient values, which is why none of the MOD() examples returned negative results.
The known problem with testing code is that not every value can be tested, and efforts are made to ensure that the most unique situations return correct results. In this case such testing left a simple condition that produces incorrect output. Hopefully Oracle will take note of the issue and attempt to address it in the next minor release. Until then you may want to use the function code supplied here to mitigate that situation (since it may not be possible to change production code to use MOD()), or carefully check REMAINDER() results for accuracy.
