Mattermost-server: Search terms with underscore using PostgreSQL engine should work

[mattermod]

Right we’re using to_tsquery in the Postgresql search but when the word has underscore is returning more results than it should.

The problem is that to_tsquery for a word with an underscore is splitting that word into multiple parts:

mattermost_test=# select to_tsquery('english', '("test_with_underscore")');
      to_tsquery      
----------------------
 'test' & 'underscor'
(1 row)

Using for example something like:

mattermost*test=# select $$test_with*underscore$$::tsquery;
        tsquery         
~~----------------------~~
 'test_with_underscore'
(1 row)

could work but we are losing the dictionary option (the ‘English’ part in to_tsquery)

---

If you're interested please comment here and come join our "Contributors" community channel on our daily build server, where you can discuss questions with community members and the Mattermost core team. For technical advice or questions, please join our "Developers" community channel.

New contributors, please see our Developer's Guide.

JIRA: https://mattermost.atlassian.net/browse/MM-24530

[fedealconada]

I can take this one!

[fedealconada]

Would you please elaborate on "when the word has underscore is returning more results than it should" @amyblais ?

[amyblais]

@ethervoid ^

[fedealconada]

@ethervoid what do you think about replacing this line with:
searchClause := fmt.Sprintf("AND to_tsvector('english', replace(%s, '_', '/')) @@ to_tsquery('english', replace(:Terms, '_', '/'))", searchType)

I've tested it out and it works, but not sure if I'm missing something...

[ethervoid]

@fedealconada Could you please share here what kind of tests are you doing? I'm asking for this because what we want to achieve is to search for terms with underscores but the way we're creating the index is storing those terms splitted

[ethervoid]

@fedealconada Are you still interested in this issue? I not we can let another user work on it ;)

[jfrerich]

This issue appears stale and I’m adding the Up For Grabs label. If work is ongoing, please remove the label. If there is an existing PR, please also link it to the issue. Thanks!

[antwigambrah]

@amyblais I would love to give it a shot.