Date: Tue, 23 Mar 2021 12:46:04 -0700 (MST)
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1616528764665-0.post@n3.nabble.com>
In-Reply-To: <693ad06d-db9a-4e59-8131-f823483c5893@manitou-mail.org>
References: <1ccc668f-4cbc-0bef-af67-450b47cdfee7@2ndquadrant.com>
 <cdd0ed2e-1d98-6fc4-07f1-23f19f53b201@2ndquadrant.com>
 <5c7f51b0-6788-4ffd-b9c4-23b710373140@manitou-mail.org>
 <fd54ad77-fb1a-c77d-1371-d7850562b080@2ndquadrant.com>
 <440ec733-d068-46d3-9756-f5f692aba7c8@manitou-mail.org>
 <99818c53-d10f-eb0b-bcb1-fd5d60f6dd96@2ndquadrant.com>
 <f2b3b8e8-12c4-ceb7-6803-b4af6eb2cb71@2ndquadrant.com>
 <693ad06d-db9a-4e59-8131-f823483c5893@manitou-mail.org>
Subject: Re: insensitive collations
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/1616528764665-0.post%40n3.nabble.com>
Precedence: bulk

Has any progress been made on supporting LIKE for nondeterministic
collations?

The pattern as well as the expresion needs to use collation-aware character
comparisons, so for a suitable collation where =C3=9F compares equally to s=
s:

SELECT * from table WHERE name LIKE '%=C3=9F%'
yields
Brian Bru=C3=9F
Steven Sossmix

and even if the pattern contains only single-byte UTF-8 characters, a
non-accented character in the first 127 might compare equally to a two-byte
accented character in the first argument, so the comparisons as well as the
character-advancing logic must be collation-aware.  This seems to imply tha=
t
for the general nondeterministic case we need to rewrite the algorithm to
use ICU functions for advancing to the next character and for comparing
characters at the current position in the pattern and string.  Is that
accurate?

for a database with UTF8 encoding and a collation that is case-insenstitive
but accent-sensitive, and where the pattern contains only single-byte
characters or wildcard characters, would LIKE and ILIKE be correct with the
current per-byte implementation - albeit without any index exploitation?


-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.h=
tml