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
In-reply-to: <19094-6ed410eb5b256abd@postgresql.org>
References: <19094-6ed410eb5b256abd@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 24 Oct 2025 14:23:34 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1970840.1761402665.1@sss.pgh.pa.us>
Date: Sat, 25 Oct 2025 10:31:05 -0400
Message-ID: <1970841.1761402665@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1970841.1761402665%40sss.pgh.pa.us>
Precedence: bulk

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