Re: Limit or Bug?

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_at_gmail.com> wrote: From: Charles Schultz <sacrophyte_at_gmail.com> Subject: Re: Limit or Bug? To: jkstill_at_gmail.com Cc: "Oracle-L Freelists" <oracle-l_at_freelists.org> 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_at_gmail.com> 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 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 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



      
--
http://www.freelists.org/webpage/oracle-l