ANY qualifier with multivalue fields.

[kbryd]

Hi

Seems like queries with ANY qualifier on multivalue fields don't work as expected.

According to http://wiki.alfresco.com/wiki/CMIS_Query_Language#quantified_in_predicate it should work…

For example, I have a type, defined like this:

               <type name="test:document">
                        <title>Document</title>
                        <parent>cm:content</parent>
                                 <property name="test:keywords">
                                        <title>Keywords</title>
                                        <type>d:text</type>
                                        <mandatory>true</mandatory>
                                        <multiple>true</multiple>
                                        <index enabled="true">
                                                <tokenised>both</tokenised>
                                        </index>
                                </property>
              </type>‍‍‍‍‍‍‍‍‍‍‍‍‍

Then, I have created an object, so the test:keywords is initialized with three values: one, two, three.

When I query the repository using:

SELECT cmis:name, test:keywords FROM test:document WHERE ANY test:keywords in ('one')
‍‍

then I get one result, as expected.

When I query for:

SELECT cmis:name, test:keywords FROM test:document WHERE ANY test:keywords in ('two')
‍‍

I get nothing.

And finally, when I query

SELECT cmis:name, test:keywords FROM test:document WHERE ANY test:keywords in ('two', 'one')
‍‍

I am getting one row again.

It looks like the engine doesn't check for other values, only the first value is taken into account.

Can someone confirm it? I am using Alfresco Community 4.0.d.

Karol

[mitpatoliya]

It looks like you have not successfully initialized  the property with values one,two,three.
only "one" is assigned as value.
How you are checking the values assigned properly or not?

[kbryd]

There are multiple values for sure. I have confirmed it by checking the object in Alfresco Node browser, additionally, when I view that object from CMIS Workbench I can also see three values.

Does it mean that such queries work for you?

Thanks
Karol

[mitpatoliya]

Hey Karol,I have not tried it by any chance.
do not consider its working for me.

[jpotts]

I can get multi-value props to return using the ANY predicate. Here are some examples. I have two test docs. One has a property called sc:version with the values "1.1, 2.2, 3.3". The other has the values "4.4, 5.5, 6.6". As you can see I can successfully retrieve either one using any value from the list. And if I query for a value not in the list, I get an empty result set.

>>> query = "select * from cmis:document as d join sc:productRelated as p on d.cmis:objectId = p.cmis:objectId where ANY p.sc:version in ('1.1')"
>>> rs = repo.query(query)
>>> len(rs)
1
>>> rs[0].name
u'test-plain-1.txt'
>>> query = "select * from cmis:document as d join sc:productRelated as p on d.cmis:objectId = p.cmis:objectId where ANY p.sc:version in ('5.5')"
>>> rs = repo.query(query)
>>> len(rs)
1
>>> rs[0].name
u'test-plain-2.txt'
>>> query = "select * from cmis:document as d join sc:productRelated as p on d.cmis:objectId = p.cmis:objectId where ((ANY p.sc:version in ('3.3')) OR (ANY p.sc:version in ('6.6')))"
>>> rs = repo.query(query)
>>> len(rs)
2
>>> rs[0].name
u'test-plain-1.txt
'>>> rs[1].name
u'test-plain-2.txt'
>>> query = "select * from cmis:document as d join sc:productRelated as p on d.cmis:objectId = p.cmis:objectId where ANY p.sc:version in ('3.3','6.6')"
>>> rs = repo.query(query)
>>> len(rs)
2
>>> rs[0].name
u'test-plain-1.txt'
>>> rs[1].name
u'test-plain-2.txt'
>>> query = "select * from cmis:document as d join sc:productRelated as p on d.cmis:objectId = p.cmis:objectId where ANY p.sc:version in ('7.7')"
>>> rs = repo.query(query)
>>> len(rs)
0
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

One difference between my example and yours is that I'm using a property defined in an aspect, so I have to do the join. But that shouldn't matter.

This is Alfresco 4.0.d Community, WAR-only, Lucene.

Jeff

[andy]

Hi

This case is covered in the unit tests.

Are you sure you property value is multi-valued?
Exactly what values are reported?

What locale are you using?

If you use the values quoted by Jeff does your property behave?

Andy