using expand in orientdb on RIDs and collections

Question

I am struggling to understand when expand will work in OrientDB. My understanding of expand as applied to an RID is that it returns the document (and all its fields) http://orientdb.com/docs/2.1/SQL-Functions.html#expand

I am sometimes getting no results from applying expand to an RID.

I'll create some sample data here and provide an example of my problem. The sample data is Person nodes connected up by "NEXT" edges.

create database plocal:people
create class Person extends V
create property Person.name string
create property Person.age float
create property Person.ident integer


insert into Person(name,age,ident) VALUES ("Bob", 30.5, 1)
insert into Person(name,age,ident) VALUES ("Bob", 30.5, 2)
insert into Person(name,age,ident) VALUES   ("Carol", 20.3, 3)
insert into Person(name,age,ident) VALUES   ("Carol", 19, 4)
insert into Person(name,age,ident) VALUES   ("Laura", 75, 5)
insert into Person(name,age,ident) VALUES   ("Laura", 60.5, 6)
insert into Person(name,age,ident) VALUES   ("Laura", 46, 7)
insert into Person(name,age,ident) VALUES   ("Mike", 16.3, 8)
insert into Person(name,age,ident) VALUES   ("David", 86, 9)
insert into Person(name,age,ident) VALUES   ("Alice", 5, 10)
insert into Person(name,age,ident) VALUES   ("Nigel", 69, 11)
insert into Person(name,age,ident) VALUES   ("Carol", 60, 12)
insert into Person(name,age,ident) VALUES   ("Mike", 16.3, 13)
insert into Person(name,age,ident) VALUES   ("Alice", 5, 14)
insert into Person(name,age,ident) VALUES   ("Mike", 16.3, 15)

create class NEXT extends E

create edge NEXT from (select from Person where ident = 1) to (select from Person where ident = 3)
create edge NEXT from (select from Person where ident = 2) to (select from Person where ident = 4)
create edge NEXT from (select from Person where ident = 8) to (select from Person where ident = 12)
create edge NEXT from (select from Person where ident = 5) to (select from Person where ident = 15)
create edge NEXT from (select from Person where ident = 15) to (select from Person where ident = 14)
create edge NEXT from (select from Person where ident = 7) to (select from Person where ident = 13)
create edge NEXT from (select from Person where ident = 13) to (select from Person where ident = 10)

This code snippet finds traversals out from node with ident 5 until it hits an Alice. It returns one row with a collection of two RIDs: RID for Laura (ident 5) and RID for Mike (ident 15), because Mike is followed by an Alice.
```
select $a 
  let $a = (traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Alice')
```
If I apply expand to $a, I get a results table with two rows, which contain details for these two records. I don't know if this is due to expand on collections (soon to be deprecated in favour of unwind?), or expand on RIDs or both.
```
select expand($a) 
  let $a = (traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Alice')
```

If I unwind $a I get a results table with two rows which contain the RIDs for these two records. (This makes sense to me)

select $a
  let $a = (traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Alice')
  unwind $a

If I expand the unwind from 3, I get the same result as from 2, which does suggest that 2 unwound and expanded the People nodes

select expand($a)
  let $a = (traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Alice')
  unwind $a

I can select the last RID in the collection of RIDs returned by 1. This gives me a table with 1 row, which contains the RID which is the last Person before Alice.
```
select last($a)
let $a = (traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Alice')
```
If I expand that result, I get 0 rows. This is really not what I expect - I would expect to get one row with the expanded record details for the record details returned in part 5
```
select expand(last($a))
  let $a = (traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Alice')
```

Can anyone shed any light on why the expand in part 6 does not expand the RID returned by last($a)?

Also if anyone knows for sure what's going on in part 2 - whether both definitions of expand are being applied at once - that would help.

EDIT

Bizarrely, this code snippet seems to do what I want point 6 to do, but I don't know why. Thought it might trigger someone's memory about what's going on (the point of my question is to understand why some things work and some don't, rather than to get any query that works in this particular case, so unfortunately having found this working snippet does not close this question.)

select expand(last($a)) 
        from (select from Person where ident = 5)
          let $a = (traverse out('NEXT') from $current while name <> 'Alice')

neRok neRok · Accepted Answer · 2015-10-29T07:49:57

1) Traverse returns all the records that it visits and that comply with the whilst clause. In this case, that is only 2 records.

2) Expand takes all the rids in a 'collection' and returns those records, in effect disposing of the query that contained the collection. Actually I've only just found out that they are deprecating expand for unwind, and I'm not sure why as they seem to serve 2 different purposes. Unwind is more like a JOIN in an RDBMS.

3) You've unwound the results against nothing, so it looks like you only get the $a 'cell' per row, but in fact you are getting nothing and $a. Perhaps this will make more the purpose of unwind clearer, select *, $a from (select from Person limit 1) let $a = (traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Alice') unwind $a

4) When you expand, you replace the original query, so all you end up with is the records in the expanded field.

5) Yes.

6) I think you have found a bug. first() and last() must be returning text or something, not a pointer. The following replicates expand(first($a)) and works, hence my presumption of a bug; select expand($a[0]) let $a = (traverse out('NEXT') from (select from Person where ident = 5) while name <> 'Alice')

using expand in orientdb on RIDs and collections

1 Answers