BUG #18766: not exists sometimes gives too few records - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18766
Logged by:          Jan Kort
Email address:      jan.kort@genetics.nl
PostgreSQL version: 16.2
Operating system:   Windows
Description:

Hi,

Unfortunately I can't reproduce this problem in a script yet, it has
occurred in 2 different databases out of 100 so far and only in specific
queries. When it does occur on a specific database it keeps occurring every
time the query is executed.

All databases are running PostgreSQL 16.2 on Windows, to be specific:

PostgreSQL 16.2, compiled by Visual C++ build 1937, 64-bit

On 2 of the databases I always get records out of this query:

select * -- 15
from ADVIES_TYPE
where owners_id IN (1,32,25)
and conditional = 'N'
and id not in (
select ADVIES_TYPE from ADVIES_HOOFD
where ADVIES_HOOFD.ADVIES_TYPE = ADVIES_TYPE.ID
and ADVIES_HOOFD.OWNERS_ID IN (1,32,25))
except
select * -- 14
from ADVIES_TYPE
where owners_id IN (1,32,25)
--and id = 1000001002
and conditional = 'N'
and not exists (
select 1 from ADVIES_HOOFD
--where ADVIES_HOOFD.ADVIES_TYPE::text = ADVIES_TYPE.ID::text
where ADVIES_HOOFD.ADVIES_TYPE = ADVIES_TYPE.ID
and ADVIES_HOOFD.OWNERS_ID IN (1,32,25))

Will return a record with ID = 1000001002 on 1 database (15-14=1 row)

On another database it will return 18 rows (18-0=18 rows)

On my personal database, it works as I expect and return 0 rows (15-15)

The first part, before the except gives 15 records and the part after the
except gives 14 records

When I compare the 15 and 14 records manually I indeed see that 1000001002
is in part 1 and missing from part 2

As far as I understand both parts should always return the same number of
records, so part1 except part2 shouldn't return a record.

When I check manually, there isn't a 1000001002  in
ADVIES_HOOFD.ADVIES_TYPE:

select * from ADVIES_HOOFD where ADVIES_TYPE = 1000001002

Gives no records

The create for advies_type is:

CREATE TABLE IF NOT EXISTS public.advies_type
(
    id integer NOT NULL DEFAULT nextval('advies_type_id_seq'::regclass),
    brondocdate_afv timestamp with time zone,
    brondocdate_opv timestamp with time zone,
    brondocnr_afv character varying(20) COLLATE pg_catalog."default",
    brondocnr_opv character varying(20) COLLATE pg_catalog."default",
    mutator_id numeric(22,0) NOT NULL,
    creator_id numeric(22,0) NOT NULL,
    createdate timestamp with time zone NOT NULL,
    mut_dat timestamp with time zone NOT NULL,
    conditional character varying(10) COLLATE pg_catalog."default" NOT
NULL,
    owners_id integer NOT NULL,
    omschrijving character varying(50) COLLATE pg_catalog."default" NOT
NULL,
    instelling integer,
    CONSTRAINT advies_typep PRIMARY KEY (id),
    CONSTRAINT instellingc1 FOREIGN KEY (instelling)
        REFERENCES public.advies_instelling (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT owners_idc1 FOREIGN KEY (owners_id)
        REFERENCES public.owners (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT conditionalc1 CHECK ((conditional::text = ANY
(ARRAY['N'::text, 'J'::text, 'H'::text])) OR conditional IS NULL)
)

And ADVIES_HOOFD:

CREATE TABLE IF NOT EXISTS public.advies_hoofd
(
    id integer NOT NULL DEFAULT nextval('advies_hoofd_id_seq'::regclass),
    brondocdate_afv timestamp with time zone,
    brondocdate_opv timestamp with time zone,
    brondocnr_afv character varying(20) COLLATE pg_catalog."default",
    brondocnr_opv character varying(20) COLLATE pg_catalog."default",
    mutator_id numeric(22,0) NOT NULL,
    creator_id numeric(22,0) NOT NULL,
    createdate timestamp with time zone NOT NULL,
    mut_dat timestamp with time zone NOT NULL,
    conditional character varying(10) COLLATE pg_catalog."default" NOT
NULL,
    owners_id integer NOT NULL,
    advies_type integer,
    advies_kenmerk character varying(40) COLLATE pg_catalog."default",
    advies_gebied_id integer,
    advies_niveau_id integer,
    advies_besluit_id integer,
    datum_advies timestamp with time zone,
    advies_termijn numeric(20,0),
    door integer,
    advies_toelichten character varying(1) COLLATE pg_catalog."default",
    omschrijving character varying(4000) COLLATE pg_catalog."default",
    datum_toets timestamp with time zone,
    getoetst_door integer,
    advies text COLLATE pg_catalog."default",
    namens integer,
    advies_status integer,
    advies_toepaswel character varying(10) COLLATE pg_catalog."default",
    advies_afgerond character varying(1) COLLATE pg_catalog."default",
    advies_meenemen character varying(1) COLLATE pg_catalog."default",
    advies_commissie_id integer,
    advies_datum_id integer,
    advies_eerder character varying(1) COLLATE pg_catalog."default",
    laatste_agenda timestamp with time zone,
    omschrijving_kort character varying(150) COLLATE pg_catalog."default",
    zaak_identificatie character varying(40) COLLATE pg_catalog."default",
    zaak_status_id integer,
    zaak_reeks_id integer,
    xcoordinaat numeric(22,6),
    ycoordinaat numeric(22,6),
    CONSTRAINT advies_hoofdp PRIMARY KEY (id),
    CONSTRAINT advies_besluit_idc1 FOREIGN KEY (advies_besluit_id)
        REFERENCES public.advies_besluit (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT advies_commissie_idc1 FOREIGN KEY (advies_commissie_id)
        REFERENCES public.adv_commissie (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT advies_datum_idc1 FOREIGN KEY (advies_datum_id)
        REFERENCES public.adv_commissie_datums (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT advies_gebied_idc1 FOREIGN KEY (advies_gebied_id)
        REFERENCES public.advies_gebied (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT advies_niveau_idc1 FOREIGN KEY (advies_niveau_id)
        REFERENCES public.advies_niveau (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT advies_statusc1 FOREIGN KEY (advies_status)
        REFERENCES public.advies_status (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT advies_typec1 FOREIGN KEY (advies_type)
        REFERENCES public.advies_type (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT doorc1 FOREIGN KEY (door)
        REFERENCES public.login (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT getoetst_doorc1 FOREIGN KEY (getoetst_door)
        REFERENCES public.login (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT namensc1 FOREIGN KEY (namens)
        REFERENCES public.advies_namens (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT owners_idc1 FOREIGN KEY (owners_id)
        REFERENCES public.owners (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT zaak_reeks_idc1 FOREIGN KEY (zaak_reeks_id)
        REFERENCES public.gfo_zaken_statusreeks (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT zaak_status_idc1 FOREIGN KEY (zaak_status_id)
        REFERENCES public.gfo_zaken_statuscode (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT advies_afgerondc1 CHECK (advies_afgerond::text = ANY
(ARRAY['T'::text, 'F'::text])),
    CONSTRAINT advies_eerderc1 CHECK (advies_eerder::text = ANY
(ARRAY['T'::text, 'F'::text])),
    CONSTRAINT advies_meenemenc1 CHECK (advies_meenemen::text = ANY
(ARRAY['T'::text, 'F'::text])),
    CONSTRAINT advies_toelichtenc1 CHECK (advies_toelichten::text = ANY
(ARRAY['T'::text, 'F'::text])),
    CONSTRAINT advies_toepaswelc1 CHECK ((advies_toepaswel::text = ANY
(ARRAY['J'::text, 'N'::text, 'V'::text])) OR advies_toepaswel IS NULL),
    CONSTRAINT conditionalc1 CHECK ((conditional::text = ANY
(ARRAY['N'::text, 'J'::text, 'H'::text])) OR conditional IS NULL)
)

So both sides of the comparison:

ADVIES_HOOFD.ADVIES_TYPE = ADVIES_TYPE.ID

Are INTEGER

When I run:

select * -- 14
from ADVIES_TYPE
where owners_id IN (1,32,25)
and id = 1000001002
and conditional = 'N'
and not exists (
select 1 from ADVIES_HOOFD
--where ADVIES_HOOFD.ADVIES_TYPE::text = ADVIES_TYPE.ID::text
where ADVIES_HOOFD.ADVIES_TYPE = ADVIES_TYPE.ID
and ADVIES_HOOFD.OWNERS_ID IN (1,32,25))

So, with the "and id = 1000001002", I get exactly that record 1000001002 as
a result. So it seems that this somehow fixes 1000001002 not appearing in
the result.

When I run the first query, but I cast the comparison to text:

select * -- 15
from ADVIES_TYPE
where owners_id IN (1,32,25)
--and id = 1000001002
and conditional = 'N'
and not exists (
select 1 from ADVIES_HOOFD
where ADVIES_HOOFD.ADVIES_TYPE::text = ADVIES_TYPE.ID::text
--where ADVIES_HOOFD.ADVIES_TYPE = ADVIES_TYPE.ID
and ADVIES_HOOFD.OWNERS_ID IN (1,32,25))

It gives 15 rows

When I change it back:

select * -- 14
from ADVIES_TYPE
where owners_id IN (1,32,25)
--and id = 1000001002
and conditional = 'N'
and not exists (
select 1 from ADVIES_HOOFD
--where ADVIES_HOOFD.ADVIES_TYPE::text = ADVIES_TYPE.ID::text
where ADVIES_HOOFD.ADVIES_TYPE = ADVIES_TYPE.ID
and ADVIES_HOOFD.OWNERS_ID IN (1,32,25))

It gives 14 rows

Like I said, I can't reproduce it in a script and attempts to make the data
in my local database as close to one of the problem databases have been
unsuccessful unfortunately.

I am hoping this is a known issue, but I can't find any mention of it. I
looked through the releasenotes 16.3 - 17.2, but couldn't find any mention
of this. Is it a known issue in 16.2 that "not exists" in combination with
INTEGER indexes can malfunction under certain circumstances?

Regards,

Jan