Re: BUG #15285: Query used index over field with ICU collation in some cases wrongly return 0 rows

On Thu, Jul 19, 2018 at 9:03 AM, PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> How to reproduce:
> CREATE COLLATION digitslast (provider = icu, locale =
> 'en(at)colReorder=latn-digit');
> CREATE TABLE t (b CHAR(4) NOT NULL COLLATE digitslast);
> insert into t select '0000' from generate_series (0, 1000) as f(x);
> insert into t select '0001' from generate_series (0, 1000) as f(x);
> insert into t select 'ABCD' from generate_series (0, 1000) as f(x);

I can confirm the bug on the master branch:

pg(at)~[25013]=# select bt_index_parent_check('i');
ERROR: item order invariant violated for index "i"
DETAIL: Lower index tid=(3,3) (points to index tid=(4,23)) higher
index tid=(3,4) (points to index tid=(5,98)) page lsn=0/169CD78.

It looks like a problem with char(n) abbreviated keys not agreeing
with B-Tree support function 1 for the same opclass. "ABCD" appears
before "0000" and "0001" in the index, which seems like the expected
behavior:

pg(at)~[25013]=# select * from bt_page_items('i', 3);
itemoffset │ ctid │ itemlen │ nulls │ vars │ data
────────────┼──────────┼─────────┼───────┼──────┼─────────────────────────
1 │ (1,0) │ 8 │ f │ f │
2 │ (2,109) │ 16 │ f │ t │ 0b 41 42 43 44 00 00 00
3 │ (4,23) │ 16 │ f │ t │ 0b 41 42 43 44 00 00 00
4 │ (5,98) │ 16 │ f │ t │ 0b 30 30 30 30 00 00 00
5 │ (6,12) │ 16 │ f │ t │ 0b 30 30 30 30 00 00 00
6 │ (7,152) │ 16 │ f │ t │ 0b 30 30 30 30 00 00 00
7 │ (8,66) │ 16 │ f │ t │ 0b 30 30 30 31 00 00 00
8 │ (9,206) │ 16 │ f │ t │ 0b 30 30 30 31 00 00 00
9 │ (10,120) │ 16 │ f │ t │ 0b 30 30 30 31 00 00 00
(9 rows)

(This is the root page.)

It appears that the main support function 1 routine disagrees with the
CREATE INDEX sort order, which is wrong. I'll try to isolate the
problem a bit further.

--
Peter Geoghegan