[Tarantool-patches] [PATCH v1 1/1] sql: fix INSTEAD OF DELETE trigger for VIEW
imeevma at tarantool.org
imeevma at tarantool.org
Wed Jan 22 16:54:04 MSK 2020
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
More information about the Tarantool-patches
mailing list