View Issue Details
|div. Intel Platforms
|XP and 7
|0012145: Search should do case insensitive lookups
That the search is case sensitiv brings often wrong results and is circuitous.
We would highly appreciate when the case senitivity could be switche off forever.
|No tags attached.
The current filters should already be generating case insensitive LIKE clauses; it specifically generates an ILIKE clause on PostgreSQL to maintain that. What database are you using?
We use mantis with mysql 5.0.51 database and php 5.2.6.
For me it works fine (case insensitiv)
Which version do you use. Is that release public?
For example I attached two screens of the count of search results.
My installation is running under:
any other filter active?
This is a behavior enforced by MySQL's definition of the LIKE clause.  When a search is performed with all lower-case, the LIKE clause is case-insensitive. If you add capitalized letters to the search term, the LIKE clause becomes case-sensitive. There is no way for MantisBT to get around this limitation. If you want to perform a case-insensitive search, use all lower-case.
Maybe you can add a strtolower() function for the searchstring??
A very welcomed improvement, even if only by a config parameter of a certain search result behavior.
We cannot expect to tell end users searching that "MySQL's definition of the LIKE clause" makes them not find what they are looking for.
Even more also e.g in issue # inputbox, there is no integer change the behaviour to the search behaviour. I even thin we should avoid multiple search boxes altogeter, but that is for another time another discussion ;)
Yes, I agree totaly.
This issue I see with this approach is concerning the way lower-casing strings would affect multibyte unicode languages, where the lowercase version of the string may not necessarily properly correspond to the encoding that the database is using, having the result of incorrect search results.
Siebrand, can I pass this on to you for review, since you better understand the ramifications of this change? If you don't think it will affect anything, then I would be in agreement of the change, but I don't want to break anything unintentionally.
I know this is possible in MS-SQL but is it possible use to collations in the sql query to make the search case-insensitive, although this functionality is broken when doing %xxx% matches. Just mentioning this as something that may be an option in MySql.
Probably better in another ticket, but since Siebrand would be looking at this i wanted to add that case-sensitivity isn't the only problem. In Japanese, accents, width and kana-insensitivity "seems" to be the standard for searching in japanese (but don't quote me on this).
the following words are the same word to show width and kana insensitivity.
Using mysql's lower() function on japanese doesn't have any effect on the query.
again speaking from a MS-SQL background, i'd highly recommend not using Like as a means to get around case-sensitivities in queries for performance reasons.
the better way to fix this would be to change the collation used on the DB or the table to support case-insensitive accent-insensitive queries. This would also get around the need to change existing queries to use Like statements.
it seems that MySQL in general doesn't have separate collations for accents, but since other DBs it should be investigated on a DB basis which collation to use. MS-SQL for example is Japanese_90_CI_AI.
Note that using Like with a ci-ai collation with wildcards in the prefix of a string is currently broken in SQL2005, 2008 and 2010 and it is not clear whether MS will fix it. There seems to be to an underlying problem with the way they have implemented their like functionality.
Following is a quote from http://dev.mysql.com/doc/refman/5.0/en/charset-collation-implementations.html
Some of these collations are based on the Unicode Collation Algorithm (UCA), others are not.
Non-UCA collations have a one-to-one mapping from character code to weight. In MySQL, such collations are case insensitive and accent insensitive. utf8_general_ci is an example: 'a', 'A', 'À', and 'á' each have different character codes but all have a weight of 0x0041 and compare as equal. "
and it is fine.
There is a sql_ilike function that should enforce the case insensitivity, but it isn't used anywhere in the source code....
Are you working on this?
my config is enclosed
thank you for your help
I have to change default for p_fores_ci because using this function in BugFilterQuery.class.php ( there are always false)
change in /core/classes/DbQuery.class.php
The case insensitive
If you have a proposal for a reasonable approach please contribute to that!
generating criterium for Like operator is done :
@minowing Thanks for your suggestion. The problem with this approach is that it's very DB-specific. MantisBT needs to work equally with MySQL, PostgreSQL, SQL Server and Oracle, so we'd need a portable solution or it's not possible then at least similar solutions for other RDBMS.