SQL subquery does not work

[mbhulin]

OrientDB Version: 3.0.7 and 2.2.27

Java Version: 1.8.0_181

OS: Windows 10

Using the sample database GratefulDeadConcerts the query
SELECT AVG(performances) FROM V
results in AVG = 73

SELECT * FROM V WHERE performances < 73
returns 375 records

However the first query as a subquery
SELECT * FROM V WHERE performances < (SELECT AVG(performances) FROM V)
retrieves no records.

[luigidellaquila]

Hi @mbhulin,

I think it's a problem of automatic type conversion: the subquery result is a collection and performances is a single value.
As a work-around you can do the following:

SELECT * FROM V 
LET $average = (SELECT AVG(performances) as value FROM V)
WHERE performances < $average[0].value

I'll check it ASAP and see if we can make it work also in the original case

thanks

Luigi

[luigidellaquila]

Hi @mbhulin

I just checked latest 3.0.x snapshot and it works fine. I fixed something similar a few days ago, so probably it's already solved

Thanks

Luigi

[mbhulin]

Hi Lugi,

unfortunately the query with subquery still does not work with OrientDB 3.0.8. Please check it.

Thanks
Martin

[luigidellaquila]

Hi @mbhulin

You are right, it still fails. I'm pushing a fix right now

Thanks

Luigi