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

On Sun, Oct 18, 2026 at 12:53 PM shihao zhong <zhong950419@gmail.com> wrote:

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.

I reproduced this issue on my macOS as well. The patch looks good to me.

Regards,

--
Fujii Masao

On Oct 18, 2026, at 11:52, shihao zhong <zhong950419@gmail.com> wrote:

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] /messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com
[2] /messages/by-id/20260826221526.89.noahmisch@microsoft.com

Thanks,
Shihao
<v1-0001-Fix-pg_iswcased-for-libc-UTF8-case-mappings.patch>

I can reproduce the issue on my MacBook too.

Given that this issue has only been confirmed on macOS so far, does it make sense to add the extra checks only for macOS, like this:
```
return iswupper_l((wint_t) wc, locale->lt) ||
iswlower_l((wint_t) wc, locale->lt)
#if defined(__darwin__)
|| towupper_l((wint_t) wc, locale->lt) != (wint_t) wc
|| towlower_l((wint_t) wc, locale->lt) != (wint_t) wc
#endif
;
```

I see that #if defined(__darwin__) is already used in many places.

Then, I would suggest adding a regression test so that CI can help check whether the problem exists on other platforms.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

Thanks Fujii and Chao for testing.

Given that this issue has only been confirmed on macOS so far, does it
make sense to add the extra checks only for macOS, like this:

I would keep it on all platforms. ILIKE compares with towlower() on
every platform, so this check is what it needs everywhere. Where the C
library is consistent it changes nothing. I went over every code point
with glibc 2.39 and musl 1.2.5, and the new checks add no character
there. It only runs at plan time.

Then, I would suggest adding a regression test so that CI can help
check whether the problem exists on other platforms.

Done in 0002.

0001 is unchanged.

Thanks,
Shihao

On Oct 20, 2026, at 10:20, shihao zhong <zhong950419@gmail.com> wrote:

Where the C
library is consistent it changes nothing.

Not really. On unaffected platforms, an unmatched char originally goes through 2 checks, now 4 checks, and the latter two are redundant, which adds overhead on unaffected platforms.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

On Sat, 2026-10-17 at 23:52 -0400, shihao zhong wrote:

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.

Thank you for the report!

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

When reading your patch, I wondered why we couldn't just do:

towupper_l(wc) != wc || towlower_l(wc) != wc

and the reason is that 00DF is a case-varying character because
towlower_l(1E9E) == 00DF, but the above test would return false. Your
patch correctly counts it as case-varying because iswlower_l(00DF) is
true.

It's a bit fragile in the sense that we don't have a clear reason for
relying on that set of tests, but it's the best we can do, so I agree
with your change and plan to commit it soon.

ICU and the builtin provider are more precise because they are able to
check for the Cased property directly.

Regards,
Jeff Davis

On Tue, 2026-10-20 at 11:16 +0800, Chao Li wrote:

Not really. On unaffected platforms, an unmatched char originally
goes through 2 checks, now 4 checks, and the latter two are
redundant, which adds overhead on unaffected platforms.

The checks are in the planner, trying to extract prefixes for an index
search. Those extra checks are insignificant.

Regards,
Jeff Davis

On Mon, 2026-10-19 at 22:20 -0400, shihao zhong wrote:

I would keep it on all platforms. ILIKE compares with towlower() on
every platform, so this check is what it needs everywhere. Where the
C
library is consistent it changes nothing. I went over every code
point
with glibc 2.39 and musl 1.2.5, and the new checks add no character
there. It only runs at plan time. 

Committed. Thank you!

Then, I would suggest adding a regression test so that CI can help
check whether the problem exists on other platforms.

Done in 0002.

I did not commit the test. I think we need to agree on a general way to
test libc better, rather than add a new test just for this. I don't
oppose a test here, though.

Regards,
Jeff Davis