How to Query case insensitive in Alfresco Solr search ?

[devkinandanchau]

Using Alfresco Community - 5.0.0 version.

Below query using Solr returned result :

1st Query : select cmisbjectId from TEST:doc where TEST:docType = 'Type1' 

But we want the case-insensitive search if there are docType value 'type1' || 'TYPE1' || 'tYpe1' || 'Type1' then it should give same number of records.

I've tried below query which works fine while "Trying DB query DbOrIndexSwitchingQueryLanguage" fetch,

but "Using SOLR query DbOrIndexSwitchingQueryLanguage" not returning any result for the same query. 

2nd Query: select cmisbjectId from TEST:doc where LOWER(TEST:docType) = 'type1'

But while using LOWER it's execution time is much higher. For example, if the 1st query takes 10ms then 2nd takes around 3 seconds. 

What is the syntax to search in Solr case insensitive or is there another way to resolve this performance issue?

[angelborroy]

Have you tried "CONTAINS" clause instead of "LOWER"?

[devkinandanchau]

Wanna do exact string search but case-insensitive, contains will lead to wrong results. 

[angelborroy]

IMO first query is giving you all required results and it's case-insensitive, but probably I'm wrong.

[afaust]

1. You cannot have case-insensitive exact matches - either you have an exact match, or a match ignoring case - there can by definition not be any overlap
2. AFAIK SOLR query does not support applying value transformation functions to field values - only to input values. SOLR does not store the original values and can only query a pre-built index, so there would be literally nothing to transform.
3. Of course executing with LOWER() against the database will be slower as the DB will have to apply that function to any value going through the filter condition. Depending on the query plan chosen by the DB, this can be many more rows than actually end up in the result.

If you want to query for case insensitive matches, your best bet is probably using FTS query language instead of CMIS. Though bear in mind that for FTS as well can by definition not achieve both at the same time (exact + insensitive match), but for different reasons. If you don't specify exact match per query modifier, you will get case insensitive matches, but due to tokenisation and stemming you may also get matches for slightly different values (i.e. "motor" vs. "motors").

Effectively, unless you start writing custom, special purpose SQL queries without using the standardised query languages, you will likely not be able to achieve 100% of what you want.

[jpotts]

Cross-posted here: How to Query case insensitive in Alfresco Solr search? - Stack Overflow.

Old thread with basically the same question here: