From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2F07726992 for ; Thu, 8 Aug 2019 10:51:04 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id r_XLWC40-JKX for ; Thu, 8 Aug 2019 10:51:04 -0400 (EDT) Received: from smtp5.mail.ru (smtp5.mail.ru [94.100.179.24]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 9E6BE26A61 for ; Thu, 8 Aug 2019 10:51:03 -0400 (EDT) From: Kirill Shcherbatov Subject: [tarantool-patches] [PATCH v2 8/8] box: get rid of box.internal.sql_function_create Date: Thu, 8 Aug 2019 17:50:52 +0300 Message-Id: <8c42515c61f85e03dcd5abe35055b9301379de0a.1565275470.git.kshcherbatov@tarantool.org> In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org, korablev@tarantool.org Cc: Kirill Shcherbatov Closes #2200 Closes #4113 Closes #2233 @TarantoolBot document Title: The box.internal.sql_function_create is forbidden Legacy mechanism box.internal.sql_function_create to make some Lua function available in SQL is forbidden now. To make some function available in SQL you need to use box.schema.func.create() mechanism: you need to specify 1) function language and language-specific options(e.g. you are able to define a persistent Lua function) 2) whether this function is_deterministic or not: deterministic functions allows to generate more efficient SQL VDBE bytecode so you better specify it when it is true 3) the function returns type: a Tarantool type string describes a type of value returned by function 4) param_list - a table of Tarantool's types strings desccribe function argument types 5) exports - a table of Tarantool's frontends where this function should be available ('LUA' by default). You need to specify {'LUA', 'SQL'} to make function available both in SQL requests and visible in box.func folder Example: -- Case1: C function -- function1.so has int divide() symbol box.schema.func.create("function1.divide", {language = 'C', returns = 'number', param_list = {'number', 'number'}, is_deterministic = true, exports = {'LUA', 'SQL'}}) box.execute('SELECT "function1.divide"(6, 3)') - metadata: - name: '"function1.divide"(6, 3)' type: number rows: - [2] box.schema.func.drop("function1.divide") -- Case2: Persistent Lua function box.schema.func.create("SUMMARIZE", {language = 'LUA', returns = 'number', body = 'function (a, b) return a + b end', param_list = {'number', 'number'}, is_deterministic = true, exports = {'LUA', 'SQL'}}) box.execute('SELECT summarize(1, 2)') - metadata: - name: summarize(1, 2) type: number rows: - [3] box.schema.func.drop("summarize") Moreover there is a special predefined Lua function LUA that allows to evaluate a custom Lua expressions in SQL. You need to pass a string in form "return ...." to LUA function that returns more than one value of any type. Example: box.execute('SELECT lua(\'return 1 + 1\')') - metadata: - name: lua('return 1 + 1') type: any rows: - [2] box.execute('SELECT lua(\'return box.cfg.memtx_memory\')') - metadata: - name: lua('return box.cfg.memtx_memory') type: any rows: - [268435456] --- src/box/func_def.h | 2 +- src/box/port.h | 17 ++ src/lib/core/port.h | 19 +++ src/box/call.c | 1 + src/box/execute.c | 1 + src/box/lua/call.c | 4 + src/box/port.c | 4 + src/box/sql.c | 3 +- src/box/sql/expr.c | 6 +- src/box/sql/func.c | 277 ++++++++++++++++++++++++++++++++ src/box/sql/vdbe.c | 45 ++++++ src/box/lua/schema.lua | 3 +- test/box/function1.result | 171 ++++++++++++++++++++ test/box/function1.test.lua | 60 +++++++ test/sql-tap/alias.test.lua | 11 +- test/sql-tap/check.test.lua | 11 +- test/sql-tap/func5.test.lua | 27 +++- test/sql-tap/lua_sql.test.lua | 121 +++++++------- test/sql-tap/subquery.test.lua | 21 ++- test/sql-tap/trigger9.test.lua | 8 +- test/sql/errinj.result | 26 --- test/sql/errinj.test.lua | 10 -- test/sql/func-recreate.result | 41 ++++- test/sql/func-recreate.test.lua | 26 ++- 24 files changed, 783 insertions(+), 132 deletions(-) diff --git a/src/box/func_def.h b/src/box/func_def.h index d99d89190..988d080f0 100644 --- a/src/box/func_def.h +++ b/src/box/func_def.h @@ -110,7 +110,7 @@ struct func_def { */ bool is_sandboxed; /** The count of function's input arguments. */ - int param_count; + uint32_t param_count; /** The type of the value returned by function. */ enum field_type returns; /** Function aggregate option. */ diff --git a/src/box/port.h b/src/box/port.h index a7f5d81bd..26f85b145 100644 --- a/src/box/port.h +++ b/src/box/port.h @@ -113,6 +113,23 @@ static_assert(sizeof(struct port_lua) <= sizeof(struct port), void port_lua_create(struct port *port, struct lua_State *L); +struct sql_value; +struct sql_context; + +/** Port implementation used with vdbe memory variables. */ +struct port_vdbemem { + const struct port_vtab *vtab; + struct sql_value *mem; + uint32_t size; +}; + +static_assert(sizeof(struct port_vdbemem) <= sizeof(struct port), + "sizeof(struct port_vdbemem) must be <= sizeof(struct port)"); + +/** Initialize a port to dump data in sql vdbe memory. */ +void +port_vdbemem_create(struct port *base, struct sql_value *mem, uint32_t size); + void port_init(void); diff --git a/src/lib/core/port.h b/src/lib/core/port.h index 09a026df5..17096d0d0 100644 --- a/src/lib/core/port.h +++ b/src/lib/core/port.h @@ -98,6 +98,19 @@ struct port_vtab { * is responsible for cleaning up. **/ const char *(*get_msgpack)(struct port *port, uint32_t *size); + /** + * Get the content of a port as a sequence of vdbe memory + * variables. The SQL VDBE uses this representation for + * process data. This API is usefull to pass VDBE + * variables to sql builtin functions. + * The lifecycle of the returned value is + * implementation-specific: it may either be returned + * directly from the port, in which case the data will + * stay alive as long as the port is alive, or it may be + * allocated on the fiber()->gc, in which case the caller + * is responsible for cleaning up. + */ + struct sql_value *(*get_vdbemem)(struct port *port, uint32_t *size); /** Destroy a port and release associated resources. */ void (*destroy)(struct port *port); }; @@ -150,6 +163,12 @@ port_get_msgpack(struct port *port, uint32_t *size) return port->vtab->get_msgpack(port, size); } +static inline struct sql_value * +port_get_vdbemem(struct port *port, uint32_t *size) +{ + return port->vtab->get_vdbemem(port, size); +} + #if defined(__cplusplus) } /* extern "C" */ #endif /* defined __cplusplus */ diff --git a/src/box/call.c b/src/box/call.c index ac2bf3004..b459a9839 100644 --- a/src/box/call.c +++ b/src/box/call.c @@ -73,6 +73,7 @@ static const struct port_vtab port_msgpack_vtab = { .dump_lua = port_msgpack_dump_lua, .dump_plain = NULL, .get_msgpack = port_msgpack_get_msgpack, + .get_vdbemem = NULL, .destroy = NULL, }; diff --git a/src/box/execute.c b/src/box/execute.c index 68e94e442..e8b012e5b 100644 --- a/src/box/execute.c +++ b/src/box/execute.c @@ -109,6 +109,7 @@ const struct port_vtab port_sql_vtab = { /* .dump_lua = */ port_sql_dump_lua, /* .dump_plain = */ NULL, /* .get_msgpack = */ NULL, + /* .get_vdbemem = */ NULL, /* .destroy = */ port_sql_destroy, }; diff --git a/src/box/lua/call.c b/src/box/lua/call.c index 001578b5a..631003c84 100644 --- a/src/box/lua/call.c +++ b/src/box/lua/call.c @@ -510,12 +510,16 @@ port_lua_destroy(struct port *base) extern const char * port_lua_dump_plain(struct port *port, uint32_t *size); +extern struct sql_value * +port_lua_get_vdbemem(struct port *base, uint32_t *size); + static const struct port_vtab port_lua_vtab = { .dump_msgpack = port_lua_dump, .dump_msgpack_16 = port_lua_dump_16, .dump_lua = port_lua_dump_lua, .dump_plain = port_lua_dump_plain, .get_msgpack = port_lua_get_msgpack, + .get_vdbemem = port_lua_get_vdbemem, .destroy = port_lua_destroy, }; diff --git a/src/box/port.c b/src/box/port.c index 7f552bcfe..6e2fe3a6e 100644 --- a/src/box/port.c +++ b/src/box/port.c @@ -140,11 +140,15 @@ port_free(void) mempool_destroy(&port_tuple_entry_pool); } +extern struct sql_value * +port_tuple_get_vdbemem(struct port *base, uint32_t *size); + const struct port_vtab port_tuple_vtab = { .dump_msgpack = port_tuple_dump_msgpack, .dump_msgpack_16 = port_tuple_dump_msgpack_16, .dump_lua = port_tuple_dump_lua, .dump_plain = NULL, .get_msgpack = NULL, + .get_vdbemem = port_tuple_get_vdbemem, .destroy = port_tuple_destroy, }; diff --git a/src/box/sql.c b/src/box/sql.c index a731332c7..bbc957736 100644 --- a/src/box/sql.c +++ b/src/box/sql.c @@ -1270,7 +1270,8 @@ sql_func_by_signature(const char *name, uint32_t argc) if (func == NULL || !func->def->exports.sql) return NULL; if (func->def->language != FUNC_LANGUAGE_SQL_BUILTIN) { - return NULL; + if (func->def->param_count != argc) + return NULL; } else { /* * The param_count field is not valid for sql diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 64b3bc835..1aaab3b64 100644 --- a/src/box/sql/expr.c +++ b/src/box/sql/expr.c @@ -4122,9 +4122,9 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target) sqlVdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)coll, P4_COLLSEQ); } - assert(func->def->language == - FUNC_LANGUAGE_SQL_BUILTIN); - int op = OP_BuiltinFunction0; + int op = func->def->language == + FUNC_LANGUAGE_SQL_BUILTIN ? + OP_BuiltinFunction0 : OP_Function; sqlVdbeAddOp4(v, op, constMask, r1, target, (char *)func, P4_FUNC); sqlVdbeChangeP5(v, (u8) nFarg); diff --git a/src/box/sql/func.c b/src/box/sql/func.c index f07c52b95..ea94d6151 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -45,6 +45,10 @@ #include #include #include "box/coll_id_cache.h" +#include "box/port.h" +#include "box/tuple.h" +#include "lua/utils.h" +#include "mpstream.h" /* * Return the collating function associated with a function. @@ -70,6 +74,279 @@ sqlSkipAccumulatorLoad(sql_context * context) context->skipFlag = 1; } +/** + * Allocate a sequence of initialized vdbe memory registers + * on region. + */ +static struct Mem * +vdbemem_alloc_on_region(uint32_t count) +{ + struct region *region = &fiber()->gc; + struct Mem *ret = region_alloc(region, count * sizeof(*ret)); + if (ret == NULL) { + diag_set(OutOfMemory, count * sizeof(*ret), + "region_alloc", "ret"); + return NULL; + } + memset(ret, 0, count * sizeof(*ret)); + for (uint32_t i = 0; i < count; i++) { + sqlVdbeMemInit(&ret[i], sql_get(), MEM_Null); + assert(memIsValid(&ret[i])); + } + return ret; +} + +static void +port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat) +{ + (void) is_flat; + struct port_vdbemem *port = (struct port_vdbemem *) base; + assert(is_flat == true); + for (uint32_t i = 0; i < port->size; i++) { + sql_value *param = + (sql_value *)((struct Mem *)port->mem + i); + switch (sql_value_type(param)) { + case MP_INT: + luaL_pushint64(L, sql_value_int64(param)); + break; + case MP_UINT: + luaL_pushuint64(L, sql_value_uint64(param)); + break; + case MP_DOUBLE: + lua_pushnumber(L, sql_value_double(param)); + break; + case MP_STR: + lua_pushstring(L, (const char *) sql_value_text(param)); + break; + case MP_BIN: + lua_pushlstring(L, sql_value_blob(param), + (size_t) sql_value_bytes(param)); + break; + case MP_NIL: + lua_pushnil(L); + break; + case MP_BOOL: + lua_pushboolean(L, sql_value_boolean(param)); + break; + default: + unreachable(); + } + } +} + +static const char * +port_vdbemem_get_msgpack(struct port *base, uint32_t *size) +{ + struct port_vdbemem *port = (struct port_vdbemem *) base; + struct region *region = &fiber()->gc; + size_t region_svp = region_used(region); + bool is_error = false; + struct mpstream stream; + mpstream_init(&stream, region, region_reserve_cb, region_alloc_cb, + set_encode_error, &is_error); + mpstream_encode_array(&stream, port->size); + for (uint32_t i = 0; i < port->size && !is_error; i++) { + sql_value *param = + (sql_value *)((struct Mem *)port->mem + i); + switch (sql_value_type(param)) { + case MP_INT: { + sql_int64 val = sql_value_int64(param); + if (val < 0) { + mpstream_encode_int(&stream, val); + break; + } + FALLTHROUGH; + } + case MP_UINT: { + sql_int64 val = sql_value_int64(param); + mpstream_encode_uint(&stream, val); + break; + } + case MP_DOUBLE: { + mpstream_encode_double(&stream, + sql_value_double(param)); + break; + } + case MP_STR: { + mpstream_encode_str(&stream, + (const char *) sql_value_text(param)); + break; + } + case MP_BIN: { + mpstream_encode_binl(&stream, sql_value_bytes(param)); + mpstream_memcpy(&stream, sql_value_blob(param), + sql_value_bytes(param)); + break; + } + case MP_NIL: { + mpstream_encode_nil(&stream); + break; + } + case MP_BOOL: { + mpstream_encode_bool(&stream, sql_value_boolean(param)); + break; + } + default: + unreachable(); + } + } + mpstream_flush(&stream); + *size = region_used(region) - region_svp; + if (is_error) + goto error; + const char *ret = (char *)region_join(region, *size); + if (ret == NULL) + goto error; + return ret; +error: + diag_set(OutOfMemory, *size, "region", "ret"); + return NULL; +} + +static const struct port_vtab port_vdbemem_vtab; + +void +port_vdbemem_create(struct port *base, struct sql_value *mem, uint32_t size) +{ + struct port_vdbemem *port = (struct port_vdbemem *) base; + port->vtab = &port_vdbemem_vtab; + port->mem = mem; + port->size = size; +} + +static struct sql_value * +port_vdbemem_get_vdbemem(struct port *base, uint32_t *size) +{ + struct port_vdbemem *port = (struct port_vdbemem *) base; + assert(port->vtab == &port_vdbemem_vtab); + *size = port->size; + return port->mem; +} + +static const struct port_vtab port_vdbemem_vtab = { + .dump_msgpack = NULL, + .dump_msgpack_16 = NULL, + .dump_lua = port_vdbemem_dump_lua, + .dump_plain = NULL, + .get_msgpack = port_vdbemem_get_msgpack, + .get_vdbemem = port_vdbemem_get_vdbemem, + .destroy = NULL, +}; + +struct sql_value * +port_lua_get_vdbemem(struct port *base, uint32_t *size) +{ + struct port_lua *port = (struct port_lua *) base; + struct lua_State *L = port->L; + int argc = lua_gettop(L); + if (argc == 0) { + diag_set(ClientError, ER_SQL_EXECUTE, + "No value was passed from Lua"); + return NULL; + } + *size = argc; + struct region *region = &fiber()->gc; + size_t region_svp = region_used(region); + struct Mem *val = vdbemem_alloc_on_region(argc); + if (val == NULL) + return NULL; + for (int i = 0; i < argc; i++) { + switch(lua_type(L, -1 - i)) { + case LUA_TBOOLEAN: + mem_set_bool(&val[i], lua_toboolean(L, -1 - i)); + break; + case LUA_TNUMBER: + sqlVdbeMemSetDouble(&val[i], lua_tonumber(L, -1 - i)); + break; + case LUA_TSTRING: + if (sqlVdbeMemSetStr(&val[i], lua_tostring(L, -1 - i), + -1, 1, SQL_TRANSIENT) != 0) + goto error; + break; + case LUA_TNIL: + sqlVdbeMemSetNull(&val[i]); + break; + default: + diag_set(ClientError, ER_SQL_EXECUTE, + "Unsupported type passed from Lua"); + goto error; + } + } + return (struct sql_value *)val; +error: + for (int i = 0; i < argc; i++) + sqlVdbeMemRelease(&val[i]); + region_truncate(region, region_svp); + return NULL; +} + +struct sql_value * +port_tuple_get_vdbemem(struct port *base, uint32_t *size) +{ + struct port_tuple *port = (struct port_tuple *)base; + *size = port->size; + if (*size == 0) { + diag_set(ClientError, ER_SQL_EXECUTE, + "No value was passed from C"); + return NULL; + } + struct region *region = &fiber()->gc; + size_t region_svp = region_used(region); + struct Mem *val = vdbemem_alloc_on_region(port->size); + if (val == NULL) + return NULL; + int i = 0; + struct port_tuple_entry *pe; + for (pe = port->first; pe != NULL; pe = pe->next) { + const char *data = tuple_data(pe->tuple); + if (mp_typeof(*data) != MP_ARRAY || + mp_decode_array(&data) != 1) { + diag_set(ClientError, ER_SQL_EXECUTE, + "Unsupported type passed from C"); + goto error; + } + uint32_t len; + const char *str; + switch (mp_typeof(*data)) { + case MP_BOOL: + mem_set_bool(&val[i], mp_decode_bool(&data)); + break; + case MP_FLOAT: + sqlVdbeMemSetDouble(&val[i], mp_decode_float(&data)); + break; + case MP_DOUBLE: + sqlVdbeMemSetDouble(&val[i], mp_decode_double(&data)); + break; + case MP_INT: + mem_set_i64(val, mp_decode_int(&data)); + break; + case MP_UINT: + mem_set_u64(val, mp_decode_uint(&data)); + break; + case MP_STR: + str = mp_decode_str(&data, &len); + if (sqlVdbeMemSetStr(&val[i], str, len, + 1, SQL_TRANSIENT) != 0) + goto error; + break; + case MP_NIL: + sqlVdbeMemSetNull(val); + break; + default: + diag_set(ClientError, ER_SQL_EXECUTE, + "Unsupported type passed from C"); + goto error; + } + i++; + } + return (struct sql_value *) val; +error: + for (int i = 0; i < port->size; i++) + sqlVdbeMemRelease(&val[i]); + region_truncate(region, region_svp); + return NULL; +} + /* * Implementation of the non-aggregate min() and max() functions */ diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index a66becc89..23b6995d7 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -46,6 +46,7 @@ #include "box/fk_constraint.h" #include "box/txn.h" #include "box/tuple.h" +#include "box/port.h" #include "sqlInt.h" #include "vdbeInt.h" #include "tarantoolInt.h" @@ -1767,6 +1768,50 @@ case OP_BuiltinFunction: { break; } +/* Opcode: Function P1 P2 P3 P4 P5 + * Synopsis: r[P3]=func(r[P2@P5]) + * + * Invoke a user function (P4 is a pointer to a function object + * that defines the function) with P5 arguments taken from + * register P2 and successors. The result of the function is + * stored in register P3. + * Register P3 must not be one of the function inputs. + * + * P1 is a 32-bit bitmask indicating whether or not each argument + * to the function was determined to be constant at compile time. + * If the first argument was constant then bit 0 of P1 is set. + */ +case OP_Function: { + struct func *func = pOp->p4.func; + int argc = pOp->p5; + struct Mem *argv = &aMem[pOp->p2]; + struct port args, ret; + + struct region *region = &fiber()->gc; + size_t region_svp = region_used(region); + port_vdbemem_create(&args, (struct sql_value *)argv, argc); + if (func_call(func, &args, &ret) != 0) + goto abort_due_to_error; + + pOut = vdbe_prepare_null_out(p, pOp->p3); + uint32_t size; + struct Mem *mem = (struct Mem *)port_get_vdbemem(&ret, &size); + if (mem != NULL && size > 0) + *pOut = mem[0]; + port_destroy(&ret); + region_truncate(region, region_svp); + if (mem == NULL) + goto abort_due_to_error; + + /* Copy the result of the function into register P3 */ + if (pOut->flags & (MEM_Str|MEM_Blob)) + if (sqlVdbeMemTooBig(pOut)) goto too_big; + + REGISTER_TRACE(p, pOp->p3, pOut); + UPDATE_MAX_BLOBSIZE(pOut); + break; +} + /* Opcode: BitAnd P1 P2 P3 * * * Synopsis: r[P3]=r[P1]&r[P2] * diff --git a/src/box/lua/schema.lua b/src/box/lua/schema.lua index 65017d391..98067f795 100644 --- a/src/box/lua/schema.lua +++ b/src/box/lua/schema.lua @@ -2143,7 +2143,8 @@ box.schema.func.create = function(name, opts) language = 'string', body = 'string', is_deterministic = 'boolean', is_sandboxed = 'boolean', comment = 'string', - opts = 'table' }) + param_list = 'table', returns = 'string', + exports = 'table', opts = 'table' }) local _func = box.space[box.schema.FUNC_ID] local _vfunc = box.space[box.schema.VFUNC_ID] local func = _vfunc.index.name:get{name} diff --git a/test/box/function1.result b/test/box/function1.result index 5b091f72b..48c9ad6cf 100644 --- a/test/box/function1.result +++ b/test/box/function1.result @@ -366,6 +366,177 @@ test_run:cmd("setopt delimiter ''"); c:close() --- ... +-- +-- gh-2233: Invoke Lua functions created outside SQL. +-- +test_run:cmd("setopt delimiter ';'") +--- +- true +... +box.schema.func.create("function1.divide", {language = 'C', returns = 'number', + is_deterministic = true, + exports = {'LUA'}}) +test_run:cmd("setopt delimiter ''"); +--- +... +box.execute('SELECT "function1.divide"()') +--- +- null +- Function 'function1.divide' does not exist +... +box.func["function1.divide"]:drop() +--- +... +test_run:cmd("setopt delimiter ';'") +--- +- true +... +box.schema.func.create("function1.divide", {language = 'C', returns = 'number', + is_deterministic = true, + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); +--- +... +box.execute('SELECT "function1.divide"()') +--- +- null +- invalid argument +... +box.execute('SELECT "function1.divide"(6)') +--- +- null +- wrong number of arguments to function function1.divide() +... +box.execute('SELECT "function1.divide"(6, 3)') +--- +- null +- wrong number of arguments to function function1.divide() +... +box.func["function1.divide"]:drop() +--- +... +test_run:cmd("setopt delimiter ';'") +--- +- true +... +box.schema.func.create("function1.divide", {language = 'C', returns = 'number', + param_list = {'number', 'number'}, + is_deterministic = true, + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); +--- +... +box.execute('SELECT "function1.divide"()') +--- +- null +- wrong number of arguments to function function1.divide() +... +box.execute('SELECT "function1.divide"(6)') +--- +- null +- wrong number of arguments to function function1.divide() +... +box.execute('SELECT "function1.divide"(6, 3, 3)') +--- +- null +- wrong number of arguments to function function1.divide() +... +box.execute('SELECT "function1.divide"(6, 3)') +--- +- metadata: + - name: '"function1.divide"(6, 3)' + type: number + rows: + - [2] +... +box.execute('SELECT "function1.divide"(5, 2)') +--- +- metadata: + - name: '"function1.divide"(5, 2)' + type: number + rows: + - [2.5] +... +box.func["function1.divide"]:drop() +--- +... +function SUMMARIZE(a, b) return a + b end +--- +... +test_run:cmd("setopt delimiter ';'") +--- +- true +... +box.schema.func.create("SUMMARIZE", {language = 'LUA', returns = 'number', + is_deterministic = true, + param_list = {'number', 'number'}, + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); +--- +... +box.execute('SELECT summarize(1, 2)') +--- +- metadata: + - name: summarize(1, 2) + type: number + rows: + - [3] +... +box.func.SUMMARIZE:drop() +--- +... +test_run:cmd("setopt delimiter ';'") +--- +- true +... +box.schema.func.create("SUMMARIZE", {language = 'LUA', returns = 'number', + body = 'function (a, b) return a + b end', + is_deterministic = true, + param_list = {'number', 'number'}, + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); +--- +... +box.execute('SELECT summarize(1, 2)') +--- +- metadata: + - name: summarize(1, 2) + type: number + rows: + - [3] +... +box.func.SUMMARIZE:drop() +--- +... +-- +-- gh-4113: Valid method to use Lua from SQL +-- +box.execute('SELECT lua(\'return 1 + 1\')') +--- +- metadata: + - name: lua('return 1 + 1') + type: any + rows: + - [2] +... +box.execute('SELECT lua(\'return box.cfg\')') +--- +- null +- 'Failed to execute SQL statement: Unsupported type passed from Lua' +... +box.execute('SELECT lua(\'return box.cfg()\')') +--- +- null +- 'Failed to execute SQL statement: No value was passed from Lua' +... +box.execute('SELECT lua(\'return box.cfg.memtx_memory\')') +--- +- metadata: + - name: lua('return box.cfg.memtx_memory') + type: any + rows: + - [107374182] +... -- Test registered functions interface. function divide(a, b) return a / b end --- diff --git a/test/box/function1.test.lua b/test/box/function1.test.lua index f894472f8..5daa2ee1d 100644 --- a/test/box/function1.test.lua +++ b/test/box/function1.test.lua @@ -128,6 +128,66 @@ identifier.run_test( test_run:cmd("setopt delimiter ''"); c:close() +-- +-- gh-2233: Invoke Lua functions created outside SQL. +-- +test_run:cmd("setopt delimiter ';'") +box.schema.func.create("function1.divide", {language = 'C', returns = 'number', + is_deterministic = true, + exports = {'LUA'}}) +test_run:cmd("setopt delimiter ''"); +box.execute('SELECT "function1.divide"()') +box.func["function1.divide"]:drop() +test_run:cmd("setopt delimiter ';'") +box.schema.func.create("function1.divide", {language = 'C', returns = 'number', + is_deterministic = true, + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); +box.execute('SELECT "function1.divide"()') +box.execute('SELECT "function1.divide"(6)') +box.execute('SELECT "function1.divide"(6, 3)') +box.func["function1.divide"]:drop() +test_run:cmd("setopt delimiter ';'") +box.schema.func.create("function1.divide", {language = 'C', returns = 'number', + param_list = {'number', 'number'}, + is_deterministic = true, + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); +box.execute('SELECT "function1.divide"()') +box.execute('SELECT "function1.divide"(6)') +box.execute('SELECT "function1.divide"(6, 3, 3)') +box.execute('SELECT "function1.divide"(6, 3)') +box.execute('SELECT "function1.divide"(5, 2)') +box.func["function1.divide"]:drop() + +function SUMMARIZE(a, b) return a + b end +test_run:cmd("setopt delimiter ';'") +box.schema.func.create("SUMMARIZE", {language = 'LUA', returns = 'number', + is_deterministic = true, + param_list = {'number', 'number'}, + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); +box.execute('SELECT summarize(1, 2)') +box.func.SUMMARIZE:drop() + +test_run:cmd("setopt delimiter ';'") +box.schema.func.create("SUMMARIZE", {language = 'LUA', returns = 'number', + body = 'function (a, b) return a + b end', + is_deterministic = true, + param_list = {'number', 'number'}, + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); +box.execute('SELECT summarize(1, 2)') +box.func.SUMMARIZE:drop() + +-- +-- gh-4113: Valid method to use Lua from SQL +-- +box.execute('SELECT lua(\'return 1 + 1\')') +box.execute('SELECT lua(\'return box.cfg\')') +box.execute('SELECT lua(\'return box.cfg()\')') +box.execute('SELECT lua(\'return box.cfg.memtx_memory\')') + -- Test registered functions interface. function divide(a, b) return a / b end box.schema.func.create("divide", {comment = 'Divide two values'}) diff --git a/test/sql-tap/alias.test.lua b/test/sql-tap/alias.test.lua index f9e6fc9fa..75391b305 100755 --- a/test/sql-tap/alias.test.lua +++ b/test/sql-tap/alias.test.lua @@ -25,14 +25,13 @@ test:plan(9) -- counter = 0 -sequence = function() - counter = counter + 1 - return counter -end -- Function is declared as deterministic deliberately. -- Otherwise it would be called as much as it occurs in a query. -box.internal.sql_create_function("sequence", "INT", sequence, 0, true) +box.schema.func.create('SEQUENCE', {language = 'Lua', is_deterministic = true, + returns = 'unsigned', + body = 'function() counter = counter + 1 return counter end', + exports = {'LUA', 'SQL'}}) test:do_test( "alias-1.1", @@ -220,6 +219,6 @@ test:do_test( -- -- -- }) - +box.func.SEQUENCE:drop() test:finish_test() diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua index 6e4666cf2..c9b60bbf5 100755 --- a/test/sql-tap/check.test.lua +++ b/test/sql-tap/check.test.lua @@ -680,10 +680,11 @@ test:do_execsql_test( -- cannot be tested). -- --reset_db() -local function myfunc(x) - return x < 10 -end -box.internal.sql_create_function("myfunc", "INT", myfunc) +box.schema.func.create('MYFUNC', {language = 'Lua', + is_deterministic = true, + body = 'function(x) return x < 10 end', + returns = 'boolean', param_list = {'number'}, + exports = {'LUA', 'SQL'}}) test:do_execsql_test( 7.1, @@ -808,5 +809,7 @@ test:do_catchsql_test( -- }) +box.func.MYFUNC:drop() + test:finish_test() diff --git a/test/sql-tap/func5.test.lua b/test/sql-tap/func5.test.lua index 0b255e659..45994a352 100755 --- a/test/sql-tap/func5.test.lua +++ b/test/sql-tap/func5.test.lua @@ -71,13 +71,25 @@ test:do_execsql_test( global_counter = 0 -counter = function(str) - global_counter = global_counter + 1 - return global_counter -end +box.schema.func.create('COUNTER1', {language = 'Lua', is_deterministic = false, + param_list = {'any'}, returns = 'integer', + exports = {'SQL', 'LUA'}, + body = [[ + function(str) + global_counter = global_counter + 1 + return global_counter + end + ]]}) -box.internal.sql_create_function("counter1", "INT", counter, -1, false) -box.internal.sql_create_function("counter2", "INT", counter, -1, true) +box.schema.func.create('COUNTER2', {language = 'Lua', is_deterministic = true, + param_list = {'any'}, returns = 'integer', + exports = {'SQL', 'LUA'}, + body = [[ + function(str) + global_counter = global_counter + 1 + return global_counter + end + ]]}) test:do_execsql_test( "func5-2.2", @@ -257,4 +269,7 @@ test:do_execsql_test( SELECT LEAST(false, 'STR', 1, 0.5); ]], { false } ) +box.func.COUNTER1:drop() +box.func.COUNTER2:drop() + test:finish_test() diff --git a/test/sql-tap/lua_sql.test.lua b/test/sql-tap/lua_sql.test.lua index b0ecccd52..67eff2d1b 100755 --- a/test/sql-tap/lua_sql.test.lua +++ b/test/sql-tap/lua_sql.test.lua @@ -1,19 +1,16 @@ #!/usr/bin/env tarantool test = require("sqltester") NULL = require('msgpack').NULL -test:plan(24) - -local function func1(a) - return a -end -local function allways_2(a) - return 2 -end +test:plan(22) test:do_test( "lua_sql-1.0", function () - box.internal.sql_create_function("func1", "INT", allways_2) + box.schema.func.create('FUNC1', {language = 'Lua', + is_deterministic = true, + body = 'function(a) return 2 end', + param_list = {'any'}, returns = 'integer', + exports = {'LUA', 'SQL'}}) return test:execsql("select func1(1)") end, {2}) @@ -22,34 +19,16 @@ test:do_test( test:do_test( "lua_sql-1.1", function () - box.internal.sql_create_function("func1", "INT", func1) + box.func.FUNC1:drop() + box.schema.func.create('FUNC1', {language = 'Lua', + is_deterministic = true, + body = 'function(a) return a end', + param_list = {'scalar'}, returns = 'integer', + exports = {'LUA', 'SQL'}}) return test:execsql("select func1(1)") end, {1}) --- try to loose memory -test:do_test( - "lua_sql-1.2", - function () - for i = 1, 1000000, 1 do - box.internal.sql_create_function("func1", "INT", func1) - end - return test:execsql("select func1(1)") - end, - {1}) - --- check sql polymorphism -test:do_test( - "lua_sql-1.3", - function () - box.internal.sql_create_function("allways_2", "INT", allways_2, 1) -- specify 1 arg - box.internal.sql_create_function("allways_2", "INT", func1) - box.internal.sql_create_function("allways_2", "INT", func1, 2) - box.internal.sql_create_function("allways_2", "INT", func1, 3) - return test:execsql("select allways_2(1)") - end, - {2}) - test:do_catchsql_test( "lua_sql-1.0", "select func3(1)", @@ -72,7 +51,7 @@ for _, val in ipairs({ {result}) end -local from_sql_to_lua = { +from_sql_to_lua = { [1] = {1, 1}, [2] = {"1", 1}, [3] = {"1.5", 1.5}, @@ -81,14 +60,19 @@ local from_sql_to_lua = { [6] = {"x'0500'", "\u{0005}\u{0000}"}, [7] = {"123123123123123", 123123123123123LL}, } -local json = require("json") -local function check_from_sql_to_lua(i, arg) - if from_sql_to_lua[i][2] == arg then - return 1 - end - return 0 -end -box.internal.sql_create_function("check_from_sql_to_lua", "INT", check_from_sql_to_lua) + +box.schema.func.create('CHECK_FROM_SQL_TO_LUA', {language = 'Lua', + is_deterministic = true, + body = [[ + function(i, arg) + if from_sql_to_lua[i][2] == arg then + return 1 + end + return 0 + end + ]], + param_list = {'integer', 'scalar'}, returns = 'integer', + exports = {'LUA', 'SQL'}}) -- check for different types for i = 1, #from_sql_to_lua, 1 do @@ -98,17 +82,23 @@ for i = 1, #from_sql_to_lua, 1 do {1}) end -local from_lua_to_sql = { +from_lua_to_sql = { [1] = {1, 1}, [2] = {"1.5", 1.5}, [3] = {"'1'", "1"}, [4] = {"true", true}, [5] = {"false", false}, } -local function check_from_lua_to_sql(i) - return from_lua_to_sql[i][2] -end -box.internal.sql_create_function("check_from_lua_to_sql", "VARBINARY", check_from_lua_to_sql) + +box.schema.func.create('CHECK_FROM_LUA_TO_SQL', {language = 'Lua', + is_deterministic = true, + body = [[ + function(i) + return from_lua_to_sql[i][2] + end + ]], + param_list = {'integer'}, returns = 'scalar', + exports = {'LUA', 'SQL'}}) -- check for different types for i = 1, #from_lua_to_sql, 1 do @@ -118,14 +108,20 @@ for i = 1, #from_lua_to_sql, 1 do {true}) end -local from_lua_to_sql_bad = { +from_lua_to_sql_bad = { [1] = NULL, [2] = 12LL, -- it is possible to support this type } -local function check_from_lua_to_sql_bad(i) - return from_lua_to_sql_bad[i] -end -box.internal.sql_create_function("check_from_lua_to_sql_bad", "VARBINARY", check_from_lua_to_sql_bad) + +box.schema.func.create('CHECK_FROM_LUA_TO_SQL_BAD', {language = 'Lua', + is_deterministic = true, + body = [[ + function(i) + return from_lua_to_sql_bad[i] + end + ]], + param_list = {'integer'}, returns = 'scalar', + exports = {'LUA', 'SQL'}}) for i = 1, #from_lua_to_sql_bad, 1 do test:do_catchsql_test( @@ -134,16 +130,27 @@ for i = 1, #from_lua_to_sql_bad, 1 do {1, "/Unsupported/"}) end -local function allways_error() - error("my_error123") - return 1 -end -box.internal.sql_create_function("allways_error", "INT", allways_error) +box.schema.func.create('ALLWAYS_ERROR', {language = 'Lua', + is_deterministic = true, + body = [[ + function() + error("my_error123") + return 1 + end + ]], + param_list = {}, returns = 'integer', + exports = {'LUA', 'SQL'}}) + test:do_catchsql_test( "lua_sql-2.6", "select allways_error()", {1, "/my_error123/"}) +box.func.FUNC1:drop() +box.func.CHECK_FROM_SQL_TO_LUA:drop() +box.func.CHECK_FROM_LUA_TO_SQL:drop() +box.func.CHECK_FROM_LUA_TO_SQL_BAD:drop() +box.func.ALLWAYS_ERROR:drop() test:finish_test() diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua index 4e4b8d453..6bedf5879 100755 --- a/test/sql-tap/subquery.test.lua +++ b/test/sql-tap/subquery.test.lua @@ -710,17 +710,20 @@ test:do_execsql_test( -- for a matching column name did not cause an otherwise static subquery -- to become a dynamic (correlated) subquery. -- -local callcnt = 0 +callcnt = 0 test:do_test( "subquery-5.1", function() - local function callcntproc(n) - callcnt = callcnt + 1 - return n - end - - callcnt = 0 - box.internal.sql_create_function("callcnt", "INT", callcntproc) + box.schema.func.create('CALLCNT', {language = 'Lua', + is_deterministic = true, + param_list = {'integer'}, returns = 'integer', + exports = {'LUA', 'SQL'}, + body = [[ + function(n) + callcnt = callcnt + 1 + return n + end + ]]}) return test:execsql [[ CREATE TABLE t4(x TEXT,y INT PRIMARY KEY); INSERT INTO t4 VALUES('one',1); @@ -791,6 +794,8 @@ test:do_test( return callcnt end, 1) +box.func.CALLCNT:drop() + --############ was disable until we get #2652 fixed -- Ticket #2652. Allow aggregate functions of outer queries inside -- a non-aggregate subquery. diff --git a/test/sql-tap/trigger9.test.lua b/test/sql-tap/trigger9.test.lua index e7e170b3d..e39a31384 100755 --- a/test/sql-tap/trigger9.test.lua +++ b/test/sql-tap/trigger9.test.lua @@ -46,7 +46,11 @@ local function has_rowdata(sql) -- X(41, "X!cmd", [=[["expr","[lsearch [execsql \"explain $sql\"] RowData]>=0"]]=]) end -box.internal.sql_create_function('randstr', 'TEXT', test.randstr, 1) +box.schema.func.create('RANDSTR', {language = 'Lua', + body = 'function(n) return test.randstr(n) end', + param_list = {'integer'}, returns = 'string', + exports = {'LUA', 'SQL'}}) + -- MUST_WORK_TEST test:do_execsql_test( @@ -171,6 +175,8 @@ test:do_execsql_test( -- }) +box.func.RANDSTR:drop() + test:do_execsql_test( "trigger9-1.5.1", [[ diff --git a/test/sql/errinj.result b/test/sql/errinj.result index ea570f0dc..ecc194fb8 100644 --- a/test/sql/errinj.result +++ b/test/sql/errinj.result @@ -397,32 +397,6 @@ box.execute("DROP TABLE t3;") - row_count: 1 ... -- --- gh-3931: Store regular identifiers in case-normal form --- -errinj = box.error.injection ---- -... -errinj.set("ERRINJ_SQL_NAME_NORMALIZATION", true) ---- -- ok -... -box.execute("CREATE TABLE hello (id INT primary key,x INT,y INT);") ---- -- null -- Failed to allocate 6 bytes in sqlDbMallocRawNN for res -... -dummy_f = function(int) return 1 end ---- -... -box.internal.sql_create_function("counter1", "INT", dummy_f, -1, false) ---- -- error: Failed to allocate 9 bytes in region_alloc for res -... -errinj.set("ERRINJ_SQL_NAME_NORMALIZATION", false) ---- -- ok -... --- -- Tests which are aimed at verifying work of commit/rollback -- triggers on _ck_constraint space. -- diff --git a/test/sql/errinj.test.lua b/test/sql/errinj.test.lua index 552860a38..2b4f74a82 100644 --- a/test/sql/errinj.test.lua +++ b/test/sql/errinj.test.lua @@ -118,16 +118,6 @@ box.execute("INSERT INTO t3 VALUES(1, 1, 3);") errinj.set("ERRINJ_WAL_IO", false) box.execute("DROP TABLE t3;") --- --- gh-3931: Store regular identifiers in case-normal form --- -errinj = box.error.injection -errinj.set("ERRINJ_SQL_NAME_NORMALIZATION", true) -box.execute("CREATE TABLE hello (id INT primary key,x INT,y INT);") -dummy_f = function(int) return 1 end -box.internal.sql_create_function("counter1", "INT", dummy_f, -1, false) -errinj.set("ERRINJ_SQL_NAME_NORMALIZATION", false) - -- -- Tests which are aimed at verifying work of commit/rollback -- triggers on _ck_constraint space. diff --git a/test/sql/func-recreate.result b/test/sql/func-recreate.result index 73fb03cc4..1f6966431 100644 --- a/test/sql/func-recreate.result +++ b/test/sql/func-recreate.result @@ -12,7 +12,15 @@ box.execute('pragma sql_default_engine=\''..engine..'\'') fiber = require('fiber') --- ... -box.internal.sql_create_function('WAITFOR', 'INT', function (n) fiber.sleep(n) return n end) +test_run:cmd("setopt delimiter ';'") +--- +- true +... +box.schema.func.create('WAITFOR', {language = 'Lua', + body = 'function (n) fiber.sleep(n) return n end', + param_list = {'integer'}, returns = 'integer', + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); --- ... ch = fiber.channel(1) @@ -24,10 +32,19 @@ _ = fiber.create(function () ch:put(box.execute('select WAITFOR(0.2)')) end) fiber.sleep(0.1) --- ... -box.internal.sql_create_function('WAITFOR', 'INT', function (n) require('fiber').sleep(n) return n end) +box.func.WAITFOR:drop() +--- +... +test_run:cmd("setopt delimiter ';'") +--- +- true +... +box.schema.func.create('WAITFOR', {language = 'Lua', + body = 'function (n) fiber.sleep(n) return n end', + param_list = {'integer'}, returns = 'integer', + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); --- -- error: 'Failed to create function ''WAITFOR'': unable to create function due to - active statements' ... ch:get() --- @@ -37,6 +54,20 @@ ch:get() rows: - [0.2] ... -box.internal.sql_create_function('WAITFOR', 'INT', function (n) require('fiber').sleep(n) return n end) +box.func.WAITFOR:drop() +--- +... +test_run:cmd("setopt delimiter ';'") +--- +- true +... +box.schema.func.create('WAITFOR', {language = 'Lua', + body = 'function (n) fiber.sleep(n) return n end', + param_list = {'integer'}, returns = 'integer', + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); +--- +... +box.func.WAITFOR:drop() --- ... diff --git a/test/sql/func-recreate.test.lua b/test/sql/func-recreate.test.lua index 753e9ca4d..5496baf6e 100644 --- a/test/sql/func-recreate.test.lua +++ b/test/sql/func-recreate.test.lua @@ -4,14 +4,34 @@ box.execute('pragma sql_default_engine=\''..engine..'\'') -- Check errors during function create process fiber = require('fiber') -box.internal.sql_create_function('WAITFOR', 'INT', function (n) fiber.sleep(n) return n end) +test_run:cmd("setopt delimiter ';'") +box.schema.func.create('WAITFOR', {language = 'Lua', + body = 'function (n) fiber.sleep(n) return n end', + param_list = {'integer'}, returns = 'integer', + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); ch = fiber.channel(1) _ = fiber.create(function () ch:put(box.execute('select WAITFOR(0.2)')) end) fiber.sleep(0.1) -box.internal.sql_create_function('WAITFOR', 'INT', function (n) require('fiber').sleep(n) return n end) +box.func.WAITFOR:drop() + +test_run:cmd("setopt delimiter ';'") +box.schema.func.create('WAITFOR', {language = 'Lua', + body = 'function (n) fiber.sleep(n) return n end', + param_list = {'integer'}, returns = 'integer', + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); ch:get() -box.internal.sql_create_function('WAITFOR', 'INT', function (n) require('fiber').sleep(n) return n end) +box.func.WAITFOR:drop() +test_run:cmd("setopt delimiter ';'") +box.schema.func.create('WAITFOR', {language = 'Lua', + body = 'function (n) fiber.sleep(n) return n end', + param_list = {'integer'}, returns = 'integer', + exports = {'LUA', 'SQL'}}) +test_run:cmd("setopt delimiter ''"); + +box.func.WAITFOR:drop() -- 2.22.0