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 856166ECE3; Wed, 3 Nov 2021 15:11:44 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 856166ECE3 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1635941504; bh=8KisDRhFuTJWvL6Kqjsl1VxGZnRGolHkt+x4ziEPvGc=; h=Date:To:References:In-Reply-To:Subject:List-Id:List-Unsubscribe: List-Archive:List-Post:List-Help:List-Subscribe:From:Reply-To: From; b=vpWoRhibuxQBuU81Zs3fEDWD5fX4yX5HxxB6pZaZltjVvvGR4lA2o+AgM9ab40wb6 MXBro4LerSakTaqQ9Rs9A4SHjKo0A3KfG5RmmoLwybW0BfEJpqteHMGp5c4jplV1GJ Mhzw/ETuzqHATHIse6s8WnPFANkI71goi2lij6wI= 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 63E766ECE3 for ; Wed, 3 Nov 2021 15:11:43 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 63E766ECE3 Received: by smtpng1.m.smailru.net with esmtpa (envelope-from ) id 1miF7G-0001Fi-KO; Wed, 03 Nov 2021 15:11:43 +0300 Date: Wed, 3 Nov 2021 15:11:41 +0300 To: v.shpilevoy@tarantool.org, tarantool-patches@dev.tarantool.org Message-ID: <20211103121141.GB135836@tarantool.org> References: <6675fda5d666534b8f1b39a2a7db27fae6d8ef40.1635927295.git.imeevma@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <6675fda5d666534b8f1b39a2a7db27fae6d8ef40.1635927295.git.imeevma@gmail.com> X-4EC0790: 10 X-7564579A: EEAE043A70213CC8 X-77F55803: 4F1203BC0FB41BD9F9D976862A30D4FE24BA5635EEB592969D7326BCF5538DC0182A05F5380850405A3D360D64B89C4153BEB218DB202948504C423AF49F5C70BA48044AFEE6107A X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7952C4D7BD0BF3359EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637222645A8BD7B05AE8638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D899CB1D4BED8B26AAA57627AB039294FF117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BAA867293B0326636D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B6B1CFA6D474D4A6A4089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-B7AD71C0: AC4F5C86D027EB782CDD5689AFBDA7A213B5FB47DCBC3458834459D11680B5054BFDB7F919355E0749116C76F0AA96A7 X-C1DE0DAB: 0D63561A33F958A504537484460EA63E2DEB843B35F7084A57C6FE5A57745FD0D59269BC5F550898D99A6476B3ADF6B47008B74DF8BB9EF7333BD3B22AA88B938A852937E12ACA75C69C5C0DDE134364410CA545F18667F91A7EA1CDA0B5A7A0 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34128DEC38EFF4BE5AA924A6B2F90AC0874370465AB71DB1BE8AB2BB63D895A5E87C8F4A5499151E781D7E09C32AA3244CD8F0719260182F52AA5AF21DF98944FA64EE5813BBCA3A9D729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2bioj3JiVFN03mSUPTeJclo3lZA== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D7649926CD9B71DD99A1409EE65E9460983D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: Re: [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: Mergen Imeev Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" In the new version of prevoius patch I changed the way ARRAY values are showed in results of sql-tap tests. Diff and new patch below. On Wed, Nov 03, 2021 at 11:17:52AM +0300, Mergen Imeev via Tarantool-patches wrote: Diff: diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua index 918854b47..4d292bcd8 100755 --- a/test/sql-tap/array.test.lua +++ b/test/sql-tap/array.test.lua @@ -990,16 +990,16 @@ test:do_execsql_test( [[ 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') + {{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)); + SELECT ARRAY(1, true, 1.5e0, ARRAY('asd', x'32'), 1234.0); ]], { - 1, true, 1.5, 'asd', '2', require('decimal').new(1234) + {1, true, 1.5, {'asd', '2'}, require('decimal').new(1234)} }) test:do_execsql_test( New patch: commit 528165624bc88897ff88705a1299e7a7f50b4d4a Author: Mergen Imeev Date: Tue Nov 2 15:35:55 2021 +0300 sql: introduce ARRAY() function 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']] ... ``` 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 c93125a8b..a3ba5da08 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 0c4e38557..02916cec3 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2036,8 +2036,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]) @@ -3497,7 +3496,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 486f797fb..d30e216cb 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 746462ec6..4d292bcd8 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.5e0, ARRAY('asd', x'32'), 1234.0); + ]], { + {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;]])