[tarantool-patches] Re: [PATCH v1 1/1] sql: return a tuple result from SQL

["n.pettik" <korablev@tarantool.org>]

> 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?