Oracle bitand inconsistent behaviour

Question

I'm working on a legacy database using bitmaps for searching. I'm using the bitand function to find bitmaps that match a given bitmask but I'm getting the wrong results: the bitand between two numbers is returning a non-zero value when zero would be expected.

I'm using Oracle 11gR2 and I can reproduce the problem like this:

create table BITAND_TEST (BITMAP binary_double);

-- insert the bitmap of 2^57 in the table
insert into BITAND_TEST values (144115188075855872);

-- bitand(2^57, 2^50) evaluates to 0
select bitand(144115188075855872, 1125899906842624) from dual;

-- but in this statement it returns non-zero
select  *
  from  BITAND_TEST 
  where bitand(BITMAP, 1125899906842624) <> 0; -- I'm using 2^50 here

Is this a type casting problem? Which types would you use to store a bitmap?

Answer 1

When you execute

select * from  BITAND_TEST;

you get 1,44115188075856E17. It is not the same value you entered, you have lost some precision. Try this

select TO_CHAR (BITMAP, '9999999999999999999999') from  BITAND_TEST;

to see the difference. I get

 144115188075855870