[tarantool-patches] [PATCH v2 2/3] sql: disallow ck using non-persistent function
Kirill Shcherbatov
kshcherbatov at tarantool.org
Thu Sep 12 11:06:42 MSK 2019
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
More information about the Tarantool-patches
mailing list