Re: BUG #15892: URGENT: Using an ICU collation in a primary key column breaks ILIKE query

James Inform wrote:

> -- This is not giving a match
> select * from icutest where data ilike 'mytest';
>
> -- BUT THIS GIVES A MATCH:
>
> select * from icutest where data2 ilike 'mytest';
>
> -- So it seems to be especially related to the scenario where a primary key
> / index exists.
>

Yes. It is because of the index that the code checks if the ILIKE
can be evaluated with an index lookup. Otherwise it doesn't.

If you feel like recompiling with a temporary fix against v11.4 to do
your own tests, please try the attached patch.

Here's the result for me:

* Unpatched version:

postgres=# explain analyze select * from icutest where data ilike 'mytest';
QUERY PLAN

-------------------------------------------------------------------------------------------------------
---------------------
Index Only Scan using icutest_pkey on icutest (cost=0.15..8.17 rows=1
width=32) (actual time=0.013..0
.013 rows=0 loops=1)
Index Cond: (data = 'mytest'::text)
Filter: (data ~~* 'mytest'::text)
Heap Fetches: 0
Planning Time: 0.593 ms
Execution Time: 0.035 ms

* Patched version:

postgres=# explain analyze select * from icutest where data ilike 'mytest';
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on icutest (cost=0.00..27.00 rows=1 width=32) (actual
time=0.122..0.123 rows=1 loops=1)
Filter: (data ~~* 'mytest'::text)
Planning Time: 0.081 ms
Execution Time: 0.144 ms

Notice how the patched version ignores the index and correctly finds the
row versus the unpatched version.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite