Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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
share|improve this answer
    
Thanks, I think we fixed the problem by changing the column to number(38) –  spaceCamel Nov 17 '11 at 14:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.