Erroneus Results using the "NOT IN" Clause

Dear Informixers, a colleague of mine has a problem with the following SQL statement: select pa_aufnr from t3 where pa_aufnr not in (select aufnahmenr from t2) ; The result is "no rows found", which is definitely wrong. Both t3 and t2 are temp tables generated with: SELECT unique aufnahmenr FROM ck_key into temp t2; SELECT unique pa_aufnr FROM patient_plus into temp t3; Table t2 has 64371 rows, t3 has 61736 rows. There are definitely rows in t3 that have no counterpart in t2. The statement does return correct results when the result set of the subquery is reduced, e.g. select pa_aufnr from t3 where pa_aufnr not in (select aufnahmenr from t2 where aufnahmenr not like "98%") Is this a known bug? Server version is Online Workgroup Server 7.20UC2 on the Siemens platform. Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | | Klinikum Grosshadern | FAX : +49-89-7095-6420 <-- NEW!!! | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

Richard, I have found the same problem. I am running SCO OpenServer5 and IDS 7.30.UC2 now, but I also found the problem on SCO OpenServer5 and IDS 7.23.UC13-1. If you receive any responses on a solution or fix, I would be interested in what you find out. Richard Spitz wrote: > > Dear Informixers, > > a colleague of mine has a problem with the following SQL statement: > > select pa_aufnr from t3 > where pa_aufnr not in (select aufnahmenr from t2) ;> > The result is "no rows found", which is definitely wrong. Both t3 and > t2 are temp tables generated with: > > SELECT unique aufnahmenr FROM ck_key into temp t2; > SELECT unique pa_aufnr FROM patient_plus into temp t3;> > Table t2 has 64371 rows, t3 has 61736 rows. There are definitely rows > in t3 that have no counterpart in t2. > > The statement does return correct results when the result set of the > subquery is reduced, e.g. > > select pa_aufnr from t3 > where pa_aufnr not in > (select aufnahmenr from t2 > where aufnahmenr not like "98%")> > Is this a known bug? Server version is Online Workgroup Server 7.20UC2 > on the Siemens platform. > > Regards, Richard > -- > +--------------------------+------------------------------------------+ > | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | > | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 | > | Klinikum Grosshadern | FAX : +49-89-7095-6420 <-- NEW!!! | > | 81366 Munich, Germany | GSM : +49-172-8933578 | > +--------------------------+------------------------------------------+ -- Netscape User (user_name@dordt.edu)