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@postgresql.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@postgresql.org>; Sun, 14 Jan 2001 06:00:14 -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 f0EB05q11336;
	Sun, 14 Jan 2001 03:00:05 -0800 (PST)
Date: Sun, 14 Jan 2001 03:00:05 -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: <3A614881.000017.77179@frodo.searchcanada.ca>
Message-ID: <Pine.BSF.4.21.0101140255470.11276-100000@megazone23.bigpanda.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.