Bug - Fulltext search incorrect in postgresql due to case sensitivity

[GWarsow]

Environment

• Server OS: Linux
• Browser: any
• Znuny version: LTS 6.5.11
• DB backend: PostgreSQL

Expected behavior

When running a fulltext search for INCOMPLETE I want to find tickets which have INCOMPLETE in their title.

Actual behavior

When running a fulltext search for INCOMPLETE tickets which have INCOMPLETE in their title are not reported.

How to reproduce

Steps to reproduce the behavior:

1. Create a new ticket manually with INCOMPLETE as part of the ticket title. Make sure not to have INCOMPLETE as a word in an article in this ticket. We only want to work with the ticket title here and not with the article_search_index.
2. Do a full text search via the textfield on the dashboard for INCOMPLETE
3. confirm that the ticket you created is not found

Additional information

In this line

Znuny/Kernel/System/Ticket/ArticleSearchIndex/DB.pm

Line 316 in 7f7219e

Value => lc $Param{SearchParams}->{$Field},

lc is applied and as a result, the sql query will search for ArticleFulltext.article_value LIKE '%incomplete%' OR st.title LIKE '%incomplete%'.

The issue is that PostgreSQL behaves differently in terms of case sensitivity than mysql. By default, LIKE in PostgreSQL is case-sensitive. So LIKE '%incomplete%' will only match exactly incomplete not INCOMPLETE or Incomplete.
The describes scenario works well in mysql but not in PostgreSQL.

If lc is applied to the value provided for fulltextsearch (INCOMPLETE in our case), this has to be reflected in the sql query as well. Since the query uses st.title LIKE '%incomplete%' in the WHERE clause, but st.title contains ... INCOMPLETE ... in uppercase letters, no match will be reported for this ticket by PostgreSQL.

[GWarsow]

When you search explicitly for Title via the extended search form, this issues does not occur because there lower is applied to both sides of the LIKE:

LOWER(st.title) LIKE LOWER('%INCOMPLETE%')

[GWarsow]

Would it be sufficient to set CaseSensitive => 0 in line 320 or would this cause unwanted side-effects?

Znuny/Kernel/System/Ticket/ArticleSearchIndex/DB.pm

Lines 314 to 321 in 7f7219e

	$SQLQuery .= $DBObject->QueryCondition(
	Key => $Field eq 'Fulltext' ? [ 'ArticleFulltext.article_value', 'st.title' ] : "$Field.article_value",
	Value => lc $Param{SearchParams}->{$Field},
	SearchPrefix => $Param{SearchParams}->{ContentSearchPrefix},
	SearchSuffix => $Param{SearchParams}->{ContentSearchSuffix},
	Extended => 1,
	CaseSensitive => 1,
	);

At least, this results in

LOWER(ArticleFulltext.article_value) LIKE LOWER('%incomplete%')  OR LOWER(st.title) LIKE LOWER('%incomplete%')

This causes additional load for ArticleFulltext.article_value which is not needed (because the index already stores lower case only), but we get the tickets with the requested ticket title using this approach.