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 2/2] sql: introduce ARRAY() function Date: Wed, 3 Nov 2021 11:17:52 +0300 [thread overview] Message-ID: <6675fda5d666534b8f1b39a2a7db27fae6d8ef40.1635927295.git.imeevma@gmail.com> (raw) In-Reply-To: <cover.1635927295.git.imeevma@gmail.com> Closes #4762 @TarantoolBot document Title: SQL built-in function ARRAY() The SQL built-in function ARRAY() is now available. It takes one or more arguments of type ANY and returns a value of type ARRAY. The return value contains all the arguments accepted by the function, in the order in which they were given. Example: ``` tarantool> box.execute([[SELECT array(1, array('a', true), 2.5, x'31');]]) --- - metadata: - name: COLUMN_1 type: array rows: - [[1, ['a', true], 2.5, '1']] ... ``` --- .../gh-4762-introduce-array-to-sql.md | 4 +++ src/box/sql.c | 2 +- src/box/sql/func.c | 24 +++++++++++++- src/box/sql/mem.c | 26 +++++++-------- src/box/sql/mem.h | 23 ++++++------- src/box/sql/parse.y | 2 +- src/box/sql/vdbe.c | 5 ++- src/box/sql/vdbeapi.c | 4 +-- test/sql-tap/array.test.lua | 33 ++++++++++++++++++- 9 files changed, 90 insertions(+), 33 deletions(-) create mode 100644 changelogs/unreleased/gh-4762-introduce-array-to-sql.md diff --git a/changelogs/unreleased/gh-4762-introduce-array-to-sql.md b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md new file mode 100644 index 000000000..77040d1a4 --- /dev/null +++ b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md @@ -0,0 +1,4 @@ +## feature/core + + * Field type ARRAY is now available in SQL. The ARRAY() function can be used to + create values of type ARRAY in SQL (gh-4762). diff --git a/src/box/sql.c b/src/box/sql.c index d15159d6e..2a78a96d5 100644 --- a/src/box/sql.c +++ b/src/box/sql.c @@ -211,7 +211,7 @@ sql_cursor_seek(struct BtCursor *cur, struct Mem *mems, uint32_t len, int *res) struct region *region = &fiber()->gc; size_t used = region_used(region); uint32_t size; - const char *tuple = sql_vdbe_mem_encode_tuple(mems, len, &size, region); + const char *tuple = mem_encode_array(mems, len, &size, region); if (tuple == NULL) return -1; if (key_alloc(cur, size) != 0) diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 07f1ed50a..63bf58473 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -232,6 +232,26 @@ step_group_concat(struct sql_context *ctx, int argc, struct Mem *argv) ctx->is_aborted = true; } +/** + * Implementation of the ARRAY() function. + * + * The ARRAY() function takes one or more ANY arguments and returns ARRAY value + * that contains all the arguments received, in the order in which they were + * received. + */ +static void +func_array(struct sql_context *ctx, int argc, struct Mem *argv) +{ + assert(argc >= 1); + uint32_t size; + struct region *region = &fiber()->gc; + size_t svp = region_used(region); + char *array = mem_encode_array(argv, argc, &size, region); + if (array == NULL || mem_copy_array(ctx->pOut, array, size) != 0) + ctx->is_aborted = true; + region_truncate(region, svp); +} + static const unsigned char * mem_as_ustr(struct Mem *mem) { @@ -1867,6 +1887,7 @@ sql_builtin_stub(sql_context *ctx, int argc, struct Mem *argv) static struct sql_func_dictionary dictionaries[] = { {"ABS", 1, 1, 0, true, 0, NULL}, + {"ARRAY", 1, SQL_MAX_FUNCTION_ARG, 0, true, 0, NULL}, {"AVG", 1, 1, SQL_FUNC_AGG, false, 0, NULL}, {"CHAR", 0, SQL_MAX_FUNCTION_ARG, 0, true, 0, NULL}, {"CHARACTER_LENGTH", 1, 1, 0, true, 0, NULL}, @@ -1948,6 +1969,7 @@ 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}, + {"ARRAY", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_ARRAY, func_array, NULL}, {"AVG", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, step_avg, fin_avg}, {"AVG", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, step_avg, fin_avg}, {"CHAR", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, charFunc, NULL}, @@ -2148,7 +2170,7 @@ is_upcast(int op, enum field_type a, enum field_type b) return is_exact(op, a, b) || (a == FIELD_TYPE_NUMBER && sql_type_is_numeric(b)) || (a == FIELD_TYPE_SCALAR && b != FIELD_TYPE_MAP && - b != FIELD_TYPE_ARRAY); + b != FIELD_TYPE_ARRAY) || a == FIELD_TYPE_ANY; } /** diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index c84bbe8fe..1d55a1209 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -3131,7 +3131,7 @@ mem_from_mp(struct Mem *mem, const char *buf, uint32_t *len) } void -mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var) +mem_encode_to_stream(const struct Mem *var, struct mpstream *stream) { assert(memIsValid(var)); switch (var->type) { @@ -3173,31 +3173,31 @@ mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var) } char * -sql_vdbe_mem_encode_tuple(struct Mem *fields, uint32_t field_count, - uint32_t *tuple_size, struct region *region) +mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size, + struct region *region) { size_t used = 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, field_count); - for (struct Mem *field = fields; field < fields + field_count; field++) - mpstream_encode_vdbe_mem(&stream, field); + mpstream_encode_array(&stream, count); + for (const struct Mem *mem = mems; mem < mems + count; mem++) + mem_encode_to_stream(mem, &stream); mpstream_flush(&stream); if (is_error) { diag_set(OutOfMemory, stream.pos - stream.buf, "mpstream_flush", "stream"); return NULL; } - *tuple_size = region_used(region) - used; - char *tuple = region_join(region, *tuple_size); - if (tuple == NULL) { - diag_set(OutOfMemory, *tuple_size, "region_join", "tuple"); + *size = region_used(region) - used; + char *array = region_join(region, *size); + if (array == NULL) { + diag_set(OutOfMemory, *size, "region_join", "array"); return NULL; } - mp_tuple_assert(tuple, tuple + *tuple_size); - return tuple; + mp_tuple_assert(array, array + *size); + return array; } /** @@ -3288,7 +3288,7 @@ port_vdbemem_get_msgpack(struct port *base, uint32_t *size) set_encode_error, &is_error); mpstream_encode_array(&stream, port->mem_count); for (uint32_t i = 0; i < port->mem_count && !is_error; i++) - mpstream_encode_vdbe_mem(&stream, (struct Mem *)port->mem + i); + mem_encode_to_stream((struct Mem *)port->mem + i, &stream); mpstream_flush(&stream); *size = region_used(region) - region_svp; if (is_error) diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h index a5b590cd3..afef6d842 100644 --- a/src/box/sql/mem.h +++ b/src/box/sql/mem.h @@ -994,23 +994,24 @@ int mem_from_mp(struct Mem *mem, const char *buf, uint32_t *len); /** - * Perform encoding memory variable to stream. + * Perform encoding of MEM to stream. + * + * @param var MEM to encode to stream. * @param stream Initialized mpstream encoder object. - * @param var Vdbe memory variable to encode with stream. */ void -mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var); +mem_encode_to_stream(const struct Mem *var, struct mpstream *stream); /** - * Perform encoding field_count Vdbe memory fields on region as - * msgpack array. - * @param fields The first Vdbe memory field to encode. - * @param field_count Count of fields to encode. - * @param[out] tuple_size Size of encoded tuple. + * Encode array of MEMs as msgpack array on region. + * + * @param mems array of MEMs to encode. + * @param count number of elements in the array. + * @param[out] size Size of encoded msgpack array. * @param region Region to use. * @retval NULL on error, diag message is set. - * @retval Pointer to valid tuple on success. + * @retval Pointer to valid msgpack array on success. */ char * -sql_vdbe_mem_encode_tuple(struct Mem *fields, uint32_t field_count, - uint32_t *tuple_size, struct region *region); +mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size, + struct region *region); diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index 4cae943d4..2b4b92e10 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -268,7 +268,7 @@ columnlist ::= tcons. CONFLICT DEFERRED END ENGINE FAIL IGNORE INITIALLY INSTEAD NO MATCH PLAN QUERY KEY OFFSET RAISE RELEASE REPLACE RESTRICT - RENAME CTIME_KW IF ENABLE DISABLE UUID + RENAME CTIME_KW IF ENABLE DISABLE UUID ARRAY . %wildcard WILDCARD. diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 323cbf04c..fd69afc74 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2024,8 +2024,7 @@ case OP_MakeRecord: { struct region *region = &fiber()->gc; size_t used = region_used(region); uint32_t tuple_size; - char *tuple = - sql_vdbe_mem_encode_tuple(pData0, nField, &tuple_size, region); + char *tuple = mem_encode_array(pData0, nField, &tuple_size, region); if (tuple == NULL) goto abort_due_to_error; if ((int64_t)tuple_size > db->aLimit[SQL_LIMIT_LENGTH]) @@ -3485,7 +3484,7 @@ case OP_Update: { mpstream_encode_array(&stream, 3); mpstream_encode_strn(&stream, "=", 1); mpstream_encode_uint(&stream, field_idx); - mpstream_encode_vdbe_mem(&stream, new_tuple + field_idx); + mem_encode_to_stream(new_tuple + field_idx, &stream); } mpstream_flush(&stream); if (is_error) { diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c index 8f7e28ffb..ae7783e4c 100644 --- a/src/box/sql/vdbeapi.c +++ b/src/box/sql/vdbeapi.c @@ -358,8 +358,8 @@ sql_stmt_result_to_msgpack(struct sql_stmt *stmt, uint32_t *tuple_size, struct region *region) { struct Vdbe *vdbe = (struct Vdbe *)stmt; - return sql_vdbe_mem_encode_tuple(vdbe->pResultSet, vdbe->nResColumn, - tuple_size, region); + return mem_encode_array(vdbe->pResultSet, vdbe->nResColumn, tuple_size, + region); } /* diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua index 2c0f687c0..44a2cc5e8 100755 --- a/test/sql-tap/array.test.lua +++ b/test/sql-tap/array.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(110) +test:plan(113) box.schema.func.create('A1', { language = 'Lua', @@ -979,6 +979,37 @@ test:do_catchsql_test( 1, "Failed to execute SQL statement: wrong arguments for function ZEROBLOB()" }) +box.execute([[CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, a ANY, + g UNSIGNED, t STRING, n NUMBER, f DOUBLE, + i INTEGER, b BOOLEAN, v VARBINARY, s SCALAR, + d DECIMAL, u UUID);]]) + +-- Make sure ARRAY() function works as intended. +test:do_execsql_test( + "array-13.1", + [[ + SELECT ARRAY(a, g, t, n, f, i, b, v, s, d, u) FROM t1 WHERE id = 1; + ]], { + 1, 1, '1', 1, 1, 1, true, '1', 1, require('decimal').new(1), + require('uuid').fromstr('11111111-1111-1111-1111-111111111111') + }) + +test:do_execsql_test( + "array-13.2", + [[ + SELECT ARRAY(1, true, 1.5, ARRAY('asd', x'32'), cast(1234 as DECIMAL)); + ]], { + 1, true, 1.5, 'asd', '2', require('decimal').new(1234) + }) + +test:do_execsql_test( + "array-13.3", + [[ + SELECT typeof(ARRAY(1)); + ]], { + "array" + }) + box.execute([[DROP TABLE t1;]]) box.execute([[DROP TABLE t;]]) -- 2.25.1
next prev parent reply other threads:[~2021-11-03 8:18 UTC|newest] Thread overview: 13+ messages / expand[flat|nested] mbox.gz Atom feed top 2021-11-03 8:17 [Tarantool-patches] [PATCH v1 0/2] Introduce field type ARRAY to SQL Mergen Imeev via Tarantool-patches 2021-11-03 8:17 ` [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type ARRAY Mergen Imeev via Tarantool-patches 2021-11-03 12:09 ` Mergen Imeev via Tarantool-patches 2021-11-03 13:53 ` Konstantin Osipov via Tarantool-patches 2021-11-03 14:27 ` Mergen Imeev via Tarantool-patches 2021-11-03 14:33 ` Konstantin Osipov via Tarantool-patches 2021-11-14 16:12 ` Vladislav Shpilevoy via Tarantool-patches 2021-11-15 16:38 ` Mergen Imeev via Tarantool-patches 2021-11-03 8:17 ` Mergen Imeev via Tarantool-patches [this message] 2021-11-03 12:11 ` [Tarantool-patches] [PATCH v1 2/2] sql: introduce ARRAY() function Mergen Imeev via Tarantool-patches 2021-11-14 16:12 ` Vladislav Shpilevoy via Tarantool-patches 2021-11-15 16:40 ` Mergen Imeev via Tarantool-patches 2021-11-18 21:19 ` [Tarantool-patches] [PATCH v1 0/2] Introduce field type ARRAY to SQL Vladislav Shpilevoy 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=6675fda5d666534b8f1b39a2a7db27fae6d8ef40.1635927295.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 2/2] sql: introduce ARRAY() function' \ /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