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]