[tarantool-patches] [PATCH] sql: prohibit duplication of FK action clause

[Kirill Yukhin <kyukhin@tarantool.org>]

The patch prohibits duplication of action clause for foreign
key constraints. It is now syntax error to specify, say,
ON UPDATE clause for the same FK twice.
The patch also remove ON INSERT caluse at all, since it
ultimately is no-op and leads to nothing but confusion.

Closes #3475
---
https://github.com/tarantool/tarantool/issues/3475
https://github.com/tarantool/tarantool/commits/kyukhin/gh-3475-fk-duplicate-action-clause

 src/box/sql/parse.y                           | 10 +++++++--
 test/sql/gh-3475-fk-duplicate-clause.result   | 32 +++++++++++++++++++++++++++
 test/sql/gh-3475-fk-duplicate-clause.test.lua | 13 +++++++++++
 3 files changed, 53 insertions(+), 2 deletions(-)
 create mode 100644 test/sql/gh-3475-fk-duplicate-clause.result
 create mode 100644 test/sql/gh-3475-fk-duplicate-clause.test.lua

diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 32ef685..7938afd 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -283,10 +283,16 @@ autoinc(X) ::= AUTOINCR.  {X = 1;}
 //
 %type refargs {int}
 refargs(A) ::= .                  { A = FKEY_NO_ACTION; }
-refargs(A) ::= refargs(A) refarg(Y). { A = (A & ~Y.mask) | Y.value; }
+refargs(A) ::= refargs(A) refarg(Y). { if ((A & Y.mask) == 0) {
+                                           A = (A & ~Y.mask) | Y.value;
+                                       } else {
+                                           sqlite3ErrorMsg(pParse,
+                                                           "near \"%T\": duplicate FK action clause.",
+                                                           &pParse->sLastToken);
+                                       }
+                                     }
 %type refarg {struct {int value; int mask;}}
 refarg(A) ::= MATCH matcharg(X).     { A.value = X<<16; A.mask = 0xff0000; }
-refarg(A) ::= ON INSERT refact.      { A.value = 0;     A.mask = 0x000000; }
 refarg(A) ::= ON DELETE refact(X).   { A.value = X;     A.mask = 0x0000ff; }
 refarg(A) ::= ON UPDATE refact(X).   { A.value = X<<8;  A.mask = 0x00ff00; }
 %type matcharg {int}
diff --git a/test/sql/gh-3475-fk-duplicate-clause.result b/test/sql/gh-3475-fk-duplicate-clause.result
new file mode 100644
index 0000000..7bcd62b
--- /dev/null
+++ b/test/sql/gh-3475-fk-duplicate-clause.result
@@ -0,0 +1,32 @@
+test_run = require('test_run').new()
+---
+...
+engine = test_run:get_cfg('engine')
+---
+...
+box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+---
+...
+box.cfg{}
+---
+...
+box.sql.execute("CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE)")
+---
+...
+box.sql.execute("CREATE TABLE t2(a INT PRIMARY KEY, b INT REFERENCES t1(b) ON UPDATE CASCADE ON UPDATE CASCADE)")
+---
+- error: 'near ")": duplicate FK action clause.'
+...
+box.sql.execute("CREATE TABLE t2(a INT PRIMARY KEY, b INT REFERENCES t1(b) ON DELETE CASCADE ON DELETE CASCADE)")
+---
+- error: 'near ")": duplicate FK action clause.'
+...
+box.sql.execute("CREATE TABLE t2(a INT PRIMARY KEY, b INT REFERENCES t1(b) ON DELETE CASCADE ON UPDATE CASCADE)")
+---
+...
+box.sql.execute("DROP TABLE t2")
+---
+...
+box.sql.execute("DROP TABLE t1")
+---
+...
diff --git a/test/sql/gh-3475-fk-duplicate-clause.test.lua b/test/sql/gh-3475-fk-duplicate-clause.test.lua
new file mode 100644
index 0000000..447c104
--- /dev/null
+++ b/test/sql/gh-3475-fk-duplicate-clause.test.lua
@@ -0,0 +1,13 @@
+test_run = require('test_run').new()
+engine = test_run:get_cfg('engine')
+box.sql.execute('pragma sql_default_engine=\''..engine..'\'')
+
+box.cfg{}
+
+box.sql.execute("CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE)")
+box.sql.execute("CREATE TABLE t2(a INT PRIMARY KEY, b INT REFERENCES t1(b) ON UPDATE CASCADE ON UPDATE CASCADE)")
+box.sql.execute("CREATE TABLE t2(a INT PRIMARY KEY, b INT REFERENCES t1(b) ON DELETE CASCADE ON DELETE CASCADE)")
+box.sql.execute("CREATE TABLE t2(a INT PRIMARY KEY, b INT REFERENCES t1(b) ON DELETE CASCADE ON UPDATE CASCADE)")
+
+box.sql.execute("DROP TABLE t2")
+box.sql.execute("DROP TABLE t1")
-- 
2.11.0