Thread: not quite expected behaviour when using IN clause

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

On Fri, 30 Sep 2005, Joe Maldonado wrote:

> 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.

RVC NOT IN IPV is described as NOT(RVC IN IPV) which turns into
NOT(RVC = ANY IPV)

= ANY does the following:
           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.

So, for key NOT IN ('something', NULL) there are two cases,
 key = 'something', in which case c applies and IN would be true and NOT
  IN false so the row doesn't get returned
 key <> 'something', in which case key = 'something' is false and key=NULL
  is unknown, so e applies and IN is unknown and NOT IN is unknown so the
  row doesn't get returned.