Weird NOT IN condition in SELECT (PostgreSQL 7.4.3 and 7.4.2 tested) - Mailing list pgsql-hackers

Hi,

I've following select, and I expect to receive a single record as result from it:

select c.id  from copie as c where c.enum=46857 andc.condizio_prestito = 'A' and        c.id not in (select id_copia
fromtesti_fermi_prenotati) andc.id not in (select id_copia from prestiti);
 

but it doesn't give me any records at all.


Following there is table descriptions:

CREATE TABLE copie (    id serial NOT NULL primary key,    enum integer,    id_biblioteca integer NOT NULL,    serie
text,   collocazione text,    note text,    condizio_prestito character(1) DEFAULT 'A'::bpchar,    ctime date DEFAULT
now(),   mtime date,    inventario integer
 
);

CREATE TABLE prestiti (    id serial NOT NULL,    inizio date DEFAULT now(),    id_libro text,    id_utente text,
finedate,    scadenza date NOT NULL,    stato smallint DEFAULT 1,    id_copia integer references copie(id)
 
);

CREATE TABLE testi_fermi_prenotati (    id_copia integer NOT NULL references copie(id),    id_prenotazione integer NOT
NULL,   fermato_il timestamp without time zone DEFAULT now() NOT NULL,    scadenza timestamp without time zone,
statocharacter(1) DEFAULT 'a'::bpchar
 
);

and some usefull query results:

prove=> select * from copie where enum=46857;  id   | enum  | id_biblioteca | serie | collocazione | note | 
condizio_prestito |   ctime    | mtime | inventario
-------+-------+---------------+-------+--------------+------+-------------------+------------+-------+------------
37163| 46857 |             1 |       | F RRN MAY    |      | A   | 2004-05-03 |       |      41576
 
(1 row)

(SEE THE FOLLOWING QUERY AND RESULT)
prove=> select * from prestiti where id_copia=37163; id | inizio | id_libro | id_utente | fine | scadenza | stato |
id_copia
----+--------+----------+-----------+------+----------+-------+----------
(0 rows)


prove=> select * from testi_fermi_prenotati where id_copia=37163; id_copia | id_prenotazione | fermato_il | scadenza |
stato
----------+-----------------+------------+----------+-------
(0 rows)


prove=> select c.id  from copie as c where c.enum=46857 andc.condizio_prestito = 'A' and        c.id not in (select
id_copiafrom testi_fermi_prenotati) andc.id not in (select id_copia from prestiti);
 

id
----
(0 rows)

prove=> select c.id  from copie as c where c.enum=46857 andc.condizio_prestito = 'A' and        c.id not in (select
id_copiafrom testi_fermi_prenotati);
 
id
------- 37163
(1 row)

prove=> select c.id  from copie as c where c.enum=46857 andc.condizio_prestito = 'A' and        c.id not in (select
id_copiafrom prestiti);
 
id
----
(0 rows)

prove=> explain analyze select c.id  from copie as c where c.enum=46857 andc.condizio_prestito = 'A' and        c.id
notin (select id_copia from testi_fermi_prenotati) andc.id not in (select id_copia from prestiti);
 
                                                     QUERY PLAN 

---------------------------------------------------------------------------------------------------------------------
SeqScan on copie c  (cost=0.00..14587.17 rows=1 width=4) (actual 
 
time=15.82..15.82 rows=0 loops=1)   Filter: ((enum = 46857) AND (condizio_prestito = 'A'::bpchar) AND (subplan) 
AND (subplan))   SubPlan     ->  Seq Scan on testi_fermi_prenotati  (cost=0.00..0.00 rows=1 width=4) 
(actual time=0.00..0.00 rows=0 loops=1)     ->  Seq Scan on prestiti  (cost=0.00..23.41 rows=1241 width=4) (actual 
time=0.01..10.21 rows=1241 loops=1) Total runtime: 15.95 msec
(6 rows)

prove=> explain select c.id  from copie as c where c.enum=46857 andc.condizio_prestito = 'A' and        c.id not in
(selectid_copia from testi_fermi_prenotati) andc.id not in (select id_copia from prestiti);
             QUERY PLAN 
 

---------------------------------------------------------------------------------------------- Seq Scan on copie c
(cost=0.00..14587.17rows=1 width=4)   Filter: ((enum = 46857) AND (condizio_prestito = 'A'::bpchar) AND (subplan) 
 
AND (subplan))   SubPlan     ->  Seq Scan on testi_fermi_prenotati  (cost=0.00..0.00 rows=1 width=4)     ->  Seq Scan
onprestiti  (cost=0.00..23.41 rows=1241 width=4)
 
(5 rows)


If you want to play with these data:
http://diff.homeunix.net/anomalia.sql.gz

then

gzip -d anomalias.sql
createdb anydb
psql -f anomalia.sql anydb


Best wishes,              \ferz