[PG19] ILIKE index scan misses rows with a libc UTF-8 collation on macOS

From: shihao zhong <zhong950419(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Jeff Davis <pgsql(at)j-davis(dot)com>, Noah Misch <noah(at)leadboat(dot)com>
Subject: [PG19] ILIKE index scan misses rows with a libc UTF-8 collation on macOS
Date: 2026-10-18 03:52:33
Message-ID: CAGRkXqS6pr-hG-g=K6RTdRw53SnMZWz3iXvh_wi8rqLSf972sA@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

This is the first low item from my list in [1]. It has the same cause as
the EUC_* report in [2], and 43a948b7b25 did not fix it.

wc_iscased_libc_utf8() asks iswupper() and iswlower(), but ILIKE
compares with towlower(). The macOS C library case-maps 117 code points
that are neither upper nor lower, like U+2160 (Roman numeral one). So
U+2160 stays in the index prefix:

create table t (x text collate "en_US.UTF-8");
insert into t values (U&'\2160q'), (U&'\2170q');
insert into t select 'filler' || g from generate_series(1, 2000) g;
create index on t (x text_pattern_ops);
analyze t;
select count(*) from t where x ilike U&'\2160q%';

On the master and REL_19_STABLE heads the index scan returns 1. A seq
scan returns 2. glibc on Linux classifies all of them, so it is fine.

The attached patch also counts a character as cased when towupper() or
towlower() changes it.

[1]
https://postgr.es/m/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com
[2] https://postgr.es/m/20260826221526.89.noahmisch@microsoft.com

Thanks,
Shihao

Attachment Content-Type Size
v1-0001-Fix-pg_iswcased-for-libc-UTF8-case-mappings.patch application/octet-stream 1.5 KB

Browse pgsql-hackers by date

  From Date Subject
Previous Message shihao zhong 2026-10-18 03:47:37 Re: Wrong results from a parameterized Append