[tarantool-patches] Re: [PATCH v3 2/3] box: run check constraint tests on space alter

[Kirill Shcherbatov <kshcherbatov@tarantool.org>]

> One unpleasant “feature” that I’ve noticed:
> 
> create table t4 (id int primary key check(id > id*id))
> insert into t4 values(0.5)
> - error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T4'':
>     id > id*id’
> 
> Conversion to INT occurs before tuples reaches on_replace trigger.
> We should discuss what to do with that. I guess you already raised
> this question when we were talking about typeof() functions inside
> check constraint.
We should live with it ;)

> Also, I’ve noticed that check expression bytecode is not optimized.
> For example:
> 
> create table t4 (id int primary key check(id > 6+7*3-1*31))
> insert into t4 values(1)
> 
> VDBE Program Listing:
>  102>    0 Init             0    7    0               00 Start at 7
>  102>    1 Variable         1    1    0               00 r[1]=parameter(1,)
>  102>    2 Noop             0    0    0               00 BEGIN: ck constraint CK_CONSTRAINT_1_T4 test
>  102>    3 Gt               3    6    1 ({type = binary}) 13 if r[1]>r[3] goto 6
>  102>    4 Halt            21    2    0 Check constraint failed 'CK_CONSTRAINT_1_T4': id > 6+7*3-1*31 C3 
>  102>    5 Noop             0    0    0               00 END: ck constraint CK_CONSTRAINT_1_T4 test
>  102>    6 Halt             0    0    0               00 
>  102>    7 Integer          6    4    0               00 r[4]=6
>  102>    8 Integer          7    6    0               00 r[6]=7
>  102>    9 Integer          3    7    0               00 r[7]=3
>  102>   10 Multiply         7    6    5               00 r[5]=r[7]*r[6]
>  102>   11 Add              5    4    2               00 r[2]=r[5]+r[4]
>  102>   12 Integer          1    4    0               00 r[4]=1
>  102>   13 Integer         31    7    0               00 r[7]=31
>  102>   14 Multiply         7    4    5               00 r[5]=r[7]*r[4]
>  102>   15 Subtract         5    2    3               00 r[3]=r[2]-r[5]
>  102>   16 Goto             0    1    0               00 
> 
> Looks extremely inefficient. We could once traverse tree and avoid
> these calculations on each insertion.

As we discussed before, SQL_ECEL_FACTOR doesn't solve this problem.
Now SQLLite codebase that we use lacks constant propagation feature.

>> +	uint32_t field_count = space_def->field_count;
>> +	int new_tuple_reg = sqlGetTempRange(&parser, field_count);
> 
> Nit: new_tuple_reg IMHO is very general name. Mb it would be better
> to call it like “bind_tuple_reg”?

Don't mind. Done

> 
>> +	*new_tuple_var = parser.nVar + 1;
> 
> Won’t it be always 1? You’ve just created parser and no byte code is generated yet,
> so how it could be different from 1? sqlGetTempRange() doesn’t affect nVar AFAIU.

Don't mind. Done

> 
>> +	struct Expr bind = {.op = TK_VARIABLE, .u.zToken = "?"};
>> +	for (uint32_t i = 0; i < field_count; i++) {
>> +		sqlExprAssignVarNumber(&parser, &bind, 1);
>> +		sqlExprCodeTarget(&parser, &bind, new_tuple_reg + i);
> 
> You don’t need to call this huge function: all you need is this:
> sqlVdbeAddOp2(v, OP_Variable, bind.iColumn, new_tuple_reg + i);

	uint32_t field_count = space_def->field_count;
	int bind_tuple_reg = sqlGetTempRange(&parser, field_count);
	for (uint32_t i = 0; i < field_count; i++) {
		sqlVdbeAddOp2(v, OP_Variable, ++parser.nVar,
			      bind_tuple_reg + i);
	}

>> +	sql_finish_coding(&parser);
> 
> Can we just add OP_Halt + sqlVdbeMakeReady() ?
No, we can't. This doesn't work.

> uint32_t field_count =
>        MIN(tuple_field_count, space->def->field_count);
> 
> Comment (in code) please this part. How space field count could
> be less than tuple field count?

	/*
	 * When last format fields are nullable, they are
	 * 'optional' i.e. they may not be present in the tuple.
	 */
	

>> +        1, "Check constraint failed 'CK_CONSTRAINT_1_T4': x+y==11\n            OR x*y==12\n            OR x/y BETWEEN 5 AND 8\n            OR -x==y+10”
> 
> Mb it is worth removing from check expr extra spaces and
> return carriage symbols to make it more readable?
Consider my new helper in previous patch:


+/**
+ * Prepare a 0-terminated string in the wptr memory buffer that
+ * does not contain a sequence of more than one whatespace
+ * character. Routine enforces ' ' (space) as whitespace
+ * delimiter.
+ * The wptr buffer is expected to have str_len + 1 bytes
+ * (this is the expected scenario where no extra whitespace
+ * characters preset in the source string).
+ * @param wptr The destination memory buffer of size
+ * @a str_len + 1.
+ * @param str The source string to be copied.
+ * @param str_len The source string @a str length.
+ */
+static void
+trim_space_snprintf(char *wptr, const char *str, uint32_t str_len)
+{
+ const char *str_end = str + str_len;
+ bool is_prev_chr_space = false;
+ while (str < str_end) {
+ if (isspace((unsigned char)*str)) {
+ if (!is_prev_chr_space)
+ *wptr++ = ' ';
+ is_prev_chr_space = true;
+ str++;
+ continue;
+ }
+ is_prev_chr_space = false;
+ *wptr++ = *str++;
+ }
+ *wptr = '\0';
+}

It is called on build

>> +s:replace({2, 1, 3})
> 
> What about NULLs and NOT NULL check?

I've added a new test (maybe as a part of previous patch)

===============================================

To perform ck constraints tests before insert or update space operation,
we use precompiled VDBE machine is associated with each ck constraint,
that is executed in on_replace trigger. Each ck constraint VDBE code is
consists of
1) prologue code that maps new(or updated) tuple fields via bindings,
2) ck constraint code is generated by CK constraint AST.
In case of ck constraint error the transaction is aborted and ck
constraint error is handled as diag message.
Each ck constraint use own on_replace trigger.

Closes #3691
---
 src/box/alter.cc                      |  58 ++++++-
 src/box/ck_constraint.c               | 163 ++++++++++++++++++-
 src/box/ck_constraint.h               |  16 +-
 src/box/errcode.h                     |   1 +
 src/box/sql/insert.c                  |  92 +++--------
 src/box/sql/sqlInt.h                  |  26 +++
 src/box/sql/vdbeapi.c                 |   8 -
 test/box/misc.result                  |   1 +
 test/sql-tap/check.test.lua           |  32 ++--
 test/sql-tap/fkey2.test.lua           |   4 +-
 test/sql-tap/table.test.lua           |  12 +-
 test/sql/checks.result                | 217 ++++++++++++++++++++++++--
 test/sql/checks.test.lua              |  61 ++++++++
 test/sql/errinj.result                |  18 ++-
 test/sql/gh-2981-check-autoinc.result |  12 +-
 test/sql/types.result                 |   3 +-
 16 files changed, 586 insertions(+), 138 deletions(-)

diff --git a/src/box/alter.cc b/src/box/alter.cc
index 2126ab369..9c560e85d 100644
--- a/src/box/alter.cc
+++ b/src/box/alter.cc
@@ -1410,14 +1410,29 @@ void
 RebuildCkConstraints::alter(struct alter_space *alter)
 {
 	rlist_swap(&alter->new_space->ck_constraint, &ck_constraint);
+	struct ck_constraint *ck;
+	rlist_foreach_entry(ck, &alter->old_space->ck_constraint, link)
+		trigger_clear(&ck->trigger);
 	rlist_swap(&ck_constraint, &alter->old_space->ck_constraint);
+	rlist_foreach_entry(ck, &alter->new_space->ck_constraint, link) {
+		/**
+		 * Triggers would be swapped later on
+		 * alter_space_do.
+		 */
+		trigger_add(&alter->old_space->on_replace, &ck->trigger);
+	}
 }
 
 void
 RebuildCkConstraints::rollback(struct alter_space *alter)
 {
 	rlist_swap(&alter->old_space->ck_constraint, &ck_constraint);
+	struct ck_constraint *ck;
+	rlist_foreach_entry(ck, &alter->new_space->ck_constraint, link)
+		trigger_clear(&ck->trigger);
 	rlist_swap(&ck_constraint, &alter->new_space->ck_constraint);
+	rlist_foreach_entry(ck, &alter->old_space->ck_constraint, link)
+		trigger_add(&alter->new_space->on_replace, &ck->trigger);
 }
 
 RebuildCkConstraints::~RebuildCkConstraints()
@@ -1435,6 +1450,35 @@ RebuildCkConstraints::~RebuildCkConstraints()
 	}
 }
 
+/**
+ * Move CK constraints from old space to the new one.
+ * Despite RebuildCkConstraints, this operation doesn't perform
+ * objects rebuild. This may be used in scenarios where space
+ * format doesn't change i.e. in alter index or space trim
+ * requests.
+ */
+class MoveCkConstraints: public AlterSpaceOp
+{
+public:
+	MoveCkConstraints(struct alter_space *alter) : AlterSpaceOp(alter) {}
+	virtual void alter(struct alter_space *alter);
+	virtual void rollback(struct alter_space *alter);
+};
+
+void
+MoveCkConstraints::alter(struct alter_space *alter)
+{
+	rlist_swap(&alter->new_space->ck_constraint,
+		   &alter->old_space->ck_constraint);
+}
+
+void
+MoveCkConstraints::rollback(struct alter_space *alter)
+{
+	rlist_swap(&alter->new_space->ck_constraint,
+		   &alter->old_space->ck_constraint);
+}
+
 /* }}} */
 
 /**
@@ -2145,8 +2189,8 @@ on_replace_dd_index(struct trigger * /* trigger */, void *event)
 	 * old space.
 	 */
 	alter_space_move_indexes(alter, iid + 1, old_space->index_id_max + 1);
+	(void) new MoveCkConstraints(alter);
 	/* Add an op to update schema_version on commit. */
-	(void) new RebuildCkConstraints(alter);
 	(void) new UpdateSchemaVersion(alter);
 	alter_space_do(txn, alter);
 	scoped_guard.is_active = false;
@@ -2214,7 +2258,7 @@ on_replace_dd_truncate(struct trigger * /* trigger */, void *event)
 		(void) new TruncateIndex(alter, old_index->def->iid);
 	}
 
-	(void) new RebuildCkConstraints(alter);
+	(void) new MoveCkConstraints(alter);
 	alter_space_do(txn, alter);
 	scoped_guard.is_active = false;
 }
@@ -4152,12 +4196,14 @@ on_replace_ck_constraint_rollback(struct trigger *trigger, void *event)
 		assert(space_ck_constraint_by_name(space,
 				ck->def->name, strlen(ck->def->name)) == NULL);
 		rlist_add_entry(&space->ck_constraint, ck, link);
+		trigger_add(&space->on_replace, &ck->trigger);
 	}  else if (stmt->new_tuple != NULL && stmt->old_tuple == NULL) {
 		/* Rollback INSERT check constraint. */
 		assert(space != NULL);
 		assert(space_ck_constraint_by_name(space,
 				ck->def->name, strlen(ck->def->name)) != NULL);
 		rlist_del_entry(ck, link);
+		trigger_clear(&ck->trigger);
 		ck_constraint_delete(ck);
 	} else {
 		/* Rollback REPLACE check constraint. */
@@ -4167,7 +4213,9 @@ on_replace_ck_constraint_rollback(struct trigger *trigger, void *event)
 			space_ck_constraint_by_name(space, name, strlen(name));
 		assert(new_ck != NULL);
 		rlist_del_entry(new_ck, link);
+		trigger_clear(&new_ck->trigger);
 		rlist_add_entry(&space->ck_constraint, ck, link);
+		trigger_add(&space->on_replace, &ck->trigger);
 		ck_constraint_delete(new_ck);
 	}
 }
@@ -4234,9 +4282,12 @@ on_replace_dd_ck_constraint(struct trigger * /* trigger*/, void *event)
 		const char *name = new_ck_constraint->def->name;
 		struct ck_constraint *old_ck_constraint =
 			space_ck_constraint_by_name(space, name, strlen(name));
-		if (old_ck_constraint != NULL)
+		if (old_ck_constraint != NULL) {
 			rlist_del_entry(old_ck_constraint, link);
+			trigger_clear(&old_ck_constraint->trigger);
+		}
 		rlist_add_entry(&space->ck_constraint, new_ck_constraint, link);
+		trigger_add(&space->on_replace, &new_ck_constraint->trigger);
 		on_commit->data = old_tuple == NULL ? new_ck_constraint :
 						      old_ck_constraint;
 		on_rollback->data = on_commit->data;
@@ -4252,6 +4303,7 @@ on_replace_dd_ck_constraint(struct trigger * /* trigger*/, void *event)
 			space_ck_constraint_by_name(space, name, name_len);
 		assert(old_ck_constraint != NULL);
 		rlist_del_entry(old_ck_constraint, link);
+		trigger_clear(&old_ck_constraint->trigger);
 		on_commit->data = old_ck_constraint;
 		on_rollback->data = old_ck_constraint;
 	}
diff --git a/src/box/ck_constraint.c b/src/box/ck_constraint.c
index 6d9f8ea44..52f615227 100644
--- a/src/box/ck_constraint.c
+++ b/src/box/ck_constraint.c
@@ -28,10 +28,15 @@
  * THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
  * SUCH DAMAGE.
  */
+#include "bind.h"
 #include "ck_constraint.h"
 #include "errcode.h"
+#include "schema.h"
+#include "session.h"
 #include "sql.h"
 #include "sql/sqlInt.h"
+#include "sql/vdbeInt.h"
+#include "tuple.h"
 
 /**
  * Resolve space_def references for check constraint via AST
@@ -54,6 +59,143 @@ ck_constraint_resolve_field_names(struct Expr *expr,
 	return rc;
 }
 
+/**
+ * Create VDBE machine for ck constraint by given definition and
+ * expression AST. The generated instructions consist of
+ * prologue code that maps tuple fields via bindings and ck
+ * constraint code which implements given expression.
+ * In case of ck constraint error during VDBE execution, it is
+ * aborted and error is handled as diag message.
+ * @param ck_constraint_def Check constraint definition to prepare
+ *                          error description.
+ * @param expr Check constraint expression AST is built for
+ *             given @ck_constraint_def, see for
+ *             (sql_expr_compile +
+ *              ck_constraint_resolve_space_def) implementation.
+ * @param space_def The space definition of the space this check
+ *                  constraint is constructed for.
+ * @retval not NULL sql_stmt program pointer on success.
+ * @retval NULL otherwise.
+ */
+static struct sql_stmt *
+ck_constraint_program_compile(struct ck_constraint_def *ck_constraint_def,
+			      struct Expr *expr, struct space_def *space_def)
+{
+	struct sql *db = sql_get();
+	struct Parse parser;
+	sql_parser_create(&parser, db);
+	struct Vdbe *v = sqlGetVdbe(&parser);
+	if (v == NULL) {
+		diag_set(OutOfMemory, sizeof(struct Vdbe), "sqlGetVdbe",
+			 "vdbe");
+		return NULL;
+	}
+	/* Compile VDBE with default sql parameters. */
+	struct session *user_session = current_session();
+	uint32_t sql_flags = user_session->sql_flags;
+	user_session->sql_flags = default_flags;
+	/*
+	 * Generate a prologue code that introduces variables to
+	 * bind tuple fields there before execution.
+	 */
+	uint32_t field_count = space_def->field_count;
+	int bind_tuple_reg = sqlGetTempRange(&parser, field_count);
+	for (uint32_t i = 0; i < field_count; i++) {
+		sqlVdbeAddOp2(v, OP_Variable, ++parser.nVar,
+			      bind_tuple_reg + i);
+	}
+	/* Generate ck constraint test code. */
+	vdbe_emit_ck_constraint(&parser, expr, ck_constraint_def->name,
+				ck_constraint_def->expr_str, bind_tuple_reg);
+
+	/* Clean-up and restore user-defined sql context. */
+	bool is_error = parser.is_aborted;
+	sql_finish_coding(&parser);
+	sql_parser_destroy(&parser);
+	user_session->sql_flags = sql_flags;
+
+	if (is_error) {
+		diag_set(ClientError, ER_CREATE_CK_CONSTRAINT,
+			 ck_constraint_def->name,
+			 box_error_message(box_error_last()));
+		sql_finalize((struct sql_stmt *) v);
+		return NULL;
+	}
+	return (struct sql_stmt *) v;
+}
+
+/**
+ * Run bytecode implementing check constraint on new tuple
+ * before insert or replace in space space_def.
+ * @param ck_constraint Check constraint to run.
+ * @param space_def The space definition of the space this check
+ *                  constraint is constructed for.
+ * @param new_tuple The tuple to be inserted in space.
+ * @retval 0 if check constraint test is passed, -1 otherwise.
+ */
+static int
+ck_constraint_program_run(struct ck_constraint *ck_constraint,
+			  const char *new_tuple)
+{
+	/*
+	 * Prepare parameters for checks->stmt execution:
+	 * Map new tuple fields to Vdbe memory variables in range:
+	 * [1, field_count]
+	 */
+	struct space *space = space_by_id(ck_constraint->space_id);
+	assert(space != NULL);
+	/*
+	 * When last format fields are nullable, they are
+	 * 'optional' i.e. they may not be present in the tuple.
+	 */
+	uint32_t tuple_field_count = mp_decode_array(&new_tuple);
+	uint32_t field_count =
+		MIN(tuple_field_count, space->def->field_count);
+	for (uint32_t i = 0; i < field_count; i++) {
+		struct sql_bind bind;
+		if (sql_bind_decode(&bind, i + 1, &new_tuple) != 0 ||
+		    sql_bind_column(ck_constraint->stmt, &bind, i + 1) != 0) {
+			diag_set(ClientError, ER_CK_CONSTRAINT_FAILED,
+				 ck_constraint->def->name,
+				 ck_constraint->def->expr_str);
+			return -1;
+		}
+	}
+	/* Checks VDBE can't expire, reset expired flag and go. */
+	struct Vdbe *v = (struct Vdbe *) ck_constraint->stmt;
+	v->expired = 0;
+	while (sql_step(ck_constraint->stmt) == SQL_ROW) {}
+	/*
+	 * Get VDBE execution state and reset VM to run it
+	 * next time.
+	 */
+	return sql_reset(ck_constraint->stmt) != SQL_OK ? -1 : 0;
+}
+
+/**
+ * Ck constraint trigger function. It ss expected to be executed
+ * in space::on_replace trigger.
+ *
+ * It extracts all ck constraint required context from event and
+ * run bytecode implementing check constraint to test a new tuple
+ * before it will be inserted in destination space.
+ */
+static void
+ck_constraint_on_replace_trigger(struct trigger *trigger, void *event)
+{
+	struct ck_constraint *ck_constraint =
+		(struct ck_constraint *) trigger->data;
+	struct txn *txn = (struct txn *) event;
+	struct txn_stmt *stmt = txn_current_stmt(txn);
+	assert(stmt != NULL);
+	struct tuple *new_tuple = stmt->new_tuple;
+	if (new_tuple == NULL)
+		return;
+	if (ck_constraint_program_run(ck_constraint,
+				      tuple_data(new_tuple)) != 0)
+		diag_raise();
+}
+
 struct ck_constraint *
 ck_constraint_new(struct ck_constraint_def *ck_constraint_def,
 		  struct space_def *space_def)
@@ -70,23 +212,33 @@ ck_constraint_new(struct ck_constraint_def *ck_constraint_def,
 		return NULL;
 	}
 	ck_constraint->def = NULL;
+	ck_constraint->stmt = NULL;
 	ck_constraint->space_id = space_def->id;
 	rlist_create(&ck_constraint->link);
-	ck_constraint->expr =
+	trigger_create(&ck_constraint->trigger,
+		       ck_constraint_on_replace_trigger, ck_constraint,
+		       NULL);
+	struct Expr *expr =
 		sql_expr_compile(sql_get(), ck_constraint_def->expr_str,
 				 strlen(ck_constraint_def->expr_str));
-	if (ck_constraint->expr == NULL ||
-	    ck_constraint_resolve_field_names(ck_constraint->expr,
-					      space_def) != 0) {
+	if (expr == NULL ||
+	    ck_constraint_resolve_field_names(expr, space_def) != 0) {
 		diag_set(ClientError, ER_CREATE_CK_CONSTRAINT,
 			 ck_constraint_def->name,
 			 box_error_message(box_error_last()));
 		goto error;
 	}
+	ck_constraint->stmt =
+		ck_constraint_program_compile(ck_constraint_def, expr,
+					      space_def);
+	if (ck_constraint->stmt == NULL)
+		goto error;
 
+	sql_expr_delete(sql_get(), expr, false);
 	ck_constraint->def = ck_constraint_def;
 	return ck_constraint;
 error:
+	sql_expr_delete(sql_get(), expr, false);
 	ck_constraint_delete(ck_constraint);
 	return NULL;
 }
@@ -94,7 +246,8 @@ error:
 void
 ck_constraint_delete(struct ck_constraint *ck_constraint)
 {
-	sql_expr_delete(sql_get(), ck_constraint->expr, false);
+	assert(rlist_empty(&ck_constraint->trigger.link));
+	sql_finalize(ck_constraint->stmt);
 	free(ck_constraint->def);
 	TRASH(ck_constraint);
 	free(ck_constraint);
diff --git a/src/box/ck_constraint.h b/src/box/ck_constraint.h
index 615612605..abbfd3a8f 100644
--- a/src/box/ck_constraint.h
+++ b/src/box/ck_constraint.h
@@ -32,6 +32,7 @@
  */
 
 #include <stdint.h>
+#include "trigger.h"
 #include "small/rlist.h"
 
 #if defined(__cplusplus)
@@ -40,6 +41,7 @@ extern "C" {
 
 struct space;
 struct space_def;
+struct sql_stmt;
 struct Expr;
 
 /**
@@ -70,12 +72,16 @@ struct ck_constraint {
 	/** The check constraint definition. */
 	struct ck_constraint_def *def;
 	/**
-	 * The check constraint expression AST is built for
-	 * ck_constraint::def::expr_str with sql_expr_compile
-	 * and resolved with sql_resolve_self_reference for
-	 * space with space[ck_constraint::space_id] definition.
+	 * Precompiled reusable VDBE program for processing check
+	 * constraints and setting bad exitcode and error
+	 * message when ck condition unsatisfied.
 	 */
-	struct Expr *expr;
+	struct sql_stmt *stmt;
+	/**
+	 * Trigger object executing check constraint before
+	 * insert and replace operations.
+	 */
+	struct trigger trigger;
 	/**
 	 * The id of the space this check constraint is
 	 * built for.
diff --git a/src/box/errcode.h b/src/box/errcode.h
index 7878bd66b..750cd23a0 100644
--- a/src/box/errcode.h
+++ b/src/box/errcode.h
@@ -247,6 +247,7 @@ struct errcode_record {
 	/*192 */_(ER_INDEX_DEF_UNSUPPORTED,	"%s are prohibited in an index definition") \
 	/*193 */_(ER_CK_DEF_UNSUPPORTED,	"%s are prohibited in a check constraint definition") \
 	/*194 */_(ER_CREATE_CK_CONSTRAINT,	"Failed to create check constraint '%s': %s") \
+	/*195 */_(ER_CK_CONSTRAINT_FAILED,	"Check constraint failed '%s': %s") \
 /*
  * !IMPORTANT! Please follow instructions at start of the file
  * when adding new errors.
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index fa4a94ef7..c024447c2 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -813,51 +813,26 @@ sqlInsert(Parse * pParse,	/* Parser context */
 	sqlDbFree(db, aRegIdx);
 }
 
-/*
- * Meanings of bits in of pWalker->eCode for checkConstraintUnchanged()
- */
-#define CKCNSTRNT_COLUMN   0x01	/* CHECK constraint uses a changing column */
-
-/* This is the Walker callback from checkConstraintUnchanged().  Set
- * bit 0x01 of pWalker->eCode if
- * pWalker->eCode to 0 if this expression node references any of the
- * columns that are being modifed by an UPDATE statement.
- */
-static int
-checkConstraintExprNode(Walker * pWalker, Expr * pExpr)
-{
-	if (pExpr->op == TK_COLUMN) {
-		assert(pExpr->iColumn >= 0 || pExpr->iColumn == -1);
-		if (pExpr->iColumn >= 0) {
-			if (pWalker->u.aiCol[pExpr->iColumn] >= 0) {
-				pWalker->eCode |= CKCNSTRNT_COLUMN;
-			}
-		}
-	}
-	return WRC_Continue;
-}
-
-/*
- * pExpr is a CHECK constraint on a row that is being UPDATE-ed.  The
- * only columns that are modified by the UPDATE are those for which
- * aiChng[i]>=0.
- *
- * Return true if CHECK constraint pExpr does not use any of the
- * changing columns.  In other words, return true if this CHECK constraint
- * can be skipped when validating the new row in the UPDATE statement.
- */
-static int
-checkConstraintUnchanged(Expr * pExpr, int *aiChng)
+void
+vdbe_emit_ck_constraint(struct Parse *parser, struct Expr *expr,
+			const char *name, const char *expr_str,
+			int new_tuple_reg)
 {
-	Walker w;
-	memset(&w, 0, sizeof(w));
-	w.eCode = 0;
-	w.xExprCallback = checkConstraintExprNode;
-	w.u.aiCol = aiChng;
-	sqlWalkExpr(&w, pExpr);
-	testcase(w.eCode == 0);
-	testcase(w.eCode == CKCNSTRNT_COLUMN);
-	return !w.eCode;
+	parser->ckBase = new_tuple_reg;
+	struct Vdbe *v = sqlGetVdbe(parser);
+	const char *ck_constraint_name = sqlDbStrDup(parser->db, name);
+	VdbeNoopComment((v, "BEGIN: ck constraint %s test",
+			ck_constraint_name));
+	int check_is_passed = sqlVdbeMakeLabel(v);
+	sqlExprIfTrue(parser, expr, check_is_passed, SQL_JUMPIFNULL);
+	sqlMayAbort(parser);
+	const char *fmt = tnt_errcode_desc(ER_CK_CONSTRAINT_FAILED);
+	const char *error_msg = tt_sprintf(fmt, ck_constraint_name, expr_str);
+	sqlVdbeAddOp4(v, OP_Halt, SQL_TARANTOOL_ERROR, ON_CONFLICT_ACTION_ABORT,
+		      0, sqlDbStrDup(parser->db, error_msg), P4_DYNAMIC);
+	sqlVdbeChangeP5(v, ER_CK_CONSTRAINT_FAILED);
+	VdbeNoopComment((v, "END: ck constraint %s test", ck_constraint_name));
+	sqlVdbeResolveLabel(v, check_is_passed);
 }
 
 void
@@ -927,35 +902,6 @@ vdbe_emit_constraint_checks(struct Parse *parse_context, struct space *space,
 			unreachable();
 		}
 	}
-	/*
-	 * For CHECK constraint and for INSERT/UPDATE conflict
-	 * action DEFAULT and ABORT in fact has the same meaning.
-	 */
-	if (on_conflict == ON_CONFLICT_ACTION_DEFAULT)
-		on_conflict = ON_CONFLICT_ACTION_ABORT;
-	/* Test all CHECK constraints. */
-	enum on_conflict_action on_conflict_check = on_conflict;
-	if (on_conflict == ON_CONFLICT_ACTION_REPLACE)
-		on_conflict_check = ON_CONFLICT_ACTION_ABORT;
-	if (!rlist_empty(&space->ck_constraint))
-		parse_context->ckBase = new_tuple_reg;
-	struct ck_constraint *ck_constraint;
-	rlist_foreach_entry(ck_constraint, &space->ck_constraint, link) {
-		struct Expr *expr = ck_constraint->expr;
-		if (is_update && checkConstraintUnchanged(expr, upd_cols) != 0)
-			continue;
-		int all_ok = sqlVdbeMakeLabel(v);
-		sqlExprIfTrue(parse_context, expr, all_ok, SQL_JUMPIFNULL);
-		if (on_conflict == ON_CONFLICT_ACTION_IGNORE) {
-			sqlVdbeGoto(v, ignore_label);
-		} else {
-			char *name = ck_constraint->def->name;
-			sqlHaltConstraint(parse_context, SQL_CONSTRAINT_CHECK,
-					  on_conflict_check, name, P4_TRANSIENT,
-					  P5_ConstraintCheck);
-		}
-		sqlVdbeResolveLabel(v, all_ok);
-	}
 	sql_emit_table_types(v, space->def, new_tuple_reg);
 	/*
 	 * Other actions except for REPLACE and UPDATE OR IGNORE
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 2d6936490..64f5f3d59 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -605,6 +605,17 @@ sql_column_value(sql_stmt *,
 int
 sql_finalize(sql_stmt * pStmt);
 
+/*
+ * Terminate the current execution of an SQL statement and reset
+ * it back to its starting state so that it can be reused.
+ *
+ * @param stmt VDBE program, may be NULL.
+ * @retval SQL_OK On success.
+ * @retval sql_ret_code Error code on error.
+ */
+int
+sql_reset(struct sql_stmt *stmt);
+
 int
 sql_exec(sql *,	/* An open database */
 	     const char *sql,	/* SQL to be evaluated */
@@ -3895,6 +3906,21 @@ vdbe_emit_constraint_checks(struct Parse *parse_context,
 			    enum on_conflict_action on_conflict,
 			    int ignore_label, int *upd_cols);
 
+/**
+ * Gnerate code to make check constraints tests on tuple insertion
+ * on INSERT, REPLACE or UPDATE operations.
+ * @param parser Current parsing context.
+ * @param expr Check constraint AST.
+ * @param name Check constraint name to raise error.
+ * @param new_tuple_reg The first ck_constraint::stmt VDBE
+ *                      register of the range
+ *                      space_def::field_count representing a
+ *                      new tuple to be inserted.
+ */
+void
+vdbe_emit_ck_constraint(struct Parse *parser, struct Expr *expr,
+			const char *name, const char *expr_str,
+			int new_tuple_reg);
 /**
  * This routine generates code to finish the INSERT or UPDATE
  * operation that was started by a prior call to
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index d2868567b..5b3ac4b6a 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -132,14 +132,6 @@ sql_finalize(sql_stmt * pStmt)
 	return rc;
 }
 
-/*
- * Terminate the current execution of an SQL statement and reset it
- * back to its starting state so that it can be reused. A success code from
- * the prior execution is returned.
- *
- * This routine sets the error code and string returned by
- * sql_errcode(), sql_errmsg() and sql_errmsg16().
- */
 int
 sql_reset(sql_stmt * pStmt)
 {
diff --git a/test/box/misc.result b/test/box/misc.result
index eeb63366e..c1c7ffdc6 100644
--- a/test/box/misc.result
+++ b/test/box/misc.result
@@ -523,6 +523,7 @@ t;
   192: box.error.INDEX_DEF_UNSUPPORTED
   193: box.error.CK_DEF_UNSUPPORTED
   194: box.error.CREATE_CK_CONSTRAINT
+  195: box.error.CK_CONSTRAINT_FAILED
 ...
 test_run:cmd("setopt delimiter ''");
 ---
diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua
index 0dda3fac8..6bcc5830b 100755
--- a/test/sql-tap/check.test.lua
+++ b/test/sql-tap/check.test.lua
@@ -55,7 +55,7 @@ test:do_catchsql_test(
         INSERT INTO t1 VALUES(6,7, 2);
     ]], {
         -- <check-1.3>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1"
+        1, "Failed to execute SQL statement: Check constraint failed 'CK_CONSTRAINT_1_T1': x<5"
         -- </check-1.3>
     })
 
@@ -75,7 +75,7 @@ test:do_catchsql_test(
         INSERT INTO t1 VALUES(4,3, 2);
     ]], {
         -- <check-1.5>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_2_T1"
+        1, "Failed to execute SQL statement: Check constraint failed 'CK_CONSTRAINT_2_T1': y>x"
         -- </check-1.5>
     })
 
@@ -147,7 +147,7 @@ test:do_catchsql_test(
         UPDATE t1 SET x=7 WHERE x==2
     ]], {
         -- <check-1.12>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1"
+        1, "Check constraint failed 'CK_CONSTRAINT_1_T1': x<5"
         -- </check-1.12>
     })
 
@@ -167,7 +167,7 @@ test:do_catchsql_test(
         UPDATE t1 SET x=5 WHERE x==2
     ]], {
         -- <check-1.14>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1"
+        1, "Check constraint failed 'CK_CONSTRAINT_1_T1': x<5"
         -- </check-1.14>
     })
 
@@ -206,9 +206,9 @@ test:do_execsql_test(
     [[
         CREATE TABLE t2(
           id  INT primary key,
-          x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=='integer'),
+          x SCALAR CONSTRAINT one CHECK( typeof(coalesce(x,0))=='integer'),
           y REAL CONSTRAINT two CHECK( typeof(coalesce(y,0.1))=='number' ),
-          z TEXT CONSTRAINT three CHECK( typeof(coalesce(z,''))=='string' )
+          z SCALAR CONSTRAINT three CHECK( typeof(coalesce(z,''))=='string' )
         );
     ]], {
         -- <check-2.1>
@@ -246,7 +246,7 @@ test:do_catchsql_test(
         INSERT INTO t2 VALUES(3, 1.1, NULL, NULL);
     ]], {
         -- <check-2.4>
-        1, "Failed to execute SQL statement: CHECK constraint failed: ONE"
+        1, "Failed to execute SQL statement: Check constraint failed 'ONE': typeof(coalesce(x,0))=='integer'"
         -- </check-2.4>
     })
 
@@ -256,7 +256,7 @@ test:do_catchsql_test(
         INSERT INTO t2 VALUES(4, NULL, 5, NULL);
     ]], {
         -- <check-2.5>
-        1, "Failed to execute SQL statement: CHECK constraint failed: TWO"
+        1, "Failed to execute SQL statement: Check constraint failed 'TWO': typeof(coalesce(y,0.1))=='number'"
         -- </check-2.5>
     })
 
@@ -266,7 +266,7 @@ test:do_catchsql_test(
         INSERT INTO t2 VALUES(5, NULL, NULL, 3.14159);
     ]], {
         -- <check-2.6>
-        1, "Failed to execute SQL statement: CHECK constraint failed: THREE"
+        1, "Failed to execute SQL statement: Check constraint failed 'THREE': typeof(coalesce(z,''))=='string'"
         -- </check-2.6>
     })
 
@@ -413,7 +413,7 @@ test:do_catchsql_test(
         INSERT INTO t3 VALUES(111,222,333);
     ]], {
         -- <check-3.9>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T3"
+        1, "Failed to execute SQL statement: Check constraint failed 'CK_CONSTRAINT_1_T3': t3.x<25"
         -- </check-3.9>
     })
 
@@ -484,7 +484,7 @@ test:do_catchsql_test(
         UPDATE t4 SET x=0, y=1;
     ]], {
         -- <check-4.6>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T4"
+        1, "Check constraint failed 'CK_CONSTRAINT_1_T4': x+y==11 OR x*y==12 OR x/y BETWEEN 5 AND 8 OR -x==y+10"
         -- </check-4.6>
     })
 
@@ -504,7 +504,7 @@ test:do_catchsql_test(
         UPDATE t4 SET x=0, y=2;
     ]], {
         -- <check-4.9>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T4"
+        1, "Check constraint failed 'CK_CONSTRAINT_1_T4': x+y==11 OR x*y==12 OR x/y BETWEEN 5 AND 8 OR -x==y+10"
         -- </check-4.9>
     })
 
@@ -581,7 +581,7 @@ test:do_catchsql_test(
         UPDATE OR FAIL t1 SET x=7-x, y=y+1;
     ]], {
         -- <check-6.5>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1"
+        1, "Check constraint failed 'CK_CONSTRAINT_1_T1': x<5"
         -- </check-6.5>
     })
 
@@ -603,7 +603,7 @@ test:do_catchsql_test(
         INSERT OR ROLLBACK INTO t1 VALUES(8,40.0, 10);
     ]], {
         -- <check-6.7>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1"
+        1, "Failed to execute SQL statement: Check constraint failed 'CK_CONSTRAINT_1_T1': x<5"
         -- </check-6.7>
     })
 
@@ -636,7 +636,7 @@ test:do_catchsql_test(
         REPLACE INTO t1 VALUES(6,7, 11);
     ]], {
         -- <check-6.12>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1"
+        1, "Failed to execute SQL statement: Check constraint failed 'CK_CONSTRAINT_1_T1': x<5"
         -- </check-6.12>
     })
 
@@ -700,7 +700,7 @@ test:do_catchsql_test(
     7.3,
     " INSERT INTO t6 VALUES(11) ", {
         -- <7.3>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T6"
+        1, "Failed to execute SQL statement: Check constraint failed 'CK_CONSTRAINT_1_T6': myfunc(a)"
         -- </7.3>
     })
 
diff --git a/test/sql-tap/fkey2.test.lua b/test/sql-tap/fkey2.test.lua
index 695a379a6..def5f8321 100755
--- a/test/sql-tap/fkey2.test.lua
+++ b/test/sql-tap/fkey2.test.lua
@@ -362,7 +362,7 @@ test:do_catchsql_test(
         UPDATE ab SET a = 5;
     ]], {
         -- <fkey2-3.2>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_EF"
+        1, "Check constraint failed 'CK_CONSTRAINT_1_EF': e!=5"
         -- </fkey2-3.2>
     })
 
@@ -382,7 +382,7 @@ test:do_catchsql_test(
         UPDATE ab SET a = 5;
     ]], {
         -- <fkey2-3.4>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_EF"
+        1, "Check constraint failed 'CK_CONSTRAINT_1_EF': e!=5"
         -- </fkey2-3.4>
     })
 
diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua
index 066662f33..19df1d5df 100755
--- a/test/sql-tap/table.test.lua
+++ b/test/sql-tap/table.test.lua
@@ -1218,20 +1218,20 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "table-21.3",
     [[
-        INSERT INTO T21 VALUES(1, -1, 1);
+        INSERT INTO T21 VALUES(2, -1, 1);
     ]], {
         -- <table-21.3>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T21"
+        1, "Failed to execute SQL statement: Check constraint failed 'CK_CONSTRAINT_1_T21': B > 0"
         -- </table-21.3>
     })
 
 test:do_catchsql_test(
     "table-21.4",
     [[
-        INSERT INTO T21 VALUES(1, 1, -1);
+        INSERT INTO T21 VALUES(2, 1, -1);
     ]], {
         -- <table-21.4>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_2_T21"
+        1, "Failed to execute SQL statement: Check constraint failed 'CK_CONSTRAINT_2_T21': C > 0"
         -- </table-21.4>
     })
 
@@ -1372,7 +1372,7 @@ test:do_catchsql_test(
         INSERT INTO T28 VALUES(0);
     ]], {
         -- <table-22.10>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CHECK1"
+        1, "Failed to execute SQL statement: Check constraint failed 'CHECK1': id != 0"
         -- </table-22.10>
     })
 
@@ -1382,7 +1382,7 @@ test:do_catchsql_test(
         INSERT INTO T28 VALUES(9);
     ]], {
         -- <table-22.11>
-        1, "Failed to execute SQL statement: CHECK constraint failed: CHECK2"
+        1, "Failed to execute SQL statement: Check constraint failed 'CHECK2': id > 10"
         -- </table-22.11>
     })
 
diff --git a/test/sql/checks.result b/test/sql/checks.result
index 8da1483f6..bd7b96435 100644
--- a/test/sql/checks.result
+++ b/test/sql/checks.result
@@ -68,7 +68,12 @@ box.space._ck_constraint:count({})
 ...
 box.execute("INSERT INTO \"test\" VALUES(5);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_01'
+- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_01'':
+    X<5'
+...
+box.space.test:insert({5})
+---
+- error: 'Check constraint failed ''CK_CONSTRAINT_01'': X<5'
 ...
 box.space._ck_constraint:replace({'CK_CONSTRAINT_01', 513, false, 'X<=5'})
 ---
@@ -80,7 +85,12 @@ box.execute("INSERT INTO \"test\" VALUES(5);")
 ...
 box.execute("INSERT INTO \"test\" VALUES(6);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_01'
+- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_01'':
+    X<=5'
+...
+box.space.test:insert({6})
+---
+- error: 'Check constraint failed ''CK_CONSTRAINT_01'': X<=5'
 ...
 -- Can't drop table with check constraints.
 box.space.test:delete({5})
@@ -113,16 +123,28 @@ box.space._ck_constraint:count()
 ...
 box.execute("INSERT INTO t1 VALUES (7, 1, 1)")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: ONE'
+- error: 'Failed to execute SQL statement: Check constraint failed ''ONE'': x<5'
+...
+box.space.T1:insert({7, 1, 1})
+---
+- error: 'Check constraint failed ''ONE'': x<5'
 ...
 box.execute("INSERT INTO t1 VALUES (2, 1, 1)")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: TWO'
+- error: 'Failed to execute SQL statement: Check constraint failed ''TWO'': y>x'
+...
+box.space.T1:insert({2, 1, 1})
+---
+- error: 'Check constraint failed ''TWO'': y>x'
 ...
 box.execute("INSERT INTO t1 VALUES (2, 4, 1)")
 ---
 - row_count: 1
 ...
+box.space.T1:update({1}, {{'+', 1, 5}})
+---
+- error: 'Check constraint failed ''ONE'': x<5'
+...
 box.execute("DROP TABLE t1")
 ---
 - row_count: 1
@@ -159,14 +181,14 @@ _ = box.space._ck_constraint:insert({'physics', s.id, false, 'X<Y'})
 ...
 box.execute("INSERT INTO \"test\" VALUES(2, 1);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: physics'
+- error: 'Failed to execute SQL statement: Check constraint failed ''physics'': X<Y'
 ...
 s:format({{name='Y', type='integer'}, {name='X', type='integer'}})
 ---
 ...
 box.execute("INSERT INTO \"test\" VALUES(1, 2);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: physics'
+- error: 'Failed to execute SQL statement: Check constraint failed ''physics'': X<Y'
 ...
 box.execute("INSERT INTO \"test\" VALUES(2, 1);")
 ---
@@ -177,7 +199,7 @@ s:truncate()
 ...
 box.execute("INSERT INTO \"test\" VALUES(1, 2);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: physics'
+- error: 'Failed to execute SQL statement: Check constraint failed ''physics'': X<Y'
 ...
 s:format({})
 ---
@@ -209,11 +231,11 @@ _ = box.space._ck_constraint:insert({'conflict', s.id, false, 'X>10'})
 ...
 box.execute("INSERT INTO \"test\" VALUES(1, 2);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: conflict'
+- error: 'Failed to execute SQL statement: Check constraint failed ''conflict'': X>10'
 ...
 box.execute("INSERT INTO \"test\" VALUES(11, 11);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: physics'
+- error: 'Failed to execute SQL statement: Check constraint failed ''physics'': X<Y'
 ...
 box.execute("INSERT INTO \"test\" VALUES(12, 11);")
 ---
@@ -250,6 +272,183 @@ box.execute("CREATE TABLE t5(x INT PRIMARY KEY, y INT, CHECK( x*y < ? ));")
 - error: 'Failed to create check constraint ''CK_CONSTRAINT_1_T5'': bindings are not
     allowed in DDL'
 ...
+--
+-- Test ck constraint corner cases
+--
+s = box.schema.create_space('test', {engine = engine})
+---
+...
+_ = s:create_index('pk')
+---
+...
+s:format({{name='X', type='any'}, {name='Y', type='integer'}, {name='Z', type='integer', is_nullable=true}})
+---
+...
+ck_not_null = box.space._ck_constraint:insert({'ZnotNULL', s.id, false, 'Z IS NOT NULL'})
+---
+...
+s:insert({1, 2, box.NULL})
+---
+- error: 'Check constraint failed ''ZnotNULL'': Z IS NOT NULL'
+...
+s:insert({1, 2})
+---
+- error: 'Check constraint failed ''ZnotNULL'': Z IS NOT NULL'
+...
+_ = box.space._ck_constraint:delete({'ZnotNULL', s.id})
+---
+...
+_ = box.space._ck_constraint:insert({'XlessY', s.id, false, 'X < Y and Y < Z'})
+---
+...
+s:insert({'1', 2})
+---
+- error: 'Tuple field 1 type does not match one required by operation: expected unsigned'
+...
+s:insert({})
+---
+- error: Tuple field 1 required by space format is missing
+...
+s:insert({2, 1})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:insert({1, 2})
+---
+- [1, 2]
+...
+s:insert({2, 3, 1})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:insert({2, 3, 4})
+---
+- [2, 3, 4]
+...
+s:update({2}, {{'+', 2, 3}})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:update({2}, {{'+', 2, 3}, {'+', 3, 3}})
+---
+- [2, 6, 7]
+...
+s:replace({2, 1, 3})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+box.snapshot()
+---
+- ok
+...
+s = box.space["test"]
+---
+...
+s:update({2}, {{'+', 2, 3}})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:update({2}, {{'+', 2, 3}, {'+', 3, 3}})
+---
+- [2, 9, 10]
+...
+s:replace({2, 1, 3})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:drop()
+---
+...
+--
+-- Test complex CHECK constraints.
+--
+s = box.schema.create_space('test', {engine = engine})
+---
+...
+s:format({{name='X', type='integer'}, {name='Y', type='integer'}, {name='Z', type='integer'}})
+---
+...
+_ = s:create_index('pk', {parts = {3, 'integer'}})
+---
+...
+_ = s:create_index('unique', {parts = {1, 'integer'}})
+---
+...
+_ = box.space._ck_constraint:insert({'complex1', s.id, false, 'x+y==11 OR x*y==12 OR x/y BETWEEN 5 AND 8 OR -x == y+10'})
+---
+...
+s:insert({1, 10, 1})
+---
+- [1, 10, 1]
+...
+s:update({1}, {{'=', 1, 4}, {'=', 2, 3}})
+---
+- [4, 3, 1]
+...
+s:update({1}, {{'=', 1, 12}, {'=', 2, 2}})
+---
+- [12, 2, 1]
+...
+s:update({1}, {{'=', 1, 12}, {'=', 2, -22}})
+---
+- [12, -22, 1]
+...
+s:update({1}, {{'=', 1, 0}, {'=', 2, 1}})
+---
+- error: 'Check constraint failed ''complex1'': x+y==11 OR x*y==12 OR x/y BETWEEN
+    5 AND 8 OR -x == y+10'
+...
+s:get({1})
+---
+- [12, -22, 1]
+...
+s:update({1}, {{'=', 1, 0}, {'=', 2, 2}})
+---
+- error: 'Check constraint failed ''complex1'': x+y==11 OR x*y==12 OR x/y BETWEEN
+    5 AND 8 OR -x == y+10'
+...
+s:get({1})
+---
+- [12, -22, 1]
+...
+s:drop()
+---
+...
+s = box.schema.create_space('test', {engine = engine})
+---
+...
+s:format({{name='X', type='integer'}, {name='Z', type='any'}})
+---
+...
+_ = s:create_index('pk', {parts = {1, 'integer'}})
+---
+...
+_ = box.space._ck_constraint:insert({'complex2', s.id, false, 'typeof(coalesce(z,0))==\'integer\''})
+---
+...
+s:insert({1, 'string'})
+---
+- error: 'Check constraint failed ''complex2'': typeof(coalesce(z,0))==''integer'''
+...
+s:insert({1, {map=true}})
+---
+- error: 'Check constraint failed ''complex2'': typeof(coalesce(z,0))==''integer'''
+...
+s:insert({1, {'a', 'r','r','a','y'}})
+---
+- error: 'Check constraint failed ''complex2'': typeof(coalesce(z,0))==''integer'''
+...
+s:insert({1, 3.14})
+---
+- error: 'Check constraint failed ''complex2'': typeof(coalesce(z,0))==''integer'''
+...
+s:insert({1, 666})
+---
+- [1, 666]
+...
+s:drop()
+---
+...
 test_run:cmd("clear filter")
 ---
 - true
diff --git a/test/sql/checks.test.lua b/test/sql/checks.test.lua
index 4029359b1..2a7510aa9 100644
--- a/test/sql/checks.test.lua
+++ b/test/sql/checks.test.lua
@@ -31,9 +31,11 @@ box.space._ck_constraint:insert({'CK_CONSTRAINT_01', 513, false, 'X<5'})
 box.space._ck_constraint:count({})
 
 box.execute("INSERT INTO \"test\" VALUES(5);")
+box.space.test:insert({5})
 box.space._ck_constraint:replace({'CK_CONSTRAINT_01', 513, false, 'X<=5'})
 box.execute("INSERT INTO \"test\" VALUES(5);")
 box.execute("INSERT INTO \"test\" VALUES(6);")
+box.space.test:insert({6})
 -- Can't drop table with check constraints.
 box.space.test:delete({5})
 box.space.test.index.pk:drop()
@@ -45,8 +47,11 @@ box.space._space:delete({513})
 box.execute("CREATE TABLE t1(x INTEGER CONSTRAINT ONE CHECK( x<5 ), y REAL CONSTRAINT TWO CHECK( y>x ), z INTEGER PRIMARY KEY);")
 box.space._ck_constraint:count()
 box.execute("INSERT INTO t1 VALUES (7, 1, 1)")
+box.space.T1:insert({7, 1, 1})
 box.execute("INSERT INTO t1 VALUES (2, 1, 1)")
+box.space.T1:insert({2, 1, 1})
 box.execute("INSERT INTO t1 VALUES (2, 4, 1)")
+box.space.T1:update({1}, {{'+', 1, 5}})
 box.execute("DROP TABLE t1")
 
 -- Test space creation rollback on spell error in ck constraint.
@@ -93,4 +98,60 @@ box.execute("DROP TABLE w2;")
 --
 box.execute("CREATE TABLE t5(x INT PRIMARY KEY, y INT, CHECK( x*y < ? ));")
 
+--
+-- Test ck constraint corner cases
+--
+s = box.schema.create_space('test', {engine = engine})
+_ = s:create_index('pk')
+s:format({{name='X', type='any'}, {name='Y', type='integer'}, {name='Z', type='integer', is_nullable=true}})
+ck_not_null = box.space._ck_constraint:insert({'ZnotNULL', s.id, false, 'Z IS NOT NULL'})
+s:insert({1, 2, box.NULL})
+s:insert({1, 2})
+_ = box.space._ck_constraint:delete({'ZnotNULL', s.id})
+_ = box.space._ck_constraint:insert({'XlessY', s.id, false, 'X < Y and Y < Z'})
+s:insert({'1', 2})
+s:insert({})
+s:insert({2, 1})
+s:insert({1, 2})
+s:insert({2, 3, 1})
+s:insert({2, 3, 4})
+s:update({2}, {{'+', 2, 3}})
+s:update({2}, {{'+', 2, 3}, {'+', 3, 3}})
+s:replace({2, 1, 3})
+box.snapshot()
+s = box.space["test"]
+s:update({2}, {{'+', 2, 3}})
+s:update({2}, {{'+', 2, 3}, {'+', 3, 3}})
+s:replace({2, 1, 3})
+s:drop()
+
+--
+-- Test complex CHECK constraints.
+--
+s = box.schema.create_space('test', {engine = engine})
+s:format({{name='X', type='integer'}, {name='Y', type='integer'}, {name='Z', type='integer'}})
+_ = s:create_index('pk', {parts = {3, 'integer'}})
+_ = s:create_index('unique', {parts = {1, 'integer'}})
+_ = box.space._ck_constraint:insert({'complex1', s.id, false, 'x+y==11 OR x*y==12 OR x/y BETWEEN 5 AND 8 OR -x == y+10'})
+s:insert({1, 10, 1})
+s:update({1}, {{'=', 1, 4}, {'=', 2, 3}})
+s:update({1}, {{'=', 1, 12}, {'=', 2, 2}})
+s:update({1}, {{'=', 1, 12}, {'=', 2, -22}})
+s:update({1}, {{'=', 1, 0}, {'=', 2, 1}})
+s:get({1})
+s:update({1}, {{'=', 1, 0}, {'=', 2, 2}})
+s:get({1})
+s:drop()
+
+s = box.schema.create_space('test', {engine = engine})
+s:format({{name='X', type='integer'}, {name='Z', type='any'}})
+_ = s:create_index('pk', {parts = {1, 'integer'}})
+_ = box.space._ck_constraint:insert({'complex2', s.id, false, 'typeof(coalesce(z,0))==\'integer\''})
+s:insert({1, 'string'})
+s:insert({1, {map=true}})
+s:insert({1, {'a', 'r','r','a','y'}})
+s:insert({1, 3.14})
+s:insert({1, 666})
+s:drop()
+
 test_run:cmd("clear filter")
diff --git a/test/sql/errinj.result b/test/sql/errinj.result
index 18d1f3882..01c3e2856 100644
--- a/test/sql/errinj.result
+++ b/test/sql/errinj.result
@@ -490,7 +490,8 @@ _ = box.space._ck_constraint:insert({'CK_CONSTRAINT_01', s.id, false, 'X<5'})
 ...
 box.execute("INSERT INTO \"test\" VALUES(5);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_01'
+- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_01'':
+    X<5'
 ...
 errinj.set("ERRINJ_WAL_IO", true)
 ---
@@ -521,7 +522,8 @@ _ = box.space._ck_constraint:delete({'CK_CONSTRAINT_01', s.id})
 ...
 box.execute("INSERT INTO \"test\" VALUES(6);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_01'
+- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_01'':
+    X<=5'
 ...
 errinj.set("ERRINJ_WAL_IO", false)
 ---
@@ -557,11 +559,13 @@ _ = box.space._ck_constraint:insert({'Xgreater10', s.id, false, 'X > 10'})
 ...
 box.execute("INSERT INTO \"test\" VALUES(2, 1);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: Xgreater10'
+- error: 'Failed to execute SQL statement: Check constraint failed ''Xgreater10'':
+    X > 10'
 ...
 box.execute("INSERT INTO \"test\" VALUES(20, 10);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: XlessY'
+- error: 'Failed to execute SQL statement: Check constraint failed ''XlessY'': X <
+    Y'
 ...
 box.execute("INSERT INTO \"test\" VALUES(20, 100);")
 ---
@@ -584,11 +588,13 @@ errinj.set("ERRINJ_WAL_IO", false)
 ...
 box.execute("INSERT INTO \"test\" VALUES(2, 1);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: XlessY'
+- error: 'Failed to execute SQL statement: Check constraint failed ''XlessY'': X <
+    Y'
 ...
 box.execute("INSERT INTO \"test\" VALUES(20, 10);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: XlessY'
+- error: 'Failed to execute SQL statement: Check constraint failed ''XlessY'': X <
+    Y'
 ...
 box.execute("INSERT INTO \"test\" VALUES(20, 100);")
 ---
diff --git a/test/sql/gh-2981-check-autoinc.result b/test/sql/gh-2981-check-autoinc.result
index 7384c81e8..e57789897 100644
--- a/test/sql/gh-2981-check-autoinc.result
+++ b/test/sql/gh-2981-check-autoinc.result
@@ -29,7 +29,8 @@ box.execute("insert into t1 values (18, null);")
 ...
 box.execute("insert into t1(s2) values (null);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1'
+- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T1'':
+    s1 <> 19'
 ...
 box.execute("insert into t2 values (18, null);")
 ---
@@ -37,7 +38,8 @@ box.execute("insert into t2 values (18, null);")
 ...
 box.execute("insert into t2(s2) values (null);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T2'
+- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T2'':
+    s1 <> 19 AND s1 <> 25'
 ...
 box.execute("insert into t2 values (24, null);")
 ---
@@ -45,7 +47,8 @@ box.execute("insert into t2 values (24, null);")
 ...
 box.execute("insert into t2(s2) values (null);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T2'
+- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T2'':
+    s1 <> 19 AND s1 <> 25'
 ...
 box.execute("insert into t3 values (9, null)")
 ---
@@ -53,7 +56,8 @@ box.execute("insert into t3 values (9, null)")
 ...
 box.execute("insert into t3(s2) values (null)")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T3'
+- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T3'':
+    s1 < 10'
 ...
 box.execute("DROP TABLE t1")
 ---
diff --git a/test/sql/types.result b/test/sql/types.result
index 582785413..ccbdd8c50 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -709,7 +709,8 @@ box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN CHECK (a = true));")
 ...
 box.execute("INSERT INTO t1 VALUES (1, false);")
 ---
-- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1'
+- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T1'':
+    a = true'
 ...
 box.execute("INSERT INTO t1 VALUES (1, true);")
 ---
-- 
2.21.0