Oracle’s REMAINDER Function: The Result Isn’t Always Correct

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 never be negative this warranted some investigation. First, let’s show the modified example and see the results:


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.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles