Bug in SQL functions that use a NULL parameter directly - Mailing list pgsql-bugs

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