Thread: 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

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.

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

On Sun, 14 Jan 2001, Michael Richards wrote:

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

Right, but value=NULL is *NOT* true when value is NULL.
That's what the spec says.  value=NULL where value is NULL
is unknown not true, therefore WHERE value=$1 ($1 being
NULL) is never going to be true.

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

Because of Access's brokenness, the parser or some other layer of the
code "fixes" explicit =NULL (ie, in the actually query string) into
IS NULL which is the correct way to check for nulls.
 The statement should be (and would get converted to):
WHERE value = $1 OR ( $1 IS NULL AND value IS NULL)

ISNULL returns TRUE if its argument is null and FALSE otherwise, so
you have UNKNOWN OR (TRUE AND TRUE) which is TRUE, as opposed to simply
UNKNOWN.

Because your original query was = $1, it doesn't do the mangling of the
SQL to change into IS NULL when $1 is NULL.  The fact that we do that
conversion at all actually breaks spec a little bit but we have little
choice with broken clients.