Date: Mon, 18 May 2026 06:51:53 -0500
From: Nico Williams <nico@cryptonector.com>
To: Jeff Davis <pgsql@j-davis.com>
Cc: Daniel Verite <daniel@manitou-mail.org>,
	Peter Eisentraut <peter@eisentraut.org>,
	Robert Haas <robertmhaas@gmail.com>,
	Pgsql-Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Support LIKE with nondeterministic collations
Message-ID: <agr9WXfspaNtzY0x@ubby>
References: <9c81e66f-0e06-4756-b9a2-d7b7d46379c1@manitou-mail.org>
 <8a4cbd9099f7c9dbe5eea78b582ab58c06c8d09a.camel@j-davis.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <8a4cbd9099f7c9dbe5eea78b582ab58c06c8d09a.camel@j-davis.com>
Archived-At: <https://www.postgresql.org/message-id/agr9WXfspaNtzY0x%40ubby>
Precedence: bulk

On Wed, Jul 31, 2024 at 03:26:34PM -0700, Jeff Davis wrote:
> On Fri, 2024-05-03 at 16:58 +0200, Daniel Verite wrote:
> > In other words it says that
> > 
> >   col LIKE 'smith%' collate "nd"
> > 
> > is equivalent to:
> > 
> >   col >= 'smith' collate "nd" AND col < U&'smith\ffff' collate "nd"
> 
> That logic seems to assume something about the collation. If you have a
> collation that orders strings by their sha256 hash, that would entirely
> break the connection between prefixes and ranges, and it wouldn't work.

The hash of what? each character's names or canonical representations in
some UTF?  If so, then, to maintain the above equivalence one would have
to alter the definition of this 'hash-based collation' so that U+FFFF is
always "last".

> Is there something about the way collations are defined that inherently
> maintains a connection between a prefix and a range? [...]

Yes: rules like the one Daniel described.

>                                               [...]? Does it remain
> true even when strange rules are added to a collation?

There are 'strange rules' which cannot be used in defining a collation,
as the result would not then be a collation.

Nico
--