Subqueries using let: $current and $parent not found

[balalakshmin]

OrientDB Version: 3.0.2

Java Version: 8

OS: Mac OS

Expected behavior

When executing the command:

select $foo from Bar
let
$foo = (select expand(out('foo-bar')) from $parent.current)

Returns Foo vertices.

Actual behavior

Exception:
com.orientechnologies.orient.core.exception.OCommandExecutionException: Class $parent not found DB name=

Sidenote:
In Orientdb 2.2.x the below used to work
select $foo from Bar
let
$foo = (select expand(out('foo-bar')) from $current)

But in v3.0.2 this also throws exception:
com.orientechnologies.orient.core.exception.OCommandExecutionException: Class $current not found DB name=

[luigidellaquila]

Hi @UserG

Please try the following:

select $foo from Bar
let
$foo = (select expand($parent.$current.out('foo-bar')))

In v 3.0 $xxx indentifiers are just standard identifiers, so they can also be class names. OrientDB just assumes this is the case, this is why it doesn't work.

Thanks

Luigi

[balalakshmin]

Hi @luigidellaquila

Thank you very much for your response.
But the above query returns empty results,
but when I do
select expand(out('foo-bar')) from Bar
I get results.

[luigidellaquila]

Hi @UserG

It seems to be a bug, I'm checking it now

Thanks

Luigi

[balalakshmin]

Thank you !

[luigidellaquila]

Hi @UserG

I think I have a fix for this, I'm pushing it now

Thanks

Luigi

[luigidellaquila]

Hi @UserG

I just pushed a fix, it will be released with v 3.0.4

Thanks

Luigi

[balalakshmin]

Hi @luigidellaquila

Thanks for the quick response. When can we expect 3.0.4 release?

[luigidellaquila]

Hi @UserG

I'd say in a very short term, 24-48h hopefully

Thanks

Luigi

[RamAnvesh]

Hi @luigidellaquila ,

In the above scenario, how do we order the results of the sub query?
For example,

select $foo from Bar let $foo = (select $parent.$current.out('foo-bar') as d order by d.age)

throws an error saying
Error parsing query: select select $foo from Bar let $foo = (select $parent.$current.out('foo-bar') as d order by d.age) ^ Encountered " <ORDER> "order "" at line 1, column 226. Was expecting one of: <LIMIT> ... <SKIP2> ... <OFFSET> ... <TIMEOUT> ... <FETCHPLAN> ... <LOCK> ... <LET> ... <NOCACHE> ... <PARALLEL> ... <UNWIND> ... ")...

Is there something we are missing?

Also is there an updated documentation which shows us how to use these variables in child context? Because we are not able to use $parent variable in from clause. Is this by design?

[luigidellaquila]

Hi @UserG

A SELECT <expression> statement returns only one element, you just cannot sort it.

You could sort the collection contained in your d, but you have to do it with a custom function (eg. a js function)

Thanks

Luigi

[RamAnvesh]

Thanks for the response. One issue is that we are having to use select <expression> in the sub-query because this query:
SELECT FROM Document LET $temp = ( SELECT @rid, $depth FROM (TRAVERSE V.OUT, E.IN FROM $parent.current )
is not working. The $parent is somehow not available in the FROM expression (We get an error saying com.orientechnologies.orient.core.exception.OCommandExecutionException: Class $parent not found DB name=...). This example query is copied from this documentation:
https://orientdb.com/docs/last/sql/SQL-Query.html#sub-query

Has this behaviour changed?

To be more accurate, our query initially looked something like this:
SELECT $foo FROM Bar LET $foo = (SELECT expand(out('foo-bar')) FROM $parent.current)