From: Noah Misch <noah@leadboat.com>
To: pgsql@j-davis.com
Cc: pgsql-hackers@postgresql.org
Subject: EUC_* ILIKE index scan stopped matching seq scan in v19
Date: Wed, 26 Aug 2026 15:15:26 -0700
Message-ID: <20260826221526.89.noahmisch@microsoft.com> (raw)
Opus 5 found the $SUBJECT regression from commit 630706c "Add pg_iswcased()".
That finding still holds at today's master. I am attaching the LLM's report
and test case.
Attachments:630706c-EUC-ILIKE-test-v0.patch (text/plain, 34.6K) download
commit 99d60ed (c1/copilot/630706c-defect-tests)
Author: Noah Misch <noah@leadboat.com>
AuthorDate: Fri Jul 31 22:47:08 2026 +0000
Commit: Noah Misch <nm@inst-builder-debian-13-build-build-g8q13.us-central1-b.c.gce-image-builder.internal>
CommitDate: Fri Jul 31 22:47:08 2026 +0000
Add TAP test for wrong indexed ILIKE in libc multibyte DBs
Commit 630706ced0 ("Add pg_iswcased().") introduced pg_iswcased() and
wired the single-byte classifier wc_iscased_libc_sb into
ctype_methods_libc_other_mb, the ctype method table used by libc
databases in a non-UTF8 multibyte encoding (EUC_JP, EUC_CN, EUC_KR,
EUC_TW, EUC_JIS_2004, MULE_INTERNAL). Once dbbeafe added the
"wc > UCHAR_MAX" guard to that classifier, pg_iswcased() reports every
multibyte character as uncased. like_fixed_prefix_ci() (added later by
9c8de15) then files a cased fullwidth letter into a case-sensitive
B-tree prefix range, so an indexed ILIKE silently returns fewer rows
than a sequential scan of the same table. This is still present in
master.
The new test in src/test/modules/test_ilike_libc_mb/ initdbs a libc
EUC_JP cluster (skipping cleanly on Windows, when ja_JP.eucjp is
absent, or when initdb refuses the encoding) and runs one ILIKE query
whose pattern is a cased fullwidth letter, once as a forced sequential
scan and once as a forced index scan. It asserts that the two plans
return identical row sets and that both return the uppercase and
lowercase fullwidth rows. On master the index scan returns 1 row while
the sequential scan returns 2, so the test fails with "index scan
returned 1 rows, seq scan returned 2 rows". The oracle asserts the
required end state and is agnostic to the eventual fix.
Do not fix the defect here; the test is meant to fail on master.
DEFECTS_630706c.md in the new module documents the root cause (including
why the obvious wc_iscased_libc_mb swap is insufficient, because for
non-UTF8 multibyte encodings pg_wchar is packed encoded bytes rather
than a libc wchar_t), the rejected candidates, and full provenance.
Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
---
src/test/modules/Makefile | 1 +
src/test/modules/meson.build | 1 +
.../modules/test_ilike_libc_mb/DEFECTS_630706c.md | 378 +++++++++++++++++++++
src/test/modules/test_ilike_libc_mb/Makefile | 16 +
src/test/modules/test_ilike_libc_mb/meson.build | 12 +
.../test_ilike_libc_mb/t/001_ilike_eucjp.pl | 239 +++++++++++++
6 files changed, 647 insertions(+)
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefileindex 098bb81..a034f48 100644--- a/src/test/modules/Makefile+++ b/src/test/modules/Makefile@@ -32,6 +32,7 @@ SUBDIRS = \
test_escape \
test_extensions \
test_ginpostinglist \
+ test_ilike_libc_mb \
test_int128 \
test_integerset \
test_json_parser \
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.buildindex 4bca42b..73212d6 100644--- a/src/test/modules/meson.build+++ b/src/test/modules/meson.build@@ -33,6 +33,7 @@ subdir('test_dsm_registry')
subdir('test_escape')
subdir('test_extensions')
subdir('test_ginpostinglist')
+subdir('test_ilike_libc_mb')
subdir('test_int128')
subdir('test_integerset')
subdir('test_json_parser')
diff --git a/src/test/modules/test_ilike_libc_mb/DEFECTS_630706c.md b/src/test/modules/test_ilike_libc_mb/DEFECTS_630706c.mdnew file mode 100644index 0000000..105a976--- /dev/null+++ b/src/test/modules/test_ilike_libc_mb/DEFECTS_630706c.md@@ -0,0 +1,378 @@+# User-visible defect from commit 630706c ("Add pg_iswcased().") still present in master++Commit **630706ced04e3a7a7f0070f4e8fb88f7503a1016** ("Add pg_iswcased().")+introduced `pg_iswcased(wc, locale)` — "True if character has multiple case+forms" — as a multibyte-aware replacement for the older `char_is_cased()` API.+It added two libc implementations, `wc_iscased_libc_sb` (single-byte) and+`wc_iscased_libc_mb` (wide-character), and wired one into each libc ctype method+table. For the table used by **non-UTF8 multibyte** libc databases+(`ctype_methods_libc_other_mb`, covering EUC_JP, EUC_CN, EUC_KR, EUC_TW,+EUC_JIS_2004 and MULE_INTERNAL) it installed the **single-byte** classifier.++Once the `wc > UCHAR_MAX` guard was later added to that single-byte classifier+(dbbeafe, "pg_locale_libc.c: add guards to ctype methods."), `pg_iswcased()`+began returning **false for every multibyte character** in those encodings.+When `like_fixed_prefix_ci()` (added by 9c8de15, "Use multibyte-aware extraction+of pattern prefixes.") started consuming `pg_iswcased()` to extract the fixed,+case-invariant prefix of an `ILIKE` pattern for a B-tree index range, a cased+fullwidth letter is mis-classified as uncased and filed into a **case-sensitive**+prefix range. The result is a **wrong query result**: an indexed `ILIKE`+silently returns fewer rows than a sequential scan of the same table for the+same query.++The defect is **still present in master** at+`481052c7754013d44a89f50912ba6845398f88df`. This report accompanies the TAP+test `src/test/modules/test_ilike_libc_mb/t/001_ilike_eucjp.pl`, which encodes+the *correct* behavior and therefore **fails on unmodified master** with:++```+not ok 7 - EUC_JP: indexed and sequential ILIKE agree (index scan returned 1 rows, seq scan returned 2 rows)+```++This report is produced by an automated defect-hunting workflow.++---++## The verbatim user prompt that drove this workflow++```+Make a large workflow, with at most 90 agents, to write test cases covering+user-visible defects in these commits:++f81bf78ce12b9fd3e50eb00dd875440007262ec4+147602822597204aa436415ebe295926b268ab5c+19b966243c38196a33b033fb0c259dcf760c0d69+630706ced04e3a7a7f0070f4e8fb88f7503a1016+87b2968df0f866aaccb6ba69adf284e3c4a79454+0a90df58cf38cf68d59c6841513be98aeeff250e+8185bb53476378443240d57f7d844347d5fae1bf+dbf217c1c7c2744a18db489c255255e07cfbb110++Use your own worktrees; disregard the present dir except as repository to+which to attach your worktree. The workflow should first look for extant+user-visible defects (still present in master). If it finds any, write a test+case covering some of those defects. If any defects found weren't suitable to+test, describe them in a report. Include in the report the prompt I used, the+latest hash of the branches you consulted, model version, etc. Commit the+tests and the report (if any) on a fresh branch per commit under review.+```++---++## Provenance++* **Repository:** PostgreSQL, git dir `/home/nm/src/pg/postgresql`.+* **Branch consulted:** `master` (== `origin/master`) at+ **`481052c7754013d44a89f50912ba6845398f88df`** ("On Windows, make link(2)+ report ENOTSUP when appropriate.", CommitDate 2026-07-31 14:39:37 -0400).+ All worktrees were created from exactly this commit. **No other branch was+ consulted.**+* **Commit under review:** `630706ced04e3a7a7f0070f4e8fb88f7503a1016`+ ("Add pg_iswcased().", Jeff Davis, 2025-12-10).+ **Parent:** `1e493158d3d25771ed066028c00cbbdb41573496`+ ("Remove char_tolower() API.").+* **Agent / models:** GitHub Copilot CLI v1.0.77; orchestrating model+ **Claude Opus 5** (`claude-opus-5`). The hunt phase deliberately used **six+ different LLM models** for finding diversity — `claude-opus-4.8`, `gpt-5.5`,+ `claude-sonnet-4.6`, `gemini-3.1-pro-preview`, `gpt-5.6-sol`,+ `claude-opus-4.6` — and triage ran on `claude-opus-4.8`.+* **Host / toolchain:** Debian; gcc (Debian 14.2.0-19) 14.2.0; ICU 76.1;+ meson 1.7.0 / ninja 1.12.1; 8 CPUs; `postgres (PostgreSQL) 20devel`.+* **Build configuration** of every worktree:+ `meson setup build --prefix=<worktree>/inst -Dcassert=true+ -Dtap_tests=enabled -Dicu=enabled -Dssl=openssl -Dbuildtype=debug`.+* **OS locales generated for the hunt** (`locale -a`): `C C.utf8 POSIX+ de_DE.iso88591 de_DE.utf8 el_GR.utf8 en_US.iso88591 en_US.utf8 fr_FR.utf8+ ja_JP.eucjp ja_JP.utf8 lt_LT.utf8 ru_RU.koi8r tr_TR.iso88599 tr_TR.utf8`.+ Of the non-UTF8 multibyte locales, only **`ja_JP.eucjp`** (EUC_JP) is+ installed on this host; the EUC_KR and EUC_CN arms of the test skip cleanly.++### Workflow shape++The user asked for a "large workflow" of at most 90 agents. It ran in four+waves over roughly seventy agents. **Wave 0** (orchestrator) attached one+dedicated git worktree and a fresh `copilot/<short>-defect-tests` branch per+commit under review (eight commits). **Wave 1** (hunters) ran five to six+independent read-only hunter agents on each commit — spread across the six LLM+models listed above so that different reasoning styles would surface different+bugs — each following a fixed method: study the diff and its master context,+check `git log <commit>..master` for follow-up fixes, analyze from an assigned+angle (diff-semantics, consumer-side, libc internals, Unicode/encoding,+robustness), and empirically reproduce every candidate against a server built+from master. **Wave 2** (triage, one agent per commit) verified, deduplicated+and classified the hunters' candidates into TESTABLE vs REPORT-ONLY vs REJECTED,+re-reproducing the survivors from scratch. **Wave 3** (writers, one agent per+commit — this agent) wrote the in-tree test and this report and committed them.+For commit 630706c the same wrong-results defect (T1 below) was found+independently by four hunters running on four different models, which is why it+is the flagship result of the run.++---++## Defects covered by the accompanying test++### T1 — Indexed `ILIKE` drops case-equivalent rows in non-UTF8 multibyte libc databases++* **File:line (master `481052c`):**+ * `src/backend/utils/adt/pg_locale_libc.c:438` —+ `ctype_methods_libc_other_mb.wc_iscased = wc_iscased_libc_sb` (**the wrong+ pointer**; compare `:461`, where the UTF8 table uses `wc_iscased_libc_mb`).+ * `src/backend/utils/adt/pg_locale_libc.c:209-215` — `wc_iscased_libc_sb()`,+ whose body is `if (wc > UCHAR_MAX) return false;` then+ `isupper_l((unsigned char) wc, …) || islower_l((unsigned char) wc, …)`.+ * `src/backend/utils/adt/pg_locale_libc.c:837-840` — selection: a non-C-ctype+ libc locale gets `ctype_methods_libc_other_mb` exactly when+ `GetDatabaseEncoding() != PG_UTF8 && pg_database_encoding_max_length() > 1`.+ * `src/backend/utils/adt/like_support.c:1135` — the consumer:+ `if (pg_iswcased(wpatt[wpos], locale)) break;` inside+ `like_fixed_prefix_ci()`.+ * `src/backend/utils/adt/pg_locale.c:1615` and+ `src/include/utils/pg_locale.h:221` — the `pg_iswcased()` dispatch and+ declaration.++* **Root cause.** `pg_iswcased()` must answer "does this character have more+ than one case form?" so that `like_fixed_prefix_ci()` can decide whether a+ leading pattern character may be used verbatim in a case-**sensitive** B-tree+ index range. For a non-UTF8 multibyte libc database the call lands in+ `ctype_methods_libc_other_mb.wc_iscased`, which commit 630706c set to the+ single-byte `wc_iscased_libc_sb`. That function only inspects the low byte of+ `wc` (`(unsigned char) wc`) and, after dbbeafe's guard, returns `false`+ outright for every `wc > UCHAR_MAX`. Every multibyte character in these+ encodings has a `pg_wchar` above 255, so `pg_iswcased()` returns **false for+ all of them** — including genuinely cased fullwidth Latin letters.+ `like_fixed_prefix_ci()` then treats such a letter as a fixed, case-invariant+ prefix and builds the index range `[A, greaterstr(A))`. In EUC_JP fullwidth+ `A` = `0xA3C1` and fullwidth `a` = `0xA3E1`, and `greaterstr(A)` = `B` =+ `0xA3C2`, so the range `[0xA3C1, 0xA3C2)` **excludes `a`** even though+ `ILIKE 'A%'` must match it. The index scan therefore misses the lowercase+ row while the sequential scan (which re-checks the full `ILIKE` predicate)+ returns it.++* **How 630706c and dbbeafe interact (be precise).**+ * **630706c** created the `pg_iswcased()` contract and *installed the wrong+ function pointer*: `wc_iscased_libc_sb` in `ctype_methods_libc_other_mb`+ instead of a multibyte-aware classifier. As originally committed,+ `wc_iscased_libc_sb` had no range guard and simply truncated `wc` to its low+ byte (`(unsigned char) wc`), so for a multibyte character it returned an+ ill-defined, byte-dependent answer. At that point the value was computed+ but not yet consumed anywhere, so it was latent.+ * **dbbeafe** ("pg_locale_libc.c: add guards to ctype methods.",+ Reported-by: Noah Misch) added `if (wc > UCHAR_MAX) return false;` to+ `wc_iscased_libc_sb` — a correct and necessary guard for the single-byte+ table and for 16-bit `wchar_t` platforms. Applied to the *mis-wired*+ multibyte table, it converted 630706c's ill-defined truncation into a+ deterministic, portable **"false for every multibyte character."**+ * **9c8de15** ("Use multibyte-aware extraction of pattern prefixes.", whose+ message explicitly cites "the new pg_iswcased() API introduced in+ 630706ced0") added the consumer `like_fixed_prefix_ci()`, turning the+ always-false classification into user-visible **wrong query results**.++ The defect is attributable to **630706c**: it defined the API and chose the+ classifier that cannot describe the very characters the API exists to+ describe. dbbeafe and 9c8de15 are the correct guard and the correct consumer;+ neither is wrong on its own.++* **Why the "obvious" one-line fix is insufficient (verified, and a correction+ to the triage's stated fix).** The natural fix suggested by the sibling+ tables — point `ctype_methods_libc_other_mb.wc_iscased` at the wide-character+ `wc_iscased_libc_mb`, as the UTF8 table does — **does not fix the bug.** I+ built it and re-ran the test; the index scan still returns 1 row. The reason+ is a `pg_wchar`/`wchar_t` representation mismatch:++ * For **UTF8**, PostgreSQL's `pg_wchar` *is* the Unicode code point, and glibc's+ `wchar_t` for a UTF-8 locale is *also* the Unicode code point, so+ `wc_iscased_libc_mb`'s `iswupper_l((wint_t) wc, …)` works. That is why the+ UTF8 table is correct and UTF8 databases are unaffected.+ * For **EUC_JP** and the other non-UTF8 multibyte encodings, `pg_mb2wchar`+ packs the *encoded bytes* into `pg_wchar` — fullwidth `A` becomes `0xA3C1`+ (`src/common/wchar.c:129-134`, `pg_euc2wchar_with_len`). glibc's `wchar_t`+ for the same character in a `ja_JP.eucjp` locale is the Unicode value+ `0xFF21`, **not** `0xA3C1`. So `iswupper_l(0xA3C1, ja_JP.eucjp)` is `0`:+ `wc_iscased_libc_mb` returns `false` for these characters too. (Confirmed+ with a small C program: `iswupper_l(0xA3C1)=0` but `iswupper_l(0xFF21)=1`,+ and `mbtowc()` of the EUC-JP bytes `A3 C1` yields `wchar_t 0xFF21`.)++ A correct fix must bridge that gap — e.g. convert the `pg_wchar` back through+ the database encoding to a libc `wchar_t` before calling `iswupper_l`/+ `iswlower_l` (the path `char2wchar()`/`strlower_libc_mb()` already take for+ case *mapping*), or teach `like_fixed_prefix_ci()` to distrust `pg_iswcased()`+ for these encodings and decline the case-sensitive prefix. Because the+ precise remedy is a design choice, **the test deliberately asserts only the+ required end state** (see below) and is agnostic to which remedy is chosen.++* **User-visible symptom.** In a libc EUC_JP (or EUC_CN/EUC_KR/EUC_TW/…)+ database with a case-mapping locale, a query such as+ `SELECT w FROM t WHERE w ILIKE 'A%'` returns **different rows depending on the+ plan**: a sequential scan returns both `A…` and `a…` rows, but a+ `text_pattern_ops` index scan returns only the `A…` row. No error is raised;+ the answer is simply, silently wrong, and depends on cost estimates and GUCs.++* **How the test detects it.** `t/001_ilike_eucjp.pl` `initdb`s a libc EUC_JP+ cluster with `--lc-ctype=ja_JP.eucjp`, builds a table with a fullwidth-`A`+ row, a fullwidth-`a` row and 100 filler rows, and a `text_pattern_ops` index.+ It then runs the identical `w ILIKE 'A%'` query twice — once with+ `enable_indexscan=off, enable_bitmapscan=off` (forced sequential scan) and once+ with `enable_seqscan=off` (forced index scan) — and asserts:+ 1. a **plan-independence oracle**: the two result sets are equal+ (`EXCEPT` count = 0, and the row counts match); and+ 2. the **correct absolute answer**: both scans return *both* the uppercase and+ lowercase fullwidth rows.+ On master the forced-index arm returns 1 row and the forced-seq arm returns 2,+ so the oracle fails with `index scan returned 1 rows, seq scan returned 2 rows`+ and the absolute-answer checks fail (`idx_has_lower = 0`). The two-part design+ means the test cannot be satisfied by a "both paths equally wrong" change+ (e.g. the insufficient `wc_iscased_libc_mb` swap above, under which both the+ plan-independence oracle *and* the absolute-answer check remain red). The+ EUC_JP byte sequences are generated in Perl as pure-ASCII `E'\xa3\xc1'`-style+ escapes, so the test is independent of the shell's and the file's own+ encoding.++ The test guards every precondition and **skips** (never hard-fails) on:+ Windows (`$windows_os`); a missing OS locale (it probes the glibc spellings+ `ja_JP.eucjp`, `ja_JP.eucJP`, `ja_JP.EUC-JP`, `ja_JP.ujis` via `setlocale`);+ an `initdb` that refuses the encoding/locale (a trial `initdb` into a tempdir);+ and a locale that does not actually case-map the test character+ (`SELECT lower(A) = a`, which exercises the independent `strlower_libc_mb`+ path, so it cannot mask the `pg_iswcased` bug). EUC_KR and EUC_CN arms are+ included and skip individually when their locales are absent.++**Test home — justification.** A plain `pg_regress` test cannot reach this bug:+the database encoding is fixed at `initdb` time, and the only encoding an+ordinary regression run can assume is the build default (typically UTF8), which+is *not* affected. Reproducing it requires an `initdb --encoding=EUC_JP+--locale-provider=libc --lc-ctype=ja_JP.eucjp` cluster, which needs+`PostgreSQL::Test::Cluster`. A brand-new module+`src/test/modules/test_ilike_libc_mb/` was chosen (over grafting onto an+existing suite) because no existing suite is about libc multibyte ctype+behavior, the new directory is the natural home for this report next to its+test, and it keeps the locale-gated, encoding-specific setup self-contained. It+is registered for both build systems: `src/test/modules/meson.build` and+`src/test/modules/Makefile`, plus the module's own `meson.build` and `Makefile`+(`TAP_TESTS = 1`).++---++## Defects found but NOT covered by a test++**None.** Triage classified exactly one qualifying user-visible defect for this+commit (T1 above), and it is fully testable in-tree, so there is no REPORT-ONLY+defect. The candidates that were investigated and set aside are negative+results, documented in the next section rather than here.++---++## Candidates examined and rejected++* **X1 — "It is deliberate design; the single-byte classifier is intentional."**+ One hunter (robustness angle) argued that the comment above+ `ctype_methods_libc_other_mb` — *"Non-UTF8 multibyte encodings use multibyte+ semantics for case mapping, but single-byte semantics for pattern matching"*+ (`pg_locale_libc.c:417-418`) — sanctions the single-byte `wc_iscased`.+ **Rejected.** That comment governs the *symmetric* regex character-class+ classifiers (`wc_isalpha`, `wc_isupper`, …), where the same single-byte+ decision is applied on both the index side and the recheck side, so no wrong+ results can arise. `wc_iscased` is different: it is consumed *asymmetrically*+ by `like_fixed_prefix_ci()` to build a case-**sensitive** index bound that the+ executor's `ILIKE` recheck does **not** reproduce. A "single-byte" answer here+ is not a documented semantics choice, it is a plan-dependent wrong result.+ Triage re-reproduced `seq=2 vs idx=1` to confirm.++* **X2 — The other `_sb` entries in `ctype_methods_libc_other_mb`+ (`wc_isalpha`, `wc_isupper`, `wc_islower`, `wc_isdigit`, `wc_isspace`, …).**+ These are also single-byte, and are equally unable to classify multibyte+ characters. **Rejected as not attributable and not wrong-results.** They+ predate 630706c (they were not touched by it), and, unlike `wc_iscased`, they+ feed the *symmetric* regex path where both scan sides agree — so there is no+ user-visible wrong answer to attribute.++* **X3 — UTF8 `pg_iswcased()` mis-classifies characters (ß, İ, ff, final σ, …).**+ **Rejected.** For UTF8, `pg_wchar` equals the Unicode code point and the UTF8+ table correctly uses `wc_iscased_libc_mb`; libc, ICU and the builtin provider+ were checked and all return `seq = idx = 2` with no case-sensitive prefix+ range. UTF8 databases are unaffected.++* **X4 — `~*` (regex case-insensitive), `citext`, and plain `LIKE`.**+ **Rejected — not affected.** `~*` extracts its prefix via+ `regex_fixed_prefix()`, which does not call `pg_iswcased()`; `citext ILIKE`+ extracts no prefix range (filter-only); plain case-sensitive `LIKE` uses+ `like_fixed_prefix()`, which never calls `pg_iswcased()`.++* **X5 — NULL-pointer / crash paths in the new API.** **Rejected — not+ user-visible** as a distinct defect; no reachable crash was reproduced.++**Follow-up commits between 630706c and master on the touched files**+(`git log --oneline 630706c..master -- src/backend/utils/adt/pg_locale_libc.c+src/backend/utils/adt/like_support.c src/include/utils/pg_locale.h`), none of+which fixes T1:++```+cc33580 Fix like_fixed_prefix_ci() selectivity.+e615da8 Fix for loop variables+e6e08dc pg_locale_libc.c: add missing casts to unsigned char.+dbbeafe pg_locale_libc.c: add guards to ctype methods.+3ab2abc Fix obsolete comment.+2d7808e Fix LIKE/regex optimization for indexscan with exact-match pattern.+6d22c67 Don't accept length of -1 in pg_locale.h APIs.+b2869eb Fix integer-overflow and alignment hazards in locale-related code.+de90bb7 Fix theoretical memory leaks in pg_locale_libc.c.+af2d4ca Clean up ICU includes.+c4ff35f ICU: use UTF8-optimized case conversion API+451c439 Update copyright for 2026+0a90df5 Avoid global LC_CTYPE dependency in pg_locale_icu.c.+87b2968 downcase_identifier(): use method table from locale provider.+24bf379 Clarify a #define introduced in 8d299052fe.+54c41a6 Remove unused single-byte char_is_cased() API.+9c8de15 Use multibyte-aware extraction of pattern prefixes.+```++`dbbeafe` added the guard and `9c8de15` added the consumer (both discussed under+T1); `cc33580` and `2d7808e` adjust `like_fixed_prefix_ci()` selectivity and an+exact-match case but leave the mis-classification intact. The bug is verified+still live on master by the accompanying test.++---++## How to reproduce++### With the accompanying test (watch it fail on master)++```bash+cd /home/nm/src/pg/wt-630706c+ninja -C build+meson test -C build --suite setup --suite test_ilike_libc_mb --print-errorlogs+```++Expected on unmodified master (3 of 21 assertions fail; EUC_KR/EUC_CN skip):++```+not ok 5 - EUC_JP: indexed ILIKE returns the uppercase and lowercase rows+not ok 6 - EUC_JP: indexed ILIKE drops no row that the sequential scan returns+not ok 7 - EUC_JP: indexed and sequential ILIKE agree (index scan returned 1 rows, seq scan returned 2 rows)+```++### By hand (manual SQL)++```bash+export PATH=/home/nm/src/pg/wt-630706c/inst/bin:$PATH+export LD_LIBRARY_PATH=/home/nm/src/pg/wt-630706c/inst/lib+D=$(mktemp -d /tmp/pgdata-XXXXXX)+initdb -D "$D/data" -U postgres --locale-provider=libc \+ --encoding=EUC_JP --lc-collate=ja_JP.eucjp --lc-ctype=ja_JP.eucjp >/dev/null+pg_ctl -D "$D/data" -l "$D/log" -o "-p 6370 -k $D" start+psql -h "$D" -p 6370 -U postgres -d postgres <<'SQL'+CREATE TABLE t (w text);+INSERT INTO t VALUES (E'\xa3\xc1'), (E'\xa3\xe1'); -- fullwidth A, a+INSERT INTO t SELECT md5(g::text) FROM generate_series(1,100) g;+CREATE INDEX t_tpo ON t (w text_pattern_ops);+ANALYZE t;+SET enable_indexscan=off; SET enable_bitmapscan=off; SET enable_seqscan=on;+SELECT count(*) AS seq_rows FROM t WHERE w ILIKE E'\xa3\xc1%'; -- expect 2+SET enable_indexscan=on; SET enable_bitmapscan=on; SET enable_seqscan=off;+SELECT count(*) AS idx_rows FROM t WHERE w ILIKE E'\xa3\xc1%'; -- master: 1 (WRONG)+SQL+pg_ctl -D "$D/data" stop -m immediate+rm -rf "$D"+```++On master `seq_rows = 2` but `idx_rows = 1`: the indexed `ILIKE` silently drops+the lowercase fullwidth row.diff --git a/src/test/modules/test_ilike_libc_mb/Makefile b/src/test/modules/test_ilike_libc_mb/Makefilenew file mode 100644index 0000000..bb1795f--- /dev/null+++ b/src/test/modules/test_ilike_libc_mb/Makefile@@ -0,0 +1,16 @@+# src/test/modules/test_ilike_libc_mb/Makefile++PGFILEDESC = "test_ilike_libc_mb - ILIKE index prefixes in libc multibyte DBs"++TAP_TESTS = 1++ifdef USE_PGXS+PG_CONFIG = pg_config+PGXS := $(shell $(PG_CONFIG) --pgxs)+include $(PGXS)+else+subdir = src/test/modules/test_ilike_libc_mb+top_builddir = ../../../..+include $(top_builddir)/src/Makefile.global+include $(top_srcdir)/contrib/contrib-global.mk+endifdiff --git a/src/test/modules/test_ilike_libc_mb/meson.build b/src/test/modules/test_ilike_libc_mb/meson.buildnew file mode 100644index 0000000..f845649--- /dev/null+++ b/src/test/modules/test_ilike_libc_mb/meson.build@@ -0,0 +1,12 @@+# Copyright (c) 2026, PostgreSQL Global Development Group++tests += {+ 'name': 'test_ilike_libc_mb',+ 'sd': meson.current_source_dir(),+ 'bd': meson.current_build_dir(),+ 'tap': {+ 'tests': [+ 't/001_ilike_eucjp.pl',+ ],+ },+}diff --git a/src/test/modules/test_ilike_libc_mb/t/001_ilike_eucjp.pl b/src/test/modules/test_ilike_libc_mb/t/001_ilike_eucjp.plnew file mode 100644index 0000000..fed4381--- /dev/null+++ b/src/test/modules/test_ilike_libc_mb/t/001_ilike_eucjp.pl@@ -0,0 +1,239 @@+# Copyright (c) 2026, PostgreSQL Global Development Group++# Regression test for pg_iswcased() on libc non-UTF8 multibyte encodings.+#+# Commit 630706ced0 ("Add pg_iswcased().") wired the single-byte classifier+# wc_iscased_libc_sb into ctype_methods_libc_other_mb, the ctype method table+# used for libc databases whose encoding is a non-UTF8 multibyte encoding+# (EUC_JP, EUC_KR, EUC_CN, EUC_TW, EUC_JIS_2004, MULE_INTERNAL). Together with+# the "if (wc > UCHAR_MAX) return false;" guard later added by dbbeafe61a, this+# makes pg_iswcased() report *every* multibyte character as "not cased".+#+# like_fixed_prefix_ci() (like_support.c) trusts pg_iswcased() when it extracts+# the fixed, case-invariant prefix of an ILIKE pattern for use as a B-tree index+# range qual. When a genuinely cased multibyte letter is wrongly reported as+# uncased, the letter is filed into a case-sensitive prefix range, so an indexed+# ILIKE silently returns fewer rows than a sequential scan of the same table.+#+# We assert two things that must hold on a correct server, independent of any+# platform-specific expected output:+# * the same ILIKE query returns identical row sets under a forced sequential+# scan and under a forced index scan (plan independence), and+# * both scans return both the uppercase- and lowercase-fullwidth rows (the+# correct absolute answer).+# On unpatched master the index scan drops the lowercase row and the test fails.+# A correct server returns identical, complete row sets from both scans. Note+# that the fix is subtler than the sibling method tables suggest: merely swapping+# in wc_iscased_libc_mb (the classifier used by the UTF8 table) does NOT help,+# because for a non-UTF8 multibyte encoding PostgreSQL's pg_wchar is the packed+# encoded bytes (fullwidth A is 0xA3C1), not a libc wchar_t (glibc yields 0xFF21),+# so iswupper_l()/iswlower_l() still misclassify it. A correct fix must bridge+# that representation gap, or teach like_fixed_prefix_ci() to distrust the result+# for these encodings. This test asserts the required end state and is agnostic+# to which of those a fix chooses.++use strict;+use warnings FATAL => 'all';++use PostgreSQL::Test::Cluster;+use PostgreSQL::Test::Utils;+use Test::More;+use POSIX qw(setlocale LC_CTYPE);++# This test needs an OS locale that case-maps multibyte letters in a non-UTF8+# multibyte encoding. Such locales never exist on Windows.+if ($windows_os)+{+ plan skip_all => "libc EUC_* locales are not available on Windows";+}++# Encodings to probe. Each entry names a PostgreSQL server encoding, a list of+# plausible OS-locale spellings (glibc spells the EUC-JP locale several ways),+# and the encoding bytes of a cased letter pair: a fullwidth Latin capital+# letter and the small letter it lower-cases to. All three encodings place the+# fullwidth ASCII block in row 3, so fullwidth "A"/"a" are 0xA3C1/0xA3E1.+my @cases = (+ {+ name => 'eucjp',+ encoding => 'EUC_JP',+ locales => [qw(ja_JP.eucjp ja_JP.eucJP ja_JP.EUC-JP ja_JP.ujis)],+ upper => 'a3c1', # U+FF21 FULLWIDTH LATIN CAPITAL LETTER A+ lower => 'a3e1', # U+FF41 FULLWIDTH LATIN SMALL LETTER A+ },+ {+ name => 'euckr',+ encoding => 'EUC_KR',+ locales => [qw(ko_KR.euckr ko_KR.eucKR ko_KR.EUC-KR)],+ upper => 'a3c1',+ lower => 'a3e1',+ },+ {+ name => 'euccn',+ encoding => 'EUC_CN',+ locales => [qw(zh_CN.gb2312 zh_CN.GB2312 zh_CN.eucCN zh_CN.euccn)],+ upper => 'a3c1',+ lower => 'a3e1',+ },+);++# Build a PostgreSQL string literal of the given hex bytes using \x escapes,+# e.g. "a3c1" => E'\xa3\xc1'. The escapes are pure ASCII in the query text, so+# the literal is independent of the client encoding and of this file's own+# encoding; the server materializes the exact bytes and validates them against+# the database encoding.+sub byte_literal+{+ my ($hex, $suffix) = @_;+ $suffix = '' unless defined $suffix;+ (my $escaped = $hex) =~ s/(..)/\\x$1/g;+ return "E'$escaped$suffix'";+}++# Return the first candidate OS locale that the C library actually has, or+# undef. The process LC_CTYPE is saved and restored around the probe.+sub probe_locale+{+ my ($candidates) = @_;+ my $saved = setlocale(LC_CTYPE);+ my $found;+ foreach my $cand (@$candidates)+ {+ if (defined setlocale(LC_CTYPE, $cand))+ {+ $found = $cand;+ last;+ }+ }+ setlocale(LC_CTYPE, $saved) if defined $saved;+ return $found;+}++foreach my $case (@cases)+{+ my $enc = $case->{encoding};+ my $U = byte_literal($case->{upper});+ my $L = byte_literal($case->{lower});+ my $Upat = byte_literal($case->{upper}, '%');++ # Each encoding contributes this many test points when it runs; the same+ # count is emitted as skips when a precondition is not met.+ my $ntests = 7;++ SKIP:+ {+ # Skip this encoding unless its OS locale is installed here.+ my $locale = probe_locale($case->{locales});+ skip "no OS locale for $enc installed on this system", $ntests+ unless defined $locale;++ # Skip if initdb refuses this encoding/locale for any reason, rather+ # than failing the whole test file.+ my $trial = PostgreSQL::Test::Utils::tempdir();+ my $initdb_ok = run_log(+ [+ 'initdb', '--no-sync',+ '--auth' => 'trust',+ '--pgdata' => "$trial/data",+ '--locale-provider=libc',+ "--encoding=$enc",+ "--lc-collate=$locale",+ "--lc-ctype=$locale",+ ]);+ skip "initdb with $enc/$locale failed on this system", $ntests+ unless $initdb_ok;++ my $node = PostgreSQL::Test::Cluster->new($case->{name});+ $node->init(+ extra => [+ '--locale-provider=libc',+ "--encoding=$enc",+ "--lc-collate=$locale",+ "--lc-ctype=$locale",+ ]);+ $node->start;++ # The bug can only manifest where the locale actually case-maps the+ # chosen letter. If it does not, there is no cased character to+ # mis-file, so skip rather than emit a meaningless result.+ my $is_cased =+ $node->safe_psql('postgres', "SELECT lower($U) = $L AND $U <> $L");+ unless ($is_cased eq 't')+ {+ $node->stop('immediate');+ skip "locale $locale does not case-map the $enc test character",+ $ntests;+ }++ $node->safe_psql(+ 'postgres', qq{+ CREATE TABLE t (w text);+ INSERT INTO t VALUES ($U), ($L);+ -- filler so an index scan is unambiguously viable+ INSERT INTO t SELECT md5(g::text) FROM generate_series(1, 100) g;+ CREATE INDEX t_tpo ON t (w text_pattern_ops);+ ANALYZE t;+ });++ # Guard: the forced-index arm of the oracle must really use the index,+ # otherwise the oracle could pass vacuously by comparing a sequential+ # scan against itself on a buggy server.+ my $plan = $node->safe_psql(+ 'postgres', qq{+ SET enable_seqscan = off;+ SET enable_indexscan = on;+ SET enable_bitmapscan = on;+ EXPLAIN (COSTS OFF) SELECT w FROM t WHERE w ILIKE $Upat;+ });+ like(+ $plan,+ qr/Index (Only )?Scan|Bitmap Index Scan/,+ "$enc: forced-index ILIKE plan uses the index");+ unlike($plan, qr/Seq Scan/,+ "$enc: forced-index ILIKE plan is not a sequential scan");++ # Oracle: run the identical ILIKE once as a forced sequential scan and+ # once as a forced index scan, and compare the row sets.+ my $res = $node->safe_psql(+ 'postgres', qq{+ SET enable_indexscan = off;+ SET enable_bitmapscan = off;+ SET enable_seqscan = on;+ CREATE TEMP TABLE r_seq AS SELECT w FROM t WHERE w ILIKE $Upat;+ SET enable_indexscan = on;+ SET enable_bitmapscan = on;+ SET enable_seqscan = off;+ CREATE TEMP TABLE r_idx AS SELECT w FROM t WHERE w ILIKE $Upat;+ SELECT (SELECT count(*) FROM r_seq),+ (SELECT count(*) FROM r_idx),+ (SELECT count(*) FROM r_seq WHERE w = $U),+ (SELECT count(*) FROM r_seq WHERE w = $L),+ (SELECT count(*) FROM r_idx WHERE w = $U),+ (SELECT count(*) FROM r_idx WHERE w = $L),+ (SELECT count(*) FROM+ (SELECT w FROM r_seq EXCEPT SELECT w FROM r_idx) d);+ });+ my ($seq_n, $idx_n, $seq_u, $seq_l, $idx_u, $idx_l, $missing) =+ split /\|/, $res;++ # Correct absolute answer: both scans must return both fullwidth rows.+ is($seq_n, 2, "$enc: sequential ILIKE returns both fullwidth rows");+ is("$seq_u$seq_l", "11",+ "$enc: sequential ILIKE returns the uppercase and lowercase rows");+ is("$idx_u$idx_l", "11",+ "$enc: indexed ILIKE returns the uppercase and lowercase rows");++ # Plan independence: the index scan must not drop rows the seq scan+ # returns, and the two counts must match.+ is($missing, 0,+ "$enc: indexed ILIKE drops no row that the sequential scan returns"+ );+ is($idx_n, $seq_n,+ "$enc: indexed and sequential ILIKE agree "+ . "(index scan returned $idx_n rows, seq scan returned $seq_n rows)"+ );++ $node->stop('immediate');+ }+}++done_testing();
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-hackers@postgresql.org
Cc: noah@leadboat.com, pgsql@j-davis.com
Subject: Re: EUC_* ILIKE index scan stopped matching seq scan in v19
In-Reply-To: <20260826221526.89.noahmisch@microsoft.com>
References: <20260826221526.89.noahmisch@microsoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox