Tarantool development patches archive
 help / color / mirror / Atom feed
* [tarantool-patches] [PATCH v1 1/1] sql: return a tuple result from SQL
@ 2018-08-21 15:00 Kirill Shcherbatov
  2018-08-21 15:28 ` [tarantool-patches] " Vladislav Shpilevoy
  2018-08-27 11:02 ` n.pettik
  0 siblings, 2 replies; 3+ messages in thread
From: Kirill Shcherbatov @ 2018-08-21 15:00 UTC (permalink / raw)
  To: tarantool-patches; +Cc: korablev, Kirill Shcherbatov

@TarantoolBot document
Title: Return a tuple result from SQL
SQL insert/update/delete requests would return
table with processed tuples. It is default behavior.

tarantool> box.sql.execute("CREATE TABLE t (s1 INT, s2 INT,
                         s3 INT, s4 INT PRIMARY KEY);")
tarantool> t = box.sql.execute("INSERT INTO t VALUES (1,1,1,2),
                            (1,1,1,5),(1,1,1,6);")
- - [1, 1, 1, 2]
  - [1, 1, 1, 5]
  - [1, 1, 1, 6]

tarantool> t[3].S4
6

NetBox would no return tuple metadata:
tarantool> t_cn[1].S4
nul

Feature may be disabled with interactive_mode pragma:
tarantool> box.sql.execute("pragma interactive_mode=0;")

Closes #2370.
---
Branch: http://github.com/tarantool/tarantool/tree/kshch/gh-2370-return-tuple-result-from-sql
Issue: https://github.com/tarantool/tarantool/issues/2370

 src/box/execute.c                                  | 37 +++++++++----
 src/box/lua/sql.c                                  | 22 ++++++--
 src/box/sql.c                                      | 15 +++---
 src/box/sql/build.c                                |  2 +
 src/box/sql/delete.c                               |  7 +++
 src/box/sql/insert.c                               |  7 +++
 src/box/sql/legacy.c                               |  3 +-
 src/box/sql/main.c                                 |  3 ++
 src/box/sql/pragma.h                               |  5 ++
 src/box/sql/sqliteInt.h                            | 38 ++++++++++++-
 src/box/sql/tarantoolInt.h                         |  4 +-
 src/box/sql/vdbe.c                                 | 36 +++++++++++--
 src/box/sql/vdbe.h                                 |  3 ++
 src/box/sql/vdbeInt.h                              | 17 +++++-
 src/box/sql/vdbeapi.c                              | 29 ++++++----
 src/box/sql/vdbeaux.c                              |  6 +++
 test/box/sql-update-with-nested-select.result      | 33 ------------
 test/box/sql-update-with-nested-select.test.lua    | 25 ---------
 test/sql-tap/lua/sqltester.lua                     |  1 +
 test/sql/check-clear-ephemeral.result              |  3 ++
 test/sql/check-clear-ephemeral.test.lua            |  1 +
 test/sql/checks.result                             |  3 ++
 test/sql/checks.test.lua                           |  1 +
 test/sql/clear.result                              |  3 ++
 test/sql/clear.test.lua                            |  1 +
 test/sql/collation.result                          |  3 ++
 test/sql/collation.test.lua                        |  1 +
 test/sql/delete-multiple-idx.result                |  3 ++
 test/sql/delete-multiple-idx.test.lua              |  1 +
 test/sql/delete.result                             |  3 ++
 test/sql/delete.test.lua                           |  1 +
 test/sql/drop-index.result                         |  3 ++
 test/sql/drop-index.test.lua                       |  1 +
 test/sql/drop-table.result                         |  3 ++
 test/sql/drop-table.test.lua                       |  1 +
 test/sql/errinj.result                             |  7 ++-
 test/sql/errinj.test.lua                           |  1 +
 test/sql/foreign-keys.result                       |  3 ++
 test/sql/foreign-keys.test.lua                     |  1 +
 test/sql/gh-2347-max-int-literals.result           |  3 ++
 test/sql/gh-2347-max-int-literals.test.lua         |  1 +
 test/sql/gh-2929-primary-key.result                |  3 ++
 test/sql/gh-2929-primary-key.test.lua              |  1 +
 test/sql/gh-2981-check-autoinc.result              |  3 ++
 test/sql/gh-2981-check-autoinc.test.lua            |  1 +
 test/sql/gh-3199-no-mem-leaks.result               |  3 ++
 test/sql/gh-3199-no-mem-leaks.test.lua             |  1 +
 test/sql/gh2141-delete-trigger-drop-table.result   |  3 ++
 test/sql/gh2141-delete-trigger-drop-table.test.lua |  1 +
 test/sql/gh2251-multiple-update.result             |  3 ++
 test/sql/gh2251-multiple-update.test.lua           |  1 +
 test/sql/gh2483-remote-persistency-check.result    |  3 ++
 test/sql/gh2483-remote-persistency-check.test.lua  |  1 +
 .../gh2808-inline-unique-persistency-check.result  |  4 ++
 ...gh2808-inline-unique-persistency-check.test.lua |  1 +
 test/sql/icu-upper-lower.result                    |  3 ++
 test/sql/icu-upper-lower.test.lua                  |  2 +-
 test/sql/insert-unique.result                      |  3 ++
 test/sql/insert-unique.test.lua                    |  1 +
 test/sql/iproto.result                             | 48 +++++++++++++----
 test/sql/iproto.test.lua                           |  1 +
 test/sql/max-on-index.result                       |  3 ++
 test/sql/max-on-index.test.lua                     |  1 +
 test/sql/message-func-indexes.result               |  3 ++
 test/sql/message-func-indexes.test.lua             |  1 +
 test/sql/misc.result                               | 62 ++++++++++++++++++++++
 test/sql/misc.test.lua                             | 19 +++++++
 test/sql/on-conflict.result                        |  3 ++
 test/sql/on-conflict.test.lua                      |  1 +
 test/sql/persistency.result                        |  5 ++
 test/sql/persistency.test.lua                      |  1 +
 test/sql/savepoints.result                         |  3 ++
 test/sql/savepoints.test.lua                       |  1 +
 test/sql/select-null.result                        |  3 ++
 test/sql/select-null.test.lua                      |  1 +
 test/sql/sql-statN-index-drop.result               |  3 ++
 test/sql/sql-statN-index-drop.test.lua             |  1 +
 test/sql/sql-update-with-nested-select.result      | 36 +++++++++++++
 test/sql/sql-update-with-nested-select.test.lua    | 26 +++++++++
 test/sql/tokenizer.result                          |  3 ++
 test/sql/tokenizer.test.lua                        |  1 +
 test/sql/transition.result                         |  3 ++
 test/sql/transition.test.lua                       |  1 +
 test/sql/transitive-transactions.result            |  3 ++
 test/sql/transitive-transactions.test.lua          |  1 +
 test/sql/triggers.result                           |  3 ++
 test/sql/triggers.test.lua                         |  1 +
 test/sql/update-with-nested-select.result          |  3 ++
 test/sql/update-with-nested-select.test.lua        |  1 +
 test/sql/upgrade.result                            |  3 ++
 test/sql/upgrade.test.lua                          |  1 +
 test/sql/view.result                               |  3 ++
 test/sql/view.test.lua                             |  1 +
 test/sql/view_delayed_wal.result                   |  3 ++
 test/sql/view_delayed_wal.test.lua                 |  1 +
 95 files changed, 525 insertions(+), 108 deletions(-)
 delete mode 100644 test/box/sql-update-with-nested-select.result
 delete mode 100644 test/box/sql-update-with-nested-select.test.lua
 create mode 100644 test/sql/sql-update-with-nested-select.result
 create mode 100644 test/sql/sql-update-with-nested-select.test.lua

diff --git a/src/box/execute.c b/src/box/execute.c
index 24459b4..c41758e 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -555,20 +555,30 @@ sql_execute(sqlite3 *db, struct sqlite3_stmt *stmt, struct port *port,
 	    struct region *region)
 {
 	int rc, column_count = sqlite3_column_count(stmt);
-	if (column_count > 0) {
-		/* Either ROW or DONE or ERROR. */
-		while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
-			if (sql_row_to_port(stmt, column_count, region,
-					    port) != 0)
-				return -1;
+	if (column_count > 0 || sqlite3_tuple_result_require_flush(stmt)) {
+		/* Either ROW or TUPLE or DONE or ERROR. */
+		while (true) {
+			rc = sqlite3_step(stmt);
+			if (rc == SQLITE_ROW) {
+				if (sql_row_to_port(stmt, column_count, region,
+						    port) != 0)
+					return -1;
+			} else if (rc == SQLITE_TUPLE) {
+				struct tuple *tuple =
+					sqlite3_result_tuple(stmt);
+				if (tuple != NULL &&
+					port_tuple_add(port, tuple) != 0)
+					return -1;
+			} else {
+				break;
+			}
 		}
 		assert(rc == SQLITE_DONE || rc != SQLITE_OK);
 	} else {
-		/* No rows. Either DONE or ERROR. */
-		rc = sqlite3_step(stmt);
+		while ((rc = sqlite3_step(stmt)) == SQLITE_TUPLE);
 		assert(rc != SQLITE_ROW && rc != SQLITE_OK);
 	}
-	if (rc != SQLITE_DONE) {
+	if (rc != SQLITE_DONE && rc != SQLITE_TUPLE) {
 		diag_set(ClientError, ER_SQL_EXECUTE, sqlite3_errmsg(db));
 		return -1;
 	}
@@ -634,6 +644,15 @@ err:
 			/* Failed port dump destroyes the port. */
 			goto err;
 		}
+	} else if (sqlite3_tuple_result_require_flush(stmt)) {
+		if (iproto_reply_array_key(out, 0, IPROTO_METADATA) != 0)
+			goto err;
+		keys = 2;
+		if (iproto_reply_array_key(out, port_tuple->size,
+					   IPROTO_DATA) != 0)
+			goto err;
+		if (port_dump_msgpack_16(&response->port, out) < 0)
+			goto err;
 	} else {
 		keys = 1;
 		assert(port_tuple->size == 0);
diff --git a/src/box/lua/sql.c b/src/box/lua/sql.c
index 17e2694..c19d450 100644
--- a/src/box/lua/sql.c
+++ b/src/box/lua/sql.c
@@ -6,6 +6,7 @@
 #include "box/info.h"
 #include "lua/utils.h"
 #include "info.h"
+#include "tuple.h"
 
 static void
 lua_push_column_names(struct lua_State *L, struct sqlite3_stmt *stmt)
@@ -83,8 +84,9 @@ lua_sql_execute(struct lua_State *L)
 
 	int rc;
 	int retval_count;
-	if (sqlite3_column_count(stmt) == 0) {
-		while ((rc = sqlite3_step(stmt)) == SQLITE_ROW);
+	if (sqlite3_column_count(stmt) == 0 &&
+	    !sqlite3_tuple_result_require_flush(stmt)) {
+		while ((rc = sqlite3_step(stmt)) == SQLITE_TUPLE);
 		retval_count = 0;
 	} else {
 		lua_newtable(L);
@@ -94,9 +96,19 @@ lua_sql_execute(struct lua_State *L)
 		lua_rawseti(L, -2, 0);
 
 		int row_count = 0;
-		while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
-			lua_push_row(L, stmt);
-			lua_rawseti(L, -2, ++row_count);
+		while (true) {
+			rc = sqlite3_step(stmt);
+			if (rc == SQLITE_ROW) {
+				lua_push_row(L, stmt);
+				lua_rawseti(L, -2, ++row_count);
+			} else if (rc == SQLITE_TUPLE) {
+				struct tuple *tuple =
+					sqlite3_result_tuple(stmt);
+				luaT_pushtupleornil(L, tuple);
+				lua_rawseti(L, -2, ++row_count);
+			} else {
+				break;
+			}
 		}
 		retval_count = 1;
 	}
diff --git a/src/box/sql.c b/src/box/sql.c
index ae12cae..907dc86 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -65,7 +65,8 @@ static const uint32_t default_sql_flags = SQLITE_ShortColNames
 					  | SQLITE_EnableTrigger
 					  | SQLITE_AutoIndex
 					  | SQLITE_RecTriggers
-					  | SQLITE_ForeignKeys;
+					  | SQLITE_ForeignKeys
+					  | SQLITE_InteractiveMode;
 
 void
 sql_init()
@@ -469,7 +470,7 @@ int tarantoolSqlite3EphemeralDrop(BtCursor *pCur)
 
 static inline int
 insertOrReplace(struct space *space, const char *tuple, const char *tuple_end,
-		enum iproto_type type)
+		enum iproto_type type, struct tuple **result)
 {
 	assert(space != NULL);
 	struct request request;
@@ -479,20 +480,20 @@ insertOrReplace(struct space *space, const char *tuple, const char *tuple_end,
 	request.space_id = space->def->id;
 	request.type = type;
 	mp_tuple_assert(request.tuple, request.tuple_end);
-	int rc = box_process_rw(&request, space, NULL);
+	int rc = box_process_rw(&request, space, result);
 	return rc == 0 ? SQLITE_OK : SQL_TARANTOOL_INSERT_FAIL;
 }
 
 int tarantoolSqlite3Insert(struct space *space, const char *tuple,
-			   const char *tuple_end)
+			   const char *tuple_end, struct tuple **result)
 {
-	return insertOrReplace(space, tuple, tuple_end, IPROTO_INSERT);
+	return insertOrReplace(space, tuple, tuple_end, IPROTO_INSERT, result);
 }
 
 int tarantoolSqlite3Replace(struct space *space, const char *tuple,
-			    const char *tuple_end)
+			    const char *tuple_end, struct tuple **result)
 {
-	return insertOrReplace(space, tuple, tuple_end, IPROTO_REPLACE);
+	return insertOrReplace(space, tuple, tuple_end, IPROTO_REPLACE, result);
 }
 
 /*
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index dddeb12..76c683c 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -2013,7 +2013,9 @@ vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
 	 * On memory allocation error sql_table delete_from
 	 * releases memory for its own.
 	 */
+	bft is_interactive = parse->pVdbe->tuple_result_flush;
 	sql_table_delete_from(parse, src_list, where);
+	parse->pVdbe->tuple_result_flush = is_interactive;
 }
 
 /**
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index 0be6883..ea22b4c 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -34,6 +34,7 @@
 #include "box/schema.h"
 #include "sqliteInt.h"
 #include "tarantoolInt.h"
+#include "vdbeInt.h"
 
 struct Table *
 sql_list_lookup_table(struct Parse *parse, SrcList *src_list)
@@ -551,6 +552,12 @@ sql_generate_row_delete(struct Parse *parse, struct Table *table,
 		if (idx_noseek >= 0)
 			sqlite3VdbeAddOp1(v, OP_Delete, idx_noseek);
 
+		struct session *user_session = current_session();
+		if (user_session->sql_flags & SQLITE_InteractiveMode) {
+			sqlite3VdbeAddOp1(v, OP_ResultTuple, cursor);
+			v->tuple_result_flush = true;
+		}
+
 		if (mode == ONEPASS_MULTI)
 			p5 |= OPFLAG_SAVEPOSITION;
 		sqlite3VdbeChangeP5(v, p5);
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 13cb61e..0981bca 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -38,6 +38,7 @@
 #include "box/session.h"
 #include "box/schema.h"
 #include "bit/bit.h"
+#include "vdbeInt.h"
 
 /*
  * Generate code that will open pTab as cursor iCur.
@@ -1481,6 +1482,12 @@ vdbe_emit_insertion_completion(Vdbe *v, int cursor_id, int tuple_id,
 
 	sqlite3VdbeAddOp2(v, opcode, cursor_id, tuple_id);
 	sqlite3VdbeChangeP5(v, pik_flags);
+
+	struct session *user_session = current_session();
+	if (user_session->sql_flags & SQLITE_InteractiveMode) {
+		sqlite3VdbeAddOp1(v, OP_ResultTuple, cursor_id);
+		v->tuple_result_flush = true;
+	}
 }
 
 /*
diff --git a/src/box/sql/legacy.c b/src/box/sql/legacy.c
index 978cf01..64904d3 100644
--- a/src/box/sql/legacy.c
+++ b/src/box/sql/legacy.c
@@ -94,6 +94,7 @@ sqlite3_exec(sqlite3 * db,	/* The database on which the SQL executes */
 			rc = sqlite3_step(pStmt);
 			/* Invoke the callback function if required */
 			if (xCallback && (SQLITE_ROW == rc ||
+					  SQLITE_TUPLE == rc ||
 					  (SQLITE_DONE == rc && !callbackIsInit
 					   && user_session->
 					   sql_flags & SQLITE_NullCallback))) {
@@ -149,7 +150,7 @@ sqlite3_exec(sqlite3 * db,	/* The database on which the SQL executes */
 				}
 			}
 
-			if (rc != SQLITE_ROW) {
+			if (rc != SQLITE_ROW && rc != SQLITE_TUPLE) {
 				rc = sqlite3VdbeFinalize((Vdbe *) pStmt);
 				pStmt = 0;
 				zSql = zLeftover;
diff --git a/src/box/sql/main.c b/src/box/sql/main.c
index a9a0385..3bad568 100644
--- a/src/box/sql/main.c
+++ b/src/box/sql/main.c
@@ -911,6 +911,9 @@ sqlite3ErrName(int rc)
 		case SQLITE_ROW:
 			zName = "SQLITE_ROW";
 			break;
+		case SQLITE_TUPLE:
+			zName = "SQLITE_TUPLE";
+			break;
 		case SQLITE_WARNING:
 			zName = "SQLITE_WARNING";
 			break;
diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h
index ecc9ee8..b4e32c1 100644
--- a/src/box/sql/pragma.h
+++ b/src/box/sql/pragma.h
@@ -176,6 +176,11 @@ static const PragmaName aPragmaName[] = {
 	 /* ColNames:  */ 13, 6,
 	 /* iArg:      */ 1},
 #endif
+	{ /* zName:     */ "interactive_mode",
+	  /* ePragTyp:  */ PragTyp_FLAG,
+	  /* ePragFlg:  */ PragFlg_Result0 | PragFlg_NoColumns1,
+	  /* ColNames:  */ 0, 0,
+	  /* iArg:      */ SQLITE_InteractiveMode},
 #if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_PARSER_TRACE)
 	{ /* zName:     */ "parser_trace",
 	 /* ePragTyp:  */ PragTyp_PARSER_TRACE,
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index b1f2f26..56c5037 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -414,6 +414,8 @@ enum sql_ret_code {
 	SQLITE_ROW,
 	/** sqlite3_step() has finished executing. */
 	SQLITE_DONE,
+	/** sqlite3_step() has a ready tuple. */
+	SQLITE_TUPLE,
 };
 
 void *
@@ -565,8 +567,30 @@ sqlite3_prepare_v2(sqlite3 * db,	/* Database handle */
 		   const char **pzTail	/* OUT: Pointer to unused portion of zSql */
 	);
 
+/**
+ * Get last step tuple prepared with OP_ResultTupe.
+ * Valid when SQLITE_TUPLE returned with sqlite3_step.
+ *
+ * @param stmt Virtual database engine program.
+ * @retval tuple pointer.
+ */
+struct tuple *
+sqlite3_result_tuple(sqlite3_stmt *stmt);
+
+/**
+* Execute one step of VDBE execution.
+* @param stmt Virtual database engine program.
+*
+* @retval SQLITE_ROW On of rows of DQL request, or meta of DML -
+*         'rows deleted', 'rows inserted' and etc. Result can be
+*         accessed by columns using sqlite3_column_...().
+* @retval SQLITE_TUPLE Inserted or updated tuple on DML.
+* @retval SQLITE_ERROR Vdbe is terminated by an error.
+* @retval SQLITE_DONE Vdbe successfully finished execution, and
+*         can be finalized.
+*/
 int
-sqlite3_step(sqlite3_stmt *);
+sqlite3_step(sqlite3_stmt *stmt);
 
 const void *
 sqlite3_column_blob(sqlite3_stmt *, int iCol);
@@ -710,6 +734,15 @@ sqlite3_aggregate_context(sqlite3_context *,
 int
 sqlite3_column_count(sqlite3_stmt * pStmt);
 
+/**
+ * Test, if compiled @stmt has data to be displayed to
+ * user.
+ * @param stmt Virtual database engine program.
+ * @retval true if any, false else
+ */
+bool
+sqlite3_tuple_result_require_flush(struct sqlite3_stmt *stmt);
+
 const char *
 sqlite3_column_name(sqlite3_stmt *, int N);
 
@@ -1609,6 +1642,8 @@ struct sqlite3 {
 #define SQLITE_VdbeAddopTrace 0x00001000	/* Trace sqlite3VdbeAddOp() calls */
 #define SQLITE_IgnoreChecks   0x00002000	/* Do not enforce check constraints */
 #define SQLITE_ReadUncommitted 0x0004000	/* For shared-cache mode */
+/* Return operations results in SQL. */
+#define SQLITE_InteractiveMode 0x40000000
 #define SQLITE_ReverseOrder   0x00020000	/* Reverse unordered SELECTs */
 #define SQLITE_RecTriggers    0x00040000	/* Enable recursive triggers */
 #define SQLITE_ForeignKeys    0x00080000	/* Enforce foreign key constraints  */
@@ -1631,7 +1666,6 @@ struct sqlite3 {
 #define SQLITE_FactorOutConst 0x0008	/* Constant factoring */
 /*                not used    0x0010   // Was: SQLITE_IdxRealAsInt */
 #define SQLITE_DistinctOpt    0x0020	/* DISTINCT using indexes */
-#define SQLITE_CoverIdxScan   0x0040	/* Covering index scans */
 #define SQLITE_OrderByIdxJoin 0x0080	/* ORDER BY of joins via index */
 #define SQLITE_SubqCoroutine  0x0100	/* Evaluate subqueries as coroutines */
 #define SQLITE_Transitive     0x0200	/* Transitive constraints */
diff --git a/src/box/sql/tarantoolInt.h b/src/box/sql/tarantoolInt.h
index 94517f6..7c76062 100644
--- a/src/box/sql/tarantoolInt.h
+++ b/src/box/sql/tarantoolInt.h
@@ -62,9 +62,9 @@ int tarantoolSqlite3MovetoUnpacked(BtCursor * pCur, UnpackedRecord * pIdxKey,
 				   int *pRes);
 int tarantoolSqlite3Count(BtCursor * pCur, i64 * pnEntry);
 int tarantoolSqlite3Insert(struct space *space, const char *tuple,
-			   const char *tuple_end);
+			   const char *tuple_end, struct tuple **result);
 int tarantoolSqlite3Replace(struct space *space, const char *tuple,
-			    const char *tuple_end);
+			    const char *tuple_end, struct tuple **result);
 int tarantoolSqlite3Delete(BtCursor * pCur, u8 flags);
 int
 sql_delete_by_key(struct space *space, char *key, uint32_t key_size);
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index dc5146f..b9ac519 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -39,6 +39,7 @@
  * in this file for details.  If in doubt, do not deviate from existing
  * commenting and indentation practices when changing or adding code.
  */
+#include "box/tuple.h"
 #include "box/box.h"
 #include "box/fkey.h"
 #include "box/txn.h"
@@ -1364,6 +1365,25 @@ case OP_IntCopy: {            /* out2 */
 	break;
 }
 
+/* Opcode: ResultTuple P1 * * * *
+ * Synopsis: output=tuple(cursor(P1))
+ *
+ * The register P1 is a cursor, from which a last tuple is
+ * returned. Sqlite3_step returns SQLITE_TUPLE.
+ */
+case OP_ResultTuple: {
+	VdbeCursor *cursor = p->apCsr[pOp->p1];
+	assert(cursor != NULL);
+	assert(cursor->eCurType == CURTYPE_TARANTOOL);
+	if (cursor->uc.pCursor->last_tuple != NULL) {
+		p->result_tuple = cursor->uc.pCursor->last_tuple;
+		p->pc = (int)(pOp - aOp) + 1;
+		rc = SQLITE_TUPLE;
+		goto vdbe_return;
+	}
+	break;
+}
+
 /* Opcode: ResultRow P1 P2 * * *
  * Synopsis: output=r[P1@P2]
  *
@@ -4262,16 +4282,26 @@ case OP_IdxInsert: {        /* in2 */
 	} else {
 		BtCursor *pBtCur = pC->uc.pCursor;
 		if (pBtCur->curFlags & BTCF_TaCursor) {
+			if (pBtCur->last_tuple != NULL)
+				box_tuple_unref(pBtCur->last_tuple);
+			pBtCur->last_tuple = NULL;
+			struct tuple *result = NULL;
 			/* Make sure that memory has been allocated on region. */
 			assert(aMem[pOp->p2].flags & MEM_Ephem);
 			if (pOp->opcode == OP_IdxInsert)
 				rc = tarantoolSqlite3Insert(pBtCur->space,
 							    pIn2->z,
-							    pIn2->z + pIn2->n);
+							    pIn2->z + pIn2->n,
+							    &result);
 			else
 				rc = tarantoolSqlite3Replace(pBtCur->space,
 							     pIn2->z,
-							     pIn2->z + pIn2->n);
+							     pIn2->z + pIn2->n,
+							     &result);
+			if (rc == SQLITE_OK) {
+				pBtCur->last_tuple = result;
+				tuple_ref(result);
+			}
 		} else if (pBtCur->curFlags & BTCF_TEphemCursor) {
 			rc = tarantoolSqlite3EphemeralInsert(pBtCur->space,
 							     pIn2->z,
@@ -4319,7 +4349,7 @@ case OP_SInsert: {
 	struct space *space = space_by_id(pOp->p1);
 	assert(space != NULL);
 	assert(space_is_system(space));
-	rc = tarantoolSqlite3Insert(space, pIn2->z, pIn2->z + pIn2->n);
+	rc = tarantoolSqlite3Insert(space, pIn2->z, pIn2->z + pIn2->n, NULL);
 	if (rc)
 		goto abort_due_to_error;
 	if (pOp->p5 & OPFLAG_NCHANGE)
diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h
index 2987d7a..5b9219a 100644
--- a/src/box/sql/vdbe.h
+++ b/src/box/sql/vdbe.h
@@ -236,6 +236,9 @@ void sqlite3VdbeAppendP4(Vdbe *, void *pP4, int p4type);
 void
 sql_vdbe_set_p4_key_def(struct Parse *parse, struct Index *index);
 
+void
+sql_vdbe_set_tuple_result_flush(struct Vdbe *vdbe, bool is_set);
+
 VdbeOp *sqlite3VdbeGetOp(Vdbe *, int);
 int sqlite3VdbeMakeLabel(Vdbe *);
 void sqlite3VdbeRunOnlyOnce(Vdbe *);
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index ce97f49..eaa51b3 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -376,7 +376,17 @@ struct Vdbe {
 	Mem *aMem;		/* The memory locations */
 	Mem **apArg;		/* Arguments to currently executing user function */
 	Mem *aColName;		/* Column names to return */
-	Mem *pResultSet;	/* Pointer to an array of results */
+	union {
+		/**
+		 * Pointer to an array of results for SQLITE_ROW.
+		 */
+		struct Mem *pResultSet;
+		/**
+		 * Result tuple, returned by an iterator for
+		 * SQLITE_ROW.
+		 */
+		struct tuple *result_tuple;
+	};
 	char *zErrMsg;		/* Error message written here */
 	VdbeCursor **apCsr;	/* One element of this array for each open cursor */
 	Mem *aVar;		/* Values for the OP_Variable opcode. */
@@ -393,6 +403,11 @@ struct Vdbe {
 	bft changeCntOn:1;	/* True to update the change-counter */
 	bft runOnlyOnce:1;	/* Automatically expire on reset */
 	bft isPrepareV2:1;	/* True if prepared with prepare_v2() */
+	/**
+	 * Compiled SQL have OP_ResultTuple instructions that
+	 * export tuples to be displayed in console.
+	 */
+	bft tuple_result_flush:1;
 	u32 aCounter[5];	/* Counters used by sqlite3_stmt_status() */
 	char *zSql;		/* Text of the SQL statement that generated this */
 	void *pFree;		/* Free this when deleting the vdbe */
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index d3a91e2..57ee473 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -573,7 +573,7 @@ sqlite3Step(Vdbe * p)
 
 #ifndef SQLITE_OMIT_TRACE
 	/* If the statement completed successfully, invoke the profile callback */
-	if (rc != SQLITE_ROW)
+	if (rc != SQLITE_ROW && rc != SQLITE_TUPLE)
 		checkProfileCallback(db, p);
 #endif
 
@@ -589,9 +589,11 @@ sqlite3Step(Vdbe * p)
 	 * contains the value that would be returned if sqlite3_finalize()
 	 * were called on statement p.
 	 */
-	assert(rc == SQLITE_ROW || rc == SQLITE_DONE || rc == SQLITE_ERROR
-	       || (rc & 0xff) == SQLITE_BUSY || rc == SQLITE_MISUSE);
-	if (p->isPrepareV2 && rc != SQLITE_ROW && rc != SQLITE_DONE) {
+	assert(rc == SQLITE_ROW || rc == SQLITE_DONE || rc == SQLITE_ERROR ||
+	       rc == SQLITE_TUPLE || (rc & 0xff) == SQLITE_BUSY ||
+	       rc == SQLITE_MISUSE);
+	if (p->isPrepareV2 && rc != SQLITE_ROW && rc != SQLITE_DONE &&
+	    rc != SQLITE_TUPLE) {
 		/* If this statement was prepared using sqlite3_prepare_v2(), and an
 		 * error has occurred, then return the error code in p->rc to the
 		 * caller. Set the error code in the database handle to the same value.
@@ -601,11 +603,13 @@ sqlite3Step(Vdbe * p)
 	return (rc & db->errMask);
 }
 
-/*
- * This is the top-level implementation of sqlite3_step().  Call
- * sqlite3Step() to do most of the work.  If a schema error occurs,
- * call sqlite3Reprepare() and try again.
- */
+struct tuple *
+sqlite3_result_tuple(sqlite3_stmt *stmt)
+{
+	Vdbe *v = (Vdbe *) stmt;
+	return v->result_tuple;
+}
+
 int
 sqlite3_step(sqlite3_stmt * pStmt)
 {
@@ -851,6 +855,13 @@ sqlite3_column_count(sqlite3_stmt * pStmt)
 	return pVm ? pVm->nResColumn : 0;
 }
 
+bool
+sqlite3_tuple_result_require_flush(struct sqlite3_stmt *stmt)
+{
+	struct Vdbe *vdbe = (struct Vdbe *)stmt;
+	return vdbe->tuple_result_flush != 0;
+}
+
 /*
  * Return the number of values available from the current row of the
  * currently executing statement pStmt.
diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c
index 242a644..6c70ff5 100644
--- a/src/box/sql/vdbeaux.c
+++ b/src/box/sql/vdbeaux.c
@@ -2148,6 +2148,12 @@ sqlite3VdbeSetNumCols(Vdbe * p, int nResColumn)
 	initMemArray(p->aColName, n, p->db, MEM_Null);
 }
 
+void
+sql_vdbe_set_tuple_result_flush(struct Vdbe *vdbe, bool is_set)
+{
+	vdbe->tuple_result_flush = (bft)is_set;
+}
+
 /*
  * Set the name of the idx'th column to be returned by the SQL statement.
  * zName must be a pointer to a nul terminated string.
diff --git a/test/box/sql-update-with-nested-select.result b/test/box/sql-update-with-nested-select.result
deleted file mode 100644
index 419cebb..0000000
--- a/test/box/sql-update-with-nested-select.result
+++ /dev/null
@@ -1,33 +0,0 @@
-test_run = require('test_run').new()
----
-...
--- box.cfg()
--- create space
-box.sql.execute("CREATE TABLE t1(a integer primary key, b UNIQUE, e);");
----
-...
--- Debug
--- box.sql.execute("PRAGMA vdbe_debug=ON ; INSERT INTO zoobar VALUES (111, 222, 'c3', 444)")
--- Seed entries
-box.sql.execute("INSERT INTO t1 VALUES(1,4,6);");
----
-...
-box.sql.execute("INSERT INTO t1 VALUES(2,5,7);");
----
-...
--- Both entries must be updated
-box.sql.execute("UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);");
----
-...
--- Check
-box.sql.execute("SELECT e FROM t1");
----
-- - [7]
-  - [8]
-...
--- Cleanup
-box.sql.execute("DROP TABLE t1;");
----
-...
--- Debug
--- require("console").start()
diff --git a/test/box/sql-update-with-nested-select.test.lua b/test/box/sql-update-with-nested-select.test.lua
deleted file mode 100644
index 7b90968..0000000
--- a/test/box/sql-update-with-nested-select.test.lua
+++ /dev/null
@@ -1,25 +0,0 @@
-test_run = require('test_run').new()
-
--- box.cfg()
-
--- create space
-box.sql.execute("CREATE TABLE t1(a integer primary key, b UNIQUE, e);");
-
--- Debug
--- box.sql.execute("PRAGMA vdbe_debug=ON ; INSERT INTO zoobar VALUES (111, 222, 'c3', 444)")
-
--- Seed entries
-box.sql.execute("INSERT INTO t1 VALUES(1,4,6);");
-box.sql.execute("INSERT INTO t1 VALUES(2,5,7);");
-
--- Both entries must be updated
-box.sql.execute("UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);");
-
--- Check
-box.sql.execute("SELECT e FROM t1");
-
--- Cleanup
-box.sql.execute("DROP TABLE t1;");
-
--- Debug
--- require("console").start()
diff --git a/test/sql-tap/lua/sqltester.lua b/test/sql-tap/lua/sqltester.lua
index 8751ef8..44e0de7 100644
--- a/test/sql-tap/lua/sqltester.lua
+++ b/test/sql-tap/lua/sqltester.lua
@@ -433,6 +433,7 @@ box.cfg{
 
 local engine = test_run and test_run:get_cfg('engine') or 'memtx'
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 function test.engine(self)
     return engine
diff --git a/test/sql/check-clear-ephemeral.result b/test/sql/check-clear-ephemeral.result
index 4ab1fe1..7b1317b 100644
--- a/test/sql/check-clear-ephemeral.result
+++ b/test/sql/check-clear-ephemeral.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- create space
 box.sql.execute("CREATE TABLE t1(a,b,c,PRIMARY KEY(b,c));")
diff --git a/test/sql/check-clear-ephemeral.test.lua b/test/sql/check-clear-ephemeral.test.lua
index c7ea733..f98c986 100644
--- a/test/sql/check-clear-ephemeral.test.lua
+++ b/test/sql/check-clear-ephemeral.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 -- box.cfg()
 
 -- create space
diff --git a/test/sql/checks.result b/test/sql/checks.result
index 3084d89..d179c6c 100644
--- a/test/sql/checks.result
+++ b/test/sql/checks.result
@@ -14,6 +14,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 --
 -- gh-3272: Move SQL CHECK into server
 --
diff --git a/test/sql/checks.test.lua b/test/sql/checks.test.lua
index fb95809..2b73102 100644
--- a/test/sql/checks.test.lua
+++ b/test/sql/checks.test.lua
@@ -3,6 +3,7 @@ test_run = env.new()
 test_run:cmd("push filter ".."'\\.lua.*:[0-9]+: ' to '.lua...\"]:<line>: '")
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 --
 -- gh-3272: Move SQL CHECK into server
diff --git a/test/sql/clear.result b/test/sql/clear.result
index c75e134..550cf6a 100644
--- a/test/sql/clear.result
+++ b/test/sql/clear.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- create space
 box.sql.execute("CREATE TABLE zoobar (c1, c2 PRIMARY KEY, c3, c4)")
diff --git a/test/sql/clear.test.lua b/test/sql/clear.test.lua
index 142cda8..618dbbf 100644
--- a/test/sql/clear.test.lua
+++ b/test/sql/clear.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/collation.result b/test/sql/collation.result
index 79ba9ab..0ecbab7 100644
--- a/test/sql/collation.result
+++ b/test/sql/collation.result
@@ -10,6 +10,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- gh-3010: COLLATE after LIMIT should throw an error
 -- All of these tests should throw error "near "COLLATE": syntax error"
 box.sql.execute("SELECT 1 LIMIT 1 COLLATE BINARY;")
diff --git a/test/sql/collation.test.lua b/test/sql/collation.test.lua
index 935dea8..1379cff 100644
--- a/test/sql/collation.test.lua
+++ b/test/sql/collation.test.lua
@@ -2,6 +2,7 @@ remote = require('net.box')
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- gh-3010: COLLATE after LIMIT should throw an error
 
diff --git a/test/sql/delete-multiple-idx.result b/test/sql/delete-multiple-idx.result
index a163cf1..371171e 100644
--- a/test/sql/delete-multiple-idx.result
+++ b/test/sql/delete-multiple-idx.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- Create space.
 box.sql.execute("CREATE TABLE t3(id primary key,x,y);");
diff --git a/test/sql/delete-multiple-idx.test.lua b/test/sql/delete-multiple-idx.test.lua
index e187355..eb71a6d 100644
--- a/test/sql/delete-multiple-idx.test.lua
+++ b/test/sql/delete-multiple-idx.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/delete.result b/test/sql/delete.result
index 52f9969..7e5bb24 100644
--- a/test/sql/delete.result
+++ b/test/sql/delete.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- create space
 box.sql.execute("CREATE TABLE t1(a, b, PRIMARY KEY(a, b));");
diff --git a/test/sql/delete.test.lua b/test/sql/delete.test.lua
index 0477d22..8512a22 100644
--- a/test/sql/delete.test.lua
+++ b/test/sql/delete.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/drop-index.result b/test/sql/drop-index.result
index 2aaddac..04a11ff 100644
--- a/test/sql/drop-index.result
+++ b/test/sql/drop-index.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- create space
 box.sql.execute("CREATE TABLE zzoobar (c1, c2 PRIMARY KEY, c3, c4)")
diff --git a/test/sql/drop-index.test.lua b/test/sql/drop-index.test.lua
index 8bb51e1..482fa4c 100644
--- a/test/sql/drop-index.test.lua
+++ b/test/sql/drop-index.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/drop-table.result b/test/sql/drop-table.result
index 08f2496..c05220a 100644
--- a/test/sql/drop-table.result
+++ b/test/sql/drop-table.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- create space
 box.sql.execute("CREATE TABLE zzzoobar (c1, c2 PRIMARY KEY, c3, c4)")
diff --git a/test/sql/drop-table.test.lua b/test/sql/drop-table.test.lua
index 9663074..f4425ba 100644
--- a/test/sql/drop-table.test.lua
+++ b/test/sql/drop-table.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/errinj.result b/test/sql/errinj.result
index a0ba60f..5962008 100644
--- a/test/sql/errinj.result
+++ b/test/sql/errinj.result
@@ -10,6 +10,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 errinj = box.error.injection
 ---
 ...
@@ -73,7 +76,9 @@ while f1:status() ~= 'dead' do fiber.sleep(0) end
 ...
 insert_res
 ---
-- rowcount: 1
+- metadata: []
+  rows:
+  - [100, 1, '1']
 ...
 select_res
 ---
diff --git a/test/sql/errinj.test.lua b/test/sql/errinj.test.lua
index 25d73f0..244c9d0 100644
--- a/test/sql/errinj.test.lua
+++ b/test/sql/errinj.test.lua
@@ -2,6 +2,7 @@ remote = require('net.box')
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 errinj = box.error.injection
 fiber = require('fiber')
 
diff --git a/test/sql/foreign-keys.result b/test/sql/foreign-keys.result
index f33b49a..816a170 100644
--- a/test/sql/foreign-keys.result
+++ b/test/sql/foreign-keys.result
@@ -5,6 +5,9 @@ test_run = env.new()
 ---
 ...
 test_run:cmd('restart server default with cleanup=1')
+box.sql.execute("pragma interactive_mode=0")
+---
+...
 -- Check that tuple inserted into _fk_constraint is FK constrains
 -- valid data.
 --
diff --git a/test/sql/foreign-keys.test.lua b/test/sql/foreign-keys.test.lua
index 8d27aa0..d522263 100644
--- a/test/sql/foreign-keys.test.lua
+++ b/test/sql/foreign-keys.test.lua
@@ -1,6 +1,7 @@
 env = require('test_run')
 test_run = env.new()
 test_run:cmd('restart server default with cleanup=1')
+box.sql.execute("pragma interactive_mode=0")
 
 
 -- Check that tuple inserted into _fk_constraint is FK constrains
diff --git a/test/sql/gh-2347-max-int-literals.result b/test/sql/gh-2347-max-int-literals.result
index c289a80..061f98e 100644
--- a/test/sql/gh-2347-max-int-literals.result
+++ b/test/sql/gh-2347-max-int-literals.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 box.cfg{}
 ---
 ...
diff --git a/test/sql/gh-2347-max-int-literals.test.lua b/test/sql/gh-2347-max-int-literals.test.lua
index 4b1ef0d..bdd51e5 100644
--- a/test/sql/gh-2347-max-int-literals.test.lua
+++ b/test/sql/gh-2347-max-int-literals.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 box.cfg{}
 
diff --git a/test/sql/gh-2929-primary-key.result b/test/sql/gh-2929-primary-key.result
index 66a9b96..28f5865 100644
--- a/test/sql/gh-2929-primary-key.result
+++ b/test/sql/gh-2929-primary-key.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- All tables in SQL are now WITHOUT ROW ID, so if user
 -- tries to create table without a primary key, an appropriate error message
 -- should be raised. This tests checks it.
diff --git a/test/sql/gh-2929-primary-key.test.lua b/test/sql/gh-2929-primary-key.test.lua
index 0e05354..d2493bc 100644
--- a/test/sql/gh-2929-primary-key.test.lua
+++ b/test/sql/gh-2929-primary-key.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- All tables in SQL are now WITHOUT ROW ID, so if user
 -- tries to create table without a primary key, an appropriate error message
diff --git a/test/sql/gh-2981-check-autoinc.result b/test/sql/gh-2981-check-autoinc.result
index b0f55e6..f4e75d9 100644
--- a/test/sql/gh-2981-check-autoinc.result
+++ b/test/sql/gh-2981-check-autoinc.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 box.cfg{}
 ---
 ...
diff --git a/test/sql/gh-2981-check-autoinc.test.lua b/test/sql/gh-2981-check-autoinc.test.lua
index 98a5fb4..17f4eee 100644
--- a/test/sql/gh-2981-check-autoinc.test.lua
+++ b/test/sql/gh-2981-check-autoinc.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 box.cfg{}
 
diff --git a/test/sql/gh-3199-no-mem-leaks.result b/test/sql/gh-3199-no-mem-leaks.result
index 9d715e8..a6c1075 100644
--- a/test/sql/gh-3199-no-mem-leaks.result
+++ b/test/sql/gh-3199-no-mem-leaks.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 fiber = require('fiber')
 ---
 ...
diff --git a/test/sql/gh-3199-no-mem-leaks.test.lua b/test/sql/gh-3199-no-mem-leaks.test.lua
index 138166b..fb29bb3 100644
--- a/test/sql/gh-3199-no-mem-leaks.test.lua
+++ b/test/sql/gh-3199-no-mem-leaks.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 fiber = require('fiber')
 
 -- This test checks that no leaks of region memory happens during
diff --git a/test/sql/gh2141-delete-trigger-drop-table.result b/test/sql/gh2141-delete-trigger-drop-table.result
index c1b64d1..3d0ac53 100644
--- a/test/sql/gh2141-delete-trigger-drop-table.result
+++ b/test/sql/gh2141-delete-trigger-drop-table.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- create space
 box.sql.execute("CREATE TABLE t(id PRIMARY KEY)")
 ---
diff --git a/test/sql/gh2141-delete-trigger-drop-table.test.lua b/test/sql/gh2141-delete-trigger-drop-table.test.lua
index 19d3188..114465e 100644
--- a/test/sql/gh2141-delete-trigger-drop-table.test.lua
+++ b/test/sql/gh2141-delete-trigger-drop-table.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- create space
 box.sql.execute("CREATE TABLE t(id PRIMARY KEY)")
diff --git a/test/sql/gh2251-multiple-update.result b/test/sql/gh2251-multiple-update.result
index 5e137ee..ae9ef1a 100644
--- a/test/sql/gh2251-multiple-update.result
+++ b/test/sql/gh2251-multiple-update.result
@@ -8,6 +8,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 box.sql.execute("CREATE TABLE t1(a integer primary key, b UNIQUE, e);")
 ---
diff --git a/test/sql/gh2251-multiple-update.test.lua b/test/sql/gh2251-multiple-update.test.lua
index 0166a17..d4a282b 100644
--- a/test/sql/gh2251-multiple-update.test.lua
+++ b/test/sql/gh2251-multiple-update.test.lua
@@ -2,6 +2,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/gh2483-remote-persistency-check.result b/test/sql/gh2483-remote-persistency-check.result
index 50e65f2..aabbbb0 100644
--- a/test/sql/gh2483-remote-persistency-check.result
+++ b/test/sql/gh2483-remote-persistency-check.result
@@ -11,6 +11,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 box.schema.user.grant('guest', 'read,write,execute', 'universe')
 ---
 ...
diff --git a/test/sql/gh2483-remote-persistency-check.test.lua b/test/sql/gh2483-remote-persistency-check.test.lua
index b952f6b..6c1f007 100644
--- a/test/sql/gh2483-remote-persistency-check.test.lua
+++ b/test/sql/gh2483-remote-persistency-check.test.lua
@@ -3,6 +3,7 @@ env = require('test_run')
 test_run = env.new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 box.schema.user.grant('guest', 'read,write,execute', 'universe')
 
diff --git a/test/sql/gh2808-inline-unique-persistency-check.result b/test/sql/gh2808-inline-unique-persistency-check.result
index fdd000f..96258b9 100644
--- a/test/sql/gh2808-inline-unique-persistency-check.result
+++ b/test/sql/gh2808-inline-unique-persistency-check.result
@@ -11,6 +11,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- Create a table and insert a datum
 box.sql.execute([[CREATE TABLE t1(a PRIMARY KEY, b, UNIQUE(b));]])
 ---
@@ -29,6 +32,7 @@ test_run:cmd('restart server default');
 -- correctly after restart (#2808)
 box.sql.execute([[INSERT INTO t1 VALUES(2,3);]])
 ---
+- - [2, 3]
 ...
 -- Sanity check
 box.sql.execute([[SELECT * FROM t1]])
diff --git a/test/sql/gh2808-inline-unique-persistency-check.test.lua b/test/sql/gh2808-inline-unique-persistency-check.test.lua
index eb4e051..e3fc978 100644
--- a/test/sql/gh2808-inline-unique-persistency-check.test.lua
+++ b/test/sql/gh2808-inline-unique-persistency-check.test.lua
@@ -3,6 +3,7 @@ env = require('test_run')
 test_run = env.new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- Create a table and insert a datum
 box.sql.execute([[CREATE TABLE t1(a PRIMARY KEY, b, UNIQUE(b));]])
diff --git a/test/sql/icu-upper-lower.result b/test/sql/icu-upper-lower.result
index 6ca44b9..8cecce5 100644
--- a/test/sql/icu-upper-lower.result
+++ b/test/sql/icu-upper-lower.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 test_run:cmd("setopt delimiter ';'")
 ---
 - true
diff --git a/test/sql/icu-upper-lower.test.lua b/test/sql/icu-upper-lower.test.lua
index 6629a74..b0bb0a3 100644
--- a/test/sql/icu-upper-lower.test.lua
+++ b/test/sql/icu-upper-lower.test.lua
@@ -1,7 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
-
+box.sql.execute('pragma interactive_mode=0;')
 test_run:cmd("setopt delimiter ';'")
 
 upper_lower_test = function (str)
diff --git a/test/sql/insert-unique.result b/test/sql/insert-unique.result
index 797c8ef..fadcd54 100644
--- a/test/sql/insert-unique.result
+++ b/test/sql/insert-unique.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- create space
 box.sql.execute("CREATE TABLE zoobar (c1, c2 PRIMARY KEY, c3, c4)")
diff --git a/test/sql/insert-unique.test.lua b/test/sql/insert-unique.test.lua
index a004c57..f932cb8 100644
--- a/test/sql/insert-unique.test.lua
+++ b/test/sql/insert-unique.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/iproto.result b/test/sql/iproto.result
index af474bc..d9a83c0 100644
--- a/test/sql/iproto.result
+++ b/test/sql/iproto.result
@@ -10,6 +10,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 box.sql.execute('create table test (id primary key, a float, b text)')
 ---
 ...
@@ -69,19 +72,31 @@ type(ret.rows[1])
 -- Operation with rowcount result.
 cn:execute('insert into test values (10, 11, NULL)')
 ---
-- rowcount: 1
+- metadata: []
+  rows:
+  - [10, 11, null]
 ...
 cn:execute('delete from test where a = 5')
 ---
-- rowcount: 1
+- metadata: []
+  rows:
+  - [4, 5, '6']
 ...
 cn:execute('insert into test values (11, 12, NULL), (12, 12, NULL), (13, 12, NULL)')
 ---
-- rowcount: 3
+- metadata: []
+  rows:
+  - [11, 12, null]
+  - [12, 12, null]
+  - [13, 12, null]
 ...
 cn:execute('delete from test where a = 12')
 ---
-- rowcount: 3
+- metadata: []
+  rows:
+  - [11, 12, null]
+  - [12, 12, null]
+  - [13, 12, null]
 ...
 -- SQL errors.
 cn:execute('insert into not_existing_table values ("kek")')
@@ -338,7 +353,9 @@ box.space.TEST2.name
 ...
 cn:execute('insert into test2 values (1, 1, 1, 1)')
 ---
-- rowcount: 1
+- metadata: []
+  rows:
+  - [1, 1, 1, 1]
 ...
 cn:execute('select * from test2')
 ---
@@ -376,7 +393,11 @@ cn:execute('create table test3(id primary key, a, b)')
 -- for _space and for _index.
 cn:execute('insert into test3 values (1, 1, 1), (2, 2, 2), (3, 3, 3)')
 ---
-- rowcount: 3
+- metadata: []
+  rows:
+  - [1, 1, 1]
+  - [2, 2, 2]
+  - [3, 3, 3]
 ...
 cn:execute('create table if not exists test3(id primary key)')
 ---
@@ -455,7 +476,11 @@ cn:execute('create trigger trig INSERT ON test3 BEGIN SELECT * FROM test3; END;'
 ...
 cn:execute('insert into test3 values (1, 1, 1), (2, 2, 2), (3, 3, 3)')
 ---
-- rowcount: 3
+- metadata: []
+  rows:
+  - [1, 1, 1]
+  - [2, 2, 2]
+  - [3, 3, 3]
 ...
 cn:execute('drop table test3')
 ---
@@ -548,7 +573,9 @@ future3 = cn:execute('insert into test values (2, 2, 2), (3, 3, 3)', nil, nil, {
 ...
 future1:wait_result()
 ---
-- rowcount: 1
+- metadata: []
+  rows:
+  - [1, 1, 1]
 ...
 future2:wait_result()
 ---
@@ -558,7 +585,10 @@ future2:wait_result()
 ...
 future3:wait_result()
 ---
-- rowcount: 2
+- metadata: []
+  rows:
+  - [2, 2, 2]
+  - [3, 3, 3]
 ...
 future4 = cn:execute('select * from test', nil, nil, {is_async = true})
 ---
diff --git a/test/sql/iproto.test.lua b/test/sql/iproto.test.lua
index 220331b..08c259c 100644
--- a/test/sql/iproto.test.lua
+++ b/test/sql/iproto.test.lua
@@ -2,6 +2,7 @@ remote = require('net.box')
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 box.sql.execute('create table test (id primary key, a float, b text)')
 space = box.space.TEST
diff --git a/test/sql/max-on-index.result b/test/sql/max-on-index.result
index b107633..9b575f6 100644
--- a/test/sql/max-on-index.result
+++ b/test/sql/max-on-index.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- create space
 -- scalar affinity
diff --git a/test/sql/max-on-index.test.lua b/test/sql/max-on-index.test.lua
index b879e38..7472d46 100644
--- a/test/sql/max-on-index.test.lua
+++ b/test/sql/max-on-index.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/message-func-indexes.result b/test/sql/message-func-indexes.result
index 5928a8e..4abe4ec 100644
--- a/test/sql/message-func-indexes.result
+++ b/test/sql/message-func-indexes.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- Creating tables.
 box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a INTEGER)")
 ---
diff --git a/test/sql/message-func-indexes.test.lua b/test/sql/message-func-indexes.test.lua
index e0eae76..b4cf6d0 100644
--- a/test/sql/message-func-indexes.test.lua
+++ b/test/sql/message-func-indexes.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- Creating tables.
 box.sql.execute("CREATE TABLE t1(id INTEGER PRIMARY KEY, a INTEGER)")
diff --git a/test/sql/misc.result b/test/sql/misc.result
index 93b383a..5702b15 100644
--- a/test/sql/misc.result
+++ b/test/sql/misc.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- Forbid multistatement queries.
 box.sql.execute('select 1;')
 ---
@@ -40,3 +43,62 @@ box.sql.execute('\n\n\n\t\t\t   ')
 ---
 - error: 'syntax error: empty request'
 ...
+--
+-- gh-2370: Return a tuple result from SQL
+--
+box.sql.execute("pragma interactive_mode=1;")
+---
+...
+box.sql.execute("CREATE TABLE t (s1 INT, s2 INT, s3 INT, s4 INT PRIMARY KEY);")
+---
+...
+box.sql.execute("INSERT INTO t VALUES (1,1,1,2),(1,1,1,5),(1,1,1,6);")
+---
+- - [1, 1, 1, 2]
+  - [1, 1, 1, 5]
+  - [1, 1, 1, 6]
+...
+box.sql.execute("UPDATE t SET s2=s2+s1 WHERE s4 IN (SELECT s4 FROM t);");
+---
+- - [1, 2, 1, 2]
+  - [1, 2, 1, 5]
+  - [1, 2, 1, 6]
+...
+box.sql.execute("UPDATE t SET s2=s2+s1 WHERE s4=6 OR s4=2;");
+---
+- - [1, 3, 1, 2]
+  - [1, 3, 1, 6]
+...
+dropped = box.sql.execute("DELETE FROM t WHERE s2 = 3;");
+---
+...
+dropped
+---
+- - [1, 3, 1, 2]
+  - [1, 3, 1, 6]
+...
+dropped[1]
+---
+- [1, 3, 1, 2]
+...
+assert(dropped[1][4] == dropped[1].S4)
+---
+- true
+...
+assert(dropped[1].S4 == 2)
+---
+- true
+...
+box.sql.execute("DELETE FROM t;")
+---
+...
+box.sql.execute("DROP TABLE t;")
+---
+...
+assert(dropped[1].S4 == 2)
+---
+- true
+...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
diff --git a/test/sql/misc.test.lua b/test/sql/misc.test.lua
index 1ed0198..a9f23c5 100644
--- a/test/sql/misc.test.lua
+++ b/test/sql/misc.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- Forbid multistatement queries.
 box.sql.execute('select 1;')
@@ -11,3 +12,21 @@ box.sql.execute(';')
 box.sql.execute('')
 box.sql.execute('     ;')
 box.sql.execute('\n\n\n\t\t\t   ')
+
+--
+-- gh-2370: Return a tuple result from SQL
+--
+box.sql.execute("pragma interactive_mode=1;")
+box.sql.execute("CREATE TABLE t (s1 INT, s2 INT, s3 INT, s4 INT PRIMARY KEY);")
+box.sql.execute("INSERT INTO t VALUES (1,1,1,2),(1,1,1,5),(1,1,1,6);")
+box.sql.execute("UPDATE t SET s2=s2+s1 WHERE s4 IN (SELECT s4 FROM t);");
+box.sql.execute("UPDATE t SET s2=s2+s1 WHERE s4=6 OR s4=2;");
+dropped = box.sql.execute("DELETE FROM t WHERE s2 = 3;");
+dropped
+dropped[1]
+assert(dropped[1][4] == dropped[1].S4)
+assert(dropped[1].S4 == 2)
+box.sql.execute("DELETE FROM t;")
+box.sql.execute("DROP TABLE t;")
+assert(dropped[1].S4 == 2)
+box.sql.execute('pragma interactive_mode=0;')
diff --git a/test/sql/on-conflict.result b/test/sql/on-conflict.result
index eed06d5..155af27 100644
--- a/test/sql/on-conflict.result
+++ b/test/sql/on-conflict.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- Create space
 box.sql.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, v INTEGER UNIQUE ON CONFLICT ABORT)")
 ---
diff --git a/test/sql/on-conflict.test.lua b/test/sql/on-conflict.test.lua
index 347245a..552afd7 100644
--- a/test/sql/on-conflict.test.lua
+++ b/test/sql/on-conflict.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- Create space
 box.sql.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, v INTEGER UNIQUE ON CONFLICT ABORT)")
diff --git a/test/sql/persistency.result b/test/sql/persistency.result
index c65baa0..8abb5cd 100644
--- a/test/sql/persistency.result
+++ b/test/sql/persistency.result
@@ -10,6 +10,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- create space
 box.sql.execute("CREATE TABLE foobar (foo PRIMARY KEY, bar)")
 ---
@@ -180,6 +183,8 @@ box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"");
 -- ... functional
 box.sql.execute("INSERT INTO foobar VALUES ('foobar trigger test', 8888)")
 ---
+- - ['foobar trigger test', 8888]
+  - ['trigger test', 9999]
 ...
 box.sql.execute("SELECT * FROM barfoo WHERE foo = 9999");
 ---
diff --git a/test/sql/persistency.test.lua b/test/sql/persistency.test.lua
index 417d8c0..2a01500 100644
--- a/test/sql/persistency.test.lua
+++ b/test/sql/persistency.test.lua
@@ -2,6 +2,7 @@ env = require('test_run')
 test_run = env.new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- create space
 box.sql.execute("CREATE TABLE foobar (foo PRIMARY KEY, bar)")
diff --git a/test/sql/savepoints.result b/test/sql/savepoints.result
index 8553dd8..28e9962 100644
--- a/test/sql/savepoints.result
+++ b/test/sql/savepoints.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- These tests check that SQL savepoints properly work outside
 -- transactions as well as inside transactions started in Lua.
 -- gh-3313
diff --git a/test/sql/savepoints.test.lua b/test/sql/savepoints.test.lua
index a4ed061..f3414e1 100644
--- a/test/sql/savepoints.test.lua
+++ b/test/sql/savepoints.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- These tests check that SQL savepoints properly work outside
 -- transactions as well as inside transactions started in Lua.
diff --git a/test/sql/select-null.result b/test/sql/select-null.result
index 53bef1b..5e1fdf5 100644
--- a/test/sql/select-null.result
+++ b/test/sql/select-null.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- create space
 box.sql.execute("CREATE TABLE t3(id INT, a, b, PRIMARY KEY(id))")
diff --git a/test/sql/select-null.test.lua b/test/sql/select-null.test.lua
index 3e9cb81..0cb2190 100644
--- a/test/sql/select-null.test.lua
+++ b/test/sql/select-null.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/sql-statN-index-drop.result b/test/sql/sql-statN-index-drop.result
index a751eca..8162d46 100644
--- a/test/sql/sql-statN-index-drop.result
+++ b/test/sql/sql-statN-index-drop.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- Initializing some things.
 box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
 ---
diff --git a/test/sql/sql-statN-index-drop.test.lua b/test/sql/sql-statN-index-drop.test.lua
index fe7e15b..9f56864 100644
--- a/test/sql/sql-statN-index-drop.test.lua
+++ b/test/sql/sql-statN-index-drop.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- Initializing some things.
 box.sql.execute("CREATE TABLE t1(id PRIMARY KEY, a);")
diff --git a/test/sql/sql-update-with-nested-select.result b/test/sql/sql-update-with-nested-select.result
new file mode 100644
index 0000000..e2984c7
--- /dev/null
+++ b/test/sql/sql-update-with-nested-select.result
@@ -0,0 +1,36 @@
+test_run = require('test_run').new()
+---
+...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
+-- box.cfg()
+-- create space
+box.sql.execute("CREATE TABLE t1(a integer primary key, b UNIQUE, e);");
+---
+...
+-- Debug
+-- box.sql.execute("PRAGMA vdbe_debug=ON ; INSERT INTO zoobar VALUES (111, 222, 'c3', 444)")
+-- Seed entries
+box.sql.execute("INSERT INTO t1 VALUES(1,4,6);");
+---
+...
+box.sql.execute("INSERT INTO t1 VALUES(2,5,7);");
+---
+...
+-- Both entries must be updated
+box.sql.execute("UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);");
+---
+...
+-- Check
+box.sql.execute("SELECT e FROM t1");
+---
+- - [7]
+  - [8]
+...
+-- Cleanup
+box.sql.execute("DROP TABLE t1;");
+---
+...
+-- Debug
+-- require("console").start()
diff --git a/test/sql/sql-update-with-nested-select.test.lua b/test/sql/sql-update-with-nested-select.test.lua
new file mode 100644
index 0000000..a33b9f8
--- /dev/null
+++ b/test/sql/sql-update-with-nested-select.test.lua
@@ -0,0 +1,26 @@
+test_run = require('test_run').new()
+box.sql.execute('pragma interactive_mode=0;')
+
+-- box.cfg()
+
+-- create space
+box.sql.execute("CREATE TABLE t1(a integer primary key, b UNIQUE, e);");
+
+-- Debug
+-- box.sql.execute("PRAGMA vdbe_debug=ON ; INSERT INTO zoobar VALUES (111, 222, 'c3', 444)")
+
+-- Seed entries
+box.sql.execute("INSERT INTO t1 VALUES(1,4,6);");
+box.sql.execute("INSERT INTO t1 VALUES(2,5,7);");
+
+-- Both entries must be updated
+box.sql.execute("UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);");
+
+-- Check
+box.sql.execute("SELECT e FROM t1");
+
+-- Cleanup
+box.sql.execute("DROP TABLE t1;");
+
+-- Debug
+-- require("console").start()
diff --git a/test/sql/tokenizer.result b/test/sql/tokenizer.result
index 95063b5..53ad43b 100644
--- a/test/sql/tokenizer.result
+++ b/test/sql/tokenizer.result
@@ -10,6 +10,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 sql_tokenizer = require('sql_tokenizer')
 ---
 ...
diff --git a/test/sql/tokenizer.test.lua b/test/sql/tokenizer.test.lua
index 4a4e289..866d9bc 100644
--- a/test/sql/tokenizer.test.lua
+++ b/test/sql/tokenizer.test.lua
@@ -2,6 +2,7 @@ env = require('test_run')
 test_run = env.new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 sql_tokenizer = require('sql_tokenizer')
 
diff --git a/test/sql/transition.result b/test/sql/transition.result
index 805e8aa..09aadf3 100644
--- a/test/sql/transition.result
+++ b/test/sql/transition.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- create space
 box.sql.execute("CREATE TABLE foobar (foo PRIMARY KEY, bar)")
 ---
diff --git a/test/sql/transition.test.lua b/test/sql/transition.test.lua
index cae45aa..86e6c89 100644
--- a/test/sql/transition.test.lua
+++ b/test/sql/transition.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- create space
 box.sql.execute("CREATE TABLE foobar (foo PRIMARY KEY, bar)")
diff --git a/test/sql/transitive-transactions.result b/test/sql/transitive-transactions.result
index a01b1d0..c73408d 100644
--- a/test/sql/transitive-transactions.result
+++ b/test/sql/transitive-transactions.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute("pragma sql_default_engine=\'"..engine.."\'")
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 test_run:cmd("setopt delimiter ';'")
 ---
 - true
diff --git a/test/sql/transitive-transactions.test.lua b/test/sql/transitive-transactions.test.lua
index 1ac2b8d..c91ee39 100644
--- a/test/sql/transitive-transactions.test.lua
+++ b/test/sql/transitive-transactions.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute("pragma sql_default_engine=\'"..engine.."\'")
+box.sql.execute('pragma interactive_mode=0;')
 test_run:cmd("setopt delimiter ';'")
 
 -- These tests are aimed at checking transitive transactions
diff --git a/test/sql/triggers.result b/test/sql/triggers.result
index 3cff9e3..7066b33 100644
--- a/test/sql/triggers.result
+++ b/test/sql/triggers.result
@@ -10,6 +10,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- Get invariant part of the tuple; name and opts don't change.
  function immutable_part(data) local r = {} for i, l in pairs(data) do table.insert(r, {l.name, l.opts}) end return r end
 ---
diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua
index 4a0938f..419c969 100644
--- a/test/sql/triggers.test.lua
+++ b/test/sql/triggers.test.lua
@@ -2,6 +2,7 @@ env = require('test_run')
 test_run = env.new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- Get invariant part of the tuple; name and opts don't change.
  function immutable_part(data) local r = {} for i, l in pairs(data) do table.insert(r, {l.name, l.opts}) end return r end
diff --git a/test/sql/update-with-nested-select.result b/test/sql/update-with-nested-select.result
index 51825a2..b0fbe78 100644
--- a/test/sql/update-with-nested-select.result
+++ b/test/sql/update-with-nested-select.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- box.cfg()
 -- create space
 box.sql.execute("CREATE TABLE t1(a integer primary key, b UNIQUE, e);");
diff --git a/test/sql/update-with-nested-select.test.lua b/test/sql/update-with-nested-select.test.lua
index f9f9b7a..6caeb63 100644
--- a/test/sql/update-with-nested-select.test.lua
+++ b/test/sql/update-with-nested-select.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- box.cfg()
 
diff --git a/test/sql/upgrade.result b/test/sql/upgrade.result
index 5e7d851..85d2d44 100644
--- a/test/sql/upgrade.result
+++ b/test/sql/upgrade.result
@@ -22,6 +22,9 @@ test_run:switch('upgrade')
 ---
 - true
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- test system tables
 box.space._space.index['name']:get('_trigger')
 ---
diff --git a/test/sql/upgrade.test.lua b/test/sql/upgrade.test.lua
index cd4dd3c..49849eb 100644
--- a/test/sql/upgrade.test.lua
+++ b/test/sql/upgrade.test.lua
@@ -7,6 +7,7 @@ test_run:cmd('create server upgrade with script="sql/upgrade/upgrade.lua", workd
 test_run:cmd('start server upgrade')
 
 test_run:switch('upgrade')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- test system tables
 box.space._space.index['name']:get('_trigger')
diff --git a/test/sql/view.result b/test/sql/view.result
index 2e42304..411f836 100644
--- a/test/sql/view.result
+++ b/test/sql/view.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 -- Verify that constraints on 'view' option are working.
 -- box.cfg()
 -- Create space and view.
diff --git a/test/sql/view.test.lua b/test/sql/view.test.lua
index 1d73133..f651774 100644
--- a/test/sql/view.test.lua
+++ b/test/sql/view.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 
 -- Verify that constraints on 'view' option are working.
 
diff --git a/test/sql/view_delayed_wal.result b/test/sql/view_delayed_wal.result
index 2e1047a..0c073eb 100644
--- a/test/sql/view_delayed_wal.result
+++ b/test/sql/view_delayed_wal.result
@@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
 ---
 ...
+box.sql.execute('pragma interactive_mode=0;')
+---
+...
 fiber = require('fiber')
 ---
 ...
diff --git a/test/sql/view_delayed_wal.test.lua b/test/sql/view_delayed_wal.test.lua
index 62ab7d7..adbeb99 100644
--- a/test/sql/view_delayed_wal.test.lua
+++ b/test/sql/view_delayed_wal.test.lua
@@ -1,6 +1,7 @@
 test_run = require('test_run').new()
 engine = test_run:get_cfg('engine')
 box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+box.sql.execute('pragma interactive_mode=0;')
 fiber = require('fiber')
 
 -- View reference counters are incremented before firing
-- 
2.7.4

^ permalink raw reply	[flat|nested] 3+ messages in thread

* [tarantool-patches] Re: [PATCH v1 1/1] sql: return a tuple result from SQL
  2018-08-21 15:00 [tarantool-patches] [PATCH v1 1/1] sql: return a tuple result from SQL Kirill Shcherbatov
@ 2018-08-21 15:28 ` Vladislav Shpilevoy
  2018-08-27 11:02 ` n.pettik
  1 sibling, 0 replies; 3+ messages in thread
From: Vladislav Shpilevoy @ 2018-08-21 15:28 UTC (permalink / raw)
  To: tarantool-patches, Kirill Shcherbatov; +Cc: korablev

Hi!

On 21/08/2018 18:00, Kirill Shcherbatov wrote:
> @TarantoolBot document
> Title: Return a tuple result from SQL
> SQL insert/update/delete requests would return
> table with processed tuples. It is default behavior.
> 
> tarantool> box.sql.execute("CREATE TABLE t (s1 INT, s2 INT,
>                           s3 INT, s4 INT PRIMARY KEY);")
> tarantool> t = box.sql.execute("INSERT INTO t VALUES (1,1,1,2),
>                              (1,1,1,5),(1,1,1,6);")
> - - [1, 1, 1, 2]
>    - [1, 1, 1, 5]
>    - [1, 1, 1, 6]
> 
> tarantool> t[3].S4
> 6
> 
> NetBox would no return tuple metadata:
> tarantool> t_cn[1].S4
> nul
> 
> Feature may be disabled with interactive_mode pragma:
> tarantool> box.sql.execute("pragma interactive_mode=0;")
> 
> Closes #2370.

Please, do not include 'Closes' into TarantoolBot request.

^ permalink raw reply	[flat|nested] 3+ messages in thread

* [tarantool-patches] Re: [PATCH v1 1/1] sql: return a tuple result from SQL
  2018-08-21 15:00 [tarantool-patches] [PATCH v1 1/1] sql: return a tuple result from SQL Kirill Shcherbatov
  2018-08-21 15:28 ` [tarantool-patches] " Vladislav Shpilevoy
@ 2018-08-27 11:02 ` n.pettik
  1 sibling, 0 replies; 3+ messages in thread
From: n.pettik @ 2018-08-27 11:02 UTC (permalink / raw)
  To: tarantool-patches; +Cc: Kirill Shcherbatov


> On 21 Aug 2018, at 18:00, Kirill Shcherbatov <kshcherbatov@tarantool.org> wrote:

Please, add to commit message details concerning implementation.
Now it looks very poor nevertheless feature itself is quite important.

> 
> @TarantoolBot document
> Title: Return a tuple result from SQL
> SQL insert/update/delete requests would return
> table with processed tuples.

Table? Only processed tuples are returned as I see.

> It is default behavior.
> 
> tarantool> box.sql.execute("CREATE TABLE t (s1 INT, s2 INT,
>                         s3 INT, s4 INT PRIMARY KEY);")
> tarantool> t = box.sql.execute("INSERT INTO t VALUES (1,1,1,2),
>                            (1,1,1,5),(1,1,1,6);")
> - - [1, 1, 1, 2]
>  - [1, 1, 1, 5]
>  - [1, 1, 1, 6]
> 
> tarantool> t[3].S4
> 6

I would add more informative description. For instance,
add example with triggers in order to show that even
implicitly inserted/deleted tuples will be returned.

Another question is - should we return deleted (for instance deleted
by triggers) tuples for inserts (and vice versa)? If not, I guess it deserves
to be noted in comments/docs.

> NetBox would no return tuple metadata:
> tarantool> t_cn[1].S4
> nul
> 
> Feature may be disabled with interactive_mode pragma:
> tarantool> box.sql.execute("pragma interactive_mode=0;")
> 
> Closes #2370.
> ---
> 95 files changed, 525 insertions(+), 108 deletions(-)
> delete mode 100644 test/box/sql-update-with-nested-select.result
> delete mode 100644 test/box/sql-update-with-nested-select.test.lua
> create mode 100644 test/sql/sql-update-with-nested-select.result
> create mode 100644 test/sql/sql-update-with-nested-select.test.lua

Why did you move this test within current patch?

> 
> diff --git a/src/box/execute.c b/src/box/execute.c
> index 24459b4..c41758e 100644
> --- a/src/box/execute.c
> +++ b/src/box/execute.c
> @@ -555,20 +555,30 @@ sql_execute(sqlite3 *db, struct sqlite3_stmt *stmt, struct port *port,
> 	    struct region *region)
> {
> 	int rc, column_count = sqlite3_column_count(stmt);
> -	if (column_count > 0) {
> -		/* Either ROW or DONE or ERROR. */
> -		while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
> -			if (sql_row_to_port(stmt, column_count, region,
> -					    port) != 0)
> -				return -1;
> +	if (column_count > 0 || sqlite3_tuple_result_require_flush(stmt)) {
> +		/* Either ROW or TUPLE or DONE or ERROR. */
> +		while (true) {
> +			rc = sqlite3_step(stmt);
> +			if (rc == SQLITE_ROW) {
> +				if (sql_row_to_port(stmt, column_count, region,
> +						    port) != 0)
> +					return -1;
> +			} else if (rc == SQLITE_TUPLE) {
> +				struct tuple *tuple =
> +					sqlite3_result_tuple(stmt);
> +				if (tuple != NULL &&
> +					port_tuple_add(port, tuple) != 0)
> +					return -1;
> +			} else {
> +				break;
> +			}
> 		}
> 		assert(rc == SQLITE_DONE || rc != SQLITE_OK);
> 	} else {
> -		/* No rows. Either DONE or ERROR. */
> -		rc = sqlite3_step(stmt);
> +		while ((rc = sqlite3_step(stmt)) == SQLITE_TUPLE);
> 		assert(rc != SQLITE_ROW && rc != SQLITE_OK);
> 	}
> -	if (rc != SQLITE_DONE) {
> +	if (rc != SQLITE_DONE && rc != SQLITE_TUPLE) {
> 		diag_set(ClientError, ER_SQL_EXECUTE, sqlite3_errmsg(db));
> 		return -1;
> 	}
> @@ -634,6 +644,15 @@ err:
> 			/* Failed port dump destroyes the port. */
> 			goto err;
> 		}
> +	} else if (sqlite3_tuple_result_require_flush(stmt)) {
> +		if (iproto_reply_array_key(out, 0, IPROTO_METADATA) != 0)
> +			goto err;
> +		keys = 2;
> +		if (iproto_reply_array_key(out, port_tuple->size,
> +					   IPROTO_DATA) != 0)
> +			goto err;
> +		if (port_dump_msgpack_16(&response->port, out) < 0)
> +			goto err;

This is almost duplicate of code in first ‘if’ branch.
Please, refactor this snippet.

Moreover, there’s some mess. You don’t fill <metadata> for iproto request.
Thus, in tests you always have empty metadata:

cn:execute('insert into test values (10, 11, NULL)')                               
---                                                                                
- metadata: []                                                                     
  rows:                                                                            
  - [10, 11, null]

It seems that it is impossible to set proper metadata for DML:
since ALL inserted/deleted tuples are returned, some of
them could have different formats.

Example is quite simple:

box.sql.execute("create table t1(id int primary key, b int)")
box.sql.execute("create table t2(id int primary key)")
box.sql.execute("create trigger tr1 insert on t1 begin insert into t2 values(new.id + 1); end;")
box.sql.execute("insert into t1 values(1, 2)")
---
- - [2]
  - [1, 2]
...

Besides, I looked at patch and to be honest I don’t like your approach.
Do you really need this OP_ResultTuple and SQLITE_TUPLE things?
Why can’t you utilise OP_ResultRow? *TUPLE* routine almost copies
one from ROW. Lets try to use already existing OP_ResultRow for this patch.

> 	} else {
> 		keys = 1;
> 		assert(port_tuple->size == 0);
> diff --git a/src/box/lua/sql.c b/src/box/lua/sql.c
> index 17e2694..c19d450 100644
> --- a/src/box/lua/sql.c
> +++ b/src/box/lua/sql.c
> @@ -6,6 +6,7 @@
> #include "box/info.h"
> #include "lua/utils.h"
> #include "info.h"
> +#include "tuple.h"
> 
> static void
> lua_push_column_names(struct lua_State *L, struct sqlite3_stmt *stmt)
> @@ -83,8 +84,9 @@ lua_sql_execute(struct lua_State *L)
> 
> 	int rc;
> 	int retval_count;
> -	if (sqlite3_column_count(stmt) == 0) {
> -		while ((rc = sqlite3_step(stmt)) == SQLITE_ROW);
> +	if (sqlite3_column_count(stmt) == 0 &&
> +	    !sqlite3_tuple_result_require_flush(stmt)) {
> +		while ((rc = sqlite3_step(stmt)) == SQLITE_TUPLE);
> 		retval_count = 0;
> 	} else {
> 		lua_newtable(L);
> @@ -94,9 +96,19 @@ lua_sql_execute(struct lua_State *L)
> 		lua_rawseti(L, -2, 0);
> 
> 		int row_count = 0;
> -		while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
> -			lua_push_row(L, stmt);
> -			lua_rawseti(L, -2, ++row_count);
> +		while (true) {
> +			rc = sqlite3_step(stmt);
> +			if (rc == SQLITE_ROW) {
> +				lua_push_row(L, stmt);
> +				lua_rawseti(L, -2, ++row_count);
> +			} else if (rc == SQLITE_TUPLE) {
> +				struct tuple *tuple =
> +					sqlite3_result_tuple(stmt);
> +				luaT_pushtupleornil(L, tuple);
> +				lua_rawseti(L, -2, ++row_count);
> +			} else {
> +				break;
> +			}
> 		}
> 		retval_count = 1;
> 	}
> diff --git a/src/box/sql.c b/src/box/sql.c
> index ae12cae..907dc86 100644
> --- a/src/box/sql.c
> +++ b/src/box/sql.c
> @@ -65,7 +65,8 @@ static const uint32_t default_sql_flags = SQLITE_ShortColNames
> 					  | SQLITE_EnableTrigger
> 					  | SQLITE_AutoIndex
> 					  | SQLITE_RecTriggers
> -					  | SQLITE_ForeignKeys;
> +					  | SQLITE_ForeignKeys
> +					  | SQLITE_InteractiveMode;

Please, for the new code avoid using SQLite prefixes.

> /*
> diff --git a/src/box/sql/build.c b/src/box/sql/build.c
> index dddeb12..76c683c 100644
> --- a/src/box/sql/build.c
> +++ b/src/box/sql/build.c
> @@ -2013,7 +2013,9 @@ vdbe_emit_stat_space_clear(struct Parse *parse, const char *stat_table_name,
> 	 * On memory allocation error sql_table delete_from
> 	 * releases memory for its own.
> 	 */
> +	bft is_interactive = parse->pVdbe->tuple_result_flush;
> 	sql_table_delete_from(parse, src_list, where);
> +	parse->pVdbe->tuple_result_flush = is_interactive;
> }
> 
> /**
> diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
> index 0be6883..ea22b4c 100644
> --- a/src/box/sql/delete.c
> +++ b/src/box/sql/delete.c
> @@ -34,6 +34,7 @@
> #include "box/schema.h"
> #include "sqliteInt.h"
> #include "tarantoolInt.h"
> +#include "vdbeInt.h"
> 
> struct Table *
> sql_list_lookup_table(struct Parse *parse, SrcList *src_list)
> @@ -551,6 +552,12 @@ sql_generate_row_delete(struct Parse *parse, struct Table *table,
> 		if (idx_noseek >= 0)
> 			sqlite3VdbeAddOp1(v, OP_Delete, idx_noseek);
> 
> +		struct session *user_session = current_session();
> +		if (user_session->sql_flags & SQLITE_InteractiveMode) {
> +			sqlite3VdbeAddOp1(v, OP_ResultTuple, cursor);
> +			v->tuple_result_flush = true;
> +		}
> +
> 		if (mode == ONEPASS_MULTI)
> 			p5 |= OPFLAG_SAVEPOSITION;
> 		sqlite3VdbeChangeP5(v, p5);
> diff --git a/src/box/sql/main.c b/src/box/sql/main.c
> index a9a0385..3bad568 100644
> --- a/src/box/sql/main.c
> +++ b/src/box/sql/main.c
> @@ -911,6 +911,9 @@ sqlite3ErrName(int rc)
> 		case SQLITE_ROW:
> 			zName = "SQLITE_ROW";
> 			break;
> +		case SQLITE_TUPLE:
> +			zName = "SQLITE_TUPLE";
> +			break;

It seems that sqlite3ErrName() is unused function, so it makes no sense
adding smth to it.

> 		case SQLITE_WARNING:
> 			zName = "SQLITE_WARNING";
> 			break;
> diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h
> index ecc9ee8..b4e32c1 100644
> --- a/src/box/sql/pragma.h
> +++ b/src/box/sql/pragma.h
> @@ -176,6 +176,11 @@ static const PragmaName aPragmaName[] = {
> 	 /* ColNames:  */ 13, 6,
> 	 /* iArg:      */ 1},
> #endif
> +	{ /* zName:     */ "interactive_mode",
> +	  /* ePragTyp:  */ PragTyp_FLAG,
> +	  /* ePragFlg:  */ PragFlg_Result0 | PragFlg_NoColumns1,
> +	  /* ColNames:  */ 0, 0,
> +	  /* iArg:      */ SQLITE_InteractiveMode},
> #if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_PARSER_TRACE)
> 	{ /* zName:     */ "parser_trace",
> 	 /* ePragTyp:  */ PragTyp_PARSER_TRACE,
> diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
> index b1f2f26..56c5037 100644
> --- a/src/box/sql/sqliteInt.h
> +++ b/src/box/sql/sqliteInt.h
> @@ -414,6 +414,8 @@ enum sql_ret_code {
> 	SQLITE_ROW,
> 	/** sqlite3_step() has finished executing. */
> 	SQLITE_DONE,
> +	/** sqlite3_step() has a ready tuple. */

What is ‘ready tuple’? Moreover, it is quite similar
to sqlite_row..

> +	SQLITE_TUPLE,
> };
> 
> void *
> @@ -565,8 +567,30 @@ sqlite3_prepare_v2(sqlite3 * db,	/* Database handle */
> 		   const char **pzTail	/* OUT: Pointer to unused portion of zSql */
> 	);
> 
> +/**
> + * Get last step tuple prepared with OP_ResultTupe.
> + * Valid when SQLITE_TUPLE returned with sqlite3_step.
> + *
> + * @param stmt Virtual database engine program.
> + * @retval tuple pointer.
> + */
> +struct tuple *
> +sqlite3_result_tuple(sqlite3_stmt *stmt);
> +
> +/**
> +* Execute one step of VDBE execution.
> +* @param stmt Virtual database engine program.
> +*
> +* @retval SQLITE_ROW On of rows of DQL request, or meta of DML -
> +*         'rows deleted', 'rows inserted' and etc. Result can be
> +*         accessed by columns using sqlite3_column_...().
> +* @retval SQLITE_TUPLE Inserted or updated tuple on DML.
> +* @retval SQLITE_ERROR Vdbe is terminated by an error.
> +* @retval SQLITE_DONE Vdbe successfully finished execution, and
> +*         can be finalized.
> +*/
> int
> -sqlite3_step(sqlite3_stmt *);
> +sqlite3_step(sqlite3_stmt *stmt);
> 
> const void *
> sqlite3_column_blob(sqlite3_stmt *, int iCol);
> @@ -710,6 +734,15 @@ sqlite3_aggregate_context(sqlite3_context *,
> int
> sqlite3_column_count(sqlite3_stmt * pStmt);
> 
> +/**
> + * Test, if compiled @stmt has data to be displayed to
> + * user.
> + * @param stmt Virtual database engine program.
> + * @retval true if any, false else

False otherwise.

> + */
> +bool
> +sqlite3_tuple_result_require_flush(struct sqlite3_stmt *stmt);

The same is here: don’t use sqlite3 prefix.

> +
> const char *
> sqlite3_column_name(sqlite3_stmt *, int N);
> 
> @@ -1609,6 +1642,8 @@ struct sqlite3 {
> #define SQLITE_VdbeAddopTrace 0x00001000	/* Trace sqlite3VdbeAddOp() calls */
> #define SQLITE_IgnoreChecks   0x00002000	/* Do not enforce check constraints */
> #define SQLITE_ReadUncommitted 0x0004000	/* For shared-cache mode */
> +/* Return operations results in SQL. */
> +#define SQLITE_InteractiveMode 0x40000000
> #define SQLITE_ReverseOrder   0x00020000	/* Reverse unordered SELECTs */
> #define SQLITE_RecTriggers    0x00040000	/* Enable recursive triggers */
> #define SQLITE_ForeignKeys    0x00080000	/* Enforce foreign key constraints  */
> @@ -1631,7 +1666,6 @@ struct sqlite3 {
> #define SQLITE_FactorOutConst 0x0008	/* Constant factoring */
> /*                not used    0x0010   // Was: SQLITE_IdxRealAsInt */
> #define SQLITE_DistinctOpt    0x0020	/* DISTINCT using indexes */
> -#define SQLITE_CoverIdxScan   0x0040	/* Covering index scans */

Redundant diff.

> #define SQLITE_OrderByIdxJoin 0x0080	/* ORDER BY of joins via index */
> #define SQLITE_SubqCoroutine  0x0100	/* Evaluate subqueries as coroutines */
> #define SQLITE_Transitive     0x0200	/* Transitive constraints */
> diff --git a/src/box/sql/tarantoolInt.h b/src/box/sql/tarantoolInt.h
> index 94517f6..7c76062 100644
> --- a/src/box/sql/tarantoolInt.h
> +++ b/src/box/sql/tarantoolInt.h
> @@ -62,9 +62,9 @@ int tarantoolSqlite3MovetoUnpacked(BtCursor * pCur, UnpackedRecord * pIdxKey,
> 				   int *pRes);
> int tarantoolSqlite3Count(BtCursor * pCur, i64 * pnEntry);
> int tarantoolSqlite3Insert(struct space *space, const char *tuple,
> -			   const char *tuple_end);
> +			   const char *tuple_end, struct tuple **result);
> int tarantoolSqlite3Replace(struct space *space, const char *tuple,
> -			    const char *tuple_end);
> +			    const char *tuple_end, struct tuple **result);
> int tarantoolSqlite3Delete(BtCursor * pCur, u8 flags);
> int
> sql_delete_by_key(struct space *space, char *key, uint32_t key_size);
> diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
> index dc5146f..b9ac519 100644
> --- a/src/box/sql/vdbe.c
> +++ b/src/box/sql/vdbe.c
> @@ -39,6 +39,7 @@
>  * in this file for details.  If in doubt, do not deviate from existing
>  * commenting and indentation practices when changing or adding code.
>  */
> +#include "box/tuple.h”

Nitpicking: AFAIK it is desirable to put headers in alphabetic order,
so put it before #include “box/txn.h"

> #include "box/box.h"
> #include "box/fkey.h"
> #include "box/txn.h"
> @@ -1364,6 +1365,25 @@ case OP_IntCopy: {            /* out2 */
> 	break;
> }
> 
> +/* Opcode: ResultTuple P1 * * * *
> + * Synopsis: output=tuple(cursor(P1))
> + *
> + * The register P1 is a cursor, from which a last tuple is
> + * returned. Sqlite3_step returns SQLITE_TUPLE.
> + */
> +case OP_ResultTuple: {
> +	VdbeCursor *cursor = p->apCsr[pOp->p1];
> +	assert(cursor != NULL);
> +	assert(cursor->eCurType == CURTYPE_TARANTOOL);
> +	if (cursor->uc.pCursor->last_tuple != NULL) {
> +		p->result_tuple = cursor->uc.pCursor->last_tuple;
> +		p->pc = (int)(pOp - aOp) + 1;
> +		rc = SQLITE_TUPLE;
> +		goto vdbe_return;
> +	}
> +	break;
> +}
> +
> /* Opcode: ResultRow P1 P2 * * *
>  * Synopsis: output=r[P1@P2]
>  *
> @@ -4262,16 +4282,26 @@ case OP_IdxInsert: {        /* in2 */
> 	} else {
> 		BtCursor *pBtCur = pC->uc.pCursor;
> 		if (pBtCur->curFlags & BTCF_TaCursor) {
> +			if (pBtCur->last_tuple != NULL)
> +				box_tuple_unref(pBtCur->last_tuple);
> +			pBtCur->last_tuple = NULL;
> +			struct tuple *result = NULL;
> 			/* Make sure that memory has been allocated on region. */
> 			assert(aMem[pOp->p2].flags & MEM_Ephem);
> 			if (pOp->opcode == OP_IdxInsert)
> 				rc = tarantoolSqlite3Insert(pBtCur->space,
> 							    pIn2->z,
> -							    pIn2->z + pIn2->n);
> +							    pIn2->z + pIn2->n,
> +							    &result);
> 			else
> 				rc = tarantoolSqlite3Replace(pBtCur->space,
> 							     pIn2->z,
> -							     pIn2->z + pIn2->n);
> +							     pIn2->z + pIn2->n,
> +							     &result);
> +			if (rc == SQLITE_OK) {

Lets move from SQLITE_OK macros and make insertOrReplace()
return straight result of box_process_rw().

> +				pBtCur->last_tuple = result;
> +				tuple_ref(result);
> +			}
> 		} else if (pBtCur->curFlags & BTCF_TEphemCursor) {
> 			rc = tarantoolSqlite3EphemeralInsert(pBtCur->space,
> 							     pIn2->z,
> @@ -4319,7 +4349,7 @@ case OP_SInsert: {
> 	struct space *space = space_by_id(pOp->p1);
> 	assert(space != NULL);
> 	assert(space_is_system(space));
> -	rc = tarantoolSqlite3Insert(space, pIn2->z, pIn2->z + pIn2->n);
> +	rc = tarantoolSqlite3Insert(space, pIn2->z, pIn2->z + pIn2->n, NULL);
> 	if (rc)
> 		goto abort_due_to_error;
> 	if (pOp->p5 & OPFLAG_NCHANGE)
> diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h
> index 2987d7a..5b9219a 100644
> --- a/src/box/sql/vdbe.h
> +++ b/src/box/sql/vdbe.h
> @@ -236,6 +236,9 @@ void sqlite3VdbeAppendP4(Vdbe *, void *pP4, int p4type);
> void
> sql_vdbe_set_p4_key_def(struct Parse *parse, struct Index *index);
> 

Firstly, you forgot about comment; secondly, this function is never used.

> +void
> +sql_vdbe_set_tuple_result_flush(struct Vdbe *vdbe, bool is_set);
> +
> VdbeOp *sqlite3VdbeGetOp(Vdbe *, int);
> int sqlite3VdbeMakeLabel(Vdbe *);
> void sqlite3VdbeRunOnlyOnce(Vdbe *);
> diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
> index ce97f49..eaa51b3 100644
> --- a/src/box/sql/vdbeInt.h
> +++ b/src/box/sql/vdbeInt.h
> @@ -376,7 +376,17 @@ struct Vdbe {
> 	Mem *aMem;		/* The memory locations */
> 	Mem **apArg;		/* Arguments to currently executing user function */
> 	Mem *aColName;		/* Column names to return */
> -	Mem *pResultSet;	/* Pointer to an array of results */
> +	union {
> +		/**
> +		 * Pointer to an array of results for SQLITE_ROW.
> +		 */
> +		struct Mem *pResultSet;
> +		/**
> +		 * Result tuple, returned by an iterator for
> +		 * SQLITE_ROW.

SQLITE_TUPLE?

> +		 */
> +		struct tuple *result_tuple;
> +	};
> 	char *zErrMsg;		/* Error message written here */
> 	VdbeCursor **apCsr;	/* One element of this array for each open cursor */
> 	Mem *aVar;		/* Values for the OP_Variable opcode. */
> @@ -393,6 +403,11 @@ struct Vdbe {
> 	bft changeCntOn:1;	/* True to update the change-counter */
> 	bft runOnlyOnce:1;	/* Automatically expire on reset */
> 	bft isPrepareV2:1;	/* True if prepared with prepare_v2() */
> +	/**
> +	 * Compiled SQL have OP_ResultTuple instructions that
> +	 * export tuples to be displayed in console.
> +	 */
> +	bft tuple_result_flush:1;

For predicates pls use ‘is_’ prefix.

> --- a/test/sql-tap/lua/sqltester.lua
> +++ b/test/sql-tap/lua/sqltester.lua
> @@ -433,6 +433,7 @@ box.cfg{
> 
> local engine = test_run and test_run:get_cfg('engine') or 'memtx'
> box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
> +box.sql.execute('pragma interactive_mode=0;')
> 
> function test.engine(self)
>     return engine
> diff --git a/test/sql/check-clear-ephemeral.result b/test/sql/check-clear-ephemeral.result
> index 4ab1fe1..7b1317b 100644
> --- a/test/sql/check-clear-ephemeral.result
> +++ b/test/sql/check-clear-ephemeral.result
> @@ -7,6 +7,9 @@ engine = test_run:get_cfg('engine')
> box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
> ---
> ...
> +box.sql.execute('pragma interactive_mode=0;’)

Can you avoid setting in each test this pragma?
I mean can you patch test config to set this pragma automatically?
Setting manually in each tests pragma seems to be unacceptable.

> diff --git a/test/sql/misc.test.lua b/test/sql/misc.test.lua
> index 1ed0198..a9f23c5 100644
> --- a/test/sql/misc.test.lua
> +++ b/test/sql/misc.test.lua
> @@ -1,6 +1,7 @@
> test_run = require('test_run').new()
> engine = test_run:get_cfg('engine')
> box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
> +box.sql.execute('pragma interactive_mode=0;')
> 
> -- Forbid multistatement queries.
> box.sql.execute('select 1;')
> @@ -11,3 +12,21 @@ box.sql.execute(';')
> box.sql.execute('')
> box.sql.execute('     ;')
> box.sql.execute('\n\n\n\t\t\t   ')
> +
> +--
> +-- gh-2370: Return a tuple result from SQL
> +--
> +box.sql.execute("pragma interactive_mode=1;")
> +box.sql.execute("CREATE TABLE t (s1 INT, s2 INT, s3 INT, s4 INT PRIMARY KEY);")
> +box.sql.execute("INSERT INTO t VALUES (1,1,1,2),(1,1,1,5),(1,1,1,6);")
> +box.sql.execute("UPDATE t SET s2=s2+s1 WHERE s4 IN (SELECT s4 FROM t);");
> +box.sql.execute("UPDATE t SET s2=s2+s1 WHERE s4=6 OR s4=2;”);

Add tests involving triggers pls.

> diff --git a/test/sql/persistency.result b/test/sql/persistency.result
> index c65baa0..8abb5cd 100644
> --- a/test/sql/persistency.result
> +++ b/test/sql/persistency.result
> @@ -10,6 +10,9 @@ engine = test_run:get_cfg('engine')
> box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
> ---
> ...
> +box.sql.execute('pragma interactive_mode=0;')
> +---
> +...
> -- create space
> box.sql.execute("CREATE TABLE foobar (foo PRIMARY KEY, bar)")
> ---
> @@ -180,6 +183,8 @@ box.sql.execute("SELECT \"name\", \"opts\" FROM \"_trigger\"");
> -- ... functional
> box.sql.execute("INSERT INTO foobar VALUES ('foobar trigger test', 8888)")
> ---
> +- - ['foobar trigger test', 8888]
> +  - ['trigger test', 9999]

Why this is trapped to output? 

^ permalink raw reply	[flat|nested] 3+ messages in thread

end of thread, other threads:[~2018-08-27 11:02 UTC | newest]

Thread overview: 3+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2018-08-21 15:00 [tarantool-patches] [PATCH v1 1/1] sql: return a tuple result from SQL Kirill Shcherbatov
2018-08-21 15:28 ` [tarantool-patches] " Vladislav Shpilevoy
2018-08-27 11:02 ` n.pettik

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox