Date: Wed, 2 Sep 2020 14:55:50 +0200
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, "Daniel Verite"
 <daniel@manitou-mail.org>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>, Peter Geoghegan
 <pg@bowt.ie>, =?UTF-8?B?0KDQvtC80LDQvSDQm9C40YLQvtCy0YfQtdC90LrQvg==?=
 <roman.lytovchenko@gmail.com>, PostgreSQL mailing lists
 <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #15285: Query used index over field with ICU collation in
 some cases wrongly return 0 rows
Message-ID: <20200902145550.6a5014fb@firost>
In-Reply-To: <4ea84116-4380-b12f-dcdf-7f2977ab2859@2ndquadrant.com>
References: <153201618542.1404.3611626898935613264@wrigleys.postgresql.org>
	<CAH2-Wz=TGN_03qBmqgFXVhw6Kbfys-EBz4MJunzRq+RB5gL7WA@mail.gmail.com>
	<CAH2-Wzkiue6PQzneBJsuDnYdUVMa5jnevVJXXkV3hR0oboOSbQ@mail.gmail.com>
	<CAEepm=1-f3OuO_zA83C0x8dLFqMA+Q=RGUkyMRp4WXxjSrvbww@mail.gmail.com>
	<20200610002933.6a6d482b@firost>
	<4ea84116-4380-b12f-dcdf-7f2977ab2859@2ndquadrant.com>
Organization: Dalibo
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Wed, 2 Sep 2020 14:06:18 +0200
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:

> On 2020-06-10 00:29, Jehan-Guillaume de Rorthais wrote:
> > In the meantime, I've been working on various workarounds. The only one I
> > found is to use "fr-u-kr-latn-digit-kn" instead of "fr-u-kr-latn-digit".
> > Unfortunately, the two collations are not equivalent, but I believe it
> > might be useful in many case.  
> 
> What precisely is broken in the ICU library? 

Using ucol_strcoll/ucol_strcollUTF8 with a custom collation sorting digits after
latn.

> All the examples so far refer to kr-latn-digit.  Are all reorderings broken,
> or something specifically related to latn and/or digit? 

I don't know. So far, I only found a couple of reports (mine included) using
kr-latn-digit in different languages. And as I wrote, kr-latn-digit-kn doesn't
seem affected. So all reorderings might not be broken.

But I have no strong facts about this, just tests.

> Are any collation customizations other than reorderings affected?

I didn't poke around to try some other random customizations. The answer lies
somewhere in the ICU codebase. I suppose we'll be able to answer this question
as soon as the bug will be explained.

However, the bug reported here are all about sorting: wrong result order and/or
wrong result because of badly sorted index.

Maybe Daniel has some more experience feedback with other customizations as he
seems to work extensively with ICU and PostgreSQL?

Regards,