Fetch plan fails when part of query

[borov333]

OrientDB Version, operating system, or hardware.

• v2.2.10

Operating System

• Linux
• MacOSX

Expected behavior and actual behavior

Expected to get the query results with fetched linked objects when executing a query with or without parameters. However, the resulting object (JSON) is returned with @rids instead of the actual linked objects. The only way I can get linked objects is at the time of toJson("fetchPlan:...."). But, I believe it will call the remote database second time, which unnecessary creates latency.

Steps to reproduce the problem

CLASS 'LeadCategory'
PROPERTIES
+----+--------------+-------+------------+---------+--------+--------+----+----+-------+-------+
|# |NAME |TYPE |LINKED-TYPE/CLASS|MANDATORY|READONLY|NOT-NULL|MIN |MAX |COLLATE|DEFAULT|
+----+--------------+-------+------------+---------+--------+--------+----+----+-------+-------+
|0 |presetAttributes|LINKSET|PresetAttribute |false |false |false | | |default| |
|1 |className |STRING | |true |false |true | | |default| |
|2 |id |INTEGER| |true |true |true | | |default| |
+----+--------------+-------+------------+---------+--------+--------+----+----+-------+-------+

Here are few failing queries:
Query 1 - Fails
List result = db.query(new OSQLSynchQuery("select from LeadCategory where id = 1").setFetchPlan("*:-1"));
result.get(0).toJSON();

It produces the following json
{"@type":"d","@Rid":"#45:1","@Version":1,"@Class":"LeadCategory","id":1,"className":"TempLead","presetAttributes":["#80:0","#79:0"],"@fieldTypes":"presetAttributes=n"}

Query 2 - Fails
String sql = "select from LeadCategory where id = ?";
OSQLSynchQuery query = new OSQLSynchQuery<>(sql);
query.setFetchPlan("*:-1");
List result = db.command(query).execute(categoryId);
result.get(0).toJSON();

It produces the same json
{"@type":"d","@Rid":"#45:1","@Version":1,"@Class":"LeadCategory","id":1,"className":"TempLead","presetAttributes":["#80:0","#79:0"],"@fieldTypes":"presetAttributes=n"}

Query 3 - Succeeds
String sql = "select from LeadCategory where id = ?";
OSQLSynchQuery query = new OSQLSynchQuery<>(sql);
List result = db.command(query).execute(categoryId);
result.get(0).toJSON("fetchPlan:*:-1");

produces correct json
{"id":1,"className":"TempLead","presetAttributes":[{"name":"utilityCompany","values":["American Electric Power","Azusa Light & Water","Black Hills Energy","Bryan Texas Utilities","California Public Utilities Commission","Central Maine Power","City & County of San Francisco","Duke Energy Indiana","Excel Energy","Georgia Power","Guadalupe Valley Electric Cooperative","High West Energy"]},{"name":"contactTime","values":["anytime","morning","afternoon","evening"]}]}

So, that was problem #1.
Also, as a problem #2, I spotted that I can't attach the fetch plan like in your docs example in the fluent function manner:
OSQLSynchQuery query = new OSQLSynchQuery<>(sql).setFetchPlan("*:-1");

Instead, I had to split the functions, which didn't work (as per problem #1 above), like:
OSQLSynchQuery query = new OSQLSynchQuery<>(sql);
query.setFetchPlan("*:-1");

Let me know if I'm doing something wrong. This is my first time playing with fetching :)

[tglman]

Hi @borov333,

did you try to use the fatchplan inside the query ?
ex: String sql = "select from LeadCategory where id = ? fetchplan *:-1";

Bye

[borov333]

Unfortunately, it failed too. The resulting json is still the same:

{"@type":"d","@Rid":"#45:1","@Version":1,"@Class":"LeadCategory","id":1,"className":"TempLead","presetAttributes":["#80:0","#79:0"],"@fieldTypes":"presetAttributes=n"}

[tglman]

hi @borov333,

I Just reread the issue, the fetchplan in the query has nothing to do with the way the json will be generated, they are two different api, the fetch plan in the query let you choose how much data connected to the results you have to download to the client, the fetch plan in the toJson let you to choose how much data connected to the result you want in the result include in the json.

Se everything work as expected, and for your case i guess you need both of them, first to download the data, second to generate the json.

Hope it help,

Bye

[borov333]

Hi Tglman,

I did a pretty dumb test, I hope I did it right. I added the fetch plan to the query to pull the records and closed db connection to make sure we don't go back. Then I called the toJson to get the actual JSON. I got an error. When I called toJson it wants to go back to the db and pull the records by the defined fetch plan. So, it is not acting as explained in the manual that fetch plan avoids extra trips to the database. Either I'm doing something wrong or there is a possibility for a bug?

Here is FAILING logic.

List<ODocument> result = null;
try (ODatabaseDocumentTx db = DbPool.getConnection()) {
    String sql = "select from LeadCategory where id = ? fetchplan *:-1";
    OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
    result = db.command(query).execute(categoryId);
}

// this call toJSON(...) results in error below
System.out.println("JSON = "+ result.get(0).toJSON("fetchPlan:*:-1")); 

SEVERE: Fetching error on record #45:1
com.orientechnologies.orient.core.exception.ODatabaseException: The database instance is not set in the current thread. Be sure to set it with: ODatabaseRecordThreadLocal.INSTANCE.set(db);

In my understanding the results are already pulled from the database and should be available in the List, but it's not the case. toJson(...) wants to go back to the db.

Here is WORKING logic, but it comes without anything fetched in the query

List<ODocument> result = null;
try (ODatabaseDocumentTx db = DbPool.getConnection()) {
    String sql = "select from LeadCategory where id = ? fetchplan *:-1";
    OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
    result = db.command(query).execute(categoryId);
}

System.out.println("JSON = "+ result.get(0).toJSON());

The result is:
JSON = {"@type":"d","@Rid":"#45:1","@Version":1,"@Class":"LeadCategory","id":1,"className":"TempLead","presetAttributes":["#80:0","#79:0"],"@fieldTypes":"presetAttributes=n"}

Here is WORKING logic, but it fetches records on a second db call :(

try (ODatabaseDocumentTx db = DbPool.getConnection()) {
    String sql = "select from LeadCategory where id = ?";
    OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);

    List<ODocument> result = db.command(query).execute(categoryId);
    System.out.println("JSON = "+ result.get(0).toJSON("fetchPlan:*:-1"));
}

The result is:
{"id":1,"className":"TempLead","presetAttributes":[{"name":"utilityCompany","values":["American Electric Power","Azusa Light & Water","Black Hills Energy","Bryan Texas Utilities","California Public Utilities Commission","Central Maine Power","City & County of San Francisco","Duke Energy Indiana"]},{"name":"contactTime","values":["anytime","morning","afternoon","evening"]}]}

[tglman]

hi @borov333,

I'll explain it in an easy way: the fetch plan in the query download the records and put them in the database local cache, the toJSON when it generate the nested json look for the records in the database local cache.

so if you do a toJSON with fetchplan and there is no database open in the current context it will complain.

If you don't use the fetchplan in the query, the toJSON will look on the server for the records.

the right solution is to use both and do the toJSON inside the try{}

Hope it help.

Bye

[borov333]

It makes sense now. I re-read the docs and it says:

OrientDB loads all connected records into the local client, meaning that the collections remain lazy, but when accessing content, the record is loaded from the local cache to mitigate the need for additional connections.

I think what confused me was that if it's in the local client's cache then I won't need to keep the connection with the db, but I guess it needs it to be able to pull these records from the client's cache.

Thanks for the details tglman.

[tglman]

Hi,

No problem, can we close this ?

bye

[borov333]

Yes, please.

[tglman]

closing :)