Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group