[tarantool-patches] [PATCH v1 3/3] sql: do not use OP_Delete+OP_Insert for UPDATES

Kirill Shcherbatov kshcherbatov at tarantool.org
Wed Dec 19 18:37:25 MSK 2018


Introduced a new OP_Update opcode making Tarantool native Update
operation.
In case of UPDATE or REPLACE we can't use new OP_Update as it
has a complex SQL-specific semantics:

CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);
INSERT INTO tj VALUES (1, 3),(2, 4),(3,5);
CREATE UNIQUE INDEX i ON tj (s2);
SELECT * FROM tj;
[1, 3], [2, 4], [3, 5]
UPDATE OR REPLACE tj SET s2 = s2 + 1;
SELECT * FROM tj;
[1, 4], [3, 6]

I.e. [1, 3] tuple is updated as [1, 4] and have replaced tuple
[2, 4]. This logic is implemented as preventive tuples deletion
by all corresponding indexes in SQL.

The other significant change is forbidden primary key update.
It was possible to deal with it the same way like with or
REPLACE specifier but we need an atomic UPDATE step for #3691
ticket to support "or IGNORE/or ABORT/or FAIL" specifiers.
Reworked tests to make testing avoiding primary key UPDATE where
possible.

Closes #3850
---
 src/box/sql/delete.c                          |   3 +-
 src/box/sql/fkey.c                            |  49 +++---
 src/box/sql/insert.c                          |  28 +--
 src/box/sql/sqliteInt.h                       |  17 +-
 src/box/sql/update.c                          |  81 +++++++--
 src/box/sql/vdbe.c                            | 106 ++++++++++++
 src/box/sql/vdbeInt.h                         |   6 +
 src/box/sql/vdbemem.c                         |  36 ++++
 src/mpstream.c                                |  20 +++
 src/mpstream.h                                |   7 +
 test/sql-tap/alter2.test.lua                  |  16 +-
 test/sql-tap/analyze9.test.lua                |  43 ++++-
 test/sql-tap/bigrow1.test.lua                 |  12 +-
 test/sql-tap/check.test.lua                   |  14 +-
 test/sql-tap/fkey2.test.lua                   | 161 +++++++++---------
 test/sql-tap/fkey3.test.lua                   |  25 ++-
 test/sql-tap/fkey4.test.lua                   |  32 ++--
 test/sql-tap/gh2140-trans.test.lua            |  26 +--
 .../gh2250-trigger-chain-limit.test.lua       |   8 +-
 test/sql-tap/gh2259-in-stmt-trans.test.lua    |  12 +-
 test/sql-tap/identifier_case.test.lua         |   2 +-
 test/sql-tap/index6.test.lua                  |   5 +-
 test/sql-tap/intpkey.test.lua                 |  10 +-
 test/sql-tap/misc1.test.lua                   |  20 ++-
 test/sql-tap/quote.test.lua                   |   6 +-
 test/sql-tap/table.test.lua                   |   2 +-
 test/sql-tap/tkt-a8a0d2996a.test.lua          |   4 +-
 test/sql-tap/tkt2767.test.lua                 |  16 +-
 test/sql-tap/tkt2832.test.lua                 |  10 +-
 test/sql-tap/tkt3554.test.lua                 |  15 +-
 test/sql-tap/trigger2.test.lua                |  28 +--
 test/sql-tap/trigger7.test.lua                |   2 +-
 test/sql-tap/triggerB.test.lua                |  28 +--
 test/sql-tap/triggerC.test.lua                |  43 ++---
 test/sql-tap/triggerD.test.lua                |   4 +-
 test/sql-tap/update.test.lua                  |  31 ++--
 test/sql-tap/with1.test.lua                   |  24 +--
 test/sql/on-conflict.result                   |   8 +-
 test/sql/on-conflict.test.lua                 |   8 +-
 test/sql/row-count.result                     |  10 +-
 test/sql/row-count.test.lua                   |  10 +-
 test/sql/triggers.result                      |  16 +-
 test/sql/triggers.test.lua                    |  16 +-
 43 files changed, 658 insertions(+), 362 deletions(-)

diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index f9c42fdec..723ca534d 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -518,7 +518,8 @@ sql_generate_row_delete(struct Parse *parse, struct Table *table,
 		 * constraints attached to other tables) are not
 		 * violated by deleting this row.
 		 */
-		fkey_emit_check(parse, table, first_old_reg, 0, NULL);
+		fkey_emit_check(parse, table, first_old_reg, 0, NULL,
+				TK_DELETE);
 	}
 
 	/* Delete the index and table entries. Skip this step if
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index 4e3270f0c..44fa1291a 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -190,11 +190,12 @@
  * @param referenced_idx Id of referenced index.
  * @param reg_data Address of array containing child table row.
  * @param incr_count Increment constraint counter by this value.
+ * @param op operation, one of TK_UPDATE, TK_INSERT, TK_DELETE.
  */
 static void
 fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
 		   struct fkey_def *fk_def, uint32_t referenced_idx,
-		   int reg_data, int incr_count)
+		   int reg_data, int incr_count, int op)
 {
 	assert(incr_count == -1 || incr_count == 1);
 	struct Vdbe *v = sqlite3GetVdbe(parse_context);
@@ -221,14 +222,6 @@ fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
 		sqlite3VdbeAddOp2(v, OP_IsNull, reg, ok_label);
 	}
 	uint32_t field_count = fk_def->field_count;
-	int temp_regs = sqlite3GetTempRange(parse_context, field_count);
-	int rec_reg = sqlite3GetTempReg(parse_context);
-	vdbe_emit_open_cursor(parse_context, cursor, referenced_idx, parent);
-	link = fk_def->links;
-	for (uint32_t i = 0; i < field_count; ++i, ++link) {
-		sqlite3VdbeAddOp2(v, OP_Copy, link->child_field + 1 + reg_data,
-				  temp_regs + i);
-	}
 	/*
 	 * If the parent table is the same as the child table, and
 	 * we are about to increment the constraint-counter (i.e.
@@ -253,15 +246,29 @@ fkey_lookup_parent(struct Parse *parse_context, struct space *parent,
 		}
 		sqlite3VdbeGoto(v, ok_label);
 	}
-	struct index *idx = space_index(parent, referenced_idx);
-	assert(idx != NULL);
-	sqlite3VdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count, rec_reg,
-			  sql_space_index_affinity_str(parse_context->db,
-						       parent->def, idx->def),
-			  P4_DYNAMIC);
-	sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0);
-	sqlite3ReleaseTempReg(parse_context, rec_reg);
-	sqlite3ReleaseTempRange(parse_context, temp_regs, field_count);
+	if (!(fkey_is_self_referenced(fk_def) && op == TK_UPDATE)) {
+		int temp_regs = sqlite3GetTempRange(parse_context, field_count);
+		int rec_reg = sqlite3GetTempReg(parse_context);
+		vdbe_emit_open_cursor(parse_context, cursor, referenced_idx,
+				      parent);
+		link = fk_def->links;
+		for (uint32_t i = 0; i < field_count; ++i, ++link) {
+			sqlite3VdbeAddOp2(v, OP_Copy,
+					  link->child_field + 1 + reg_data,
+					  temp_regs + i);
+		}
+		struct index *idx = space_index(parent, referenced_idx);
+		assert(idx != NULL);
+		sqlite3VdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count,
+				  rec_reg,
+				  sql_space_index_affinity_str(parse_context->db,
+							       parent->def,
+							       idx->def),
+				  P4_DYNAMIC);
+		sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0);
+		sqlite3ReleaseTempReg(parse_context, rec_reg);
+		sqlite3ReleaseTempRange(parse_context, temp_regs, field_count);
+	}
 	struct session *session = current_session();
 	if (!fk_def->is_deferred &&
 	    (session->sql_flags & SQLITE_DeferFKs) == 0 &&
@@ -515,7 +522,7 @@ fkey_action_is_set_null(struct Parse *parse_context, const struct fkey *fkey)
 
 void
 fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
-		int reg_new, const int *changed_cols)
+		int reg_new, const int *changed_cols, int op)
 {
 	struct sqlite3 *db = parser->db;
 	struct session *user_session = current_session();
@@ -549,7 +556,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
 			 * foreign key constraint violation.
 			 */
 			fkey_lookup_parent(parser, parent, fk_def, fk->index_id,
-					   reg_old, -1);
+					   reg_old, -1, op);
 		}
 		if (reg_new != 0 && !fkey_action_is_set_null(parser, fk)) {
 			/*
@@ -568,7 +575,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
 			 * cause an FK violation.
 			 */
 			fkey_lookup_parent(parser, parent, fk_def, fk->index_id,
-					   reg_new, +1);
+					   reg_new, +1, op);
 		}
 	}
 	/*
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 1b02ea907..de240c409 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -760,10 +760,9 @@ sqlite3Insert(Parse * pParse,	/* Parser context */
 		 */
 		vdbe_emit_constraint_checks(pParse, pTab, regIns + 1,
 					    on_error, endOfLoop, 0);
-		fkey_emit_check(pParse, pTab, 0, regIns, 0);
-		vdbe_emit_insertion_completion(v, space, regIns + 1,
-					       pTab->def->field_count,
-					       on_error);
+		fkey_emit_check(pParse, pTab, 0, regIns, 0, TK_INSERT);
+		vdbe_emit_insertion_completion(v, space, OP_IdxInsert,
+					       regIns + 1, 0, 0, on_error);
 	}
 
 	/* Update the count of rows that are inserted
@@ -1053,8 +1052,9 @@ process_index:  ;
 }
 
 void
-vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space,
-			       int raw_data_reg, uint32_t tuple_len,
+vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, int op,
+			       int raw_data_reg, int raw_key_reg,
+			       int upd_cols_reg,
 			       enum on_conflict_action on_conflict)
 {
 	assert(v != NULL);
@@ -1065,10 +1065,18 @@ vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space,
 		pik_flags |= OPFLAG_OE_FAIL;
 	else if (on_conflict == ON_CONFLICT_ACTION_ROLLBACK)
 		pik_flags |= OPFLAG_OE_ROLLBACK;
-	sqlite3VdbeAddOp3(v, OP_MakeRecord, raw_data_reg, tuple_len,
-			  raw_data_reg + tuple_len);
-	sqlite3VdbeAddOp1(v, OP_IdxInsert, raw_data_reg + tuple_len);
-	sqlite3VdbeChangeP4(v, -1, (char *)space, P4_SPACEPTR);
+	if (op == OP_IdxInsert) {
+		uint32_t tuple_raw_reg = raw_data_reg + space->def->field_count;
+		sqlite3VdbeAddOp3(v, OP_MakeRecord, raw_data_reg,
+				  space->def->field_count, tuple_raw_reg);
+		sqlite3VdbeAddOp1(v, op, tuple_raw_reg);
+		sqlite3VdbeChangeP4(v, -1, (char *)space, P4_SPACEPTR);
+	} else if (op == OP_IdxUpdate) {
+		sqlite3VdbeAddOp4(v, op, raw_data_reg, raw_key_reg,
+				  upd_cols_reg, (char *)space, P4_SPACEPTR);
+	} else {
+		unreachable();
+	}
 	sqlite3VdbeChangeP5(v, pik_flags);
 }
 
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 1ec52b875..f0cc32229 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -3865,13 +3865,21 @@ vdbe_emit_constraint_checks(struct Parse *parse_context,
  *
  * @param v Virtual database engine.
  * @param space Pointer to space object.
+ * @param op VDBE opcode OP_IdxInsert for INSERT or OP_IdxUpdate
+ *           for UPDATE operation.
  * @param raw_data_reg Register with raw data to insert.
- * @param tuple_len Number of registers to hold the tuple.
+ * @param raw_key_reg (when op == OP_IdxUpdate) Register with key
+ *                    for lookup by primary index.
+ * @param upd_cols_reg (when op == OP_IdxUpdate) Register with
+ *                      blob of updated fields numbers.
+ *                      Read OP_IdxUpdate opcode comments for
+ *                      more details.
  * @param on_conflict On conflict action.
  */
 void
-vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space,
-			       int raw_data_reg, uint32_t tuple_len,
+vdbe_emit_insertion_completion(struct Vdbe *v, struct space *space, int op,
+			       int raw_data_reg, int raw_key_reg,
+			       int upd_cols_reg,
 			       enum on_conflict_action on_conflict);
 
 void
@@ -4730,10 +4738,11 @@ void sqlite3WithPush(Parse *, With *, u8);
  * @param reg_old Register with deleted row.
  * @param reg_new Register with inserted row.
  * @param changed_cols Array of updated columns. Can be NULL.
+ * @param op operation, one of TK_UPDATE, TK_INSERT, TK_DELETE.
  */
 void
 fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old,
-		int reg_new, const int *changed_cols);
+		int reg_new, const int *changed_cols, int op);
 
 /**
  * Emit VDBE code to do CASCADE, SET NULL or SET DEFAULT actions
diff --git a/src/box/sql/update.c b/src/box/sql/update.c
index 0e2d0fde8..aca8e4adc 100644
--- a/src/box/sql/update.c
+++ b/src/box/sql/update.c
@@ -36,6 +36,7 @@
 #include "sqliteInt.h"
 #include "box/session.h"
 #include "tarantoolInt.h"
+#include "box/tuple_format.h"
 #include "box/schema.h"
 
 void
@@ -112,6 +113,8 @@ sqlite3Update(Parse * pParse,		/* The parser context */
 	int regNew = 0;		/* Content of the NEW.* table in triggers */
 	int regOld = 0;		/* Content of OLD.* table in triggers */
 	int regKey = 0;		/* composite PRIMARY KEY value */
+	/* Count of changed rows. Match aXRef items != -1. */
+	int upd_cols_cnt = 0;
 
 	db = pParse->db;
 	if (pParse->nErr || db->mallocFailed) {
@@ -183,6 +186,7 @@ sqlite3Update(Parse * pParse,		/* The parser context */
 					goto update_cleanup;
 				}
 				aXRef[j] = i;
+				upd_cols_cnt++;
 				break;
 			}
 		}
@@ -216,7 +220,7 @@ sqlite3Update(Parse * pParse,		/* The parser context */
 		regNewPk = ++pParse->nMem;
 	}
 	regNew = pParse->nMem + 1;
-	pParse->nMem += def->field_count;
+	pParse->nMem += def->field_count + 1;
 
 	/* If we are trying to update a view, realize that view into
 	 * an ephemeral table.
@@ -430,23 +434,64 @@ sqlite3Update(Parse * pParse,		/* The parser context */
 		vdbe_emit_constraint_checks(pParse, pTab, regNewPk + 1,
 					    on_error, labelContinue, aXRef);
 		/* Do FK constraint checks. */
-		if (hasFK)
-			fkey_emit_check(pParse, pTab, regOldPk, 0, aXRef);
-		/*
-		 * Delete the index entries associated with the
-		 * current record. It can be already removed by
-		 * trigger or REPLACE conflict action.
-		 */
-		int addr1 = sqlite3VdbeAddOp4Int(v, OP_NotFound, pk_cursor, 0,
-						 regKey, nKey);
-		assert(regNew == regNewPk + 1);
-		sqlite3VdbeAddOp2(v, OP_Delete, pk_cursor, 0);
-		sqlite3VdbeJumpHere(v, addr1);
-		if (hasFK)
-			fkey_emit_check(pParse, pTab, 0, regNewPk, aXRef);
-		vdbe_emit_insertion_completion(v, space, regNew,
-					       pTab->def->field_count,
-					       on_error);
+		if (hasFK) {
+			fkey_emit_check(pParse, pTab, regOldPk, 0, aXRef,
+					TK_UPDATE);
+		}
+		if (on_error == ON_CONFLICT_ACTION_REPLACE) {
+			/*
+			 * Delete the index entries associated with the
+			 * current record. It can be already removed by
+			 * trigger or REPLACE conflict action.
+			 */
+			int not_found_lbl =
+				sqlite3VdbeAddOp4Int(v, OP_NotFound, pk_cursor,
+						     0, regKey, nKey);
+			assert(regNew == regNewPk + 1);
+			sqlite3VdbeAddOp2(v, OP_Delete, pk_cursor, 0);
+			sqlite3VdbeJumpHere(v, not_found_lbl);
+		}
+		if (hasFK) {
+			fkey_emit_check(pParse, pTab, 0, regNewPk, aXRef,
+					TK_UPDATE);
+		}
+		if (on_error == ON_CONFLICT_ACTION_REPLACE) {
+			vdbe_emit_insertion_completion(v, space, OP_IdxInsert,
+						       regNew, 0, 0, on_error);
+		} else {
+			int key_reg;
+			if (okOnePass) {
+				key_reg = sqlite3GetTempReg(pParse);
+				const char *zAff =
+					sql_space_index_affinity_str(pParse->db,
+								     def,
+								     pPk->def);
+				sqlite3VdbeAddOp4(v, OP_MakeRecord, iPk,
+						  pk_part_count, key_reg, zAff,
+						  pk_part_count);
+			} else {
+				assert(nKey == 0);
+				key_reg = regKey;
+			}
+
+			/* Prapare array of changed fields. */
+			uint32_t upd_cols_sz = upd_cols_cnt * sizeof(uint32_t);
+			uint32_t *upd_cols = sqlite3DbMallocRaw(db, upd_cols_sz);
+			if (upd_cols == NULL)
+				goto update_cleanup;
+			upd_cols_cnt = 0;
+			for (uint32_t i = 0; i < def->field_count; i++) {
+				if (aXRef[i] == -1)
+					continue;
+				upd_cols[upd_cols_cnt++] = i;
+			}
+			int upd_cols_reg = sqlite3GetTempReg(pParse);
+			sqlite3VdbeAddOp4(v, OP_Blob, upd_cols_sz, upd_cols_reg,
+					0, (const char *)upd_cols, P4_DYNAMIC);
+			vdbe_emit_insertion_completion(v, space, OP_IdxUpdate,
+						       regNew, key_reg,
+						       upd_cols_reg, on_error);
+		}
 		/*
 		 * Do any ON CASCADE, SET NULL or SET DEFAULT
 		 * operations required to handle rows that refer
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index bf6f4cdd1..534d3e885 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -44,11 +44,13 @@
 #include "box/fkey.h"
 #include "box/txn.h"
 #include "box/session.h"
+#include "box/tuple_format.h"
 #include "sqliteInt.h"
 #include "vdbeInt.h"
 #include "tarantoolInt.h"
 
 #include "msgpuck/msgpuck.h"
+#include "mpstream.h"
 
 #include "box/schema.h"
 #include "box/space.h"
@@ -620,6 +622,13 @@ vdbe_add_new_autoinc_id(struct Vdbe *vdbe, int64_t id)
 	return 0;
 }
 
+/** Callback to forward and error from mpstream methods. */
+static inline void
+mpstream_encode_error(void *error_ctx)
+{
+	*(bool *)error_ctx = true;
+}
+
 /*
  * Execute as much of a VDBE program as we can.
  * This is the core of sqlite3_step().
@@ -4456,6 +4465,103 @@ case OP_IdxInsert: {
 		goto abort_due_to_error;
 	break;
 }
+/* Opcode: OP_IdxUpdate P1 P2 P3 P4 P5
+ * Synopsis: key=r[P1]
+ *
+ * Make space Update operation. Primary key fields could not be
+ * modified - use OP_Replace opcode instead.
+ * This opcode extends IdxInsert/IdxReplace interface to
+ * transform to OP_Replace in particular case of sql operator
+ * "UPDATE or REPLACE".
+ *
+ * @param P1 The first field of updated tuple.
+ * @param P2 Index of a register with index key MakeRecord.
+ * @param P3 Index of a register with upd_fields blob.
+ *           This blob has size sizeof(uint32_t)*upd_fields_cnt.
+ *           It's items are numbers of fields to be replaced with
+ *           new values from P1 blob. They must be sorted in
+ *           ascending order.
+ * @param P4 Pointer to the struct space to be updated.
+ * @param P5 Flags. If P5 contains OPFLAG_NCHANGE, then VDBE
+ *           accounts the change in a case of successful
+ *           insertion in nChange counter. If P5 contains
+ *           OPFLAG_OE_IGNORE, then we are processing INSERT OR
+ *           INGORE statement. Thus, in case of conflict we don't
+ *           raise an error.
+ */
+case OP_IdxUpdate: {
+	struct Mem *new_tuple = &aMem[pOp->p1];
+	if (pOp->p5 & OPFLAG_NCHANGE)
+		p->nChange++;
+
+	struct space *space = pOp->p4.space;
+	assert(pOp->p4type == P4_SPACEPTR);
+
+	struct Mem *key_mem = &aMem[pOp->p2];
+	assert((key_mem->flags & MEM_Blob) != 0);
+
+	struct Mem *upd_fields_mem = &aMem[pOp->p3];
+	assert((upd_fields_mem->flags & MEM_Blob) != 0);
+	uint32_t *upd_fields = (uint32_t *)upd_fields_mem->z;
+	uint32_t upd_fields_cnt = upd_fields_mem->n / sizeof(uint32_t);
+
+	/* Prepare Tarantool update ops msgpack. */
+	struct region *region = &fiber()->gc;
+	size_t used = region_used(region);
+	bool is_error = false;
+	struct mpstream stream;
+	mpstream_init(&stream, region, region_reserve_cb, region_alloc_cb,
+		      mpstream_encode_error, &is_error);
+	mpstream_encode_array(&stream, upd_fields_cnt);
+	for (uint32_t i = 0; i < upd_fields_cnt; i++) {
+		uint32_t field_idx = upd_fields[i];
+		assert(field_idx < space->def->field_count);
+		mpstream_encode_array(&stream, 3);
+		mpstream_encode_strn(&stream, "=", 1);
+		mpstream_encode_uint(&stream, field_idx + 1);
+		mpstream_encode_vdbe_mem(&stream, new_tuple + field_idx);
+	}
+	mpstream_flush(&stream);
+	if (is_error) {
+		diag_set(OutOfMemory, stream.pos - stream.buf,
+			"mpstream_flush", "stream");
+		rc = SQL_TARANTOOL_ERROR;
+		goto abort_due_to_error;
+	}
+	uint32_t ops_size = region_used(region) - used;
+	const char *ops = region_join(region, ops_size);
+	if (ops == NULL) {
+		diag_set(OutOfMemory, ops_size, "region_join", "raw");
+		rc = SQL_TARANTOOL_ERROR;
+		goto abort_due_to_error;
+	}
+
+	rc = SQLITE_OK;
+	if (box_update(space->def->id, 0, key_mem->z, key_mem->z + key_mem->n,
+		       ops, ops + ops_size, TUPLE_INDEX_BASE, NULL) != 0)
+		rc = SQL_TARANTOOL_ERROR;
+
+	if (pOp->p5 & OPFLAG_OE_IGNORE) {
+		/*
+		 * Ignore any kind of failes and do not raise
+		 * error message
+		 */
+		rc = SQLITE_OK;
+		/*
+		 * If we are in trigger, increment ignore raised
+		 * counter.
+		 */
+		if (p->pFrame)
+			p->ignoreRaised++;
+	} else if (pOp->p5 & OPFLAG_OE_FAIL) {
+		p->errorAction = ON_CONFLICT_ACTION_FAIL;
+	} else if (pOp->p5 & OPFLAG_OE_ROLLBACK) {
+		p->errorAction = ON_CONFLICT_ACTION_ROLLBACK;
+	}
+	if (rc != 0)
+		goto abort_due_to_error;
+	break;
+}
 
 /* Opcode: SInsert P1 P2 P3 * P5
  * Synopsis: space id = P1, key = r[P3], on error goto P2
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index 50bc35b2b..69898bbcf 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -552,4 +552,10 @@ int sqlite3VdbeRecordCompareMsgpack(const void *key1,
 				    struct UnpackedRecord *key2);
 u32 sqlite3VdbeMsgpackGet(const unsigned char *buf, Mem * pMem);
 
+struct mpstream;
+
+/** Perform encoding memory variable to stream. */
+void
+mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var);
+
 #endif				/* !defined(SQLITE_VDBEINT_H) */
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index 22beba8be..1fee2673d 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -40,6 +40,7 @@
 #include "vdbeInt.h"
 #include "tarantoolInt.h"
 #include "box/schema.h"
+#include "mpstream.h"
 
 #ifdef SQLITE_DEBUG
 /*
@@ -1714,3 +1715,38 @@ sqlite3ValueBytes(sqlite3_value * pVal)
 		return 0;
 	return valueBytes(pVal);
 }
+
+void
+mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
+{
+	assert(memIsValid(var));
+	if (var->flags & MEM_Null) {
+		mpstream_encode_nil(stream);
+	} else if (var->flags & MEM_Real) {
+		mpstream_encode_double(stream, var->u.r);
+	} else if (var->flags & MEM_Int) {
+		if (var->u.i >= 0) {
+			mpstream_encode_uint(stream, var->u.i);
+		} else {
+			mpstream_encode_int(stream, var->u.i);
+		}
+	} else if (var->flags & MEM_Str) {
+		mpstream_encode_strn(stream, var->z, var->n);
+	} else if (var->flags & MEM_Bool) {
+		mpstream_encode_bool(stream, var->u.b);
+	} else {
+		/* Emit BIN header iff the BLOB doesn't store MsgPack content */
+		if ((var->flags & MEM_Subtype) == 0 ||
+		     var->subtype != SQL_SUBTYPE_MSGPACK) {
+			uint32_t binl = var->n +
+					((var->flags & MEM_Zero) ?
+					var->u.nZero : 0);
+			mpstream_encode_binl(stream, binl);
+		}
+		mpstream_encode_raw(stream, var->z, var->n);
+		if (var->flags & MEM_Zero) {
+			for (int i = 0; i < var->u.nZero; i ++)
+				mpstream_encode_nil(stream);
+		}
+	}
+}
diff --git a/src/mpstream.c b/src/mpstream.c
index e4f7950ba..843fc959e 100644
--- a/src/mpstream.c
+++ b/src/mpstream.c
@@ -175,3 +175,23 @@ mpstream_encode_bool(struct mpstream *stream, bool val)
     char *pos = mp_encode_bool(data, val);
     mpstream_advance(stream, pos - data);
 }
+
+void
+mpstream_encode_raw(struct mpstream *stream, const char *raw, uint32_t len)
+{
+	char *data = mpstream_reserve(stream, len);
+	if (data == NULL)
+		return;
+	memcpy(data, raw, len);
+	mpstream_advance(stream, len);
+}
+
+void
+mpstream_encode_binl(struct mpstream *stream, uint32_t len)
+{
+    char *data = mpstream_reserve(stream, mp_sizeof_binl(len));
+	if (data == NULL)
+		return;
+    mp_encode_binl(data, len);
+    mpstream_advance(stream, len);
+}
diff --git a/src/mpstream.h b/src/mpstream.h
index e22d05241..600ab5bec 100644
--- a/src/mpstream.h
+++ b/src/mpstream.h
@@ -133,6 +133,13 @@ mpstream_encode_nil(struct mpstream *stream);
 void
 mpstream_encode_bool(struct mpstream *stream, bool val);
 
+void
+mpstream_encode_binl(struct mpstream *stream, uint32_t len);
+
+/** Copies data blob raw of length len directly to stream. */
+void
+mpstream_encode_raw(struct mpstream *stream, const char *raw, uint32_t len);
+
 #if defined(__cplusplus)
 } /* extern "C" */
 #endif /* defined(__cplusplus) */
diff --git a/test/sql-tap/alter2.test.lua b/test/sql-tap/alter2.test.lua
index d13cfb7a0..e1dd0ff1b 100755
--- a/test/sql-tap/alter2.test.lua
+++ b/test/sql-tap/alter2.test.lua
@@ -204,13 +204,13 @@ test:do_execsql_test(
     [[
         DROP TABLE child;
         DROP TABLE parent;
-        CREATE TABLE child (id INT PRIMARY KEY, a INT, b INT);
-        CREATE TABLE parent (id INT PRIMARY KEY, c INT, d INT);
-        ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent ON UPDATE CASCADE MATCH PARTIAL;
-        INSERT INTO parent VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8);
-        INSERT INTO child VALUES(1, 1, 1), (3, 2, 2);
+        CREATE TABLE child (id INT UNIQUE, a INT, b INT, z INT PRIMARY KEY AUTOINCREMENT);
+        CREATE TABLE parent (id INT UNIQUE, c INT, d INT, z INT PRIMARY KEY AUTOINCREMENT);
+        ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY (id) REFERENCES parent(id) ON UPDATE CASCADE MATCH PARTIAL;
+        INSERT INTO parent(id, c, d) VALUES(1, 2, 3), (3, 4, 5), (6, 7, 8);
+        INSERT INTO child(id, a, b) VALUES(1, 1, 1), (3, 2, 2);
         UPDATE parent SET id = 5 WHERE id = 1;
-        SELECT * FROM CHILD;
+        SELECT id,a,b FROM CHILD ORDER BY id,a,b;
     ]], {
         -- <alter2-3.2>
         3, 2, 2, 5, 1, 1
@@ -220,7 +220,7 @@ test:do_execsql_test(
 test:do_catchsql_test(
     "alter2-4.1",
     [[
-        ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES child;
+        ALTER TABLE child ADD CONSTRAINT fk FOREIGN KEY REFERENCES child(id);
     ]], {
         -- <alter2-4.1>
         1, "near \"REFERENCES\": syntax error"
@@ -230,7 +230,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "alter2-4.2",
     [[
-        ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES child;
+        ALTER TABLE child ADD CONSTRAINT fk () FOREIGN KEY REFERENCES child(id);
     ]], {
         -- <alter2-4.1>
         1, "near \"(\": syntax error"
diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua
index 585e91851..df62a1624 100755
--- a/test/sql-tap/analyze9.test.lua
+++ b/test/sql-tap/analyze9.test.lua
@@ -405,6 +405,27 @@ test:do_execsql_test(
 -- The following tests experiment with adding corrupted records to the
 -- 'sample' column of the _sql_stat4 table.
 --
+local get_pk = function (space, record)
+    local pkey = {}
+    for _, part in pairs(space.index[0].parts) do
+        table.insert(pkey, record[part.fieldno])
+    end
+    return pkey
+end
+
+local inject_stat_error_func = function (space_name)
+    local space = box.space[space_name]
+    local record = space:select({"T1", "I1", nil}, {limit = 1})[1]
+    space:delete(get_pk(space, record))
+    local record_new = {}
+    for i = 1,#record-1 do record_new[i] = record[i] end
+    record_new[#record] = ''
+    space:insert(record_new)
+    return 0
+end
+
+box.internal.sql_create_function("inject_stat_error", "INT", inject_stat_error_func)
+
 test:do_execsql_test(
     7.1,
     [[
@@ -417,8 +438,7 @@ test:do_execsql_test(
         INSERT INTO t1 VALUES(null, 4, 4);
         INSERT INTO t1 VALUES(null, 5, 5);
         ANALYZE;
-        UPDATE "_sql_stat4" SET "sample" = '' WHERE "sample" =
-            (SELECT "sample" FROM "_sql_stat4" WHERE "tbl" = 't1' AND "idx" = 'i1' LIMIT 1);
+        SELECT inject_stat_error('_sql_stat4');
         ANALYZE;
     ]])
 
@@ -1050,11 +1070,28 @@ test:do_execsql_test(
         -- </15.4>
     })
 
+local inject_stat_error_func = function (space_name)
+    local space = box.space[space_name]
+    local stats = space:select()
+    for _, stat in pairs(stats) do
+        space:delete(get_pk(space, stat))
+        local new_tuple = {"no such tbl"}
+        for i=2,#stat do
+            table.insert(new_tuple, stat[i])
+        end
+        space:insert(new_tuple)
+    end
+    return 0
+end
+
+box.internal.sql_create_function("inject_stat_error", "INT", inject_stat_error_func)
+
+
 test:do_execsql_test(
     15.7,
     [[
         ANALYZE;
-        UPDATE "_sql_stat1" SET "tbl" = 'no such tbl';
+        SELECT inject_stat_error('_sql_stat1');
     ]])
 
 test:do_execsql_test(
diff --git a/test/sql-tap/bigrow1.test.lua b/test/sql-tap/bigrow1.test.lua
index 43c0d0b67..e69ddf136 100755
--- a/test/sql-tap/bigrow1.test.lua
+++ b/test/sql-tap/bigrow1.test.lua
@@ -40,7 +40,7 @@ test:do_test(
 test:do_execsql_test(
     "bigrow-1.1",
     [[
-        CREATE TABLE t1(a text primary key, b text, c text);
+        CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, a TEXT UNIQUE, b TEXT, c TEXT);
         --SELECT name FROM sqlite_master
         --  WHERE type='table' OR type='index'
         --  ORDER BY name
@@ -55,7 +55,7 @@ test:do_test(
     "bigrow-1.2",
     function()
         big1 = string.sub(bigstr, 1, 65519 + 1)
-        local sql = "INSERT INTO t1 VALUES('abc',"
+        local sql = "INSERT INTO t1(a,b,c) VALUES('abc',"
         sql = sql .. "'"..big1.."', 'xyz');"
         test:execsql(sql)
         return test:execsql "SELECT a, c FROM t1"
@@ -80,7 +80,7 @@ test:do_test(
     "bigrow-1.4",
     function()
         big2 = string.sub(bigstr, 1, 65520+1)
-        local sql = "INSERT INTO t1 VALUES('abc2',"
+        local sql = "INSERT INTO t1(a,b,c) VALUES('abc2',"
         sql = sql .. "'"..big2.."', 'xyz2');"
         return test:catchsql(sql)
     end, {
@@ -134,7 +134,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "bigrow-1.6",
     [[
-        SELECT * FROM t1
+        SELECT a,b,c FROM t1
     ]], {
         -- <bigrow-1.6>
         big1, "abc", "xyz"
@@ -144,8 +144,8 @@ test:do_execsql_test(
 test:do_execsql_test(
     "bigrow-1.7",
     [[
-        INSERT INTO t1 VALUES('1','2','3');
-        INSERT INTO t1 VALUES('A','B','C');
+        INSERT INTO t1(a,b,c) VALUES('1','2','3');
+        INSERT INTO t1(a,b,c) VALUES('A','B','C');
         SELECT b FROM t1 WHERE a=='1';
     ]], {
         -- <bigrow-1.7>
diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua
index c24ae2ff1..1f369fb02 100755
--- a/test/sql-tap/check.test.lua
+++ b/test/sql-tap/check.test.lua
@@ -420,7 +420,7 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "check-4.1",
     [[
-        CREATE TABLE t4(x  INT primary key, y INT ,
+        CREATE TABLE t4(x INT UNIQUE, y INT, z INT PRIMARY KEY
           CHECK (
                x+y==11
             OR x*y==12
@@ -437,8 +437,8 @@ test:do_execsql_test(
 test:do_execsql_test(
     "check-4.2",
     [[
-        INSERT INTO t4 VALUES(1,10);
-        SELECT * FROM t4
+        INSERT INTO t4 VALUES(1,10,1);
+        SELECT x,y FROM t4
     ]], {
         -- <check-4.2>
         1, 10
@@ -449,7 +449,7 @@ test:do_execsql_test(
     "check-4.3",
     [[
         UPDATE t4 SET x=4, y=3;
-        SELECT * FROM t4
+        SELECT x,y FROM t4
     ]], {
         -- <check-4.3>
         4, 3
@@ -460,7 +460,7 @@ test:do_execsql_test(
     "check-4.4",
     [[
         UPDATE t4 SET x=12, y=2;
-        SELECT * FROM t4
+        SELECT x,y FROM t4
     ]], {
         -- <check-4.4>
         12, 2
@@ -471,7 +471,7 @@ test:do_execsql_test(
     "check-4.5",
     [[
         UPDATE t4 SET x=12, y=-22;
-        SELECT * FROM t4
+        SELECT x,y FROM t4
     ]], {
         -- <check-4.5>
         12, -22
@@ -491,7 +491,7 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "check-4.7",
     [[
-        SELECT * FROM t4;
+        SELECT x,y FROM t4;
     ]], {
         -- <check-4.7>
         12, -22
diff --git a/test/sql-tap/fkey2.test.lua b/test/sql-tap/fkey2.test.lua
index 61db29f32..03bf025f3 100755
--- a/test/sql-tap/fkey2.test.lua
+++ b/test/sql-tap/fkey2.test.lua
@@ -7,15 +7,14 @@ test:plan(116)
 test:do_execsql_test(
     "fkey2-1.1",
     [[
-        CREATE TABLE t1(a  INT PRIMARY KEY, b INT );
-        CREATE TABLE t2(c  INT PRIMARY KEY REFERENCES t1(a), d INT);
+        CREATE TABLE t1(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+        CREATE TABLE t2(c INT UNIQUE REFERENCES t1(a), d INT, id INT PRIMARY KEY AUTOINCREMENT);
 
-        CREATE TABLE t3(a  INT PRIMARY KEY, b INT );
-        CREATE TABLE t4(c  INT PRIMARY KEY REFERENCES t3, d INT );
-
-        CREATE TABLE t7(a INT , b INTEGER PRIMARY KEY);
-        CREATE TABLE t8(c  INT PRIMARY KEY REFERENCES t7, d INT );
+        CREATE TABLE t3(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+        CREATE TABLE t4(c INT UNIQUE REFERENCES t3(a), d INT, id INT PRIMARY KEY AUTOINCREMENT);
 
+        CREATE TABLE t7(a INT, b INTEGER UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
+        CREATE TABLE t8(c INT UNIQUE REFERENCES t7(b), d INT, id INT PRIMARY KEY AUTOINCREMENT);
     ]], {
         -- <fkey2-1.1>
         -- </fkey2-1.1>
@@ -24,7 +23,7 @@ test:do_execsql_test(
 test:do_catchsql_test(
     "fkey2-1.2",
     [[
-        INSERT INTO t2 VALUES(1, 3);
+        INSERT INTO t2(c,d) VALUES(1, 3);
     ]], {
         -- <fkey2-1.2>
         1, "FOREIGN KEY constraint failed"
@@ -34,7 +33,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.3",
     [[
-        INSERT INTO t1 VALUES(1, 2);
+        INSERT INTO t1(a, b) VALUES(1, 2);
     ]], {
         -- <fkey2-1.3>
         0
@@ -44,7 +43,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.4",
     [[
-        INSERT INTO t2 VALUES(1, 3);
+        INSERT INTO t2(c,d) VALUES(1, 3);
     ]], {
         -- <fkey2-1.4>
         0
@@ -54,7 +53,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.5",
     [[
-        INSERT INTO t2 VALUES(2, 4);
+        INSERT INTO t2(c,d) VALUES(2, 4);
     ]], {
         -- <fkey2-1.5>
         1, "FOREIGN KEY constraint failed"
@@ -64,8 +63,8 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.6",
     [[
-        INSERT INTO t1 VALUES(3, 5);
-        INSERT INTO t2 VALUES(3, 4);
+        INSERT INTO t1(a,b) VALUES(3, 5);
+        INSERT INTO t2(c,d) VALUES(3, 4);
     ]], {
         -- <fkey2-1.6>
         0
@@ -85,7 +84,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.8",
     [[
-        INSERT INTO t1 VALUES(6, 7);
+        INSERT INTO t1(a,b) VALUES(6, 7);
         UPDATE t2 SET c = 6 WHERE d = 4;
     ]], {
         -- <fkey2-1.8>
@@ -129,7 +128,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.12",
     [[
-        INSERT INTO t4 values (1,3);
+        INSERT INTO t4(c,d) values (1,3);
     ]], {
         -- <fkey2-1.12>
         1, "FOREIGN KEY constraint failed"
@@ -139,7 +138,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.13",
     [[
-        INSERT INTO t3 values (1,2);
+        INSERT INTO t3(a,b) values (1,2);
     ]], {
         -- <fkey2-1.13>
         0
@@ -149,7 +148,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.14",
     [[
-        INSERT INTO t4 values (1,3);
+        INSERT INTO t4(c,d) values (1,3);
     ]], {
         -- <fkey2-1.14>
         0
@@ -159,7 +158,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.15",
     [[
-        INSERT INTO t8 values (1,3);
+        INSERT INTO t8(c,d) values (1,3);
     ]], {
         -- <fkey2-1.15>
         1, "FOREIGN KEY constraint failed"
@@ -169,7 +168,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.16",
     [[
-        INSERT INTO t7 values (2,1);
+        INSERT INTO t7(a,b) values (2,1);
     ]], {
         -- <fkey2-1.16>
         0
@@ -179,7 +178,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.17",
     [[
-        INSERT INTO t8 values (1,3);
+        INSERT INTO t8(c,d) values (1,3);
     ]], {
         -- <fkey2-1.17>
         0
@@ -189,7 +188,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.18",
     [[
-        INSERT INTO t8 values (2,4);
+        INSERT INTO t8(c,d) values (2,4);
     ]], {
         -- <fkey2-1.18>
         1, "FOREIGN KEY constraint failed"
@@ -199,7 +198,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.19",
     [[
-        INSERT INTO t8 values (6,4);
+        INSERT INTO t8(c,d) values (6,4);
     ]], {
         -- <fkey2-1.19>
         1,"FOREIGN KEY constraint failed"
@@ -269,7 +268,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "fkey2-1.26",
     [[
-        INSERT INTO t8 VALUES(666, 54644);
+        INSERT INTO t8(c,d) VALUES(666, 54644);
     ]], {
         -- <fkey2-1.26>
         1, "FOREIGN KEY constraint failed"
@@ -341,17 +340,19 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-3.1",
     [[
-        CREATE TABLE ab(a  INT PRIMARY KEY, b TEXT);
+        CREATE TABLE ab(id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE, b TEXT);
         CREATE TABLE cd(
-            c  INT PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE,
+            id INT PRIMARY KEY AUTOINCREMENT,
+            c  INT UNIQUE REFERENCES ab(a) ON UPDATE CASCADE ON DELETE CASCADE,
             d TEXT);
         CREATE TABLE ef(
-            e  INT PRIMARY KEY REFERENCES cd ON UPDATE CASCADE,
+            id INT PRIMARY KEY AUTOINCREMENT,
+            e  INT UNIQUE REFERENCES cd(c) ON UPDATE CASCADE,
             f TEXT , CHECK (e!=5));
 
-        INSERT INTO ab VALUES(1, 'b');
-        INSERT INTO cd VALUES(1, 'd');
-        INSERT INTO ef VALUES(1, 'e');
+        INSERT INTO ab(a,b) VALUES(1, 'b');
+        INSERT INTO cd(c,d) VALUES(1, 'd');
+        INSERT INTO ef(e,f) VALUES(1, 'e');
     ]], {
     })
 
@@ -368,7 +369,7 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-3.3",
     [[
-        SELECT * FROM ab;
+        SELECT a,b FROM ab;
     ]], {
         -- <fkey2-3.3>
         1, "b"
@@ -388,7 +389,7 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-3.5",
     [[
-        SELECT * FROM ef;
+        SELECT e,f FROM ef;
     ]], {
         -- <fkey2-3.5>
         1, "e"
@@ -410,7 +411,7 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-3.7",
     [[
-        SELECT * FROM ab;
+        SELECT a,b FROM ab;
     ]], {
         -- <fkey2-3.7>
         1, "b"
@@ -424,20 +425,21 @@ test:do_execsql_test(
         DROP TABLE IF EXISTS t2;
         DROP TABLE IF EXISTS t1;
         CREATE TABLE t1(
-            node  INT PRIMARY KEY,
-            parent  INT REFERENCES t1 ON DELETE CASCADE);
+            id INT PRIMARY KEY AUTOINCREMENT,
+            node  INT UNIQUE NOT NULL,
+            parent  INT REFERENCES t1(node) ON DELETE CASCADE);
         CREATE TABLE t2(node  INT PRIMARY KEY, parent INT );
         CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
             DELETE FROM t2 WHERE parent = old.node;
         END;
-        INSERT INTO t1 VALUES(1, NULL);
-        INSERT INTO t1 VALUES(2, 1);
-        INSERT INTO t1 VALUES(3, 1);
-        INSERT INTO t1 VALUES(4, 2);
-        INSERT INTO t1 VALUES(5, 2);
-        INSERT INTO t1 VALUES(6, 3);
-        INSERT INTO t1 VALUES(7, 3);
-        INSERT INTO t2 SELECT * FROM t1;
+        INSERT INTO t1(node, parent) VALUES(1, NULL);
+        INSERT INTO t1(node, parent) VALUES(2, 1);
+        INSERT INTO t1(node, parent) VALUES(3, 1);
+        INSERT INTO t1(node, parent) VALUES(4, 2);
+        INSERT INTO t1(node, parent) VALUES(5, 2);
+        INSERT INTO t1(node, parent) VALUES(6, 3);
+        INSERT INTO t1(node, parent) VALUES(7, 3);
+        INSERT INTO t2(node, parent) SELECT node,parent FROM t1;
     ]], {
         -- <fkey2-4.1>
         -- </fkey2-4.1>
@@ -525,8 +527,8 @@ test:do_execsql_test(
     [[
         DROP TABLE IF EXISTS t1;
         DROP TABLE IF EXISTS t2;
-        CREATE TABLE t1(a  INT PRIMARY KEY, b INT );
-        CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b TEXT);
+        CREATE TABLE t1(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+        CREATE TABLE t2(id INT PRIMARY KEY AUTOINCREMENT, c INTEGER REFERENCES t1(a), b TEXT);
     ]], {
         -- <fkey2-5.1>
         -- </fkey2-5.1>
@@ -535,7 +537,7 @@ test:do_execsql_test(
 test:do_catchsql_test(
     "fkey2-5.2",
     [[
-        INSERT INTO t2 VALUES(1, 'A');
+        INSERT INTO t2(c,b) VALUES(1, 'A');
     ]], {
         -- <fkey2-5.2>
         1, "FOREIGN KEY constraint failed"
@@ -545,9 +547,9 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-5.3",
     [[
-        INSERT INTO t1 VALUES(1, 2);
-        INSERT INTO t1 VALUES(2, 3);
-        INSERT INTO t2 VALUES(1, 'A');
+        INSERT INTO t1(a,b) VALUES(1, 2);
+        INSERT INTO t1(a,b) VALUES(2, 3);
+        INSERT INTO t2(c,b) VALUES(1, 'A');
         UPDATE t2 SET c = 2;
     ]], {
         -- <fkey2-5.3>
@@ -668,12 +670,11 @@ test:do_execsql_test(
             ON DELETE SET NULL);
         INSERT INTO pp VALUES(1, 2, 3);
         INSERT INTO pp VALUES(4, 5, 6);
-        INSERT INTO pp VALUES(7, 8, 9);
+        INSERT INTO pp VALUES(7, 1, 9);
         INSERT INTO cc VALUES(6, 'A', 5, 1);
         INSERT INTO cc VALUES(6, 'B', 5, 2);
-        INSERT INTO cc VALUES(9, 'A', 8, 3);
-        INSERT INTO cc VALUES(9, 'B', 8, 4);
-        UPDATE pp SET b = 1 WHERE a = 7;
+        INSERT INTO cc VALUES(3, 'A', 2, 3);
+        INSERT INTO cc VALUES(3, 'B', 2, 4);
         SELECT * FROM cc;
     ]], {
         -- <fkey2-6.6>
@@ -766,13 +767,13 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-8.1",
     [[
-        CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
-        CREATE TABLE t2(c INTEGER PRIMARY KEY, d INT, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
+        CREATE TABLE t1(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
+        CREATE TABLE t2(id INT PRIMARY KEY AUTOINCREMENT, c INT UNIQUE, d INT, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
 
-        INSERT INTO t1 VALUES(10, 100);
-        INSERT INTO t2 VALUES(10, 100);
+        INSERT INTO t1(a,b) VALUES(10, 100);
+        INSERT INTO t2(c,d) VALUES(10, 100);
         UPDATE t1 SET a = 15;
-        SELECT * FROM t2;
+        SELECT c,d FROM t2;
     ]], {
         -- <fkey2-8.1>
         15, 100
@@ -788,12 +789,12 @@ test:do_execsql_test(
     [[
         DROP TABLE IF EXISTS t2;
         DROP TABLE IF EXISTS t1;
-        CREATE TABLE t1(a INT , b TEXT PRIMARY KEY);
+        CREATE TABLE t1(a INT, b TEXT UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
         CREATE TABLE t2(
-            x TEXT PRIMARY KEY REFERENCES t1 ON UPDATE RESTRICT);
-        INSERT INTO t1 VALUES(1, 'one');
-        INSERT INTO t1 VALUES(2, 'two');
-        INSERT INTO t1 VALUES(3, 'three');
+            x TEXT PRIMARY KEY REFERENCES t1(b) ON UPDATE RESTRICT);
+        INSERT INTO t1(a,b) VALUES(1, 'four');
+        INSERT INTO t1(a,b) VALUES(2, 'two');
+        INSERT INTO t1(a,b) VALUES(3, 'three');
         INSERT INTO t2 VALUES('two');
         UPDATE t1 SET b = 'four' WHERE b = 'one';
     ]], {
@@ -916,14 +917,14 @@ test:do_execsql_test(
             c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT , c18 TEXT , c19 TEXT ,
             c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT , c28 TEXT , c29 TEXT ,
             c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT , c38 TEXT , c39 TEXT ,
-            PRIMARY KEY(c34, c35));
+            UNIQUE(c34, c35), id INT PRIMARY KEY AUTOINCREMENT);
         CREATE TABLE down(
             c00 TEXT , c01 TEXT , c02 TEXT , c03 TEXT , c04 TEXT , c05 TEXT , c06 TEXT , c07 TEXT , c08 TEXT , c09 TEXT ,
             c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT , c18 TEXT , c19 TEXT ,
             c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT , c28 TEXT , c29 TEXT ,
             c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT , c38 TEXT , c39 TEXT ,
-            PRIMARY KEY(c39, c38),
-            FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE);
+            UNIQUE(c39, c38),
+            FOREIGN KEY(c39, c38) REFERENCES up(c34,c35) ON UPDATE CASCADE, id INT PRIMARY KEY AUTOINCREMENT);
         INSERT INTO up(c34, c35) VALUES('yes', 'no');
         INSERT INTO down(c39, c38) VALUES('yes', 'no');
         UPDATE up SET c34 = 'possibly';
@@ -1168,8 +1169,8 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-11.1",
     [[
-        CREATE TABLE self(a INT PRIMARY KEY, b INT REFERENCES self(a));
-        INSERT INTO self VALUES(13, 13);
+        CREATE TABLE self(a INT UNIQUE, b INT REFERENCES self(a), id INT PRIMARY KEY AUTOINCREMENT);
+        INSERT INTO self(a,b) VALUES(13, 13);
         UPDATE self SET a = 14, b = 14;
     ]], {
         -- <fkey2-11.1>
@@ -1227,7 +1228,7 @@ test:do_execsql_test(
 test:do_catchsql_test(
     "fkey2-11.7",
     [[
-        INSERT INTO self VALUES(20, 21);
+        INSERT INTO self(a,b) VALUES(20, 21);
     ]], {
         -- <fkey2-11.7>
         1, "FOREIGN KEY constraint failed"
@@ -1238,8 +1239,8 @@ test:do_execsql_test(
     "fkey2-11.8",
     [[
         DROP TABLE IF EXISTS self;
-        CREATE TABLE self(a INT UNIQUE, b INT PRIMARY KEY REFERENCES self(a));
-        INSERT INTO self VALUES(13, 13);
+        CREATE TABLE self(a INT UNIQUE, b INT UNIQUE REFERENCES self(a), id INT PRIMARY KEY AUTOINCREMENT);
+        INSERT INTO self(a,b) VALUES(13, 13);
         UPDATE self SET a = 14, b = 14;
     ]], {
         -- <fkey2-11.8>
@@ -1297,7 +1298,7 @@ test:do_execsql_test(
 test:do_catchsql_test(
     "fkey2-11.14",
     [[
-        INSERT INTO self VALUES(20, 21);
+        INSERT INTO self(a,b) VALUES(20, 21);
     ]], {
         -- <fkey2-11.14>
         1, "FOREIGN KEY constraint failed"
@@ -1310,12 +1311,12 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-12.1",
     [[
-        CREATE TABLE tdd08(a INT PRIMARY KEY, b INT);
+        CREATE TABLE tdd08(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
         CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
-        INSERT INTO tdd08 VALUES(200,300);
+        INSERT INTO tdd08(a,b) VALUES(200,300);
 
-        CREATE TABLE tdd08_b(w  INT PRIMARY KEY,x INT ,y INT , FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
-        INSERT INTO tdd08_b VALUES(100,200,300);
+        CREATE TABLE tdd08_b(w INT UNIQUE, x INT ,y INT , FOREIGN KEY(x,y) REFERENCES tdd08(a,b), id INT PRIMARY KEY AUTOINCREMENT);
+        INSERT INTO tdd08_b(w,x,y) VALUES(100,200,300);
     ]], {
         -- <fkey2-12.1>
         -- </fkey2-12.1>
@@ -1334,7 +1335,7 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-12.3",
     [[
-        SELECT * FROM tdd08;
+        SELECT a,b FROM tdd08;
     ]], {
         -- <fkey2-12.3>
         200, 300
@@ -1344,7 +1345,7 @@ test:do_execsql_test(
 test:do_catchsql_test(
     "fkey2-12.4",
     [[
-        INSERT INTO tdd08_b VALUES(400,500,300);
+        INSERT INTO tdd08_b(w,x,y) VALUES(400,500,300);
     ]], {
         -- <fkey2-12.4>
         1, "FOREIGN KEY constraint failed"
@@ -1374,16 +1375,16 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey2-13.1",
     [[
-        CREATE TABLE tce71(a INT PRIMARY KEY, b INT);
+        CREATE TABLE tce71(a INT UNIQUE, b INT, id INT PRIMARY KEY AUTOINCREMENT);
         CREATE UNIQUE INDEX ice71 ON tce71(a,b);
-        INSERT INTO tce71 VALUES(100,200);
+        INSERT INTO tce71(a,b) VALUES(100,200);
         CREATE TABLE tce72(w  INT PRIMARY KEY, x INT , y INT , FOREIGN KEY(x,y) REFERENCES tce71(a,b));
         INSERT INTO tce72 VALUES(300,100,200);
         UPDATE tce71 set b = 200 where a = 100;
         SELECT * FROM tce71, tce72;
     ]], {
         -- <fkey2-13.1>
-        100, 200, 300, 100, 200
+        100, 200, 1, 300, 100, 200
         -- </fkey2-13.1>
     })
 
diff --git a/test/sql-tap/fkey3.test.lua b/test/sql-tap/fkey3.test.lua
index 8fbbdcfbc..011402da6 100755
--- a/test/sql-tap/fkey3.test.lua
+++ b/test/sql-tap/fkey3.test.lua
@@ -158,12 +158,12 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey3-3.6",
     [[
-        CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT, c INT, d TEXT, UNIQUE(a, b),
-            FOREIGN KEY(c, d) REFERENCES t6(a, b));
-        INSERT INTO t6 VALUES(1, 'a', 1, 'a');
-        INSERT INTO t6 VALUES(2, 'a', 2, 'a');
-        INSERT INTO t6 VALUES(3, 'a', 1, 'a');
-        INSERT INTO t6 VALUES(5, 'a', 2, 'a');
+        CREATE TABLE t6(a INT UNIQUE, b TEXT, c INT, d TEXT, UNIQUE(a, b),
+            FOREIGN KEY(c, d) REFERENCES t6(a, b), id INT PRIMARY KEY AUTOINCREMENT);
+        INSERT INTO t6(a,b,c,d) VALUES(1, 'a', 1, 'a');
+        INSERT INTO t6(a,b,c,d) VALUES(2, 'a', 2, 'a');
+        INSERT INTO t6(a,b,c,d) VALUES(3, 'a', 1, 'a');
+        INSERT INTO t6(a,b,c,d) VALUES(5, 'a', 2, 'a');
     ]], {
         -- <fkey3-3.6>
         -- </fkey3-3.6>
@@ -172,7 +172,7 @@ test:do_execsql_test(
 test:do_catchsql_test(
     "fkey3-3.7",
     [[
-        INSERT INTO t6 VALUES(4, 'a', 65, 'a');
+        INSERT INTO t6(a,b,c,d) VALUES(4, 'a', 65, 'a');
     ]], {
         -- <fkey3-3.7>
         1, "FOREIGN KEY constraint failed"
@@ -182,23 +182,22 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "fkey3-3.8",
     [[
-        INSERT INTO t6 VALUES(100, 'one', 100, 'one');
+        INSERT INTO t6(a,b,c,d) VALUES(100, 'one', 100, 'one');
         DELETE FROM t6 WHERE a = 100;
-        SELECT * FROM t6 WHERE a = 100;
+        SELECT a,b,c,d FROM t6 WHERE a = 100;
     ]], {
         -- <fkey3-3.8>
         -- </fkey3-3.8>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "fkey3-3.9",
     [[
-        INSERT INTO t6 VALUES(100, 'one', 100, 'one');
+        INSERT INTO t6(a,b,c,d) VALUES(100, 'one', 100, 'one');
         UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
-        DELETE FROM t6 WHERE a = 100;
-        SELECT * FROM t6 WHERE a = 100;
     ]], {
         -- <fkey3-3.9>
+        1, "FOREIGN KEY constraint failed"
         -- </fkey3-3.9>
     })
 
diff --git a/test/sql-tap/fkey4.test.lua b/test/sql-tap/fkey4.test.lua
index a1b3b1f41..13fd308ee 100755
--- a/test/sql-tap/fkey4.test.lua
+++ b/test/sql-tap/fkey4.test.lua
@@ -8,9 +8,9 @@ test:do_execsql_test(
     "fkey8-1.1",
     [[
         CREATE TABLE p1(a INT PRIMARY KEY);
-        CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON DELETE CASCADE);
+        CREATE TABLE c1(id INT PRIMARY KEY AUTOINCREMENT, b INT UNIQUE REFERENCES p1 ON DELETE CASCADE);
         INSERT INTO p1 VALUES (1), (2), (3);
-        INSERT INTO c1 VALUES (2);
+        INSERT INTO c1(b) VALUES (2);
         DELETE FROM p1 WHERE a = 2;
         SELECT * FROM c1;
     ]], {
@@ -24,9 +24,9 @@ test:do_catchsql_test(
         DROP TABLE IF EXISTS c1;
         DROP TABLE IF EXISTS p1;
         CREATE TABLE p1(a INT PRIMARY KEY);
-        CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON DELETE SET NULL);
+        CREATE TABLE c1(id INT PRIMARY KEY AUTOINCREMENT, b INT UNIQUE NOT NULL REFERENCES p1 ON DELETE SET NULL);
         INSERT INTO p1 VALUES (1), (2), (3);
-        INSERT INTO c1 VALUES (2);
+        INSERT INTO c1(b) VALUES (2);
         DELETE FROM p1 WHERE a = 2;
     ]], {
         -- <fkey8-1.2>
@@ -40,11 +40,11 @@ test:do_execsql_test(
         DROP TABLE IF EXISTS c1;
         DROP TABLE IF EXISTS p1;
         CREATE TABLE p1(a INT PRIMARY KEY);
-        CREATE TABLE c1(b INT PRIMARY KEY DEFAULT 3 REFERENCES p1 ON DELETE SET DEFAULT);
+        CREATE TABLE c1(id INT PRIMARY KEY AUTOINCREMENT, b INT UNIQUE DEFAULT 3 REFERENCES p1 ON DELETE SET DEFAULT);
         INSERT INTO p1 VALUES (1), (2), (3);
-        INSERT INTO c1 VALUES (2);
+        INSERT INTO c1(b) VALUES (2);
         DELETE FROM p1 WHERE a = 2;
-        SELECT * FROM c1;
+        SELECT b FROM c1;
     ]], {
         -- <fkey8-1.3>
         3
@@ -152,10 +152,10 @@ test:do_catchsql_test(
         DROP TABLE IF EXISTS cc1;
         DROP TABLE IF EXISTS c1;
         DROP TABLE IF EXISTS p1;
-        CREATE TABLE p1(a INT PRIMARY KEY);
-        CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON UPDATE SET NULL, c INT);
-        INSERT INTO p1 VALUES (1), (2), (3);
-        INSERT INTO c1 VALUES (2, 1), (3, 2);
+        CREATE TABLE p1(a INT UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
+        CREATE TABLE c1(b INT UNIQUE NOT NULL REFERENCES p1(a) ON UPDATE SET NULL, c INT, id INT PRIMARY KEY AUTOINCREMENT);
+        INSERT INTO p1(a) VALUES (1), (2), (3);
+        INSERT INTO c1(b, c) VALUES (2, 1), (3, 2);
         UPDATE OR IGNORE p1 SET a = 4 WHERE a = 2;
     ]], {
         -- <fkey8-1.9>
@@ -168,12 +168,12 @@ test:do_execsql_test(
     [[
         DROP TABLE IF EXISTS c1;
         DROP TABLE IF EXISTS p1;
-        CREATE TABLE p1(a INT PRIMARY KEY);
-        CREATE TABLE c1(b INT PRIMARY KEY REFERENCES p1 ON UPDATE CASCADE, c INT);
-        INSERT INTO p1 VALUES (1), (2), (3);
-        INSERT INTO c1 VALUES (2, 1), (3, 2);
+        CREATE TABLE p1(a INT UNIQUE, id INT PRIMARY KEY AUTOINCREMENT);
+        CREATE TABLE c1(b INT UNIQUE NOT NULL REFERENCES p1(a) ON UPDATE CASCADE, c INT, id INT PRIMARY KEY AUTOINCREMENT);
+        INSERT INTO p1(a) VALUES (1), (2), (3);
+        INSERT INTO c1(b,c) VALUES (2, 1), (3, 2);
         UPDATE OR IGNORE p1 SET a = 4 WHERE a = 2;
-        SELECT * FROM c1;
+        SELECT b,c FROM c1 ORDER BY b,c;
     ]], {
         -- <fkey8-1.10>
         3, 2, 4, 1
diff --git a/test/sql-tap/gh2140-trans.test.lua b/test/sql-tap/gh2140-trans.test.lua
index 3c6f9042f..fe978d1a9 100755
--- a/test/sql-tap/gh2140-trans.test.lua
+++ b/test/sql-tap/gh2140-trans.test.lua
@@ -5,42 +5,42 @@ test:plan(10)
 box.sql.execute("DROP TABLE IF EXISTS t1")
 box.sql.execute("DROP TABLE IF EXISTS t2")
 
-box.sql.execute("CREATE TABLE t1 (s1 int primary key, s2 int);")
-box.sql.execute("CREATE TABLE t2 (s1 int primary key, s2 int);")
+box.sql.execute("CREATE TABLE t1 (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
+box.sql.execute("CREATE TABLE t2 (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
 
-box.sql.execute("INSERT INTO t1 VALUES (1,1);")
-box.sql.execute("INSERT INTO t2 VALUES (1,1);")
+box.sql.execute("INSERT INTO t1 VALUES (1,1,1);")
+box.sql.execute("INSERT INTO t2 VALUES (1,1,1);")
 
 test:do_execsql_test('commit1_check',
                      [[START TRANSACTION;
-                         INSERT INTO t1 VALUES (2,2);
+                         INSERT INTO t1 VALUES (2,2,2);
                        COMMIT;
 
-                       SELECT * FROM t1]],
+                       SELECT s1,s2 FROM t1]],
                      {1, 1, 2, 2})
 
 test:do_execsql_test('rollback1_check',
                      [[START TRANSACTION;
-                         INSERT INTO t1 VALUES (3,3);
+                         INSERT INTO t1 VALUES (3,3,3);
                        ROLLBACK;
 
-                       SELECT * FROM t1]],
+                       SELECT s1,s2 FROM t1]],
                      {1, 1, 2, 2})
 
 for _, verb in ipairs({'ROLLBACK', 'ABORT'}) do
     box.sql.execute('DELETE FROM t2')
-    answer = "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
+    answer = "Duplicate key exists in unique index 'unique_unnamed_T1_2' in space 'T1'"
     test:do_catchsql_test('insert1_'..verb,
                           [[START TRANSACTION;
-                            INSERT INTO t2 VALUES (2, 2);
-                            INSERT OR ]]..verb..[[ INTO t1 VALUES (1,1);
+                            INSERT INTO t2 VALUES (20, 2, 2);
+                            INSERT OR ]]..verb..[[ INTO t1 VALUES (10,1,1);
                           ]],
                           {1, answer})
 
     local expect = {}
     if verb == 'ABORT' then
          box.sql.execute('COMMIT')
-         expect = {2, 2}
+         expect = {20, 2, 2}
     end
     test:do_execsql_test('insert1_'..verb..'_check',
                          'SELECT * FROM t2', expect)
@@ -48,7 +48,7 @@ for _, verb in ipairs({'ROLLBACK', 'ABORT'}) do
     box.sql.execute('DELETE FROM t2')
     test:do_catchsql_test('update1_'..verb,
                           [[START TRANSACTION;
-                            INSERT INTO t2 VALUES (2, 2);
+                            INSERT INTO t2 VALUES (20, 2, 2);
                             UPDATE OR ]]..verb..[[ t1 SET s1 = 1 WHERE s1 = 2;
                           ]],
                           {1, answer})
diff --git a/test/sql-tap/gh2250-trigger-chain-limit.test.lua b/test/sql-tap/gh2250-trigger-chain-limit.test.lua
index bfbb26097..e38f286e6 100755
--- a/test/sql-tap/gh2250-trigger-chain-limit.test.lua
+++ b/test/sql-tap/gh2250-trigger-chain-limit.test.lua
@@ -9,20 +9,20 @@ for _, table_count in ipairs({30, 31}) do
         drop_string = 'DROP TABLE IF EXISTS t' .. i .. ';'
         box.sql.execute(drop_string)
 
-        create_string = 'CREATE TABLE t' .. i .. ' (s1 int primary key, s2 int);'
+        create_string = 'CREATE TABLE t' .. i .. ' (s1 INT UNIQUE, s2 INT, s3 INT PRIMARY KEY);'
         box.sql.execute(create_string)
 
-        insert_string = 'INSERT INTO t' .. i .. ' VALUES (0,' .. i .. ');'
+        insert_string = 'INSERT INTO t' .. i .. ' VALUES (0,' .. i .. ', 0);'
         box.sql.execute(insert_string)
 
         -- Second table for triggers mixture check
         drop_string = 'DROP TABLE IF EXISTS tt' .. i .. ';'
         box.sql.execute(drop_string)
 
-        create_string = 'CREATE TABLE tt' .. i .. ' (s1 int primary key, s2 int);'
+        create_string = 'CREATE TABLE tt' .. i .. ' (s1 INT UNIQUE, s2 INT, s3 INT PRIMARY KEY);'
         box.sql.execute(create_string)
 
-        insert_string = 'INSERT INTO tt' .. i .. ' VALUES (0,' .. i .. ');'
+        insert_string = 'INSERT INTO tt' .. i .. ' VALUES (0,' .. i .. ', 0);'
         box.sql.execute(insert_string)
     end
 
diff --git a/test/sql-tap/gh2259-in-stmt-trans.test.lua b/test/sql-tap/gh2259-in-stmt-trans.test.lua
index d1ced19ec..95e03985f 100755
--- a/test/sql-tap/gh2259-in-stmt-trans.test.lua
+++ b/test/sql-tap/gh2259-in-stmt-trans.test.lua
@@ -5,8 +5,8 @@ test:plan(20)
 box.sql.execute("DROP TABLE IF EXISTS t1")
 box.sql.execute("DROP TABLE IF EXISTS t2")
 
-box.sql.execute("CREATE TABLE t1 (s1 int primary key, s2 int);")
-box.sql.execute("CREATE TABLE t2 (s1 int primary key, s2 int);")
+box.sql.execute("CREATE TABLE t1 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE t2 (s1 INT UNIQUE, s2 INT PRIMARY KEY);")
 
 box.sql.execute("INSERT INTO t2 VALUES (1,1);")
 box.sql.execute("INSERT INTO t1 VALUES (3,3);")
@@ -18,7 +18,7 @@ for _, prefix in pairs({"BEFORE", "AFTER"}) do
 
     test:do_catchsql_test(prefix..'_insert1',
                           'INSERT INTO t1 VALUES(1, 2)',
-                          {1,"Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+                          {1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
 
     test:do_execsql_test(prefix..'_insert1_check1',
                          'SELECT *  FROM t1',
@@ -34,7 +34,7 @@ for _, prefix in pairs({"BEFORE", "AFTER"}) do
 
     test:do_catchsql_test(prefix..'_update1',
                           'UPDATE t1 SET s1=1',
-                          {1,"Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+                          {1,"Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
 
     test:do_execsql_test(prefix..'_update1_check1',
                          'SELECT *  FROM t1',
@@ -52,7 +52,7 @@ for _, prefix in pairs({"BEFORE", "AFTER"}) do
 
     test:do_catchsql_test(prefix..'delete1',
                           'DELETE FROM t1;',
-                          {1, "Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+                          {1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
 
     -- Nothing should be inserted due to abort
     test:do_execsql_test('delete1_check1',
@@ -69,7 +69,7 @@ end
 -- Check multi-insert
 test:do_catchsql_test('insert2',
                       'INSERT INTO t1 VALUES (5, 6), (6, 7)',
-                      {1, "Duplicate key exists in unique index 'pk_unnamed_T2_1' in space 'T2'"})
+                      {1, "Duplicate key exists in unique index 'pk_unnamed_T2_2' in space 'T2'"})
 test:do_execsql_test('insert2_check',
                      'SELECT * FROM t1;',
                      {3, 3})
diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua
index 9e8107aef..d26982297 100755
--- a/test/sql-tap/identifier_case.test.lua
+++ b/test/sql-tap/identifier_case.test.lua
@@ -116,7 +116,7 @@ test:do_test(
     end,
     6)
 
-test:execsql([[create table table1(columnn INT , "columnn" INT primary key)]])
+test:execsql([[create table table1(pk INT PRIMARY KEY AUTOINCREMENT, columnn INT , "columnn" INT UNIQUE)]])
 test:execsql([[insert into table1("columnn", "COLUMNN") values(2,1)]])
 
 
diff --git a/test/sql-tap/index6.test.lua b/test/sql-tap/index6.test.lua
index 05385efe6..d39da43e8 100755
--- a/test/sql-tap/index6.test.lua
+++ b/test/sql-tap/index6.test.lua
@@ -227,7 +227,8 @@ test:do_execsql_test(
         -- </index6-6.0>
     })
 else
-    test:execsql("CREATE TABLE t6(a INT ,b INT , PRIMARY KEY (a,b));")
+    test:execsql("CREATE TABLE t6(id INT PRIMARY KEY AUTOINCREMENT, a INT ,b INT);")
+    test:execsql("CREATE UNIQUE INDEX t6i1 ON t6(a, b);")
     test:execsql("INSERT INTO t6(a,b) VALUES(123,456);")
 end
 
@@ -235,7 +236,7 @@ test:do_execsql_test(
     "index6-6.1",
     [[
         UPDATE OR REPLACE t6 SET b=789;
-        SELECT * FROM t6;
+        SELECT a,b FROM t6;
     ]], {
         -- <index6-6.1>
         123, 789
diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
index 132d9a37f..f6d5eaf8f 100755
--- a/test/sql-tap/intpkey.test.lua
+++ b/test/sql-tap/intpkey.test.lua
@@ -145,11 +145,13 @@ test:do_execsql_test(
     })
 
 -- Try to change the ROWID for the new entry.
+-- Direct update of PK is forbidden
 --
 test:do_execsql_test(
     "intpkey-1.11",
     [[
-        UPDATE t1 SET a=4 WHERE b='one';
+        DELETE FROM t1 WHERE a = 7;
+        INSERT INTO t1 VALUES(4,'one','two');
         SELECT * FROM t1;
     ]], {
         -- <intpkey-1.11>
@@ -281,7 +283,8 @@ test:do_execsql_test(
 test:do_execsql_test(
     "intpkey-2.2",
     [[
-        UPDATE t1 SET a=8 WHERE b=='y';
+        DELETE FROM t1 WHERE b=='y';
+        INSERT INTO t1 VALUES(8,'y','z');
         SELECT * FROM t1 WHERE b=='y';
     ]], {
         -- <intpkey-2.2>
@@ -335,7 +338,8 @@ test:do_execsql_test(
     "intpkey-2.7",
     [[
         --UPDATE t1 SET a=-4 WHERE rowid=8;
-        UPDATE t1 SET a=-4 WHERE a=8;
+        DELETE FROM t1 WHERE a==8;
+        INSERT INTO t1 VALUES(-4,'y','z');
         SELECT * FROM t1 WHERE b>'a';
     ]], {
         -- <intpkey-2.7>
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index 0bf680fe6..a533a7c07 100755
--- a/test/sql-tap/misc1.test.lua
+++ b/test/sql-tap/misc1.test.lua
@@ -237,16 +237,19 @@ test:do_test(
 test:do_execsql_test(
     "misc1-4.1",
     [[
+        CREATE TABLE temp(id INT PRIMARY KEY, a TEXT);
         CREATE TABLE t2(a TEXT primary key);
+
         START TRANSACTION;
-        INSERT INTO t2 VALUES('This is a long string to use up a lot of disk -');
-        UPDATE t2 SET a=a||a||a||a;
-        INSERT INTO t2 SELECT '1 - ' || a FROM t2;
-        INSERT INTO t2 SELECT '2 - ' || a FROM t2;
-        INSERT INTO t2 SELECT '3 - ' || a FROM t2;
-        INSERT INTO t2 SELECT '4 - ' || a FROM t2;
-        INSERT INTO t2 SELECT '5 - ' || a FROM t2;
-        INSERT INTO t2 SELECT '6 - ' || a FROM t2;
+        INSERT INTO temp VALUES(0, 'This is a long string to use up a lot of disk -');
+        UPDATE temp SET a=a||a||a||a;
+        INSERT INTO t2 (a) SELECT a FROM temp;
+        INSERT INTO t2 (a) SELECT '1 - ' || a FROM t2;
+        INSERT INTO t2 (a) SELECT '2 - ' || a FROM t2;
+        INSERT INTO t2 (a) SELECT '3 - ' || a FROM t2;
+        INSERT INTO t2 (a) SELECT '4 - ' || a FROM t2;
+        INSERT INTO t2 (a) SELECT '5 - ' || a FROM t2;
+        INSERT INTO t2 (a) SELECT '6 - ' || a FROM t2;
         COMMIT;
         SELECT count(*) FROM t2;
     ]], {
@@ -429,6 +432,7 @@ test:execsql([[
     DROP TABLE t2;
     DROP TABLE t3;
     DROP TABLE t4;
+    DROP TABLE temp;
 ]])
 -- 64-bit integers are represented exactly.
 --
diff --git a/test/sql-tap/quote.test.lua b/test/sql-tap/quote.test.lua
index 3f0bf865d..0a3b89daa 100755
--- a/test/sql-tap/quote.test.lua
+++ b/test/sql-tap/quote.test.lua
@@ -27,7 +27,7 @@ test:do_catchsql_test(
     "quote-1.0",
     [[
         --- CREATE TABLE '@abc' ( '#xyz' int PRIMARY KEY, '!pqr' text );
-        CREATE TABLE "abc5_" ( "#xyz" int PRIMARY KEY, "!pqr" text );
+        CREATE TABLE "abc5_" (id INT PRIMARY KEY, "#xyz" INT UNIQUE, "!pqr" TEXT );
     ]], {
         -- <quote-1.0>
         0
@@ -39,7 +39,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "quote-1.1",
     [[
-        INSERT INTO "abc5_" VALUES(5,'hello')
+        INSERT INTO "abc5_" VALUES(1, 5,'hello')
     ]], {
         -- <quote-1.1>
         0
@@ -52,7 +52,7 @@ test:do_catchsql_test(
         SELECT * FROM "abc5_"
     ]], {
         -- <quote-1.2.1>
-        0, {5, "hello"}
+        0, {1, 5, "hello"}
         -- </quote-1.2.1>
     })
 
diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua
index 71645e2e2..7057f6b0f 100755
--- a/test/sql-tap/table.test.lua
+++ b/test/sql-tap/table.test.lua
@@ -528,7 +528,7 @@ test:do_execsql2_test(
 test:do_execsql_test(
     "table-7.3",
     [[
-        CREATE TABLE savepoint_t(release_t int primary key);
+        CREATE TABLE savepoint_t(id INT PRIMARY KEY AUTOINCREMENT, release_t INT UNIQUE);
         INSERT INTO savepoint_t(release_t) VALUES(10);
         UPDATE savepoint_t SET release_t = 5;
         SELECT release_t FROM savepoint_t;
diff --git a/test/sql-tap/tkt-a8a0d2996a.test.lua b/test/sql-tap/tkt-a8a0d2996a.test.lua
index aefc2be0c..cc8827bc2 100755
--- a/test/sql-tap/tkt-a8a0d2996a.test.lua
+++ b/test/sql-tap/tkt-a8a0d2996a.test.lua
@@ -23,8 +23,8 @@ testprefix = "tkt-a8a0d2996a"
 test:do_execsql_test(
     1.0,
     [[
-        CREATE TABLE t(x TEXT primary key,y TEXT);
-        INSERT INTO t VALUES('1','1');
+        CREATE TABLE t(id INT PRIMARY KEY, x TEXT UNIQUE, y TEXT);
+        INSERT INTO t VALUES(1, '1','1');
         SELECT typeof(x), typeof(y) FROM t WHERE 1=x+0 AND y=='1';
     ]], {
         -- <1.0>
diff --git a/test/sql-tap/tkt2767.test.lua b/test/sql-tap/tkt2767.test.lua
index 066c82100..b69b90be1 100755
--- a/test/sql-tap/tkt2767.test.lua
+++ b/test/sql-tap/tkt2767.test.lua
@@ -31,13 +31,13 @@ if (1 > 0)
         "tkt2767-1.1",
         [[
             -- Construct a table with many rows of data
-            CREATE TABLE t1(x  INT primary key);
-            INSERT INTO t1 VALUES(1);
-            INSERT INTO t1 VALUES(2);
-            INSERT INTO t1 SELECT x+2 FROM t1;
-            INSERT INTO t1 SELECT x+4 FROM t1;
-            INSERT INTO t1 SELECT x+8 FROM t1;
-            INSERT INTO t1 SELECT x+16 FROM t1;
+            CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, x INT UNIQUE);
+            INSERT INTO t1 VALUES(1, 1);
+            INSERT INTO t1 VALUES(2, 2);
+            INSERT INTO t1 (x) SELECT x+2 FROM t1;
+            INSERT INTO t1 (x) SELECT x+4 FROM t1;
+            INSERT INTO t1 (x) SELECT x+8 FROM t1;
+            INSERT INTO t1 (x) SELECT x+16 FROM t1;
 
             -- BEFORE triggers that invoke raise(ignore).  The effect of
             -- these triggers should be to make INSERTs, UPDATEs, and DELETEs
@@ -99,7 +99,7 @@ if (1 > 0)
     test:do_execsql_test(
         "tkt2767-1.4",
         [[
-            INSERT INTO t1 SELECT x+32 FROM t1;
+            INSERT INTO t1 (x) SELECT x+32 FROM t1;
             SELECT count(*), sum(x) FROM t1;
         ]], {
             -- <tkt2767-1.4>
diff --git a/test/sql-tap/tkt2832.test.lua b/test/sql-tap/tkt2832.test.lua
index 108c05cdb..d3f24b586 100755
--- a/test/sql-tap/tkt2832.test.lua
+++ b/test/sql-tap/tkt2832.test.lua
@@ -25,10 +25,10 @@ test:plan(6)
 test:do_execsql_test(
     "tkt2832-1.1",
     [[
-        CREATE TABLE t1(a INT PRIMARY KEY);
-        INSERT INTO t1 VALUES(2);
-        INSERT INTO t1 VALUES(1);
-        INSERT INTO t1 VALUES(3);
+        CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE);
+        INSERT INTO t1(a) VALUES(2);
+        INSERT INTO t1(a) VALUES(1);
+        INSERT INTO t1(a) VALUES(3);
     ]], {
         -- <tkt2832-1.1>
         
@@ -40,7 +40,7 @@ test:do_execsql_test(
     "tkt2832-1.2",
     [[
         UPDATE OR REPLACE t1 SET a = 1;
-        SELECT * FROM t1;
+        SELECT a FROM t1;
     ]], {
         -- <tkt2832-1.2>
         1
diff --git a/test/sql-tap/tkt3554.test.lua b/test/sql-tap/tkt3554.test.lua
index ed194107f..0fcf350cb 100755
--- a/test/sql-tap/tkt3554.test.lua
+++ b/test/sql-tap/tkt3554.test.lua
@@ -26,7 +26,8 @@ test:plan(4)
 test:do_execsql_test(
     "tkt3544-1.1",
     [[
-        CREATE TABLE test ( obj TEXT, t1 INT , t2 INT , PRIMARY KEY(obj, t1, t2) );
+        CREATE TABLE test (id INT PRIMARY KEY AUTOINCREMENT, obj TEXT, t1 INT , t2 INT);
+        CREATE UNIQUE INDEX testi1 ON test(obj, t1, t2);
 
         CREATE TRIGGER test_insert BEFORE INSERT ON test BEGIN
           UPDATE test SET t1 = new.t1
@@ -49,8 +50,8 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3544-1.2",
     [[
-        INSERT INTO test VALUES('a', 10000, 11000);
-        SELECT * FROM test;
+        INSERT INTO test(obj, t1, t2) VALUES('a', 10000, 11000);
+        SELECT obj, t1, t2 FROM test;
     ]], {
         -- <tkt3544-1.2>
         "a", 10000, 11000
@@ -61,9 +62,9 @@ test:do_test(
     "tkt3544-1.3",
     function()
         test:execsql [[
-            INSERT INTO test VALUES('a', 9000, 10500);
+            INSERT INTO test(obj, t1, t2) VALUES('a', 9000, 10500);
         ]]
-        return test:execsql " SELECT * FROM test "
+        return test:execsql " SELECT obj, t1, t2 FROM test "
     end, {
         -- <tkt3544-1.3>
         "a", 9000, 11000
@@ -74,9 +75,9 @@ test:do_test(
     "tkt3544-1.4",
     function()
         test:execsql [[
-            INSERT INTO test VALUES('a', 10000, 12000);
+            INSERT INTO test(obj, t1, t2) VALUES('a', 10000, 12000);
         ]]
-        return test:execsql " SELECT * FROM test "
+        return test:execsql " SELECT obj, t1, t2 FROM test "
     end, {
         -- <tkt3544-1.4>
         "a", 9000, 12000
diff --git a/test/sql-tap/trigger2.test.lua b/test/sql-tap/trigger2.test.lua
index 5d84c312a..b9b6dae74 100755
--- a/test/sql-tap/trigger2.test.lua
+++ b/test/sql-tap/trigger2.test.lua
@@ -61,10 +61,10 @@ test:plan(26)
 test:catchsql " pragma recursive_triggers = off "
 -- 1.
 ii = 0
-tbl_definitions = { "CREATE TABLE tbl (a INTEGER PRIMARY KEY, b INT );",
-                    "CREATE TABLE tbl (a  INT PRIMARY KEY, b INT );",
-                    "CREATE TABLE tbl (a INT , b  INT PRIMARY KEY);",
-                    "CREATE TABLE tbl (a INT , b INTEGER PRIMARY KEY);" }
+tbl_definitions = { "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INTEGER UNIQUE, b INT );",
+                    "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INT UNIQUE, b INT );",
+                    "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INT, b INT UNIQUE );",
+                    "CREATE TABLE tbl (id INT PRIMARY KEY AUTOINCREMENT, a INT, b INTEGER UNIQUE );"}
 -- Tarantool: temporary tables are not supported so far. #2119
 -- table.insert(tbl_definitions,"CREATE TEMP TABLE tbl (a, b INTEGER PRIMARY KEY);")
 -- table.insert(tbl_definitions,"CREATE TEMP TABLE tbl (a INTEGER PRIMARY KEY, b);")
@@ -81,8 +81,8 @@ for _, tbl_defn in ipairs(tbl_definitions) do
     ]]
     test:execsql(tbl_defn)
     test:execsql [[
-        INSERT INTO tbl VALUES(1, 2);
-        INSERT INTO tbl VALUES(3, 4);
+        INSERT INTO tbl(a, b) VALUES(1, 2);
+        INSERT INTO tbl(a, b) VALUES(3, 4);
     ]]
     test:execsql [[
         CREATE TABLE rlog (idx INTEGER PRIMARY KEY, old_a INT , old_b INT , db_sum_a INT , db_sum_b INT , new_a INT , new_b INT );
@@ -140,8 +140,8 @@ for _, tbl_defn in ipairs(tbl_definitions) do
     test:execsql [[
         DELETE FROM tbl;
         DELETE FROM rlog;
-        INSERT INTO tbl VALUES (100, 100);
-        INSERT INTO tbl VALUES (300, 200);
+        INSERT INTO tbl(a, b) VALUES (100, 100);
+        INSERT INTO tbl(a, b) VALUES (300, 200);
         CREATE TRIGGER delete_before_row BEFORE DELETE ON tbl FOR EACH ROW
           BEGIN
           INSERT INTO rlog VALUES ( (SELECT coalesce(max(idx),0) + 1 FROM rlog),
@@ -203,7 +203,7 @@ for _, tbl_defn in ipairs(tbl_definitions) do
             INSERT INTO other_tbl VALUES(1, 2);
             INSERT INTO other_tbl VALUES(3, 4);
             -- INSERT INTO tbl SELECT * FROM other_tbl;
-            INSERT INTO tbl VALUES(5, 6);
+            INSERT INTO tbl(a,b) VALUES(5, 6);
             DROP TABLE other_tbl;
 
             SELECT * FROM rlog;
@@ -355,8 +355,8 @@ table.insert(when_triggers,"t2 BEFORE INSERT ON tbl WHEN (SELECT count(*) FROM t
 
 test:execsql [[
     CREATE TABLE tbl (a  INT , b  INT PRIMARY KEY, c INT , d INT );
-    CREATE TABLE log (a  INT PRIMARY KEY);
-    INSERT INTO log VALUES (0);
+    CREATE TABLE log (id INT PRIMARY KEY AUTOINCREMENT, a INT);
+    INSERT INTO log VALUES (0, 0);
 ]]
 for _, trig in ipairs(when_triggers) do
     test:execsql("CREATE TRIGGER "..trig.." BEGIN UPDATE log set a = a + 1; END;")
@@ -368,17 +368,17 @@ test:do_test(
         local r = {}
         table.insert(r, test:execsql([[
         INSERT INTO tbl VALUES(0, 1, 0, 0);     -- 1 (ifcapable subquery)
-        SELECT * FROM log;
+        SELECT a FROM log;
         UPDATE log SET a = 0;]])[1])
 
         table.insert(r, test:execsql([[
         INSERT INTO tbl VALUES(0, 2, 0, 0);     -- 0
-        SELECT * FROM log;
+        SELECT a FROM log;
         UPDATE log SET a = 0;]])[1])
 
         table.insert(r, test:execsql([[
         INSERT INTO tbl VALUES(200, 3, 0, 0);     -- 1
-        SELECT * FROM log;
+        SELECT a FROM log;
         UPDATE log SET a = 0;]])[1])
 
         return r
diff --git a/test/sql-tap/trigger7.test.lua b/test/sql-tap/trigger7.test.lua
index 21ff2af4d..21b015024 100755
--- a/test/sql-tap/trigger7.test.lua
+++ b/test/sql-tap/trigger7.test.lua
@@ -40,7 +40,7 @@ test:do_test(
     "trigger7-2.1",
     function()
         test:execsql [[
-			CREATE TABLE t1(x INT PRIMARY KEY, y INT);
+            CREATE TABLE t1(x INT UNIQUE, y INT, z INT PRIMARY KEY AUTOINCREMENT);
             CREATE TRIGGER r1 AFTER UPDATE OF x ON t1 BEGIN
               SELECT '___update_t1.x___';
             END;
diff --git a/test/sql-tap/triggerB.test.lua b/test/sql-tap/triggerB.test.lua
index 455c3cb3e..414a4d098 100755
--- a/test/sql-tap/triggerB.test.lua
+++ b/test/sql-tap/triggerB.test.lua
@@ -25,9 +25,9 @@ test:plan(201)
 test:do_execsql_test(
     "triggerB-1.1",
     [[
-        CREATE TABLE x(x INTEGER PRIMARY KEY, y INT NOT NULL);
-        INSERT INTO x VALUES(1, 1);
-        INSERT INTO x VALUES(2, 1);
+        CREATE TABLE x(id INTEGER PRIMARY KEY, x INTEGER UNIQUE, y INT NOT NULL);
+        INSERT INTO x VALUES(1, 1, 1);
+        INSERT INTO x VALUES(2, 2, 1);
         CREATE VIEW vx AS SELECT x, y, 0 AS yy FROM x;
         CREATE TRIGGER tx INSTEAD OF UPDATE OF y ON vx
         BEGIN
@@ -57,7 +57,7 @@ test:do_catchsql_test(
         CREATE TRIGGER ty AFTER INSERT ON x BEGIN
            SELECT wen.x; -- Unrecognized name
         END;
-        INSERT INTO x VALUES(1,2);
+        INSERT INTO x VALUES(3,1,2);
     ]], {
         -- <triggerB-2.1>
         1, "no such column: WEN.X"
@@ -81,8 +81,8 @@ test:do_test(
     "triggerB-2.3",
     function()
         test:execsql [[
-            CREATE TABLE t2(a INTEGER PRIMARY KEY, b INT );
-            INSERT INTO t2 VALUES(1,2);
+            CREATE TABLE t2(id INTEGER PRIMARY KEY, a INTEGER UNIQUE, b INT );
+            INSERT INTO t2 VALUES(1, 1,2);
             CREATE TABLE changes(x  INT PRIMARY KEY,y INT );
             CREATE TRIGGER r1t2 AFTER UPDATE ON t2 BEGIN
               INSERT INTO changes VALUES(new.a, new.b);
@@ -128,17 +128,17 @@ test:do_test(
     function()
         test:execsql [[
             CREATE TABLE t3(
-               c0  TEXT PRIMARY KEY,  c1 TEXT ,  c2 TEXT ,  c3 TEXT ,  c4 TEXT ,  c5 TEXT ,  c6 TEXT ,  c7 TEXT ,  c8 TEXT ,  c9 TEXT ,
-               c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT , c18 TEXT , c19 TEXT ,
-               c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT , c28 TEXT , c29 TEXT ,
-               c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT , c38 TEXT , c39 TEXT ,
-               c40 TEXT , c41 TEXT , c42 TEXT , c43 TEXT , c44 TEXT , c45 TEXT , c46 TEXT , c47 TEXT , c48 TEXT , c49 TEXT ,
-               c50 TEXT , c51 TEXT , c52 TEXT , c53 TEXT , c54 TEXT , c55 TEXT , c56 TEXT , c57 TEXT , c58 TEXT , c59 TEXT ,
-               c60 TEXT , c61 TEXT , c62 TEXT , c63 TEXT , c64 TEXT , c65 TEXT
+               id INT PRIMARY KEY, c0 TEXT UNIQUE,  c1 TEXT ,  c2 TEXT ,  c3 TEXT ,  c4 TEXT ,  c5 TEXT ,  c6 TEXT ,  c7 TEXT ,
+               c8 TEXT ,  c9 TEXT , c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT ,
+               c18 TEXT , c19 TEXT , c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT ,
+               c28 TEXT , c29 TEXT , c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT ,
+               c38 TEXT , c39 TEXT , c40 TEXT , c41 TEXT , c42 TEXT , c43 TEXT , c44 TEXT , c45 TEXT , c46 TEXT , c47 TEXT ,
+               c48 TEXT , c49 TEXT , c50 TEXT , c51 TEXT , c52 TEXT , c53 TEXT , c54 TEXT , c55 TEXT , c56 TEXT , c57 TEXT ,
+               c58 TEXT , c59 TEXT , c60 TEXT , c61 TEXT , c62 TEXT , c63 TEXT , c64 TEXT , c65 TEXT
             );
             CREATE TABLE t3_changes(colnum INT PRIMARY KEY, oldval TEXT , newval TEXT );
             INSERT INTO t3 VALUES(
-               'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
+               0, 'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
                'a10','a11','a12','a13','a14','a15','a16','a17','a18','a19',
                'a20','a21','a22','a23','a24','a25','a26','a27','a28','a29',
                'a30','a31','a32','a33','a34','a35','a36','a37','a38','a39',
diff --git a/test/sql-tap/triggerC.test.lua b/test/sql-tap/triggerC.test.lua
index 59a6d2367..f94c5bdbf 100755
--- a/test/sql-tap/triggerC.test.lua
+++ b/test/sql-tap/triggerC.test.lua
@@ -52,7 +52,7 @@ test:execsql " PRAGMA recursive_triggers = on "
 test:do_execsql_test(
     "triggerC-1.1",
     [[
-        CREATE TABLE t1(a TEXT PRIMARY KEY, b TEXT, c TEXT);
+        CREATE TABLE t1(id INT PRIMARY KEY AUTOINCREMENT, a TEXT UNIQUE, b TEXT, c TEXT);
         CREATE TABLE log(t TEXT PRIMARY KEY, a1 TEXT, b1 TEXT, c1 TEXT, a2 TEXT, b2 TEXT, c2 TEXT);
         CREATE TRIGGER trig1 BEFORE INSERT ON t1 BEGIN
           INSERT INTO log VALUES('before', NULL, NULL, NULL, new.a, new.b, new.c);
@@ -82,7 +82,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "triggerC-1.2",
     [[
-        INSERT INTO t1 VALUES('A', 'B', 'C');
+        INSERT INTO t1 VALUES(1, 'A', 'B', 'C');
         SELECT * FROM log ORDER BY t DESC;
     ]], {
         -- <triggerC-1.2>
@@ -96,7 +96,7 @@ test:do_execsql_test(
         SELECT * FROM t1
     ]], {
         -- <triggerC-1.3>
-        "A", "B", "C"
+        1, "A", "B", "C"
         -- </triggerC-1.3>
     })
 
@@ -118,7 +118,7 @@ test:do_execsql_test(
         SELECT * FROM t1
     ]], {
         -- <triggerC-1.5>
-        "a", "B", "C"
+        1, "a", "B", "C"
         -- </triggerC-1.5>
     })
 
@@ -182,7 +182,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "triggerC-1.11",
     [[
-        CREATE TABLE t5 (a INT primary key, b INT, c INT);
+        CREATE TABLE t5 (a INT UNIQUE, b INT PRIMARY KEY, c INT);
         INSERT INTO t5 values (1, 2, 3);
         CREATE TRIGGER au_tbl AFTER UPDATE ON t5 BEGIN
           UPDATE OR IGNORE t5 SET a = new.a, c = 10;
@@ -206,7 +206,7 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "triggerC-1.13",
     [[
-        CREATE TABLE t6(a INTEGER PRIMARY KEY, b INT);
+        CREATE TABLE t6(a INT UNIQUE, b INT PRIMARY KEY);
         INSERT INTO t6 VALUES(1, 2);
         create trigger r1 after update on t6 for each row begin
           SELECT 1;
@@ -222,10 +222,11 @@ test:do_execsql_test(
     "triggerC-1.14",
     [[
         DROP TABLE IF EXISTS t1;
-        CREATE TABLE cnt(n INT PRIMARY KEY);
-        INSERT INTO cnt VALUES(0);
-        CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT UNIQUE, c INT, d INT, e INT);
+        CREATE TABLE cnt(id INT PRIMARY KEY, n INT UNIQUE);
+        INSERT INTO cnt VALUES(0, 0);
+        CREATE TABLE t1(a INT UNIQUE, b INT UNIQUE, c INT, d INT, e INT PRIMARY KEY);
         CREATE INDEX t1cd ON t1(c,d);
+        CREATE UNIQUE INDEX t1a ON t1(a);
         CREATE TRIGGER t1r1 AFTER UPDATE ON t1 BEGIN UPDATE cnt SET n=n+1; END;
         INSERT INTO t1 VALUES(1,2,3,4,5);
         INSERT INTO t1 VALUES(6,7,8,9,10);
@@ -241,7 +242,7 @@ test:do_catchsql_test(
         UPDATE OR ROLLBACK t1 SET a=100;
     ]], {
         -- <triggerC-1.15>
-        1, "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
+        1, "Duplicate key exists in unique index 'unique_unnamed_T1_1' in space 'T1'"
         -- </triggerC-1.15>
     })
 
@@ -712,11 +713,12 @@ test:do_test(
     "triggerC-10.1",
     function()
         test:execsql [[
-            CREATE TABLE t10(a TEXT PRIMARY KEY, updatecnt INT DEFAULT 0);
+            CREATE TABLE t10(id INT PRIMARY KEY, a TEXT, updatecnt INT DEFAULT 0);
+            CREATE UNIQUE INDEX t10i1 ON t10(a);
             CREATE TRIGGER t10_bu BEFORE UPDATE OF a ON t10 BEGIN
               UPDATE t10 SET updatecnt = updatecnt+1 WHERE a = old.a;
             END;
-            INSERT INTO t10 VALUES('hello', 0);
+            INSERT INTO t10 VALUES(0, 'hello', 0);
         ]]
         -- Before the problem was fixed, table t10 would contain the tuple
         -- (world, 0) after running the following script (because the value
@@ -728,7 +730,7 @@ test:do_test(
         ]]
     end, {
         -- <triggerC-10.1>
-        "world", 1
+        0, "world", 1
         -- </triggerC-10.1>
     })
 
@@ -739,7 +741,7 @@ test:do_execsql_test(
         SELECT * FROM t10;
     ]], {
         -- <triggerC-10.2>
-        "tcl", 5
+        0, "tcl", 5
         -- </triggerC-10.2>
     })
 
@@ -748,10 +750,11 @@ test:do_test(
     function()
         test:execsql [[
             CREATE TABLE t11(
-              c1 INT PRIMARY KEY,   c2 INT,  c3 INT,  c4 INT,  c5 INT,  c6 INT,  c7 INT,  c8 INT,  c9 INT, c10 INT,
-              c11 INT, c12 INT, c13 INT, c14 INT, c15 INT, c16 INT, c17 INT, c18 INT, c19 INT, c20 INT,
-              c21 INT, c22 INT, c23 INT, c24 INT, c25 INT, c26 INT, c27 INT, c28 INT, c29 INT, c30 INT,
-              c31 INT, c32 INT, c33 INT, c34 INT, c35 INT, c36 INT, c37 INT, c38 INT, c39 INT, c40 INT
+              c0 INT PRIMARY KEY, c1 INT UNIQUE,   c2 INT,  c3 INT,  c4 INT,  c5 INT,  c6 INT,  c7 INT,
+              c8 INT,  c9 INT, c10 INT, c11 INT, c12 INT, c13 INT, c14 INT, c15 INT, c16 INT,
+              c17 INT, c18 INT, c19 INT, c20 INT, c21 INT, c22 INT, c23 INT, c24 INT, c25 INT,
+              c26 INT, c27 INT, c28 INT, c29 INT, c30 INT, c31 INT, c32 INT, c33 INT, c34 INT,
+              c35 INT, c36 INT, c37 INT, c38 INT, c39 INT, c40 INT
             );
 
             CREATE TRIGGER t11_bu BEFORE UPDATE OF c1 ON t11 BEGIN
@@ -759,7 +762,7 @@ test:do_test(
             END;
 
             INSERT INTO t11 VALUES(
-              1,   2,  3,  4,  5,  6,  7,  8,  9, 10,
+              0, 1,   2,  3,  4,  5,  6,  7,  8,  9, 10,
               11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
               21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
               31, 32, 33, 34, 35, 36, 37, 38, 39, 40
@@ -775,7 +778,7 @@ test:do_test(
         ]]
     end, {
         -- <triggerC-10.3>
-        5, 2, 3, 35, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 32, 33, 22, 34, 35, 36, 37, 38, 39, 40
+        0, 5, 2, 3, 35, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 32, 33, 22, 34, 35, 36, 37, 38, 39, 40
         -- </triggerC-10.3>
     })
 
diff --git a/test/sql-tap/triggerD.test.lua b/test/sql-tap/triggerD.test.lua
index f4d1c29a8..37b33ae2d 100755
--- a/test/sql-tap/triggerD.test.lua
+++ b/test/sql-tap/triggerD.test.lua
@@ -36,7 +36,7 @@ test:do_test(
     "triggerD-1.1",
     function()
         return test:execsql [[
-            CREATE TABLE t1(rowid INT PRIMARY KEY, oid INT, _rowid_ INT, x INT);
+            CREATE TABLE t1(id INT PRIMARY KEY, rowid INT UNIQUE, oid INT, _rowid_ INT, x INT);
             CREATE TABLE log(a TEXT PRIMARY KEY,b INT,c INT,d INT,e INT);
             CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN
               INSERT INTO log VALUES('r1', new.rowid, new.oid, new._rowid_, new.x);
@@ -69,7 +69,7 @@ test:do_test(
     "triggerD-1.2",
     function()
         return test:execsql [[
-            INSERT INTO t1 VALUES(100,200,300,400);
+            INSERT INTO t1 VALUES(0, 100,200,300,400);
             SELECT * FROM log
         ]]
     end, {
diff --git a/test/sql-tap/update.test.lua b/test/sql-tap/update.test.lua
index d0fc66ebb..82168932e 100755
--- a/test/sql-tap/update.test.lua
+++ b/test/sql-tap/update.test.lua
@@ -888,15 +888,16 @@ test:do_catchsql_test("update-9.4", [[
 test:do_execsql_test("update-10.1", [[
   DROP TABLE test1;
   CREATE TABLE t1(
-     a integer primary key,
+     a integer UNIQUE,
      b  INT UNIQUE,
      c INT , d INT ,
      e INT , f INT ,
-     UNIQUE(c,d)
+     UNIQUE(c,d),
+     id INT PRIMARY KEY AUTOINCREMENT
   );
-  INSERT INTO t1 VALUES(1,2,3,4,5,6);
-  INSERT INTO t1 VALUES(2,3,4,4,6,7);
-  SELECT * FROM t1
+  INSERT INTO t1(a,b,c,d,e,f) VALUES(1,2,3,4,5,6);
+  INSERT INTO t1(a,b,c,d,e,f) VALUES(2,3,4,4,6,7);
+  SELECT a,b,c,d,e,f FROM t1
 ]], {
   -- <update-10.1>
   1, 2, 3, 4, 5, 6, 2, 3, 4, 4, 6, 7
@@ -905,7 +906,7 @@ test:do_execsql_test("update-10.1", [[
 
 test:do_catchsql_test("update-10.2", [[
   UPDATE t1 SET a=1, e=9 WHERE f=6;
-  SELECT * FROM t1;
+  SELECT a,b,c,d,e,f FROM t1;
 ]], {
   -- <update-10.2>
   0, {1, 2, 3, 4, 9, 6, 2, 3, 4, 4, 6, 7}
@@ -914,15 +915,15 @@ test:do_catchsql_test("update-10.2", [[
 
 test:do_catchsql_test("update-10.3", [[
   UPDATE t1 SET a=1, e=10 WHERE f=7;
-  SELECT * FROM t1;
+  SELECT a,b,c,d,e,f FROM t1;
 ]], {
   -- <update-10.3>
-  1, "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
+  1, "Duplicate key exists in unique index 'unique_unnamed_T1_1' in space 'T1'"
   -- </update-10.3>
 })
 
 test:do_catchsql_test("update-10.4", [[
-  SELECT * FROM t1;
+  SELECT a,b,c,d,e,f FROM t1;
 ]], {
   -- <update-10.4>
   0, {1, 2, 3, 4, 9, 6, 2, 3, 4, 4, 6, 7}
@@ -931,7 +932,7 @@ test:do_catchsql_test("update-10.4", [[
 
 test:do_catchsql_test("update-10.5", [[
   UPDATE t1 SET b=2, e=11 WHERE f=6;
-  SELECT * FROM t1;
+  SELECT a,b,c,d,e,f FROM t1;
 ]], {
   -- <update-10.5>
   0, {1, 2, 3, 4, 11, 6, 2, 3, 4, 4, 6, 7}
@@ -940,7 +941,7 @@ test:do_catchsql_test("update-10.5", [[
 
 test:do_catchsql_test("update-10.6", [[
   UPDATE t1 SET b=2, e=12 WHERE f=7;
-  SELECT * FROM t1;
+  SELECT a,b,c,d,e,f FROM t1;
 ]], {
   -- <update-10.6>
   1, "Duplicate key exists in unique index 'unique_unnamed_T1_2' in space 'T1'"
@@ -948,7 +949,7 @@ test:do_catchsql_test("update-10.6", [[
 })
 
 test:do_catchsql_test("update-10.7", [[
-  SELECT * FROM t1;
+  SELECT a,b,c,d,e,f FROM t1;
 ]], {
   -- <update-10.7>
   0, {1, 2, 3, 4, 11, 6, 2, 3, 4, 4, 6, 7}
@@ -957,7 +958,7 @@ test:do_catchsql_test("update-10.7", [[
 
 test:do_catchsql_test("update-10.8", [[
   UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
-  SELECT * FROM t1;
+  SELECT a,b,c,d,e,f FROM t1;
 ]], {
   -- <update-10.8>
   0, {1, 2, 3, 4, 13, 6, 2, 3, 4, 4, 6, 7}
@@ -966,7 +967,7 @@ test:do_catchsql_test("update-10.8", [[
 
 test:do_catchsql_test("update-10.9", [[
   UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
-  SELECT * FROM t1;
+  SELECT a,b,c,d,e,f FROM t1;
 ]], {
   -- <update-10.9>
   1, "Duplicate key exists in unique index 'unique_unnamed_T1_3' in space 'T1'"
@@ -974,7 +975,7 @@ test:do_catchsql_test("update-10.9", [[
 })
 
 test:do_catchsql_test("update-10.10", [[
-  SELECT * FROM t1;
+  SELECT a,b,c,d,e,f FROM t1;
 ]], {
   -- <update-10.10>
   0, {1, 2, 3, 4, 13, 6, 2, 3, 4, 4, 6, 7}
diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua
index 28b3a858b..97585c13b 100755
--- a/test/sql-tap/with1.test.lua
+++ b/test/sql-tap/with1.test.lua
@@ -25,7 +25,7 @@ testprefix = "with1"
 -- end
 
 test:do_execsql_test(1.0, [[
-  CREATE TABLE t1(x INTEGER PRIMARY KEY, y INTEGER);
+  CREATE TABLE t1(x INTEGER UNIQUE, y INTEGER, z INTEGER PRIMARY KEY);
   WITH x(a) AS ( SELECT * FROM t1) SELECT 10
 ]], {
   -- <1.0>
@@ -42,7 +42,7 @@ test:do_execsql_test(1.1, [[
 })
 
 test:do_execsql_test(1.2, [[
-  WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
+  WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,1,2);
 ]], {
   -- <1.2>
   
@@ -185,15 +185,15 @@ test:do_catchsql_test(3.6, [[
 ---------------------------------------------------------------------------
 test:do_execsql_test(4.1, [[
   DROP TABLE IF EXISTS t1;
-  CREATE TABLE t1(x INT PRIMARY KEY);
-  INSERT INTO t1 VALUES(1);
-  INSERT INTO t1 VALUES(2);
-  INSERT INTO t1 VALUES(3);
-  INSERT INTO t1 VALUES(4);
+  CREATE TABLE t1(x INT UNIQUE, z INT PRIMARY KEY AUTOINCREMENT);
+  INSERT INTO t1(x) VALUES(1);
+  INSERT INTO t1(x) VALUES(2);
+  INSERT INTO t1(x) VALUES(3);
+  INSERT INTO t1(x) VALUES(4);
 
   WITH dset AS ( SELECT 2 UNION ALL SELECT 4 )
   DELETE FROM t1 WHERE x IN dset;
-  SELECT * FROM t1;
+  SELECT (x) FROM t1;
 ]], {
   -- <4.1>
   1, 3
@@ -202,18 +202,18 @@ test:do_execsql_test(4.1, [[
 
 test:do_execsql_test(4.2, [[
   WITH iset AS ( SELECT 2 UNION ALL SELECT 4 )
-  INSERT INTO t1 SELECT * FROM iset;
-  SELECT * FROM t1;
+  INSERT INTO t1(x) SELECT * FROM iset;
+  SELECT x FROM t1;
 ]], {
   -- <4.2>
-  1, 2, 3, 4
+  1, 3, 2, 4
   -- </4.2>
 })
 
 test:do_execsql_test(4.3, [[
   WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
   UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
-  SELECT * FROM t1;
+  SELECT x FROM t1;
 ]], {
   -- <4.3>
   1, 3, 8, 9
diff --git a/test/sql/on-conflict.result b/test/sql/on-conflict.result
index 731f03c66..97ec77526 100644
--- a/test/sql/on-conflict.result
+++ b/test/sql/on-conflict.result
@@ -86,10 +86,10 @@ box.sql.execute("DROP TABLE a;")
 ...
 -- gh-3566: UPDATE OR IGNORE causes deletion of old entry.
 --
-box.sql.execute("CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);")
+box.sql.execute("CREATE TABLE tj (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
 ---
 ...
-box.sql.execute("INSERT INTO tj VALUES (1, 2), (2, 3);")
+box.sql.execute("INSERT INTO tj VALUES (1, 1, 2), (2, 2, 3);")
 ---
 ...
 box.sql.execute("CREATE UNIQUE INDEX i ON tj (s2);")
@@ -98,7 +98,7 @@ box.sql.execute("CREATE UNIQUE INDEX i ON tj (s2);")
 box.sql.execute("UPDATE OR IGNORE tj SET s1 = s1 + 1;")
 ---
 ...
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
 ---
 - - [1, 2]
   - [3, 3]
@@ -106,7 +106,7 @@ box.sql.execute("SELECT * FROM tj;")
 box.sql.execute("UPDATE OR IGNORE tj SET s2 = s2 + 1;")
 ---
 ...
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
 ---
 - - [1, 2]
   - [3, 4]
diff --git a/test/sql/on-conflict.test.lua b/test/sql/on-conflict.test.lua
index aa58b854b..9486942b9 100644
--- a/test/sql/on-conflict.test.lua
+++ b/test/sql/on-conflict.test.lua
@@ -35,13 +35,13 @@ box.sql.execute("DROP TABLE a;")
 
 -- gh-3566: UPDATE OR IGNORE causes deletion of old entry.
 --
-box.sql.execute("CREATE TABLE tj (s1 INT PRIMARY KEY, s2 INT);")
-box.sql.execute("INSERT INTO tj VALUES (1, 2), (2, 3);")
+box.sql.execute("CREATE TABLE tj (s0 INT PRIMARY KEY, s1 INT UNIQUE, s2 INT);")
+box.sql.execute("INSERT INTO tj VALUES (1, 1, 2), (2, 2, 3);")
 box.sql.execute("CREATE UNIQUE INDEX i ON tj (s2);")
 box.sql.execute("UPDATE OR IGNORE tj SET s1 = s1 + 1;")
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
 box.sql.execute("UPDATE OR IGNORE tj SET s2 = s2 + 1;")
-box.sql.execute("SELECT * FROM tj;")
+box.sql.execute("SELECT s1, s2 FROM tj;")
 
 -- gh-3565: INSERT OR REPLACE causes assertion fault.
 --
diff --git a/test/sql/row-count.result b/test/sql/row-count.result
index d4c86ac2b..d6248eb0f 100644
--- a/test/sql/row-count.result
+++ b/test/sql/row-count.result
@@ -27,10 +27,10 @@ box.sql.execute("SELECT ROW_COUNT();")
 ---
 - - [1]
 ...
-box.sql.execute("CREATE TABLE t3 (i1 INT PRIMARY KEY, i2 INT);")
+box.sql.execute("CREATE TABLE t3 (i1 INT UNIQUE, i2 INT, i3 INT PRIMARY KEY);")
 ---
 ...
-box.sql.execute("INSERT INTO t3 VALUES (0, 0);")
+box.sql.execute("INSERT INTO t3 VALUES (0, 0, 0);")
 ---
 ...
 box.sql.execute("SELECT ROW_COUNT();")
@@ -80,7 +80,7 @@ box.sql.execute("SELECT ROW_COUNT();")
 ---
 - - [4]
 ...
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
 ---
 ...
 box.sql.execute("TRUNCATE TABLE t3;")
@@ -90,7 +90,7 @@ box.sql.execute("SELECT ROW_COUNT();")
 ---
 - - [0]
 ...
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
 ---
 ...
 box.sql.execute("UPDATE t3 SET i2 = 666;")
@@ -110,7 +110,7 @@ box.sql.execute("SELECT ROW_COUNT();")
 ---
 - - [3]
 ...
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
 ---
 ...
 box.sql.execute("DELETE FROM t3")
diff --git a/test/sql/row-count.test.lua b/test/sql/row-count.test.lua
index 45a39d19a..f10807fff 100644
--- a/test/sql/row-count.test.lua
+++ b/test/sql/row-count.test.lua
@@ -9,8 +9,8 @@ box.sql.execute("SELECT ROW_COUNT();")
 box.sql.execute("SELECT ROW_COUNT();")
 box.sql.execute("CREATE TABLE t2 (s1 CHAR(10) PRIMARY KEY, s2 CHAR(10) REFERENCES t1 ON DELETE CASCADE);")
 box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("CREATE TABLE t3 (i1 INT PRIMARY KEY, i2 INT);")
-box.sql.execute("INSERT INTO t3 VALUES (0, 0);")
+box.sql.execute("CREATE TABLE t3 (i1 INT UNIQUE, i2 INT, i3 INT PRIMARY KEY);")
+box.sql.execute("INSERT INTO t3 VALUES (0, 0, 0);")
 box.sql.execute("SELECT ROW_COUNT();")
 box.sql.execute("CREATE TRIGGER x AFTER DELETE ON t1 FOR EACH ROW BEGIN UPDATE t3 SET i1 = i1 + ROW_COUNT(); END;")
 box.sql.execute("SELECT ROW_COUNT();")
@@ -25,10 +25,10 @@ box.sql.execute("REPLACE INTO t2 VALUES('a', 'c');")
 box.sql.execute("SELECT ROW_COUNT();")
 box.sql.execute("DELETE FROM t1;")
 box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
 box.sql.execute("TRUNCATE TABLE t3;")
 box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
 box.sql.execute("UPDATE t3 SET i2 = 666;")
 box.sql.execute("SELECT ROW_COUNT();")
 -- gh-3816: DELETE optimization returns valid number of
@@ -36,7 +36,7 @@ box.sql.execute("SELECT ROW_COUNT();")
 --
 box.sql.execute("DELETE FROM t3 WHERE 0 = 0;")
 box.sql.execute("SELECT ROW_COUNT();")
-box.sql.execute("INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);")
+box.sql.execute("INSERT INTO t3 VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);")
 box.sql.execute("DELETE FROM t3")
 box.sql.execute("SELECT ROW_COUNT();")
 -- But triggers still should't be accounted.
diff --git a/test/sql/triggers.result b/test/sql/triggers.result
index 45d231f72..bbfff3302 100644
--- a/test/sql/triggers.result
+++ b/test/sql/triggers.result
@@ -253,7 +253,7 @@ box.sql.execute("DROP TABLE T1;")
 box.sql.execute("PRAGMA sql_default_engine ('vinyl');")
 ---
 ...
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
 ---
 ...
 box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
@@ -262,13 +262,13 @@ box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE
 box.sql.execute("PRAGMA sql_default_engine('memtx');")
 ---
 ...
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
 ---
 ...
-box.sql.execute("INSERT INTO m VALUES ('0');")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
 ---
 ...
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
 ---
 ...
 box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
@@ -289,7 +289,7 @@ box.sql.execute("DROP TABLE n;")
 box.sql.execute("PRAGMA sql_default_engine ('memtx');")
 ---
 ...
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
 ---
 ...
 box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
@@ -298,13 +298,13 @@ box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE
 box.sql.execute("PRAGMA sql_default_engine('vinyl');")
 ---
 ...
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
 ---
 ...
-box.sql.execute("INSERT INTO m VALUES ('0');")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
 ---
 ...
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
 ---
 ...
 box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua
index a322b2f02..eb3f41ef5 100644
--- a/test/sql/triggers.test.lua
+++ b/test/sql/triggers.test.lua
@@ -101,12 +101,12 @@ box.sql.execute("DROP TABLE T1;")
 --
 -- Case 1: Src 'vinyl' table; Dst 'memtx' table
 box.sql.execute("PRAGMA sql_default_engine ('vinyl');")
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
 box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
 box.sql.execute("PRAGMA sql_default_engine('memtx');")
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
-box.sql.execute("INSERT INTO m VALUES ('0');")
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
 box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
 
 -- ANALYZE operates with _sql_stat{1,4} tables should work
@@ -117,12 +117,12 @@ box.sql.execute("DROP TABLE n;")
 
 -- Case 2: Src 'memtx' table; Dst 'vinyl' table
 box.sql.execute("PRAGMA sql_default_engine ('memtx');")
-box.sql.execute("CREATE TABLE m (s1 TEXT PRIMARY KEY);")
+box.sql.execute("CREATE TABLE m (s0 INT PRIMARY KEY, s1 TEXT UNIQUE);")
 box.sql.execute("CREATE TRIGGER m1 BEFORE UPDATE ON m FOR EACH ROW BEGIN UPDATE n SET s2 = DATETIME('now'); END;")
 box.sql.execute("PRAGMA sql_default_engine('vinyl');")
-box.sql.execute("CREATE TABLE n (s1 TEXT PRIMARY KEY, s2 REAL);")
-box.sql.execute("INSERT INTO m VALUES ('0');")
-box.sql.execute("INSERT INTO n VALUES ('',null);")
+box.sql.execute("CREATE TABLE n (s0 INT PRIMARY KEY, s1 TEXT UNIQUE, s2 REAL);")
+box.sql.execute("INSERT INTO m VALUES (0, '0');")
+box.sql.execute("INSERT INTO n VALUES (0, '',null);")
 box.sql.execute("UPDATE m SET s1 = 'The Rain In Spain';")
 
 -- ANALYZE operates with _sql_stat{1,4} tables should work
-- 
2.19.2





More information about the Tarantool-patches mailing list