Defect in Prepared Statements parser and IN condition

[PhantomYdn]

OrientDB Version: 3.1.5

Java Version: 8

Imagine OClass with at least 2 properties: link (OType.LINK) and linklist (OType.LINKLIST). Previously (in OrientDB v2) the following 2 prepared statements will be considered correct and will work as expected:

SELECT FROM MyClass WHERE link IN :parameter
SELECT FROM MyClass WHERE linklist IN :parameter

But for some reason in OrientDB v3 second option stopped working. To make it working you should have it "corrected" in the following way:

SELECT FROM MyClass WHERE linklist IN [:parameter]

It's quite inconvenient because you have to know type of the property (single value or multivalue).

[luigidellaquila]

HI @PhantomYdn

What do you pass in the second case, a List or a single RID?

Thanks

Luigi

[PhantomYdn]

Hello, @luigidellaquila, I pass List.

Here is a commit which helped to overcome this behavior of OrientDB: https://github.com/OrienteerBAP/wicket-orientdb/commit/156a173ad06776f38790a8d9ca4b60388acd0b53 It was spotted in our UI: after applying of filter to a LINKLIST column - the results are empty. It was not covered in test cases - so we found that pretty late. The solution is to add brackets. And we have to adjust filters for LINK properties back to syntax without brackets - otherwise result of a query become also empty.

If it's new syntax of OrientDB - I'm good (because behavior is adjusted in our code:)), but it might be inconvenient for others.

[luigidellaquila]

Hi @PhantomYdn

Thank you for clarifying, actually it seems to be a bug. I'll check it ASAP

Thanks

Luigi

[PhantomYdn]

@luigidellaquila , thank you! It seems that something really bad happened with IN condition (or more...). Real-life example through our REST interface: https://app.paleontology.rocks/orientdb/query/db/sql/select+from+Book+where+@rid+=+%231356:3 Please pay attention to category field. Now lets try to query all Books which has contains any of the elements listed (definition of IN) of 2 first categories of this Book. Both of the following queries will return empty results: https://app.paleontology.rocks/orientdb/query/db/sql/select+from+Book+where+category+in+[%231420:4,+%231420:7] https://app.paleontology.rocks/orientdb/query/db/sql/select+from+Book+where+[%231420:4,+%231420:7]+in+category

What's making that even more interesting... These queries seems to work OK on freshly created DB 3.1.5. DB which is accessible through links was created on 3.1.3, then used on 3.1.4 and finally "upgraded" to 3.1.5

P.S. I will clean links after your investigation...

[PhantomYdn]

@luigidellaquila, checked source of com.orientechnologies.orient.core.sql.parser.OInCondition. Seems that there are really 2 issues:

• [ ] IN treats incorrectly prepared statement on the right side
• [ ] IN works literally as "equals" if both sides are collections

Will try to fix unless you will be faster:)

[PhantomYdn]

Surprisingly, CONTAINSANY do what's we are needed. Check the example below. All documents: Filtered:

But CONTAINSANY also has a defect. Check this results out:

[PhantomYdn]

@luigidellaquila, please advise, was this fixed in some latest minor releases?