X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 131019DCA6E for ; Tue, 27 Dec 2005 20:34:47 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25964-03 for ; Tue, 27 Dec 2005 20:34:50 -0400 (AST) X-Greylist: delayed 00:22:08.812512 by SQLgrey- Received: from b.mail.sonic.net (b.mail.sonic.net [64.142.19.5]) by postgresql.org (Postfix) with ESMTP id 0002A9DC9AF for ; Tue, 27 Dec 2005 20:34:44 -0400 (AST) Received: from localhost.localdomain (64-142-36-103.dsl.static.sonic.net [64.142.36.103]) by b.mail.sonic.net (8.13.3/8.13.3) with ESMTP id jBS0YmT3010274 (version=TLSv1/SSLv3 cipher=DES-CBC3-SHA bits=168 verify=NO); Tue, 27 Dec 2005 16:34:48 -0800 Received: from elein by localhost.localdomain with local (Exim 4.50) id 1ErPHU-0003Vj-3Q; Tue, 27 Dec 2005 16:35:04 -0800 Date: Tue, 27 Dec 2005 16:35:03 -0800 To: Tom Lane Cc: elein , George Pavlov , pgsql-sql@postgresql.org Subject: Re: "large" IN/NOT IN subqueries result in query returning wrong data Message-ID: <20051228003503.GE20674@varlena.com> Mail-Followup-To: Tom Lane , George Pavlov , pgsql-sql@postgresql.org References: <3697.1135712691@sss.pgh.pa.us> <20051228001251.GB20674@varlena.com> <9416.1135729540@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9416.1135729540@sss.pgh.pa.us> User-Agent: Mutt/1.5.9i From: elein X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.048 required=5 tests=[AWL=0.048] X-Spam-Score: 0.048 X-Spam-Level: X-Archive-Number: 200512/216 X-Sequence-Number: 23758 On Tue, Dec 27, 2005 at 07:25:40PM -0500, Tom Lane wrote: > elein writes: > > In 8.0 we get: > > > elein=# select 1 in (NULL, 1, 2); > > ?column? > > ---------- > > t > > (1 row) > > > elein=# select 3 not in (NULL, 1, 2); > > ?column? > > ---------- > > > (1 row) > > > For consistency, either both should return NULL or > > both return true/false. > > The above behavior is correct per spec. Feel free to argue its > consistency with the SQL committee ;-) Oh, no! Not the committee! > > Note that the above are not inverses because you changed the lefthand > input. You do get consistent results when you just add or omit NOT: Yes, you are right. I skipped the permutations to get down to the point. > > regression=# select 1 not in (NULL, 1, 2); > ?column? > ---------- > f > (1 row) > > regression=# select 3 in (NULL, 1, 2); > ?column? > ---------- > > (1 row) > > regards, tom lane Thanks for your clarification. ~elein elein@varlena.com