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)
