postgres.git / summary / log / commit / refs
commit a99bd8d584ea268d8a3d02d0f0facb74429ffcf9
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon Jul 06 18:35:21 2026 +0000
First released in: 18.6
Fix LIKE matching with nondeterministic collations and backslashes.
Commit 85b7efa1c added support for LIKE with nondeterministic
collations, but it included a bug in the de-escaping logic for
literal pattern substrings. That unconditionally skipped all
backslashes, but when it encounters '\\' it should emit the second
backslash as a de-escaped character. That led to acting as though
the escaped backslash was not there.
Bug: #19474
Reported-by: Bowen Shi <zxwsbg12138@gmail.com>
Author: Nitin Motiani <nitinmotiani@google.com>
Reviewed-by: Zsolt Parragi <zsolt.parragi@percona.com>
Reviewed-by: Ewan Young <kdbase.hack@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: https://postgr.es/m/19474-5b86a95f3d9a7ecb@postgresql.org
Discussion: https://postgr.es/m/CAH5HC94yU+K8Gcdy12M5BS8gwD_SXLSHzc9k5tNk7JDnpBiFMA@mail.gmail.com
Backpatch-through: 18
src/backend/utils/adt/like_match.c | 5 ++---
src/test/regress/expected/collate.icu.utf8.out | 31 ++++++++++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 7 ++++++
3 files changed, 40 insertions(+), 3 deletions(-)
diff --git a/src/backend/utils/adt/like_match.c b/src/backend/utils/adt/like_match.c
index bac6c182467..4f74bf3cbcb 100644
--- a/src/backend/utils/adt/like_match.c
+++ b/src/backend/utils/adt/like_match.c
@@ -253,9 +253,8 @@ MatchText(const char *t, int tlen, const char *p, int plen, pg_locale_t locale)
for (const char *c = p; c < p1; c++)
{
if (*c == '\\')
- ;
- else
- *(b++) = *c;
+ c++; /* we already checked this isn't the end */
+ *(b++) = *c;
}
subpat = buf;
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index f8e65313752..2d17d56f6d0 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2996,6 +2996,37 @@ SELECT U&'\0061\0308bc' LIKE U&'_\00e4bc' COLLATE ignore_accents;
-- escape character at end of pattern
SELECT 'foox' LIKE 'foo\' COLLATE ignore_accents;
ERROR: LIKE pattern must not end with escape character
+-- literal backslash with nondeterministic collation (bug #19474)
+SELECT 'back\slash' COLLATE ignore_accents LIKE 'back\slash%' ESCAPE '#';
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT 'aäb' COLLATE ignore_accents LIKE 'a#äb' ESCAPE '#' AS multibyte_escape;
+ multibyte_escape
+------------------
+ t
+(1 row)
+
+SELECT 'a\äb' COLLATE ignore_accents LIKE 'a\äb%' ESCAPE '#' AS backslash_multibyte;
+ backslash_multibyte
+---------------------
+ t
+(1 row)
+
+SELECT 'a\b%c' COLLATE ignore_accents LIKE 'a#\b#%%c' ESCAPE '#' AS mixed_escapes;
+ mixed_escapes
+---------------
+ t
+(1 row)
+
+SELECT 'backslash' COLLATE ignore_accents LIKE 'back\\slash%';
+ ?column?
+----------
+ f
+(1 row)
+
-- foreign keys (mixing different nondeterministic collations not allowed)
CREATE TABLE test10pk (x text COLLATE case_sensitive PRIMARY KEY);
CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) ON UPDATE CASCADE ON DELETE CASCADE); -- error
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index f4c4271fb34..4fb01a17298 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -1079,6 +1079,13 @@ SELECT U&'\0061\0308bc' LIKE U&'_\00e4bc' COLLATE ignore_accents;
-- escape character at end of pattern
SELECT 'foox' LIKE 'foo\' COLLATE ignore_accents;
+-- literal backslash with nondeterministic collation (bug #19474)
+SELECT 'back\slash' COLLATE ignore_accents LIKE 'back\slash%' ESCAPE '#';
+SELECT 'aäb' COLLATE ignore_accents LIKE 'a#äb' ESCAPE '#' AS multibyte_escape;
+SELECT 'a\äb' COLLATE ignore_accents LIKE 'a\äb%' ESCAPE '#' AS backslash_multibyte;
+SELECT 'a\b%c' COLLATE ignore_accents LIKE 'a#\b#%%c' ESCAPE '#' AS mixed_escapes;
+SELECT 'backslash' COLLATE ignore_accents LIKE 'back\\slash%';
+
-- foreign keys (mixing different nondeterministic collations not allowed)
CREATE TABLE test10pk (x text COLLATE case_sensitive PRIMARY KEY);
CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) ON UPDATE CASCADE ON DELETE CASCADE); -- error
[parent: d0bb49e61168]