Re: [BUGS] Bug in SQL functions that use a NULL parameter directly

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

For comparison with NULL you have to use "IS NULL" according to the SQL
specification.

The reference to MS Access is that it breaks SQL specification by
allowing "= NULL" to work, and psql tries to allow that to work because
it's being friendly.

You need to put this in for your where clause:

WHERE value = $1 OR ($1 IS NULL and value IS NULL)

That's the SQL specification.

The comparison "value = NULL" will always return NULL (i.e. unknown)
according to the SQL specification.  This may seem obscure to you now,
but it's darned useful to have trinary logic available.  In fact I am
currently wrestling with Oracle at the moment because it specifically
doesn't consider NULL and '' to be different :-(

Hope this helps,
                    Andrew.
--
_____________________________________________________________________
           Andrew McMillan, e-mail: Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267