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 6FBF46F3E5; Thu, 11 Nov 2021 14:38:43 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 6FBF46F3E5 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1636630723; bh=tqho23WmV8pBQ5uPQsSFvtQRxznzMsDTMzJhcLtxd7Q=; 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=jz0OM1qqy98M7AnOWJmz/5k7MCpE11mC9ot1mfxdTQemlv9eq6e7m9E4zkREUT4QC YCmmRVpRZSeQuK+lWXj/+oC0MsDsvVyr8BiQP87eDOS26ah3g5Frs5B24lWwv/XqDK DYmBSL6Pikf8Ale+8WFIcQca9Rer/jBl3C2Uwgrw= 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 EFBE66F3E5 for ; Thu, 11 Nov 2021 14:37:52 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org EFBE66F3E5 Received: by smtpng1.m.smailru.net with esmtpa (envelope-from ) id 1ml8Ou-00016p-Aw; Thu, 11 Nov 2021 14:37:52 +0300 To: v.shpilevoy@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Thu, 11 Nov 2021 14:37:52 +0300 Message-Id: <6b02d7952684831268f52a8bca407204ec0471ae.1636630534.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: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD9731B3922EC06397911A6D4ED1A8575E7BD5B9E03AA43849A00894C459B0CD1B97D29F254B07CA2F3D81239A78FFED9687B241399135D2ADC53A0125EEE708EC8 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7484B509D84968742EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F79006372D22CC9620B7D5068638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D8BB80F8AC907FAB03AAFD9A778CD33373117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BF1175FABE1C0F9B6A471835C12D1D977C4224003CC8364762BB6847A3DEAEFB0F43C7A68FF6260569E8FC8737B5C2249EC8D19AE6D49635B68655334FD4449CB9ECD01F8117BC8BEAAAE862A0553A39223F8577A6DFFEA7C289736CE4F78F08343847C11F186F3C59DAA53EE0834AAEE X-B7AD71C0: AC4F5C86D027EB782CDD5689AFBDA7A213B5FB47DCBC3458834459D11680B505A26DD5A60D971B38B73F4AD7E40AE671 X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C4C7A0BC55FA0FE5FCE94D83A6518677D181F067820CF2D05AF7C1C7DB7DF15BE6B1881A6453793CE9C32612AADDFBE061C801D989C91DAA47C32612AADDFBE0614AFB60FD1831C04C9510FB958DCE06DB6ED91DBE5ABE359A48600B0EAD68145B8D1F0E447259586B93EDB24507CE13387DFF0A840B692CF8 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34CC972AA0E2E780C450D410A045D76766BC87DE41AF1A564B59895D18837FC7B348C68A7F16AA555E1D7E09C32AA3244C47B39B220E3E6FC843712E6DF5F1A2E1E8FBBEFAE1C4874C729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2bioj4t8MBgWr8bL6Vielg2yHnA== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D6D9778F2E3FF81F6AD802C86EAA77A0F83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 2/2] sql: introduce MAP() 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 #4763 @TarantoolBot document Title: SQL built-in function MAP() The SQL built-in function MAP() is now available. It takes one or more arguments of any type and returns a value of type MAP. The return value uses the arguments that were in odd places as keys, and the next argument after the key is treated as the value of that key. For an odd number of arguments, the value of the last key is NULL. Only INTEGER, STRING and UUID values can be keys. Example: ``` tarantool> box.execute([[SELECT map(1, array('a', true), 'asd', x'31');]]) --- - metadata: - name: COLUMN_1 type: map rows: - [{1: ['a', true], 'asd': '1'}] ... ``` --- .../gh-4763-introduce-map-to-sql.md | 4 + src/box/sql/func.c | 22 ++++ src/box/sql/mem.c | 41 ++++++ src/box/sql/mem.h | 17 +++ src/box/sql/parse.y | 2 +- test/sql-tap/map.test.lua | 121 +++++++++++++++++- 6 files changed, 205 insertions(+), 2 deletions(-) create mode 100644 changelogs/unreleased/gh-4763-introduce-map-to-sql.md diff --git a/changelogs/unreleased/gh-4763-introduce-map-to-sql.md b/changelogs/unreleased/gh-4763-introduce-map-to-sql.md new file mode 100644 index 000000000..08f2dc538 --- /dev/null +++ b/changelogs/unreleased/gh-4763-introduce-map-to-sql.md @@ -0,0 +1,4 @@ +## feature/core + + * Field type MAP is now available in SQL. The MAP() function can be used to + create values of type MAP in SQL (gh-4763). diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 8bf3e473d..2be73b278 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -252,6 +252,26 @@ func_array(struct sql_context *ctx, int argc, struct Mem *argv) region_truncate(region, svp); } +/** + * 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_map(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_map(argv, argc, &size, region); + if (array == NULL || mem_copy_map(ctx->pOut, array, size) != 0) + ctx->is_aborted = true; + region_truncate(region, svp); +} + static const unsigned char * mem_as_ustr(struct Mem *mem) { @@ -1909,6 +1929,7 @@ static struct sql_func_dictionary dictionaries[] = { {"LIKELY", 1, 1, SQL_FUNC_UNLIKELY, true, 0, NULL}, {"LOWER", 1, 1, SQL_FUNC_DERIVEDCOLL | SQL_FUNC_NEEDCOLL, true, 0, NULL}, + {"MAP", 1, SQL_MAX_FUNCTION_ARG, 0, true, 0, NULL}, {"MAX", 1, 1, SQL_FUNC_MAX | SQL_FUNC_AGG | SQL_FUNC_NEEDCOLL, false, 0, NULL}, {"MIN", 1, 1, SQL_FUNC_MIN | SQL_FUNC_AGG | SQL_FUNC_NEEDCOLL, false, 0, @@ -2034,6 +2055,7 @@ static struct sql_func_definition definitions[] = { NULL}, {"LOWER", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, LowerICUFunc, NULL}, + {"MAP", -1, {field_type_MAX}, FIELD_TYPE_MAP, func_map, NULL}, {"MAX", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, step_minmax, NULL}, {"MAX", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, step_minmax, NULL}, diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index f5e8c11cc..b28423086 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -3211,6 +3211,47 @@ mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size, return array; } +char * +mem_encode_map(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_map(&stream, (count + 1) / 2); + for (uint32_t i = 0; i < count / 2; ++i) { + const struct Mem *key = &mems[2 * i]; + const struct Mem *value = &mems[2 * i + 1]; + if ((key->type & (MEM_TYPE_UINT | MEM_TYPE_INT | MEM_TYPE_UUID | + MEM_TYPE_STR)) == 0) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + mem_str(key), "integer, string or uuid"); + return NULL; + } + mem_encode_to_stream(key, &stream); + mem_encode_to_stream(value, &stream); + } + if (count % 2 == 1) { + mem_encode_to_stream(&mems[count - 1], &stream); + mpstream_encode_nil(&stream); + } + mpstream_flush(&stream); + if (is_error) { + diag_set(OutOfMemory, stream.pos - stream.buf, + "mpstream_flush", "stream"); + return NULL; + } + *size = region_used(region) - used; + char *map = region_join(region, *size); + if (map == NULL) { + diag_set(OutOfMemory, *size, "region_join", "map"); + return NULL; + } + return map; +} + /** * Allocate a sequence of initialized vdbe memory registers * on region. diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h index 7ddf81236..64f978a1f 100644 --- a/src/box/sql/mem.h +++ b/src/box/sql/mem.h @@ -1019,3 +1019,20 @@ mem_encode_to_stream(const struct Mem *var, struct mpstream *stream); char * mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size, struct region *region); + +/** + * Encode array of MEMs as msgpack map on region. Values in even position are + * treated as keys in MAP, values in odd position are treated as values in MAP. + * If the number of MEMs in the array is odd, the value of the last key will be + * NULL. + * + * @param mems array of MEMs to encode. + * @param count number of elements in the array. + * @param[out] size Size of encoded msgpack map. + * @param region Region to use. + * @retval NULL on error, diag message is set. + * @retval Pointer to valid msgpack map on success. + */ +char * +mem_encode_map(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 e27c1804b..1b2c1597c 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 ARRAY + RENAME CTIME_KW IF ENABLE DISABLE UUID ARRAY MAP . %wildcard WILDCARD. diff --git a/test/sql-tap/map.test.lua b/test/sql-tap/map.test.lua index 2be82db61..f487fef55 100755 --- a/test/sql-tap/map.test.lua +++ b/test/sql-tap/map.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(110) +test:plan(123) box.schema.func.create('M1', { language = 'Lua', @@ -981,6 +981,125 @@ test:do_catchsql_test( 1, "Failed to execute SQL statement: wrong arguments for function ZEROBLOB()" }) +-- Make sure ARRAY() function works as intended. +test:do_execsql_test( + "map-13.1", + [[ + SELECT MAP('a', a, 'g', g, 't', t, 'n', n, 'f', f, 'i', i, 'b', b, + 'v', v, 's', s, 'd', d, 'u', u) FROM t1 WHERE id = 1; + ]], { + {t = "1", f = 1, n = 1, v = "1", g = 1, b = true, s = 1, + d = require('decimal').new(1), a = {a = 1}, i = 1, + u = require('uuid').fromstr('11111111-1111-1111-1111-111111111111')} + }) + +test:do_execsql_test( + "map-13.2", + [[ + SELECT MAP('q', 1, 'w', true, 'e', 1.5e0, 'r', ARRAY('asd', x'32'), + 't', 1234.0); + ]], { + {w = true, e = 1.5, r = {'asd', '2'}, t = require('decimal').new(1234), + q = 1} + }) + +test:do_execsql_test( + "map-13.3", + [[ + SELECT typeof(MAP(1, 1)); + ]], { + "map" + }) + +-- Make sure MAP() accepts only INTEGER, STRING and UUID as keys. +test:do_execsql_test( + "map-13.4", + [[ + SELECT MAP(1, 1); + ]], { + {[1] = 1} + }) + +test:do_execsql_test( + "map-13.5", + [[ + SELECT MAP(-1, 1); + ]], { + {[-1] = 1} + }) + +test:do_execsql_test( + "map-13.6", + [[ + SELECT MAP('a', 1); + ]], { + {a = 1} + }) + +test:do_execsql_test( + "map-13.6", + [[ + SELECT typeof(MAP(UUID(), 1)); + ]], { + "map" + }) + +test:do_catchsql_test( + "map-13.7", + [[ + SELECT MAP(1.5e0, 1); + ]], { + 1, + "Type mismatch: can not convert double(1.5) to integer, string or uuid" + }) + +test:do_catchsql_test( + "map-13.8", + [[ + SELECT MAP(1.5, 1); + ]], { + 1, + "Type mismatch: can not convert decimal(1.5) to integer, string or uuid" + }) + +test:do_catchsql_test( + "map-13.9", + [[ + SELECT MAP(x'33', 1); + ]], { + 1, + "Type mismatch: can not convert varbinary(x'33') to integer, string ".. + "or uuid" + }) + +test:do_catchsql_test( + "map-13.10", + [[ + SELECT MAP(ARRAY(1, 2, 3), 1); + ]], { + 1, + "Type mismatch: can not convert array([1, 2, 3]) to integer, string or uuid" + }) + +test:do_catchsql_test( + "map-13.11", + [[ + SELECT MAP(MAP('a', 1), 1); + ]], { + 1, + 'Type mismatch: can not convert map({"a": 1}) to integer, string '.. + 'or uuid' + }) + +-- Metatypes with base types INTEGER, STRING and UUID can also be keys. +test:do_execsql_test( + "map-13.12", + [[ + SELECT MAP(CAST(1 AS ANY), 1); + ]], { + {[1] = 1} + }) + box.execute([[DROP TABLE t1;]]) box.execute([[DROP TABLE t;]]) -- 2.25.1