On Sat, Jul 4, 2026 at 3:03 AM Tom Lane <[email protected]> wrote: > > I don't think this patch is ready. It is wasting code and cycles to > fix a nonexistent problem. We are working in a backend-safe encoding, > therefore it is not possible for one byte of a multibyte character to > match '\' (nor '_' nor '%'). So the existing logic that pays no > attention to multibyte boundaries is not wrong, and if it were then > the preceding loop that looks for the end of the literal-match > substring would also be wrong (as well as some hundreds of other > places that make similar assumptions). > > This code does get consecutive-backslash cases wrong, and we need > to fix that, but we don't need to add complexity to fix something > that's not broken. >
Thanks for the feedback, Tom. I have removed the logic for the multibyte check and simplified the code. Please take a look at the latest patch. Regards, Nitin Motiani Google
From edfa685d0041ca5f14110081df1edc6ecbf4d99d Mon Sep 17 00:00:00 2001 From: Nitin Motiani <[email protected]> Date: Thu, 14 May 2026 10:49:54 +0000 Subject: [PATCH v4] Fix LIKE matching with nondeterministic collations and backslashes Commit 85b7efa1cd added support for LIKE with nondeterministic collations, but it included a bug in the unescaping logic for pattern partitions. When the pattern contained a literal backslash (which is represented as '\\' in the internal pattern), the code would skip both backslashes, resulting in an incorrect match failure against the original text. This logic also can cause a false positive match. If the pattern has a literal backslash but the text doesn't have one, skipping the backslashes would lead to a false positive. This fix ensures that an escape backslash correctly causes the following character to be copied literally into the subpattern before comparison. A few regression tests are added to verify the fix and prevent future regressions. Reported-by: b/19474 on pgsql-bugs --- src/backend/utils/adt/like_match.c | 15 +++++++-- .../regress/expected/collate.icu.utf8.out | 31 +++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 7 +++++ 3 files changed, 50 insertions(+), 3 deletions(-) diff --git a/src/backend/utils/adt/like_match.c b/src/backend/utils/adt/like_match.c index f5f72b82e21..7c5f8f969bc 100644 --- a/src/backend/utils/adt/like_match.c +++ b/src/backend/utils/adt/like_match.c @@ -256,10 +256,19 @@ MatchText(const char *t, int tlen, const char *p, int plen, pg_locale_t locale) b = buf = palloc(p1 - p); for (const char *c = p; c < p1; c++) { + /* + * If we see a backslash, skip it and copy the next + * character literally. Since all backend server encodings + * are ASCII-safe, a '\' (0x5C) can never be part of a + * multibyte character. + * Note: We don't need to check for the end of string + * as the preceding loop has already verified that + * an escape character is not the last byte. + */ + if (*c == '\\') - ; - else - *(b++) = *c; + c++; + *(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 fb95eee9b7c..3209964cc44 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -3006,6 +3006,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 954d8ea6cb4..3cee7223f95 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -1083,6 +1083,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 -- 2.55.0.rc2.803.g1fd1e6609c-goog
