Re: BUG #19094: select statement on postgres 17 vs postgres 18 is returning different/duplicate results

PG Bug reporting form <noreply(at)postgresql(dot)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