Query on cmis:name does not return all results

[Fabermundi2]

Hello,

I have a problem when doing a query regarding the document name.
When I do the following query to show all documents begining with "C11-4":

select * from cmis:document WHERE cmis:name like 'C11-4%' order by cmis:name desc

I get as first result the document with the name "C11-41000.pdf".
When changing the to show all files beginning with "C11-41"

select * from cmis:document WHERE cmis:name like 'C11-41%' order by cmis:name desc

I get the document "C11-41926" that was not shown with the first query.

Has anyone an idea with the first query does not return all results?

Thank you in advance for your answers.

[afaust]

What is your query consistency mode (set in the request or in your search subsystem)? What type of search system are you using? What version of ACS / ASS (Search Services) are you using?

My first thought: This kind of difference in behaviour points to a SOLR / Search Services based query execution and tokenisation of the query term / name value not matching up. I have seen some similar cases where a number-based name fragment was not matched with a wildcard after the first digit following a token boundary (whitespace, separator, alpha-numeric change). Only with two or more digits before the wildcard would a match work. Ideally, this type of query should be executed against the database where tokenisation would not play a role, and the LIKE operation work exactly like a DB LIKE.

[schulto]

Hello and thank you for your response.

I'm using Alfresco 7.1 with Solr 6 with Search Services 2.0.2.

To have a better use case I've created a folder with only the files

C11-40999.pdf
C11-41000.pdf
C11-41926.pdf

Then I run the query

select * from cmis:document WHERE IN_TREE('workspace://SpacesStore/c22b3fab-ef46-4cc5-8184-47e9e2fd8913') and cmis:name like 'C11-4%'

To get only the two files C11-40999.pdf and C11-41000.pdf as a result.

If I understand this right you think that the search engine groups the files and this could be an explanation for this unwanted result.

Could you explain me, how I can run the query against the database? I used the Node browser at the webinterface to run the query as "cmis-alfresco".

[afaust]

As long as you need to use IN_TREE, the query will not be able to be rewritten against the database. Only IN_FOLDER (direct parent condition) will work with a database query. For testing purposes, you could remove IN_TREE to switch to a database query as well.

[Fabermundi2]

Hello and sorry for the late reply.
I've changed the query to use "IN_FOLDER" like this:

select * from cmis:document WHERE IN_FOLDER('workspace://SpacesStore/c22b3fab-ef46-4cc5-8184-47e9e2fd8913') and cmis:name like 'C11-4%'

Unfortunately the result was no change. It only returns me two of the 3 documents.

[Melody242]

---

@Fabermundi2 gm-socrateswrote:

Hello,

I have a problem when doing a query regarding the document name.
When I do the following query to show all documents begining with "C11-4":

select * from cmis:document WHERE cmis:name like 'C11-4%' order by cmis:name desc

I get as first result the document with the name "C11-41000.pdf".
When changing the to show all files beginning with "C11-41"

select * from cmis:document WHERE cmis:name like 'C11-41%' order by cmis:name desc

I get the document "C11-41926" that was not shown with the first query.

Has anyone an idea with the first query does not return all results?

Thank you in advance for your answers.

---

To retrieve all documents beginning with "C11-4" in a CMIS query, check if the cmis:name property is case-sensitive and modify the query accordingly. Use wildcard characters for flexibility. Ensure proper indexing for efficient querying.

[Fabermundi2]

Hello and sorry for the late reply.

- Case sensitivity should be irrelevant because all documents are in the same form and begin with C11-.

- I use a wildcard as my query contains "like 'C11-4%'

- The indexes seems to be good as for example exact searches work. In addition the cm:name is marked as tokenised "both".