CMIS: strange behaviour in querying

[ltenze]

Dear all,

I encountered a big problem in my alfresco-cmis installation.
I am trying to make some queries via CMIS and I am not able to get the correct answer when I am using cmis:lastModificationDate. For example, I tested the following query:
select cmisbjectId, cmis:name, cmisbjectTypeId
from cmis:document where IN_FOLDER('workspace://SpacesStore/1a60ae05-2eac-4f1e-84bc-3c9abefb1f23') AND cmis:lastModificationDate < TIMESTAMP '2011-10-20T00:00:00.000Z'

This query returns an empty result.
Therefore I checked the complementary query by changing "lesser than" < with "greater than" > :
select cmisbjectId, cmis:name, cmisbjectTypeId
from cmis:document where IN_FOLDER('workspace://SpacesStore/1a60ae05-2eac-4f1e-84bc-3c9abefb1f23') AND cmis:lastModificationDate > TIMESTAMP '2011-10-20T00:00:00.000Z'

I tested other timestamp format (instead of using Z I used +00:00 -00:00 and so on), but without any result.

The answer is always empty. I do not understand why. If I remove the "AND …" part, all documents are displayed.
I am not able to find any log in the alfresco installation to check if any error is present (I tried by looking at catalina.out and alfresco.log).

Have you any idea to solve this problem?
Thanks in advance!
Livius

[jpotts]

I thought I was seeing the same problem until I noticed that the year in your date string is 2011, not 2012. In my repository I don't have any documents that were modified before 2012, so it always returns an empty set. Once I changed the year, the result set came back as I would expect.

Can you double-check the year and the folder node reference?

Jeff

[ltenze]

Dear Jeff,

many thanks for your answer.
I doubly checked the year and the query should be right.
As I said, I tested the both cases "lesser than" < and "greater than" > the given date; the result is always empty. I tested the same query without the AND condition, that is
select cmisbjectId, cmis:name, cmisbjectTypeId
from cmis:document where IN_FOLDER('workspace://SpacesStore/1a60ae05-2eac-4f1e-84bc-3c9abefb1f23')
and the result is the right one… all the documents inside the folder are shown, therefore the folder reference should be ok.

I retry with other year 2011 2012 with both "greater than" and "lesser than" condition, but the result is always empty.
I tried also queries (always lesser than and greater than) by using cmis:creationDate instead of cmis:lastModificationDate, but the result is always the same: empty.

I cannot understand this behavior; could it be a configuration problem?
Thanks in advance.

Livio

[ltenze]

Maybe another useful information; I tested other queries and I found that if I use
select cmisbjectId, cmis:name, cmisbjectTypeId
from cmis:document where IN_FOLDER('workspace://SpacesStore/1a60ae05-2eac-4f1e-84bc-3c9abefb1f23') and cmis:lastModificationDate = '2011-10-20T11:26:10.816+02:00'

that is I use the equal condition (=) the result is the right one. But I modify the same query by using >= or <=, I get an empty result.

Thanks to all!
Livio

[jpotts]

Can you please share the code you are using to execute the query? For example, here is the Java class I'm using and it works fine. Maybe you could take this, compile it, and compare it to what you are doing.

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.chemistry.opencmis.client.api.ItemIterable;
import org.apache.chemistry.opencmis.client.api.QueryResult;
import org.apache.chemistry.opencmis.client.api.Repository;
import org.apache.chemistry.opencmis.client.api.Session;
import org.apache.chemistry.opencmis.client.api.SessionFactory;
import org.apache.chemistry.opencmis.client.runtime.SessionFactoryImpl;
import org.apache.chemistry.opencmis.commons.SessionParameter;
import org.apache.chemistry.opencmis.commons.enums.BindingType;


public class CmisTest {
    private String serviceUrl = "http://localhost:8080/alfresco/cmisatom";
    private Session session = null;
    private String user = "admin";
    private String password = "admin";
    
   /**
    * @param args
    */
   public static void main(String[] args) {
      CmisTest ct = new CmisTest();
      ct.doTest();
   }

   public void doTest() {
      
      String query = "select * from cmis:document where IN_FOLDER('workspace://SpacesStore/6fc1a5f9-31ac-4457-9620-eef4b82cabdd') AND cmis:lastModificationDate < TIMESTAMP '2012-10-20T00:00:00.000Z'";
       ItemIterable<QueryResult> q = getSession().query(query, false);      
       for (QueryResult qr : q) {
           System.out.println(
               qr.getPropertyByQueryName("cmis:objectTypeId")
                   .getFirstValue()
                   + " , "
                   + qr.getPropertyByQueryName("cmis:name").getFirstValue()
               );         
       }
   }
   
   public Session getSession() {
      if (session == null) {
         // default factory implementation
         SessionFactory factory = SessionFactoryImpl.newInstance();
         Map<String, String> parameter = new HashMap<String, String>();
   
         // user credentials
         parameter.put(SessionParameter.USER, getUser());
         parameter.put(SessionParameter.PASSWORD, getPassword());
   
         // connection settings
         parameter.put(SessionParameter.ATOMPUB_URL, getServiceUrl());
                  
         parameter.put(SessionParameter.BINDING_TYPE, BindingType.ATOMPUB.value());
         
         // Set the alfresco object factory
         // Used when using the CMIS extension for Alfresco for working with aspects
         //parameter.put(SessionParameter.OBJECT_FACTORY_CLASS, "org.alfresco.cmis.client.impl.AlfrescoObjectFactoryImpl");
         
         List<Repository> repositories = factory.getRepositories(parameter);
   
         this.session = repositories.get(0).createSession();
      }
      return this.session;
   }
   public String getPassword() {
      return password;
   }

   public void setPassword(String password) {
      this.password = password;
   }

   public String getUser() {
      return user;
   }

   public void setUser(String user) {
      this.user = user;
   }

   public String getServiceUrl() {
      return serviceUrl;
   }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Jeff

[ltenze]

Hi Jeff,

thanks for your help.
I selected your code, I compiled it and I changed properly the query (by varying the workspace reference). The result is empty if I use the cmis:lastModificationDate field.
If I remove the lastModificationDate, I see all the documents of my repository.
I tested both > and < for lastModificationDate: always empty.

Livio

P.S.: I am trying to interface drupal -> cmis -> alfresco. I am using drupal6 and alfresco4.0a.

[ltenze]

Another suggestion: the same query, if I use the alfresco 3.4 version, gives the right results. The same installation, with alfresco4.0a, gives always an empty result.

Livio

[jpotts]

Well, I agree, there are some strange things going on with the timestamp comparison. I'm not sure they are the same as what you are seeing.

I have a folder with three documents, all modified on October 3, 2011 at either 14:15 or 14:10.

>>> for doc in folder.getChildren():
…     doc.properties['cmis:lastModificationDate']                             
…          
datetime.datetime(2011, 10, 3, 14, 15, 2, 845000, tzinfo=<FixedOffset u'-05:00'>)
datetime.datetime(2011, 10, 3, 14, 10, 26, 199000, tzinfo=<FixedOffset u'-05:00'>)
datetime.datetime(2011, 10, 3, 14, 10, 26, 234000, tzinfo=<FixedOffset u'-05:00'>)
‍‍‍‍‍‍‍

If I do a query for October 3, less than 23:59:59, I get an empty set.

>>> query = "select * from cmis:document where IN_FOLDER('workspace://SpacesStore/2735602b-3287-4420-8d34-0eedf7b2cda9') AND cmis:lastModificationDate < TIMESTAMP '2011-10-03T23:59:59.000-05:00'"
>>> rs = repo.query(query)                                                      
>>> len(rs)                                                                     
0‍‍‍‍

If I do a query for less than or equal to October 3, but earlier in the day, I get my results even though I don't think I should:

>>> query = "select * from cmis:document where IN_FOLDER('workspace://SpacesStore/2735602b-3287-4420-8d34-0eedf7b2cda9') AND cmis:lastModificationDate <= TIMESTAMP '2011-10-03T03:59:59.000-05:00'"
>>> rs = repo.query(query)                                                      
>>> len(rs)                                                                     
3‍‍‍‍

If I do a query for less than October 4, I get the results I expect.

>>> query = "select * from cmis:document where IN_FOLDER('workspace://SpacesStore/2735602b-3287-4420-8d34-0eedf7b2cda9') AND cmis:lastModificationDate < TIMESTAMP '2011-10-04T03:59:59.000-05:00'"
>>> rs = repo.query(query)                                                      
>>> len(rs)                                                                     
3‍‍‍‍

It's like the TIMESTAMP comparison is looking only at the date and not time time component or something.

Jeff

[jpotts]

Ah-ha. I found this Jira:
https://issues.alfresco.com/jira/browse/ALF-5378

which led me to this Wiki page:
http://wiki.alfresco.com/wiki/CMIS_Query_Language#Configuring_DateTime_resolution

I wonder if this is the problem.

Jeff

[ltenze]

Hi Jeff,

unfortunately, I know that setting… for time resolution (I mean http://wiki.alfresco.com/wiki/CMIS_Query_Language#Configuring_DateTime_resolution). I already enabled it, but the result are the same. 😞
Today I will make tests with a new fresh installation of alfresco 4d.

Thanks!

Livio