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

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.