From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: lori.corbani@jax.org
Subject: BUG #19094: select statement on postgres 17 vs postgres 18 is returning different/duplicate results
Date: Fri, 24 Oct 2025 14:23:34 +0000
Message-ID: <19094-6ed410eb5b256abd@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19094
Logged by: Lori Corbani
Email address: lori.corbani@jax.org
PostgreSQL version: 18.0
Operating system: Rocky Linux 9.6 (Blue Onyx)
Description:
postgres 17: (A) SELECT 115457, (B) SELECT 115457 : same counts
postgres 18: (A) SELECT 115444, (B) SELECT 115436 : different counts
I am running this from /usr/pgsql-18/bin/psql
step 1: psql -h ${PG_DBSERVER} -U ${PG_DBUSER} -d ${PG_DBNAME} -e
step 2: run (A) version / without the "distinct" clause
step 3: select results
step 4: exit psql
step 5. repeat step 1-4
each time I run "pgsql" and the same SQL, I get a different count.
sometimes the count goes up and sometimes the count goes down.
running the SQL with the "distinct" fixes the problem, but we are not having
this problem on postgres 17.
Postgres 17 consistently returns the same count.
When we migrated from Postgres 17 to Postgres 18, I used the same
"postgressql.conf" and "pg_hpa.config".
Also, we migrated from Postgres 15 to Postgres 17 earlier this year with no
issues; using the same method.
If I remove the "exists" statement, then the counts are fine.
So, it seems that it is the "exists" statement that is causing the issue.
"select s._Strain_key" VS "select distinct s._Strain_key"
from prb_strain s
where s.private = 0
and s.strain not ilike '%involves%'
and s.strain not ilike '%either%'
and s.strain not ilike '% and %'
and s.strain not ilike '% or %'
and exists (select 1 from voc_annot va, voc_term t
where va._AnnotType_key = 1009
and va._Term_key = t._Term_key
and t.term != 'Not Applicable'
and t.term != 'Not Specified'
and va._Object_key = s._Strain_key)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, lori.corbani@jax.org
Subject: Re: BUG #19094: select statement on postgres 17 vs postgres 18 is returning different/duplicate results
In-Reply-To: <19094-6ed410eb5b256abd@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox