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