Re: Bug in SQL functions that use a NULL parameter directly

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(at)postgresql(dot)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(at)postgresql(dot)org>; Sun, 14 Jan 2001 13:37:32 -0500 (EST)
(envelope-from sszabo(at)megazone23(dot)bigpanda(dot)com)
Received: from localhost (sszabo(at)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(at)megazone23(dot)bigpanda(dot)com>
To: Michael Richards <michael(at)fastmail(dot)ca>
cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug in SQL functions that use a NULL parameter directly
In-Reply-To: <3A61EDA9(dot)00004D(dot)77179(at)frodo(dot)searchcanada(dot)ca>
Message-ID: <Pine(dot)BSF(dot)4(dot)21(dot)0101141030320(dot)12140-100000(at)megazone23(dot)bigpanda(dot)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.