We need this information to perform delete and update operations
using space API in future.

Option sql_vdbe_max_steps stops long running
queries from blocking tx thread or queries that 
use too much memory during local execution. 

Each sql query is compiled into VDBE opcodes on storages. This
parameter sets max number of opcodes that VDBE
can execute.

Example: `select * from t option(sql_vdbe_max_steps=1000)`

Option vtable_max_rows limits the maximum number of rows in
virtual table. This limit is checked on storages before returning
intermediate results and on routers when receiving results from
storages.

Example: `select * from t option(vtable_max_rows=1000)`

* added HAVING clause. HAVING condition may contain aggregates. Any column outside aggregate function must be part of a grouping expression. 

E.g: `select sum(a) from t group by b having c > 1` Above query is invalid, because `c` is not a grouping expression.

Current commit redesigns distributed INSERT command. Previously we
dispatched insert SQL command to the storages. If INSERT could be
done locally (without building a virtual table on the router) we
used SQL "bucket_id(<string>)" function to recalculate buckets
on the starage via SQL.

This approach had the main disadvantage - it worked only with a
"bucket_id" SQL function that had a single argument as a parameter.
An attempt to support multiple parameters of different types (tuple
columns as we plan to implement for Picodata engine in future) faced
serious technical problems.
Also, the old implementation had performance issues:
- we created temporary spaces on the storage even for a single VALUE
  insertion
- we always dispatched VALUES to the storage to build a virtual table
- the resulting SQL was too verbose (as we produced a subquery under
  INSERT node)

It was desided to get rid of the old approach and migrate to non-SQL
insertion. It means that a new type of motion was introduled - local
segment. It allows us to build a virtual table on the storage in
Rust memory, then using space API transform and insert collected tuples
directly into the target space within a single transaction. Also we
can materailize VALUES (if they contain only constants) on the router
and get rid of the redundant network transmission over the network.

feat: add support of Exists and NonExists operator, fix bug with selecting columns from VALUES, grammar small fix

In case we had a join where one of tables under
Scan had bucket_id column in the middle (not in the end)
than we incorrectly updated position in ALL references in
join condition. The correct way is to update references only
for corresponding join child, which is done in this commit

Arseniy Volynets authored 1 year ago and Arseniy Volynets committed 1 year ago

- Because Unary is also a bool expression, we had
wrong conflict resolution for join (without single),
that didn't take into account that bool expression
may have Unary as one of the children.
- I also noticed a small bug for single distribution
in join, fixed it.