Misbehavior on ILIKE with ElasticSearch: '%' at begining required

[JVent_]

Hi, I'm getting a misbehavior with elasticsearch while casting a NXQL query with ILIKE operators. I mapped the fields lowercase_analyzer in the elasticsearch config file. It works perfect with ElasticSearch, but it cast different result depending on user type.

For example, an Administrator execute this NXQL:

SELECT * FROM Document WHERE ecm:mixinType != 'HiddenInNavigation' AND ecm:isProxy = 0 AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND pre:myCustomField ILIKE 'CUSTOM%'

And the query returns all results that starts with CUSTOM in that field, for example CUSTOM00001, CUSTOM0002... etc.

But the same query executed with a normal user returns 0 results. I have to execute the query using the wildcart at the begining

SELECT * FROM Document WHERE ecm:mixinType != 'HiddenInNavigation' AND ecm:isProxy = 0 AND ecm:isCheckedInVersion = 0 AND ecm:currentLifeCycleState != 'deleted' AND pre:myCustomField ILIKE '%CUSTOM%'

It happens with any field I configured and mapping for ilike in elasticsearch.

myCustomField is a String. The fields are mapped like the example in the blog:

"pre:myCustomField": {                                
	  "type": "multi_field",                 
	  "fields" : {                             
	    "pre:myCustomField": {                          
	      "include-in-all": true,              
	      "type": "string"                   
	    },                                       
	    "lowercase": {                         
	      "type": "string",                  
	      "analyzer": "lowercase_analyzer"   
	    }                                        
	  }                                          
	}

[Greg_Drayon]

Does the normal user have enough rights to access to the documents fetched to the administrator?

[JVent_]

Yes, he has, he can see same documents as Administrator, in fact, the documents are available vía browsing. The misbehavior is in the search page.

[ben_]

Hi,

If I understand well the same query (ILIKE 'CUSTOM%') works for an administrator and not for a user that has the right to see the matching documents.

First make sure that the lowercase mapping is up to date by reindexing the repositories from the Admin Center/Elasticsearch/Admin. If it does not fix the problem, you can activate the Elasticsearch trace as described here:

https://doc.nuxeo.com/display/ADMINDOC60/Elasticsearch+Setup#ElasticsearchSetup-ActivateTraces

So we can analyze the Elasticsearch request and response.

Regards

ben

[pibou_Bouvret]

The two queries are different

[JVent_]

[pibou Bouvret](https