Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
"BogDan Vatra"
Date:
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) !!!

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

From
Gaetano Mendola
Date:
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

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

From
"j6m"
Date:
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 ;

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

From
David Fetter
Date:
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!