We have an installation of Alfresco Community 7.2 and Solr 6 in which we have created our own models with specific types for the documents that are uploaded.
One of these types contains a field with the invoice number, of type text, which when used in CMIS searches does not return the correct result.
Specifically, filtering by field values that begin with '9562%' only returns documents with values '9562300xxx', but there are documents with values '9562301xxx' and '9562302xxx' that do not appear in the response. The query always contains an IN_TREE in the filter, so it is always run against SOLR, never against DB.
This is the CMIS query we are executing:
SELECT * FROM ham:factura_cliente_ham WHERE IN_TREE('70b8de21-d8a3-48d9-af58-d4247d1c732c') AND (cmis:creationDate >= '2023-06-01T00:00:00.000Z' AND cmis:creationDate <= '2023-06-30T23:59:59.000Z') AND cxc:cxcNumFactura LIKE '9562%' ORDER BY cmis:lastModificationDate DESC
Changing the filter to '95623%' or '956230%' still returns only records with '9562300xxx'.
With the filter '9562301xxx' or '9562302xxx' it does return the documents that it had previously omitted.
To run the filter I have tried both "LIKE" and "CONTAINS" operators, and the result is the same.
I have also tried removing the "IN_TREE" operator and using the "LIKE" operator, so the query is executed against the DB, and then it does return all the matching records.
The problem is that when the query is executed against DB we do not have information on the total number of existing records, losing the possibility of paging, which is essential in our case.
We've done a couple of total reindexes but nothing has changed.
Re: Cmis query response misses some matching results
Hello, Any other suggestions as to why filtering on cxc:cxcNumFactura LIKE '9562%' does not return documents where the value of cxc:cxcNumFactura is 9562301xxx or 9562302xxx, but filtering on cxc:cxcNumFactura LIKE '9562301%' does return matching documents? Do you need any extra information? Thank you so much