Bugs in REMAINDER Function

While browsing through SQL Reference documentation (Oracle 10g Release 2), I stopped at REMAINDER function. The REMAINDER returns the remainder of n2 divided by n1 and internally uses ROUND function to arrive at the remainder value.

Oracle documentation also says, "If n1 != 0, then the remainder is n2 - (n1*N) where N is the integer nearest n2/n1."

SQL> Select REMAINDER(501, 5) from dual;

REMAINDER(501,5)
----------------
               1

SQL>

But, because of an internal bug, REMAINDER function returns incorrect results when "n2/n1" results in 0.5, 1.5, 2.5, .....

SQL> Select REMAINDER(5, 2) from dual;

REMAINDER(5,2)
--------------
             1

SQL>

According to the formula,

n2 - (n1 * N) = 5 - (2 * ROUND(5/2)) = 5 - (2 * 3) = 5 - 6 = -1

the value returned should be "-1" where as the REMAINDER function returns "+1".

My observation is that, for example "1.5" is getting rounded to "1" instead of "2" and is spoiling the outcome of REMAINDER.

I wrote a small PL/SQL block to screen my observation:

SQL> set linesize 1000
SQL> set pages     100
SQL> set serveroutput on
SQL>
SQL>    declare
  2       n1  number;
  3       n2  number;
  4       N   number;
  5
  6       sys_rem number;
  7       my_rem number;
  8     begin
  9       for n1 in 1..5 loop
 10        --for n2 in 1..50 loop
 11        n2 := 1;
 12        while n2 <= 20 loop
 13          Sys_rem := REMAINDER(n2, n1);
 14          N := Round(n2/n1);
 15          My_rem := n2 - (n1 * N);
 16          If sys_rem <> my_rem then
 17            Dbms_output.put_line('n2 : ' || rpad(to_char(n2), 4, ' ') ||
 18                                 '  n1 : ' || to_char(n1) ||
 19                                 '   ' ||
 20                                 ' n2/n1 = ' || rpad(to_char(n2/n1), 4, ' ') ||
 21                                 '     ' ||
 22                                 ' ORCL rem = ' || rpad(to_char(sys_rem), 4, ' ') ||
 23                                 '     ' ||
 24                                 ' Calc Rem [' || rpad(to_char(n2), 4, ' ') ||
 25                                 ' - (' || to_char(n1) || '*' ||
 26                                 rpad(to_char(N), 4, ' ') || ')] = '||
 27                                 to_char(my_rem));
 28          End If;
 29          n2 := n2 + 0.1;
 30        end loop;
 31      end loop;
 32    end;
 33    /
n2 : 2.5   n1 : 1    n2/n1 = 2.5       ORCL rem = .5        Calc Rem [2.5  - (1*3   )] = -.5
n2 : 4.5   n1 : 1    n2/n1 = 4.5       ORCL rem = .5        Calc Rem [4.5  - (1*5   )] = -.5
n2 : 6.5   n1 : 1    n2/n1 = 6.5       ORCL rem = .5        Calc Rem [6.5  - (1*7   )] = -.5
n2 : 8.5   n1 : 1    n2/n1 = 8.5       ORCL rem = .5        Calc Rem [8.5  - (1*9   )] = -.5
n2 : 10.5  n1 : 1    n2/n1 = 10.5      ORCL rem = .5        Calc Rem [10.5 - (1*11  )] = -.5
n2 : 12.5  n1 : 1    n2/n1 = 12.5      ORCL rem = .5        Calc Rem [12.5 - (1*13  )] = -.5
n2 : 14.5  n1 : 1    n2/n1 = 14.5      ORCL rem = .5        Calc Rem [14.5 - (1*15  )] = -.5
n2 : 16.5  n1 : 1    n2/n1 = 16.5      ORCL rem = .5        Calc Rem [16.5 - (1*17  )] = -.5
n2 : 18.5  n1 : 1    n2/n1 = 18.5      ORCL rem = .5        Calc Rem [18.5 - (1*19  )] = -.5
n2 : 1     n1 : 2    n2/n1 = .5        ORCL rem = 1         Calc Rem [1    - (2*1   )] = -1
n2 : 5     n1 : 2    n2/n1 = 2.5       ORCL rem = 1         Calc Rem [5    - (2*3   )] = -1
n2 : 9     n1 : 2    n2/n1 = 4.5       ORCL rem = 1         Calc Rem [9    - (2*5   )] = -1
n2 : 13    n1 : 2    n2/n1 = 6.5       ORCL rem = 1         Calc Rem [13   - (2*7   )] = -1
n2 : 17    n1 : 2    n2/n1 = 8.5       ORCL rem = 1         Calc Rem [17   - (2*9   )] = -1
n2 : 1.5   n1 : 3    n2/n1 = .5        ORCL rem = 1.5       Calc Rem [1.5  - (3*1   )] = -1.5
n2 : 7.5   n1 : 3    n2/n1 = 2.5       ORCL rem = 1.5       Calc Rem [7.5  - (3*3   )] = -1.5
n2 : 13.5  n1 : 3    n2/n1 = 4.5       ORCL rem = 1.5       Calc Rem [13.5 - (3*5   )] = -1.5
n2 : 19.5  n1 : 3    n2/n1 = 6.5       ORCL rem = 1.5       Calc Rem [19.5 - (3*7   )] = -1.5
n2 : 2     n1 : 4    n2/n1 = .5        ORCL rem = 2         Calc Rem [2    - (4*1   )] = -2
n2 : 10    n1 : 4    n2/n1 = 2.5       ORCL rem = 2         Calc Rem [10   - (4*3   )] = -2
n2 : 18    n1 : 4    n2/n1 = 4.5       ORCL rem = 2         Calc Rem [18   - (4*5   )] = -2
n2 : 2.5   n1 : 5    n2/n1 = .5        ORCL rem = 2.5       Calc Rem [2.5  - (5*1   )] = -2.5
n2 : 12.5  n1 : 5    n2/n1 = 2.5       ORCL rem = 2.5       Calc Rem [12.5 - (5*3   )] = -2.5

PL/SQL procedure successfully completed.

SQL>

I have tested this code on Oracle 10g Release 2. Unfortunately, this bug also exists in Oracle 11g.

I have raised a Service Request and also filed a bug with Oracle Support.

Regards