pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWeird NOT IN effect with NULL values
8+ messages / 7 participants
[nested] [flat]
* Weird NOT IN effect with NULL values
@ 2001-03-01 18:16 Frank Joerdens <frank@joerdens.de>
2001-03-01 18:32 ` Re: Weird NOT IN effect with NULL values Josh Berkus <josh@agliodbs.com>
2001-03-01 18:32 ` Re: Weird NOT IN effect with NULL values Ken Kline <ken@oldbs.com>
2001-03-01 18:42 ` Re: Weird NOT IN effect with NULL values Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-01 18:56 ` Re: Weird NOT IN effect with NULL values Tom Lane <tgl@sss.pgh.pa.us>
2001-03-01 18:59 ` Re: Weird NOT IN effect with NULL values Peter Eisentraut <peter_e@gmx.net>
2001-03-01 19:10 ` Re: Weird NOT IN effect with NULL values Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 6 replies; 8+ messages in thread
From: Frank Joerdens @ 2001-03-01 18:16 UTC (permalink / raw)
To: pgsql-sql
When doing a subselect with NOT IN, as in
SELECT name
FROM customer
WHERE customer_id NOT IN (
SELECT customer_id
FROM salesorder
);
(from Bruce Momjian's book)
I get no rows if the result column returned by the subselect
contains NULL values. It works as expected if I remove the NULL values
from the result set. Is this behaviour correct and if so, why?
I am using 7.1 beta 4.
Regards, Frank
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird NOT IN effect with NULL values
2001-03-01 18:16 Weird NOT IN effect with NULL values Frank Joerdens <frank@joerdens.de>
@ 2001-03-01 18:32 ` Josh Berkus <josh@agliodbs.com>
2001-03-01 20:46 ` Re: Weird NOT IN effect with NULL values Bruce Momjian <pgman@candle.pha.pa.us>
5 siblings, 1 reply; 8+ messages in thread
From: Josh Berkus @ 2001-03-01 18:32 UTC (permalink / raw)
To: pgsql-sql
Mr. Joerdens,
> I get no rows if the result column returned by the subselect
> contains NULL values. It works as expected if I remove the NULL values
> from the result set. Is this behaviour correct and if so, why?
I can see how that bug would happen. You may want to forward your
e-mail to pgsql-bugs.
Regardless, you'll find that you get faster results (as well as avoiding
the NULL bug) if you use the following form of the query:
SELECT name
FROM customer
WHERE NOT EXISTS (
SELECT customer_id
FROM salesorder
WHERE customer_id = customer.customer_id
);
Bruce, you may want to consider editing your next edition to include the
above modification. WHERE ... NOT IN is a bad idea for any subselect on
medium-large tables.
-Josh Berkus
--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird NOT IN effect with NULL values
2001-03-01 18:16 Weird NOT IN effect with NULL values Frank Joerdens <frank@joerdens.de>
2001-03-01 18:32 ` Re: Weird NOT IN effect with NULL values Josh Berkus <josh@agliodbs.com>
@ 2001-03-01 20:46 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Bruce Momjian @ 2001-03-01 20:46 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
> SELECT name
> FROM customer
> WHERE NOT EXISTS (
> SELECT customer_id
> FROM salesorder
> WHERE customer_id = customer.customer_id
> );
>
> Bruce, you may want to consider editing your next edition to include the
> above modification. WHERE ... NOT IN is a bad idea for any subselect on
> medium-large tables.
FAQ item mentions this, and section 8.2 shows eqivalency at the end of
the section.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird NOT IN effect with NULL values
2001-03-01 18:16 Weird NOT IN effect with NULL values Frank Joerdens <frank@joerdens.de>
@ 2001-03-01 18:32 ` Ken Kline <ken@oldbs.com>
5 siblings, 0 replies; 8+ messages in thread
From: Ken Kline @ 2001-03-01 18:32 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql
this is kind of weird but it is how it works.
You cannot use equality for null...
Null does not equal Null
Null means no value, since it's not a value
it can't equal anything another no value.
SELECT name
FROM customer
WHERE customer_id NOT IN
(
SELECT customer_id
FROM salesorder
)
and customer_id is not null;
should work
Ken
Frank Joerdens wrote:
> When doing a subselect with NOT IN, as in
>
> SELECT name
> FROM customer
> WHERE customer_id NOT IN (
> SELECT customer_id
> FROM salesorder
> );
>
> (from Bruce Momjian's book)
>
> I get no rows if the result column returned by the subselect
> contains NULL values. It works as expected if I remove the NULL values
> from the result set. Is this behaviour correct and if so, why?
>
> I am using 7.1 beta 4.
>
> Regards, Frank
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird NOT IN effect with NULL values
2001-03-01 18:16 Weird NOT IN effect with NULL values Frank Joerdens <frank@joerdens.de>
@ 2001-03-01 18:42 ` Bruce Momjian <pgman@candle.pha.pa.us>
5 siblings, 0 replies; 8+ messages in thread
From: Bruce Momjian @ 2001-03-01 18:42 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql
> When doing a subselect with NOT IN, as in
>
> SELECT name
> FROM customer
> WHERE customer_id NOT IN (
> SELECT customer_id
> FROM salesorder
> );
>
> (from Bruce Momjian's book)
>
> I get no rows if the result column returned by the subselect
> contains NULL values. It works as expected if I remove the NULL values
> from the result set. Is this behaviour correct and if so, why?
>
> I am using 7.1 beta 4.
Read more in the book. It covers subqueries with nulls, bottom of pages
96. Not sure about web URL but it is in the subqueries section titled
"NOT IN and Subqueries with NULL Values".
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird NOT IN effect with NULL values
2001-03-01 18:16 Weird NOT IN effect with NULL values Frank Joerdens <frank@joerdens.de>
@ 2001-03-01 18:56 ` Tom Lane <tgl@sss.pgh.pa.us>
5 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-03-01 18:56 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql
Frank Joerdens <frank@joerdens.de> writes:
> When doing a subselect with NOT IN, as in
> SELECT name
> FROM customer
> WHERE customer_id NOT IN (
> SELECT customer_id
> FROM salesorder
> );
> I get no rows if the result column returned by the subselect
> contains NULL values. It works as expected if I remove the NULL values
> from the result set. Is this behaviour correct and if so, why?
This is correct because of SQL's 3-valued boolean logic. SQL92 defines
"A NOT IN B" as equivalent to "NOT (A = SOME B)", and the latter has
the rules
c) If the implied <comparison predicate> is true for at least
one row RT in T, then "R <comp op> <some> T" is true.
d) If T is empty or if the implied <comparison predicate> is
false for every row RT in T, then "R <comp op> <some> T" is
false.
e) If "R <comp op> <quantifier> T" is neither true nor false,
then it is unknown.
Now the implied comparison will generate TRUE for the subselect rows
that contain a matching customer_id, FALSE for the rows that contain
non-matching (but not null) customer_id, and UNKNOWN (null) for the
rows that contain nulls. So if you have nulls then case (d) never
holds: the result of A = SOME B is either true or unknown. And so
the result of NOT IN is either false or unknown, and either way the
outer WHERE fails.
This can be justified intuitively if you consider that null means
"don't know": you can say for sure that the target customer_id IS in
the subselect if you find it there, but you can't say for sure that it
IS NOT there, because you don't know all the subselect result elements.
Bottom line: you probably want to suppress nulls in the subselect...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird NOT IN effect with NULL values
2001-03-01 18:16 Weird NOT IN effect with NULL values Frank Joerdens <frank@joerdens.de>
@ 2001-03-01 18:59 ` Peter Eisentraut <peter_e@gmx.net>
5 siblings, 0 replies; 8+ messages in thread
From: Peter Eisentraut @ 2001-03-01 18:59 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql
Frank Joerdens writes:
> When doing a subselect with NOT IN, as in
>
> SELECT name
> >FROM customer
> WHERE customer_id NOT IN (
> SELECT customer_id
> >FROM salesorder
> );
>
> (from Bruce Momjian's book)
>
> I get no rows if the result column returned by the subselect
> contains NULL values. It works as expected if I remove the NULL values
> >from the result set. Is this behaviour correct and if so, why?
It is correct.
customer_id NOT IN (value1, value2, value3, ...)
(which is what the subselect would essentially resolve to) is equivalent
to
NOT (customer_id = value1 OR customer_id = value2 OR customer_id = value3 ...)
Say value2 is NULL. Then we have
NOT (customer_id = value1 OR customer_id = NULL OR customer_id = value3 ...)
NOT (customer_id = value1 OR NULL OR customer_id = value3 ...)
NOT (NULL)
NULL
which means FALSE in a WHERE condition, so no rows are returned. Note
that 'xxx = NULL' is different from 'xxx IS NULL'. Also note that NULL is
not the same as FALSE in general.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Weird NOT IN effect with NULL values
2001-03-01 18:16 Weird NOT IN effect with NULL values Frank Joerdens <frank@joerdens.de>
@ 2001-03-01 19:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
5 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2001-03-01 19:10 UTC (permalink / raw)
To: Frank Joerdens <frank@joerdens.de>; +Cc: pgsql-sql
On Thu, 1 Mar 2001, Frank Joerdens wrote:
> When doing a subselect with NOT IN, as in
>
> SELECT name
> FROM customer
> WHERE customer_id NOT IN (
> SELECT customer_id
> FROM salesorder
> );
>
> (from Bruce Momjian's book)
>
> I get no rows if the result column returned by the subselect
> contains NULL values. It works as expected if I remove the NULL values
> from the result set. Is this behaviour correct and if so, why?
>
> I am using 7.1 beta 4.
I believe it may be actually correct. If my reading of the spec is
correct (which it possibly is not), customer_id NOT IN (subselect) is
effectively, NOT ( customer_id = ANY (subselect) ) and then:
Using the rules for ANY,
If customer_id=<inner customer_id> for at least one row, IN returns true
so NOT IN returns false.
If customer_id=<inner customer_id> is false for every row, IN returns
false so NOT IN returns true.
Otherwise IN and NOT IN both return unknown.
Since customer_id=NULL is unknown, you're getting at least one unknown in
the ANY expression so NOT IN doesn't return true, it returns unknown
which is not sufficient for making the where clause return the row.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2001-03-01 20:46 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-01 18:16 Weird NOT IN effect with NULL values Frank Joerdens <frank@joerdens.de>
2001-03-01 18:32 ` Josh Berkus <josh@agliodbs.com>
2001-03-01 20:46 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-01 18:32 ` Ken Kline <ken@oldbs.com>
2001-03-01 18:42 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-03-01 18:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-01 18:59 ` Peter Eisentraut <peter_e@gmx.net>
2001-03-01 19:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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