Different results with first(out()) operators

[MarcelPitch]

Hi,

I'm working with 2.0 M3.

I have a graph like this :

I have different results for a query, depending on the syntax used.

I'd get the first VertexTwo, linked by EdgeOne to the VertexOne where uuid = 3.

I wrote the request like this :

SELECT 
    $myVar
FROM 
    VertexOne
WHERE
    uuid = 3
LET 
    $myVar = first(out('EdgeOne'))

RESULT : I get the #11:0 Vertex. Wrong ...

---

But when I wrote the request like this :

SELECT 
    $myVar
FROM 
    #10:1
LET 
    $myVar = first(out('EdgeOne'))

RESULT : I get the #11:1 Vertex. Right !!!

---

And now, when I wrote the request like this :

SELECT 
    first($myVar)
FROM 
    VertexOne
WHERE
    uuid = 3
LET 
    $myVar = out('EdgeOne')

RESULT : I get the #11:1 Vertex. Right !!!

---

And now, when I wrote the request like that :

SELECT 
    $myVar
FROM 
(
    SELECT
    FROM
        VertexOne
    WHERE
        uuid = 3
)
LET 
    $myVar = first(out('EdgeOne'))

RESULT : I get the #11:1 Vertex. Right !!!

---

I did the test on 2.0 M2, and same result.

I don't know if it's an anomaly or just a misunderstanding with orient SQL synthax.
I thought I wrote the same request but in different way.

Thank you and have a nice day !

[lvca]

LET must go before WHERE.

[MarcelPitch]

Ok Luca,

I have taken note of my mistake !

But in this case, moving the LET before WHERE doesn't change anything.

[lvca]

@MarcelPitch could you share your db or a portion of it to run your queries?

[MarcelPitch]

Here are 2 javascript functions for Orient Studio to recreate the test.

I execute them on a new DB and test the requests in the Studio Browser.

__createClasses

var g = orient.getGraphNoTx();

g.command('sql','CREATE CLASS VertexOne EXTENDS V',[]);
g.command('sql','CREATE PROPERTY VertexOne.uuid INTEGER',[]);

g.command('sql','CREATE CLASS VertexTwo EXTENDS V',[]);
g.command('sql','CREATE PROPERTY VertexTwo.uuid INTEGER',[]);

g.command('sql','CREATE CLASS EdgeOne EXTENDS E',[]);

__createVertices

var g = orient.getGraph();

g.command('sql',"CREATE VERTEX VertexOne SET uuid = 1", []);
g.command('sql',"CREATE VERTEX VertexTwo SET uuid = 2", []);
g.command('sql',"CREATE EDGE EdgeOne FROM (SELECT FROM VertexOne WHERE uuid = 1) TO (SELECT FROM VertexTwo WHERE uuid = 2)", []);


g.command('sql',"CREATE VERTEX VertexOne SET uuid = 3", []);
g.command('sql',"CREATE VERTEX VertexTwo SET uuid = 4", []);
g.command('sql',"CREATE EDGE EdgeOne FROM (SELECT FROM VertexOne WHERE uuid = 3) TO (SELECT FROM VertexTwo WHERE uuid = 4)", []);

If you try the requests by copy/paste from my previous comment, just beware of the (wrong) cluster id.

[lvca]

Thanks for the test case. I've found the reason. LET block is evaluated before to apply the WHERE conditions. Without an index on "uuid", a full scan is executed, so that why the first rid is #13:0. If you create an index on VertexOne.uuid, the result is the expected. This is because you used the first() function that get only the first element, so in this case #13:0.

I'm thinking that the behavior should be consistent with and without indexes, by executing the LET block AFTER the evaluation of WHERE condition, but this could break old queries that used context variables in filter.

So I'd leave this issue as open to let to the new parser to do this change (2.1). In the meanwhile you can use any of workaround.

[MarcelPitch]

OK Luca,

Thanks for the workround.

Have a nice day !

[luigidellaquila]

@lvca I don't get how this has to be fixed...
If the original behavior has to be preserved, then the index has to be ignored every time there is a LET. I don't like this very much.

IMHO this can be managed in a consistent way only in one of the following:

1. breaking backward compatibility
2. defining another function for that

[lvca]

@luigidellaquila what's your proposal for 1 and 2?