Thread
-
Re: Bug in SQL functions that use a NULL parameter directly
Michael Richards <michael@fastmail.ca> — 2001-01-14T18:19:21Z
I do not understand how this can possibly be correct unless NULL is not permitted in a function. In one case, I've got: WHERE value= $1 Which is called with NULL and therefore should be: WHERE value= NULL This fails. The other case which is logically equivalent I've got: WHERE value= $1 OR ($1=NULL AND value=NULL) This passes. So I get a true and a false from the same logical statement. I am not using anything to do with MS Access, so I do not see how it may be involved with this problem. -Michael >> I'm using 7.0.3 and I've found a bug: >> >> create table test(value int4); >> create function testfunc(int4) >> RETURNS bool AS >> 'SELECT count(*)>0 AS RESULT FROM test where value= $1' >> language 'SQL'; >> >> So I want this function to return true when it finds the >> specified value in the table. It does not work when you have a >> null in the table and call it with a null. > > This is actually probably correct. NULL=NULL is not true but > unknown which will not satisfy the where clause. The reason such > a query does something different from the psql prompt is that the > parse is looking for =NULL to turn it into IS NULL due to broken > MS Acess statements. In this case it doesn't know to turn it into > an ISNULL and so instead does a comparison which will never be > true according to spec. > _________________________________________________________________ http://fastmail.ca/ - Fast Free Web Email for Canadians >From pgsql-bugs-owner@postgresql.org Sun Jan 14 13:37:34 2001 Received: from megazone23.bigpanda.com (rfx-64-6-210-138.users.reflexcom.com [64.6.210.138]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0EIbWp65489 for <pgsql-bugs@postgresql.org>; Sun, 14 Jan 2001 13:37:32 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: from localhost (sszabo@localhost) by megazone23.bigpanda.com (8.11.1/8.11.1) with ESMTP id f0EIbUY12160; Sun, 14 Jan 2001 10:37:30 -0800 (PST) Date: Sun, 14 Jan 2001 10:37:30 -0800 (PST) From: Stephan Szabo <sszabo@megazone23.bigpanda.com> To: Michael Richards <michael@fastmail.ca> cc: pgsql-bugs@postgresql.org Subject: Re: Bug in SQL functions that use a NULL parameter directly In-Reply-To: <3A61EDA9.00004D.77179@frodo.searchcanada.ca> Message-ID: <Pine.BSF.4.21.0101141030320.12140-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200101/72 X-Sequence-Number: 447 On Sun, 14 Jan 2001, Michael Richards wrote: > I do not understand how this can possibly be correct unless NULL is > not permitted in a function. > > In one case, I've got: > WHERE value= $1 > Which is called with NULL and therefore should be: > WHERE value= NULL > This fails. Right, but value=NULL is *NOT* true when value is NULL. That's what the spec says. value=NULL where value is NULL is unknown not true, therefore WHERE value=$1 ($1 being NULL) is never going to be true. > The other case which is logically equivalent I've got: > WHERE value= $1 OR ($1=NULL AND value=NULL) > This passes. > > So I get a true and a false from the same logical statement. I am not > using anything to do with MS Access, so I do not see how it may be > involved with this problem. Because of Access's brokenness, the parser or some other layer of the code "fixes" explicit =NULL (ie, in the actually query string) into IS NULL which is the correct way to check for nulls. The statement should be (and would get converted to): WHERE value = $1 OR ( $1 IS NULL AND value IS NULL) ISNULL returns TRUE if its argument is null and FALSE otherwise, so you have UNKNOWN OR (TRUE AND TRUE) which is TRUE, as opposed to simply UNKNOWN. Because your original query was = $1, it doesn't do the mangling of the SQL to change into IS NULL when $1 is NULL. The fact that we do that conversion at all actually breaks spec a little bit but we have little choice with broken clients. -
Re: [BUGS] Bug in SQL functions that use a NULL parameter directly
Andrew McMillan <andrew@catalyst.net.nz> — 2001-01-14T18:31:36Z
Michael Richards wrote: > > I do not understand how this can possibly be correct unless NULL is > not permitted in a function. > > In one case, I've got: > WHERE value= $1 > Which is called with NULL and therefore should be: > WHERE value= NULL > This fails. > > The other case which is logically equivalent I've got: > WHERE value= $1 OR ($1=NULL AND value=NULL) > This passes. > > So I get a true and a false from the same logical statement. I am not > using anything to do with MS Access, so I do not see how it may be > involved with this problem. For comparison with NULL you have to use "IS NULL" according to the SQL specification. The reference to MS Access is that it breaks SQL specification by allowing "= NULL" to work, and psql tries to allow that to work because it's being friendly. You need to put this in for your where clause: WHERE value = $1 OR ($1 IS NULL and value IS NULL) That's the SQL specification. The comparison "value = NULL" will always return NULL (i.e. unknown) according to the SQL specification. This may seem obscure to you now, but it's darned useful to have trinary logic available. In fact I am currently wrestling with Oracle at the moment because it specifically doesn't consider NULL and '' to be different :-( Hope this helps, Andrew. -- _____________________________________________________________________ Andrew McMillan, e-mail: Andrew@catalyst.net.nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267