From: Peter Eisentraut <peter@eisentraut.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Support LIKE with nondeterministic collations
Date: Wed, 27 Nov 2024 07:23:44 +0000
Message-ID: <E1tGCOm-003irY-4m@gemulon.postgresql.org> (raw)
Support LIKE with nondeterministic collations
This allows for example using LIKE with case-insensitive collations.
There was previously no internal implementation of this, so it was met
with a not-supported error. This adds the internal implementation and
removes the error. The implementation follows the specification of
the SQL standard for this.
Unlike with deterministic collations, the LIKE matching cannot go
character by character but has to go substring by substring. For
example, if we are matching against LIKE 'foo%bar', we can't start by
looking for an 'f', then an 'o', but instead with have to find
something that matches 'foo'. This is because the collation could
consider substrings of different lengths to be equal. This is all
internal to MatchText() in like_match.c.
The changes in GenericMatchText() in like.c just pass through the
locale information to MatchText(), which was previously not needed.
This matches exactly Generic_Text_IC_like() below.
ILIKE is not affected. (It's unclear whether ILIKE makes sense under
nondeterministic collations.)
This also updates match_pattern_prefix() in like_support.c to support
optimizing the case of an exact pattern with nondeterministic
collations. This was already alluded to in the previous code.
(includes documentation examples from Daniel Vérité and test cases
from Paul A Jungwirth)
Reviewed-by: Jian He <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/700d2e86-bf75-4607-9cf2-f5b7802f6e88@eisentraut.org
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/85b7efa1cdd63c2fe2b70b725b8285743ee5787f
Modified Files
--------------
doc/src/sgml/charset.sgml | 2 +-
doc/src/sgml/func.sgml | 52 ++++++-
src/backend/utils/adt/like.c | 26 ++--
src/backend/utils/adt/like_match.c | 149 ++++++++++++++++++-
src/backend/utils/adt/like_support.c | 29 ++--
src/test/regress/expected/collate.icu.utf8.out | 189 ++++++++++++++++++++++++-
src/test/regress/sql/collate.icu.utf8.sql | 55 ++++++-
7 files changed, 458 insertions(+), 44 deletions(-)
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-committers@postgresql.org
Cc: peter@eisentraut.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Support LIKE with nondeterministic collations
In-Reply-To: <E1tGCOm-003irY-4m@gemulon.postgresql.org>
* 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