[tarantool-patches] [PATCH v1 1/1] sql: triggers on view unfinished updates

[tarantool-patches] [PATCH v1 1/1] sql: triggers on view unfinished updates

[Kirill Shcherbatov]

Excepting primary index is a typical thing for view so
we should use space_index instead of index_find that doesn't
setup diag error when no index is finded.

Closes #3536.
---
Branch: https://github.com/tarantool/tarantool/tree/kshch/gh-3536-failed-view-insertion
Issue: https://github.com/tarantool/tarantool/issues/3536

 src/box/sql/build.c        |  3 ++-
 test/sql/triggers.result   | 34 ++++++++++++++++++++++++++++++++++
 test/sql/triggers.test.lua | 14 ++++++++++++++
 3 files changed, 50 insertions(+), 1 deletion(-)

diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index cdf2bfc..dddeb12 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -290,7 +290,8 @@ table_column_is_in_pk(Table *table, uint32_t column)
 	struct space *space = space_by_id(table->def->id);
 	assert(space != NULL);
 
-	struct index *primary_idx = index_find(space, 0 /* PK */);
+	/* Primary key always has index 0. */
+	struct index *primary_idx = space_index(space, 0);
 	/* Views don't have any indexes. */
 	if (primary_idx == NULL)
 		return false;
diff --git a/test/sql/triggers.result b/test/sql/triggers.result
index 658571b..556e931 100644
--- a/test/sql/triggers.result
+++ b/test/sql/triggers.result
@@ -356,3 +356,37 @@ box.sql.execute("DROP TABLE test;")
 box.sql.execute("DROP TABLE test2;")
 ---
 ...
+--
+-- gh-3536: Some triggers cause error messages and/or half-finished updates
+--
+box.cfg{}
+---
+...
+box.sql.execute("CREATE TABLE t (s1 INT, s2 INT, s3 INT, s4 INT PRIMARY KEY);")
+---
+...
+box.sql.execute("CREATE VIEW v AS SELECT s1, s2 FROM t;")
+---
+...
+box.sql.execute("CREATE TRIGGER tv INSTEAD OF UPDATE ON v BEGIN UPDATE t SET s3 = new.s1 WHERE s1 = old.s1; END;")
+---
+...
+box.sql.execute("INSERT INTO t VALUES (1,1,1,1);")
+---
+...
+box.sql.execute("UPDATE v SET s2 = s1 + 1;")
+---
+...
+box.sql.execute("UPDATE v SET s1 = s1 + 5;")
+---
+...
+box.sql.execute("SELECT * FROM t;")
+---
+- - [1, 1, 6, 1]
+...
+box.sql.execute("DROP VIEW v;")
+---
+...
+box.sql.execute("DROP TABLE t;")
+---
+...
diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua
index 8fd385c..fff58e9 100644
--- a/test/sql/triggers.test.lua
+++ b/test/sql/triggers.test.lua
@@ -142,3 +142,17 @@ box.sql.execute("SELECT * FROM test2")
 box.sql.execute("ROLLBACK;")
 box.sql.execute("DROP TABLE test;")
 box.sql.execute("DROP TABLE test2;")
+
+--
+-- gh-3536: Some triggers cause error messages and/or half-finished updates
+--
+box.cfg{}
+box.sql.execute("CREATE TABLE t (s1 INT, s2 INT, s3 INT, s4 INT PRIMARY KEY);")
+box.sql.execute("CREATE VIEW v AS SELECT s1, s2 FROM t;")
+box.sql.execute("CREATE TRIGGER tv INSTEAD OF UPDATE ON v BEGIN UPDATE t SET s3 = new.s1 WHERE s1 = old.s1; END;")
+box.sql.execute("INSERT INTO t VALUES (1,1,1,1);")
+box.sql.execute("UPDATE v SET s2 = s1 + 1;")
+box.sql.execute("UPDATE v SET s1 = s1 + 5;")
+box.sql.execute("SELECT * FROM t;")
+box.sql.execute("DROP VIEW v;")
+box.sql.execute("DROP TABLE t;")
-- 
2.7.4

[tarantool-patches] Re: [PATCH v1 1/1] sql: triggers on view unfinished updates

[n.pettik]

> Excepting primary index is a typical thing for view so

Nitpicking: not ‘excepting’ but lack of I guess.

> we should use space_index instead of index_find that doesn't
> setup diag error when no index is finded.
> 
> Closes #3536.
> ---
> diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua
> index 8fd385c..fff58e9 100644
> --- a/test/sql/triggers.test.lua
> +++ b/test/sql/triggers.test.lua
> @@ -142,3 +142,17 @@ box.sql.execute("SELECT * FROM test2")
> box.sql.execute("ROLLBACK;")
> box.sql.execute("DROP TABLE test;")
> box.sql.execute("DROP TABLE test2;")
> +
> +--
> +-- gh-3536: Some triggers cause error messages and/or half-finished updates
> +--
> +box.cfg{}

Why do you need to call box.cfg{} here?

[tarantool-patches] Re: [PATCH v1 1/1] sql: triggers on view unfinished updates

[Kirill Shcherbatov]

Hi! tnx!
> Nitpicking: not ‘excepting’ but lack of I guess.
ok.> Why do you need to call box.cfg{} here?.
ok.

[tarantool-patches] Re: [PATCH v1 1/1] sql: triggers on view unfinished updates

[Vladislav Shpilevoy]

On 16/08/2018 16:26, Kirill Shcherbatov wrote:
> Excepting primary index is a typical thing for view so
> we should use space_index instead of index_find that doesn't
> setup diag error when no index is finded.

No such word: 'finded'.

> 
> Closes #3536.
> ---
> Branch: https://github.com/tarantool/tarantool/tree/kshch/gh-3536-failed-view-insertion
> Issue: https://github.com/tarantool/tarantool/issues/3536
> 
>   src/box/sql/build.c        |  3 ++-
>   test/sql/triggers.result   | 34 ++++++++++++++++++++++++++++++++++
>   test/sql/triggers.test.lua | 14 ++++++++++++++
>   3 files changed, 50 insertions(+), 1 deletion(-)
> 
> diff --git a/src/box/sql/build.c b/src/box/sql/build.c
> index cdf2bfc..dddeb12 100644
> --- a/src/box/sql/build.c
> +++ b/src/box/sql/build.c
> @@ -290,7 +290,8 @@ table_column_is_in_pk(Table *table, uint32_t column)
>   	struct space *space = space_by_id(table->def->id);
>   	assert(space != NULL);
>   
> -	struct index *primary_idx = index_find(space, 0 /* PK */);
> +	/* Primary key always has index 0. */
> +	struct index *primary_idx = space_index(space, 0);
>   	/* Views don't have any indexes. */
>   	if (primary_idx == NULL)
>   		return false;
> diff --git a/test/sql/triggers.result b/test/sql/triggers.result
> index 658571b..556e931 100644
> --- a/test/sql/triggers.result
> +++ b/test/sql/triggers.result
> @@ -356,3 +356,37 @@ box.sql.execute("DROP TABLE test;")
>   box.sql.execute("DROP TABLE test2;")
>   ---
>   ...
> +--
> +-- gh-3536: Some triggers cause error messages and/or half-finished updates
> +--
> +box.cfg{}
> +---
> +...
> +box.sql.execute("CREATE TABLE t (s1 INT, s2 INT, s3 INT, s4 INT PRIMARY KEY);")
> +---
> +...
> +box.sql.execute("CREATE VIEW v AS SELECT s1, s2 FROM t;")
> +---
> +...
> +box.sql.execute("CREATE TRIGGER tv INSTEAD OF UPDATE ON v BEGIN UPDATE t SET s3 = new.s1 WHERE s1 = old.s1; END;")
> +---
> +...
> +box.sql.execute("INSERT INTO t VALUES (1,1,1,1);")
> +---
> +...
> +box.sql.execute("UPDATE v SET s2 = s1 + 1;")
> +---
> +...
> +box.sql.execute("UPDATE v SET s1 = s1 + 5;")
> +---
> +...
> +box.sql.execute("SELECT * FROM t;")
> +---
> +- - [1, 1, 6, 1]
> +...
> +box.sql.execute("DROP VIEW v;")
> +---
> +...
> +box.sql.execute("DROP TABLE t;")
> +---
> +...
> diff --git a/test/sql/triggers.test.lua b/test/sql/triggers.test.lua
> index 8fd385c..fff58e9 100644
> --- a/test/sql/triggers.test.lua
> +++ b/test/sql/triggers.test.lua
> @@ -142,3 +142,17 @@ box.sql.execute("SELECT * FROM test2")
>   box.sql.execute("ROLLBACK;")
>   box.sql.execute("DROP TABLE test;")
>   box.sql.execute("DROP TABLE test2;")
> +
> +--
> +-- gh-3536: Some triggers cause error messages and/or half-finished updates
> +--
> +box.cfg{}
> +box.sql.execute("CREATE TABLE t (s1 INT, s2 INT, s3 INT, s4 INT PRIMARY KEY);")
> +box.sql.execute("CREATE VIEW v AS SELECT s1, s2 FROM t;")
> +box.sql.execute("CREATE TRIGGER tv INSTEAD OF UPDATE ON v BEGIN UPDATE t SET s3 = new.s1 WHERE s1 = old.s1; END;")
> +box.sql.execute("INSERT INTO t VALUES (1,1,1,1);")
> +box.sql.execute("UPDATE v SET s2 = s1 + 1;")
> +box.sql.execute("UPDATE v SET s1 = s1 + 5;")
> +box.sql.execute("SELECT * FROM t;")
> +box.sql.execute("DROP VIEW v;")
> +box.sql.execute("DROP TABLE t;")
> 

[tarantool-patches] Re: [PATCH v1 1/1] sql: triggers on view unfinished updates

[Kirill Shcherbatov]

> No such word: 'finded'.
Ok... fixed.

[tarantool-patches] Re: [PATCH v1 1/1] sql: triggers on view unfinished updates

[n.pettik]

Now LGTM.

[tarantool-patches] Re: [PATCH v1 1/1] sql: triggers on view unfinished updates

[Kirill Yukhin]

Hello,
On 16 авг 16:26, Kirill Shcherbatov wrote:
> Excepting primary index is a typical thing for view so
> we should use space_index instead of index_find that doesn't
> setup diag error when no index is finded.
> 
> Closes #3536.
> ---
> Branch: https://github.com/tarantool/tarantool/tree/kshch/gh-3536-failed-view-insertion
> Issue: https://github.com/tarantool/tarantool/issues/3536
I've checked in the patch into 2.0 branch.

--
Regards, Kirill Yukhin