Re: Limit or Bug?

[Asif Momen <asif_oracle@xxxxxxxxx>]

Hi,


MOD function starts returning erroneous output starting with
"200000000000000000000000000000000000002" value. Also, REMAINDER functions 
behaves the same way.

I believe there's some bug associated with MOD & REMAINDER functions.



SQL> set serveroutput on
SQL> declare
  2    n  number;
  3    i  integer;
  4    bd binary_double;
  5    x1  binary_double;
  6    y1  binary_double;
  7
  8    bf  BINARY_FLOAT;
  9    x2  BINARY_FLOAT;
 10    y2  BINARY_FLOAT;
 11  begin
 12    n := mod( power(2, 128) -1, 2);
 13    i := mod( power(2, 128) -1, 2);
 14    x1 := 2;
 15    y1 := 128;
 16    bd := mod( power(x1, y1) -1, x1);
 17    x2 := 2;
 18    y2 := 128;
 19    bf := mod( power(x2, y2) -1, x2);
 20
 21    dbms_output.put_line( ' Number : ' || to_char(n) );
 22    dbms_output.put_line( ' Integer : ' || to_char(i) );
 23    dbms_output.put_line( ' Binary Double : ' || to_char(bd) );
 24    dbms_output.put_line( ' Binary Float : ' || to_char(bf) );
 25
 26    y1 := 200000000000000000000000000000000000002;
 27    bd := mod(y1 - 1, x1);
 28    dbms_output.put_line( ' Binary Double : ' || to_char(bd) );
 29
 30    y2 := 200000000000000000000000000000000000002;
 31    bf := mod(y2 - 1, x2);
 32    dbms_output.put_line( ' Binary Float : ' || to_char(bf) );
 33  end;
 34  /
Number : -1
Integer : -1
Binary Double : 0
Binary Float : 0
Binary Double : 0
Binary Float : 0

PL/SQL procedure successfully completed.

SQL> select mod( 200000000000000000000000000000000000000 - 1, 2) from dual;

  MOD(200000000000000000000000000000000000000-1,2)
--------------------------------------------------
                                                 1

SQL> select mod( 200000000000000000000000000000000000002 - 1, 2) from dual;

  MOD(200000000000000000000000000000000000002-1,2)
--------------------------------------------------
                                                -1

SQL>

SQL> select remainder( 200000000000000000000000000000000000000, 2) from dual;

REMAINDER(200000000000000000000000000000000000000,2)
----------------------------------------------------
                                                   0

SQL> select remainder( 200000000000000000000000000000000000001, 2) from dual;

REMAINDER(200000000000000000000000000000000000001,2)
----------------------------------------------------
                                                  -1


Regards,

Asif Momen
http://momendba.blogspot.com


--- On Thu, 7/3/08, Charles Schultz <sacrophyte@xxxxxxxxx> wrote:
From: Charles Schultz <sacrophyte@xxxxxxxxx>
Subject: Re: Limit or Bug?
To: jkstill@xxxxxxxxx
Cc: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
Date: Thursday, July 3, 2008, 2:19 PM

Interesting indeed:

SQL > select mod(3.4028E+38,2) from dual;


MOD(3.4028E+38,2)
-----------------

                0

SQL > select mod(3.4028E+38 + 1,2) from dual;


MOD(3.4028E+38+1,2)
-------------------

                 -1


On Thu, Jul 3, 2008 at 3:44 PM, Jared Still <jkstill@xxxxxxxxx> wrote:


The following bit of SQL uses the mod() function to determine modulus 2 of two 
large numbers


13:37:20 SQL>set echo on
13:37:24 SQL>@mod_test
13:37:25 SQL>
13:37:25 SQL>
13:37:25 SQL>

13:37:25 SQL>select mod(power(2,127)-1,2) from dual
13:37:25   2  /

MOD(POWER(2,127)-1,2)
---------------------
                    1

1 row selected.

13:37:25 SQL>
13:37:25 SQL>select mod(power(2,128)-1,2) from dual


13:37:25   2  /

MOD(POWER(2,128)-1,2)
---------------------
                   -1

1 row selected.

13:37:25 SQL>

--------------------------------------------------

Notice that mod(power(2,128)-1,2) returns an incorrect answer, while the


answer for mod(power(2,127)-1,2) is correct.

Does someone here know why?

Is it a limit?

Or is it a bug?

Please supply an explanation and/or URL for your answer.  :)

My guess (yes Alex, it's a guess, or rather, a hunch) is that this is


related to two's complement binary numbers, but I haven't been able
to (yet) find any explanation for this.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist





-- 
Charles Schultz

• References:
  • Re: Limit or Bug?
    • From: Charles Schultz

Other related posts:

Limit or Bug?

Re: Limit or Bug?

Re: Limit or Bug?

Re: Limit or Bug?

Re: Limit or Bug?