Re: Can't use NULL in IN conditional?

From: "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, ctassell(at)isn(dot)net, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Can't use NULL in IN conditional?
Date: 2000-12-11 17:01:40
Message-ID: 0012111201401S.00289@comptechnews
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Monday 11 December 2000 10:51, Tom Lane wrote:
> pgsql-bugs(at)postgresql(dot)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(at)comptechnews(dot)com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Vasko Miroslav 2000-12-11 17:30:27 obsolete version of python bindings
Previous Message Piers Scannell 2000-12-11 16:18:26 RE: Can't use NULL in IN conditional?