topic Re: CMIS: strange behaviour in querying in Alfresco Archive

CMIS: strange behaviour in querying

ltenze — Mon, 13 Feb 2012 14:13:57 GMT

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, cmisbjectTypeIdfrom cmis

Re: CMIS: strange behaviour in querying

jpotts — Mon, 13 Feb 2012 17:20:21 GMT

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

Re: CMIS: strange behaviour in querying

ltenze — Tue, 14 Feb 2012 10:02:54 GMT

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

Re: CMIS: strange behaviour in querying

ltenze — Tue, 14 Feb 2012 10:41:23 GMT

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

Re: CMIS: strange behaviour in querying

jpotts — Tue, 14 Feb 2012 15:08:27 GMT

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

Re: CMIS: strange behaviour in querying

ltenze — Wed, 15 Feb 2012 11:58:47 GMT

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.

Re: CMIS: strange behaviour in querying

ltenze — Wed, 15 Feb 2012 13:02:26 GMT

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

Re: CMIS: strange behaviour in querying

jpotts — Wed, 15 Feb 2012 18:09:33 GMT

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

Re: CMIS: strange behaviour in querying

jpotts — Wed, 15 Feb 2012 18:12:47 GMT

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

Re: CMIS: strange behaviour in querying

ltenze — Thu, 16 Feb 2012 09:19:34 GMT

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

Re: CMIS: strange behaviour in querying

ltenze — Thu, 16 Feb 2012 15:20:12 GMT

Hi Jeff,

I installed a fresh copy of alfresco 4.0d and I tested the code you send me, with the proper modifications. The result is always the same. When I use inequality (<, >, >=, <=) condition with date, I get an empty result.

Thanks.
Livio

Re: CMIS: strange behaviour in querying

jpotts — Thu, 16 Feb 2012 15:30:53 GMT

Are you using the binary installer for 4.0d or the WAR-only distribution? How are you creating the documents you are querying for? Through CMIS or one of the web clients?

Jeff

Re: CMIS: strange behaviour in querying

ltenze — Thu, 16 Feb 2012 16:04:25 GMT

Hi Jeff,

I used the the binary installer (alfresco-community-4.0.d-installer-linux-x64.bin) and the documents are upload in alfresco by share web clients.

Thanks.
Livio

Re: CMIS: strange behaviour in querying

andy — Mon, 20 Feb 2012 11:08:48 GMT

Hi

This is a bug fix from 3.3 that lost its way when merged forward to 4.0 with the old and new (open)CMIS support.
It works in 3.4 as this does not use opencmis by default.
In 4.0 it fails as one end of the range query is not built correctly.

See https://issues.alfresco.com/jira/browse/ALF-12944

Andy

Re: CMIS: strange behaviour in querying

victorromanyuk — Fri, 30 Mar 2012 10:43:08 GMT

So will it be fixed?
I downloaded and installed Alfresco Community v4.0 on 29th March 2012. The bug is still there. Queries with dates return empty list. (Only with operators <, >, <=, >= ). Queries with operators =, <> return valid result.
Here is the example of query caught by Fiddler:
select * from cmis:document WHERE cmis:creationDate > TIMESTAMP '2012-03-27T21:17:00.000Z' - no entries though many documents have to be returned.
Regards, Victor

Re: CMIS: strange behaviour in querying

andy — Fri, 30 Mar 2012 18:36:40 GMT

Hi

It is fixed.
It will be in the next community release.

If you need this to work before then you can either use the old CMIS api (as 3.4) or consider the Enterprise version of Alfresco.

Andy

Re: CMIS: strange behaviour in querying

acarpine — Tue, 17 Apr 2012 08:09:16 GMT

Honestly I still cannot to use query with timestamp also in the Alfresco 4 Enterprise Edition
The simple query

SELECT * FROM cmis:document
WHERE cmis:creationDate > TIMESTAMP '2011-04-01T00:00:00.000+02:00'

returns always 0 results

Re: CMIS: strange behaviour in querying

andy — Tue, 17 Apr 2012 19:28:51 GMT

Hi

Exactly which Alfresco version are you using?

Andy

Re: CMIS: strange behaviour in querying

acarpine — Wed, 18 Apr 2012 06:53:31 GMT

Hi Andy,
the version I'm using is:

Alfresco Enterprise v4.0.0
(800) schema 5025
Spring Surf and Spring WebScripts - v1.0.0
(Release 968)

Andrea

Re: CMIS: strange behaviour in querying

andy — Wed, 18 Apr 2012 14:50:15 GMT

Hi

See https://issues.alfresco.com/jira/browse/ALF-12944
Fixed for V 4.0.1.
The fix is not in any 4.0.0 hot fix as far as I know.

The work around is to use the 3.4 cmis query urls which are fine (not the new open cmis versions which are affected by this issue)

Andy