From: Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org> To: v.shpilevoy@tarantool.org Cc: tarantool-patches@dev.tarantool.org Subject: [Tarantool-patches] [PATCH v1 08/13] sql: rework AVG() Date: Fri, 10 Sep 2021 19:02:13 +0300 [thread overview] Message-ID: <094e9605f220dbc257dbe43cdd3432934ddd5975.1631289462.git.imeevma@gmail.com> (raw) In-Reply-To: <cover.1631289462.git.imeevma@gmail.com> This patch makes AVG() accept DOUBLE values by default. Also, after this patch AVG() will be able to work with DECIMAL values. Part of #4145 Part of #6355 --- src/box/sql/func.c | 106 +++++++++-------------- test/sql-tap/built-in-functions.test.lua | 33 ++++++- 2 files changed, 72 insertions(+), 67 deletions(-) diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 12a6a5a2c..9e0c09206 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -102,6 +102,44 @@ fin_total(struct sql_context *ctx) mem_copy_as_ephemeral(ctx->pOut, ctx->pMem); } +/** Implementation of the AVG() function. */ +static void +step_avg(struct sql_context *ctx, int argc, struct Mem **argv) +{ + assert(argc == 1); + (void)argc; + assert(ctx->pMem->type == MEM_TYPE_NULL || mem_is_bin(ctx->pMem)); + if (argv[0]->type == MEM_TYPE_NULL) + return; + if (ctx->pMem->type == MEM_TYPE_NULL) { + uint32_t size = 2 * sizeof(struct Mem); + struct Mem *mems = sqlDbMallocRawNN(sql_get(), size); + mem_create(&mems[0]); + mem_create(&mems[1]); + mem_copy_as_ephemeral(&mems[0], argv[0]); + mem_set_uint(&mems[1], 1); + mem_set_bin_allocated(ctx->pMem, (char *)mems, size); + return; + } + struct Mem *mems = (struct Mem *)ctx->pMem->z; + assert(mems[1].type = MEM_TYPE_UINT); + ++mems[1].u.u; + if (mem_add(&mems[0], argv[0], &mems[0]) != 0) + ctx->is_aborted = true; +} + +/** Finalizer for the AVG() function. */ +static void +fin_avg(struct sql_context *ctx) +{ + assert(ctx->pMem->type == MEM_TYPE_NULL || mem_is_bin(ctx->pMem)); + if (ctx->pMem->type == MEM_TYPE_NULL) + return mem_set_null(ctx->pOut); + struct Mem *mems = (struct Mem *)ctx->pMem->z; + if (mem_div(&mems[0], &mems[1], ctx->pOut) != 0) + ctx->is_aborted = true; +} + static const unsigned char * mem_as_ustr(struct Mem *mem) { @@ -1656,69 +1694,6 @@ soundexFunc(sql_context * context, int argc, sql_value ** argv) } } -/* - * An instance of the following structure holds the context of a - * sum() or avg() aggregate computation. - */ -typedef struct SumCtx SumCtx; -struct SumCtx { - struct Mem mem; - uint32_t count; -}; - -/* - * Routines used to compute the sum, average, and total. - * - * The SUM() function follows the (broken) SQL standard which means - * that it returns NULL if it sums over no inputs. TOTAL returns - * 0.0 in that case. In addition, TOTAL always returns a float where - * SUM might return an integer if it never encounters a floating point - * value. TOTAL never fails, but SUM might through an exception if - * it overflows an integer. - */ -static void -sum_step(struct sql_context *context, int argc, sql_value **argv) -{ - assert(argc == 1); - UNUSED_PARAMETER(argc); - struct SumCtx *p = sql_aggregate_context(context, sizeof(*p)); - if (p == NULL) { - context->is_aborted = true; - return; - } - if (p->count == 0) { - mem_create(&p->mem); - assert(context->func->def->returns == FIELD_TYPE_INTEGER || - context->func->def->returns == FIELD_TYPE_DOUBLE); - if (context->func->def->returns == FIELD_TYPE_INTEGER) - mem_set_uint(&p->mem, 0); - else - mem_set_double(&p->mem, 0.0); - } - if (argv[0]->type == MEM_TYPE_NULL) - return; - ++p->count; - assert(mem_is_num(argv[0])); - if (mem_add(&p->mem, argv[0], &p->mem) != 0) - context->is_aborted = true; -} - -static void -avgFinalize(sql_context * context) -{ - SumCtx *p; - p = sql_aggregate_context(context, 0); - if (p == NULL || p->count == 0) { - mem_set_null(context->pOut); - return; - } - struct Mem mem; - mem_create(&mem); - mem_set_uint(&mem, p->count); - if (mem_div(&p->mem, &mem, context->pOut) != 0) - context->is_aborted = true; -} - /* * The following structure keeps track of state information for the * count() aggregate function. @@ -2015,8 +1990,9 @@ struct sql_func_definition { static struct sql_func_definition definitions[] = { {"ABS", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, absFunc, NULL}, {"ABS", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, absFunc, NULL}, - {"AVG", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, sum_step, avgFinalize}, - {"AVG", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, sum_step, avgFinalize}, + {"AVG", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, step_avg, fin_avg}, + {"AVG", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, step_avg, fin_avg}, + {"AVG", 1, {FIELD_TYPE_DECIMAL}, FIELD_TYPE_DECIMAL, step_avg, fin_avg}, {"CHAR", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, charFunc, NULL}, {"CHAR_LENGTH", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, lengthFunc, NULL}, diff --git a/test/sql-tap/built-in-functions.test.lua b/test/sql-tap/built-in-functions.test.lua index 507d06549..08a63b86d 100755 --- a/test/sql-tap/built-in-functions.test.lua +++ b/test/sql-tap/built-in-functions.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(58) +test:plan(61) local dec = require('decimal') @@ -477,7 +477,7 @@ test:do_test( local res = {pcall(box.execute, [[SELECT AVG(?);]], {'1'})} return {tostring(res[3])} end, { - "Type mismatch: can not convert string('1') to integer" + "Type mismatch: can not convert string('1') to double" }) test:do_catchsql_test( @@ -605,4 +605,33 @@ test:do_execsql_test( } ) +-- Make sure AVG() accepts and returns DOUBLE by default. +test:do_test( + "builtins-4.1.1", + function() + return box.execute([[SELECT AVG(?);]], {1}).metadata + end, { + {name = "COLUMN_1", type = "double"}, + }) + +test:do_test( + "builtins-4.1.2", + function() + local res = {pcall(box.execute, [[SELECT AVG(?);]], {-1ULL})} + return {tostring(res[3])} + end, { + "Type mismatch: can not convert integer(18446744073709551615) to double" + }) + +-- Make sure AVG() works with DECIMAL properly. +test:do_execsql_test( + "builtins-4.1.3", + [[ + SELECT AVG(cast(column_2 as DECIMAL)) from (values(1), (123.432)); + ]], + { + dec.new(62.216) + } +) + test:finish_test() -- 2.25.1
next prev parent reply other threads:[~2021-09-10 16:05 UTC|newest] Thread overview: 24+ messages / expand[flat|nested] mbox.gz Atom feed top 2021-09-10 16:01 [Tarantool-patches] [PATCH v1 00/13] sql: reworks aggregate functions Mergen Imeev via Tarantool-patches 2021-09-10 16:01 ` [Tarantool-patches] [PATCH v1 01/13] sql: use register P1 for number of arguments Mergen Imeev via Tarantool-patches 2021-09-10 16:01 ` [Tarantool-patches] [PATCH v1 02/13] sql: remove AggStep0 and OP_BuiltinFunction0 Mergen Imeev via Tarantool-patches 2021-09-10 16:27 ` Mergen Imeev via Tarantool-patches 2021-09-14 21:21 ` Vladislav Shpilevoy via Tarantool-patches 2021-09-10 16:01 ` [Tarantool-patches] [PATCH v1 03/13] sql: move collation to struct sql_context Mergen Imeev via Tarantool-patches 2021-09-14 21:22 ` Vladislav Shpilevoy via Tarantool-patches 2021-09-21 10:40 ` Mergen Imeev via Tarantool-patches 2021-09-10 16:02 ` [Tarantool-patches] [PATCH v1 04/13] sql: introduce mem_append() Mergen Imeev via Tarantool-patches 2021-09-10 16:02 ` [Tarantool-patches] [PATCH v1 05/13] sql: remove sql_vdbemem_finalize() Mergen Imeev via Tarantool-patches 2021-09-14 21:23 ` Vladislav Shpilevoy via Tarantool-patches 2021-09-21 10:47 ` Mergen Imeev via Tarantool-patches 2021-09-22 22:47 ` Vladislav Shpilevoy via Tarantool-patches 2021-09-10 16:02 ` [Tarantool-patches] [PATCH v1 06/13] sql: rework SUM() Mergen Imeev via Tarantool-patches 2021-09-10 16:02 ` [Tarantool-patches] [PATCH v1 07/13] sql: rework TOTAL() Mergen Imeev via Tarantool-patches 2021-09-10 16:02 ` Mergen Imeev via Tarantool-patches [this message] 2021-09-14 21:24 ` [Tarantool-patches] [PATCH v1 08/13] sql: rework AVG() Vladislav Shpilevoy via Tarantool-patches 2021-09-10 16:02 ` [Tarantool-patches] [PATCH v1 09/13] sql: rework COUNT() Mergen Imeev via Tarantool-patches 2021-09-10 16:02 ` [Tarantool-patches] [PATCH v1 10/13] sql: rework MIN() and MAX() Mergen Imeev via Tarantool-patches 2021-09-10 16:02 ` [Tarantool-patches] [PATCH v1 11/13] sql: rework GROUP_CONCAT() Mergen Imeev via Tarantool-patches 2021-09-10 16:02 ` [Tarantool-patches] [PATCH v1 12/13] sql: remove copying of result in finalizers Mergen Imeev via Tarantool-patches 2021-09-14 21:24 ` Vladislav Shpilevoy via Tarantool-patches 2021-09-21 10:49 ` Mergen Imeev via Tarantool-patches 2021-09-10 16:02 ` [Tarantool-patches] [PATCH v1 13/13] sql: remove MEM_TYPE_AGG Mergen Imeev via Tarantool-patches
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=094e9605f220dbc257dbe43cdd3432934ddd5975.1631289462.git.imeevma@gmail.com \ --to=tarantool-patches@dev.tarantool.org \ --cc=imeevma@tarantool.org \ --cc=v.shpilevoy@tarantool.org \ --subject='Re: [Tarantool-patches] [PATCH v1 08/13] sql: rework AVG()' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox