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