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

Tom,

Attached is a file with this info. Please let me know if this is what you need.

PRB_Strain.bcp.gz
PRB_Strain_create.object : schema
VOC_Annot.bcp.gz
VOC_Annot_create.object : schema
VOC_Term.bcp.gz
VOC_Term_create.object : schema

Thanks.
Lori

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Saturday, October 25, 2025 10:31 AM
To: Lori Corbani <Lori(dot)Corbani(at)jax(dot)org>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: [EXTERNAL]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
---

The information in this email, including attachments, may be confidential and is intended solely for the addressee(s). If you believe you received this email by mistake, please notify the sender by return email as soon as possible.