Bug #638: Buggy select statment with numeric

A select statement with a where clause on a numeric column tested
for equality against null always return empty result.

This is correct behavior per SQL9x standards. NULL is *not* a value, but
represents the absense of information so tests FALSE in comparisons of
any kind.

hth

- Thomas

On Mon, 22 Apr 2002 pgsql-bugs@postgresql.org wrote:

Florian Steffen (mailing-list@urbanet.ch) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Buggy select statment with numeric

Long Description
A select statement with a where clause on a numeric column tested
for equality against null always return empty result. With ISNULL
everything is fine, but not with the = operator.

This is correct behavior. NULL=NULL is unknown not true. Older versions
had a hack to make an explicit =NULL the same as IS NULL for certain
clients that emitted such statements when they meant IS NULL. This is now
off by default but can be turned on using the set variable
TRANSFORM_NULL_EQUALS.

pgsql-bugs@postgresql.org writes:

select * from t where n ISNULL;

n | s
---+--------------
| asdasdasd
| qqqqqqqqqqqq
(2 rows)

select * from t where n=NULL;

n | s
---+---
(0 rows)

This is not a bug.

However, you can make 7.2 work in that braindead Microsoft-compatible
style if you wish to. See the transform_null_equals parameter.

regards, tom lane