Re: Can't use NULL in IN conditional?

On Monday 11 December 2000 10:51, Tom Lane wrote:
> pgsql-bugs@postgresql.org writes:
> > -- This works
> > SELECT type_id, code FROM product_types WHERE code = '0A' OR code = NULL;
> > -- This doesn't
> > SELECT type_id, code FROM product_types WHERE code IN ('OA', NULL);
>
> "code = NULL" is not legal SQL --- or at least, the standard's
> interpretation of it is not what you appear to expect.  According to the
> spec the result must always be NULL, which is effectively FALSE in this
> context.
>
> Since certain Microsoft products misinterpret "var = NULL" as "var IS
> NULL", we've inserted a hack into our parser to convert a comparison
> against a literal NULL to an IS NULL clause.  However, that only works for
> the specific cases of "var = NULL" and "var <> NULL", not for any other
> contexts where a null might be compared against something else.
>
> Personally I regard this hack as a bad idea, and would prefer to take it
> out.  I'd certainly resist extending it to the IN operator...
>
>             regards, tom lane

What you are saying agrees with things I've read elsewhere, and a little
definition/note that I wrote on my "Databasing" Terms page:

three-valued logic:
    a logic system that employs TRUE, FALSE, and UNKNOWN. NULL values introduce
UNKNOWN into boolean operations. A truth table must be used to lookup the
proper value (TRUE or FALSE) of UNKNOWN under specific operations. In SQL
implementations that use three-valued logic, you must consult the
documentation for its truth table. 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.  (any additions/corrections to this definition/note will be happily
considered)

I think Bruce Momjian's book says this too:
http://www.postgresql.org/docs/aw_pgsql_book/node45.html
(that book is really useful!)

--
-------- 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/ ------------