Select from where traverse operator not working

66 views
Skip to first unread message

StevenTomer

unread,
Mar 12, 2018, 6:00:02 PM3/12/18
to OrientDB
Hello,

I'm using OrientDB 2.2.32.  We're updating our software from 2.0.8.

The traverse() operator in selects does not appear to be working.

When I run the attached program, I get a com.orientechnologies.orient.core.sql.OCommandSQLParsingException.

Exception in thread "main" com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error parsing query:
SELECT FROM Person WHERE givenname = 'William' and in traverse(0,-1,'in') ( givenname = 'George' )
                                                    ^
Encountered "" at line 1, column 52.
Was expecting one of:
    
DB name="familytree"

I hope it hasn't been removed.  We use both the TRAVERSE FROM and
SELECT FROM WHERE traverse() extensively.

TRAVERSE FROM is wonderful for gathering a group of records from a given point.
The traverse() operator is wonderful for queries like the above (return all people named William
descended from a person named George.

We really need both.

Thanks,

Steven Tomer
FamilyTree.java

Luigi Dell'Aquila

unread,
Mar 13, 2018, 4:07:47 AM3/13/18
to orient-...@googlegroups.com
Hi Steven,

The traverse() operator was deprecated long time ago and is not fully supported anymore.
I strongly suggest you to use TRAVERSE queries (https://orientdb.com/docs/2.2.x/SQL-Traverse.html) or even better MATCH queries (https://orientdb.com/docs/2.2.x/SQL-Match.html)

Another thing: if you are migrating now, you could probably consider v 3.0 (see https://orientdb.com/orientdb-labs/), the final GA will be released this week

Thanks

Luigi


--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Steven Tomer

unread,
Mar 13, 2018, 9:52:21 AM3/13/18
to OrientDB
Hello Luigi,

That's sad news indeed.  It will require a major task to rip out all of the places we're using traverse().

I've been playing around with the MATCH operator, and I can't get it to model the former query:

SELECT FROM Person WHERE givenname = 'William' and in traverse(0,-1,'in') ( givenname = 'George' )

I've tried:

MATCH {class: Person, as: person, where: (givenname = 'William')}.in(){while: ($depth > 0), where: (givenname = 'George')} RETURN person
MATCH {class: Person, as: person, where: (givenname = 'William')}.in('in'){while: ($depth > 0), where: (givenname = 'George')} RETURN person
MATCH {class: Person, as: person, where: (givenname = 'William')}.in('MyEdge'){while: ($depth > 0), where: (givenname = 'George')} RETURN person

None of your examples have an intermediary 'edge' type between them.  We have Person -> MyEdge -> Person -> MyEdge -> Person.  The traverse
operator lets me hop over the edges.  How do you do it with MATCH?

Steve



--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

Luigi Dell'Aquila

unread,
Mar 13, 2018, 10:16:52 AM3/13/18
to orient-...@googlegroups.com
Hi Steve,

Try this

SELECT expand(person) FROM (
  MATCH {class: Person, as: person, where: (givenname = 'William')}.in(){while: ($depth = 0 OR givenname != 'George'), where: (givenname = 'George')} RETURN person
)

The $depth = 0 guarantees that you don't stop traversing immediately; the givenname != 'George' just stops traversing as soon as you find a "George", I guess it is what you want (you don't need duplicates)

Thanks

Luigi

Steven Tomer

unread,
Mar 13, 2018, 11:03:08 AM3/13/18
to OrientDB
Luigi,

That query never returns (looks like it goes into an infinite loop).

I had to kill the server process to make it stop.

Any other ideas?

Steve

Luigi Dell'Aquila

unread,
Mar 13, 2018, 11:08:09 AM3/13/18
to orient-...@googlegroups.com
Hi Steve,

ah, got it, the old traverse() stopped when it found duplicates, while the MATCH doesn't.

Do you have a dataset I can check, so that I can try it and give you better help?

Thanks

Luigi


Steven Tomer

unread,
Mar 13, 2018, 11:16:20 AM3/13/18
to OrientDB
I attached the java program that creates it to my initial report.

To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

Steven Tomer

unread,
Mar 20, 2018, 11:30:26 AM3/20/18
to OrientDB
Luigi,

I've zipped up the dataset I've been using and am attaching it here.

We really need a way to do queries like this.  Is there any chance we can re-enable the old traverse() operator?

Thanks,

Steve
familytree.zip

Luigi Dell'Aquila

unread,
Mar 20, 2018, 11:34:24 AM3/20/18
to orient-...@googlegroups.com
Hi Steve,

Thank you very much, I'll check it asap. 
There is a way to re-enable the old traverse, you have to disable strict SQL parsing:

ALTER DATABASE CUSTOM strictSQL=false
Anyway, I wouldn't recommend it in the long run, as you will lose a lot of stability in SQL parsing

I'll give you a feedback asap about the query

Thanks

Luigi

Steven Tomer

unread,
Mar 20, 2018, 11:37:21 AM3/20/18
to OrientDB
Thanks so much!

Steve

Steven Tomer

unread,
Mar 26, 2018, 9:53:13 AM3/26/18
to OrientDB
Luigi,

I haven't heard back on this.

Since the backend to the traverse() is still there, we're wondering how much trouble it would be to add it into the new parser.

It definitely has some powerful and unique use cases.  If these aren't covered by the new match projection, I would suggest that it should live on to cover them.

Thanks, 

Steve

Thanks so much!

Steve

To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

Luigi Dell'Aquila

unread,
Mar 26, 2018, 10:45:06 AM3/26/18
to orient-...@googlegroups.com
Hi Steve,

Sorry, we are pretty busy with the release of v 3.0.0, I'll try to follow up as soon as I can.

The traverse() executor is still there, but only in the old SQL executor. The new parser (strict SQL) was implemented for the new executor, so it doesn't make much sense to have it in the parser without implementing it in the new executor. 
I have to say that in general we don't like the traverse() operator syntax much, and now we have many alternatives like TRAVERSE and MATCH queries that are supposed to do the same thing, so adding complexity re-introducing a legacy behavior does not sound a very good choice. 
To be honest, since we switched on the strictSQL option by default (more than two years ago), it's maybe the second time we have an explicit request for the traverse() operator, and in both cases it was for a migration from previous versions, so I'm pretty confident that we can cover your use case what we have, with better performance and a more elegant way.

I'll keep you posted

Thanks

Luigi


Thanks so much!

Steve

To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

Steven Tomer

unread,
Apr 9, 2018, 1:38:27 AM4/9/18
to OrientDB
Luigi,

When is the release of 3.0.0 planned?

We're still waiting to hear back from you on how to do this.  

Thanks,

Steve

Thanks so much!

Steve

To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.

Steven Tomer

unread,
May 17, 2018, 12:02:54 PM5/17/18
to OrientDB
Luigi,

It's been almost two months since we asked about this.  We'll turn off the strictSQL option if we have to, but that doesn't seem like a good long-term solution.  We really would like a way to do this.

We're having major memory issues with our current version of OrientDB, and would like to upgrade, but this is really standing in the way.

Please let us know what we can do.  We've been patient, but this seems extraordinary.

Steve

Thanks so much!

Steve

To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-database+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/ic5UE-kyi-w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-database+unsubscribe@googlegroups.com.

StevenTomer

unread,
May 31, 2018, 2:16:32 PM5/31/18
to OrientDB
It's been another two weeks.  Is there anyone there that can answer this question?

Why the silence?

Steve

Luigi Dell'Aquila

unread,
Jun 1, 2018, 2:59:32 AM6/1/18
to orient-...@googlegroups.com
Hi Steve,

Sorry for the long silence.
As you probably noticed, the official OrientDB v 3.0 is now available on the website.
I'll try to give you a feedback asap about a new query for the same funcitonality

Thanks

Luigi

StevenTomer

unread,
Jun 21, 2018, 8:56:56 PM6/21/18
to OrientDB
Luigi,

It's been yet another 3 weeks...

Still waiting.

Steve

Luigi Dell'Aquila

unread,
Jun 22, 2018, 3:44:33 AM6/22/18
to orient-...@googlegroups.com
Hi Steve

Probably the problem here is that you have loops in your graph.
I think the only solution so far is to add a reasonable MAXDEPTH to the MATCH pattern, eg.

SELECT expand(person) FROM (
  MATCH {class: Person, as: person, where: (givenname = 'William')}.in(){while: (($depth = 0 OR givenname != 'George') AND $depth < 10), where: (givenname = 'George')} RETURN person
)

I hope it helps

Thanks

Luigi


--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages