Re: BUG #18766: not exists sometimes gives too few records

On 1/3/25 18:04, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18766
> Logged by: Jan Kort
> Email address: jan(dot)kort(at)genetics(dot)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?
>

I don't think so. It's a bit hard to grok what exactly is expected to be
happening - we don't know the data or the explain plans, so we'd have to
speculate about what is expected to happen etc.

If I had to guess, this seems a bit like index corruption, in which case
the queries can easily return confusing results, depending on what query
plan happens to be selected (e.g. it might work fine without index).

That'd also explain why you may have difficulties trying to reproduce
this on a different machine, a copy of the data, etc.

Use EXPLAIN to check the query plan, and then try to force it to not use
indexes (SET enable_indescans=off). If the result changes to the correct
one, that's a hint it's about indexes. You may try rebuilding the index,
or perhaps even all indexes (because if this one has issues, why not the
others?).

If this doesn't help, I suggest you try to narrow down the example. For
example, instead of the whole query with EXISTS, determine for which
values of (ADVIES_TYPE, OWNERS_ID) the subquery returns the wrong
results, and then look at only that (much smaller) query. Check if it
still uses the same plan (as in the whole query), if it returns the
right results, etc.

regards

--
Tomas Vondra