FW: FW: bitand functions and NUMBER(20)

From: "Mark W. Farnham" <mwf@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 19 Jul 2007 14:23:59 -0400
It starts giving bad results somewhere between 9199999999999999999 and
9299999999999999999.

SQL> c/93/92
  1* select bitand(9999999999999999999,9299999999999999999) value from dual
SQL> r
  1* select bitand(9999999999999999999,9299999999999999999) value from dual

                VALUE
---------------------
 09223372036854775807    <---- wrong, and apparently anything with an
internal calculation result set at least this big comes back with this
answer. I didn't truss this. I'll be happily enlightened if someone chimes
in with a better analysis of this or where I'm going wrong with the thought
process, but I can't put in any more time right now.

SQL> c/92/91
  1* select bitand(9999999999999999999,9199999999999999999) value from dual
SQL> r
  1* select bitand(9999999999999999999,9199999999999999999) value from dual

                VALUE
---------------------
 09199999999999999999    <---- correct

-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx] 
Sent: Thursday, July 19, 2007 2:03 PM
To: 'Stefan Kuhn'
Subject: RE: FW: bitand functions and NUMBER(20)

I'm getting the same results as you with all the to_numbers supplied.

This is also interesting: (That 9223372036854775807 value is clearly some
internal overflow maximum). I think you've found a bug unless I'm missing
something.

  1* select 19999999999999999999+1 value from dual

                VALUE
---------------------
 20000000000000000000
(so the display and whatever clearly has enough width)

SQL> r
  1* select bitand(1999999999999999999,1999999999999999999) value from dual

                VALUE
---------------------
 01999999999999999999

(19 digits works just fine)

SQL> c/9,/99,
  1* select bitand(19999999999999999999,1999999999999999999) value from dual
SQL> c/9)/99)
  1* select bitand(19999999999999999999,19999999999999999999) value from
dual
SQL> r
  1* select bitand(19999999999999999999,19999999999999999999) value from
dual

                VALUE
---------------------
 09223372036854775807

(twenty digits gets this answer.)

So other than Oracle fixing the bug or you using smaller values (two columns
or something), you would have to use string manipulation of the character
value of the number and carve it up for the bitand and concatenate the
results. 



--
//www.freelists.org/webpage/oracle-l
FW: FW: bitand functions and NUMBER(20)

Other related posts:
