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