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

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.