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 4728FD891E for ; Wed, 19 Oct 2005 18:11:35 -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 33032-10 for ; Wed, 19 Oct 2005 21:11:32 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1570ED6DAA for ; Wed, 19 Oct 2005 18:11:32 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j9JLBUXV026535; Wed, 19 Oct 2005 17:11:30 -0400 (EDT) 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> References: <00cc01c5d00b$3a67dc70$1102a8c0@dream> <1129681803.15546.21.camel@state.g2switchworks.com> <001a01c5d4ea$243a1830$6400a8c0@OTTO> Comments: In-reply-to =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= message dated "Wed, 19 Oct 2005 22:17:34 +0200" Date: Wed, 19 Oct 2005 17:11:30 -0400 Message-ID: <26534.1129756290@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.11 required=5 tests=[AWL=-0.097, UPPERCASE_25_50=0.207] X-Spam-Level: X-Archive-Number: 200510/227 X-Sequence-Number: 23080 =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= writes: > 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 per spec. The computation is effectively NOT (0 = NULL OR 0 = 1) NOT (NULL OR FALSE) NOT NULL NULL ie, the result is UNKNOWN, which WHERE treats the same as FALSE. regards, tom lane