From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4A1B32EECA for ; Thu, 16 May 2019 09:56:57 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rEgkRJ1rM1hR for ; Thu, 16 May 2019 09:56:57 -0400 (EDT) Received: from smtp49.i.mail.ru (smtp49.i.mail.ru [94.100.177.109]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 94A862EEC7 for ; Thu, 16 May 2019 09:56:56 -0400 (EDT) From: Kirill Shcherbatov Subject: [tarantool-patches] [PATCH v4 2/4] box: run check constraint tests on space alter Date: Thu, 16 May 2019 16:56:51 +0300 Message-Id: In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org, v.shpilevoy@tarantool.org Cc: Kirill Shcherbatov To perform ck constraints tests before insert or update space operation, we use precompiled VDBE machine is associated with each ck constraint, that is executed in on_replace trigger. Each ck constraint VDBE code is consists of 1) prologue code that maps new(or updated) tuple fields via bindings, 2) ck constraint code is generated by CK constraint AST. In case of ck constraint error the transaction is aborted and ck constraint error is handled as diag message. Each ck constraint use own on_replace trigger. Needed for #3691 --- src/box/alter.cc | 58 ++++++- src/box/ck_constraint.c | 158 ++++++++++++++++++- src/box/ck_constraint.h | 16 +- src/box/errcode.h | 1 + src/box/sql/insert.c | 92 +++-------- src/box/sql/sqlInt.h | 26 +++ src/box/sql/vdbeapi.c | 8 - test/box/misc.result | 1 + test/sql-tap/check.test.lua | 32 ++-- test/sql-tap/fkey2.test.lua | 4 +- test/sql-tap/table.test.lua | 12 +- test/sql/checks.result | 217 ++++++++++++++++++++++++-- test/sql/checks.test.lua | 61 ++++++++ test/sql/errinj.result | 18 ++- test/sql/gh-2981-check-autoinc.result | 12 +- test/sql/types.result | 3 +- 16 files changed, 581 insertions(+), 138 deletions(-) diff --git a/src/box/alter.cc b/src/box/alter.cc index e65c49d14..746ce62f6 100644 --- a/src/box/alter.cc +++ b/src/box/alter.cc @@ -1410,14 +1410,29 @@ void RebuildCkConstraints::alter(struct alter_space *alter) { rlist_swap(&alter->new_space->ck_constraint, &ck_constraint); + struct ck_constraint *ck; + rlist_foreach_entry(ck, &alter->old_space->ck_constraint, link) + trigger_clear(&ck->trigger); rlist_swap(&ck_constraint, &alter->old_space->ck_constraint); + rlist_foreach_entry(ck, &alter->new_space->ck_constraint, link) { + /** + * Triggers would be swapped later on + * alter_space_do. + */ + trigger_add(&alter->old_space->on_replace, &ck->trigger); + } } void RebuildCkConstraints::rollback(struct alter_space *alter) { rlist_swap(&alter->old_space->ck_constraint, &ck_constraint); + struct ck_constraint *ck; + rlist_foreach_entry(ck, &alter->new_space->ck_constraint, link) + trigger_clear(&ck->trigger); rlist_swap(&ck_constraint, &alter->new_space->ck_constraint); + rlist_foreach_entry(ck, &alter->old_space->ck_constraint, link) + trigger_add(&alter->new_space->on_replace, &ck->trigger); } RebuildCkConstraints::~RebuildCkConstraints() @@ -1435,6 +1450,35 @@ RebuildCkConstraints::~RebuildCkConstraints() } } +/** + * Move CK constraints from old space to the new one. + * Despite RebuildCkConstraints, this operation doesn't perform + * objects rebuild. This may be used in scenarios where space + * format doesn't change i.e. in alter index or space trim + * requests. + */ +class MoveCkConstraints: public AlterSpaceOp +{ +public: + MoveCkConstraints(struct alter_space *alter) : AlterSpaceOp(alter) {} + virtual void alter(struct alter_space *alter); + virtual void rollback(struct alter_space *alter); +}; + +void +MoveCkConstraints::alter(struct alter_space *alter) +{ + rlist_swap(&alter->new_space->ck_constraint, + &alter->old_space->ck_constraint); +} + +void +MoveCkConstraints::rollback(struct alter_space *alter) +{ + rlist_swap(&alter->new_space->ck_constraint, + &alter->old_space->ck_constraint); +} + /* }}} */ /** @@ -2145,8 +2189,8 @@ on_replace_dd_index(struct trigger * /* trigger */, void *event) * old space. */ alter_space_move_indexes(alter, iid + 1, old_space->index_id_max + 1); + (void) new MoveCkConstraints(alter); /* Add an op to update schema_version on commit. */ - (void) new RebuildCkConstraints(alter); (void) new UpdateSchemaVersion(alter); alter_space_do(txn, alter); scoped_guard.is_active = false; @@ -2214,7 +2258,7 @@ on_replace_dd_truncate(struct trigger * /* trigger */, void *event) (void) new TruncateIndex(alter, old_index->def->iid); } - (void) new RebuildCkConstraints(alter); + (void) new MoveCkConstraints(alter); alter_space_do(txn, alter); scoped_guard.is_active = false; } @@ -4160,12 +4204,14 @@ on_replace_ck_constraint_rollback(struct trigger *trigger, void *event) assert(space_ck_constraint_by_name(space, ck->def->name, strlen(ck->def->name)) == NULL); rlist_add_entry(&space->ck_constraint, ck, link); + trigger_add(&space->on_replace, &ck->trigger); } else if (stmt->new_tuple != NULL && stmt->old_tuple == NULL) { /* Rollback INSERT check constraint. */ assert(space != NULL); assert(space_ck_constraint_by_name(space, ck->def->name, strlen(ck->def->name)) != NULL); rlist_del_entry(ck, link); + trigger_clear(&ck->trigger); ck_constraint_delete(ck); } else { /* Rollback REPLACE check constraint. */ @@ -4175,7 +4221,9 @@ on_replace_ck_constraint_rollback(struct trigger *trigger, void *event) space_ck_constraint_by_name(space, name, strlen(name)); assert(new_ck != NULL); rlist_del_entry(new_ck, link); + trigger_clear(&new_ck->trigger); rlist_add_entry(&space->ck_constraint, ck, link); + trigger_add(&space->on_replace, &ck->trigger); ck_constraint_delete(new_ck); } } @@ -4242,9 +4290,12 @@ on_replace_dd_ck_constraint(struct trigger * /* trigger*/, void *event) const char *name = new_ck_constraint->def->name; struct ck_constraint *old_ck_constraint = space_ck_constraint_by_name(space, name, strlen(name)); - if (old_ck_constraint != NULL) + if (old_ck_constraint != NULL) { rlist_del_entry(old_ck_constraint, link); + trigger_clear(&old_ck_constraint->trigger); + } rlist_add_entry(&space->ck_constraint, new_ck_constraint, link); + trigger_add(&space->on_replace, &new_ck_constraint->trigger); on_commit->data = old_tuple == NULL ? new_ck_constraint : old_ck_constraint; on_rollback->data = on_commit->data; @@ -4260,6 +4311,7 @@ on_replace_dd_ck_constraint(struct trigger * /* trigger*/, void *event) space_ck_constraint_by_name(space, name, name_len); assert(old_ck_constraint != NULL); rlist_del_entry(old_ck_constraint, link); + trigger_clear(&old_ck_constraint->trigger); on_commit->data = old_ck_constraint; on_rollback->data = old_ck_constraint; } diff --git a/src/box/ck_constraint.c b/src/box/ck_constraint.c index db012837b..43798be76 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 "session.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,138 @@ ck_constraint_resolve_field_names(struct Expr *expr, return rc; } +/** + * Create VDBE machine for ck constraint by given definition and + * expression AST. The generated instructions consist of + * prologue code that maps tuple fields via bindings and ck + * constraint code which implements given expression. + * In case of ck constraint error during VDBE execution, it is + * aborted and error is handled as diag message. + * @param ck_constraint_def Check constraint definition to prepare + * error description. + * @param expr Check constraint expression AST is built for + * given @ck_constraint_def, see for + * (sql_expr_compile + + * ck_constraint_resolve_space_def) implementation. + * @param space_def The space definition of the space this check + * constraint is constructed for. + * @retval not NULL sql_stmt program pointer on success. + * @retval NULL otherwise. + */ +static struct sql_stmt * +ck_constraint_program_compile(struct ck_constraint_def *ck_constraint_def, + struct Expr *expr, struct space_def *space_def) +{ + struct sql *db = sql_get(); + struct Parse parser; + sql_parser_create(&parser, db, 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 fields there before execution. + */ + uint32_t field_count = space_def->field_count; + int bind_tuple_reg = sqlGetTempRange(&parser, field_count); + for (uint32_t i = 0; i < field_count; i++) { + sqlVdbeAddOp2(v, OP_Variable, ++parser.nVar, + bind_tuple_reg + i); + } + /* Generate ck constraint test code. */ + vdbe_emit_ck_constraint(&parser, expr, ck_constraint_def->name, + ck_constraint_def->expr_str, bind_tuple_reg); + + /* Clean-up and restore user-defined sql context. */ + bool is_error = parser.is_aborted; + sql_finish_coding(&parser); + sql_parser_destroy(&parser); + + if (is_error) { + diag_set(ClientError, ER_CREATE_CK_CONSTRAINT, + ck_constraint_def->name, + box_error_message(box_error_last())); + sql_finalize((struct sql_stmt *) v); + return NULL; + } + return (struct sql_stmt *) v; +} + +/** + * Run bytecode implementing check constraint on new tuple + * before insert or replace in space space_def. + * @param ck_constraint Check constraint to run. + * @param space_def The space definition of the space this check + * constraint is constructed for. + * @param new_tuple The tuple to be inserted in space. + * @retval 0 if check constraint test is passed, -1 otherwise. + */ +static int +ck_constraint_program_run(struct ck_constraint *ck_constraint, + const char *new_tuple) +{ + /* + * Prepare parameters for checks->stmt execution: + * Map new tuple fields to Vdbe memory variables in range: + * [1, field_count] + */ + struct space *space = space_by_id(ck_constraint->space_id); + assert(space != NULL); + /* + * When last format fields are nullable, they are + * 'optional' i.e. they may not be present in the tuple. + */ + uint32_t tuple_field_count = mp_decode_array(&new_tuple); + uint32_t field_count = + MIN(tuple_field_count, space->def->field_count); + for (uint32_t i = 0; i < field_count; i++) { + struct sql_bind bind; + if (sql_bind_decode(&bind, i + 1, &new_tuple) != 0 || + sql_bind_column(ck_constraint->stmt, &bind, i + 1) != 0) { + diag_set(ClientError, ER_CK_CONSTRAINT_FAILED, + ck_constraint->def->name, + ck_constraint->def->expr_str); + return -1; + } + } + /* Checks VDBE can't expire, reset expired flag and go. */ + struct Vdbe *v = (struct Vdbe *) ck_constraint->stmt; + v->expired = 0; + while (sql_step(ck_constraint->stmt) == SQL_ROW) {} + /* + * Get VDBE execution state and reset VM to run it + * next time. + */ + return sql_reset(ck_constraint->stmt) != SQL_OK ? -1 : 0; +} + +/** + * Ck constraint trigger function. It ss expected to be executed + * in space::on_replace trigger. + * + * It extracts all ck constraint required context from event and + * run bytecode implementing check constraint to test a new tuple + * before it will be inserted in destination space. + */ +static void +ck_constraint_on_replace_trigger(struct trigger *trigger, void *event) +{ + struct ck_constraint *ck_constraint = + (struct ck_constraint *) trigger->data; + struct txn *txn = (struct txn *) event; + struct txn_stmt *stmt = txn_current_stmt(txn); + assert(stmt != NULL); + struct tuple *new_tuple = stmt->new_tuple; + if (new_tuple == NULL) + return; + if (ck_constraint_program_run(ck_constraint, + tuple_data(new_tuple)) != 0) + diag_raise(); +} + struct ck_constraint * ck_constraint_new(struct ck_constraint_def *ck_constraint_def, struct space_def *space_def) @@ -73,23 +210,33 @@ ck_constraint_new(struct ck_constraint_def *ck_constraint_def, return NULL; } ck_constraint->def = NULL; + ck_constraint->stmt = NULL; ck_constraint->space_id = space_def->id; rlist_create(&ck_constraint->link); - ck_constraint->expr = + trigger_create(&ck_constraint->trigger, + ck_constraint_on_replace_trigger, ck_constraint, + NULL); + struct Expr *expr = sql_expr_compile(sql_get(), ck_constraint_def->expr_str, strlen(ck_constraint_def->expr_str)); - if (ck_constraint->expr == NULL || - ck_constraint_resolve_field_names(ck_constraint->expr, - space_def) != 0) { + if (expr == NULL || + ck_constraint_resolve_field_names(expr, space_def) != 0) { diag_set(ClientError, ER_CREATE_CK_CONSTRAINT, ck_constraint_def->name, box_error_message(box_error_last())); goto error; } + ck_constraint->stmt = + ck_constraint_program_compile(ck_constraint_def, expr, + space_def); + if (ck_constraint->stmt == NULL) + goto error; + sql_expr_delete(sql_get(), expr, false); ck_constraint->def = ck_constraint_def; return ck_constraint; error: + sql_expr_delete(sql_get(), expr, false); ck_constraint_delete(ck_constraint); return NULL; } @@ -97,7 +244,8 @@ error: void ck_constraint_delete(struct ck_constraint *ck_constraint) { - sql_expr_delete(sql_get(), ck_constraint->expr, false); + assert(rlist_empty(&ck_constraint->trigger.link)); + sql_finalize(ck_constraint->stmt); free(ck_constraint->def); TRASH(ck_constraint); free(ck_constraint); diff --git a/src/box/ck_constraint.h b/src/box/ck_constraint.h index 9c72d5977..a948a074e 100644 --- a/src/box/ck_constraint.h +++ b/src/box/ck_constraint.h @@ -32,6 +32,7 @@ */ #include +#include "trigger.h" #include "small/rlist.h" #if defined(__cplusplus) @@ -40,6 +41,7 @@ extern "C" { struct space; struct space_def; +struct sql_stmt; struct Expr; /** The supported language of the ck constraint. */ @@ -81,12 +83,16 @@ struct ck_constraint { /** The check constraint definition. */ struct ck_constraint_def *def; /** - * The check constraint expression AST is built for - * ck_constraint::def::expr_str with sql_expr_compile - * and resolved with sql_resolve_self_reference for - * space with space[ck_constraint::space_id] definition. + * Precompiled reusable VDBE program for processing check + * constraints and setting bad exitcode and error + * message when ck condition unsatisfied. */ - struct Expr *expr; + struct sql_stmt *stmt; + /** + * Trigger object executing check constraint before + * insert and replace operations. + */ + struct trigger trigger; /** * The id of the space this check constraint is * built for. diff --git a/src/box/errcode.h b/src/box/errcode.h index 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/sql/insert.c b/src/box/sql/insert.c index 7b25d18b3..8409ab343 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 new_tuple_reg) { - Walker w; - memset(&w, 0, sizeof(w)); - w.eCode = 0; - w.xExprCallback = checkConstraintExprNode; - w.u.aiCol = aiChng; - sqlWalkExpr(&w, pExpr); - testcase(w.eCode == 0); - testcase(w.eCode == CKCNSTRNT_COLUMN); - return !w.eCode; + parser->ckBase = new_tuple_reg; + struct Vdbe *v = sqlGetVdbe(parser); + const char *ck_constraint_name = sqlDbStrDup(parser->db, name); + VdbeNoopComment((v, "BEGIN: ck constraint %s test", + ck_constraint_name)); + int check_is_passed = sqlVdbeMakeLabel(v); + sqlExprIfTrue(parser, expr, check_is_passed, SQL_JUMPIFNULL); + sqlMayAbort(parser); + const char *fmt = tnt_errcode_desc(ER_CK_CONSTRAINT_FAILED); + const char *error_msg = tt_sprintf(fmt, ck_constraint_name, expr_str); + sqlVdbeAddOp4(v, OP_Halt, SQL_TARANTOOL_ERROR, ON_CONFLICT_ACTION_ABORT, + 0, sqlDbStrDup(parser->db, error_msg), P4_DYNAMIC); + sqlVdbeChangeP5(v, ER_CK_CONSTRAINT_FAILED); + VdbeNoopComment((v, "END: ck constraint %s test", ck_constraint_name)); + sqlVdbeResolveLabel(v, check_is_passed); } void @@ -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 0753705ec..d353d7906 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 */ @@ -3894,6 +3905,21 @@ vdbe_emit_constraint_checks(struct Parse *parse_context, enum on_conflict_action on_conflict, int ignore_label, int *upd_cols); +/** + * Gnerate code to make check constraints tests on tuple insertion + * on INSERT, REPLACE or UPDATE operations. + * @param parser Current parsing context. + * @param expr Check constraint AST. + * @param name Check constraint name to raise error. + * @param new_tuple_reg The first ck_constraint::stmt VDBE + * register of the range + * space_def::field_count representing a + * new tuple to be inserted. + */ +void +vdbe_emit_ck_constraint(struct Parse *parser, struct Expr *expr, + const char *name, const char *expr_str, + int new_tuple_reg); /** * This routine generates code to finish the INSERT or UPDATE * operation that was started by a prior call to diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c index d2868567b..5b3ac4b6a 100644 --- a/src/box/sql/vdbeapi.c +++ b/src/box/sql/vdbeapi.c @@ -132,14 +132,6 @@ sql_finalize(sql_stmt * pStmt) return rc; } -/* - * Terminate the current execution of an SQL statement and reset it - * back to its starting state so that it can be reused. A success code from - * the prior execution is returned. - * - * This routine sets the error code and string returned by - * sql_errcode(), sql_errmsg() and sql_errmsg16(). - */ int sql_reset(sql_stmt * pStmt) { diff --git a/test/box/misc.result b/test/box/misc.result index 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); ]], { -- - 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" -- }) @@ -75,7 +75,7 @@ test:do_catchsql_test( INSERT INTO t1 VALUES(4,3, 2); ]], { -- - 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" -- }) @@ -147,7 +147,7 @@ test:do_catchsql_test( UPDATE t1 SET x=7 WHERE x==2 ]], { -- - 1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1" + 1, "Check constraint failed 'CK_CONSTRAINT_1_T1': x<5" -- }) @@ -167,7 +167,7 @@ test:do_catchsql_test( UPDATE t1 SET x=5 WHERE x==2 ]], { -- - 1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1" + 1, "Check constraint failed 'CK_CONSTRAINT_1_T1': x<5" -- }) @@ -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' ) ); ]], { -- @@ -246,7 +246,7 @@ test:do_catchsql_test( INSERT INTO t2 VALUES(3, 1.1, NULL, NULL); ]], { -- - 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'" -- }) @@ -256,7 +256,7 @@ test:do_catchsql_test( INSERT INTO t2 VALUES(4, NULL, 5, NULL); ]], { -- - 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'" -- }) @@ -266,7 +266,7 @@ test:do_catchsql_test( INSERT INTO t2 VALUES(5, NULL, NULL, 3.14159); ]], { -- - 1, "Failed to execute SQL statement: CHECK constraint failed: THREE" + 1, "Failed to execute SQL statement: Check constraint failed 'THREE': typeof(coalesce(z,''))=='string'" -- }) @@ -413,7 +413,7 @@ test:do_catchsql_test( INSERT INTO t3 VALUES(111,222,333); ]], { -- - 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" -- }) @@ -484,7 +484,7 @@ test:do_catchsql_test( UPDATE t4 SET x=0, y=1; ]], { -- - 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" -- }) @@ -504,7 +504,7 @@ test:do_catchsql_test( UPDATE t4 SET x=0, y=2; ]], { -- - 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" -- }) @@ -581,7 +581,7 @@ test:do_catchsql_test( UPDATE OR FAIL t1 SET x=7-x, y=y+1; ]], { -- - 1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1" + 1, "Check constraint failed 'CK_CONSTRAINT_1_T1': x<5" -- }) @@ -603,7 +603,7 @@ test:do_catchsql_test( INSERT OR ROLLBACK INTO t1 VALUES(8,40.0, 10); ]], { -- - 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" -- }) @@ -636,7 +636,7 @@ test:do_catchsql_test( REPLACE INTO t1 VALUES(6,7, 11); ]], { -- - 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" -- }) @@ -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)" -- }) 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; ]], { -- - 1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_EF" + 1, "Check constraint failed 'CK_CONSTRAINT_1_EF': e!=5" -- }) @@ -382,7 +382,7 @@ test:do_catchsql_test( UPDATE ab SET a = 5; ]], { -- - 1, "Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_EF" + 1, "Check constraint failed 'CK_CONSTRAINT_1_EF': e!=5" -- }) 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); ]], { -- - 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" -- }) test:do_catchsql_test( "table-21.4", [[ - INSERT INTO T21 VALUES(1, 1, -1); + INSERT INTO T21 VALUES(2, 1, -1); ]], { -- - 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" -- }) @@ -1372,7 +1372,7 @@ test:do_catchsql_test( INSERT INTO T28 VALUES(0); ]], { -- - 1, "Failed to execute SQL statement: CHECK constraint failed: CHECK1" + 1, "Failed to execute SQL statement: Check constraint failed 'CHECK1': id != 0" -- }) @@ -1382,7 +1382,7 @@ test:do_catchsql_test( INSERT INTO T28 VALUES(9); ]], { -- - 1, "Failed to execute SQL statement: CHECK constraint failed: CHECK2" + 1, "Failed to execute SQL statement: Check constraint failed 'CHECK2': id > 10" -- }) diff --git a/test/sql/checks.result b/test/sql/checks.result index f675c020b..b74572e3e 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({'CK_CONSTRAINT_01', 513, false, 'X<=5', 'SQL'}) --- @@ -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({'physics', s.id, false, 'X10', 'SQL'}) ... 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'': Xx ), 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. @@ -95,4 +100,60 @@ box.execute("DROP TABLE w2;") -- box.execute("CREATE TABLE t5(x INT PRIMARY KEY, y INT, CHECK( x*y < ? ));") +-- +-- Test ck constraint corner cases +-- +s = box.schema.create_space('test', {engine = engine}) +_ = s:create_index('pk') +s:format({{name='X', type='any'}, {name='Y', type='integer'}, {name='Z', type='integer', is_nullable=true}}) +ck_not_null = box.space._ck_constraint:insert({'ZnotNULL', s.id, false, 'Z IS NOT NULL', 'SQL'}) +s:insert({1, 2, box.NULL}) +s:insert({1, 2}) +_ = box.space._ck_constraint:delete({'ZnotNULL', s.id}) +_ = box.space._ck_constraint:insert({'XlessY', s.id, false, 'X < Y and Y < Z', 'SQL'}) +s:insert({'1', 2}) +s:insert({}) +s:insert({2, 1}) +s:insert({1, 2}) +s:insert({2, 3, 1}) +s:insert({2, 3, 4}) +s:update({2}, {{'+', 2, 3}}) +s:update({2}, {{'+', 2, 3}, {'+', 3, 3}}) +s:replace({2, 1, 3}) +box.snapshot() +s = box.space["test"] +s:update({2}, {{'+', 2, 3}}) +s:update({2}, {{'+', 2, 3}, {'+', 3, 3}}) +s:replace({2, 1, 3}) +s:drop() + +-- +-- Test complex CHECK constraints. +-- +s = box.schema.create_space('test', {engine = engine}) +s:format({{name='X', type='integer'}, {name='Y', type='integer'}, {name='Z', type='integer'}}) +_ = s:create_index('pk', {parts = {3, 'integer'}}) +_ = s:create_index('unique', {parts = {1, 'integer'}}) +_ = box.space._ck_constraint:insert({'complex1', s.id, false, 'x+y==11 OR x*y==12 OR x/y BETWEEN 5 AND 8 OR -x == y+10', 'SQL'}) +s:insert({1, 10, 1}) +s:update({1}, {{'=', 1, 4}, {'=', 2, 3}}) +s:update({1}, {{'=', 1, 12}, {'=', 2, 2}}) +s:update({1}, {{'=', 1, 12}, {'=', 2, -22}}) +s:update({1}, {{'=', 1, 0}, {'=', 2, 1}}) +s:get({1}) +s:update({1}, {{'=', 1, 0}, {'=', 2, 2}}) +s:get({1}) +s:drop() + +s = box.schema.create_space('test', {engine = engine}) +s:format({{name='X', type='integer'}, {name='Z', type='any'}}) +_ = s:create_index('pk', {parts = {1, 'integer'}}) +_ = box.space._ck_constraint:insert({'complex2', s.id, false, 'typeof(coalesce(z,0))==\'integer\'', 'SQL'}) +s:insert({1, 'string'}) +s:insert({1, {map=true}}) +s:insert({1, {'a', 'r','r','a','y'}}) +s:insert({1, 3.14}) +s:insert({1, 666}) +s:drop() + test_run:cmd("clear filter") diff --git a/test/sql/errinj.result b/test/sql/errinj.result index e5bcbc9db..59abb588f 100644 --- a/test/sql/errinj.result +++ b/test/sql/errinj.result @@ -490,7 +490,8 @@ _ = box.space._ck_constraint:insert({'CK_CONSTRAINT_01', s.id, false, 'X<5', 'SQ ... box.execute("INSERT INTO \"test\" VALUES(5);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_01' +- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_01'': + X<5' ... errinj.set("ERRINJ_WAL_IO", true) --- @@ -521,7 +522,8 @@ _ = box.space._ck_constraint:delete({'CK_CONSTRAINT_01', s.id}) ... box.execute("INSERT INTO \"test\" VALUES(6);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_01' +- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_01'': + X<=5' ... errinj.set("ERRINJ_WAL_IO", false) --- @@ -557,11 +559,13 @@ _ = box.space._ck_constraint:insert({'Xgreater10', s.id, false, 'X > 10', 'SQL'} ... box.execute("INSERT INTO \"test\" VALUES(2, 1);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: Xgreater10' +- error: 'Failed to execute SQL statement: Check constraint failed ''Xgreater10'': + X > 10' ... box.execute("INSERT INTO \"test\" VALUES(20, 10);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: XlessY' +- error: 'Failed to execute SQL statement: Check constraint failed ''XlessY'': X < + Y' ... box.execute("INSERT INTO \"test\" VALUES(20, 100);") --- @@ -584,11 +588,13 @@ errinj.set("ERRINJ_WAL_IO", false) ... box.execute("INSERT INTO \"test\" VALUES(2, 1);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: XlessY' +- error: 'Failed to execute SQL statement: Check constraint failed ''XlessY'': X < + Y' ... box.execute("INSERT INTO \"test\" VALUES(20, 10);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: XlessY' +- error: 'Failed to execute SQL statement: Check constraint failed ''XlessY'': X < + Y' ... box.execute("INSERT INTO \"test\" VALUES(20, 100);") --- diff --git a/test/sql/gh-2981-check-autoinc.result b/test/sql/gh-2981-check-autoinc.result index 7384c81e8..e57789897 100644 --- a/test/sql/gh-2981-check-autoinc.result +++ b/test/sql/gh-2981-check-autoinc.result @@ -29,7 +29,8 @@ box.execute("insert into t1 values (18, null);") ... box.execute("insert into t1(s2) values (null);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1' +- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T1'': + s1 <> 19' ... box.execute("insert into t2 values (18, null);") --- @@ -37,7 +38,8 @@ box.execute("insert into t2 values (18, null);") ... box.execute("insert into t2(s2) values (null);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T2' +- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T2'': + s1 <> 19 AND s1 <> 25' ... box.execute("insert into t2 values (24, null);") --- @@ -45,7 +47,8 @@ box.execute("insert into t2 values (24, null);") ... box.execute("insert into t2(s2) values (null);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T2' +- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T2'': + s1 <> 19 AND s1 <> 25' ... box.execute("insert into t3 values (9, null)") --- @@ -53,7 +56,8 @@ box.execute("insert into t3 values (9, null)") ... box.execute("insert into t3(s2) values (null)") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T3' +- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T3'': + s1 < 10' ... box.execute("DROP TABLE t1") --- diff --git a/test/sql/types.result b/test/sql/types.result index 582785413..ccbdd8c50 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -709,7 +709,8 @@ box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a BOOLEAN CHECK (a = true));") ... box.execute("INSERT INTO t1 VALUES (1, false);") --- -- error: 'Failed to execute SQL statement: CHECK constraint failed: CK_CONSTRAINT_1_T1' +- error: 'Failed to execute SQL statement: Check constraint failed ''CK_CONSTRAINT_1_T1'': + a = true' ... box.execute("INSERT INTO t1 VALUES (1, true);") --- -- 2.21.0