Re: AW: Re: [SQL] behavior of ' = NULL' vs. MySQL vs. Stand ards

Tom Ivar Helbekkmo <tih@kpnQwest.no> quotes:
>         ... This results in
>         a three-valued logic, which has an UNKNOWN in addition
>         to TRUE and FALSE.  [...]  UNKNOWN is a logical value and
>         not the same as a NULL, which is a data value.

SQL92 is not very clear about whether NULL and UNKNOWN are distinct,
but it is worth noticing that their truth tables for comparison
operators, and/or/not, etc, only mention unknown --- never null ---
as a possible value of a boolean condition.  SQL99 clarifies the
intent:
        The data type boolean comprises the distinct truth values true and        false. Unless prohibited by a NOT
NULLconstraint, the boolean        data type also supports the unknown truth value as the null value.        This
specificationdoes not make a distinction between the null        value of the boolean data type and the unknown truth
valuethat is        the result of an SQL <predicate>, <search condition>, or <boolean        value expression>; they
maybe used interchangeably to mean exactly        the same thing.
 

Which in fact is what Postgres does.

> A quick test run with psql shows that PostgreSQL does not properly
> implement three-valued logic: it does not recognize the UNKNOWN
> keyword alongside TRUE and FALSE, in any situation.

We do not currently have correct implementations of IS TRUE, IS FALSE,
or IS UNKNOWN (IS TRUE/FALSE are in there but give the wrong result
for null inputs).  This is on my to-do list to fix; not sure if the
master TODO list mentions it or not.  Actually it'd be a good project
for a newbie hacker who wants to learn about the backend's
expression-handling machinery.  Anyone want to take it on?

It's also worth noticing that our implementation of IS NULL isn't really
up to speed: the spec allows the argument to be a row value constructor,
not just a scalar.  But we mostly don't have support for row-value-
constructor expressions anyway (it's not an Entry SQL feature).
        regards, tom lane