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 B42A56ECE3; Wed, 3 Nov 2021 11:18:52 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org B42A56ECE3 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1635927532; bh=+YPFe06iR90O+1Y+zef8sNR7TzJNg+qvIxzo3WXvQtI=; 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=VAurkDVTcDReiTiKB8iAuogg3OjZLtkaQsfAwlOvf+jr8/em8pWUVO1bWDL2BFmrj GbIEo61dg6D9mPlbOWWFsPgI2oAM9vouBYi1majKEbz0d6DzZSgsSUP54wPGV/FzI4 Ljhu3W/14QJXzkdKwRakpdiRdPPhH0X99msDCzAY= Received: from smtpng1.i.mail.ru (smtpng1.i.mail.ru [94.100.181.251]) (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 7A66A6EC41 for ; Wed, 3 Nov 2021 11:17:53 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 7A66A6EC41 Received: by smtpng1.m.smailru.net with esmtpa (envelope-from ) id 1miBSy-0000wN-Qr; Wed, 03 Nov 2021 11:17:53 +0300 To: v.shpilevoy@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Wed, 3 Nov 2021 11:17:52 +0300 Message-Id: <6675fda5d666534b8f1b39a2a7db27fae6d8ef40.1635927295.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: 78E4E2B564C1792B X-77F55803: 4F1203BC0FB41BD98893113B6235BE91332DEC9885600AEC2E9AEA2ED6EFE527182A05F538085040E375E724A3543E08E53B9B8DD5D11F56C7A62FA56338F9F5FC0408055AC63180 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7571C18AED7CB6805EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F790063714A07403F1CB524B8638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D8AC430540B3C4CD7C36C93A269ADE3C3D117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8B974A882099E279BDA471835C12D1D977C4224003CC8364762BB6847A3DEAEFB0F43C7A68FF6260569E8FC8737B5C2249EC8D19AE6D49635B68655334FD4449CB9ECD01F8117BC8BEAAAE862A0553A39223F8577A6DFFEA7CFA80D66F452D417A43847C11F186F3C59DAA53EE0834AAEE X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C4C7A0BC55FA0FE5FCB5E2348A7CA18DD59C99466E347F21CA21C35A8C8A89A3C8B1881A6453793CE9C32612AADDFBE061C801D989C91DAA47C32612AADDFBE0614AFB60FD1831C04C9510FB958DCE06DB6ED91DBE5ABE359AA4AB5AF8DDD74F43B936CB490224F2464EEA7BD89490CAC0EDDA962BC3F61961 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34505665BFD4C7070592B3BF5B150DD2389F4CC65EB4567370FD44BF4F917D2C28E5F432FC1D0E20331D7E09C32AA3244CEA68C65D7F1CE9ACB1647714C8ACCE143A76366E8A9DE7CA729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2bioj3JiVFN03mSUn1fCwkLUXPQ== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D0510FD38B94D34B900BBBC00F2C678B483D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 2/2] sql: introduce ARRAY() function 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" 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