View Issue Details

IDProjectCategoryView StatusLast Update
0012145mantisbtfilterspublic2023-02-14 05:03
Reporterudobes2 Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status confirmedResolutionreopened 
Platformdiv. Intel PlatformsOSWindowsOS VersionXP and 7
Product Version1.2.1 
Summary0012145: Search should do case insensitive lookups
Description

That the search is case sensitiv brings often wrong results and is circuitous.
Maybe in german we realise this more than you, when writing tickets in english.

We would highly appreciate when the case senitivity could be switche off forever.

Greetings
Udo

TagsNo tags attached.
Attached Files
mantis1.jpg (7,808 bytes)   
mantis1.jpg (7,808 bytes)   
mantis2.jpg (8,464 bytes)   
mantis2.jpg (8,464 bytes)   

Relationships

has duplicate 0012372 closeddhx Search should do case insensitive lookups 
related to 0011012 closeddhx manage_user_page.php?prefix=C is case sensitive 
related to 0026133 new Case sensitivity on View Issues searches 

Activities

jreese

jreese

2010-07-08 07:57

reporter   ~0026039

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?

udobes2

udobes2

2010-07-08 09:40

reporter   ~0026040

We use mantis with mysql 5.0.51 database and php 5.2.6.
The search field definitly is case sensitiv.
Udo

Phileas

Phileas

2010-07-12 03:52

reporter   ~0026053

For me it works fine (case insensitiv)

udobes2

udobes2

2010-07-12 10:18

reporter   ~0026055

Last edited: 2010-07-12 10:36

Which version do you use. Is that release public?
Do you use a different infrastructure?

For example I attached two screens of the count of search results.
Searching for 'error' I receive 95 hits.
Searching for 'Error' I receive 38 hits.

My installation is running under:
Mantis 1.2.1 - 182
Apache/2.2.9 (Unix) PHP/5.2.6
MySQL 5.0.51b

Udo

Phileas

Phileas

2010-07-12 10:50

reporter   ~0026056

Last edited: 2010-07-12 11:00

uups ...
i use:
Mantis 1.2.1 - 183 !?
Apache/2.2.11 . PHP/5.2.9 . MySQL (5.0.51a)5.1.33 from XAMPP 1.7.1

any other filter active?
we should further talk in the forum (may be in the german)

jreese

jreese

2010-07-12 11:11

reporter   ~0026057

This is a behavior enforced by MySQL's definition of the LIKE clause. [1] 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.

[1] http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html

udobes2

udobes2

2010-07-12 14:13

reporter   ~0026058

Last edited: 2010-07-12 15:07

Maybe you can add a strtolower() function for the searchstring??
And please tell me the line, that has to be changed.
Udo

cor3huis

cor3huis

2010-07-12 20:13

reporter   ~0026059

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 ;)

udobes2

udobes2

2010-07-13 17:02

reporter   ~0026070

Yes, I agree totaly.
Thought to implement the "... LIKE '%'.strtolower(<serchstring>).'%'; as search clause. No choice for users and always the same search results.
Udo

jreese

jreese

2010-07-15 13:26

reporter   ~0026090

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.

squarebox

squarebox

2010-07-15 20:55

reporter   ~0026093

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.
スケジュール (Full-width - katakana)
スケジュール (Half-width - katakana)
すけじゅーる (Hiragana)

dhx

dhx

2010-09-19 02:33

reporter   ~0026779

http://stackoverflow.com/questions/203399/how-do-you-write-a-case-insensitive-query-for-both-mysql-and-postgres

squarebox

squarebox

2010-09-20 21:30

reporter   ~0026820

Last edited: 2010-09-20 21:42

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

"
Collations for Unicode multi-byte character sets

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. "

gsalin

gsalin

2018-08-13 10:39

reporter   ~0060417

Hi,
we have an instance on postgresql and it seems the search is still case sensitive....quite annoying
I modified the sql_like function in ./core/classes/DbQuery.class.php, replacing the line
$t_operator = 'LIKE';
with
$t_operator = 'ILIKE';

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

2018-08-13_163815.png (18,223 bytes)   
2018-08-13_163815.png (18,223 bytes)   
minowing

minowing

2019-04-01 13:30

reporter   ~0061807

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
public function sql_like( $p_alias, $p_pattern, $p_escape = null, $p_force_ci = false )
to
public function sql_like( $p_alias, $p_pattern, $p_escape = null, $p_force_ci = true )

cproensa

cproensa

2019-04-01 16:28

developer   ~0061808

The case insensitive like operator is not fully implemented in DbQuery class, and that's why it's not used explicitly from the code.
This is a tricky topic, has performance related issues, and adding to that, needs portability between databases.

If you have a proposal for a reasonable approach please contribute to that!

minowing

minowing

2019-04-02 02:35

reporter   ~0061809

Hi cproensa,
I'm not php developer but I can describe how we solved this issue in our system (Java+PostgreSQL) (it is has no relevant impact on queries (we use it on tables with 500k+ records so I guess for mantis dbs it can be sufficient).
definitions of problems:

  1. issue : case sensitivity search
  2. issue ( probably only for non english languages ) : removing accents in search .

Solution:
Postgresql has extension unaccent with function unaccent(varchar) which we use for this issue.

generating criterium for Like operator is done :
"unaccent(lower(" + KEY+ ")) LIKE unaccent(lower("+SEARCH_VALUE+"))"
so in sql_like function in DBQuery.class.php the code should be similiar:
...
case DB_TYPE_PGSQL:
$t_expr = 'unaccent(lower(' . $t_expr . '))';
$t_operator = 'LIKE';
$t_pattern = 'unaccent(lower(' . $t_pattern . '))';
break;
....

Note 1:
The SEARCH_VALUE has to be passed to sql as parameteter not as String concatation. (?sql-injection issue?)

Note 2:
We afraid about impact on performance also , so we have stored list of fields where this logic can be used or can't be used (code,description, note and similliar fields are in can be group) . But in current versions of postgresql all is running very fast so group 'can do' is growing....

dregad

dregad

2019-04-02 03:38

developer   ~0061810

@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.