Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted June 27, 2016

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

By David Fitzjarrell

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM