[tarantool-patches] [PATCH v2 2/3] sql: disallow ck using non-persistent function

[Kirill Shcherbatov <kshcherbatov@tarantool.org>]

Each CK constraint object is a part of the database schema and
is restored during recovery. It is not possible if a CK
constraint uses some user-defined function inside. Thus we should
disallow non-persistent functions participate in ck constraints.

@TarantoolBot document
Title: disallow ck constraint using non-persistent function

Now CK constraints may use only persistent function and
predefined SQL built-in functions. In case of invalid definition
the error would be raised:

function myfunc(x) return x < 10 end
box.schema.func.create("MYFUNC", {exports = {'LUA', 'SQL'},
				  param_list = {'integer'}})
box.execute("CREATE TABLE t6(a  INT CHECK (myfunc(a)) primary key);");
---
- null
- 'Failed to create check constraint ''ck_unnamed_T6_1'': ck constraint
  could not
  use non-persistent function ''MYFUNC'''
---
 src/box/sql/resolve.c    | 10 ++++++++++
 test/sql/checks.result   | 43 ++++++++++++++++++++++++++++++++++++++--
 test/sql/checks.test.lua | 21 +++++++++++++++++++-
 3 files changed, 71 insertions(+), 3 deletions(-)

diff --git a/src/box/sql/resolve.c b/src/box/sql/resolve.c
index 6f625dc18..0d6f146fb 100644
--- a/src/box/sql/resolve.c
+++ b/src/box/sql/resolve.c
@@ -653,6 +653,16 @@ resolveExprStep(Walker * pWalker, Expr * pExpr)
 				pExpr->iTable = func->def->name[0] == 'u' ?
 						8388608 : 125829120;
 			}
+			if ((pNC->ncFlags & NC_IsCheck) != 0 &&
+			    func->def->body == NULL &&
+			    func->def->language != FUNC_LANGUAGE_SQL_BUILTIN) {
+				diag_set(ClientError, ER_SQL_PARSER_GENERIC,
+					 "Check constraint can not invoke "
+					 "non-persistent function");
+				pParse->is_aborted = true;
+				pNC->nErr++;
+				return WRC_Abort;
+			}
 			assert(!func->def->is_deterministic ||
 			       (pNC->ncFlags & NC_IdxExpr) == 0);
 			if (func->def->is_deterministic)
diff --git a/test/sql/checks.result b/test/sql/checks.result
index 3f121226b..7939d46df 100644
--- a/test/sql/checks.result
+++ b/test/sql/checks.result
@@ -830,9 +830,48 @@ test_run:cmd('switch default')
 ---
 - true
 ...
-test_run:cmd('stop server test')
+--
+-- gh-4176: Can't recover if check constraint involves function.
+-- Make sure that non-persistent functions can't participate in
+-- check constraints, since after instance reboot they disappear
+-- and check constraint can't be created.
+--
+function myfunc(x) return x < 10 end
+---
+...
+box.schema.func.create("MYFUNC", {exports = {'LUA', 'SQL'}, param_list = {'integer'}})
+---
+...
+box.execute("CREATE TABLE t6(a  INT CHECK (myfunc(a)) primary key);");
+---
+- null
+- 'Failed to create check constraint ''ck_unnamed_T6_1'': Check constraint can not
+  invoke non-persistent function'
+...
+box.func.MYFUNC:drop()
+---
+...
+box.schema.func.create("MYFUNC", {exports = {'LUA', 'SQL'}, param_list = {'integer'}, body = "function(x) return x < 10 end"})
+---
+...
+box.execute("CREATE TABLE t6(a  INT CHECK (myfunc(a)) primary key);");
+---
+- row_count: 1
+...
+box.space.T6:insert({11})
+---
+- error: 'Check constraint failed ''ck_unnamed_T6_1'': myfunc(a)'
+...
+test_run:cmd("restart server default")
+box.space.T6:insert({11})
+---
+- error: 'Check constraint failed ''ck_unnamed_T6_1'': myfunc(a)'
+...
+box.space.T6:drop()
+---
+...
+box.func.MYFUNC:drop()
 ---
-- true
 ...
 test_run:cmd("clear filter")
 ---
diff --git a/test/sql/checks.test.lua b/test/sql/checks.test.lua
index 9716647d0..051c9ae38 100644
--- a/test/sql/checks.test.lua
+++ b/test/sql/checks.test.lua
@@ -273,6 +273,25 @@ box.execute("DROP TABLE test;")
 
 test_run = require('test_run').new()
 test_run:cmd('switch default')
-test_run:cmd('stop server test')
+
+--
+-- gh-4176: Can't recover if check constraint involves function.
+-- Make sure that non-persistent functions can't participate in
+-- check constraints, since after instance reboot they disappear
+-- and check constraint can't be created.
+--
+function myfunc(x) return x < 10 end
+box.schema.func.create("MYFUNC", {exports = {'LUA', 'SQL'}, param_list = {'integer'}})
+box.execute("CREATE TABLE t6(a  INT CHECK (myfunc(a)) primary key);");
+box.func.MYFUNC:drop()
+
+box.schema.func.create("MYFUNC", {exports = {'LUA', 'SQL'}, param_list = {'integer'}, body = "function(x) return x < 10 end"})
+box.execute("CREATE TABLE t6(a  INT CHECK (myfunc(a)) primary key);");
+box.space.T6:insert({11})
+test_run:cmd("restart server default")
+box.space.T6:insert({11})
+
+box.space.T6:drop()
+box.func.MYFUNC:drop()
 
 test_run:cmd("clear filter")
-- 
2.23.0