View Issue Details

IDProjectCategoryView StatusLast Update
0012145mantisbtfilterspublic2018-08-13 10:39
Reporterudobes2Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status confirmedResolutionreopened 
Platformdiv. Intel PlatformsOSWindowsOS VersionXP and 7
Product Version1.2.1 
Target VersionFixed in Version 
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.

Relationships

has duplicate 0012372 closeddhx Search should do case insensitive lookups 
related to 0011012 closeddhx manage_user_page.php?prefix=C is case sensitive 

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

View 4 revisions

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

udobes2

udobes2

2010-07-12 10:29

reporter  

mantis1.jpg (7,808 bytes)
mantis1.jpg (7,808 bytes)
udobes2

udobes2

2010-07-12 10:30

reporter  

mantis2.jpg (8,464 bytes)
mantis2.jpg (8,464 bytes)
Phileas

Phileas

2010-07-12 10:50

reporter   ~0026056

Last edited: 2010-07-12 11:00

View 2 revisions

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

View 2 revisions

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

View 2 revisions

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)

Issue History

Date Modified Username Field Change
2010-07-08 06:05 udobes2 New Issue
2010-07-08 07:57 jreese Note Added: 0026039
2010-07-08 07:57 jreese Assigned To => jreese
2010-07-08 07:57 jreese Status new => feedback
2010-07-08 09:40 udobes2 Note Added: 0026040
2010-07-08 09:40 udobes2 Status feedback => assigned
2010-07-12 03:52 Phileas Note Added: 0026053
2010-07-12 10:18 udobes2 Note Added: 0026055
2010-07-12 10:26 udobes2 Note Edited: 0026055 View Revisions
2010-07-12 10:29 udobes2 Note Edited: 0026055 View Revisions
2010-07-12 10:29 udobes2 File Added: mantis1.jpg
2010-07-12 10:30 udobes2 File Added: mantis2.jpg
2010-07-12 10:36 udobes2 Note Edited: 0026055 View Revisions
2010-07-12 10:50 Phileas Note Added: 0026056
2010-07-12 11:00 Phileas Note Edited: 0026056 View Revisions
2010-07-12 11:11 jreese Note Added: 0026057
2010-07-12 11:11 jreese Status assigned => resolved
2010-07-12 11:11 jreese Resolution open => not fixable
2010-07-12 14:13 udobes2 Note Added: 0026058
2010-07-12 15:07 udobes2 Note Edited: 0026058 View Revisions
2010-07-12 20:13 cor3huis Note Added: 0026059
2010-07-13 17:02 udobes2 Note Added: 0026070
2010-07-15 13:26 jreese Assigned To jreese => siebrand
2010-07-15 13:26 jreese Note Added: 0026090
2010-07-15 13:26 jreese Status resolved => feedback
2010-07-15 13:26 jreese Resolution not fixable => reopened
2010-07-15 20:55 squarebox Note Added: 0026093
2010-09-19 02:30 dhx Relationship added has duplicate 0012372
2010-09-19 02:32 dhx Summary search should be case insensitiv => Search should do case insensitive lookups
2010-09-19 02:33 dhx Note Added: 0026779
2010-09-19 02:38 dhx Assigned To siebrand =>
2010-09-19 02:38 dhx Status feedback => confirmed
2010-09-19 02:38 dhx Target Version => 1.2.4
2010-09-19 02:38 dhx Relationship added related to 0011012
2010-09-20 21:30 squarebox Note Added: 0026820
2010-09-20 21:42 squarebox Note Edited: 0026820 View Revisions
2010-12-14 21:05 jreese Target Version 1.2.4 => 1.2.5
2011-04-05 12:25 jreese Target Version 1.2.5 => 1.2.6
2011-07-26 09:53 jreese Target Version 1.2.6 => 1.2.7
2011-08-22 10:49 jreese Target Version 1.2.7 => 1.2.8
2011-09-06 10:33 jreese Target Version 1.2.8 => 1.2.9
2012-03-04 09:23 atrol Target Version 1.2.9 => 1.2.10
2012-04-02 02:33 atrol Target Version 1.2.10 => 1.2.11
2012-06-06 23:54 jreese Target Version 1.2.11 => 1.2.12
2012-11-10 19:04 dregad Target Version 1.2.12 => 1.2.13
2013-01-22 09:48 dregad Target Version 1.2.13 => 1.2.14
2013-01-29 09:28 dregad Target Version 1.2.14 => 1.2.15
2013-04-12 09:57 dregad Target Version 1.2.15 => 1.2.16
2014-01-23 17:54 atrol Target Version 1.2.16 =>
2018-08-13 10:39 gsalin File Added: 2018-08-13_163815.png
2018-08-13 10:39 gsalin Note Added: 0060417