topic Re: Misbehavior on ILIKE with ElasticSearch: '%' at begining required in Nuxeo Forum

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

JVent_ — Thu, 23 Jul 2015 15:41:37 GMT

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

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

Greg_Drayon — Thu, 12 Nov 2015 11:20:15 GMT

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

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

JVent_ — Mon, 16 Nov 2015 17:50:21 GMT

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.

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

ben_ — Tue, 17 Nov 2015 14:08:20 GMT

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

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

pibou_Bouvret — Mon, 22 Oct 2018 07:49:08 GMT

The two queries are different

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

JVent_ — Mon, 22 Oct 2018 07:59:02 GMT

[pibou Bouvret](https