Skip site navigation (1) Skip section navigation (2)

Re: NULLS and <> : Discrepancies ?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
Cc: "Emmanuel Charpentier,,," <charpent(at)bacbuc(dot)dyndns(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: NULLS and <> : Discrepancies ?
Date: 2000-12-30 00:29:55
Message-ID: 12719.978136195@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
>> Could someone explain to me why not eliminating nulls destroys the
>> potential results of the query ? In other words, for any X not null, X
>> not in (some NULLs) is false.

> You already know the answer: comparisons to NULL always evaluate to
> false.

Thomas, I'm surprised at you!  Comparisons to NULL do not yield false,
they yield NULL.

So, given

	foo NOT IN (bar, NULL)

we may rewrite this as

	NOT (foo IN (bar, NULL))

	NOT (foo = bar OR foo = NULL)

	NOT (false OR NULL)

	NOT (NULL)

	NULL

On the other hand

	foo NOT IN (foo, NULL)

	NOT (foo IN (foo, NULL))

	NOT (foo = foo OR foo = NULL)

	NOT (true OR NULL)

	NOT (true)

	false

So the correct statement of the behavior is that the result of NOT IN is
always either FALSE or NULL if there are any NULLs involved.  This is
perfectly correct if you recall the interpretation of NULL as "don't
know".  The truth value of "foo = NULL" is not FALSE, it is UNKNOWN,
because you don't know what the NULL is ... it could be foo.

It happens that WHERE treats a NULL condition result the same as FALSE,
ie don't select the row, but they are not the same thing.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: The Hermit HackerDate: 2000-12-30 00:39:57
Subject: Re: GNU readline and BSD license
Previous:From: Tom LaneDate: 2000-12-30 00:16:49
Subject: Re: GNU readline and BSD license

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group