From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: 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 dev.tarantool.org (Postfix) with ESMTPS id 1EE1D46970E for ; Wed, 22 Jan 2020 16:54:06 +0300 (MSK) From: imeevma@tarantool.org Date: Wed, 22 Jan 2020 16:54:04 +0300 Message-Id: Subject: [Tarantool-patches] [PATCH v1 1/1] sql: fix INSTEAD OF DELETE trigger for VIEW List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: korablev@tarantool.org Cc: tarantool-patches@dev.tarantool.org This patch makes the INSTEAD OF DELETE trigger work for every row in VIEW. Prior to this patch, it worked only once for each group of non-unique rows. Also, this patch adds tests to check that the INSTEAD OF UPDATE trigger work for every row in VIEW. Closes #4740 --- https://github.com/tarantool/tarantool/issues/4740 https://github.com/tarantool/tarantool/tree/imeevma/gh-4740-fix-instead-of-delete-trigger src/box/sql/delete.c | 2 +- test/sql/view.result | 79 ++++++++++++++++++++++++++++++++++++++++++++++++++ test/sql/view.test.lua | 18 ++++++++++++ 3 files changed, 98 insertions(+), 1 deletion(-) diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c index 8d9ae4f..e9edbcc 100644 --- a/src/box/sql/delete.c +++ b/src/box/sql/delete.c @@ -231,7 +231,7 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list, int eph_cursor = parse->nTab++; int addr_eph_open = sqlVdbeCurrentAddr(v); if (is_view) { - pk_len = space->def->field_count; + pk_len = space->def->field_count + 1; parse->nMem += pk_len; sqlVdbeAddOp2(v, OP_OpenTEphemeral, reg_eph, pk_len); diff --git a/test/sql/view.result b/test/sql/view.result index f3b4da1..1e73ff6 100644 --- a/test/sql/view.result +++ b/test/sql/view.result @@ -321,3 +321,82 @@ box.execute("DROP TABLE t1;"); --- - row_count: 1 ... +-- +-- gh-4740: make sure INSTEAD OF DELETE and INSTEAD OF UPDATE +-- triggers work for each row of view. +-- +box.cfg{} +--- +... +box.execute('CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT, a INT);') +--- +- row_count: 1 +... +box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT, a INT);') +--- +- row_count: 1 +... +box.execute('CREATE VIEW v AS SELECT a FROM t;') +--- +- row_count: 1 +... +box.execute('CREATE TRIGGER r1 INSTEAD OF DELETE ON v FOR EACH ROW BEGIN INSERT INTO t1 VALUES (NULL, 1); END;') +--- +- row_count: 1 +... +box.execute('CREATE TRIGGER r2 INSTEAD OF UPDATE ON v FOR EACH ROW BEGIN INSERT INTO t1 VALUES (NULL, 2); END;') +--- +- row_count: 1 +... +box.execute('INSERT INTO t VALUES (NULL, 1), (NULL, 1), (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 3);') +--- +- autoincrement_ids: + - 1 + - 2 + - 3 + - 4 + - 5 + - 6 + row_count: 6 +... +box.execute('DELETE FROM v;') +--- +- row_count: 0 +... +box.execute('UPDATE v SET a = 10;') +--- +- row_count: 0 +... +box.execute('SELECT * FROM t1;') +--- +- metadata: + - name: I + type: integer + - name: A + type: integer + rows: + - [1, 1] + - [2, 1] + - [3, 1] + - [4, 1] + - [5, 1] + - [6, 1] + - [7, 2] + - [8, 2] + - [9, 2] + - [10, 2] + - [11, 2] + - [12, 2] +... +box.execute('DROP VIEW v;') +--- +- row_count: 1 +... +box.execute('DROP TABLE t;') +--- +- row_count: 1 +... +box.execute('DROP TABLE t1;') +--- +- row_count: 1 +... diff --git a/test/sql/view.test.lua b/test/sql/view.test.lua index 76ea303..47ca726 100644 --- a/test/sql/view.test.lua +++ b/test/sql/view.test.lua @@ -120,3 +120,21 @@ box.space.T2:drop() -- Cleanup box.execute("DROP VIEW v1;"); box.execute("DROP TABLE t1;"); + +-- +-- gh-4740: make sure INSTEAD OF DELETE and INSTEAD OF UPDATE +-- triggers work for each row of view. +-- +box.cfg{} +box.execute('CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT, a INT);') +box.execute('CREATE TABLE t1 (i INT PRIMARY KEY AUTOINCREMENT, a INT);') +box.execute('CREATE VIEW v AS SELECT a FROM t;') +box.execute('CREATE TRIGGER r1 INSTEAD OF DELETE ON v FOR EACH ROW BEGIN INSERT INTO t1 VALUES (NULL, 1); END;') +box.execute('CREATE TRIGGER r2 INSTEAD OF UPDATE ON v FOR EACH ROW BEGIN INSERT INTO t1 VALUES (NULL, 2); END;') +box.execute('INSERT INTO t VALUES (NULL, 1), (NULL, 1), (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 3);') +box.execute('DELETE FROM v;') +box.execute('UPDATE v SET a = 10;') +box.execute('SELECT * FROM t1;') +box.execute('DROP VIEW v;') +box.execute('DROP TABLE t;') +box.execute('DROP TABLE t1;') -- 2.7.4