SQL batch-command does not return correct values for DELETE

[mamobyz]

As described here: https://github.com/orientechnologies/orientdb/wiki/SQL-batch, it should be possible to get all responses of the statements in a batch back as an array for further evaluations. This works well for Insert/Create, which suggests the feature is supported by OrientDB v2.0, which I am using. But it seems not working for Delete statements.

It is not possible to execute LET command in OrientoDB console, nor in the Web Studio (BTW: also in this regard, I am not sure, whether it is an issue or LET is intentionally not supported in both tools?). Thus, I can only demonstrate the issue with a node.js program. In the code, the line q1.commit().return(['$c1', '$c2', '$c3']) works well, contrary to the line q2.commit().return(['$d1', '$d2']) for deletion. For deletion, only a single value can be returned, with return('$d?'), but not multiple values.

var Oriento = require('oriento');

var db = Oriento({
    host: 'localhost',
    port: 2424,
    user: 'root',
    password: 'root'
    , logger: {debug: console.log.bind(console)}
}).use('GratefulDeadConcerts');


var q1 = db.createQuery();
q1.let('c1', 'create vertex v set name = "4f"');
q1.let('c2', 'create vertex v set name = "1f"');
q1.let('c3', 'create vertex v set name = "5d"');
q1.commit().return(['$c1', '$c2', '$c3']);

var q2 = db.createQuery();
q2.let('d1', 'delete vertex v where name = "4f"');
q2.let('d2', 'delete vertex v where name = "5d"');
q2.commit().return(['$d1', '$d2']);

q1.all().then(function (res) {
    console.log(res);
    return q2.all();
}).then(function (res) {
    console.log(res);
}).finally(function () {
    db.server.close();
});

[martingg88]

yes.. it's doesn't return correct value if you have exapnd function in any select query. as for example below. it return the invalid result for user and payment, but except account.

BEGIN
LET account = SELECT * FROM account WHERE email = "ck@hotmail.com"
LET user = SELECT expand(in("own")) FROM account WHERE email = "ck@hotmail.com"
LET payment = SELECT EXPAND(out("own")[@Class =payment]) FROM $user
COMMIT
RETURN [$account, $user, $payment]

[
{ '@type': 'd',
'@Class': 'account',
email: 'ckgan2004@hotmail.com'
password: '00a8a1d8571d5cd2bf0
username: 'ckgan2004',
language: 'en',
timezone: 'Asia/Kuala_Lumpur',
active: false,
online: false,
ctime: Sun Feb 22 2015 20:45:0
mtime: Sun Feb 22 2015 23:08:1
nickname: 'ckgan2004',
'@Rid': '#30:82' },
{ '@Rid': '#5:0' },
{ classId: -2, value: null }
]

[martingg88]

look forward anyone to solve this issue as soon as possible

[lvca]

DELETE command, by default, returns the number of deleted records. If you need the deleted record, append "RETURN BEFORE" to the command.

[mamobyz]

@lvca: I am sorry, I do not agree with you. Your response is inaccurate at least in the following two ways:

• DELETE VERTEX does not support RETURN-clause, not at the moment. I am testing on v2.0.7.
• If you say, DELETE command returns the deleted record by default, why is it necessary to add the RETURN BEFORE directive at all?

A mere DELETE would accept an extra RETURN clause whether needed. But I want to delete a vertex. Please suggest a solution, thanks!

[lvca]

@mamobyz You're right: DELETE VERTEX doesn't support RETURN keyword. I switch this as new implementation.

[lvca]

Implemented.