Thread

1. Weird NOT IN condition in SELECT (PostgreSQL 7.4.3 and 7.4.2 tested)

   Ferruccio Zamuner <nonsolosoft@diff.org> — 2004-06-23T14:21:28Z

   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
   
   

2. Re: Weird NOT IN condition in SELECT (PostgreSQL 7.4.3

   Richard Huxton <dev@archonet.com> — 2004-06-23T14:38:53Z

   Ferruccio Zamuner wrote:
   > 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,
   
   > CREATE TABLE prestiti (
   >     id_copia integer references copie(id)
   
   > CREATE TABLE testi_fermi_prenotati (
   >     id_copia integer NOT NULL references copie(id),
   
   Are you sure you don't have any null values in prestiti.id_copia?
   
   -- 
      Richard Huxton
      Archonet Ltd
   
   

3. Re: Weird NOT IN condition in SELECT (PostgreSQL

   Ferruccio Zamuner <nonsolosoft@diff.org> — 2004-06-23T15:04:48Z

   Richard Huxton said:
   > Ferruccio Zamuner wrote:
   >> 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.
   
   >> CREATE TABLE prestiti (
   >>     id_copia integer references copie(id)
   >
   >> CREATE TABLE testi_fermi_prenotati (
   >>     id_copia integer NOT NULL references copie(id),
   >
   > Are you sure you don't have any null values in prestiti.id_copia?
   
   Yes, there is a null value and it has not to be there.
   Thank you.
   
   Bye,                        \ferz