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

On 7 Jun 2001, Tom Ivar Helbekkmo wrote:

> Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.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.)