[HACKERS] Weird NOT IN condition in SELECT (PostgreSQL 7.4.3 and 7.4.2 tested)

Wed, 23 Jun 2004 07:34:01 -0700 

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 and
        c.condizio_prestito = 'A' and
        c.id not in (select id_copia from testi_fermi_prenotati) and
        c.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,
    fine date,
    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,
    stato character(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 and
        c.condizio_prestito = 'A' and
        c.id not in (select id_copia from testi_fermi_prenotati) and
        c.id not in (select id_copia from prestiti);

id
----
(0 rows)

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

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

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

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------
Seq Scan 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 and
c.condizio_prestito = 'A' and
c.id not in (select id_copia from testi_fermi_prenotati) and
c.id not in (select id_copia from prestiti);
QUERY PLAN

----------------------------------------------------------------------------------------------
Seq Scan on copie c (cost=0.00..14587.17 rows=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 on prestiti (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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly

Reply via email to