CONTAINSANY/CONTAINSALL not consistent with IN when using LET variable in filtering

[creisle]

OrientDB Version: 3.0.22

Java Version

openjdk version "1.8.0_191"
OpenJDK Runtime Environment (build 1.8.0_191-b12)
OpenJDK 64-Bit Server VM (build 25.191-b12, mixed mode)

OS: centos07

Expected behavior

I expected to need to expand for all filtering comparison against subqueries or none, rather than having the behaviour depend on the operator being used

Actual behavior

I am not sure if this behaviour is intended or not, but when I use a subquery let variable in filtering the parent query, I have to use expand to make CONTAINSANY/CONTAINSALL work but not when using IN. Why are they treated differently? How do I know when I need to expand?

Steps to reproduce

initial setup

// set up the schema classes
create class Kid extends V
create property Kid.name string
create class Parent extends V
create property Parent.children LINKSET Kid
create property Parent.favorite LINK Kid

// create some records
create vertex Kid set name = 'bob'
// #13:0
create vertex Kid set name = 'alice'
// #14:0
create vertex Parent set children = [13:0, 14:0], favorite = 13:0
create vertex Parent set children = [14:0]

Then when I use a subquery to filter against the link property using IN it works without expanding

SELECT * from Parent LET $kids = (select * from Kid where name = 'bob') WHERE favorite IN $kids

But when I try something similar for the linkset property it fails silent and nothing is returned

SELECT * from Parent LET $kids = (select * from Kid where name = 'bob') WHERE children CONTAINSANY $kids

However if I add expand then the above works as expected

SELECT * from Parent LET $kids = (select * from Kid where name = 'bob') WHERE children CONTAINSANY (SELECT expand($kids))

I am finding it confusing to know when I need to use expand

[grimace]

I tried this as well, because I'm having difficulty with containsAll as well as containsAny. This example ( using containsAny ) only works ( using expand ) If I put an index (NONUNIQUE) on Parents.children. And doesn't work at all if I use containsAll instead of containsAny.

for instance:

SELECT from Parent LET $kids = (select * from Kid where name IN ['bob','alice']) WHERE children CONTAINSANY (SELECT expand($kids))

returns 2 parents, but

SELECT from Parent LET $kids = (select * from Kid where name IN ['bob','alice']) WHERE children CONTAINSALL (SELECT expand($kids))

returns nothing, but I would expect it to return just one parent that has BOTH 'bob' and 'alice' as children.

However, if I just use the array of @rids like this:

SELECT from Parent WHERE children CONTAINSALL [14:0,13:0]

a single parent is returned.

[luigidellaquila]

Hi @creisle @grimace

I fixed #9048 yesterday, that should solve the problem with CONTAINSANY without indexes.
I'll check this issue ASAP and let you know

Thanks

Luigi

[luigidellaquila]

Hi @creisle @grimace

I checked it with the new fixes and I can confirm that the CONTAINSANY is already fixed.
I also pushed a fix for the CONTAINSALL, so now it works fine too

Thanks

Luigi

[grimace]

That's amazing! Thx. How do I get a milestone build?

[luigidellaquila]

Hi @grimace

you can find the snapshot here https://oss.sonatype.org/content/repositories/snapshots/com/orientechnologies/orientdb-community/3.0.25-SNAPSHOT/ (for testing)

The official hotfix will be released in a few days

Thanks

Luigi