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