[tarantool-patches] Re: [PATCH v5 5/6] box: run check constraint tests on space alter
Kirill Shcherbatov
kshcherbatov at tarantool.org
Fri May 31 16:45:28 MSK 2019
> In the commit title you somewhy said, that you run
> CHECKs on alter, but it is not so. CHECKs on a non-empty
> space disable its alteration. This patch runs CHECKs on
> DML, not on DDL.
I've updated the commit message
box: run check constraint on space insertion
> Additionally I just realized, that probably the standard
> explicitly says in which order we should run CHECKs - before
> ON REPLACE triggers or after? Or it does not matter? Please,
> investigate. If CHECKs are always first, for example, then
> we should append CHECKs on_replace trigger in the first place.
Standard says nothing about relative ck constraints and triggers order.
There was relative discussion here a few time ago
https://github.com/tarantool/tarantool/issues/2356
but about FK constraints.
Let's keep it as is.
> 1. But it is not a swap. Before it was
>
> old_space: old_space.trigger, new_space: nil
>
> After it is
>
> old_space: new_space.trigger, new_space: nil
>
> After one another 'swap' nothing changes - you just always
> assign a new trigger to the old space. swap(swap(a, b))
> should be equal (a, b). In your case you somehow get
> swap(swap(a, nil)) = (c, nil).
>
> What is more, now old_space has 2 pointers at new_space:
> the new_space pointer itself (via trigger.data), and
> &new_space.trigger (via on_replace) list. It is not ok,
> obviously. Probably you decided to do so, because this error is
> later recovered by space_swap_triggers() - it is a hack, sorry.
> I think, that in this function you should not even touch
> on_replace.
>
> Lets deal with these problems one-by-one.
>
> 1) About old_space referencing new_space via trigger.data.
> You don't need it. In the trigger function you obtain
> struct txn_stmt, which already contains struct space pointer.
> You do not need to assign trigger.data at all. Remove it,
> please.
>
> 2) About old_space referencing &new_space.trigger. Lets store
> in struct space a pointer at struct trigger for ck constraints.
> Not the trigger itself, but a pointer.
> It solves the problem, and allows to swap struct trigger pointers
> in Move and Rebuild. Also, it allows to do not allocate struct
> trigger when it is not used. Now it is allocated always, because
> is a part of struct space. You will allocate that trigger on demand,
> when a first CK appears.
>
> When you will have the things above done, your Move and RebuildCK
> will not even touch space.on_replace. They will only swap ck lists
> and struct trigger *ck_trigger pointer in old_ and new_space.
>
> Space.on_replace swap will be done by space_swap_triggers().
Done.
>> + diag_set(OutOfMemory, sizeof(struct Vdbe), "sqlGetVdbe",
>> + "vdbe");
>
> 2. You did not destroy a parser.
You are right. Fixed.
>> + struct space *space = (struct space *) trigger->data;
>
> 3. Take struct space from txn_stmt and do not touch trigger.data.
> Otherwise the trigger object depends on space, and you can't
> swap them.
Ok. Done.
> 4. Perfect, this should work quite swiftly.
> 5. You do not need that header. Just announce struct trigger.
>
>> +#include "sql.h"
> 6. Why do you need it? The only new function here is
> ck_constraint_on_replace_trigger and its declaration does not
> depend on SQL. The only new attribute here is struct sql_stmt,
> but you announced it, and it is ok.
Ok. Done.
> 7. Please, move that code into 'if (iTab < 0)'. Otherwise
> non-CHECK code will check two conditions - tuple_fetcher_reg
> and iTab. Before your patch it was checking iTab only.
Done.
>> 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.
>
> 8. Why do you allow NULL? I grepped and see that it is never NULL.
I didn't like to touch the routine's body at that moment.
ok, refactored now.
>> +/**
>> + * 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.
>
> 9. Mismatching order of parameters.
Fixed.
=======================================================
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 | 99 +++++++-
src/box/ck_constraint.c | 133 ++++++++++-
src/box/ck_constraint.h | 28 ++-
src/box/errcode.h | 1 +
src/box/space.c | 2 +
src/box/space.h | 2 +
src/box/sql/expr.c | 13 +-
src/box/sql/insert.c | 92 ++------
src/box/sql/sqlInt.h | 37 ++-
src/box/sql/vdbe.h | 1 -
src/box/sql/vdbeapi.c | 29 +--
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, 774 insertions(+), 165 deletions(-)
diff --git a/src/box/alter.cc b/src/box/alter.cc
index bedeb71cd..7a6975427 100644
--- a/src/box/alter.cc
+++ b/src/box/alter.cc
@@ -1438,6 +1438,8 @@ 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);
+ SWAP(new_space->ck_constraint_trigger,
+ old_space->ck_constraint_trigger);
}
void
@@ -1467,6 +1469,44 @@ 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);
+ SWAP(new_space->ck_constraint_trigger,
+ old_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);
+}
+
/* }}} */
/**
@@ -2177,7 +2217,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);
@@ -2246,7 +2286,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;
}
@@ -4201,9 +4241,9 @@ on_replace_ck_constraint_rollback(struct trigger *trigger, void *event)
{
struct txn_stmt *stmt = txn_last_stmt((struct txn *) event);
struct ck_constraint *ck = (struct ck_constraint *)trigger->data;
- struct space *space = NULL;
- if (ck != NULL)
- space = space_by_id(ck->def->space_id);
+ assert(ck != NULL);
+ struct space *space = space_by_id(ck->def->space_id);
+ struct trigger *ck_trigger = space->ck_constraint_trigger;
if (stmt->old_tuple != NULL && stmt->new_tuple == NULL) {
/* Rollback DELETE check constraint. */
assert(ck != NULL);
@@ -4211,6 +4251,8 @@ 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(&ck_trigger->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);
@@ -4218,6 +4260,11 @@ 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(ck_trigger);
+ ck_trigger->destroy(ck_trigger);
+ space->ck_constraint_trigger = NULL;
+ }
} else {
/* Rollback REPLACE check constraint. */
assert(space != NULL);
@@ -4240,8 +4287,23 @@ on_replace_ck_constraint_commit(struct trigger *trigger, void *event)
{
struct txn_stmt *stmt = txn_last_stmt((struct txn *) event);
struct ck_constraint *ck = (struct ck_constraint *)trigger->data;
- if (stmt->old_tuple != NULL)
+ assert(ck != NULL);
+ struct space *space = space_by_id(ck->def->space_id);
+ assert(space != NULL);
+ if (stmt->old_tuple != NULL && stmt->new_tuple == NULL) {
+ /* Commit DELETE check constraint. */
+ struct trigger *ck_trigger = space->ck_constraint_trigger;
+ assert(ck_trigger != NULL);
+ if (rlist_empty(&space->ck_constraint)) {
+ ck_trigger->destroy(ck_trigger);
+ space->ck_constraint_trigger = NULL;
+ ck_constraint_delete(ck);
+ }
+ } else if (stmt->old_tuple != NULL) {
+ /* Commit REPLACE check constraint. */
+ assert(stmt->new_tuple != NULL);
ck_constraint_delete(ck);
+ }
}
/** A trigger invoked on replace in the _ck_constraint space. */
@@ -4257,6 +4319,8 @@ on_replace_dd_ck_constraint(struct trigger * /* trigger*/, void *event)
tuple_field_u32_xc(old_tuple != NULL ? old_tuple : new_tuple,
BOX_CK_CONSTRAINT_FIELD_SPACE_ID);
struct space *space = space_cache_find_xc(space_id);
+ struct trigger *ck_trigger = space->ck_constraint_trigger;
+ assert(ck_trigger == NULL || !rlist_empty(&ck_trigger->link));
struct trigger *on_rollback =
txn_alter_trigger_new(on_replace_ck_constraint_rollback, NULL);
struct trigger *on_commit =
@@ -4273,7 +4337,7 @@ on_replace_dd_ck_constraint(struct trigger * /* trigger*/, void *event)
/* Create or replace check constraint. */
struct ck_constraint_def *ck_def =
ck_constraint_def_new_from_tuple(new_tuple);
- auto ck_guard = make_scoped_guard([=] { free(ck_def); });
+ auto ck_def_guard = make_scoped_guard([=] { free(ck_def); });
/*
* FIXME: Ck constraint creation on non-empty
* space is not implemented yet.
@@ -4288,17 +4352,34 @@ on_replace_dd_ck_constraint(struct trigger * /* trigger*/, void *event)
ck_constraint_new(ck_def, space->def);
if (new_ck_constraint == NULL)
diag_raise();
- ck_guard.is_active = false;
+ ck_def_guard.is_active = false;
+ auto ck_guard = make_scoped_guard([=] {
+ ck_constraint_delete(new_ck_constraint); });
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)
rlist_del_entry(old_ck_constraint, link);
rlist_add_entry(&space->ck_constraint, new_ck_constraint, link);
+ if (ck_trigger == NULL) {
+ ck_trigger =
+ (struct trigger *)malloc(sizeof(*ck_trigger));
+ if (ck_trigger == NULL) {
+ tnt_raise(OutOfMemory, sizeof(*ck_trigger),
+ "malloc", "ck_trigger");
+ }
+ trigger_create(ck_trigger,
+ ck_constraint_on_replace_trigger, NULL,
+ (trigger_f0) free);
+ trigger_add(&space->on_replace, ck_trigger);
+ space->ck_constraint_trigger = ck_trigger;
+ }
+ ck_guard.is_active = false;
on_commit->data = old_tuple == NULL ? new_ck_constraint :
old_ck_constraint;
on_rollback->data = on_commit->data;
} else {
+ assert(ck_trigger != NULL);
assert(new_tuple == NULL && old_tuple != NULL);
/* Drop check constraint. */
uint32_t name_len;
@@ -4310,6 +4391,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(ck_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 69b9793ea..d7935794d 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"};
@@ -89,6 +94,118 @@ 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) {
+ sql_parser_destroy(&parser);
+ 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)
+{
+ (void) trigger;
+ 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 = stmt->space;
+ assert(space != NULL);
+ 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_prepare_tuple(fetcher, new_tuple);
+
+ 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)
@@ -105,22 +222,28 @@ ck_constraint_new(struct ck_constraint_def *ck_constraint_def,
return NULL;
}
ck_constraint->def = NULL;
+ ck_constraint->stmt = NULL;
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;
}
@@ -128,7 +251,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);
ck_constraint_def_delete(ck_constraint->def);
TRASH(ck_constraint);
free(ck_constraint);
diff --git a/src/box/ck_constraint.h b/src/box/ck_constraint.h
index 3ae3d5c91..180280d00 100644
--- a/src/box/ck_constraint.h
+++ b/src/box/ck_constraint.h
@@ -40,7 +40,9 @@ extern "C" {
struct space;
struct space_def;
+struct sql_stmt;
struct Expr;
+struct trigger;
/** Supported languages of ck constraint. */
enum ck_constraint_language {
@@ -86,12 +88,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;
/**
* Organize check constraint structs into linked list
* with space::ck_constraint.
@@ -185,6 +186,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..b6ad87bf7 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)
@@ -207,6 +208,7 @@ space_new_ephemeral(struct space_def *def, struct rlist *key_list)
void
space_delete(struct space *space)
{
+ assert(space->ck_constraint_trigger == NULL);
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 7ffe884b3..949f37d45 100644
--- a/src/box/space.h
+++ b/src/box/space.h
@@ -202,6 +202,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..78f63cbfd 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -3731,9 +3731,16 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
int iTab = pExpr->iTable;
int col = pExpr->iColumn;
if (iTab < 0) {
- if (pParse->ckBase > 0) {
- /* Generating CHECK constraints. */
- return col + pParse->ckBase;
+ if (pParse->tuple_fetcher_reg > 0) {
+ /*
+ * Generating CHECK
+ * constraints.
+ */
+ assert(iTab < 0);
+ sqlVdbeAddOp3(v, OP_Fetch,
+ pParse->tuple_fetcher_reg,
+ col, target);
+ return target;
} 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
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index c4b629c6c..c32de2be9 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -798,51 +798,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
@@ -912,35 +887,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..e2c760623 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.
+ * @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,24 @@ 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 an informative
+ * error.
+ * @param expr_str Ck constraint expression source string 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..ace1eceb1 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -132,30 +132,17 @@ 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)
{
- int rc;
- if (pStmt == 0) {
- rc = SQL_OK;
- } else {
- Vdbe *v = (Vdbe *) pStmt;
- sql *db = v->db;
- checkProfileCallback(db, v);
- rc = sqlVdbeReset(v);
- sqlVdbeRewind(v);
- assert((rc & (db->errMask)) == rc);
- rc = sqlApiExit(db, rc);
- }
- return rc;
+ assert(pStmt != NULL);
+ struct Vdbe *v = (Vdbe *) pStmt;
+ struct sql *db = v->db;
+ checkProfileCallback(db, v);
+ int rc = sqlVdbeReset(v);
+ sqlVdbeRewind(v);
+ assert((rc & (db->errMask)) == rc);
+ return sqlApiExit(db, rc);
}
/*
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 efe626428..6272cea7a 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);")
---
@@ -269,11 +291,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')")
---
@@ -283,6 +307,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 399caf134..dadedb935 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 1c9ef5468..9f8569e5a 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -741,7 +741,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