From: Tom Lane <tgl@sss.pgh.pa.us>
To: lori.corbani@jax.org
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19094: select statement on postgres 17 vs postgres 18 is returning different/duplicate results
Date: Sat, 25 Oct 2025 10:31:05 -0400
Message-ID: <1970841.1761402665@sss.pgh.pa.us> (raw)
In-Reply-To: <19094-6ed410eb5b256abd@postgresql.org>
References: <19094-6ed410eb5b256abd@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> 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)
This report is inadequate to help us identify the issue.
You've not provided the relevant table declarations,
nor any sample data that would reproduce the problem.
Given the squishiness of the described behavior, I realize that
building a self-contained reproducer might be hard. In the
meantime, could you at least provide EXPLAIN ANALYZE results
from correct and incorrect executions?
regards, tom lane
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: tgl@sss.pgh.pa.us, lori.corbani@jax.org, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19094: select statement on postgres 17 vs postgres 18 is returning different/duplicate results
In-Reply-To: <1970841.1761402665@sss.pgh.pa.us>
* 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