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

Wed, 23 Jun 2004 16:21:28 +0200
Ferruccio Zamuner <nonsolosoft@diff.org>
Weird NOT IN condition in SELECT (PostgreSQL 7.4.3 and 7.4.2 tested)
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

Wed, 23 Jun 2004 15:38:53 +0100
Richard Huxton <dev@archonet.com>
Re: Weird NOT IN condition in SELECT (PostgreSQL 7.4.3
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

Wed, 23 Jun 2004 10:04:48 -0500 (CDT)
"Ferruccio Zamuner" <nonsolosoft@diff.org>
Re: Weird NOT IN condition in SELECT (PostgreSQL 