Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4
-
Unexpected results
Description
In utf8mb4_german2_ci, 'ß' is equal to 'ss', so a CHAR ( 1 ) column holding 'ß' should match the constant 'ss'. However, an index lookup with that constant misses the row:
SET NAMES utf8mb4; |
CREATE TABLE t1 (v CHAR(1) COLLATE utf8mb4_german2_ci, KEY(v)); |
INSERT INTO t1 VALUES ('ß'),('s'),('x'); |
SELECT v FROM t1 WHERE v = 'ss'; |
DROP TABLE t1; |
|
The query returns an empty set. It should return ß.
PostgreSQL 18.1 returns ß for the equivalent case. It uses a nondeterministic ICU collation at primary strength, under which 'ß' equals 'ss'. Turning
off enable_seqscan makes the planner use the index on a table this small.
CREATE COLLATION de_ss (provider = icu, locale = 'de-u-ks-level1', |
deterministic = false); |
CREATE TABLE t1 (v char(1) COLLATE de_ss); |
CREATE INDEX ON t1 (v); |
INSERT INTO t1 VALUES ('ß'), ('s'), ('x'); |
SET enable_seqscan = off; |
SELECT v FROM t1 WHERE v = 'ss'; |
DROP TABLE t1; |
DROP COLLATION de_ss; |
|