It was reported in [1] that LIKE on a non-deterministic collation returns an incorrect result when the pattern contains a literal backslash.
This was caused by escaping all backslashes in like_match.c (even when the internal pattern contained '\\'). This behaviour has been present since 85b7efa1cd which originally added support for LIKE with non-deterministic collations. This patch fixes the issue by always including the character after a literal '\' in the final buffer. I didn't check for the end of the string because that check is already handled in the block above when checking for escape characters. I also added a regression test for this issue and confirmed that it passes with the fix. Please take a look and let me know what you folks think. [1] https://www.postgresql.org/message-id/flat/19474-5b86a95f3d9a7ecb%40postgresql.org Thanks & Regards, Nitin Motiani Google
From 7e5e0de5a77b4f69cb9a3273470f3e5a4ec5a025 Mon Sep 17 00:00:00 2001 From: Nitin Motiani <[email protected]> Date: Thu, 14 May 2026 10:49:54 +0000 Subject: [PATCH v1] 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 fix ensures that an escape backslash correctly causes the following character to be copied literally into the subpattern before comparison. A regression test is added to verify the fix and prevent future regressions. Reported-by: b/19474 on pgsql-bugs --- src/backend/utils/adt/like_match.c | 5 ++--- src/test/regress/expected/collate.icu.utf8.out | 7 +++++++ src/test/regress/sql/collate.icu.utf8.sql | 3 +++ 3 files changed, 12 insertions(+), 3 deletions(-) diff --git a/src/backend/utils/adt/like_match.c b/src/backend/utils/adt/like_match.c index f5f72b82e21..149538cafbc 100644 --- a/src/backend/utils/adt/like_match.c +++ b/src/backend/utils/adt/like_match.c @@ -257,9 +257,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++; + *(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 04e2f6df037..3ee7580b05d 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -2741,6 +2741,13 @@ 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) + -- 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 18c47e6e05a..7810e56da6e 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -960,6 +960,9 @@ 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 '#'; + -- 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.54.0.563.g4f69b47b94-goog
