pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Subject: Re: insensitive collations
Date: Tue, 23 Mar 2021 12:46:04 -0700 (MST)
Message-ID: <1616528764665-0.post@n3.nabble.com> (raw)
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>

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 ß compares equally to ss:

SELECT * from table WHERE name LIKE '%ß%'
yields
Brian Bruß
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 that
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.html





view thread (44+ messages)  latest in thread

Message-ID: <1616528764665-0.post@n3.nabble.com>
Permalink:  ../1616528764665-0.post@n3.nabble.com/
Also on:    postgresql.org/message-id/1616528764665-0.post@n3.nabble.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: jfinnert@amazon.com
  Subject: Re: insensitive collations
  In-Reply-To: <1616528764665-0.post@n3.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox