Date: Wed, 24 Mar 2021 13:41:38 -0700 (MST)
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1616618498263-0.post@n3.nabble.com>
In-Reply-To: <1616528764665-0.post@n3.nabble.com>
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>
 <1616528764665-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/1616618498263-0.post%40n3.nabble.com>
Precedence: bulk

For a UTF8 encoded, case-insensitive (nondeterministic), accent-sensitive I=
CU
collation, a LIKE predicate can be used with a small transformation of the
predicate, and the pattern can contain multi-byte characters:

from:

SELECT * FROM locations WHERE location LIKE 'midi-Pyren=C3=A9%'; =20
-- ERROR:  nondeterministic collations are not supported for LIKE

to:

SELECT * FROM locations WHERE lower(location) COLLATE "C" LIKE
lower('midi-Pyren=C3=A9%');

and if there is an index defined as follows:

CREATE INDEX location_LOWER_C_index ON
    locations (LOWER(location) COLLATE "C");

then the LIKE predicate above performs the desired CI_AS evaluation and als=
o
exploits the index:

EXPLAIN VERBOSE SELECT * FROM locations WHERE LOWER(location) COLLATE "C"
LIKE LOWER('midi-Pyren=C3=A9%');
                                                                       =20
QUERY PLAN                                                                 =
       =20
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------
 Index Scan using location_LOWER_C_index on public.locations=20
(cost=3D0.13..8.16 rows=3D1 width=3D18)
   Output: location
   Index Cond: (((lower((locations.location)::text))::text >=3D
'midi-pyren?'::text) AND ((lower((locations.location)::text))::text <
'midi-pyren?'::text))
   Filter: ((lower((locations.location)::text))::text ~~
'midi-pyren?%'::text)
(4 rows)


It turns out that CI_AS represents the vast majority (more than 99.9%) of
nondeterministic collations that we are seeing in babelfish, because
SQL_Latin1_General_CP1_CI_AS is the default collation in SQL Server.

Currently nondeterministic collations are disabled at the database level.=
=20
The cited reason was because of the lack of LIKE support and because certai=
n
catalog views use LIKE.  That may still need to be a limitation if those
LIKE predicates currently have an index exploitation unless we can create a
LOWER(col) COLLATE "C" index on the catalog.  Which catalog views were thes=
e
that had the problem?


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