Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fACEdkm07963 for ; Mon, 12 Nov 2001 09:39:46 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id A4551D5E3; Mon, 12 Nov 2001 06:39:50 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 980205BE2; Mon, 12 Nov 2001 06:39:50 -0800 (PST) Date: Mon, 12 Nov 2001 06:39:50 -0800 (PST) From: Stephan Szabo To: Markus Bertheau Cc: postgres sql list Subject: Re: no cond returns diff res than cond or !cond In-Reply-To: <1005575205.31390.2.camel@entwicklung01.cenes.de> Message-ID: <20011112063643.M74385-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/155 X-Sequence-Number: 5539 On 12 Nov 2001, Markus Bertheau wrote: > cenes_test=> select version(); > version > ------------------------------------------------------------- > PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 > (1 row) > > cenes_test=> select count(*) from produkte; > count > ------- > 36384 > (1 row) > > cenes_test=> select count(*) from produkte where artnummer = NULL or > artnummer != NULL; > count > ------- > 36326 > (1 row) > > 2nd query 58 less than first. > > bug? Probably not. You don't want to compare to NULL (although there is a hack which makes an explicit = NULL into the correct comparison), you want to use IS NULL and IS NOT NULL. Comparing to NULL returns an unknown (neither true nor false) and negating that comparison still returns unknown. The hack is in there for compatibility with an MS Access feature, and I think is optional in 7.2, and turns = NULL into IS NULL, there's no equivalent hack for != NULL AFAIK.