[Tarantool-patches] [PATCH v2 16/16] netbox: introduce prepared statements

[Nikita Pettik <korablev@tarantool.org>]

This patch introduces support of prepared statements in IProto
protocol. To achieve this new IProto command is added - IPROTO_PREPARE.
It is sent with IPROTO_SQL_TEXT key and it means to prepare SQL statement
(for details see previous commit). Also to reply on PREPARE request a few
response keys are added: IPROTO_BIND_METADATA (contains parameters
metadata) and IPROTO_BIND_COUNT (count of parameters to be bound).

Closes #2592

@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 string
containing original SQL request. Prepared statement cache is global and
follows LRU eviction policy: when there's no place for another one
statement, the statement that is least recently used (prepared) is
removed. Cache size is adjusted by box.cfg{sql_cache_size} variable
(can be set dynamically; in case size is reduced some statements may be
erased from cache). Note that any DDL operation leads to expiration of all
prepared statements: they are recompiled on demand. To erase whole cache
one can set its size to 0. Prepared statements are available in local mode
(i.e. via box.prepare() function) and are supported in IProto protocol.
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})

In terms of remote connection:

cn = netbox:connect(addr)
s = cn:prepare("SELECT * FROM t WHERE id = ?;")
cn:execute(s.sql_str, {1})
---
 src/box/execute.c          |  88 +++++++++++++++++++++++++++++++++++++
 src/box/iproto.cc          |  25 ++++++++---
 src/box/iproto_constants.c |   6 ++-
 src/box/iproto_constants.h |   4 ++
 src/box/lua/net_box.c      |  79 +++++++++++++++++++++++++++++++++
 src/box/lua/net_box.lua    |  13 ++++++
 src/box/xrow.c             |   2 +-
 src/box/xrow.h             |   2 +-
 test/box/misc.result       |   1 +
 test/sql/engine.cfg        |   1 +
 test/sql/prepared.result   | 106 ++++++++++++++++++++++++++-------------------
 test/sql/prepared.test.lua |  61 +++++++++++++++++++-------
 12 files changed, 320 insertions(+), 68 deletions(-)

diff --git a/src/box/execute.c b/src/box/execute.c
index 2b5a9ba90..13e5a6ba1 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -326,6 +326,67 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
 	return 0;
 }
 
+static inline int
+sql_get_params_metadata(struct sql_stmt *stmt, struct obuf *out)
+{
+	int bind_count = sql_bind_parameter_count(stmt);
+	int size = mp_sizeof_uint(IPROTO_BIND_METADATA) +
+		   mp_sizeof_array(bind_count);
+	char *pos = (char *) obuf_alloc(out, size);
+	if (pos == NULL) {
+		diag_set(OutOfMemory, size, "obuf_alloc", "pos");
+		return -1;
+	}
+	pos = mp_encode_uint(pos, IPROTO_BIND_METADATA);
+	pos = mp_encode_array(pos, bind_count);
+	for (int i = 0; i < bind_count; ++i) {
+		size_t size = mp_sizeof_map(2) +
+			      mp_sizeof_uint(IPROTO_FIELD_NAME) +
+			      mp_sizeof_uint(IPROTO_FIELD_TYPE);
+		const char *name = sql_bind_parameter_name(stmt, i);
+		if (name == NULL)
+			name = "?";
+		const char *type = "ANY";
+		size += mp_sizeof_str(strlen(name));
+		size += mp_sizeof_str(strlen(type));
+		char *pos = (char *) obuf_alloc(out, size);
+		if (pos == NULL) {
+			diag_set(OutOfMemory, size, "obuf_alloc", "pos");
+			return -1;
+		}
+		pos = mp_encode_map(pos, 2);
+		pos = mp_encode_uint(pos, IPROTO_FIELD_NAME);
+		pos = mp_encode_str(pos, name, strlen(name));
+		pos = mp_encode_uint(pos, IPROTO_FIELD_TYPE);
+		pos = mp_encode_str(pos, type, strlen(type));
+	}
+	return 0;
+}
+
+static int
+sql_get_prepare_common_keys(struct sql_stmt *stmt, struct obuf *out, int keys,
+			    const char *sql_str)
+{
+	int size = mp_sizeof_map(keys) +
+		   mp_sizeof_uint(IPROTO_SQL_TEXT) +
+		   mp_sizeof_str(strlen(sql_str)) +
+		   mp_sizeof_uint(IPROTO_BIND_COUNT) +
+		   mp_sizeof_uint(sql_bind_parameter_count(stmt));
+	char *pos = (char *) obuf_alloc(out, size);
+	if (pos == NULL) {
+		diag_set(OutOfMemory, size, "obuf_alloc", "pos");
+		return -1;
+	}
+	pos = mp_encode_map(pos, keys);
+	pos = mp_encode_uint(pos, IPROTO_SQL_TEXT);
+	pos = mp_encode_str(pos, sql_str, strlen(sql_str));
+	pos = mp_encode_uint(pos, IPROTO_BIND_COUNT);
+	pos = mp_encode_uint(pos, sql_bind_parameter_count(stmt));
+	if (sql_get_params_metadata(stmt, out) != 0)
+		return -1;
+	return 0;
+}
+
 static int
 port_sql_dump_msgpack(struct port *port, struct obuf *out)
 {
@@ -407,6 +468,33 @@ port_sql_dump_msgpack(struct port *port, struct obuf *out)
 		}
 		break;
 	}
+	case DQL_PREPARE: {
+		/* Format is following:
+		 * query_id,
+		 * param_count,
+		 * params {name, type},
+		 * metadata {name, type}
+		 */
+		int keys = 4;
+		if (sql_get_prepare_common_keys(stmt, out, keys,
+						sql_stmt_query_str(stmt)) != 0)
+			return -1;
+		if (sql_get_metadata(stmt, out, sql_column_count(stmt)) != 0)
+			return -1;
+		break;
+	}
+	case DML_PREPARE: {
+		/* Format is following:
+		 * query_id,
+		 * param_count,
+		 * params {name, type},
+		 */
+		int keys = 3;
+		if (sql_get_prepare_common_keys(stmt, out, keys,
+						sql_stmt_query_str(stmt)) != 0)
+			return -1;
+		break;
+		}
 	default: unreachable();
 	}
 	return 0;
diff --git a/src/box/iproto.cc b/src/box/iproto.cc
index 34c8f469a..a52c76961 100644
--- a/src/box/iproto.cc
+++ b/src/box/iproto.cc
@@ -178,7 +178,7 @@ struct iproto_msg
 		struct call_request call;
 		/** Authentication request. */
 		struct auth_request auth;
-		/* SQL request, if this is the EXECUTE request. */
+		/* SQL request, if this is the EXECUTE/PREPARE request. */
 		struct sql_request sql;
 		/** In case of iproto parse error, saved diagnostics. */
 		struct diag diag;
@@ -1155,6 +1155,7 @@ static const struct cmsg_hop *dml_route[IPROTO_TYPE_STAT_MAX] = {
 	call_route,                             /* IPROTO_CALL */
 	sql_route,                              /* IPROTO_EXECUTE */
 	NULL,                                   /* IPROTO_NOP */
+	sql_route,                              /* IPROTO_PREPARE */
 };
 
 static const struct cmsg_hop join_route[] = {
@@ -1210,6 +1211,7 @@ iproto_msg_decode(struct iproto_msg *msg, const char **pos, const char *reqend,
 		cmsg_init(&msg->base, call_route);
 		break;
 	case IPROTO_EXECUTE:
+	case IPROTO_PREPARE:
 		if (xrow_decode_sql(&msg->header, &msg->sql) != 0)
 			goto error;
 		cmsg_init(&msg->base, sql_route);
@@ -1653,7 +1655,8 @@ tx_process_sql(struct cmsg *m)
 
 	if (tx_check_schema(msg->header.schema_version))
 		goto error;
-	assert(msg->header.type == IPROTO_EXECUTE);
+	assert(msg->header.type == IPROTO_EXECUTE ||
+	       msg->header.type == IPROTO_PREPARE);
 	tx_inject_delay();
 	if (msg->sql.bind != NULL) {
 		bind_count = sql_bind_list_decode(msg->sql.bind, &bind);
@@ -1662,9 +1665,20 @@ tx_process_sql(struct cmsg *m)
 	}
 	sql = msg->sql.sql_text;
 	sql = mp_decode_str(&sql, &len);
-	if (sql_prepare_and_execute(sql, len, bind, bind_count, &port,
-				    &fiber()->gc) != 0)
-		goto error;
+	if (msg->header.type == IPROTO_EXECUTE) {
+		sql = msg->sql.sql_text;
+		sql = mp_decode_str(&sql, &len);
+		if (sql_prepare_and_execute(sql, len, bind, bind_count,
+					    &port, &fiber()->gc) != 0)
+			goto error;
+	} else {
+		/* IPROTO_PREPARE */
+		sql = msg->sql.sql_text;
+		sql = mp_decode_str(&sql, &len);
+		if (sql_prepare(sql, len, &port) != 0)
+			goto error;
+	}
+
 	/*
 	 * Take an obuf only after execute(). Else the buffer can
 	 * become out of date during yield.
@@ -1676,6 +1690,7 @@ tx_process_sql(struct cmsg *m)
 		port_destroy(&port);
 		goto error;
 	}
+	/* Nothing to dump in case of UNPREPARE request. */
 	if (port_dump_msgpack(&port, out) != 0) {
 		port_destroy(&port);
 		obuf_rollback_to_svp(out, &header_svp);
diff --git a/src/box/iproto_constants.c b/src/box/iproto_constants.c
index 09ded1ecb..6c4dc4f8c 100644
--- a/src/box/iproto_constants.c
+++ b/src/box/iproto_constants.c
@@ -107,6 +107,7 @@ const char *iproto_type_strs[] =
 	"CALL",
 	"EXECUTE",
 	NULL, /* NOP */
+	"PREPARE",
 };
 
 #define bit(c) (1ULL<<IPROTO_##c)
@@ -124,6 +125,7 @@ const uint64_t iproto_body_key_map[IPROTO_TYPE_STAT_MAX] = {
 	0,                                                     /* CALL */
 	0,                                                     /* EXECUTE */
 	0,                                                     /* NOP */
+	0,                                                     /* PREPARE */
 };
 #undef bit
 
@@ -179,8 +181,8 @@ const char *iproto_key_strs[IPROTO_KEY_MAX] = {
 	"data",             /* 0x30 */
 	"error",            /* 0x31 */
 	"metadata",         /* 0x32 */
-	NULL,               /* 0x33 */
-	NULL,               /* 0x34 */
+	"bind meta",        /* 0x33 */
+	"bind count",       /* 0x34 */
 	NULL,               /* 0x35 */
 	NULL,               /* 0x36 */
 	NULL,               /* 0x37 */
diff --git a/src/box/iproto_constants.h b/src/box/iproto_constants.h
index 5e8a7d483..080886ab0 100644
--- a/src/box/iproto_constants.h
+++ b/src/box/iproto_constants.h
@@ -110,6 +110,8 @@ enum iproto_key {
 	 * ]
 	 */
 	IPROTO_METADATA = 0x32,
+	IPROTO_BIND_METADATA = 0x33,
+	IPROTO_BIND_COUNT = 0x34,
 
 	/* Leave a gap between response keys and SQL keys. */
 	IPROTO_SQL_TEXT = 0x40,
@@ -203,6 +205,8 @@ enum iproto_type {
 	IPROTO_EXECUTE = 11,
 	/** No operation. Treated as DML, used to bump LSN. */
 	IPROTO_NOP = 12,
+	/** Prepare SQL statement. */
+	IPROTO_PREPARE = 13,
 	/** The maximum typecode used for box.stat() */
 	IPROTO_TYPE_STAT_MAX,
 
diff --git a/src/box/lua/net_box.c b/src/box/lua/net_box.c
index 001af95dc..03b585f36 100644
--- a/src/box/lua/net_box.c
+++ b/src/box/lua/net_box.c
@@ -585,6 +585,26 @@ netbox_encode_execute(lua_State *L)
 	return 0;
 }
 
+static int
+netbox_encode_prepare(lua_State *L)
+{
+	if (lua_gettop(L) < 3)
+		return luaL_error(L, "Usage: netbox.encode_prepare(ibuf, "\
+				     "sync, query)");
+	struct mpstream stream;
+	size_t svp = netbox_prepare_request(L, &stream, IPROTO_PREPARE);
+
+	mpstream_encode_map(&stream, 1);
+
+	size_t len;
+	const char *query = lua_tolstring(L, 3, &len);
+	mpstream_encode_uint(&stream, IPROTO_SQL_TEXT);
+	mpstream_encode_strn(&stream, query, len);
+
+	netbox_encode_request(&stream, svp);
+	return 0;
+}
+
 /**
  * Decode IPROTO_DATA into tuples array.
  * @param L Lua stack to push result on.
@@ -752,6 +772,63 @@ netbox_decode_execute(struct lua_State *L)
 	return 2;
 }
 
+static int
+netbox_decode_prepare(struct lua_State *L)
+{
+	uint32_t ctypeid;
+	const char *data = *(const char **)luaL_checkcdata(L, 1, &ctypeid);
+	assert(mp_typeof(*data) == MP_MAP);
+	uint32_t map_size = mp_decode_map(&data);
+	int query_id_idx = 0, meta_idx = 0, bind_meta_idx = 0,
+	    bind_count_idx = 0;
+	const char *sql_str = 0;
+	for (uint32_t i = 0; i < map_size; ++i) {
+		uint32_t key = mp_decode_uint(&data);
+		switch(key) {
+		case IPROTO_SQL_TEXT: {
+			uint32_t len = 0;
+			sql_str = mp_decode_str(&data, &len);
+			lua_pushlstring(L, sql_str, len);
+			query_id_idx = i - map_size;
+			break;
+		}
+		case IPROTO_METADATA: {
+			netbox_decode_metadata(L, &data);
+			meta_idx = i - map_size;
+			break;
+		}
+		case IPROTO_BIND_METADATA: {
+			netbox_decode_metadata(L, &data);
+			bind_meta_idx = i - map_size;
+			break;
+		}
+		default: {
+			assert(key == IPROTO_BIND_COUNT);
+			uint32_t bind_count = mp_decode_uint(&data);
+			luaL_pushuint64(L, bind_count);
+			bind_count_idx = i - map_size;
+			break;
+		}}
+	}
+	/* These fields must be present in response. */
+	assert(query_id_idx * bind_meta_idx * bind_count_idx != 0);
+	/* General meta is presented only in DQL responses. */
+	lua_createtable(L, 0, meta_idx != 0 ? 4 : 3);
+	lua_pushvalue(L, query_id_idx - 1);
+	lua_setfield(L, -2, "sql_str");
+	lua_pushvalue(L, bind_count_idx - 1);
+	lua_setfield(L, -2, "param_count");
+	lua_pushvalue(L, bind_meta_idx - 1);
+	lua_setfield(L, -2, "params");
+	if (meta_idx != 0) {
+		lua_pushvalue(L, meta_idx - 1);
+		lua_setfield(L, -2, "metadata");
+	}
+
+	*(const char **)luaL_pushcdata(L, ctypeid) = data;
+	return 2;
+}
+
 int
 luaopen_net_box(struct lua_State *L)
 {
@@ -767,11 +844,13 @@ luaopen_net_box(struct lua_State *L)
 		{ "encode_update",  netbox_encode_update },
 		{ "encode_upsert",  netbox_encode_upsert },
 		{ "encode_execute", netbox_encode_execute},
+		{ "encode_prepare", netbox_encode_prepare},
 		{ "encode_auth",    netbox_encode_auth },
 		{ "decode_greeting",netbox_decode_greeting },
 		{ "communicate",    netbox_communicate },
 		{ "decode_select",  netbox_decode_select },
 		{ "decode_execute", netbox_decode_execute },
+		{ "decode_prepare", netbox_decode_prepare },
 		{ NULL, NULL}
 	};
 	/* luaL_register_module polutes _G */
diff --git a/src/box/lua/net_box.lua b/src/box/lua/net_box.lua
index c2e1bb9c4..b1301cd96 100644
--- a/src/box/lua/net_box.lua
+++ b/src/box/lua/net_box.lua
@@ -104,6 +104,7 @@ local method_encoder = {
     upsert  = internal.encode_upsert,
     select  = internal.encode_select,
     execute = internal.encode_execute,
+    prepare = internal.encode_prepare,
     get     = internal.encode_select,
     min     = internal.encode_select,
     max     = internal.encode_select,
@@ -128,6 +129,7 @@ local method_decoder = {
     upsert  = decode_nil,
     select  = internal.decode_select,
     execute = internal.decode_execute,
+    prepare = internal.decode_prepare,
     get     = decode_get,
     min     = decode_get,
     max     = decode_get,
@@ -1197,6 +1199,17 @@ function remote_methods:execute(query, parameters, sql_opts, netbox_opts)
                          sql_opts or {})
 end
 
+function remote_methods:prepare(query, parameters, sql_opts, netbox_opts)
+    check_remote_arg(self, "prepare")
+    if type(query) ~= "string" then
+        box.error(box.error.SQL_PREPARE, "expected string as SQL statement")
+    end
+    if sql_opts ~= nil then
+        box.error(box.error.UNSUPPORTED, "prepare", "options")
+    end
+    return self:_request('prepare', netbox_opts, nil, query)
+end
+
 function remote_methods:wait_state(state, timeout)
     check_remote_arg(self, 'wait_state')
     if timeout == nil then
diff --git a/src/box/xrow.c b/src/box/xrow.c
index 18bf08971..c27632617 100644
--- a/src/box/xrow.c
+++ b/src/box/xrow.c
@@ -591,7 +591,7 @@ error:
 		else
 			request->sql_text = value;
 	}
-	if (request->sql_text == NULL) {
+	if (request->sql_text == NULL ) {
 		xrow_on_decode_err(row->body[0].iov_base, end, ER_MISSING_REQUEST_FIELD,
 			 iproto_key_name(IPROTO_SQL_TEXT));
 		return -1;
diff --git a/src/box/xrow.h b/src/box/xrow.h
index 60def2d3c..bcb96f079 100644
--- a/src/box/xrow.h
+++ b/src/box/xrow.h
@@ -526,7 +526,7 @@ int
 iproto_reply_error(struct obuf *out, const struct error *e, uint64_t sync,
 		   uint32_t schema_version);
 
-/** EXECUTE request. */
+/** EXECUTE/PREPARE request. */
 struct sql_request {
 	/** SQL statement text. */
 	const char *sql_text;
diff --git a/test/box/misc.result b/test/box/misc.result
index ce66e1bfe..8bb93b16c 100644
--- a/test/box/misc.result
+++ b/test/box/misc.result
@@ -230,6 +230,7 @@ t;
   - EVAL
   - CALL
   - ERROR
+  - PREPARE
   - REPLACE
   - UPSERT
   - AUTH
diff --git a/test/sql/engine.cfg b/test/sql/engine.cfg
index a1b4b0fc5..e38bec24e 100644
--- a/test/sql/engine.cfg
+++ b/test/sql/engine.cfg
@@ -10,6 +10,7 @@
         "local": {"remote": "false"}
     },
     "prepared.test.lua": {
+        "remote": {"remote": "true"},
         "local": {"remote": "false"}
     },
     "*": {
diff --git a/test/sql/prepared.result b/test/sql/prepared.result
index 3321f41f3..4a92f4d97 100644
--- a/test/sql/prepared.result
+++ b/test/sql/prepared.result
@@ -12,25 +12,41 @@ fiber = require('fiber')
 -- Wrappers to make remote and local execution interface return
 -- same result pattern.
 --
+is_remote = test_run:get_cfg('remote') == 'true'
+ | ---
+ | ...
+execute = nil
+ | ---
+ | ...
+prepare = nil
+ | ---
+ | ...
+
 test_run:cmd("setopt delimiter ';'")
  | ---
  | - true
  | ...
-execute = function(...)
-    local res, err = box.execute(...)
-    if err ~= nil then
-        error(err)
+if is_remote then
+    box.schema.user.grant('guest','read, write, execute', 'universe')
+    box.schema.user.grant('guest', 'create', 'space')
+    cn = remote.connect(box.cfg.listen)
+    execute = function(...) return cn:execute(...) end
+    prepare = function(...) return cn:prepare(...) end
+else
+    execute = function(...)
+        local res, err = box.execute(...)
+        if err ~= nil then
+            error(err)
+        end
+        return res
     end
-    return res
-end;
- | ---
- | ...
-prepare = function(...)
-    local res, err = box.prepare(...)
-    if err ~= nil then
-        error(err)
+    prepare = function(...)
+        local res, err = box.prepare(...)
+        if err ~= nil then
+            error(err)
+        end
+        return res
     end
-    return res
 end;
  | ---
  | ...
@@ -119,28 +135,22 @@ execute(s.sql_str, {1, 3})
  |     type: string
  |   rows: []
  | ...
-s:execute({1, 2})
+
+test_run:cmd("setopt delimiter ';'")
  | ---
- | - metadata:
- |   - name: ID
- |     type: integer
- |   - name: A
- |     type: number
- |   - name: B
- |     type: string
- |   rows:
- |   - [1, 2, '3']
+ | - true
  | ...
-s:execute({1, 3})
+if not is_remote then
+    res = s:execute({1, 2})
+    assert(res ~= nil)
+    res = s:execute({1, 3})
+    assert(res ~= nil)
+end;
  | ---
- | - metadata:
- |   - name: ID
- |     type: integer
- |   - name: A
- |     type: number
- |   - name: B
- |     type: string
- |   rows: []
+ | ...
+test_run:cmd("setopt delimiter ''");
+ | ---
+ | - true
  | ...
 
 -- Test preparation of different types of queries.
@@ -287,6 +297,7 @@ space:replace{7, 8.5, '9'}
 s = prepare("SELECT a FROM test WHERE b = '3';")
  | ---
  | ...
+
 execute(s.sql_str)
  | ---
  | - metadata:
@@ -303,21 +314,21 @@ execute(s.sql_str)
  |   rows:
  |   - [2]
  | ...
-s:execute()
+test_run:cmd("setopt delimiter ';'")
  | ---
- | - metadata:
- |   - name: A
- |     type: number
- |   rows:
- |   - [2]
+ | - true
  | ...
-s:execute()
+if not is_remote then
+    res = s:execute()
+    assert(res ~= nil)
+    res = s:execute()
+    assert(res ~= nil)
+end;
  | ---
- | - metadata:
- |   - name: A
- |     type: number
- |   rows:
- |   - [2]
+ | ...
+test_run:cmd("setopt delimiter ''");
+ | ---
+ | - true
  | ...
 
 s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
@@ -561,6 +572,13 @@ f2:join();
  | - true
  | ...
 
+if is_remote then
+    cn:close()
+    box.schema.user.revoke('guest', 'read, write, execute', 'universe')
+    box.schema.user.revoke('guest', 'create', 'space')
+end;
+ | ---
+ | ...
 test_run:cmd("setopt delimiter ''");
  | ---
  | - true
diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua
index 24e49832f..de667d6d5 100644
--- a/test/sql/prepared.test.lua
+++ b/test/sql/prepared.test.lua
@@ -5,20 +5,32 @@ fiber = require('fiber')
 -- Wrappers to make remote and local execution interface return
 -- same result pattern.
 --
+is_remote = test_run:get_cfg('remote') == 'true'
+execute = nil
+prepare = nil
+
 test_run:cmd("setopt delimiter ';'")
-execute = function(...)
-    local res, err = box.execute(...)
-    if err ~= nil then
-        error(err)
+if is_remote then
+    box.schema.user.grant('guest','read, write, execute', 'universe')
+    box.schema.user.grant('guest', 'create', 'space')
+    cn = remote.connect(box.cfg.listen)
+    execute = function(...) return cn:execute(...) end
+    prepare = function(...) return cn:prepare(...) end
+else
+    execute = function(...)
+        local res, err = box.execute(...)
+        if err ~= nil then
+            error(err)
+        end
+        return res
     end
-    return res
-end;
-prepare = function(...)
-    local res, err = box.prepare(...)
-    if err ~= nil then
-        error(err)
+    prepare = function(...)
+        local res, err = box.prepare(...)
+        if err ~= nil then
+            error(err)
+        end
+        return res
     end
-    return res
 end;
 
 test_run:cmd("setopt delimiter ''");
@@ -39,8 +51,15 @@ s.params
 s.param_count
 execute(s.sql_str, {1, 2})
 execute(s.sql_str, {1, 3})
-s:execute({1, 2})
-s:execute({1, 3})
+
+test_run:cmd("setopt delimiter ';'")
+if not is_remote then
+    res = s:execute({1, 2})
+    assert(res ~= nil)
+    res = s:execute({1, 3})
+    assert(res ~= nil)
+end;
+test_run:cmd("setopt delimiter ''");
 
 -- Test preparation of different types of queries.
 -- Let's start from DDL. It doesn't make much sense since
@@ -93,10 +112,17 @@ space:replace{1, 2, '3'}
 space:replace{4, 5, '6'}
 space:replace{7, 8.5, '9'}
 s = prepare("SELECT a FROM test WHERE b = '3';")
+
 execute(s.sql_str)
 execute(s.sql_str)
-s:execute()
-s:execute()
+test_run:cmd("setopt delimiter ';'")
+if not is_remote then
+    res = s:execute()
+    assert(res ~= nil)
+    res = s:execute()
+    assert(res ~= nil)
+end;
+test_run:cmd("setopt delimiter ''");
 
 s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
 execute(s.sql_str)
@@ -190,6 +216,11 @@ f2:set_joinable(true)
 f1:join();
 f2:join();
 
+if is_remote then
+    cn:close()
+    box.schema.user.revoke('guest', 'read, write, execute', 'universe')
+    box.schema.user.revoke('guest', 'create', 'space')
+end;
 test_run:cmd("setopt delimiter ''");
 
 box.space.TEST:drop()
-- 
2.15.1