Skip site navigation (1) Skip section navigation (2)

Bug in SQL functions that use a NULL parameter directly

From: "Michael Richards" <michael(at)fastmail(dot)ca>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug in SQL functions that use a NULL parameter directly
Date: 2001-01-14 06:34:41
Message-ID: 3A614881.000017.77179@frodo.searchcanada.ca (view raw or flat)
Thread:
Lists: 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
>From pgsql-bugs-owner(at)postgresql(dot)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(at)postgresql(dot)org>; Sun, 14 Jan 2001 06:00:14 -0500 (EST)
	(envelope-from sszabo(at)megazone23(dot)bigpanda(dot)com)
Received: from localhost (sszabo(at)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(at)megazone23(dot)bigpanda(dot)com>
To: Michael Richards <michael(at)fastmail(dot)ca>
cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug in SQL functions that use a NULL parameter directly
In-Reply-To: <3A614881(dot)000017(dot)77179(at)frodo(dot)searchcanada(dot)ca>
Message-ID: <Pine(dot)BSF(dot)4(dot)21(dot)0101140255470(dot)11276-100000(at)megazone23(dot)bigpanda(dot)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. 



pgsql-bugs by date

Next:From: pgsql-bugsDate: 2001-01-14 12:43:15
Subject: Database corruption in RH 6.2/prepackaged PG
Previous:From: Chris AndersonDate: 2001-01-13 03:16:31
Subject: possible database corruption

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group