"=" and "IN" operators doesn't work in CMIS query

[aminebarbare]

Hi guys,

I defined an aspect where the "typeDocument" property is defined :

<?xml version="1.0" encoding="UTF-8"?>
<!-- Definition of new Model -->
<model name="idoc:ideodocModel" xmlns="http://www.alfresco.org/model/dictionary/1.0">
<!-- Optional meta-data about the model -->
<description>IdeoDoc Model</description>
<author>MERIEM BENSAID</author>
<version>1.0</version>
<!-- Imports are required to allow references to definitions in other models -->
<imports>
<!-- Import Alfresco Dictionary Definitions -->
<import uri="http://www.alfresco.org/model/dictionary/1.0" prefix="d" />
<!-- Import Alfresco Content Domain Model Definitions -->
<import uri="http://www.alfresco.org/model/content/1.0" prefix="cm" />
</imports>
<namespaces>
<namespace uri="http://sante.documentaire.sqli.com/model/content/1.0" prefix="idoc" />
</namespaces>
<aspects>
<aspect name="idoc:typeDocument">
<title>Type du document</title>
<properties>
<property name="idoc:idTypedocument">
<title>identifiant du type</title>
<type>d:text</type>
<multiple>false</multiple>
</property>
<property name="idoc:libelleTypeDocument">
<title>Libellé du type</title>
<type>d:text</type>
<multiple>false</multiple>
</property>
</properties>
</aspect>
</aspects>
</model>

So I'm trying to execute the following query to get documents with "idTypedocument" equals to 9, but this query gets nothing.

SELECT * FROM vig:content content JOIN idoc:typeDocument typeDoc ON content.cmis:objectId = typeDoc.cmis:objectId WHERE content.cmis:objectId is not null  AND typeDoc.idoc:idTypedocument = '9'

However when i execute tthe following query I get the results I want.

SELECT * FROM vig:content content JOIN idoc:typeDocument typeDoc ON content.cmis:objectId = typeDoc.cmis:objectId WHERE content.cmis:objectId is not null  AND typeDoc.idoc:idTypedocument <= '9' AND typeDoc.idoc:idTypedocument >= '9'

The problem is that i'm only replacing the "=" operator with "<= AND >=" operators.
The type of the "idTypedocument" attribute is String and i'm using the version : (Version :Community - v4.2.0 (r63893-b12))

So am I forgetting something in the aspect/property definition?

Thanks in advance.

PS: the same problem with "IN" operator.

[jpotts · Accepted solution]

You cannot do those comparisons against a string, according to the CMIS spec. When comparing strings, you may only use "=, <>, [NOT] LIKE".

[aminebarbare]

Well the thing is that the operators '=' and 'IN()' are not working (the query gets nothing)

I tried combining '>=' and '<='  operators in the same query to get '=' (>= + <= is the same as = "logically") and surprisingly I get the right results from the query.

So i wanna normally use the '=' and 'IN()' in my queries instead of combining '>=' and '<=' or using 'LIKE'.

[jpotts]

What CMIS service URL are you using and what are you using to run the query?

[aminebarbare]

CMIS service URL : http://localhost:8080/alfresco/api/-default-/public/cmis/versions/1.1/atom

and I'm using

chemistry-opencmis-client-impl

version 0.12.0

[jpotts]

Can you share your code?

[jpotts]

Using 201605-GA running on Mac OS X with Chemistry 0.12.0 I cannot recreate your problem. I used your custom model and code that looks like:

String queryString = "select * from cmis:document content JOIN idoc:typeDocument tDoc ON content.cmis:objectId = tDoc.cmis:objectId WHERE content.cmis:objectId is not null AND tDoc.idoc:idTypedocument = '3'";
ItemIterable<QueryResult> results = session.query(queryString, false).getPage(5);

That returned my expected results, which is a document with its idoc:idTypedocument set to 3.

I also tried it using your <= AND >= workaround and it returned the exact same result.

[aminebarbare]

Does 201605-GA refer to a new alfresco version (5.xx) ? because the version i'm currently using is Version :Community - v4.2.0 (r63893-b12).

[janv]

Yes, Community 201605 GA includes Platform 5.1.g

Alfresco Community Edition 201605 GA Release Notes

https://svn.alfresco.com/repos/alfresco-open-mirror/alfresco/COMMUNITYTAGS/5.1.g/root/projects/repos...

Regards,

Jan

[jpotts]

I also tried this on 4.2.0 (r63893-b12) schema 6033 but for that test I used the out-of-the-box model and the following query, which should effectively be the same as yours with a custom model:

queryString = "select * from cmis:document content JOIN cm:titled tDoc ON content.cmisbjectId = tDoc.cmisbjectId WHERE content.cmisbjectId is not null AND tDoc.cm:title = 'sample-title'";

Can you set a title on one of your documents and try this query?