Thread: Bug #638: Buggy select statment with numeric

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 has been tested on versions 7.1.3 and 7.2.

Sample Code
> create table t (n NUMERIC(39), s TEXT);
CREATE
> insert into t values (NULL, 'asdasdasd');
INSERT 35860 1
> insert into t values (NULL, 'qqqqqqqqqqqq');
INSERT 35861 1
> select * from t where n ISNULL;
 n |      s
---+--------------
   | asdasdasd
   | qqqqqqqqqqqq
(2 rows)

> select * from t where n=NULL;
 n | s
---+---
(0 rows)


No file was uploaded with this report

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

> 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