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

"Pilling, Michael" wrote:

> The real problem here then is that the documentation showing
> the boolean comparison operators does not mention this

This page does, at length:

http://www.postgresql.org/docs/9.0/interactive/functions-comparison.html

What page are you looking at?

> And indeed the parser does not generate warnings either.

It would be totally wrong for the parser to generate warnings about
correct behavior.

-Kevin


P.S. A copy/paste of part of the above-mentioned page:


To check whether a value is or is not null, use the constructs:

expression IS NULL
expression IS NOT NULL

or the equivalent, but nonstandard, constructs:

expression ISNULL
expression NOTNULL

Do not write expression = NULL because NULL is not "equal to" NULL.
(The null value represents an unknown value, and it is not known
whether two unknown values are equal.) This behavior conforms to the
SQL standard.

Tip: Some applications might expect that expression = NULL
returns true if expression evaluates to the null value. It is highly
recommended that these applications be modified to comply with the
SQL standard. However, if that cannot be done the
transform_null_equals configuration variable is available. If it is
enabled, PostgreSQL will convert x = NULL clauses to x IS NULL.

Note: If the expression is row-valued, then IS NULL is true when
the row expression itself is null or when all the row's fields are
null, while IS NOT NULL is true when the row expression itself is
non-null and all the row's fields are non-null. Because of this
behavior, IS NULL and IS NOT NULL do not always return inverse
results for row-valued expressions, i.e., a row-valued expression
that contains both NULL and non-null values will return false for
both tests. This definition conforms to the SQL standard, and is a
change from the inconsistent behavior exhibited by PostgreSQL
versions prior to 8.2.

Ordinary comparison operators yield null (signifying "unknown"), not
true or false, when either input is null. For example, 7 = NULL
yields null. When this behavior is not suitable, use the IS [ NOT ]
DISTINCT FROM constructs:

expression IS DISTINCT FROM expression
expression IS NOT DISTINCT FROM expression

For non-null inputs, IS DISTINCT FROM is the same as the <> operator.
However, if both inputs are null it returns false, and if only one
input is null it returns true. Similarly, IS NOT DISTINCT FROM is
identical to = for non-null inputs, but it returns true when both
inputs are null, and false when only one input is null. Thus, these
constructs effectively act as though null were a normal data value,
rather than "unknown".

Boolean values can also be tested using the constructs

expression IS TRUE
expression IS NOT TRUE
expression IS FALSE
expression IS NOT FALSE
expression IS UNKNOWN
expression IS NOT UNKNOWN

These will always return true or false, never a null value, even when
the operand is null. A null input is treated as the logical value
"unknown". Notice that IS UNKNOWN and IS NOT UNKNOWN are effectively
the same as IS NULL and IS NOT NULL, respectively, except that the
input expression must be of Boolean type.