Feature: Allow LET & RETURN in subqueries aka allow subqueries with full semantics

[tomsseisums]

Is your feature request related to a problem?

Essentially, I'm trying to fit my project onto Surreal from the perspective of "Surreal as a Backend" where with the power of Surreal, we could handle all data operations in Surreal without the need of any intermediate layer.

Today we tried to set up events with rich expressions, fields with rich expressions and use rich expressions in CREATE / UPDATE, but were faced with parse errors.

---

1️⃣ DEFINE EVENT .. WHEN @expression THEN @expression - does not allow LET in either of @expression

DEFINE EVENT create_metrics ON TABLE forecast WHEN $event = "CREATE" THEN (
    LET $sales = (SELECT quantity FROM sales);
    LET $total = math::sum($sales);
    LET $count = count($sales);

    CREATE metrics SET average_sales = ($total / $count);
);

{
    "code": 400,
    "details": "Request problems detected",
    "description": "There is a problem with your request. Refer to the documentation for further information.",
    "information": "There was a problem with the database: Parse error on line 1 at character 0 when parsing 'DEFINE EVENT update_metrics ON TABLE forecast WHEN $event = \"CREATE\" THEN (\n    LET $sales = (SELECT'"
}

---

2️⃣ DEFINE FIELD ... VALUE @expression does not allow LET in @expression

DEFINE FIELD average_sales ON metrics VALUE (
    LET $sales = (SELECT quantity FROM sales);
    LET $total = math::sum($sales);
    LET $count = count($sales);

    RETURN ($total / $count);
);

{
    "code": 400,
    "details": "Request problems detected",
    "description": "There is a problem with your request. Refer to the documentation for further information.",
    "information": "There was a problem with the database: Parse error on line 1 at character 38 when parsing 'VALUE (\n    LET $sales = (SELECT quantity FROM sales);\n    LET $total = math::sum($sales);\n    LET $'"
}

---

3️⃣ CREATE ... SET @field = @value does not allow LET in @value

CREATE metrics SET average_sales = (
    LET $sales = (SELECT quantity FROM sales);
    LET $total = math::sum($sales);
    LET $count = count($sales);

    RETURN ($total / $count);
);

{
    "code": 400,
    "details": "Request problems detected",
    "description": "There is a problem with your request. Refer to the documentation for further information.",
    "information": "There was a problem with the database: Parse error on line 1 at character 15 when parsing 'SET average_sales = (\n    LET $sales = (SELECT quantity FROM sales);\n    LET $total = math::sum($sal'"
}

Describe the solution

Wherever a subquery can be used as an expression, allow using the full SRQL power there.

Alternative methods

An intermediate API layer would definitely work.
^{But I think that Surreal is closer to being a complete CRUD layer on it's own than what it's worth to build an intermediate layer around it.}

SurrealDB version

surreal 1.0.0-beta.8+20220930.c246533 for macos on aarch64

Contact Details

@tomsseisums

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct

[tomsseisums]

4️⃣ There is 4th scenario as well CREATE ... CONTENT:

CREATE metrics CONTENT (
    LET $sales = (SELECT quantity FROM sales);
    RETURN { sales: $sales }
)

{
    "code": 400,
    "details": "Request problems detected",
    "description": "There is a problem with your request. Refer to the documentation for further information.",
    "information": "There was a problem with the database: Parse error on line 1 at character 15 when parsing 'CONTENT (\n    LET $sales = (SELECT quantity FROM sales);\n    RETURN { sales: $sales }\n)'"
}

[mathe42]

My thoughts:

1. Also related to Feature: Allow multiple expression in IF THEN ELSE statements #225
2. Lets define some naming lets call this list of statements a block (of statements)
3. We don't need RETURN just use the value of last statement. Yes RETURN is much more explicit but that will not work in for example the ifelse case as there you expect the return to be the of the parent block (that where the if is located in).
4. I think we should use { and } as starting and end as that is how it is used in other languages.
5. Note: If we have this than Feature: Procedures or Functions #247 could be implemented much more easy.

Syntax

This ignores whitespace + comments

1. first char should be {
2. List
   a. statements seperated by ; (we should not allow DEFINE-statements)
   b. ended by ; if a value is provided
3. optional a value (number, object, expression,...)
4. last char should be }

Parser should be something like (NOTE: I know that this is not the exact rust-syntax but I think this gives a good idea)

tuple(
  tag("{"),
  alt(
    tuple(value, opt(tag(';'))),
    tuple(
      statementsWithoutDefine,
      opt(tuple(tag(';'), value, opt(tag(';'))))
    )
  ),
  tag("}")
)

[m59peacemaker]

My thoughts:

1. Also related to [Feature: Allow multiple expression in IF THEN ELSE statements #225](https://github.com/surrealdb/surrealdb/issues/225)

2. Lets define some naming lets call this list of statements a `block (of statements)`

3. We don't need `RETURN` just use the value of last statement. Yes `RETURN` is much more explicit but that will not work in for example the `ifelse` case as there you expect the return to be the of the parent block (that where the if is located in).

4. I think we should use `{` and `}` as starting and end as that is how it is used in other languages.

5. Note: If we have this than [Feature: Procedures or Functions #247](https://github.com/surrealdb/surrealdb/issues/247) could be implemented much more easy.

Syntax

This ignores whitespace + comments

1. first char should be `{`

2. List
   a. statements seperated by `;` (we should not allow `DEFINE`-statements)
   b. ended by `;` if a value is provided

3. optional a value (number, object, expression,...)

4. last char should be `}`

Parser should be something like (NOTE: I know that this is not the exact rust-syntax but I think this gives a good idea)

tuple(
  tag("{"),
  alt(
    tuple(value, opt(tag(';'))),
    tuple(
      statementsWithoutDefine,
      opt(tuple(tag(';'), value, opt(tag(';'))))
    )
  ),
  tag("}")
)

What's the issue with DEFINE in a THEN statement? We have had to fork surrealdb and do an ugly hack to get an equivalent behavior. I was hoping to get rid of the fork and our hack using the new js query function from #2331, but the parser limitation applies to that as well:

DEFINE EVENT define_event ON event WHEN $event = 'CREATE' OR $event = 'UPDATE' THEN (function($value) {
	const value = arguments[0]
	return await surrealdb.query(`DEFINE EVENT ${value.name} ON ${value.table} WHEN ${value.condition} THEN (${value.statement})`)
});

CREATE event SET name = "foo_event", table = "foo", condition = "$event = 'CREATE'", statement = "CREATE bar SET x = 1";

There was a problem with the database: Problem with embedded script function. An exception occurred: Parse error on line 1 at character 0 when parsing 'DEFINE EVENT foo_event ON foo WHEN $event = 'CREATE' THEN (CREATE bar SET x = 1)'