hi,
We are running Oracle database standard edition 18.7.0.0.0.
I noticed a strange issue. A user complained that OBIEE was generating wrong results. I first suspected OBIEE to be the problem, but then I had a closer look at the SQL generated by OBIEE.
After some investigation, it turns out that the behavior of NOT IN does not work as expected. NOT EXISTS works.
In this case, we have a MATERIAL_CODE VARCHAR2(110) used by NOT IN
-- This does not work. Returns zero rows:
select * from del_me_all_materials where material_code not in (select material_code from del_me_with_amount);
-- This works. Returns the expected number of rows:
select * from del_me_all_materials where not exists (select 1 from del_me_with_amount where del_me_with_amount.material_code=del_me_all_materials.material_code);
I found some workarounds that worked. For example concatenating '.' to the material code.
select * from del_me_all_materials where material_code || '.' not in (select material_code || '.' from del_me_with_amount);