Search is case-sensitive in non-English languages

[rkfg]

Description

It's hard to find the text you need in languages other than English as case becomes important.

Steps to reproduce

• in a non-English room try to search for a word in lower and upper case
• the results would be quite different
• searching for an English word in upper and lower case shows the same result

Also, here the FTS language is hardcoded as English so no stemming is supported for other languages. I propose lowercasing the event text before inserting it and do the same on querying.

Version information

• Homeserver: own homeserver
• Version: 0.27.4
• Install method: package manager
• Platform: Debian 9.4 on a VDS

[areisp]

There are quite a few articles suggesting to create the postgresql database with LC_COLLATE and LC_CTYPE set to C. Like here.
I don't know if there is a reason for that, (performance-wise maybe?), but recreating the db (and restoring the dump into it) with region specific locale in LC_COLLATE and LC_CTYPE, which is ru_RU.UTF-8 in my case, fixes the problem. The new text is getting indexed just fine.
As for previous conversations, I used some questionable workaround:

UPDATE event_search SET vector = array_to_tsvector(lower(tsvector_to_array(vector)::text)::text[]) ;

Not sure if it was proper and/or safe way.

[MurzNN]

@areisp thanks for workaround description, can it also fix issue element-hq/element-web#7247 with user search? In which table we must execute proposed UPDATE for it?

[ptman]

#6696 (comment)

[ilmari]

The issue is that the C database locale doesn't know how to case-fold non-ASCII letters. Using C.UTF-8 would avoid the index corruption issues when upgrading libc, while still allowing case-insensitive searching for non-ASCII letters.

[532910]

C.UTF-8 fixes this issue, but adds another:

C.UTF-8:

synapse=# select event_id from event_search where vector @@ to_tsquery('можно & забрать');
              event_id              
------------------------------------
 $1566.................:matrix.org
 $1546..................:matrix.org
(2 rows)

synapse=# select event_id from event_search where vector @@ to_tsquery('МОЖНО & забрать');
              event_id              
------------------------------------
 $1566.................:matrix.org
 $1546..................:matrix.org
(2 rows)

C:

synapse=# select event_id from event_search where vector @@ to_tsquery('можно & забрать');
              event_id
------------------------------------
 $1566.................:matrix.org
 $1546..................:matrix.org
(2 rows)

synapse=# select event_id from event_search where vector @@ to_tsquery('МОЖНО & забрать');
                   event_id
----------------------------------------------
 $1574..............:homeserver.tld
 $1574..............:homeserver.tld
 $1574..............:homeserver.tld
 $1574..............:homeserver.tld
 $1576...............:homeserver.tld
 $1576...............:homeserver.tld
 $1591..............:homeserver.tld
 $Zgt9.......................................
(8 rows)

[MurzNN]

Maybe you must rebuild indexes after changing locale?

[532910]

Sure, I've issued REINDEX database synapse; and restarted psql client

[532910]

Full log:
https://clbin.com/k2J64

[MurzNN]

I have found a PR here https://github.com/matrix-org/synapse/pull/6268/files that forcing lowercase of strings for solving similar problem with case-sensitive search.
So, if we can't use LC_COLLATE and LC_CTYPE other than C, maybe we can add similar behavior, and convert to lowercase all strings before adding to search index, and do same trick with search phrase too?

[grinapo]

Sidenote: as far as I see synapse will reject the workarounds which try to use a non-C db locale, so this will come up as a problem again.

The current state of searching is a hack, it should be possible to use a proper full-text search backend. I'm not sure it would be very hard to develop one independently (using the db and redirecting the api calls), but right now I'm busy with many other things. :-(