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

On Thu, Jul 19, 2018 at 9:03 AM, PG Bug reporting form
<noreply@postgresql.org> wrote:
> How to reproduce:
> CREATE COLLATION digitslast (provider = icu, locale =
> 'en@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@~[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@~[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