From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from [87.239.111.99] (localhost [127.0.0.1]) by dev.tarantool.org (Postfix) with ESMTP id C0E8A6F867; Tue, 1 Feb 2022 16:39:24 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org C0E8A6F867 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1643722764; bh=7y8Pz+zxaU7iMYD29a302RQZ6827dtibVjUKnoWx1JU=; h=To:Cc:Date:In-Reply-To:References:Subject:List-Id: List-Unsubscribe:List-Archive:List-Post:List-Help:List-Subscribe: From:Reply-To:From; b=gKqaog+DMuoWaH02lUPJw8ygkoZ/LeCpbg36LjRyqIVgdqAv23jhx5fPr5wy8mYaZ uXODo4AuyAF7rKhbJkjSM/+3P9sFYCV5yoy9viiwxbczES4yRFf2sNpXfhcsfXheo0 lEOsbJulvh4DmxYi85xv5xnJMlTbbWQPyrSB6Cto= Received: from smtpng2.i.mail.ru (smtpng2.i.mail.ru [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 23F616F869 for ; Tue, 1 Feb 2022 16:37:28 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 23F616F869 Received: by smtpng2.m.smailru.net with esmtpa (envelope-from ) id 1nEtLb-00046F-Iv; Tue, 01 Feb 2022 16:37:27 +0300 To: v.shpilevoy@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Tue, 1 Feb 2022 16:37:27 +0300 Message-Id: <162eb863bee6a45a63ee85604ce004d83e8aedfa.1643722506.git.imeevma@gmail.com> X-Mailer: git-send-email 2.25.1 In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-4EC0790: 10 X-7564579A: B8F34718100C35BD X-77F55803: 4F1203BC0FB41BD99F281FB7F96F126D2F5CB4A1A69D820725F5B6F6576C653800894C459B0CD1B951833152012EBA3C6EF28E16FA3A0C17AA45120333E8030E3E51E86926A75756 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7D3E352D4AD7EBF54EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637BC6099E116BE74F18638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D83F5CCEC71F8E83DD170F861659C35608117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCF1175FABE1C0F9B6A471835C12D1D9774AD6D5ED66289B52BA9C0B312567BB23117882F446042972877693876707352033AC447995A7AD18C26CFBAC0749D213D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B6D635BA3ABDB36C18089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-8FC586DF: 6EFBBC1D9D64D975 X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975CCED4562AEA10A75F043754C3563453F0011E42809DCEEA0A9C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EFE323A604FE95ED09DC48ACC2A39D04F89CDFB48F4795C241BDAD6C7F3747799A X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D347798CB24D899047332111B35041D363D2D602001A22AB5BFC4D8DCFFAB6EC99549638963552C33B41D7E09C32AA3244CBAF5CE80CAB366704C6A7583CB1C37AD60759606DA2E136A729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2bioje/Fy4v4Lxyq9Jcxe6YTA8Q== X-Mailru-Sender: 689FA8AB762F739339CABD9B3CA9A7D6EB296172B4C02CA6AF0490435FC8818083D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E3365FEEDEB644C299C0ED14614B50AE0675 X-Mras: Ok Subject: [Tarantool-patches] [PATCH v2 4/4] sql: introduce FINALIZE for custom aggregate X-BeenThere: tarantool-patches@dev.tarantool.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , From: Mergen Imeev via Tarantool-patches Reply-To: imeevma@tarantool.org Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" This patch introduces a way to create a FINALIZER for user-defined aggregate functions. Closes #2579 @TarantoolBot document Title: User-defined aggregate functions User-defined aggregate functions are now available. To create a user-defined aggregate function, the 'aggregate' option should be set to 'group'. The function must have at least one argument. The last argument is always the result of the function in the previous step, or NULL if it is the first step. The last argument will be added automatically. Example: ``` box.schema.func.create("F1", {language = "Lua", body = [[ function(x, state) if state == nil then state = {sum = 0, count = 0} end state.sum = state.sum + x state.count = state.count + 1 return state end ]], param_list = {"integer", "map"}, returns = "map", aggregate = "group", exports = {"SQL"}, }) box.execute('CREATE TABLE t (i INT PRIMARY KEY);') box.execute('INSERT INTO t VALUES(1), (2), (3), (4), (5);') box.execute('SELECT f1(i) FROM t;') ``` The result will be: ``` tarantool> box.execute([[select f1(i) from t;]]) --- - metadata: - name: COLUMN_1 type: map rows: - [{'sum': 15, 'count': 5}] ... ``` To create a finalizer for a function, another function should be created. This function should follow the following rules: 1) its name should be '_finalize'; 2) it must take exactly one argument; 3) it must be a non-aggregate function. If an aggregate function has a finalizer, the result of the aggregate function will be determined by the finalizer. Example: ``` box.schema.func.create("F1_finalize", { language = "Lua", body = [[ function(state) if state == nil then return 0 end return state.sum / state.count end ]], param_list = {"map"}, returns = "number", exports = {'LUA', 'SQL'}, }) box.execute('SELECT f1(i) FROM t;') ``` The result will be: ``` tarantool> box.execute([[select f1(i) from t;]]) --- - metadata: - name: COLUMN_1 type: number rows: - [3] ... ``` --- .../gh-2579-introduce-custom-aggregates.md | 3 + src/box/sql/func.c | 13 ++ src/box/sql/resolve.c | 12 ++ src/box/sql/select.c | 7 ++ src/box/sql/sqlInt.h | 4 + .../sql-tap/gh-2579-custom-aggregate.test.lua | 113 +++++++++++++++++- 6 files changed, 151 insertions(+), 1 deletion(-) create mode 100644 changelogs/unreleased/gh-2579-introduce-custom-aggregates.md diff --git a/changelogs/unreleased/gh-2579-introduce-custom-aggregates.md b/changelogs/unreleased/gh-2579-introduce-custom-aggregates.md new file mode 100644 index 000000000..7975f9f08 --- /dev/null +++ b/changelogs/unreleased/gh-2579-introduce-custom-aggregates.md @@ -0,0 +1,3 @@ +## feature/sql + +* user-defined aggregate functions are now available in SQL (gh-2579). diff --git a/src/box/sql/func.c b/src/box/sql/func.c index cda872194..a445dbc3a 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -2071,6 +2071,19 @@ sql_func_find(struct Expr *expr) return func; } +struct func * +sql_func_finalize(const char *name) +{ + const char *finalize_name = tt_sprintf("%s_finalize", name); + uint32_t len = strlen(finalize_name); + struct func *finalize = func_by_name(finalize_name, len); + if (finalize == NULL || + finalize->def->param_count != 1 || + finalize->def->aggregate == FUNC_AGGREGATE_GROUP) + return NULL; + return finalize; +} + uint32_t sql_func_flags(const char *name) { diff --git a/src/box/sql/resolve.c b/src/box/sql/resolve.c index 22b4e6799..095e6d6a8 100644 --- a/src/box/sql/resolve.c +++ b/src/box/sql/resolve.c @@ -663,6 +663,18 @@ resolveExprStep(Walker * pWalker, Expr * pExpr) return WRC_Abort; } pExpr->type = func->def->returns; + /* + * In case a user-defined aggregate function was called, + * the result type will be the result type of the + * FINALIZE part of the function. + */ + if (func->def->language != FUNC_LANGUAGE_SQL_BUILTIN && + func->def->aggregate == FUNC_AGGREGATE_GROUP) { + const char *name = pExpr->u.zToken; + struct func *finalize = sql_func_finalize(name); + if (finalize != NULL) + pExpr->type = finalize->def->returns; + } assert(!func->def->is_deterministic || (pNC->ncFlags & NC_IdxExpr) == 0); if (func->def->is_deterministic) diff --git a/src/box/sql/select.c b/src/box/sql/select.c index dcea48c6e..a3fe2f60f 100644 --- a/src/box/sql/select.c +++ b/src/box/sql/select.c @@ -5566,7 +5566,14 @@ finalize_agg_function(struct Vdbe *vdbe, const struct AggInfo_func *agg_func) if (agg_func->func->def->language == FUNC_LANGUAGE_SQL_BUILTIN) { sqlVdbeAddOp1(vdbe, OP_AggFinal, agg_func->iMem); sqlVdbeAppendP4(vdbe, agg_func->func, P4_FUNC); + return; } + if (sql_func_finalize(agg_func->pExpr->u.zToken) == NULL) + return; + const char *name = tt_sprintf("%s_finalize", agg_func->pExpr->u.zToken); + const char *str = sqlDbStrDup(sql_get(), name); + sqlVdbeAddOp4(vdbe, OP_FunctionByName, 1, agg_func->iMem, + agg_func->iMem, str, P4_DYNAMIC); } /* diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index f49522dc8..8d0321f56 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -4308,6 +4308,10 @@ sql_func_find(struct Expr *expr); int sql_emit_args_types(struct Vdbe *v, int reg, struct func *base, uint32_t argc); +/** Return a function that is a finalizer for function with given name. */ +struct func * +sql_func_finalize(const char *name); + /** * Return the parameters of the function with the given name. If the function * with the given name does not exist, or the function is not a built-in SQL diff --git a/test/sql-tap/gh-2579-custom-aggregate.test.lua b/test/sql-tap/gh-2579-custom-aggregate.test.lua index 213e2e870..fc1633b95 100755 --- a/test/sql-tap/gh-2579-custom-aggregate.test.lua +++ b/test/sql-tap/gh-2579-custom-aggregate.test.lua @@ -3,7 +3,7 @@ local build_path = os.getenv("BUILDDIR") package.cpath = build_path..'/test/sql-tap/?.so;'..build_path..'/test/sql-tap/?.dylib;'..package.cpath local test = require("sqltester") -test:plan(5) +test:plan(9) test:execsql([[ CREATE TABLE t (i INT PRIMARY KEY); @@ -105,6 +105,117 @@ test:do_test( "least one argument" }) +-- Make sure finalizers of aggregate functions work as intended. +box.schema.func.create("F1_finalize", { + language = "Lua", + body = [[ + function(state) + if state == nil then + return 0 + end + return state.sum / state.count + end + ]], + param_list = {"map"}, + returns = "number", + exports = {'LUA', 'SQL'}, +}) + +test:do_execsql_test( + "gh-2579-6", + [[ + SELECT f1(i), typeof(f1(i)) from t; + ]], { + 3, "integer" + }) + +-- +-- Make sure that the finalizers of the aggregate function and the aggregate +-- function can be of different types. +-- +box.schema.func.create("F2_finalize", { + language = "Lua", + body = [[ + function(state) + if state == nil then + return 0 + end + return state[#state] + end + ]], + param_list = {"array"}, + returns = "number", + exports = {'LUA', 'SQL'}, +}) + +test:do_execsql_test( + "gh-2579-7", + [[ + SELECT f2(i) from t; + ]], { + 5 + }) + +-- Make sure that aggregate function cannot become finalizer. +box.schema.func.drop("F2_finalize") +box.schema.func.create("F2_finalize", { + language = "Lua", + body = [[ + function(state) + if state == nil then + return 0 + end + sum = 0 + for k, v in pairs(state) do + sum = sum + v + end + return sum + end + ]], + aggregate = "group", + param_list = {"array"}, + returns = "number", + exports = {'SQL'}, +}) + +test:do_execsql_test( + "gh-2579-8", + [[ + SELECT f2(i), typeof(f2(i)) from t; + ]], { + {1, 2, 3, 4, 5}, "array" + }) + +-- +-- Make sure that function with number of arguments not equal to 1 cannot become +-- finalizer. +-- +box.schema.func.drop("F2_finalize") +box.schema.func.create("F2_finalize", { + language = "Lua", + body = [[ + function(state, x) + if state == nil then + return 0 + end + return state[#state] * x + end + ]], + param_list = {"map", "integer"}, + returns = "number", + exports = {'LUA', 'SQL'}, +}) + +test:do_execsql_test( + "gh-2579-9", + [[ + SELECT f2(i), typeof(f2(i)), "F2_finalize"(f2(i), 2) from t; + ]], { + {1, 2, 3, 4, 5}, "array", 10 + }) + +box.schema.func.drop('F2_finalize') +box.schema.func.drop('F1_finalize') box.schema.func.drop('gh-2579-custom-aggregate.f3') box.schema.func.drop('F2') box.schema.func.drop('F1') -- 2.25.1