Date: Sat, 3 Apr 2021 11:47:46 -0700 (MST)
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1617475666203-0.post@n3.nabble.com>
In-Reply-To: <1616677789675-0.post@n3.nabble.com>
References: <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>
 <1616528764665-0.post@n3.nabble.com> <1616618498263-0.post@n3.nabble.com>
 <54ef5bfe-043e-4328-9d70-5818789838f4@manitou-mail.org>
 <1616677789675-0.post@n3.nabble.com>
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/1617475666203-0.post%40n3.nabble.com>
Precedence: bulk

My previous post had a flaw, but fixing that flaw led me to what may be a
bug?

Using column COLLATE "C" ILIKE pattern almost does what you'd like, but the
single-character wildcard is treated as a single byte with "C", and that
won't match a multi-byte character.  The fix, I thought, would be to use a
deterministic CS_AS ICU collation, since we can handle the per-character
advance correctly in that case (we think).  Well, maybe not.  It looks like
single-character wildcards using a deterministic ICU collation match a
single byte instead of a single character:

This creates a deterministic case-sensitive, accent-sensitive collation in =
a
utf8-encoded database:

SET client_encoding =3D WIN1252;
CREATE COLLATION CS_AS (
    provider =3D icu,
    locale =3D 'utf8@colStrength=3Dsecondary;colCaseLevel=3Dyes',
    deterministic =3D true);

CREATE TABLE locations (location VARCHAR(255) COLLATE CS_AS);
CREATE INDEX location_index ON locations (location);

INSERT INTO locations VALUES ('Franche-Comt=C3=A9')
                        , ('Midi-Pyr=C3=A9n=C3=A9es')
                        , ('midi-Pyr=C3=A9n=C3=A9es')
                        , ('midi-Pyren=C3=A9es') =20
                        , ('Brian Bru=C3=9F')=20
                        , ('Brian Bruss')=20
                        , ('Steven Sossmix')                     =20
                        , ('Provence-Alpes-C=C3=B4te d Azur');

postgres=3D# SELECT * FROM locations WHERE location LIKE 'Franche-Comt=C3=
=A9';
    location   =20
----------------
 Franche-Comt=C3=A9
(1 row)

postgres=3D# SELECT * FROM locations WHERE location LIKE 'Franche-Comt_';  =
 --
is this a bug?
 location=20
----------
(0 rows)

postgres=3D# SELECT * FROM locations WHERE location LIKE 'Franche-Comt__'; =
 --
the wildcard is applied byte by byte instead of character by character, so
the 2-byte accented character is matched only by 2 '_'s
    location   =20
----------------
 Franche-Comt=C3=A9
(1 row)


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