Re: Limit or Bug?
- From: Asif Momen <asif_oracle@xxxxxxxxx>
- To: jkstill@xxxxxxxxx, sacrophyte@xxxxxxxxx
- Date: Sat, 5 Jul 2008 02:02:12 -0700 (PDT)
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
Other related posts:
