From: Tom Lane <tgl@sss.pgh.pa.us>
To: jd@commandprompt.com
Cc: Scott Whitney <swhitney@journyx.com>
Cc: 'Hoover, Jeffrey' <jhoover@jcvi.org>
Cc: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>
Cc: pgsql-admin@postgresql.org
Subject: Re: Strange query problem...
Date: Wed, 28 Jan 2009 14:49:52 -0500
Message-ID: <21337.1233172192@sss.pgh.pa.us> (raw)
In-Reply-To: <1233168415.10539.2.camel@jd-laptop.pragmaticzealot.org>
References: <20090128183715.A03677E4E79@mail.int.journyx.com>
<1233168415.10539.2.camel@jd-laptop.pragmaticzealot.org>
"Joshua D. Drake" <jd@commandprompt.com> writes:
> This looks like a NULL vs '' issue. Am I wrong?
No, it's a NULL vs NOT IN issue. Specifically, if the subquery yields
any NULLs and the comparison operator is strict (which nearly all are)
then it's impossible to get a TRUE result from the NOT IN --- the only
possibilities are FALSE (if a match is found among the non-nulls)
or NULL (if not). Standard gotcha for newbie SQL coders.
My recommendation is to use NOT EXISTS instead; it's got less surprising
semantics (and, as of 8.4, it'll get optimized significantly better than
NOT IN).
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, jd@commandprompt.com, swhitney@journyx.com, jhoover@jcvi.org, Kevin.Grittner@wicourts.gov
Subject: Re: Strange query problem...
In-Reply-To: <21337.1233172192@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox