Feature: Improve control flow in SurrealQL by introducing RETURN breaking and block expressions about surrealdb HOT 1 CLOSED

I realize that there needs to be clarification on how this would work with transactions.
Transactions in their current state are pretty convoluted. They only work at the top-level and provide no warning about this when used in lower scopes.
For example, the following doesn't work as expected:

{
  BEGIN;
  CREATE foo SET bar = "biz";
  CANCEL;
}

The expected result would be that the CREATE statement doesn't commit because of the CANCEL, but when executed we see that it does create a new record, and that the BEGIN/CANCEL/COMMIT statements have no apparent effect.

Anyways, the problem at hand is how RETURN statements change the result of a transaction. If returns break execution, it wouldn't allow calling COMMIT or CANCEL and getting a result.

IMO, the most intuitive solution is to make transactions work in blocks and make RETURN act like a CANCEL that breaks execution.

A couple examples:

BEGIN;
CREATE foo SET bar = "biz";
RETURN CREATE foo SET bar = "boo";
# This RETURN does nothing (unlike before, where it would make it the single return value of the transaction
COMMIT;

Result:

[
  [{id:"foo:f09uja09sdj0asd", bar:"biz"}],
  [{id:"foo:adsad0h80jfe09j", bar:"boo"}]
]

{
  BEGIN;
  CREATE foo SET bar = "biz";
  RETURN CREATE foo SET bar = "boo"; # Here, the RETURN cancels the transaction and returns the CREATE
  COMMIT; # This block doesn't return a value
}

Result:

[
  [{id:"foo:adsad0h80jfe09j", bar:"boo"}]
]

{
  BEGIN;
  CREATE foo SET bar = "biz";
  CREATE foo SET bar = "boo";
  COMMIT;
  true # the block returns true
}

Result: [true]

Edge cases:
What if a later statement depends on the result of a statement inside a canceled transaction?
This is currently covered:

BEGIN;
LET $foo = CREATE foo SET bar = "biz";
CANCEL;
RETURN $foo;

$foo produces a phantom result, a record that never existed and is worthless outside of the canceled transaction. To me, this represent the biggest flaw of this syntax: the transaction has a scope that is not expressed, but is instead merged into the current scope.
An improved syntax would be explicit about the scope:

BEGIN {
  LET $foo = CREATE foo SET bar = "biz";
  CANCEL false; # an optional return parameter
  $foo
}

Here, the scope is explicit and variables are not leaked outside. It also allows for an implicit commit as the final value. CANCEL and COMMIT would have an optional return value for setting the value of the transaction.

Anyways, I guess this does warrant an RFC considering how many breaking changes would be needed.

from surrealdb.