From: | Joe Maldonado <joe(dot)maldonado(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | not quite expected behaviour when using IN clause |
Date: | 2005-09-30 19:27:14 |
Message-ID: | 824355640509301227o4074a03cpeb8e7ef0ee5fddca@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello all,
I apologize for the wide distribution but we recently ran into an
interesting behaviour using PostgreSQL 8.0.3 and did not know whether this
was a bug or intended behaviour.
When an IN clause contains a NULL value the entire in clause is considered
as being false, thus no records are returned.
Why doesn't IN evaluate NULL as a value?
so for example:
SELECT count(*) FROM test WHERE key NOT IN ('something');
returns the count of rows...
where
SELECT count(*) FROM test WHERE key NOT IN ('something', NULL);
does not. table test does not have any NULL values in the key column.
the query plans follow...
mazu=# EXPLAIN ANALYZE SELECT count(*) FROM test WHERE key NOT IN
('something');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=100000022.44..100000022.44 rows=1 width=0) (actual time=
0.664..0.665 rows=1 loops=1)
-> Seq Scan on test (cost=100000000.00..100000020.38 rows=826 width=0)
(actual time=0.030..0.349 rows=168 loops=1)
Filter: (("key")::text <> 'something'::text)
Total runtime: 0.826 ms
(4 rows)
mazu=# EXPLAIN ANALYZE SELECT count(*) FROM test WHERE key NOT IN
('something', NULL);
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Aggregate (cost=100000022.44..100000022.44 rows=1 width=0) (actual time=
0.027..0.029 rows=1 loops=1)
-> Result (cost=100000000.00..100000020.38 rows=826 width=0) (actual time=
0.002..0.002 rows=0 loops=1)
One-Time Filter: NULL::boolean
-> Seq Scan on test (cost=100000000.00..100000020.38 rows=826 width=0)
(never executed)
Filter: (("key")::text <> 'something'::text)
Total runtime: 0.110 ms
(6 rows)
--
Joe Maldonado
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2005-09-30 19:34:59 | Re: database bloat, but vacuums are done, and fsm seems to be setup ok |
Previous Message | Mike Nolan | 2005-09-30 19:19:00 | Re: Help with inventory control |