Feedback-ID: ie0a040ee:Fastmail
Message-ID: <b32cefe2-b9e2-499e-b919-fe8f21c5bc22@eisentraut.org>
Date: Fri, 3 May 2024 20:53:52 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Support LIKE with nondeterministic collations
To: Daniel Verite <daniel@manitou-mail.org>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Pgsql-Hackers <pgsql-hackers@postgresql.org>
References: <9c81e66f-0e06-4756-b9a2-d7b7d46379c1@manitou-mail.org>
Content-Language: en-US
From: Peter Eisentraut <peter@eisentraut.org>
In-Reply-To: <9c81e66f-0e06-4756-b9a2-d7b7d46379c1@manitou-mail.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/b32cefe2-b9e2-499e-b919-fe8f21c5bc22%40eisentraut.org>
Precedence: bulk

On 03.05.24 16:58, Daniel Verite wrote:
>     * Generating bounds for a sort key (prefix matching)
> 
>     Having sort keys for strings allows for easy creation of bounds -
>     sort keys that are guaranteed to be smaller or larger than any sort
>     key from a give range. For example, if bounds are produced for a
>     sortkey of string “smith”, strings between upper and lower bounds
>     with one level would include “Smith”, “SMITH”, “sMiTh”. Two kinds
>     of upper bounds can be generated - the first one will match only
>     strings of equal length, while the second one will match all the
>     strings with the same initial prefix.
> 
>     CLDR 1.9/ICU 4.6 and later map U+FFFF to a collation element with
>     the maximum primary weight, so that for example the string
>     “smith\uFFFF” can be used as the upper bound rather than modifying
>     the sort key for “smith”.
> 
> 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"
> 
> which could be obtained from an index scan, assuming a btree
> index on "col" collate "nd".
> 
> U+FFFF is a valid code point but a "non-character" [1] so it's
> not supposed to be present in normal strings.

Thanks, this could be very useful!