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 0609522BC0 for ; Sat, 29 Dec 2018 05:49:07 -0500 (EST) 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 i9PS9-eMOux4 for ; Sat, 29 Dec 2018 05:49:06 -0500 (EST) Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (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 B32E022BA8 for ; Sat, 29 Dec 2018 05:49:06 -0500 (EST) From: Kirill Shcherbatov Subject: [tarantool-patches] [PATCH v2 3/5] sql: fix fkey exception for self-referenced table Date: Sat, 29 Dec 2018 13:49:00 +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, korablev@tarantool.org Cc: Kirill Shcherbatov UPDATE operation doesn't fail when fkey self-reference condition unsatisfied, when table has other records. To do not raise error where it is not necessary Vdbe makes lookup in parent table with OP_Found. This branch is not valid for self-referenced table since its looking for a tuple affected by UPDATE operation and since the foreign key has already detected a conflict it mast be raised. Example: CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT, c INT, d TEXT, UNIQUE(a, b), FOREIGN KEY(c, d) REFERENCES t6(a, b)); INSERT INTO t6 VALUES(1, 'a', 1, 'a'); INSERT INTO t6 VALUES(100, 'one', 100, 'one'); UPDATE t6 SET c = 1, d = 'a' WHERE a = 100; -- fk conflict must be raised here Needed for #3850 Closes #3918 --- src/box/sql/fkey.c | 58 +++++++++++++++++++++++-------------- test/sql-tap/fkey3.test.lua | 5 ++-- 2 files changed, 38 insertions(+), 25 deletions(-) diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c index 4e3270f0c..6242275c6 100644 --- a/src/box/sql/fkey.c +++ b/src/box/sql/fkey.c @@ -194,7 +194,7 @@ static void fkey_lookup_parent(struct Parse *parse_context, struct space *parent, struct fkey_def *fk_def, uint32_t referenced_idx, - int reg_data, int incr_count) + int reg_data, int incr_count, bool is_update) { assert(incr_count == -1 || incr_count == 1); struct Vdbe *v = sqlite3GetVdbe(parse_context); @@ -221,14 +221,6 @@ fkey_lookup_parent(struct Parse *parse_context, struct space *parent, sqlite3VdbeAddOp2(v, OP_IsNull, reg, ok_label); } uint32_t field_count = fk_def->field_count; - int temp_regs = sqlite3GetTempRange(parse_context, field_count); - int rec_reg = sqlite3GetTempReg(parse_context); - vdbe_emit_open_cursor(parse_context, cursor, referenced_idx, parent); - link = fk_def->links; - for (uint32_t i = 0; i < field_count; ++i, ++link) { - sqlite3VdbeAddOp2(v, OP_Copy, link->child_field + 1 + reg_data, - temp_regs + i); - } /* * If the parent table is the same as the child table, and * we are about to increment the constraint-counter (i.e. @@ -253,15 +245,36 @@ fkey_lookup_parent(struct Parse *parse_context, struct space *parent, } sqlite3VdbeGoto(v, ok_label); } - struct index *idx = space_index(parent, referenced_idx); - assert(idx != NULL); - sqlite3VdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count, rec_reg, - sql_space_index_affinity_str(parse_context->db, - parent->def, idx->def), - P4_DYNAMIC); - sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0); - sqlite3ReleaseTempReg(parse_context, rec_reg); - sqlite3ReleaseTempRange(parse_context, temp_regs, field_count); + /** + * Make a lookup in a parent table with OP_Found. + * We mustn't make it for a self-referenced table since + * it's tuple will be modified by the update operation. + * And since the foreign key has already detected a + * conflict, fk counter must be increased. + */ + if (!(fkey_is_self_referenced(fk_def) && is_update)) { + int temp_regs = sqlite3GetTempRange(parse_context, field_count); + int rec_reg = sqlite3GetTempReg(parse_context); + vdbe_emit_open_cursor(parse_context, cursor, referenced_idx, + parent); + link = fk_def->links; + for (uint32_t i = 0; i < field_count; ++i, ++link) { + sqlite3VdbeAddOp2(v, OP_Copy, + link->child_field + 1 + reg_data, + temp_regs + i); + } + struct index *idx = space_index(parent, referenced_idx); + assert(idx != NULL); + sqlite3VdbeAddOp4(v, OP_MakeRecord, temp_regs, field_count, + rec_reg, + sql_space_index_affinity_str(parse_context->db, + parent->def, + idx->def), + P4_DYNAMIC); + sqlite3VdbeAddOp4Int(v, OP_Found, cursor, ok_label, rec_reg, 0); + sqlite3ReleaseTempReg(parse_context, rec_reg); + sqlite3ReleaseTempRange(parse_context, temp_regs, field_count); + } struct session *session = current_session(); if (!fk_def->is_deferred && (session->sql_flags & SQLITE_DeferFKs) == 0 && @@ -517,6 +530,7 @@ void fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, int reg_new, const int *changed_cols) { + bool is_update = changed_cols != NULL; struct sqlite3 *db = parser->db; struct session *user_session = current_session(); @@ -534,7 +548,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, struct fkey *fk; rlist_foreach_entry(fk, &space->child_fkey, child_link) { struct fkey_def *fk_def = fk->def; - if (changed_cols != NULL && !fkey_is_self_referenced(fk_def) && + if (is_update && !fkey_is_self_referenced(fk_def) && !fkey_is_modified(fk_def, FIELD_LINK_CHILD, changed_cols)) continue; parser->nTab++; @@ -549,7 +563,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, * foreign key constraint violation. */ fkey_lookup_parent(parser, parent, fk_def, fk->index_id, - reg_old, -1); + reg_old, -1, is_update); } if (reg_new != 0 && !fkey_action_is_set_null(parser, fk)) { /* @@ -568,7 +582,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, * cause an FK violation. */ fkey_lookup_parent(parser, parent, fk_def, fk->index_id, - reg_new, +1); + reg_new, +1, is_update); } } /* @@ -577,7 +591,7 @@ fkey_emit_check(struct Parse *parser, struct Table *tab, int reg_old, */ rlist_foreach_entry(fk, &space->parent_fkey, parent_link) { struct fkey_def *fk_def = fk->def; - if (changed_cols != NULL && + if (is_update && !fkey_is_modified(fk_def, FIELD_LINK_PARENT, changed_cols)) continue; if (!fk_def->is_deferred && diff --git a/test/sql-tap/fkey3.test.lua b/test/sql-tap/fkey3.test.lua index 8fbbdcfbc..9bd1aef23 100755 --- a/test/sql-tap/fkey3.test.lua +++ b/test/sql-tap/fkey3.test.lua @@ -190,15 +190,14 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "fkey3-3.9", [[ INSERT INTO t6 VALUES(100, 'one', 100, 'one'); UPDATE t6 SET c = 1, d = 'a' WHERE a = 100; - DELETE FROM t6 WHERE a = 100; - SELECT * FROM t6 WHERE a = 100; ]], { -- + 1, "FOREIGN KEY constraint failed" -- }) -- 2.19.2