[Firebird-devel] [FB-Tracker] Created: (CORE-1293) select with 'not in' clause sometimer returns incorrect results

[Jure E. (JIRA) <tr...@fi...>]

select with 'not in' clause sometimer returns incorrect results
---------------------------------------------------------------

                 Key: CORE-1293
                 URL: http://tracker.firebirdsql.org/browse/CORE-1293
             Project: Firebird Core
          Issue Type: Bug
          Components: Engine
    Affects Versions: 2.0.1, 1.5.4, 2.0.0, 1.5.3
         Environment: Windows XP SP2, any hardware configuration (tested on multiple and various systems)
            Reporter: Jure Erznoznik


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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira