Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA13976 for ; Wed, 9 Feb 2000 10:48:45 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA05634; Wed, 9 Feb 2000 10:48:23 -0500 (EST) To: "Oliver Elphick" cc: Kevin Dalley , 57466@bugs.debian.org, pgsql-sql@postgreSQL.org Subject: Re: [SQL] Re: Bug#57466: select ... except fails when there are nulls in second clause In-reply-to: <200002090908.JAA06657@linda.lfix.co.uk> References: <200002090908.JAA06657@linda.lfix.co.uk> Comments: In-reply-to "Oliver Elphick" message dated "Wed, 09 Feb 2000 09:08:20 +0000" Date: Wed, 09 Feb 2000 10:48:23 -0500 Message-ID: <5630.950111303@sss.pgh.pa.us> From: Tom Lane "Oliver Elphick" writes: >> The following statement returns 0 rows when there is a null in table >> second: >> pgbug=> select value from first except select value from second; > I think that this is not a bug at all, but a necessary consequence of how > nulls are treated, but I would be grateful for confirmation of this. > > This is equivalent to saying "give me all items in first where value is > not found in second". The point about a null is that you don't know > what value it is, so it might be a value that you want. Right. This might be more clear if you look at the query in the form that it gets rewritten into: SELECT value FROM first WHERE value <> ALL (SELECT value FROM second); which in turn can be visualized as WHERE (first.value <> second.value1) AND (first.value <> second.value2) AND ... (first.value <> second.valueN); If any of the values coming from second are NULL, then the result of the AND cannot be TRUE: it can only be FALSE ("there's definitely a matching value") or NULL ("I don't know whether there's a matching value"). Postgres 6.5.* does have some bugs in this area, because various places fail to implement proper three-valued boolean logic; in particular nodeSubplan.c didn't return a NULL boolean result when it should have. (That makes no difference in this example, but would make a difference if the result of the subselect operator were combined with other boolean clauses.) I believe I've fixed all those problems for 7.0. regards, tom lane