[Tarantool-patches] [PATCH v1 2/2] sql: introduce MAP() function
imeevma at tarantool.org
imeevma at tarantool.org
Thu Nov 11 14:37:52 MSK 2021
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
More information about the Tarantool-patches
mailing list