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

Re: Can't use NULL in IN conditional?

From: "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: ctassell(at)isn(dot)net, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Can't use NULL in IN conditional?
Date: 2000-12-12 05:01:28
Message-ID: 0012120001281T.00289@comptechnews (view raw or flat)
Thread:
Lists: pgsql-bugs
On Monday 11 December 2000 12:34, Tom Lane wrote:
> Three-valued logic is perfectly straightforward if you keep in mind the
> interpretation of NULL/UNKNOWN: "I don't know if this is true or false".
> Thus:
>
> 	NOT unknown => unknown
>
> 	false AND unknown => false   (it can't possibly be true)
> 	true AND unknown => unknown
> 	unknown AND unknown => unknown
>
> 	false OR unknown => unknown
> 	true OR unknown => true   (it's true no matter what the unknown is)
> 	unknown OR unknown => unknown
>
> For ordinary operators such as "=", the result is generally NULL if any
> input is NULL, although there are some specific cases where you can
> deduce a correct result knowing only some of the inputs.  In particular,
> NULL = NULL does not yield TRUE, it yields UNKNOWN.  This is correct
> when you consider that NULL is not a specific value, but a placeholder
> for an unknown value.  (Reference: SQL99 section 8.2 general rule 1a.)
>
> IS NULL and IS NOT NULL are not ordinary operators in this sense, since
> they can deliver a non-null result for NULL input.
>
> Also, SQL specifies that a WHERE clause that evaluates to "unknown" is
> taken as false, ie, the row is not selected.
>
> Bottom line is that in a spec-conformant implementation,
> 	WHERE code = '0A' OR code = NULL
> will act the same as if you'd just written "WHERE code = '0A'"; the
> second clause always yields unknown and so can never cause the WHERE to
> be taken as true.
>
> > Some newer implementations of SQL
> > eliminate UNKNOWN, and may generally behave as follows: all boolean tests
> > involving NULL return FALSE except the explicit test IS NULL, e.g., if
> > NULL is a possibility, it has to be tested for explicity using IS NULL or
> > IS NOT NULL.
>
> They may *appear* to return FALSE if you aren't looking too closely,
> since WHERE treats top-level results of FALSE and UNKNOWN the same.
> If they really don't make the distinction then they are broken.
> AFAICT, neither SQL92 nor SQL99 regard NULL support as optional.
>
> 			regards, tom lane

Thanks for the clarification and SQL reference.  I spent some time on this 
today and updated a file of mine at

http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic

to take into consideration these things.  If you do take a look at it and 
find an error, I will fix it.  This dbdesign.html file is a file linked to 
from http://postgresql.readysetnet.com/docs/faq-english.html so I'm hoping to 
keep it correct and useful.  Thanks :)


-- 
-------- Robert B. Easter  reaster(at)comptechnews(dot)com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------

In response to

pgsql-bugs by date

Next:From: Dronamraju RajeshDate: 2000-12-12 05:55:52
Subject: LockReplace: xid table corrupted
Previous:From: Markus BrachnerDate: 2000-12-11 21:32:36
Subject: PostgreSQL 7.0.3 freeze

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