Arseniy Volynets authored 7 months ago and Dmitry Ivanov committed 3 months ago

- If you prepare and execute statement with params
in the projection and then unprepare the
statement, byte counter may show the wrong value
or even overflow.
- The problem is that when we compile sql
statement, we set parameter type to 'any'.
But when we execute the statement we set parameter
type to actual type. Then we use this type in
calculation of estimated of sql cache entry size.
This leads to different estimated sizes of cache
entry during prepare and during unprepare after
statement was executed
- Fix this by resetting type to 'any' after
executing the statement

NO_DOC=picodata internal patch
NO_CHANGELOG=picodata internal patch

Arseniy Volynets authored 1 year ago and Dmitry Ivanov committed 3 months ago

- Add a configurable non-negative
session parameter "sql_vdbe_max_steps"
-- max number of opcodes that Vdbe
is allowed to execute for sql query.

- Default value can be specified in box.cfg.
If not set via box.cfg, default value
is 45000. Value 0 means that no
checks for number of executed Vdbe
opcodes will be made.

- Add the third argument to box.execute
function, that allows to specify options
for query execution. The only option
supported: sql_vdbe_max_steps. Usage
example:

```
box.execute([[select * from t]], {}, {{sql_vdbe_max_steps = 1000}})
```

part of picodata/picodata/sbroad!461

NO_DOC=picodata internal patch
NO_CHANGELOG=picodata internal patch

Denis Smirnov authored 2 years ago and Dmitry Ivanov committed 3 months ago

Actually there is no reason to throw an error and make a user
manually recreate prepared statement when it expires. A much more
user friendly way is to recreate it under hood when statement's
schema version differs from the box one.

NO_DOC=refactoring
NO_TEST=refactoring
NO_CHANGELOG=refactoring

This patch introduces new keyword SEQSCAN and new restrictions on
SELECTs. These restrictions are disabled by default.

Closes #7747

@TarantoolBot document
Title: SEQSCAN

Now scanning SELECT will not run and will throw an error if the new
SEQSCAN keyword is not used for scanned spaces. This change only affects
SELECT and does not affect UPDATE and DELETE. A SELECT is recognized as
a scanning SELECT if `EXPLAIN QUERY PLAN SELECT ...` indicates that the
SELECT `scans` rather than `searches`.

For example, if we have spaces created with these queries:
```
CREATE TABLE t(i INT PRIMARY KEY, a INT);
CREATE TABLE s(i INT PRIMARY KEY, a INT);
```

Then these queries will throw an error:
```
SELECT * FROM t;
SELECT * FROM t WHERE a > 1;
SELECT * FROM t WHERE i + 1 = 5;
SELECT * FROM t, s;
SELECT * FROM t JOIN s;
```

And these will not:
```
SELECT * FROM t WHERE i > 1;
SELECT * FROM SEQSCAN t;
SELECT * FROM SEQSCAN t WHERE i + 1 = 5;
SELECT * FROM SEQSCAN t, SEQSCAN s;
SELECT * FROM SEQSCAN t JOIN SEQSCAN s;
```

Scanning can be allowed or disallowed by default. To do this, a new
session setting is introduced: `sql_seq_scan`. The default value for
setting is `true`, i.e. scanning is allowed. When set to `false`, the
scanning SELECTs will throw a `scanning is not allowed` error.

This patch forces SQL to create core foreign keys instead of SQL foreign
keys in SQL.

Part of #6986

NO_DOC=will be added later
NO_CHANGELOG=will be added later

This patch enables static and dynamic type check for functions SUBSTR(),
GROUP_CONCAT(), REPLACE(), TRIM(). All these functions afther this patch
will be able to return VARINARY value when VARBINARY arguments are given
instead of STRING arguments.

Closes #6105

Prior to this, the patch functions MIN(), MAX(), LEAST() and GREATEST()
showed SCALAR as result types in metadata. However, in reality, the type
of the result could be any scalar type. After this patch, the type of
the result will always be the same as the type in the metadata. Also,
for MIN() and MAX() functions, the type of the result will be the same
as the type of the argument. For the LEAST() and GREATEST() functions,
the result type will be the same as the type of the arguments if all
arguments are of the same type, or it will be NUMBER if all arguments
are of numeric types, or it will be SCALAR.

Part of #6105

This patch enables static and dynamic type checks for functions that do
not need any rework.

Part of #6105

This patch disallows arithmetic operations for SCALAR and NUMBER values.
It also corrects the description of the error that is generated when an
inappropriate value participates in an arithmetic operation.

Part of #6221

Name resulting columns generated by an expression or <VALUES>
construction by the "COLUMN_N" pattern.

Closes #3962

@TarantoolBot document
Title: Column naming in SQL

Now, every auto generated column is named by the "COLUMN_N"
pattern, where N is the number of generated column in a query
(starting  from 1). Auto generated column is a column in a query
result generated by an expression or a column from <VALUES>
construction.

Examples:
```
box.execute("VALUES(1, 2, 3);")
---
- metadata:
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: integer
  - name: COLUMN_3
    type: integer
  rows:
  - [1, 2, 3]
...
box.execute("SELECT * FROM (VALUES (1+1, 1+1));")
---
- metadata:
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: integer
  rows:
  - [2, 2]
...
box.execute("SELECT 1+1, 1+1;")
---
- metadata:
  - name: COLUMN_1
    type: integer
  - name: COLUMN_2
    type: integer
  rows:
  - [2, 2]
...
```

Here, the expression "mycol + 1" generates a new column, so that
it is the first auto generated resulting column will be named as
"COLUMN_1".
```
tarantool> CREATE TABLE test (mycol INT PRIMARY KEY);
---
- row_count: 1
...

tarantool> SELECT mycol, mycol + 1 FROM test;
---
- metadata:
  - name: MYCOL
    type: integer
  - name: COLUMN_1
    type: integer
  rows: []
...
```
Note that you can use generated names already within the query,
e.g. in <ORDER BY> clause.
```
tarantool> SELECT mycol, mycol + 1 FROM test ORDER BY column_1;
---
- metadata:
  - name: MYCOL
    type: integer
  - name: COLUMN_1
    type: integer
  rows: []
...
```

It should also be noted that if you use column names similar to
the "COLUMN_N" pattern, you can get the same names as a result:

```
tarantool> CREATE TABLE test (column_1 SCALAR PRIMARY KEY);
---
- row_count: 1
...

tarantool> INSERT INTO test VALUES(1);
---
- row_count: 1
...

tarantool> SELECT column_1, column_1 + 1 FROM test;
---
- metadata:
  - name: COLUMN_1
    type: scalar
  - name: COLUMN_1
    type: scalar
  rows:
  - [1, 2]
...
```

Before this patch prepared statements didn't reset bound values after
its execution. As a result, if during next execution cycle not all
parameters were provided, cached values would appear. For instance:

prep = box.prepare('select :a, :b, :c')
prep:execute({{[':a'] = 1}, {[':b'] = 2}, {[':c'] = 3}}
-- [1, 2, 3]
prep:execute({{[':a'] = 1}, {[':b'] = 2}})
-- [1, 2, 3]

However, expected result for the last query should be [1, 2, NULL].
Let's fix it and always reset all binding values before next execution.

Closes #4825

Names of bindings are stored in the array indexed from 1 (see struct
Vdbe->pVList). So to get name of i-th values to be bound, one should
call sqlVListNumToName(list, i+1) not sqlVListNumToName(list, i).
For this reason, names of binding parameters returned in meta-information
in response to :prepare() call are shifted by one. Let's fix it and
calculate position of binding parameter taking into consideration
1-based indexing.

Closes #4760

SQL prepared statement cache is implemented as two data structures: hash
table <stmt_id : pointer-to-metadata> and GC queue. The latter is
required to avoid workload spikes on session's disconnect: instead of
cleaning up memory for all session-local prepared statements, prepared
statements to be deleted are moved to GC queue. When memory limit for PS
is reached, all elements from queue are removed at once. If statement
traps to the GC queue it is assumed to be already dead. Accidentally,
change of occupied by PS cache takes place only after GC queue clean-up,
so correct size of PS cache is displayed only after GC cycles. Let's fix
this and account PS cache size change right after entry removal (i.e. at
the moment PS gets into GC queue).

To track current memory occupied by prepared statements and number of
them, let's extend box.info submodule with .sql statistics: now it
contains current total size of prepared statements and their count.

@TarantoolBot document
Title: Prepared statements in SQL

Now it is possible to prepare (i.e. compile into byte-code and save to
the cache) statement and execute it several times. Mechanism is similar
to ones in other DBs. Prepared statement is identified by numeric
ID, which are returned alongside with prepared statement handle.
Note that they are not sequential and represent value of hash function
applied to the string containing original SQL request.
Prepared statement holder is shared among all sessions. However, session
has access only to statements which have been prepared in scope of it.
There's no eviction policy like in any cache; to remove statement from
holder explicit unprepare request is required. Alternatively, session's
disconnect also removes statements from holder.
Several sessions can share one prepared statement, which will be
destroyed when all related sessions are disconnected or send unprepare
request. Memory limit for prepared statements is adjusted by
box.cfg{sql_cache_size} handle (can be set dynamically;

Any DDL operation leads to expiration of all prepared statements: they
should be manually removed or re-prepared.
Prepared statements are available in local mode (i.e. via box.prepare()
function) and are supported in IProto protocol. In the latter case
next IProto keys are used to make up/receive requests/responses:
IPROTO_PREPARE - new IProto command; key is 0x13. It can be sent with
one of two mandatory keys: IPROTO_SQL_TEXT (0x40 and assumes string value)
or IPROTO_STMT_ID (0x43 and assumes integer value). Depending on body it
means to prepare or unprepare SQL statement: IPROTO_SQL_TEXT implies prepare
request, meanwhile IPROTO_STMT_ID - unprepare;
IPROTO_BIND_METADATA (0x33 and contains parameters metadata of type map)
and IPROTO_BIND_COUNT (0x34 and corresponds to the count of parameters to
be bound) are response keys. They are mandatory members of result of
IPROTO_PREPARE execution.

To track statistics of used memory and number of currently prepared
statements, box.info is extended with SQL statistics:

box.info:sql().cache.stmt_count - number of prepared statements;
box.info:sql().cache.size - size of occupied by prepared statements memory.

Typical workflow with prepared statements is following:

s = box.prepare("SELECT * FROM t WHERE id = ?;")
s:execute({1}) or box.execute(s.sql_str, {1})
s:execute({2}) or box.execute(s.sql_str, {2})
s:unprepare() or box.unprepare(s.query_id)

Structure of object is following (member : type):

- stmt_id: integer
  execute: function
  params: map [name : string, type : integer]
  unprepare: function
  metadata: map [name : string, type : integer]
  param_count: integer
...

In terms of remote connection:

cn = netbox:connect(addr)
s = cn:prepare("SELECT * FROM t WHERE id = ?;")
cn:execute(s.sql_str, {1})
cn:unprepare(s.query_id)

Closes #2592

This patch introduces support of prepared statements in IProto
protocol. To achieve this new IProto command is added - IPROTO_PREPARE
(key is 0x13). It is sent with one of two mandatory keys:
IPROTO_SQL_TEXT (0x40 and assumes string value) or IPROTO_STMT_ID (0x43
and assumes integer value). Depending on body it means to prepare or
unprepare SQL statement: IPROTO_SQL_TEXT implies prepare request,
meanwhile IPROTO_STMT_ID - unprepare.  Also to reply on PREPARE request a
few response keys are added: IPROTO_BIND_METADATA (0x33 and contains
parameters metadata of type map) and IPROTO_BIND_COUNT (0x34 and
corresponds to the count of parameters to be bound).

Part of #2592

This patch introduces local prepared statements. Support of prepared
statements in IProto protocol and netbox is added in the next patch.

Prepared statement is an opaque instance of SQL Virtual Machine. It can
be executed several times without necessity of query recompilation. To
achieve this one can use box.prepare(...) function. It takes string of
SQL query to be prepared; returns extended set of meta-information
including statement's ID, parameter's types and names, types and names
of columns of the resulting set, count of parameters to be bound.  Lua
object representing result of :prepare() invocation also features two
methods - :execute() and :unprepare(). They correspond to
box.execute(stmt.stmt_id) and box.unprepare(stmt.stmt_id), i.e.
automatically substitute string of prepared statement to be executed.
Statements are held in prepared statement cache - for details see
previous commit.  After schema changes all prepared statement located in
cache are considered to be expired - they must be re-prepared by
separate :prepare() call (or be invalidated with :unrepare()).

Two sessions can share one prepared statements. But in the current
implementation if statement is executed by one session, another is
not able to use it and will compile it from scratch and than execute.

SQL cache memory limit is regulated by box{sql_cache_size} which can be
set dynamically. However, it can be set to the value which is less than
the size of current free space in cache (since otherwise some statements
can disappear from cache).

Part of #2592