SQL: REMAINDER function

Ever heard of the REMAINDER function? I did’t before reading some article by Melanie Caffrey. This might not be a function that I couldn’t live without, but yet nice to know about. Let’s take a look at what the documentation says (Note! At first sight it might look a little strange!):

MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0.
REMAINDER returns the remainder of n2 divided by n1.
The MOD function is similar to REMAINDER except that it uses FLOOR in its formula, whereas REMAINDER uses ROUND

Well to me it seems like they should retun the same value. The documentation mention that MOD uses FLOOR, and REMAINDER usees ROUND. But both functions should return the remainder of n2 divided by n1. The docmentation seems to be very unclear, or even worse, might be wrong.

Let’s look at an example:

SQL> 
set serveroutput on
declare
   l        number   :=  8;
   l_max    number   := 15;
begin
      
   DBMS_OUTPUT.PUT_LINE('VALUE     '||rpad('/'||l,10,' ') ||'INT       MODULUS   REMAINDER ');
   DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------');
   for l_row in (select n, 
                     trunc(n/l)              result_int, 
                     MOD(n, l)               modulus, 
                     (n-l*TRUNC(n/l))        modulus_eq, 
                     REMAINDER(n, l)         remaining, 
                     (n-l*TRUNC(n/l))-l      remaining_eq  
                  from (
                     SELECT LEVEL n
                     FROM dual       
                     CONNECT BY LEVEL <= l_max)) loop
                     
      DBMS_OUTPUT.PUT(rpad(l_row.n,10,' '));
      DBMS_OUTPUT.PUT(rpad('/'||l,10,' '));
      DBMS_OUTPUT.PUT(rpad(l_row.result_int,10,' '));
      DBMS_OUTPUT.PUT(rpad(l_row.modulus,10,' '));
      DBMS_OUTPUT.PUT_LINE(rpad(l_row.remaining,10,' '));
   end loop;
end;
/

VALUE     /8        INT       MODULUS   REMAINDER 
-----------------------------------------------------------
1         /8        0         1         1         
2         /8        0         2         2         
3         /8        0         3         3         
4         /8        0         4         4         
5         /8        0         5         -3        
6         /8        0         6         -2        
7         /8        0         7         -1        
8         /8        1         0         0         
9         /8        1         1         1         
10        /8        1         2         2         
11        /8        1         3         3         
12        /8        1         4         -4        
13        /8        1         5         -3        
14        /8        1         6         -2        
15        /8        1         7         -1        
PL/SQL procedure successfully completed.

This example definitly shows that the functions do't return the same.
As the definition states, the modulus function (MOD) gives the remainder after integer devision. So what does then the remainder function give us?
The numbers above suggest that the remainder functions looks at the return value from moduls and compares it to the divisor. If it's bigger that the half (ROUND), we return how much more is needed in the is needed for devision to return an integer (moduls=0). And if its less than half of the dividend, it returns the numbers needed to complete the integer devisjon upwards. So the postiviv vs negative sign shows if the division is "over the top" (read: over half way there). The integer shows how far above or how far left to complete the integer devision (mod=0).

Let's try to simulate what the two function does (using FLOOR and ROUND), by extending the example above:

set serveroutput on
declare
   l        number   :=  8;
   l_max    number   := 26;
begin
      
   DBMS_OUTPUT.PUT_LINE('VALUE     '||rpad('/'||l,10,' ') ||'INT       MODULUS   MOD_EQ    REMAINDER REM_EQ');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
   for l_row in (select n, 
                     trunc(n/l)              result_int, 
                     MOD(n, l)               modulus, 
                     (n-l*FLOOR(n/l))        modulus_eq, 
                     REMAINDER(n, l)         remaining, 
                     (n-l*ROUND(n/l))        remaining_eq  
                  from (
                     SELECT LEVEL n
                     FROM dual       
                     CONNECT BY LEVEL <= l_max)) loop
                     
      DBMS_OUTPUT.PUT(rpad(l_row.n,10,' '));
      DBMS_OUTPUT.PUT(rpad('/'||l,10,' '));
      DBMS_OUTPUT.PUT(rpad(l_row.result_int,10,' '));
      DBMS_OUTPUT.PUT(rpad(l_row.modulus,10,' '));
      DBMS_OUTPUT.PUT(rpad(l_row.modulus_eq,10,' '));
      DBMS_OUTPUT.PUT(rpad(l_row.remaining,10,' '));
      DBMS_OUTPUT.PUT_LINE(rpad(l_row.remaining_eq,10,' '));
   end loop;
end;
/

VALUE     /8        INT       MODULUS   MOD_EQ    REMAINDER REM_EQ
--------------------------------------------------------------------
1         /8        0         1         1         1         1         
2         /8        0         2         2         2         2         
3         /8        0         3         3         3         3         
4         /8        0         4         4         4         -4        
5         /8        0         5         5         -3        -3        
6         /8        0         6         6         -2        -2        
7         /8        0         7         7         -1        -1        
8         /8        1         0         0         0         0         
9         /8        1         1         1         1         1         
10        /8        1         2         2         2         2         
11        /8        1         3         3         3         3         
12        /8        1         4         4         -4        -4        
13        /8        1         5         5         -3        -3        
14        /8        1         6         6         -2        -2        
15        /8        1         7         7         -1        -1        
16        /8        2         0         0         0         0         
17        /8        2         1         1         1         1         
18        /8        2         2         2         2         2         
19        /8        2         3         3         3         3         
20        /8        2         4         4         4         -4        
21        /8        2         5         5         -3        -3        
22        /8        2         6         6         -2        -2        
23        /8        2         7         7         -1        -1        
24        /8        3         0         0         0         0         
25        /8        3         1         1         1         1         
26        /8        3         2         2         2         2         
PL/SQL procedure successfully completed.

This seems the right algorithm except where n equals to 4 and 20. Here we see the function returns a positive number, while my equation shows the same negative number. I can't really explain why this is happening. If i choose a odd number in the dividend everything looks ok:

set serveroutput on
declare
   l        number   :=  7;
   l_max    number   := 20;
begin
  ...
end;
/

VALUE     /7        INT       MODULUS   MOD_EQ    REMAINDER REM_EQ
--------------------------------------------------------------------
1         /7        0         1         1         1         1         
2         /7        0         2         2         2         2         
3         /7        0         3         3         3         3         
4         /7        0         4         4         -3        -3        
5         /7        0         5         5         -2        -2        
6         /7        0         6         6         -1        -1        
7         /7        1         0         0         0         0         
8         /7        1         1         1         1         1         
9         /7        1         2         2         2         2         
10        /7        1         3         3         3         3         
11        /7        1         4         4         -3        -3        
12        /7        1         5         5         -2        -2        
13        /7        1         6         6         -1        -1        
14        /7        2         0         0         0         0         
15        /7        2         1         1         1         1         
16        /7        2         2         2         2         2         
17        /7        2         3         3         3         3         
18        /7        2         4         4         -3        -3        
19        /7        2         5         5         -2        -2        
20        /7        2         6         6         -1        -1        
PL/SQL procedure successfully completed.

Post a Comment

Your email is never published nor shared. Required fields are marked *