Fetchplan issue in v3+

[creisle]

OrientDB Version: 3.0.6

Java Version:

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

OS: centos06

Expected behavior

fetch all linked records

Actual behavior

no change to the query result when the fetch plan was given

Steps to reproduce

create a new db and a single record

create vertex v content {"name": "bob"}

returns #9:0 use this as a link in the new record

create vertex v content {"name": "alice", "brother": #9:0}

select alice with a fetch plan (any of the following)

select * from v where name = 'alice' fetchPlan *:-1
select * from v where name = 'alice' fetchPlan *:1
select * from v where name = 'alice' fetchPlan brother:1

None of these return the linked record contents. For this simple example a nested project could be used which does work. I realize fetchPlans are deprecated, but I am building my queries dynamically and won't be able to use nested projections for the more complex examples

This works in 2.2.X but not 3.0.X. I am trying to migrate to the 3.0.X version

[luigidellaquila]

Hi @creisle

Fetch plans were deprecated in v 3.0 and do not work with the new SQL executor (they still work with the old one though), see http://orientdb.com/docs/3.0.x/release/3.0/Upgrading-to-OrientDB-3.0.html#fetch-plan

I strongly suggest you to take a look at nested projections, that are much more flexible and easy to use http://orientdb.com/docs/3.0.x/sql/SQL-Projections.html#nested-projections

Thanks

Luigi

[creisle]

Hi @luigidellaquila,

Ah I see, I thought deprecated meant it was still supported for a while. I have taken a look at nested projections but perhaps I just missed it in the documentation. Is there a way to project all links? For example when I want everything linked to a given record but I don't know all the attribute names beforehand so I can't specify them.

Currently I can do this with fetchPlans with the *:-1 but I have not seen a way to do something similar with nested projections and unfortunately this is required functionality for what I am doing. I think in this case this is then related to #8378

Thanks,

Cara

[luigidellaquila]

Hi @creisle

There is no way to do a deep expansion (ie. *:-1) because it's pretty dangerous in terms of memory management (you could load all the DB). You have to explicitly define the depth level, eg. *:{*:{*}}

Thanks

Luigi

[creisle]

@luigidellaquila , sorry that was a bad example to pick, just the first one to come to mind. The glob is what I was after, just couldn't find an example in the docs of this. I don't need to fetch depth to be unlimited, I always specify it anyway.

That said, when I try this in studio on the test example i'd given above. It does not return an expanded linked record. Am I still doing something wrong?

select *:{*} from v where name = 'alice'

Raw result

{
    "result": [
        {
            "name": "alice",
            "brother": "#9:0",
            "@rid": "#9:1",
            "@version": 1,
            "@class": "V"
        }
    ],
    "executionPlan": {
        "@type": "d",
        "@version": 0,
        "cost": 0,
        "prettyPrint": "+ FETCH FROM CLASS v\n  + FETCH FROM CLUSTER 9 ASC\n  + FETCH FROM CLUSTER 10 ASC\n  + FETCH NEW RECORDS FROM CURRENT TRANSACTION SCOPE (if any)\n+ FILTER ITEMS WHERE \n  name = 'alice'\n+ CALCULATE PROJECTIONS\n  * :{*}",
        "type": "QueryExecutionPlan",
        "steps": [
            {
                "@type": "d",
                "@version": 0,
                "cost": -1,
                "subSteps": [
                    {
                        "@type": "d",
                        "@version": 0,
                        "cost": 0,
                        "subSteps": [
                            
                        ],
                        "name": "FetchFromClusterExecutionStep",
                        "description": "+ FETCH FROM CLUSTER 9 ASC",
                        "type": "FetchFromClusterExecutionStep",
                        "targetNode": "FetchFromClusterExecutionStep",
                        "javaType": "com.orientechnologies.orient.core.sql.executor.FetchFromClusterExecutionStep",
                        "@fieldTypes": "cost=l"
                    },
                    {
                        "@type": "d",
                        "@version": 0,
                        "cost": 0,
                        "subSteps": [
                            
                        ],
                        "name": "FetchFromClusterExecutionStep",
                        "description": "+ FETCH FROM CLUSTER 10 ASC",
                        "type": "FetchFromClusterExecutionStep",
                        "targetNode": "FetchFromClusterExecutionStep",
                        "javaType": "com.orientechnologies.orient.core.sql.executor.FetchFromClusterExecutionStep",
                        "@fieldTypes": "cost=l"
                    },
                    {
                        "@type": "d",
                        "@version": 0,
                        "cost": -1,
                        "subSteps": [
                            
                        ],
                        "name": "FetchTemporaryFromTxStep",
                        "description": "+ FETCH NEW RECORDS FROM CURRENT TRANSACTION SCOPE (if any)",
                        "type": "FetchTemporaryFromTxStep",
                        "targetNode": "FetchTemporaryFromTxStep",
                        "javaType": "com.orientechnologies.orient.core.sql.executor.FetchTemporaryFromTxStep",
                        "@fieldTypes": "cost=l"
                    }
                ],
                "name": "FetchFromClassExecutionStep",
                "description": "+ FETCH FROM CLASS v\n   + FETCH FROM CLUSTER 9 ASC\n   + FETCH FROM CLUSTER 10 ASC\n   + FETCH NEW RECORDS FROM CURRENT TRANSACTION SCOPE (if any)",
                "type": "FetchFromClassExecutionStep",
                "targetNode": "FetchFromClassExecutionStep",
                "javaType": "com.orientechnologies.orient.core.sql.executor.FetchFromClassExecutionStep",
                "@fieldTypes": "cost=l,subSteps=z"
            },
            {
                "@type": "d",
                "@version": 0,
                "cost": 0,
                "subSteps": [
                    
                ],
                "name": "FilterStep",
                "description": "+ FILTER ITEMS WHERE \n  name = 'alice'",
                "type": "FilterStep",
                "targetNode": "FilterStep",
                "javaType": "com.orientechnologies.orient.core.sql.executor.FilterStep",
                "@fieldTypes": "cost=l"
            },
            {
                "@type": "d",
                "@version": 0,
                "cost": 0,
                "subSteps": [
                    
                ],
                "name": "ProjectionCalculationStep",
                "description": "+ CALCULATE PROJECTIONS\n  * :{*}",
                "type": "ProjectionCalculationStep",
                "targetNode": "ProjectionCalculationStep",
                "javaType": "com.orientechnologies.orient.core.sql.executor.ProjectionCalculationStep",
                "@fieldTypes": "cost=l"
            }
        ],
        "javaType": "com.orientechnologies.orient.core.sql.executor.OSelectExecutionPlan",
        "@fieldTypes": "cost=l,steps=z"
    },
    "notification": "Query executed in 0.012 sec. Returned 1 record(s)"
}

[luigidellaquila]

Hi @creisle

You are right, the following works fine

select *, brother:{*} from v where name = 'alice'

but the following doesn't

select *:{*} from v where name = 'alice'

I think we can consider it a bug, I'll check it ASAP and let you know

Thanks

Luigi

[luigidellaquila]

Hi @creisle

I just pushed a fix, it will be in the snapshot in a few minutes and will be released with v 3.0.14

Thanks

Luigi

[creisle]

that is great! thank you :)