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 323826E454; Thu, 10 Feb 2022 12:16:13 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 323826E454 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1644484573; bh=pUMiVk2SL9oBNbfbrSruxXfgiCjWOIdVoEi4Rr7eEPw=; 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=XWC8jlUPBPBG/R4WfSjVQZhThuNBhwklXgLxMOOcmNPFMcTCr1ldtu71AEMoXzC/7 LMrXA+DWWynGFGf3W4FO5JNhWHYbdVuB5qtAw9Rz9uEMNKIphl40pzcd8muYnif0LZ vbM7+nBEJWwmBF2UFMzqNtrQX9zPrnnnX5gBLhyk= Received: from smtp31.i.mail.ru (smtp31.i.mail.ru [94.100.177.91]) (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 740EC6E225 for ; Thu, 10 Feb 2022 12:14:18 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 740EC6E225 Received: by smtp31.i.mail.ru with esmtpa (envelope-from ) id 1nI5Wr-0000V8-JB; Thu, 10 Feb 2022 12:14:18 +0300 To: kyukhin@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Thu, 10 Feb 2022 12:14:17 +0300 Message-Id: <85df57e6fcf04f2704d207a621e2658000653eb7.1644484227.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-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD9B74B50284A7C1A0B97F9FEC8801B7CF8F01E66BE5396193D182A05F538085040A23965CCA814AF2F547FFECF244A09202FB8B6954F063FEE58902005BBF7120B X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE76574C3D62D66A535EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637AC20F58FBAB79054EA1F7E6F0F101C6723150C8DA25C47586E58E00D9D99D84E1BDDB23E98D2D38BEBC5CAB6D411FFA63140B8AC18D705E6EC3E448B84857A4DCC7F00164DA146DAFE8445B8C89999728AA50765F790063783E00425F71A4181389733CBF5DBD5E9C8A9BA7A39EFB766F5D81C698A659EA7CC7F00164DA146DA9985D098DBDEAEC82FFDA4F57982C5F4F6B57BC7E6449061A352F6E88A58FB86F5D81C698A659EA73AA81AA40904B5D9A18204E546F3947C6317DB91DEFCD11303F1AB874ED890284AD6D5ED66289B52698AB9A7B718F8C46E0066C2D8992A16725E5C173C3A84C3135EDF6918C5D223BA3038C0950A5D36B5C8C57E37DE458B0BC6067A898B09E46D1867E19FE14079C09775C1D3CA48CF3D321E7403792E342EB15956EA79C166A417C69337E82CC275ECD9A6C639B01B78DA827A17800CE75A9E79F66F1C28F3731C566533BA786AA5CC5B56E945C8DA X-8FC586DF: 6EFBBC1D9D64D975 X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975C05348A8C7D563C4214F7D44D1C29DC92C0EB4B1D68B134D69C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EF4A2C4B6BA9DFC510699F904B3F4130E343918A1A30D5E7FCCB5012B2E24CD356 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D343D50AEDB859DBAD93E897DD5D0FFECB188EA1D63C36DF833AC77A7DD3765A45BF93E0417CD1DF7C61D7E09C32AA3244C3F76969A0C831DEC7EADCAA9354A06A8795D98D676DD64D0729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojptAT+HCMDqMMzpZRzkiqdA== X-Mailru-Sender: 5C3750E245F362008BC1685FEC6306ED9A0EAB00682FEE87547FFECF244A092084876E3DC5E244DF5105BD0848736F9966FEC6BF5C9C28D97E07721503EA2E00ED97202A5A4E92BF72D6B4FCE48DF648AE208404248635DF 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 dc2fd771a..21d3f80e5 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -2075,6 +2075,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 afd08888e..e4758e556 100755 --- a/test/sql-tap/gh-2579-custom-aggregate.test.lua +++ b/test/sql-tap/gh-2579-custom-aggregate.test.lua @@ -4,7 +4,7 @@ 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); @@ -106,6 +106,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