BUG #19681: ILIKE rejected on nondeterministic collations while LIKE works

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: 303677365(at)qq(dot)com
Subject: BUG #19681: ILIKE rejected on nondeterministic collations while LIKE works
Date: 2026-09-08 07:05:18
Message-ID: 19681-074ed6100d92cc5b@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19681
Logged by: chunling qin
Email address: 303677365(at)qq(dot)com
PostgreSQL version: 18.6
Operating system: x86_64
Description:

On a column with a nondeterministic (case-insensitive) collation, the
pattern-matching operators split into two contradictory groups:
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2',
deterministic = false);
CREATE TABLE ndt(b text COLLATE ci);
INSERT INTO ndt VALUES ('a'),('A'),('str'),('STR'),('Hello');

Group 1 — silently supported (documented for LIKE):
SELECT count(*) FROM ndt WHERE b LIKE 's%'; -- 2 (matches 'str'
and 'STR')
SELECT count(*) FROM ndt WHERE b LIKE 'str'; -- 2
SELECT count(*) FROM ndt WHERE b IN ('str'); -- 2
SELECT count(*) FROM ndt WHERE b = 'str'; -- 1
SELECT count(*) FROM ndt WHERE upper(b) LIKE 'S%'; -- 2 (upper+LIKE:
ILIKE's definition!)
SELECT b LIKE 'str' COLLATE ci FROM ndt WHERE b = 'STR'; -- true
(documented example form)

Group 2 — rejected with an error:
SELECT count(*) FROM ndt WHERE b ILIKE 's%'; -- ERROR:
nondeterministic collations are not supported for ILIKE
SELECT b ILIKE 'str' COLLATE ci FROM ndt; -- ERROR: same
SELECT count(*) FROM ndt WHERE b ~ '^s'; -- ERROR:
nondeterministic collations are not supported for regular expressions
SELECT count(*) FROM ndt WHERE b SIMILAR TO 's%'; -- ERROR: same

The documentation (func-matching.sgml) states that LIKE supports
nondeterministic collations and that SIMILAR TO / POSIX regex do not — but
says nothing about ILIKE. Meanwhile ILIKE's documented definition is exactly
"matches LIKE, but case-insensitively" (implemented as case folding + LIKE),
and that folding path works fine on the same column (upper(b) LIKE 'S%'
returns 2 rows). The citext extension — the other case-insensitive subsystem
— accepts ILIKE without error on its columns, making the ND-collation
rejection of the same operator even more inconsistent.

Browse pgsql-bugs by date

  From Date Subject
Next Message Álvaro Herrera 2026-09-08 07:25:25 Re: REPACK (CONCURRENTLY) doesn't handle invalid indexes
Previous Message PG Bug reporting form 2026-09-08 06:58:16 BUG #19680: FK integrity bypassed by session timezone (orphan rows)