X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B892FDA741 for ; Wed, 19 Oct 2005 18:00:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31035-09 for ; Wed, 19 Oct 2005 21:00:01 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 70ED5DA72D for ; Wed, 19 Oct 2005 18:00:02 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 23A35354B6; Wed, 19 Oct 2005 14:00:03 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 21946354B5; Wed, 19 Oct 2005 14:00:03 -0700 (PDT) Date: Wed, 19 Oct 2005 14:00:03 -0700 (PDT) From: Stephan Szabo To: =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= Cc: pgsql-sql@postgresql.org Subject: Re: NULL in IN clause In-Reply-To: <001a01c5d4ea$243a1830$6400a8c0@OTTO> Message-ID: <20051019135459.D99528@megazone.bigpanda.com> References: <00cc01c5d00b$3a67dc70$1102a8c0@dream> <1129681803.15546.21.camel@state.g2switchworks.com> <001a01c5d4ea$243a1830$6400a8c0@OTTO> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200510/228 X-Sequence-Number: 23081 On Wed, 19 Oct 2005, [iso-8859-2] Havasv=F6lgyi Ott=F3 wrote: > Hi, > > I have just run this command on 8.0.4 : > > SELECT 'foo' WHERE 0 NOT IN (NULL, 1); > > And it resulted is zero rows. > Without NULL it is OK. > Is this a bug, or the standard has such a rule? This is standard behavior. Seeing if I can do this from memory... a NOT IN b is equivalent in the spec to NOT(a IN b). a IN b is equivalent to a =3DANY b. a =3DANY b returns true if a =3D x is true for any x in b. a= =3DANY b returns false if a =3D x is false for all x in b. Otherwise it returns unknown. 0 =3D NULL returns unknown 0 =3D 1 returns false So, 0 IN (NULL,1) returns unknown. NOT(unknown) is unknown. WHERE clauses only return rows for which the search condition is true, so a row is not returned.