Oracle Tips and Tricks — David Fitzjarrell

June 27, 2016

Examining The Remains

Filed under: General — dfitzjarrell @ 08:14

"Don't you know anything at all about numbers?"
"Well, I don't think they're very important," snapped Milo, too embarrassed to admit the truth.
"NOT IMPORTANT!" roared the Dodecahedron, turning red with fury.  "Could you have tea for two 
without the two, or three blind mice without the three? Would there be four corners of the earth 
if there weren't a four? And how would you sail the seven seas without a seven?"
"All I meant was..." began Milo, but the Dodecahedron, overcome with emotion and shouting furiously,
carried right on.
"If you had high hopes, how would you know how high they were? And did you know that narrow escapes 
come in all different widths? Would you travel the whole wide world without ever knowing how wide it
was? And how could you do anything at long last," he concluded, waving his arms over his head, "without
knowing how long the last was? Why, numbers are the most beautiful and valuable things in the world.
Just follow me and I'll show you." He turned on his heel and stalked off into the cave.
-- Norton Juster, The Phantom Tollbooth

It used to be that a database was, well, a database and it didn’t contain lots of interesting mathematical and analytic functions, just tables, data and basic string and number functions. That’s changed, mainly because of what work we now need to do and what results we need to report; enterprise level databases are both more complicated in terms of extended functionality and easier in terms of end-users generating with a single function values that were the result of complicated calculations done outside of the database. It’s a testament to Oracle that, for the most part, these functions provide correct results. Unfortunately it appears that Oracle has ‘dropped the ball’ with what may be one of the simpler supplied functions, REMAINDER().

To start this odyssey a PL/SQL post was provided to illustrate the difference between MOD() and REMAINDER() and, unfortunately, the results from REMAINDER looked rather odd. I’ve provided the example from that post, modified to provide some context in the output. The ‘problem’ is that some of the remainders are negative which flies in the face of the mathematical definition of a remainder, which is:


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).

The thought in my mind and that of every mathematician on this planet is that a remainder will never be negative. It’s what is ‘left over’ and you can’t ‘owe someone’ a remainder; besides being impossible according to the definition it’s also just not polite. For example, 15 divided by 2 has a remainder of 1, not -1. Let’s run the modified example and see what it returns:


SQL> 
SQL> --
SQL> -- REMAINDER doesn't return the correct remainder for
SQL> -- divisors of odd numbers 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.

Why, oh why, does this happen? Oracle takes a ‘shortcut’ to get there, using a formula that, as far as I can tell, isn’t checking all that it needs to check before ‘spitting out’ the answer. That formula is shown below:


					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

Notice what check is missing? For some odd reason Oracle never checks to see if the product (n*X) is less than or equal to m. Since Oracle calculates X by performing ROUND(m/n,0) fractional parts of a quotient, when they are .5 or greater, round up to the next highest integer. For the case of 15 divided by even numbers the calculated value of X results in (n*X) being equal to 16. Once that happens the remainders all end up as -1 which, by definition, is impossible.

Fiddle-dee-dee, what are we to do? One option is to use MOD() instead, since the results for MOD() don’t exhibit the same problem. Another option, if production code can’t be changed, is to create another remainder function, possibly in the application schema, with a private synonym calling it ‘REMAINDER’. One example of such a function 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> 

So, let’s see what results are returned, since this function uses TRUNC() instead 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.

Wow, golly gee whilikers, the results are correct! Of course that should be expected since there is no possibility that (n*X) will be greater than m. Let’s take a side trip and see how ROUND() and TRUNC() provide different results for odd numbers divided by even numbers, using 15 as a ‘test subject’:


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> 

There’s quite a difference between ROUND() and TRUNC(); to be fair MOD() uses FLOOR() in the calculations to generate MOD values and, as mentioned before, REMAINDER() uses ROUND(), probably in an effort to make the two functions internally different. Using a bit more code the ROUND() option could also work:


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> -- Uses ROUND() to compute values
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 := round(p_num1/p_num2, 0);
  7  	     if v_x*p_num2 > p_num1 then
  8  		     v_x := v_x -1;
  9  	     end if;
 10  	     v_rmdr := p_num1 - (v_x * p_num2);
 11  	     return(v_rmdr);
 12  end;
 13  /

Function created.

SQL> 
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.

SQL> 

but I think the first function is slightly more efficient. Additionally the second function essentially ‘re-invents’ FLOOR(), so why not simply use FLOOR() to get the job done:


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> -- Uses FLOOR() to compute values
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 := floor(p_num1/p_num2);
  7          v_rmdr := p_num1 - (v_x * p_num2);
  8          return(v_rmdr);
  9  end;
 10  /

Function created.

SQL>
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.

SQL>

FLOOR() returns the highest value that is not larger than the supplied argument (in this case 15 divided by some number), rounding down when necessary. It’s interesting that Oracle used a different formula to compute REMAINDER() values that could (and does) produce negative results.

A discussion ensued on Twitter that ROUND() contained a bug, yet nothing was further from the truth. ROUND() behaves exactly as it should; the issue is that ROUND() can generate a number that, when multiplied by the supplied divisor, is larger than the supplied dividend, producing remainders that are negative. So the ‘bug’, if you will, is in how Oracle implemented REMAINDER() [R=m-(n*X)], and that ‘bug’ can be fixed by coding the function to produce (n*X) values that are always less than or equal to m.

No matter how thoroughly testing is done it seems that one of two problems will rear its ugly head. One is that as hard as you try you can’t generate every bit of data the application will see ‘in the wild’. The other is you can overlook simple examples in favor of the more complex. This is, of course, why we file bug reports with software vendors, because we caught something they didn’t. It is, I suppose, the ‘circle of life’ for software.

Now the remainder is up to you.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: