Case insensitivity

General discussion of Mantis.

Moderators: Developer, Contributor

Post Reply
ShadowKatmandu
Posts: 16
Joined: May 19, 2014 2:46 pm

Case insensitivity

Post by ShadowKatmandu » Sep 04, 2019 10:41 am

We are using PG SQL for our backend, which is case-sensitive on queries. There is logic built into Mantis to do case insensitive queries for this; however, it requires a parameter be passed to the sql_like function to force it. There's a separate function, sql_ilike, to do this, but there is no circumstance where this is called. Is there a way to change this behavior?

TerranceM
Posts: 2
Joined: Aug 27, 2019 8:55 am
Location: USA
Contact:

Re: Case insensitivity

Post by TerranceM » Sep 05, 2019 1:23 pm

There are a few ways to compare strings.

complete strings

partial strings/from the beginning

partial strings/somewhere


for each of these 3 types it can be exact match or case insensitive

so 6 different methods:

1) complete string/exact

select * from table where "column" = 'value'

2) complete string/case insentitive

select * from table where "column" = 'value' case insensitive

3) partial string from the beginning / exact match

select * from table where "column" like 'value%' ;
select * from table where position( 'value' in "column" ) = 1;
select * from table where substring( "column" from 1 for 5 ) = 'value';


4) partial string from the beginning / case insentitive

select * from table where "column" like 'value%' ignore case ;
select * from table where position( lower('value') in lower("column") ) = 1;
select * from table where substring( lower("column") from 1 for 5 ) =
lower('value');


5) partial string somewhere / exact match

select * from table where "column" like '%value%' ;
select * from table where position( 'value' in "column" ) > 0;

6) partial string somewhere / case insentitive

select * from table where "column" like '%value%' ignore case ;
select * from table where position( lower('value') in lower("column") ) > 0;


my experience is that functions (lower, upper etc) have a serious
overhead and cause serious performance degrade.

ShadowKatmandu
Posts: 16
Joined: May 19, 2014 2:46 pm

Re: Case insensitivity

Post by ShadowKatmandu » Sep 05, 2019 2:25 pm

I understand the many ways to do this in a SQL query. That was not my question at all.

To be specific:

On the view issues page, there is a search box with an Apply Filter button next to it. This is a CASE SENSITIVE search. I gave technical details as to why this is so. There is functionality in Mantis to deal with it, but it doesn't seem to be used.

How do I get Mantis to do CASE INSENSITIVE searches with PG SQL without changing core code?

Post Reply