Hi Hackers, Jeff,
In v19, ILIKE in a database with a single-byte encoding and a C ctype
never matches when '%' is followed by a byte >= 0x80:
CREATE DATABASE l1 TEMPLATE template0 ENCODING 'LATIN1' LOCALE 'C';
\c l1
SET client_encoding = 'UTF8';
SELECT 'xÆy' ILIKE '%Æ%'; -- 18.6: t 19rc1: f
SELECT 'abÆ' ILIKE '%Æ'; -- 18.6: t 19rc1: f
SELECT 'Æcd' ILIKE 'Æ%'; -- t on both
SELECT 'xÆy' LIKE '%Æ%'; -- t on both
The same happens in WIN1252 and KOI8R databases with LOCALE 'C'. It
reproduces on 19rc1 and on master and REL_19_STABLE as of today.
The cause is in commit 1e493158d3d ("Remove char_tolower() API"). That
path is C_IMatchText, where like_match.c's GETCHAR() became
pg_ascii_tolower(), which returns unsigned char. The '%' branch of
MatchText keeps the first pattern byte in a char:
char firstpat;
...
firstpat = GETCHAR(*p);
...
if (GETCHAR(*t) == firstpat || nondeterministic)
For 0xC6, firstpat holds -58 where char is signed, while GETCHAR(*t)
promotes to 198, so the test never succeeds and the scan after '%' skips
every position. Before that commit GETCHAR was SB_lower_char(), which
returned char, so both sides had the same signedness. The other
comparisons in MatchText compare GETCHAR() with GETCHAR(), unsigned on
both sides, which is why a pattern without a leading '%' still matches.
Platforms where char is unsigned (ARM, POWER) are not affected.
The attached patch declares firstpat as int. That covers the
non-MATCH_LOWER instantiations too, where GETCHAR() is the plain char
and both sides are promoted from the same type.
The main regression database is normally UTF-8, which never reaches
C_IMatchText, so the patch adds a TAP test,
src/test/modules/test_misc/t/016_ilike_single_byte.pl, that creates a
LATIN1 / C database and checks seven ILIKE cases. It builds its
strings with convert_from() so it doesn't depend on the client
encoding. On master without the fix it fails:
got: 'false,false,false,false,true,true,false'
expected: 'true,true,false,true,true,false,false'
and with it, it passes, as do make check (239/239) and the rest of
test_misc (FreeBSD 15.1 amd64, clang 19; built and tested on master at
45fc51fcbf4).
I found this through pg_tre's regression suite, which compares its
index with a sequential scan in LATIN1, WIN1252 and KOI8R databases.
The ILIKE checks for words that start with a non-ASCII letter came back
empty on 19rc1 for both scans, because both end in core's ILIKE.
This should go into 19 before release; the patch carries
"Backpatch-through: 19".
what do you think? bug, or misunderstanding on my part, or...? :)
-gregFrom a8f901f22954028f995b3f21712aaa54e603fdb1 Mon Sep 17 00:00:00 2001
From: Greg Burd <[email protected]>
Date: Sun, 18 Oct 2026 07:52:08 -0400
Subject: [PATCH v1] Fix ILIKE '%' followed by a non-ASCII byte in single-byte
C-ctype databases
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Since 1e493158d3d, C_IMatchText (the ILIKE path for a single-byte
encoding with a C ctype) lowercases with pg_ascii_tolower(), which
returns unsigned char. MatchText stores the first pattern byte after a
'%' in a char and compares it with GETCHAR(*t); on platforms where char
is signed, a byte >= 0x80 is negative in firstpat but positive on the
other side, so the comparison never succeeds and the '%' never finds a
match. 'xÃy' ILIKE '%Ã%' in a LATIN1 database with LOCALE 'C' returns
false, where 18 returns true. Declare firstpat as int.
The regression database is normally UTF-8, which never takes this path,
so add a TAP test that creates a LATIN1 database.
Backpatch-through: 19
---
src/backend/utils/adt/like_match.c | 7 ++-
src/test/modules/test_misc/meson.build | 1 +
.../test_misc/t/016_ilike_single_byte.pl | 43 +++++++++++++++++++
3 files changed, 50 insertions(+), 1 deletion(-)
create mode 100644 src/test/modules/test_misc/t/016_ilike_single_byte.pl
diff --git a/src/backend/utils/adt/like_match.c b/src/backend/utils/adt/like_match.c
index defcaa96fb5..830d2b98653 100644
--- a/src/backend/utils/adt/like_match.c
+++ b/src/backend/utils/adt/like_match.c
@@ -109,7 +109,12 @@ MatchText(const char *t, int tlen, const char *p, int plen, pg_locale_t locale)
*/
if (*p == '%')
{
- char firstpat;
+ /*
+ * int, not char: under MATCH_LOWER, GETCHAR() yields an unsigned
+ * char, which a signed char would not compare equal to for bytes
+ * >= 0x80.
+ */
+ int firstpat;
/*
* % processing is essentially a search for a text position at
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 5d81f5b13be..1b6ffc3a185 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -24,6 +24,7 @@ tests += {
't/013_temp_obj_multisession.pl',
't/014_log_statement_max_length.pl',
't/015_temp_schema_exit_deferrable.pl',
+ 't/016_ilike_single_byte.pl',
],
# The injection points are cluster-wide, so disable installcheck
'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/016_ilike_single_byte.pl b/src/test/modules/test_misc/t/016_ilike_single_byte.pl
new file mode 100644
index 00000000000..f01c40cc565
--- /dev/null
+++ b/src/test/modules/test_misc/t/016_ilike_single_byte.pl
@@ -0,0 +1,43 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# ILIKE in a single-byte encoding with a C ctype matches via C_IMatchText
+# (like.c), which folds bytes with pg_ascii_tolower(). A '%' followed by a
+# non-ASCII byte must still find it. The main regression database is
+# usually UTF-8, which never takes this path, so use a LATIN1 database.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('ilike_single_byte');
+$node->init;
+$node->start;
+
+$node->safe_psql('postgres',
+ q{CREATE DATABASE latin1 TEMPLATE template0 ENCODING 'LATIN1' LOCALE 'C'});
+
+# Build every string from bytes, so the test does not depend on the client
+# encoding: \xc6 is LATIN1 'Ã', \xe6 is 'æ', \xe9 is 'é'.
+my $result = $node->safe_psql(
+ 'latin1', q{
+SELECT string_agg(r::text, ',' ORDER BY n)
+ FROM (VALUES
+ (1, convert_from('\x78c679', 'LATIN1') ILIKE convert_from('\x25c625', 'LATIN1')),
+ (2, convert_from('\x6162c6', 'LATIN1') ILIKE convert_from('\x25c6', 'LATIN1')),
+ (3, convert_from('\x786579', 'LATIN1') ILIKE convert_from('\x25e925', 'LATIN1')),
+ (4, convert_from('\x78c6e679', 'LATIN1') ILIKE convert_from('\x25e625', 'LATIN1')),
+ (5, convert_from('\xc66364', 'LATIN1') ILIKE convert_from('\xc625', 'LATIN1')),
+ (6, convert_from('\x78c679', 'LATIN1') NOT ILIKE convert_from('\x25c625', 'LATIN1')),
+ (7, convert_from('\x78e679', 'LATIN1') ILIKE convert_from('\x25c625', 'LATIN1'))
+ ) AS v(n, r)});
+
+# 1: '%Ã%' finds à mid-string; 2: at the end; 3: '%é%' does not match 'xey';
+# 4: '%æ%' finds æ after Ã; 5: no leading '%' (always worked);
+# 6: the NOT ILIKE counterpart of 1; 7: à and æ are different bytes and the
+# C ctype folds ASCII only, so '%Ã%' does not match 'xæy'.
+is($result, 'true,true,false,true,true,false,false',
+ 'ILIKE with % before a non-ASCII byte, LATIN1 / C');
+
+done_testing();
--
2.54.0
