Re: Erroneus Results using the "NOT IN" Clause

Posted in 1999

Richard Spitz — Wed, 25 Aug 1999 10:50:26 +0200 — source: Informix-list mailing list archive (1991-1998)

"Art S. Kagel" wrote: > > Ummmmm.... If there are 64371 rows in t2 and fewer, ie 61736, rows in t3 > how do you know that the unique keys in t3 are not a proper subset of the > keys in t2? If true then the results you see are correct! As I already wrote, there are definitely rows in t3 that are NOT in t2: select aufnahmenr from t2 where aufnahmenr in ('0001311720','0001306058' , '0001032630', '0001046924' ) ; aufnahmenr 0001032630 0001046924 ------------------------------------------ select pa_aufnr from t3 where pa_aufnr in ('0001311720','0001306058' , '0001032630', '0001046924' ) ; pa_aufnr 0001032630 0001046924 0001306058 0001311720 ------------------------------------------ select aufnahmenr from t2 where aufnahmenr not in (select pa_aufnr from t3) ; aufnahmenr 9900012791 9900055991 9900061591 9900073391 9900135591 9900154991 ... ... ------------------------------------------ select pa_aufnr from t3 where pa_aufnr not in (select aufnahmenr from t2) ; pa_aufnr No rows found. ------------------------------------------ The last result is definitely wrong. 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 | +--------------------------+------------------------------------------+

↪ replying to Richard Spitz

Art S. Kagel — Wed, 25 Aug 1999 13:22:45 -0400 — source: Informix-list mailing list archive (1991-1998)

Ahh, now that is clear to me. Weird ca-ca too. :-o R7.20 IS an older version and MAY have such a bug. OK.... I have no idea why that should happen, so I'll clam up now. %-( Art S. Kagel Richard Spitz wrote: > > "Art S. Kagel" wrote: > > > > Ummmmm.... If there are 64371 rows in t2 and fewer, ie 61736, rows in t3 > > how do you know that the unique keys in t3 are not a proper subset of the > > keys in t2? If true then the results you see are correct! > > As I already wrote, there are definitely rows in t3 that are NOT in t2: > > select aufnahmenr from t2 where aufnahmenr in ('0001311720','0001306058' , > '0001032630', '0001046924' ) ;> > aufnahmenr > > 0001032630 > 0001046924 > > ------------------------------------------ > select pa_aufnr from t3 where pa_aufnr in ('0001311720','0001306058' , > '0001032630', '0001046924' ) ;> > pa_aufnr > > 0001032630 > 0001046924 > 0001306058 > 0001311720 > > ------------------------------------------ > > select aufnahmenr from t2 > where aufnahmenr not in (select pa_aufnr from t3) ;> > aufnahmenr > > 9900012791 > 9900055991 > 9900061591 > 9900073391 > 9900135591 > 9900154991 > ... > ... > ------------------------------------------ > > select pa_aufnr from t3 > where pa_aufnr not in (select aufnahmenr from t2) ;> > pa_aufnr > > No rows found. > ------------------------------------------ > > The last result is definitely wrong. > > 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 | > +--------------------------+------------------------------------------+