Misbehavior on ILIKE with ElasticSearch: '%' at begining required
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"
}
}
}
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