Orientdb: Subqueries using let: $current and $parent not found

[userg]

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

[userg]

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

[userg]

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

[userg]

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)

[luigidellaquila]

Hi @RamAnvesh

Regarding this query

SELECT FROM Document LET $temp = ( SELECT @rid, $depth FROM (TRAVERSE V.OUT, E.IN FROM $parent.current )

Does the Parent refer to the Document in the main SELECT? In this case, it is two levels above, so you have to use $parent.$parent.$current

Thanks

Luigi

[userg]

Hi @luigidellaquila,

The query was whether $parent / $current is supported in the from clause in 3.0.

When we try the following query we are getting the exception:
"com.orientechnologies.orient.core.exception.OCommandExecutionException: Class $parent not found DB name="

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

Though the workaround
select $foo from Bar let $foo = (select expand($parent.$current.out('foo-bar'))) works, wanted to check why the previous statement won't work or if we are missing something

[luigidellaquila]

Hi @userg

Sorry, you are right, I lost a bit the train of thought...
The problem here is that in v 3.0 the SQL executor is more strict and expects a legal target in the FROM clause (eg. a class name, a cluster:number, or an index:name).
There is a chance that in the near future we will make it a bit more flexible, but for now you have to stick to the expand() work-around

Thanks

Luigi

[niks368]

Hi @luigidellaquila
I am experiencing a similar problem when I use following query:

SELECT $columns FROM CI LET $columns = (SELECT * FROM (TRAVERSE out() FROM $parent.$parent.$current) WHERE othercolumn = 5)

I receive this error:
com.orientechnologies.orient.core.exception.OCommandExecutionException: Class $parent not found DB name="databases"

I am using OrientDB Version: 3.0.4

[RamAnvesh]

@niks368 looks like you cannot use parent and current variables in the FROM clause in OrientDB 3.0

[srini6teen]

SELECT $b FROM a
LET $b = (SELECT expand($parent.$current.out('foo-bar')))
WHERE id = 'some value'
always returns an empty value even though,

SELECT FROM a where id = 'some value' has an OUT "foo-bar" edge

Using OrientDB 3.0.4

[userg]

@srini6teen is your 'id' indexed? If so you have to use $parent.$current.rid.out. I've raised an issue for the same here https://github.com/orientechnologies/orientdb/issues/8412

[niks368]

@RamAnvesh
Since the parent and current variables don't work in OrientDB 3.0 and the select expand(...) work around only expands one level deep, how can this be done?

[luigidellaquila]

Hi @niks368

Actually you can concatenate multiple $parent in the same path, as $parent.$parent.$current, could it fit your use case?

Thanks

Luigi

[niks368]

Hi @luigidellaquila

Thank you for your answer!
In my case I don't know the depth of the traversal, which is why i cannot use select.

When I change the traverseto the select expand(...) I get the following query:
SELECT $columns FROM CI LET $columns = (SELECT expand($parent.$current.out()))
Unfortunately this query only expand one level deep.

Is there a way to do this with traversal?

[luigidellaquila]

Hi @niks368

Did you try the following:

SELECT $columns FROM CI LET $columns = (
  SELECT * FROM (
    TRAVERSE out() FROM ( 
       SELECT expand($parent.$parent.$parent.$current)
    )
  ) WHERE othercolumn = 5
)

Thanks

Luigi

[niks368]

Hi @luigidellaquila

Unfortunately this doesn't work. I get an empty array returned.

When I try this query i get the columns, so it should be found by the traverse.
SELECT $columns FROM CI LET $columns = ( SELECT expand($parent.$current.out()) )

Thanks
Niko

[luigidellaquila]

Hi @niks368

Did you try latest snapshot?
https://oss.sonatype.org/content/repositories/snapshots/com/orientechnologies/orientdb-community/3.0.7-SNAPSHOT/

I just tried the following on demodb and it seems to work fine

SELECT $columns FROM Countries LET $columns = ( SELECT expand($parent.$current.in()) )

Thanks

Luigi