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

Tom Ivar Helbekkmo <tih(at)kpnQwest(dot)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 NULL constraint, the boolean
data type also supports the unknown truth value as the null value.
This specification does not make a distinction between the null
value of the boolean data type and the unknown truth value that is
the result of an SQL <predicate>, <search condition>, or <boolean
value expression>; they may be 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