Re: ANSI Semantics with NULL and NOT IN

In article <9552@emory.mathcs.emory.edu> johnl@obelix.informix.com (Jonathan Leffler) writes: >>From: uunet!binky.Binky.COM!irwin (Irwin Schafer) >>Subject: ANSI Semantics with NULL and NOT IN >>X-Informix-List-Id: <news.1810> >> >>Given table "r1": >> a1 a2 a3 >> 1 A null >> 2 B 1 >> 3 C 1 >> >>On Ingres, this query returns the value "A": >> select a2 from r1 where a1 in (select distinct a3 from r1);>>However, this query returns no rows: >> select a2 from r1 where a1 not in (select distinct a3 from r1);>>Where one may expect two rows ("B" and "C") > >Well, under Informix OnLine 4.10, the second select gives B and C as you >expected. That was on both a database which was MODE ANSI and on a database >which was not MODE ANSI. I would expect the same result under different >versions of OnLine, and under Standard Engine. I dunno about "as expected"; I expected the opposite. I tried it under 5.0 SE, and the second query returns NO rows, as I had expected. I'd say the 5.0 behavior is correct, as the comparison "is 2 NOT = ANY of (NULL,1)" is UNKNOWN, and therefore converts to FALSE in 2-valued logic. Hence, no row. >Yours, >Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h> -- Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland "Cities are among the most cosmopolitan places on Earth." -- Dianne Feinstein, addressing a conference on the future of cities at SCU