| 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 |
| From | Date | Subject | |
|---|---|---|---|
| Previous Message | shihao zhong | 2026-10-18 03:47:37 | Re: Wrong results from a parameterized Append |