cmis query does not return correct results

[Nicodeme]

I hwhen i execute this cmis query to alfresco acs7.2.1 

SELECT *

FROM fds:document AS D

JOIN cmis:folder AS O ON D.cmisbjectId = O.cmisbjectId

WHERE 

NOT (CONTAINS(D, 'PATH:\"/app:company_home/cm:Failure//*\"') OR CONTAINS(D, 'PATH:\"/app:company_home/cmropZone//*\"'))

and  D.fds:docType='F1 CONTR' and O.cmis:name like '2021%' i get 100 results (as it is limited bij cmis workbench to 100)

this is the log :

2022-10-13 15:21:52,717 DEBUG [org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage] [http-nio-8080-exec-2] Using SOLR query: db-cmis for SearchParameters [language=cmis-alfresco, query=SELECT * FROM fds:document AS D JOIN cmis:folder AS O ON D.cmisbjectId = O.cmisbjectId WHERE NOT (CONTAINS(D, 'PATH:\"/app:company_home/cm:Failure//*\"') OR CONTAINS(D, 'PATH:\"/app:company_home/cmropZone//*\"')) and D.fds:docType='F1 CONTR' and O.cmis:name like '2021%' , stores=[workspace://SpacesStore], queryParameterDefinitions=[], excludeDataInTheCurrentTransaction=false, sortDefinitions=[], locales=[en], mlAnalaysisMode=EXACT_LANGUAGE_AND_ALL, limitBy=FINAL_SIZE, permissionEvaluation=EAGER, limit=100, allAttributes=[], textAttributes=[], maxItems=100, skipCount=0, defaultFTSOperator=AND, defaultFTSFieldOperator=AND, queryTemplates={}, namespace=http://www.alfresco.org/model/content/1.0, maxPermissionChecks=-1, maxPermissionCheckTimeMillis=-1, defaultFieldName=TEXT, fieldFacets=[], facetQueries=[], filterQueries=[], pivots=[], stats=null, useInMemorySort=null, maxRawResultSetSizeForInMemorySort=null, extraParameters={cmisVersion=CMIS_1_1}, excludeTenantFilter=false, isBulkFetchEnabled=true, queryConsistency=DEFAULT, sinceTxId=null, searchTerm=null, highlight=null, interval=null, range=null, timezone=null, spellCheck=false] 2022-10-13 15:21:52,743 DEBUG [org.alfresco.repo.search.impl.solr.SolrQueryHTTPClient] [http-nio-8080-exec-2] Sent :/solr/alfresco/cmis?wt=json&fl=DBID%2Cscore&rows=100&df=TEXT&start=0&locale=en&alternativeDic=DEFAULT_DICTIONARY&cmisVersion=CMIS_1_1&fq=%7B%21afts%7DAUTHORITY_FILTER_FROM_JSON&fq=%7B%21afts%7DTENANT_FILTER_FROM_JSON 2022-10-13 15:21:52,743 DEBUG [org.alfresco.repo.search.impl.solr.SolrQueryHTTPClient] [http-nio-8080-exec-2] with: {"tenants":[""],"defaultNamespace":"http://www.alfresco.org/model/content/1.0","defaultFTSFieldOperator":"AND","query":"SELECT * FROM fds:document AS D JOIN cmis:folder AS O ON D.cmisbjectId = O.cmisbjectId WHERE NOT (CONTAINS(D, 'PATH:\\\"/app:company_home/cm:Failure//*\\\"') OR CONTAINS(D, 'PATH:\\\"/app:company_home/cmropZone//*\\\"')) and D.fds:docType='F1 CONTR' and O.cmis:name like '2021%' ","templates":[],"allAttributes":[],"queryConsistency":"DEFAULT","authorities":["GROUP_EVERYONE","ROLE_ADMINISTRATOR","ROLE_AUTHENTICATED","ddebaerd"],"locales":["en_"],"textAttributes":[],"defaultFTSOperator":"AND","anyDenyDenies":true,"mlAnalaysisMode":"EXACT_LANGUAGE_AND_ALL"} 2022-10-13 15:21:52,743 DEBUG [org.alfresco.repo.search.impl.solr.SolrQueryHTTPClient] [http-nio-8080-exec-2] Got: 110 in 8 ms 2022-10-13 15:21:52,743 DEBUG [org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage] [http-nio-8080-exec-2] SOLR returned 100 results in 26ms

when i execute the same query with a slibht difference :

SELECT *

FROM fds:document AS D

JOIN cmis:folder AS O ON D.cmisbjectId = O.cmisbjectId

WHERE 

NOT (CONTAINS(D, 'PATH:\"/app:company_home/cm:Failure//*\"') OR CONTAINS(D, 'PATH:\"/app:company_home/cmropZone//*\"'))

and  D.fds:docType='F1 CONTR' and O.cmis:name like '20%'

i do not get any results, hereby the log

2022-10-13 15:19:04,185 DEBUG [org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage] [http-nio-8080-exec-9] Using SOLR query: db-cmis for SearchParameters [language=cmis-alfresco, query=SELECT * FROM fds:document AS D JOIN cmis:folder AS O ON D.cmisbjectId = O.cmisbjectId WHERE NOT (CONTAINS(D, 'PATH:\"/app:company_home/cm:Failure//*\"') OR CONTAINS(D, 'PATH:\"/app:company_home/cmropZone//*\"')) and D.fds:docType='F1 CONTR' and O.cmis:name like '20%' , stores=[workspace://SpacesStore], queryParameterDefinitions=[], excludeDataInTheCurrentTransaction=false, sortDefinitions=[], locales=[en], mlAnalaysisMode=EXACT_LANGUAGE_AND_ALL, limitBy=FINAL_SIZE, permissionEvaluation=EAGER, limit=100, allAttributes=[], textAttributes=[], maxItems=100, skipCount=0, defaultFTSOperator=AND, defaultFTSFieldOperator=AND, queryTemplates={}, namespace=http://www.alfresco.org/model/content/1.0, maxPermissionChecks=-1, maxPermissionCheckTimeMillis=-1, defaultFieldName=TEXT, fieldFacets=[], facetQueries=[], filterQueries=[], pivots=[], stats=null, useInMemorySort=null, maxRawResultSetSizeForInMemorySort=null, extraParameters={cmisVersion=CMIS_1_1}, excludeTenantFilter=false, isBulkFetchEnabled=true, queryConsistency=DEFAULT, sinceTxId=null, searchTerm=null, highlight=null, interval=null, range=null, timezone=null, spellCheck=false] 2022-10-13 15:19:04,196 DEBUG [org.alfresco.repo.search.impl.solr.SolrQueryHTTPClient] [http-nio-8080-exec-9] Sent :/solr/alfresco/cmis?wt=json&fl=DBID%2Cscore&rows=100&df=TEXT&start=0&locale=en&alternativeDic=DEFAULT_DICTIONARY&cmisVersion=CMIS_1_1&fq=%7B%21afts%7DAUTHORITY_FILTER_FROM_JSON&fq=%7B%21afts%7DTENANT_FILTER_FROM_JSON 2022-10-13 15:19:04,196 DEBUG [org.alfresco.repo.search.impl.solr.SolrQueryHTTPClient] [http-nio-8080-exec-9] with: {"tenants":[""],"defaultNamespace":"http://www.alfresco.org/model/content/1.0","defaultFTSFieldOperator":"AND","query":"SELECT * FROM fds:document AS D JOIN cmis:folder AS O ON D.cmisbjectId = O.cmisbjectId WHERE NOT (CONTAINS(D, 'PATH:\\\"/app:company_home/cm:Failure//*\\\"') OR CONTAINS(D, 'PATH:\\\"/app:company_home/cmropZone//*\\\"')) and D.fds:docType='F1 CONTR' and O.cmis:name like '20%' ","templates":[],"allAttributes":[],"queryConsistency":"DEFAULT","authorities":["GROUP_EVERYONE","ROLE_ADMINISTRATOR","ROLE_AUTHENTICATED","ddebaerd"],"locales":["en_"],"textAttributes":[],"defaultFTSOperator":"AND","anyDenyDenies":true,"mlAnalaysisMode":"EXACT_LANGUAGE_AND_ALL"} 2022-10-13 15:19:04,196 DEBUG [org.alfresco.repo.search.impl.solr.SolrQueryHTTPClient] [http-nio-8080-exec-9] Got: 0 in 3 ms 2022-10-13 15:19:04,196 DEBUG [org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage] [http-nio-8080-exec-9] SOLR returned 0 results in 10ms

 i also get the anoying stack trace everytime i execute that kind of query

2022-10-13 15:21:52,709 DEBUG [org.alfresco.web.app.servlet.KerberosAuthenticationFilter] [http-nio-8080-exec-2] Fallback authentication succeeded. 2022-10-13 15:21:52,713 DEBUG [org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage] [http-nio-8080-exec-2] Trying db query for db-cmis for SearchParameters [language=cmis-alfresco, query=SELECT * FROM fds:document AS D JOIN cmis:folder AS O ON D.cmisbjectId = O.cmisbjectId WHERE NOT (CONTAINS(D, 'PATH:\"/app:company_home/cm:Failure//*\"') OR CONTAINS(D, 'PATH:\"/app:company_home/cmropZone//*\"')) and D.fds:docType='F1 CONTR' and O.cmis:name like '2021%' , stores=[workspace://SpacesStore], queryParameterDefinitions=[], excludeDataInTheCurrentTransaction=false, sortDefinitions=[], locales=[en], mlAnalaysisMode=EXACT_LANGUAGE_AND_ALL, limitBy=FINAL_SIZE, permissionEvaluation=EAGER, limit=100, allAttributes=[], textAttributes=[], maxItems=100, skipCount=0, defaultFTSOperator=AND, defaultFTSFieldOperator=AND, queryTemplates={}, namespace=http://www.alfresco.org/model/content/1.0, maxPermissionChecks=-1, maxPermissionCheckTimeMillis=-1, defaultFieldName=TEXT, fieldFacets=[], facetQueries=[], filterQueries=[], pivots=[], stats=null, useInMemorySort=null, maxRawResultSetSizeForInMemorySort=null, extraParameters={cmisVersion=CMIS_1_1}, excludeTenantFilter=false, isBulkFetchEnabled=true, queryConsistency=DEFAULT, sinceTxId=null, searchTerm=null, highlight=null, interval=null, range=null, timezone=null, spellCheck=false] 2022-10-13 15:21:52,715 DEBUG [org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage] [http-nio-8080-exec-2] DB query failed for db-cmis for SearchParameters [language=cmis-alfresco, query=SELECT * FROM fds:document AS D JOIN cmis:folder AS O ON D.cmisbjectId = O.cmisbjectId WHERE NOT (CONTAINS(D, 'PATH:\"/app:company_home/cm:Failure//*\"') OR CONTAINS(D, 'PATH:\"/app:company_home/cmropZone//*\"')) and D.fds:docType='F1 CONTR' and O.cmis:name like '2021%' , stores=[workspace://SpacesStore], queryParameterDefinitions=[], excludeDataInTheCurrentTransaction=false, sortDefinitions=[], locales=[en], mlAnalaysisMode=EXACT_LANGUAGE_AND_ALL, limitBy=FINAL_SIZE, permissionEvaluation=EAGER, limit=100, allAttributes=[], textAttributes=[], maxItems=100, skipCount=0, defaultFTSOperator=AND, defaultFTSFieldOperator=AND, queryTemplates={}, namespace=http://www.alfresco.org/model/content/1.0, maxPermissionChecks=-1, maxPermissionCheckTimeMillis=-1, defaultFieldName=TEXT, fieldFacets=[], facetQueries=[], filterQueries=[], pivots=[], stats=null, useInMemorySort=null, maxRawResultSetSizeForInMemorySort=null, extraParameters={cmisVersion=CMIS_1_1}, excludeTenantFilter=false, isBulkFetchEnabled=true, queryConsistency=DEFAULT, sinceTxId=null, searchTerm=null, highlight=null, interval=null, range=null, timezone=null, spellCheck=false] org.alfresco.repo.search.impl.querymodel.QueryModelException: 09130186 Analysis mode not supported for DB DEFAULT at org.alfresco.repo.search.impl.querymodel.impl.db.functions.DBFTSPhrase.prepare(DBFTSPhrase.java:134) at org.alfresco.repo.search.impl.querymodel.impl.db.DBFunctionalConstraint.prepare(DBFunctionalConstraint.java:80) at org.alfresco.repo.search.impl.querymodel.impl.db.DBDisjunction.prepare(DBDisjunction.java:81) at org.alfresco.repo.search.impl.querymodel.impl.db.DBConjunction.prepare(DBConjunction.java:83) at org.alfresco.repo.search.impl.querymodel.impl.db.DBQuery.prepare(DBQuery.java:245) at org.alfresco.repo.search.impl.querymodel.impl.db.DBQueryEngine.executeQuery(DBQueryEngine.java:318) at 

Is there any reason for it ?

[angelborroy]

Only 1 "CONTAINS" function is allowed.

http://docs.oasis-open.org/cmis/CMIS/v1.1/CMIS-v1.1.html