PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15892
PG Version11.4
OSLinux Mint 19 Tara / Mac OS X 10.13.6
Opened2019-07-03 22:50:17+00
Reported byJames Inform
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15892
Logged by:          James Inform
Email address:      (redacted)
PostgreSQL version: 11.4
Operating system:   Linux Mint 19 Tara / Mac OS X 10.13.6
Description:        

-- This is tested under Linux Mint 19 Tara with uconv v2.1  ICU 60.2
-- and Mac OS X 10.13.6 with uconv v2.1  ICU 58.2
-- PostgreSQL Version 11.4
-- PostgreSQL was built with: ./configure '--prefix=/home/myself/pg114'
'--with-systemd' '--with-icu' '--with-libxml' '--with-libxslt' '--with-perl'
'--with-python' '--disable-rpath' '--with-extra-version= ICU'

-- STEPS to reproduce
-- First you need PostgreSQL 11.4 built with ICU support 

create database testdb;

\c testdb;

-- Just create a simple table with one column
create table icutest(data text not null collate "de-x-icu" primary key);

-- Insert a record with uppercase string
insert into icutest values ('MYTEST');

-- This is not giving a match
select * from icutest where data ilike 'mytest';

-- These two queries give the exspected result
select * from icutest where lower(data) ilike 'mytest';
select * from icutest where data ilike ('mytest' collate "default");

-- If you do the same with a non primary source column, then everything
works like exspected
-- I'm not an Expert, but maybe the index behind the primary key is based on
the wrong collation

Messages

DateAuthorSubject
2019-07-03 22:50:17+00PG Bug reporting formBUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query
2019-07-04 10:36:59+00"Daniel Verite"Re: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query
2019-07-04 13:41:45+00James InformRe: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query
2019-07-04 16:40:11+00James InformRe: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query
2019-07-04 16:50:27+00"Daniel Verite"Re: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query
2019-07-04 17:57:19+00Arthur ZakirovRe: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query
2019-08-11 21:32:49+00Tom LaneRe: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query
2019-08-12 09:12:13+00Arthur ZakirovRe: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query
2019-08-12 17:17:38+00Tom LaneRe: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query