[tarantool-patches] [PATCH v5 5/6] box: run check constraint tests on space alter
Kirill Shcherbatov
kshcherbatov at tarantool.org
Thu May 23 13:19:38 MSK 2019
To perform ck constraints tests before insert or update space
operation, we use precompiled VDBE machine associated with
each ck constraint, that is executed in on_replace trigger.
Each ck constraint VDBE code consists of
1) prologue code that maps new(or updated) tuple via binding,
2) ck constraint code generated by CK constraint AST.
In case of ck constraint error the tuple insert/replace operation
is aborted and ck constraint error is handled as diag message.
Needed for #3691
---
src/box/alter.cc | 64 ++++-
src/box/ck_constraint.c | 131 ++++++++++-
src/box/ck_constraint.h | 29 ++-
src/box/errcode.h | 1 +
src/box/space.c | 4 +
src/box/space.h | 3 +
src/box/sql/expr.c | 25 +-
src/box/sql/insert.c | 92 ++------
src/box/sql/sqlInt.h | 36 ++-
src/box/sql/vdbe.h | 1 -
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 | 324 +++++++++++++++++++++++++-
test/sql/checks.test.lua | 96 ++++++++
test/sql/errinj.result | 18 +-
test/sql/gh-2981-check-autoinc.result | 12 +-
test/sql/types.result | 3 +-
20 files changed, 744 insertions(+), 152 deletions(-)
diff --git a/src/box/alter.cc b/src/box/alter.cc
index 463357c67..e4ded1995 100644
--- a/src/box/alter.cc
+++ b/src/box/alter.cc
@@ -1421,6 +1421,12 @@ RebuildCkConstraints::space_swap_ck_constraint(struct space *old_space,
{
rlist_swap(&new_space->ck_constraint, &ck_constraint);
rlist_swap(&ck_constraint, &old_space->ck_constraint);
+
+ trigger_clear(&old_space->ck_constraint_trigger);
+ if (!rlist_empty(&new_space->ck_constraint)) {
+ trigger_add(&old_space->on_replace,
+ &new_space->ck_constraint_trigger);
+ }
}
void
@@ -1450,6 +1456,48 @@ RebuildCkConstraints::~RebuildCkConstraints()
}
}
+/**
+ * Move CK constraints from old space to the new one.
+ * Unlike RebuildCkConstraints, this operation doesn't perform
+ * ck constraints rebuild. This may be used in scenarios where
+ * space format doesn't change i.e. on index alter or space trim.
+ */
+class MoveCkConstraints: public AlterSpaceOp
+{
+ void space_swap_ck_constraint(struct space *old_space,
+ struct space *new_space);
+public:
+ MoveCkConstraints(struct alter_space *alter) : AlterSpaceOp(alter) {}
+ virtual void alter(struct alter_space *alter);
+ virtual void rollback(struct alter_space *alter);
+};
+
+void
+MoveCkConstraints::space_swap_ck_constraint(struct space *old_space,
+ struct space *new_space)
+{
+ rlist_swap(&new_space->ck_constraint,
+ &old_space->ck_constraint);
+
+ trigger_clear(&old_space->ck_constraint_trigger);
+ if (!rlist_empty(&new_space->ck_constraint)) {
+ trigger_add(&old_space->on_replace,
+ &new_space->ck_constraint_trigger);
+ }
+}
+
+void
+MoveCkConstraints::alter(struct alter_space *alter)
+{
+ space_swap_ck_constraint(alter->old_space, alter->new_space);
+}
+
+void
+MoveCkConstraints::rollback(struct alter_space *alter)
+{
+ space_swap_ck_constraint(alter->new_space, alter->old_space);
+}
+
/* }}} */
/**
@@ -2160,7 +2208,7 @@ 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 RebuildCkConstraints(alter);
+ (void) new MoveCkConstraints(alter);
/* Add an op to update schema_version on commit. */
(void) new UpdateSchemaVersion(alter);
alter_space_do(txn, alter);
@@ -2229,7 +2277,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;
}
@@ -4173,6 +4221,10 @@ 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);
+ if (rlist_empty(&space->ck_constraint_trigger.link)) {
+ trigger_add(&space->on_replace,
+ &space->ck_constraint_trigger);
+ }
} else if (stmt->new_tuple != NULL && stmt->old_tuple == NULL) {
/* Rollback INSERT check constraint. */
assert(space != NULL);
@@ -4180,6 +4232,8 @@ on_replace_ck_constraint_rollback(struct trigger *trigger, void *event)
ck->def->name, strlen(ck->def->name)) != NULL);
rlist_del_entry(ck, link);
ck_constraint_delete(ck);
+ if (rlist_empty(&space->ck_constraint))
+ trigger_clear(&space->ck_constraint_trigger);
} else {
/* Rollback REPLACE check constraint. */
assert(space != NULL);
@@ -4258,6 +4312,10 @@ on_replace_dd_ck_constraint(struct trigger * /* trigger*/, void *event)
if (old_ck_constraint != NULL)
rlist_del_entry(old_ck_constraint, link);
rlist_add_entry(&space->ck_constraint, new_ck_constraint, link);
+ if (rlist_empty(&space->ck_constraint_trigger.link)) {
+ trigger_add(&space->on_replace,
+ &space->ck_constraint_trigger);
+ }
on_commit->data = old_tuple == NULL ? new_ck_constraint :
old_ck_constraint;
on_rollback->data = on_commit->data;
@@ -4273,6 +4331,8 @@ 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);
+ if (rlist_empty(&space->ck_constraint))
+ trigger_clear(&space->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 c74687b31..ba2df4b67 100644
--- a/src/box/ck_constraint.c
+++ b/src/box/ck_constraint.c
@@ -29,10 +29,15 @@
* SUCH DAMAGE.
*/
#include "box/session.h"
+#include "bind.h"
#include "ck_constraint.h"
#include "errcode.h"
+#include "schema.h"
+#include "small/region.h"
#include "sql.h"
#include "sql/sqlInt.h"
+#include "sql/vdbeInt.h"
+#include "tuple.h"
const char *ck_constraint_language_strs[] = {"SQL"};
@@ -57,6 +62,116 @@ ck_constraint_resolve_field_names(struct Expr *expr,
return rc;
}
+/**
+ * Create a VDBE machine for the ck constraint by a given
+ * definition and an expression AST. The generated instructions
+ * consist of prologue code that maps tuple_fetcher via binding
+ * and ck constraint code that implements a given expression.
+ * @param ck_constraint_def Check constraint definition to prepare
+ * an error description.
+ * @param expr Ck constraint expression AST built for a given
+ * @a ck_constraint_def, see for (sql_expr_compile and
+ * 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 sql *db = sql_get();
+ struct Parse parser;
+ sql_parser_create(&parser, db, default_flags);
+ struct Vdbe *v = sqlGetVdbe(&parser);
+ if (v == NULL) {
+ diag_set(OutOfMemory, sizeof(struct Vdbe), "sqlGetVdbe",
+ "vdbe");
+ return NULL;
+ }
+ /*
+ * Generate a prologue code that introduces variables to
+ * bind tuple_fetcher before execution.
+ */
+ int tuple_fetcher_reg = sqlGetTempReg(&parser);
+ sqlVdbeAddOp2(v, OP_Variable, ++parser.nVar, tuple_fetcher_reg);
+ /* Generate ck constraint test code. */
+ vdbe_emit_ck_constraint(&parser, expr, ck_constraint_def->name,
+ ck_constraint_def->expr_str, tuple_fetcher_reg);
+
+ /* Clean-up and restore user-defined sql context. */
+ bool is_error = parser.is_aborted;
+ sql_finish_coding(&parser);
+ sql_parser_destroy(&parser);
+
+ 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 with given
+ * tuple_fetcher instance.
+ * @param ck_constraint Ck constraint object to run.
+ * @param fetcher The initialized tuple_fetcher instance.
+ * @retval 0 On success, when check constraint test is passed.
+ * @retval -1 Otherwise. The diag message is set.
+ */
+static int
+ck_constraint_program_run(struct ck_constraint *ck_constraint,
+ struct tuple_fetcher *fetcher)
+{
+ if (sql_bind_ptr(ck_constraint->stmt, 1, fetcher) != 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;
+ sql_step(ck_constraint->stmt);
+ /*
+ * Get VDBE execution state and reset VM to run it
+ * next time.
+ */
+ return sql_reset(ck_constraint->stmt) != SQL_OK ? -1 : 0;
+}
+
+void
+ck_constraint_on_replace_trigger(struct trigger *trigger, void *event)
+{
+ 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;
+
+ struct space *space = (struct space *) trigger->data;
+ uint32_t fetcher_sz = sizeof(struct tuple_fetcher) +
+ sizeof(uint32_t) * space->def->field_count;
+ struct tuple_fetcher *fetcher = region_alloc(&fiber()->gc, fetcher_sz);
+ if (fetcher == NULL) {
+ diag_set(OutOfMemory, fetcher_sz, "region_alloc", "fetcher");
+ diag_raise();
+ }
+ tuple_fetcher_create(fetcher, new_tuple, tuple_data(new_tuple),
+ new_tuple->bsize);
+
+ struct ck_constraint *ck_constraint;
+ rlist_foreach_entry(ck_constraint, &space->ck_constraint, link) {
+ if (ck_constraint_program_run(ck_constraint, fetcher) != 0)
+ diag_raise();
+ }
+}
+
struct ck_constraint *
ck_constraint_new(struct ck_constraint_def *ck_constraint_def,
struct space_def *space_def)
@@ -73,23 +188,29 @@ 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 =
+ 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);
+ 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;
}
@@ -97,7 +218,7 @@ error:
void
ck_constraint_delete(struct ck_constraint *ck_constraint)
{
- sql_expr_delete(sql_get(), ck_constraint->expr, false);
+ 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 e20203bb5..f76e2d5f4 100644
--- a/src/box/ck_constraint.h
+++ b/src/box/ck_constraint.h
@@ -32,6 +32,8 @@
*/
#include <stdint.h>
+#include "trigger.h"
+#include "sql.h"
#include "small/rlist.h"
#if defined(__cplusplus)
@@ -40,6 +42,7 @@ extern "C" {
struct space;
struct space_def;
+struct sql_stmt;
struct Expr;
/** Supported languages of ck constraint. */
@@ -81,12 +84,11 @@ 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;
/**
* The id of the space this check constraint is
* built for.
@@ -157,6 +159,23 @@ ck_constraint_new(struct ck_constraint_def *ck_constraint_def,
void
ck_constraint_delete(struct ck_constraint *ck_constraint);
+/**
+ * Ck constraint trigger function. It is expected to be executed
+ * in space::on_replace trigger.
+ *
+ * It performs all ck constraints defined for a given space
+ * running the precompiled bytecode to test a new tuple
+ * before it will be inserted in destination space.
+ * The trigger data stores space identifier instead of space
+ * pointer to make ck constraint independent of specific space
+ * object version.
+ *
+ * Raises an exception when some ck constraint is unsatisfied.
+ * The diag message is set.
+ */
+void
+ck_constraint_on_replace_trigger(struct trigger *trigger, void *event);
+
/**
* Find check constraint object in space by given name and
* name_len.
diff --git a/src/box/errcode.h b/src/box/errcode.h
index 1f7c81693..e2ec240d2 100644
--- a/src/box/errcode.h
+++ b/src/box/errcode.h
@@ -248,6 +248,7 @@ struct errcode_record {
/*193 */_(ER_CK_DEF_UNSUPPORTED, "%s are prohibited in a ck constraint definition") \
/*194 */_(ER_MULTIKEY_INDEX_MISMATCH, "Field %s is used as multikey in one index and as single key in another") \
/*195 */_(ER_CREATE_CK_CONSTRAINT, "Failed to create check constraint '%s': %s") \
+ /*196 */_(ER_CK_CONSTRAINT_FAILED, "Check constraint failed '%s': %s") \
/*
* !IMPORTANT! Please follow instructions at start of the file
diff --git a/src/box/space.c b/src/box/space.c
index a42b3a64b..abf2b3d6b 100644
--- a/src/box/space.c
+++ b/src/box/space.c
@@ -43,6 +43,7 @@
#include "xrow.h"
#include "iproto_constants.h"
#include "schema.h"
+#include "ck_constraint.h"
int
access_check_space(struct space *space, user_access_t access)
@@ -166,6 +167,8 @@ space_create(struct space *space, struct engine *engine,
rlist_create(&space->parent_fk_constraint);
rlist_create(&space->child_fk_constraint);
rlist_create(&space->ck_constraint);
+ trigger_create(&space->ck_constraint_trigger,
+ ck_constraint_on_replace_trigger, space, NULL);
return 0;
fail_free_indexes:
@@ -207,6 +210,7 @@ space_new_ephemeral(struct space_def *def, struct rlist *key_list)
void
space_delete(struct space *space)
{
+ assert(rlist_empty(&space->ck_constraint_trigger.link));
for (uint32_t j = 0; j <= space->index_id_max; j++) {
struct index *index = space->index_map[j];
if (index != NULL)
diff --git a/src/box/space.h b/src/box/space.h
index 823f291ce..e1246953b 100644
--- a/src/box/space.h
+++ b/src/box/space.h
@@ -37,6 +37,7 @@
#include "index.h"
#include "error.h"
#include "diag.h"
+#include "trigger.h"
#if defined(__cplusplus)
extern "C" {
@@ -203,6 +204,8 @@ struct space {
* ck_constraint::link.
*/
struct rlist ck_constraint;
+ /** Trigger that performs ck constraint validation. */
+ struct trigger ck_constraint_trigger;
/**
* Lists of foreign key constraints. In SQL terms child
* space is the "from" table i.e. the table that contains
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 017b36fb4..7f13098ed 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -3730,16 +3730,23 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
case TK_COLUMN:{
int iTab = pExpr->iTable;
int col = pExpr->iColumn;
+ if (pParse->tuple_fetcher_reg > 0) {
+ /* Generating CHECK constraints. */
+ assert(iTab < 0);
+ sqlVdbeAddOp3(v, OP_Fetch,
+ pParse->tuple_fetcher_reg,
+ col, target);
+ return target;
+ }
if (iTab < 0) {
- if (pParse->ckBase > 0) {
- /* Generating CHECK constraints. */
- return col + pParse->ckBase;
- } else {
- /* Coding an expression that is part of an index where column names
- * in the index refer to the table to which the index belongs
- */
- iTab = pParse->iSelfTab;
- }
+ /*
+ * Coding an expression that is
+ * a part of an index where column
+ * names in the index refer to
+ * the table to which the
+ * index belongs.
+ */
+ iTab = pParse->iSelfTab;
}
return sqlExprCodeGetColumn(pParse,
pExpr->space_def, col,
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index b28fe5760..a390ed541 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -812,51 +812,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 tuple_fetcher_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->tuple_fetcher_reg = tuple_fetcher_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
@@ -926,35 +901,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 abb7b9c2a..d602116da 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 */
@@ -639,7 +650,6 @@ sql_exec(sql *, /* An open database */
#define SQL_IOERR_GETTEMPPATH (SQL_IOERR | (25<<8))
#define SQL_IOERR_CONVPATH (SQL_IOERR | (26<<8))
#define SQL_IOERR_VNODE (SQL_IOERR | (27<<8))
-#define SQL_CONSTRAINT_CHECK (SQL_CONSTRAINT | (1<<8))
#define SQL_CONSTRAINT_FOREIGNKEY (SQL_CONSTRAINT | (3<<8))
#define SQL_CONSTRAINT_FUNCTION (SQL_CONSTRAINT | (4<<8))
#define SQL_CONSTRAINT_NOTNULL (SQL_CONSTRAINT | (5<<8))
@@ -944,7 +954,6 @@ sql_init_db(sql **db);
int
sql_close(sql *);
-
/**
* Get number of the named parameter in the prepared sql
* statement.
@@ -2610,7 +2619,11 @@ struct Parse {
int nMem; /* Number of memory cells used so far */
int nOpAlloc; /* Number of slots allocated for Vdbe.aOp[] */
int szOpAlloc; /* Bytes of memory space allocated for Vdbe.aOp[] */
- int ckBase; /* Base register of data during check constraints */
+ /*
+ * The register with tuple_fetcher to generate an
+ * alternative Vdbe code (during check constraints).
+ */
+ int tuple_fetcher_reg;
int iSelfTab; /* Table of an index whose exprs are being coded */
int iCacheLevel; /* ColCache valid when aColCache[].iLevel<=iCacheLevel */
int iCacheCnt; /* Counter used to generate aColCache[].lru values */
@@ -3907,6 +3920,23 @@ 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 expr_str Ck constraint expression source string to
+ * raise an informative error.
+ * @param name Check constraint name to raise an informative
+ * error.
+ * @param tuple_fetcher_reg The VDBE register with prepared
+ * tuple_fetcher pointer inside is
+ * initialized with a tuple to be inserted.
+ */
+void
+vdbe_emit_ck_constraint(struct Parse *parser, struct Expr *expr,
+ const char *name, const char *expr_str,
+ int tuple_fetcher_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/vdbe.h b/src/box/sql/vdbe.h
index f9bb96f09..09ea8935b 100644
--- a/src/box/sql/vdbe.h
+++ b/src/box/sql/vdbe.h
@@ -140,7 +140,6 @@ struct SubProgram {
/* Error message codes for OP_Halt */
#define P5_ConstraintNotNull 1
#define P5_ConstraintUnique 2
-#define P5_ConstraintCheck 3
#define P5_ConstraintFK 4
/*
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index 198c2a9d7..d15677df6 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 5bf419d4f..33e41b55e 100644
--- a/test/box/misc.result
+++ b/test/box/misc.result
@@ -524,6 +524,7 @@ t;
193: box.error.CK_DEF_UNSUPPORTED
194: box.error.MULTIKEY_INDEX_MISMATCH
195: box.error.CREATE_CK_CONSTRAINT
+ 196: 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 ede77c630..e1334f435 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 5f0098e23..3973d242f 100644
--- a/test/sql/checks.result
+++ b/test/sql/checks.result
@@ -73,7 +73,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({513, 'CK_CONSTRAINT_01', false, 'SQL', 'X<=5'})
---
@@ -85,7 +90,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})
@@ -118,16 +128,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
@@ -164,14 +186,14 @@ _ = box.space._ck_constraint:insert({s.id, 'physics', false, 'SQL', '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);")
---
@@ -182,7 +204,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({})
---
@@ -214,11 +236,11 @@ _ = box.space._ck_constraint:insert({s.id, 'conflict', false, 'SQL', '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);")
---
@@ -266,11 +288,13 @@ box.space._ck_constraint:select()[1].code
...
box.execute("INSERT INTO t1 VALUES('1 a')")
---
-- 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'':
+ x LIKE ''1 a'''
...
box.execute("INSERT INTO t1 VALUES('1 a')")
---
-- 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'':
+ x LIKE ''1 a'''
...
box.execute("INSERT INTO t1 VALUES('1 a')")
---
@@ -280,6 +304,284 @@ box.execute("DROP TABLE t1")
---
- row_count: 1
...
+--
+-- Test binding reset on new insertion
+--
+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({s.id, 'ZnotNULL', false, 'SQL', 'X = 1 AND Z IS NOT NULL'})
+---
+...
+s:insert({2, 1})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({1, 1})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({1, 1, box.NULL})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({2, 1, 3})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({1, 1})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({1, 1, 3})
+---
+- [1, 1, 3]
+...
+s:drop()
+---
+...
+--
+-- 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({s.id, 'ZnotNULL', false, 'SQL', '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({s.id, 'ZnotNULL'})
+---
+...
+_ = box.space._ck_constraint:insert({s.id, 'XlessY', false, 'SQL', '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({s.id, 'complex1', false, 'SQL', '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({s.id, 'complex2', false, 'SQL', '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 large tuple.
+--
+s = box.schema.create_space('test')
+---
+...
+_ = s:create_index('pk', {parts = {1, 'integer'}})
+---
+...
+format65 = {}
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+for i = 1,66 do
+ table.insert(format65, {name='X'..i, type='integer', is_nullable = true})
+end
+test_run:cmd("setopt delimiter ''");
+---
+...
+s:format(format65)
+---
+...
+_ = box.space._ck_constraint:insert({s.id, 'X1is666andX65is666', false, 'SQL', 'X1 == 666 and X65 == 666 and X63 IS NOT NULL'})
+---
+...
+s:insert(s:frommap({X1 = 1, X65 = 1}))
+---
+- error: 'Check constraint failed ''X1is666andX65is666'': X1 == 666 and X65 == 666
+ and X63 IS NOT NULL'
+...
+s:insert(s:frommap({X1 = 666, X65 = 1}))
+---
+- error: 'Check constraint failed ''X1is666andX65is666'': X1 == 666 and X65 == 666
+ and X63 IS NOT NULL'
+...
+s:insert(s:frommap({X1 = 1, X65 = 666}))
+---
+- error: 'Check constraint failed ''X1is666andX65is666'': X1 == 666 and X65 == 666
+ and X63 IS NOT NULL'
+...
+s:insert(s:frommap({X1 = 666, X65 = 666}))
+---
+- error: 'Check constraint failed ''X1is666andX65is666'': X1 == 666 and X65 == 666
+ and X63 IS NOT NULL'
+...
+s:insert(s:frommap({X1 = 666, X65 = 666, X63 = 1}))
+---
+- [666, null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, 1, null, 666]
+...
+s:drop()
+---
+...
test_run:cmd("clear filter")
---
- true
diff --git a/test/sql/checks.test.lua b/test/sql/checks.test.lua
index 3892138fd..c00654f99 100644
--- a/test/sql/checks.test.lua
+++ b/test/sql/checks.test.lua
@@ -33,9 +33,11 @@ box.space._ck_constraint:insert({513, 'CK_CONSTRAINT_01', false, 'SQL', 'X<5'})
box.space._ck_constraint:count({})
box.execute("INSERT INTO \"test\" VALUES(5);")
+box.space.test:insert({5})
box.space._ck_constraint:replace({513, 'CK_CONSTRAINT_01', false, 'SQL', '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()
@@ -47,8 +49,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.
@@ -103,4 +108,95 @@ box.execute("INSERT INTO t1 VALUES('1 a')")
box.execute("INSERT INTO t1 VALUES('1 a')")
box.execute("DROP TABLE t1")
+--
+-- Test binding reset on new insertion
+--
+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({s.id, 'ZnotNULL', false, 'SQL', 'X = 1 AND Z IS NOT NULL'})
+s:insert({2, 1})
+s:insert({1, 1})
+s:insert({1, 1, box.NULL})
+s:insert({2, 1, 3})
+s:insert({1, 1})
+s:insert({1, 1, 3})
+s:drop()
+
+--
+-- 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({s.id, 'ZnotNULL', false, 'SQL', 'Z IS NOT NULL'})
+s:insert({1, 2, box.NULL})
+s:insert({1, 2})
+_ = box.space._ck_constraint:delete({s.id, 'ZnotNULL'})
+_ = box.space._ck_constraint:insert({s.id, 'XlessY', false, 'SQL', '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({s.id, 'complex1', false, 'SQL', '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({s.id, 'complex2', false, 'SQL', '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 large tuple.
+--
+s = box.schema.create_space('test')
+_ = s:create_index('pk', {parts = {1, 'integer'}})
+format65 = {}
+test_run:cmd("setopt delimiter ';'")
+for i = 1,66 do
+ table.insert(format65, {name='X'..i, type='integer', is_nullable = true})
+end
+test_run:cmd("setopt delimiter ''");
+s:format(format65)
+_ = box.space._ck_constraint:insert({s.id, 'X1is666andX65is666', false, 'SQL', 'X1 == 666 and X65 == 666 and X63 IS NOT NULL'})
+s:insert(s:frommap({X1 = 1, X65 = 1}))
+s:insert(s:frommap({X1 = 666, X65 = 1}))
+s:insert(s:frommap({X1 = 1, X65 = 666}))
+s:insert(s:frommap({X1 = 666, X65 = 666}))
+s:insert(s:frommap({X1 = 666, X65 = 666, X63 = 1}))
+s:drop()
+
test_run:cmd("clear filter")
diff --git a/test/sql/errinj.result b/test/sql/errinj.result
index 414e3c476..28b4b5025 100644
--- a/test/sql/errinj.result
+++ b/test/sql/errinj.result
@@ -490,7 +490,8 @@ _ = box.space._ck_constraint:insert({s.id, 'CK_CONSTRAINT_01', false, 'SQL', 'X<
...
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({s.id, 'CK_CONSTRAINT_01'})
...
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({s.id, 'Xgreater10', false, 'SQL', 'X > 10'}
...
box.execute("INSERT INTO \"test\" VALUES(1, 2);")
---
-- 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(1, 2);")
---
-- 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);")
---
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
More information about the Tarantool-patches
mailing list