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

Tom,

2 things I did this morning:

1. I added "order by" clause ; no change

2. I added "select distinct"; which fixed this, as I expected. However, when I googled the Postgres "exists" best practices, it seems to suggest that the "distinct" is unnecessary.

From Google Search:
Therefore, using DISTINCT within a subquery for an EXISTS clause is generally redundant and unnecessary. The EXISTS operator only cares if any row satisfies the subquery's condition, not how many or if they are unique. Adding DISTINCT would typically add overhead by requiring the database to sort and filter for uniqueness, which is not required for the EXISTS check itself.

What is your suggestion for best practice when using "exists" clause?

Many thanks.
Lori

-----Original Message-----
From: Lori Corbani
Sent: Monday, October 27, 2025 7:34 AM
To: 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: 'pgsql-bugs(at)lists(dot)postgresql(dot)org' <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: 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.