Re: ANSI Semantics with NULL and NOT IN

In article <1992Sep17.030421.6389@informix.com> aland@informix.com (Colonel Panic) writes: >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. Well, maybe this is knitting picks, but UNKNOWN does not convert to FALSE in 2-valued logic, it is merely UNKNOWN in three-valued logic which is the logic of ANSI SQL. Rows which are not TRUE are not included in the result set of a query. Neither FALSE nor UNKNOWN are true and therefore, as you say, no row. Just trying to keep things concise and accurate :-) Jon Rosen