search

tsgrp / HPI

OpenContent Management Suite (OCMS)
http://www.tsgrp.com/products
17 stars 5 forks source link

CMIS searches on date ranges exclude single value or the leftmost range due to use of greater than operator, not greater or equals operator #2435

Closed renesaladrigas closed 3 years ago

renesaladrigas commented 3 years ago

When utilizing CMIS query language for ACA, searching for dates is incorrectly excluding the lower range of date range searches. For instance, a search against a date property with the given range of "09-01-2021 00:00.000" through "09-08-2021 00:00.000" will not include results for documents whose date is "09-01-2021 00:00.000" because the lower end of the range is a >, not and =>.

Here is a screenshot of a document created with the value "09-01-2021" for the field "Report Date": image

If we evaluate the value of the unix date of "1630472400000" to a human readable date, you will see the document's "Report Date" is "09-01-2021 00:00.000": image

When using the CMIS search implementation the query that is generated from searching using a datebox looks like the one below. The where clause needs to be modified to >= to include the result that was created at the 0th hour of the day, as facilitated by the application's upload capabilities.

2021-09-01 23:09:37,890 77348682 ajp-apr-8009-exec-5 DEBUG AbstractAlfrescoEmbQueryImpl Generated Query: SELECT insuranceDemo_claimsDocument.cmis:objectId FROM insuranceDemo:claimsDocument as insuranceDemo_claimsDocument JOIN insuranceDemo:hasClaimsInfo as insuranceDemo_hasClaimsInfo on insuranceDemo_claimsDocument.cmis:objectId = insuranceDemo_hasClaimsInfo.cmis:objectId WHERE ((insuranceDemo_hasClaimsInfo.insuranceDemo:reportDate > '2021-09-01T00:00:00.000' AND insuranceDemo_hasClaimsInfo.insuranceDemo:reportDate < '2021-09-01T23:59:59.999')) ORDER BY insuranceDemo_claimsDocument.cmis:lastModificationDate

Note: this issue is not happening in edge2, but it's reproducible in a client environment that is using Alfresco 7.x.

renesaladrigas commented 3 years ago

CMIS QUERY IMPL DATE RANGE: Correcting behavior that excluded the left and right boundaries for CMIS date range queries. For a selected date (with no time, simply date), the leftmost boundary will include values of 00:00.000, and the righmost boundary (with no time, simply date) will include values of 59:59.999 in the result set of values for this query implementation. Revision #26994 CR - dhartman