Search code examples
javasqlorientdb

OrientDB use result of subquery to search index

I am using OrientDb 2.1-rc4 as a document database. I have a MyClass class that gets updated very frequently by a multithreded application. To improve performance I removed the link from the State class to MyClass, and added a link and index from MyClass to State. In order to get all instances of MyClass I need to get a list of State rids and then query the index. This query gets my State rids.

SELECT DISTINCT(roles.views.states.@rid) 
FROM #12:1

returns

[["#14:0","#14:1"]]

This query finds the correct rids in the index.

SELECT FROM index:MyClass.state 
where key in [#14:0,#14:1]

When I put the two queries together.

SELECT FROM index:MWorkUnit.state 
where key in (SELECT DISTINCT(roles.views.states.@rid) FROM #12:1)

I get the following error.

java.lang.ClassCastException: com.orientechnologies.orient.core.sql.query.OSQLSynchQuery cannot be cast to java.util.List

How do I get OrientDB to treat my subquery as an rid list?

Edit: This query works when not using the index.

SELECT FROM MWorkUnit 
where state IN (SELECT expand(roles.views.states).@rid FROM #12:1)
Solution

  • This seems a bug on query against indexes: sub queries are not correctly evaluated. Please could you open a new issue or that?

    This workaround should work:

    SELECT FROM index:MWorkUnit.state 
LET $list = (SELECT DISTINCT(roles.views.states.@rid) FROM #12:1) 
where key in $list