Select .... where id not in (....) returns 0 incorrectly

["J. Roeleveld" <joost@xxxxxxxxxxxx>]

Hi all,

While trying to write some queries to clean up records I no longer need, I 
come up with a very strange situation where numbers literally don't add up as 
expected.

If anyone can help me trace where this strange behaviour is coming from and 
how to resolve it, please let me know.
In case this is already resolved in a recent version, I am currently using 
version 11.14.

Please see the following log from psql:

joost=> create temporary table q ( id integer );
CREATE TABLE
joost=> insert into q ( id ) select snapshotlistid from backupitem;
INSERT 0 765
joost=> insert into q ( id ) select snapshotlistid from queue;
INSERT 0 3183

joost=> select count(1) from q;
 count 
-------
  3948
(1 row)

joost=> select count(1) from snapshotlist where id in (select id from q);
 count 
-------
  1810
(1 row)

joost=> select count(1) from snapshotlist where id not in (select id from q);
 count 
-------
     0
(1 row)

joost=> select count(1) from snapshotlist where id not in (select id from 
snapshotlist where id in (select id from q));
  count  
---------
 2293923
(1 row)


The tables are defined like: (Note, I did remove some fields from the tables 
which have no impact. Most are foreign keys to further tables or varchar data 
fields)

CREATE TABLE snapshotlist (
  id SERIAL PRIMARY KEY,
  active boolean,
  created TIMESTAMP DEFAULT clock_timestamp(),
  modified TIMESTAMP
);

CREATE TABLE queue (
  id SERIAL PRIMARY KEY,
  queuetask VARCHAR(500) NOT NULL,
  snapshotlistid INTEGER REFERENCES snapshotlist(id) ON DELETE RESTRICT,
  uuid uuid NOT NULL,
  UNIQUE(uuid)
);

CREATE TABLE backupitem (
  id SERIAL PRIMARY KEY,
  snapshotlistid INTEGER REFERENCES snapshotlist(id) ON DELETE RESTRICT NOT 
NULL,
  UNIQUE(snapshotlistid)
);