Re: BUG #6064: != NULL, <> NULL do not work [sec=UNCLASSIFIED]

On Mon, 2011-06-20 at 10:00 +0930, Pilling, Michael wrote:
> The behaviour of the generated code may well be correct and indeed I
> agree that it is but from
> everything you and the detailed documentation have said column != NULL
> is at least deprecated
> and is highly likely to indicate a programming error.

The right side of the expression may be an expression as well; e.g.:
 a != b  (or a <> b)

The DBMS would not know that one side is NULL until runtime.

> It is totally normal for a parser to warn
> against archaic or dangerous constructs.

It's only an obvious mistake in the trivial case you show where one side
is a constant NULL (therefore making the entire expression a constant
NULL). The more general form "a != b" is quite common, even if somewhat
dangerous in the presence of NULL.

> The idea being to avoid subtle runtime bugs that are
> hard to track down. This happens in Ada, Java and even C and many
> other languages.

NULL is one place in SQL that hides possible mistakes that could
otherwise be caught by the compiler, leaving your queries in danger of
subtle runtime bugs. There's not a good way to conform to the SQL spec
and catch the kind of subtle NULL problems to which you're referring.

It may be possible to make a static analysis "safety check" tool to warn
users about dangerous constructs like that, but it would be a fairly
major effort (and would probably just end up telling you to put COALESCE
everywhere). Trying to only catch the kind of trivial mistakes involving
constants and known operators is counterproductive, in my opinion.

The bottom line is that NULLs are a little on the dangerous side. If you
think your example is bad, consider the semantics of NOT IN with respect
to NULL -- that's a trap even for experts. If you want to be safe, make
liberal use of COALESCE and WHERE x IS NOT NULL on any expression that
you think might ever evaluate to NULL.

Also note that NULLs can be created by outer joins and aggregates even
if your source data has no NULLs at all.

PostgreSQL is a SQL DBMS, and in SQL, NULL affects everything. I'm sure
there are places in the documentation that could be improved, but
warnings on every page would be counterproductive.

Regards,
    Jeff Davis