Encountered " <LET> "LET "" at line 1, column 1.

[alpeshgaglani]

OrientDB Version: 2.2.17
OS: Ubuntu

Expected behavior
script sql
LET memberEdge = (CREATE EDGE FROM #15:1 TO #21:1 SET modifiedTime = 1488486947265)
end
should succeed

Actual behavior
Error: com.orientechnologies.orient.core.exception.OCommandExecutionException: Invalid script:Encountered " "LET "" at line 1, column 1.
Was expecting one of:
...
";" ...
...

    DB name="production"
    DB name="production"

Steps to reproduce
The error goes away if I remove parenthesis:
script sql
LET memberEdge = CREATE EDGE FROM #15:1 TO #21:1 SET modifiedTime = 1488486947265
end
works. However, the query is auto created by orientjs, so I cant change it.

[wolf4ood]

hi @alpeshgaglani

do you have the script to reproduce this issue?

Thanks

[alpeshgaglani]

Hmm.. I cant reproduce this on a new database. This happens in our production database that was originally created with an older version (I believe 2.1.6). As mentioned, removing the paranthesis fixes the issue. Is query parsing dependent on the version of database or the version of the server? If former, is there a way to upgrade database version?

[luigidellaquila]

Hi @alpeshgaglani

Probably on your old db you have strictSql set to false. This setting disables the new SQL parser and can lead to this kind of problems

Thanks

Luigi

[alpeshgaglani]

Turns out it's actually the other way round - this error occurs as long is strictSql is set to TRUE.
The repro steps are:
create database plocal:/var/orientdb/TmpDB
alter database custom strictSql=true
create class VertexA extends V
create class VertexB extends V
create class AToB extends E
alter class VertexA STRICTMODE true
alter class VertexB STRICTMODE true
alter class AToB STRICTMODE true
create property VertexA.name string
create property VertexB.name string
create property VertexA.out_AToB LINKLIST AToB
create property VertexB.in_AToB LINKLIST AToB
create property AToB.out LINK VertexA
create property AToB.in LINK VertexB
insert into VertexA set name = "Hello"
insert into VertexB set name = "World"

script sql
let memberofedge = (CREATE EDGE AToB from #17:0 TO #20:0)
end

This fails. However, removal of paranthesis executes the query

This is a huge issue for our production db with multiple failed queries. we have monkey-patched it by removing the orientjs statement builder and passing straight strings for critical paths, but there are over 80 cases that it's still an issue.

[wolf4ood]

hi @alpeshgaglani

do you have a JS script to reproduce this issue?

Thanks

[alpeshgaglani]

var orientjs = require("orientjs");

var dbSvr = orientjs({
    host: 'localhost',
    port: 2424,
    username: "root",
    password: "somepassword"
});

var db, vertexAClass, vertexBClass;
dbSvr.create("TestDB").then(function (newDb) {
    db = newDb;
    return db.exec("ALTER DATABASE CUSTOM strictSql = true").then(function () {
        return db.class.create("VertexA", "V").then(function (dbClass) {
            vertexAClass = dbClass;
            return db.query("ALTER CLASS " + dbClass.name + " STRICTMODE true").then(function () {
                return dbClass.property.create("name");
            })
        });
    }).then(function () {
        return db.class.create("VertexB", "V").then(function (dbClass) {
            vertexBClass = dbClass;
            return db.query("ALTER CLASS " + dbClass.name + " STRICTMODE true").then(function () {
                return dbClass.property.create("name");
            });
        });
    }).then(function () {
        return db.class.create("AToB", "E").then(function (dbClass) {
            return db.query("ALTER CLASS " + dbClass.name + " STRICTMODE true").then(function () {
                return db.exec("CREATE PROPERTY AToB.in LINK VertexB (MANDATORY true)").then(function () {
                    return db.exec("CREATE PROPERTY AToB.out LINK VertexA (MANDATORY true)");
                });
            });
        })
    }).then(function () {
        return db.exec("CREATE PROPERTY VertexA.out_AToB LINKLIST AToB").then(function () {
            return db.exec("CREATE PROPERTY VertexB.in_AToB LINKLIST AToB");
        });
    }).then(function () {
        return db.exec("INSERT INTO VertexA SET name = 'One'");
    }).then(function () {
        return db.exec("INSERT INTO VertexB SET name = 'Two'");
    }).then(function () {
        var query = db.let("One", function (statement) {
            return statement.select().from("VertexA").where({ name: "One" });
        }).let("Two", function (statement) {
            return statement.select().from("VertexB").where({ name: "Two" });
        }).let("NewEdge-1", "CREATE EDGE AToB FROM $One TO $Two");
        /*
         function (statement) {
            return statement.create("Edge", "AToB").from("$One").to("$Two");
        });*/
        console.log("Statement: " + query.buildStatement());
        return query.commit().all();
    })
        .then(function (response) {
            console.log("response", response)
            dbSvr.drop("TestDB");
        })
        .catch(function (err) {
            console.log(err);
            dbSvr.drop("TestDB");
        });
});

[wolf4ood]

hi @alpeshgaglani

this test in particular fails for this variable name
NewEdge-1 that contains - that is not supported by the new parser

if you use NewEdge1 it will work

Then the .commit() is part of the the statement

if you want to log the real script generated you have to do this

var commit = query.commit();
console.log("Statement: " + commit.buildStatement());
return commit.all();

[alpeshgaglani]

Ah.. where can I get the details for the new parser? We are seeing a bunch of queries that used to work, but are no longer functioning. As an example, we have an edge with property publishedDate of type date. Previously, a query like:
select expand($unpublished) from #22:0 let $unpublished = (select from out_HasActivityVersion[publishedDate != null].out from $current.in_HasCategory.out) used to work.

However, the square bracket filtering is no longer working. It would be great to know what's changed so we can react accordingly.

[luigidellaquila]

HI @alpeshgaglani

In this specific case, probably the problem is only the != null, just try to replace it with is not null.