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

Re: "WHERE col NOT IN" yields falsely empty result.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Martin(dot)J(dot)Carter(at)nottingham(dot)ac(dot)uk, pgsql-bugs(at)postgresql(dot)org
Subject: Re: "WHERE col NOT IN" yields falsely empty result.
Date: 2001-06-13 14:15:08
Message-ID: 26161.992441708@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
pgsql-bugs(at)postgresql(dot)org writes:
> Since the difference set bar - foo is nonempty, the above
> should yield one or more rows.  However, the presence of a null
> in foo.col1 (tests 3 and 8 below) yields zero rows, even where
> the difference set has rows with no null entries (see test 2).

This is the spec-mandated behavior.  Think of NULL as "I don't know what
this value is".  Unless you get a match to one of the non-null outputs
of the subselect (in which case you can definitely say that the test
value *is* IN the subselect), you are forced to conclude that you don't
know for sure whether the test value is in the set or not.  Accordingly,
NOT IN will always yield either FALSE or NULL in this situation.

> Am I missing something obvious? eg in one of the FAQs?

If it isn't in the FAQ, it probably should be ...

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: David M. RichterDate: 2001-06-13 14:24:01
Subject: POSTGRES 7.1.2 upgrade on a IRIX 6.4 System
Previous:From: Basil A. EvseenkoDate: 2001-06-13 14:01:48
Subject: Strange CREATE VIEW behavior??

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