Submitted by: Jure Erznoznik (velis)

Attachments:
bugdb.zip

a simple select like:
SELECT * FROM table1 WHERE table1.varcharfield = 'const' AND table1.floatfield NOT IN (SELECT table2.floatfield FROM table2)

This select will *sometimes* return zero records if table2 contains values in the floatfield that do not exist in table1.floatfield.

I cannot reproduce the problem on custom created tables, but I have prepared the database that exhibits this bug at http://www.jantar.si/bugdb.zip

It is a very small download and contains only the offending tables. The database has been packed / backed up / restored / whatever, so we're pretty sure it's not corrupt.
The actual select that does not perform is:
SELECT * FROM USERS WHERE DEPARTMENT = 'visitor' AND CODE NOT IN (SELECT CODE FROM ACTVISITORS)

Changing the select to:
SELECT * FROM USERS WHERE DEPARTMENT = 'visitor' AND CODE IN (SELECT CODE FROM ACTVISITORS)
Notice that here 'NOT' is removed. This select returns results as expected, but just the other way around so it's not the solution.

workaround1 (join-ed select):
select users.* from users
left outer join actvisitors on users.code = actvisitors.code
where upper(users.department) = 'VISITOR'
and actvisitors.code is null

workaround2 (remove the offending records first):
delete FROM ACTVISITORS where CODE NOT IN (SELECT code FROM USERS WHERE UPPER(DEPARTMENT) = 'VISITOR')
After removal the original select will return a correct result set.