cmis query over multivalued property not working

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

abreums

Hi, I'm using Alfresco 4.2.f and Chemistry-Alfresco-Extension 0.3 (running on Mac OS X 10.8.4, tomcat 7, Java Oracle 7)

I have a multivalued property defined in an aspect:

…
 <property name="hhhh:val">
   <type>d:text</type>
   <multiple>true</multiple>
   <index enabled="true">
       <atomic>true</atomic>
       <stored>false</stored>
       <tokenised>false</tokenised>
   </index>
</property>
‍‍‍‍‍‍‍‍‍‍‍‍

I would like to make a search of all objects that has any combination of a group of values, but without any other value besides the ones from this group.

For example, searching for ('A','B','C') would bring:
Obj1 ('A')
Obj2 ('A','C')
But not
Obj3 ('D')
Obj4 ('A','D')

I tried:

SELECT * from cmis:document D join hhhh:secondary S on D.cmis:objectId = S.cmis:objectId where ANY S.hhhh:val NOT IN ('A', 'B', 'C')
‍‍‍

But this returns: Obj1, Obj2, Obj4.

I though ANY … NOT IN would exclude Obj4. Is this a bug or am I writing something wrong?

abreums

My error…

ANY is a logical OR… so,

searching using "ANY x NOT IN ('A','B','C')" would bring:
x <> 'A' OR x <> 'B' OR x <> 'C'

To achieve what I would like I need a two SELECT:
SELECT B.hhhh:val, A.cmisbjectID from hhhh:type A join hhhh:aspect B join A.cmisbjectId = B.cmisbjectId WHERE B.hhhh:val NOT IN ("original list of values")

build a list of 'hhhh:val' that would be the "negative list of hhhh:val values" =  "list resulted from first SELECT" - "original list of values",
followed by:
Select * from hhh:type A join hhhh:aspect B join A.cmisbjectId = B.cmisbjectId WHERE B.hhhh:val NOT IN ("negative list of hhhh:val values")

But a two SELECT will bring performance impact in my application.
I will evaluate a different model instead of using multi-valued field.