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

On 7 Jun 2001, Tom Ivar Helbekkmo wrote:

> Zeugswetter Andreas SB <ZeugswetterA(at)wien(dot)spardat(dot)at> writes:
>
> > Actually I am not sure whether the column = NULL syntax is even defined
> > or allowed in SQL92 (e.g. Informix interprets the NULL as column name in
> > this context and errs out).
>
> He goes on to explain three-valued logic in more detail, showing truth
> tables according to Jan Lukasiewicz (the inventor of RPN), and says,
> of SQL-92, that it "is comforting to see that [it has] the same truth
> tables as the three-valued system of Lukasiewicz". Further, he says:
>
> SQL-92 added a new predicate of the form
>
> <search condition> IS [NOT] TRUE | FALSE | UNKNOWN
>
> which will let you map any combination of three-valued
> logic to the two Boolean values.
>
> 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. It will also
> return boolean truth values for comparisons with NULL values, using
> them as "real" data values in the comparison. Worse (IMHO), this is
> not consistent: while a test for "column = NULL" will return rows
> where that is true, and a test for "not column = NULL" will return the
> rest, "column <> NULL" returns no rows! This means that the theta
> operators are not all treated the same way, which is surely wrong!

That's the nature of the hack we're talking about. It's a grammar level
hack to turn a specific sequence of tokens (= NULL) into IS NULL due
to a client's generated queries. If you're comparing something other
than the constant NULL, it should do what is expected (ie, a comparison
between a NULL in a table or even CAST(NULL as INT4) does the "right"
thing).

I think adding IS UNKNOWN would probably be trivial (I think the code is
basically there in IS NULL.)