Re: strange behaviour - Mailing list pgsql-general

From Peter Eisentraut
Subject Re: strange behaviour
Date
Msg-id Pine.LNX.4.30.0102251430160.752-100000@peter.localdomain
Whole thread Raw
In response to strange behaviour  (Mathieu Arnold <mat@mat.cc>)
List pgsql-general
Mathieu Arnold writes:

> sympa=> select count(*) from subscriber_table;
>   count
> -------
>   14029
> sympa=> select count(*) from subscriber_table where bounce_subscriber = NULL;
>   count
> -------
>   14024
> sympa=> select count(*) from subscriber_table where bounce_subscriber <> NULL;
>   count
> -------
>       0

'anything <> NULL' can never be true, because 'anything {operator} NULL'
is always NULL, which means false in a WHERE clause.  The reason that
'anything = NULL' works is that it is explicitly handled to work around MS
Access breakage.  What you want to use is 'xxx IS NULL' and 'xxx IS NOT
NULL'.

> sympa=> select count(*) from subscriber_table where bounce_subscriber = '';
>   count
> -------
>       0
> sympa=> select count(*) from subscriber_table where bounce_subscriber <> '';
>   count
> -------
>       5
>
> I was thinking that "= NULL" and "<> NULL" were oposite and that "<> NULL"
> would give me the result i had with "<> ''" or am I mistaken ?

NULL and '' are not the same, in spite of anything Oracle is trying to
tell you.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


pgsql-general by date:

Previous
From: Mathieu Arnold
Date:
Subject: strange behaviour
Next
From: Uro¹ Gruber
Date:
Subject: PHP4.0.4pl1 and PostgreSQL 7.1beta4