Horton
Menu

Re: Wrong results from inner-unique joins caused by collation mismatch

Richard Guo <guofenglinux@gmail.com>

View thread
From: Richard Guo <guofenglinux@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2026-04-25T09:24:39Z
Lists: pgsql-hackers

Attachments

On Sat, Apr 25, 2026 at 12:44 AM Richard Guo <guofenglinux@gmail.com> wrote:
> On Fri, Apr 24, 2026 at 11:53 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > 1. I think there are other places in the planner that will need
> > substantially this same logic.  I recommend breaking out a
> > subroutine defined more or less as "do these collations have
> > equivalent notions of equality".

> Right.  I just found several other places that need this same logic,
> which are in query_is_distinct_for().  Without it, we produce wrong
> results:

0001 wrapped the logic in subroutine collations_are_compatible().

(I'm a little unsure about the InvalidOid cases.  The current
implementation treats InvalidOid on either side as compatible, as
absence of a collation can't conflict with the other side.  This
generalizes the asymmetric treatment in IndexCollMatchesExprColl().)

0002 fixed query_is_distinct_for(), using that subroutine.

- Richard

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Consider collation when proving subquery uniqueness

  2. Consider collation when proving uniqueness from unique indexes