Bug in SQL functions that use a NULL parameter directly

Hi.

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.

insert into test values (NULL);
select testfunc(NULL);
testfunc
----------
f
(1 row)

select * from test;
value
-------

(1 row)

Now if I really muck with the expression...
create function testfunc1(int4)
RETURNS bool AS
'SELECT count(*)>0 AS RESULT FROM test where value= $1 OR
(value=NULL AND $1=NULL)'
language 'SQL';

It works:
select testfunc1(NULL);
testfunc1
-----------
t
(1 row)

-Michael
_________________________________________________________________
http://fastmail.ca/ - Fast Free Web Email for Canadians
>From pgsql-bugs-owner(at)postgresql(dot)org Sun Jan 14 06:00:18 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 f0EB0Dp39337
for <pgsql-bugs(at)postgresql(dot)org>; Sun, 14 Jan 2001 06:00:14 -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 f0EB05q11336;
Sun, 14 Jan 2001 03:00:05 -0800 (PST)
Date: Sun, 14 Jan 2001 03:00:05 -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: <3A614881(dot)000017(dot)77179(at)frodo(dot)searchcanada(dot)ca>
Message-ID: <Pine(dot)BSF(dot)4(dot)21(dot)0101140255470(dot)11276-100000(at)megazone23(dot)bigpanda(dot)com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200101/66
X-Sequence-Number: 441

On Sun, 14 Jan 2001, Michael Richards wrote:

> Hi.
>
> 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.