Queries with embedded queries in where clause fail to use indexes.

OrientDB Version: 3.2.10

Java Version: 17.0.1+12-39

OS: RHEL 8.6

Expected behavior

Queries with embedded queries in where clause should use appropriate indexes when executing.

Actual behavior

select from UOM_RELATIONSHIP_TYPE where inCOI = (select @rid from COI where COI_NAME = 'my coi') or
select from UOM_RELATIONSHIP_TYPE where inCOI.COI_NAME = 'my coi'
executes in 300ms because it is doing a full scan of each vertex type

While doing this:

var rid = oDatabaseDocument.query("select @rid from COI where COI_NAME = 'my coi'");
var output = oDatabaseDocument.query("select from UOM_RELATIONSHIP_TYPE where inCOI = " + rid);

executes in 9ms, because it uses indexes and avoids a full scan.

Steps to reproduce

1. Create 2 tables with one that has an out edge to the other.
2. Index the search field (from example above a unique btree on COI_NAME)
3. Populate with some data.
4. Create a query that uses the out edge as the filter.
5. Execute the query with the PROFILE key word to see the databases execution plan, it should fail to use indexes.
6. Execute the query in the console and time the response.

No source file or test is named. Reproduce the two queries on OrientDB 3.2.10 with PROFILE, compare their execution plans with the variable-based version, and trace the query-planning path responsible for index selection. Done means the embedded-query forms use the appropriate indexes and avoid full scans.