cmis query over multivalued property not working

[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.