Re: Can't use NULL in IN conditional? - Mailing list 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@comptechnews.com ---------
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------