Comment 2 for bug 1968196

Revision history for this message
Jeff Davis (jdavis-sitka) wrote (last edit ):

It looks like acq search uses the Postgres ILIKE operator, which is supposed to do case-insensitive string matching. I did a search where Line Item Attribute - Title of work contains "Čestina" and it produced this cstore call:

CALL: open-ils.cstore open-ils.cstore.json_query.atomic {"order_by":{"jub":{"id":{}}},"from":{"jub":{"acqlia":{"field":"lineitem","type":"left","fkey":"id"}}},"limit":10,"offset":0,"select":{"jub":[{"column":"id","transform":"distinct"}]},"where":{"-and":[{"+jub":{"id":{">=":"0"}}},{"-exists":{"select":{"acqlia":["id"]},"where":{"-and":[{"lineitem":{"=":{"+jub":"id"}}},{"definition":"1"},{"attr_value":{"ilike":"%\u010cestina%"}}]},"from":"acqlia"}}]}}

I tested ILIKE directly (using the psql command-line client in a UTF-8 environment) in an Evergreen database that uses C collation, and it didn't handle case folding properly for the example provided:

evergreen=# select case when 'čestina' ilike 'Čestina' then true else false end;
 case
------
 f
(1 row)

evergreen=# select case when 'hello' ilike 'Hello' then true else false end;
 case
------
 t
(1 row)

However, specifying a collation directly in the query seemed to work:

evergreen=# select case when 'čestina' collate "C.utf8" ilike 'Čestina' collate "C.utf8" then true else false end;
 case
------
 t
(1 row)

(Comment edited with new results since there was a typo in my previous tests.)