BUG #1921: NULL<>NULL ?!?!?!?!?!?!?

BogDan Vatra wrote:

The following bug has been logged online:

Bug reference: 1921
Logged by: BogDan Vatra
Email address: bog_dan_ro@yahoo.com
PostgreSQL version: 8.0.3
Operating system: win xp pro
Description: NULL<>NULL ?!?!?!?!?!?!?
Details:

Please execute this query:

create table test (id serial, tt varchar(50));
insert into test (tt) values(NULL);
select tt from test where tt=NULL;

This should return 1 row but ...
An then try

select tt from test where tt<>NULL;

0 row(s) !!!

This is not a bug. If you want find all values with that
field null you have to do:

select tt from test where tt is null;

And I can assure you this is what the standard say.

Regards
Gaetano Mendola

Hi,

The SQL standard requires that null values do not equate any value,
including themselves.

Corrected syntax of your queries is shown below.

If I recall it correctly, this nehaviour was enforced in the 7.2->7.3 or
7.1->7.2 evolution.

If for instance a char(1) field called 'myfield' admits several values
rangeing from 'A' to 'I' and null, extracting all rows where char is neither
'C' or 'F' should imply the following where-clause

... where myfield is null or myfield is not in ('C','F')

A cluase like "where myfield is not in ('C','F')" would not extract all rows
where myfield is null.

Hope this helps

Regards
J6M

----- Original Message -----
From: "BogDan Vatra" <bog_dan_ro@yahoo.com>
To: <pgsql-bugs@postgresql.org>
Sent: Thursday, September 29, 2005 10:38 AM
Subject: [BUGS] BUG #1921: NULL<>NULL ?!?!?!?!?!?!?

create table test (id serial, tt varchar(50));
insert into test (tt) values(NULL);
select tt from test where tt=NULL;

select tt from test where tt is null ;

select tt from test where tt<>NULL;

select tt from test where tt is not null ;

On Thu, Sep 29, 2005 at 09:38:54AM +0100, BogDan Vatra wrote:

The following bug has been logged online:

Bug reference: 1921
Logged by: BogDan Vatra
Email address: bog_dan_ro@yahoo.com
PostgreSQL version: 8.0.3
Operating system: win xp pro
Description: NULL<>NULL ?!?!?!?!?!?!?
Details:

Please execute this query:

create table test (id serial, tt varchar(50));
insert into test (tt) values(NULL);
select tt from test where tt=NULL;

This should return 1 row but ...
An then try

select tt from test where tt<>NULL;

0 row(s) !!!

It is not a bug. NULL isn't a datum. It's a state of ignorance. If
I don't know what your birthday is and I don't know what your
coworker's birthday is, I also don't know whether they are equal, and
so the only right answer to "Is BogDan's birthday unequal to his
coworker's?" is "I don't know."

HTH :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!