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 DC3029DC9DC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Dec 2005 20:12:39 -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 22286-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 27 Dec 2005 20:12:42 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id A17519DC9AF
	for <pgsql-sql@postgresql.org>; Tue, 27 Dec 2005 20:12:37 -0400 (AST)
Received: from b.mail.sonic.net (b.mail.sonic.net [64.142.19.5])
	by svr4.postgresql.org (Postfix) with ESMTP id 4B1215AFE26
	for <pgsql-sql@postgresql.org>; Wed, 28 Dec 2005 00:12:41 +0000 (GMT)
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 jBS0CZET003273
	(version=TLSv1/SSLv3 cipher=DES-CBC3-SHA bits=168 verify=NO);
	Tue, 27 Dec 2005 16:12:37 -0800
Received: from elein by localhost.localdomain with local (Exim 4.50)
	id 1ErOvz-0003QE-BZ; Tue, 27 Dec 2005 16:12:51 -0800
Date: Tue, 27 Dec 2005 16:12:51 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: George Pavlov <gpavlov@mynewplace.com>, pgsql-sql@postgresql.org
Subject: Re: "large" IN/NOT IN subqueries result in query returning wrong data
Message-ID: <20051228001251.GB20674@varlena.com>
Mail-Followup-To: Tom Lane <tgl@sss.pgh.pa.us>,
	George Pavlov <gpavlov@mynewplace.com>, pgsql-sql@postgresql.org
References: 
 <CCB89282FCE1024EA3DCE687A96A5164039EBD13@ehost010-6.exch010.intermedia.net>
	<3697.1135712691@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3697.1135712691@sss.pgh.pa.us>
User-Agent: Mutt/1.5.9i
From: elein <elein@varlena.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/214
X-Sequence-Number: 23756

On Tue, Dec 27, 2005 at 02:44:51PM -0500, Tom Lane wrote:
> "George Pavlov" <gpavlov@mynewplace.com> writes:
> > select count(*) from t2 where t2.name not in (
> >   select t1.name from t1 limit 261683) 
> > --> 13
> > select count(*) from t2 where t2.name not in (
> >   select t1.name from t1 limit 261684) 
> > --> 0
> 
> > What is so magical about 261683?
> 
> Most likely, the 261684'th row of t1 has a NULL value of name.
> Many people find the behavior of NOT IN with nulls unintuitive,
> but it's per SQL spec ...
> 
> 			regards, tom lane

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.

For completeness testing, the following are correct. 
Select NULL in/not in any list returns NULL.
   elein=# select NULL in (1,2);
    ?column?
   ----------

   (1 row)
   
   elein=# select NULL not in (1,2);
    ?column?
   ----------

   (1 row)
   
   elein=# select NULL in (NULL, 1,2);
    ?column?
   ----------

   (1 row)
   
   elein=# select NULL not in (NULL, 1,2);
    ?column?
   ----------

   (1 row)
   
elein
--------------------------------------------------------------
elein@varlena.com        Varlena, LLC        www.varlena.com
(510)655-2584(o)                             (510)543-6079(c)

          PostgreSQL Consulting, Support & Training   

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
--------------------------------------------------------------
AIM: varlenallc          Yahoo: AElein       Skype: varlenallc
--------------------------------------------------------------
I have always depended on the [QA] of strangers.