From: Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org> To: Vladislav Shpilevoy <v.shpilevoy@tarantool.org> Cc: tarantool-patches@dev.tarantool.org Subject: Re: [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type MAP Date: Mon, 15 Nov 2021 19:45:43 +0300 [thread overview] Message-ID: <20211115164543.GB171607@tarantool.org> (raw) In-Reply-To: <a77ff686-3b8b-b081-59fd-49bcc4de43eb@tarantool.org> Thank you for the review! My answers, diff and new patch below. There will be missing part of diff since I applied it during resolution of merge-conflict. Also, I dropped second patch on the branch. On Sun, Nov 14, 2021 at 05:24:21PM +0100, Vladislav Shpilevoy wrote: > Thanks for the patch! > > See 5 comments below. > > On 11.11.2021 12:37, imeevma@tarantool.org wrote: > > This patch introduces MAP to SQL. After this patch, all SQL operations > > and built-in functions should work correctly with MAP values. However, > > there is currently no way to create MAP values using only SQL tools. > > > > Part of #4763 > > > > @TarantoolBot document > > Title: Field type MAP in SQL > > > > Properties of type MAP in SQL: > > 1) a value ofttype MAP can be implicitly and explicitly cast only to > > ANY; > > 1. ofttype -> of type. > Fixed. > > 2) only a value of type ANY with primitive type MAP can be explicitly > > cast to MAP; > > 3) a value of any other type cannot be implicitly cast to MAP; > > 4) a value of type MAP cannot participate in arithmetic, bitwise, > > comparison, and concationation operations. > > 2. concationation -> concatenation. > Fixed. > 3. You need to add a changelog file. For the arrays patch too. > Added here and in patch for ARRAY. > > @@ -3258,6 +3268,19 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat) > > luaL_setarrayhint(L, -1); > > return; > > } > > + case MEM_TYPE_MAP: { > > + const char *data = mem->z; > > + uint32_t size = mp_decode_map(&data); > > + lua_createtable(L, 0, size); > > + for (uint32_t i = 0; i < size; i++) { > > + luamp_decode(L, luaL_msgpack_default, &data); > > + luamp_decode(L, luaL_msgpack_default, &data); > > + lua_settable(L, -3); > > + } > > + if (luaL_msgpack_default->decode_save_metatables) > > + luaL_setmaphint(L, -1); > > 4. The same as for arrays. For both of them you can call luamp_decode > on the root. > Fixed. This part will be missing from diff since I applied it during rebase to branch with ARRAY. > > diff --git a/test/sql-tap/map.test.lua b/test/sql-tap/map.test.lua > > new file mode 100755 > > index 000000000..2be82db61 > > --- /dev/null > > +++ b/test/sql-tap/map.test.lua > > @@ -0,0 +1,987 @@ > > +#!/usr/bin/env tarantool > > +local test = require("sqltester") > > +test:plan(110) > > + > > +box.schema.func.create('M1', { > > + language = 'Lua', > > + body = 'function(a, b) return {[tostring(a)] = b} end', > > + returns = 'map', > > + param_list = {'any', 'any'}, > > + exports = {'LUA', 'SQL'} > > +}); > > + > > +box.schema.func.create('M2', { > > + language = 'Lua', > > + body = 'function(a, b, c, d) return '.. > > + '{[tostring(a)] = b, [tostring(c)] = d} end', > > + returns = 'map', > > + param_list = {'any', 'any', 'any', 'any'}, > > + exports = {'LUA', 'SQL'} > > +}); > > + > > +box.schema.func.create('M3', { > > + language = 'Lua', > > + body = 'function(a, b, c, d, e, f) return '.. > > + '{[tostring(a)] = b, [tostring(c)] = d, [tostring(e)] = f} end', > > 5. Why do you need the tostrings? Keys are free to have any type in > MessagePack. In Lua too. Can you add tests for non-string keys? > I did this to avoid calling metatable(). Fixed. > > + returns = 'map', > > + param_list = {'any', 'any', 'any', 'any', 'any', 'any'}, > > + exports = {'LUA', 'SQL'} > > +}); Diff: diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index 2ec218d1d..e25170aa8 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -1169,14 +1169,6 @@ bool_to_str0(struct Mem *mem) return mem_copy_str0(mem, str); } -static inline int -map_to_str0(struct Mem *mem) -{ - assert(mem->type == MEM_TYPE_MAP); - const char *str = mp_str(mem->z); - return mem_copy_str0(mem, str); -} - static inline int uuid_to_str0(struct Mem *mem) { diff --git a/test/sql-tap/map.test.lua b/test/sql-tap/map.test.lua index ec6d9023c..1afbb2b1d 100755 --- a/test/sql-tap/map.test.lua +++ b/test/sql-tap/map.test.lua @@ -4,7 +4,8 @@ test:plan(110) box.schema.func.create('M1', { language = 'Lua', - body = 'function(a, b) return {[tostring(a)] = b} end', + body = 'function(a, b) local m = {[a] = b} '.. + 'return setmetatable(m, { __serialize = "map" }) end', returns = 'map', param_list = {'any', 'any'}, exports = {'LUA', 'SQL'} @@ -12,8 +13,8 @@ box.schema.func.create('M1', { box.schema.func.create('M2', { language = 'Lua', - body = 'function(a, b, c, d) return '.. - '{[tostring(a)] = b, [tostring(c)] = d} end', + body = 'function(a, b, c, d) local m = {[a] = b, [c] = d} '.. + 'return setmetatable(m, { __serialize = "map" }) end', returns = 'map', param_list = {'any', 'any', 'any', 'any'}, exports = {'LUA', 'SQL'} @@ -21,8 +22,8 @@ box.schema.func.create('M2', { box.schema.func.create('M3', { language = 'Lua', - body = 'function(a, b, c, d, e, f) return '.. - '{[tostring(a)] = b, [tostring(c)] = d, [tostring(e)] = f} end', + body = 'function(a, b, c, d, e, f) local m = {[a] = b, [c] = d, [e] = f} '.. + 'return setmetatable(m, { __serialize = "map" }) end', returns = 'map', param_list = {'any', 'any', 'any', 'any', 'any', 'any'}, exports = {'LUA', 'SQL'} @@ -293,7 +294,7 @@ test:do_execsql_test( SELECT a FROM t1 WHERE a IS NOT NULL; ]], { {a = 1}, - {['1'] = 2} + {[1] = 2} }) test:do_catchsql_test( @@ -301,7 +302,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(g) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to unsigned' + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' }) test:do_catchsql_test( @@ -309,7 +310,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(t) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to string' + 1, 'Type mismatch: can not convert map({1: 2}) to string' }) test:do_catchsql_test( @@ -317,7 +318,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(n) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to number' + 1, 'Type mismatch: can not convert map({1: 2}) to number' }) test:do_catchsql_test( @@ -325,7 +326,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(f) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to double' + 1, 'Type mismatch: can not convert map({1: 2}) to double' }) test:do_catchsql_test( @@ -333,7 +334,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(i) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to integer' + 1, 'Type mismatch: can not convert map({1: 2}) to integer' }) test:do_catchsql_test( @@ -341,7 +342,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(b) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to boolean' + 1, 'Type mismatch: can not convert map({1: 2}) to boolean' }) test:do_catchsql_test( @@ -349,7 +350,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(v) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to varbinary' + 1, 'Type mismatch: can not convert map({1: 2}) to varbinary' }) test:do_catchsql_test( @@ -357,7 +358,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(s) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to scalar' + 1, 'Type mismatch: can not convert map({1: 2}) to scalar' }) test:do_catchsql_test( @@ -365,7 +366,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(d) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to decimal' + 1, 'Type mismatch: can not convert map({1: 2}) to decimal' }) test:do_catchsql_test( @@ -373,7 +374,7 @@ test:do_catchsql_test( [[ INSERT INTO t1(u) VALUES(m1(1, 2)); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to uuid' + 1, 'Type mismatch: can not convert map({1: 2}) to uuid' }) -- Make sure nothing can be implicitly cast to MAP. @@ -475,7 +476,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) + 1; ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to integer, '.. + 1, 'Type mismatch: can not convert map({1: 2}) to integer, '.. "decimal or double" }) @@ -484,7 +485,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) - 1; ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to integer, '.. + 1, 'Type mismatch: can not convert map({1: 2}) to integer, '.. "decimal or double" }) @@ -493,7 +494,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) * 1; ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to integer, '.. + 1, 'Type mismatch: can not convert map({1: 2}) to integer, '.. "decimal or double" }) @@ -502,7 +503,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) / 1; ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to integer, '.. + 1, 'Type mismatch: can not convert map({1: 2}) to integer, '.. "decimal or double" }) @@ -511,7 +512,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) % 1; ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to integer' + 1, 'Type mismatch: can not convert map({1: 2}) to integer' }) test:do_catchsql_test( @@ -519,7 +520,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) >> 1; ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to unsigned' + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' }) test:do_catchsql_test( @@ -527,7 +528,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) << 1; ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to unsigned' + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' }) test:do_catchsql_test( @@ -535,7 +536,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) | 1; ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to unsigned' + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' }) test:do_catchsql_test( @@ -543,7 +544,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) & 1; ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to unsigned' + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' }) test:do_catchsql_test( @@ -551,7 +552,7 @@ test:do_catchsql_test( [[ SELECT ~m1(1, 2); ]], { - 1, 'Type mismatch: can not convert map({"1": 2}) to unsigned' + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' }) test:do_catchsql_test( @@ -559,7 +560,7 @@ test:do_catchsql_test( [[ SELECT m1(1, 2) || 'asd'; ]], { - 1, 'Inconsistent types: expected string or varbinary got map({"1": 2})' + 1, 'Inconsistent types: expected string or varbinary got map({1: 2})' }) -- Make sure MAP is not comparable. New patch: commit fec5aa8d4f90609bc210d557d6eb15afd4506f48 Author: Mergen Imeev <imeevma@gmail.com> Date: Wed Nov 3 13:42:53 2021 +0300 sql: introduce field type MAP This patch introduces MAP to SQL. After this patch, all SQL operations and built-in functions should work correctly with MAP values. However, there is currently no way to create MAP values using only SQL tools. Part of #4763 @TarantoolBot document Title: Field type MAP in SQL Properties of type MAP in SQL: 1) a value of type MAP can be implicitly and explicitly cast only to ANY; 2) only a value of type ANY with primitive type MAP can be explicitly cast to MAP; 3) a value of any other type cannot be implicitly cast to MAP; 4) a value of type MAP cannot participate in arithmetic, bitwise, comparison, and concatination operations. diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c index c7e7d4e8d..30bd6f0d8 100644 --- a/extra/mkkeywordhash.c +++ b/extra/mkkeywordhash.c @@ -125,6 +125,7 @@ static Keyword aKeywordTable[] = { { "LEFT", "TK_JOIN_KW", true }, { "LIKE", "TK_LIKE_KW", true }, { "LIMIT", "TK_LIMIT", false }, + { "MAP", "TK_MAP", true }, { "MATCH", "TK_MATCH", true }, { "NATURAL", "TK_JOIN_KW", true }, { "NO", "TK_NO", false }, diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 20d62bce7..cfc5149c9 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -1370,6 +1370,7 @@ quoteFunc(struct sql_context *context, int argc, const struct Mem *argv) context->is_aborted = true; break; } + case MEM_TYPE_MAP: case MEM_TYPE_ARRAY: { char *buf = NULL; int size = mp_snprint(buf, 0, argv[0].z) + 1; @@ -1383,8 +1384,7 @@ quoteFunc(struct sql_context *context, int argc, const struct Mem *argv) mem_set_str0_allocated(context->pOut, buf); break; } - case MEM_TYPE_BIN: - case MEM_TYPE_MAP: { + case MEM_TYPE_BIN: { const char *zBlob = argv[0].z; int nBlob = argv[0].n; uint32_t size = 2 * nBlob + 3; diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index e18bb24a2..e25170aa8 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -199,6 +199,8 @@ mem_type_class_to_str(const struct Mem *mem) return "uuid"; case MEM_TYPE_ARRAY: return "array"; + case MEM_TYPE_MAP: + return "map"; default: break; } @@ -487,6 +489,12 @@ mem_set_map_allocated(struct Mem *mem, char *value, uint32_t size) set_msgpack_value(mem, value, size, 0, MEM_TYPE_MAP); } +int +mem_copy_map(struct Mem *mem, const char *value, uint32_t size) +{ + return mem_copy_bytes(mem, value, size, MEM_TYPE_MAP); +} + void mem_set_array_ephemeral(struct Mem *mem, char *value, uint32_t size) { @@ -1161,14 +1169,6 @@ bool_to_str0(struct Mem *mem) return mem_copy_str0(mem, str); } -static inline int -map_to_str0(struct Mem *mem) -{ - assert(mem->type == MEM_TYPE_MAP); - const char *str = mp_str(mem->z); - return mem_copy_str0(mem, str); -} - static inline int uuid_to_str0(struct Mem *mem) { @@ -1270,8 +1270,6 @@ mem_to_str(struct Mem *mem) return bool_to_str0(mem); case MEM_TYPE_BIN: return bin_to_str(mem); - case MEM_TYPE_MAP: - return map_to_str0(mem); case MEM_TYPE_UUID: return uuid_to_str0(mem); case MEM_TYPE_DEC: @@ -1360,6 +1358,11 @@ mem_cast_explicit(struct Mem *mem, enum field_type type) return -1; mem->flags &= ~MEM_Any; return 0; + case FIELD_TYPE_MAP: + if (mem->type != MEM_TYPE_MAP) + return -1; + mem->flags &= ~MEM_Any; + return 0; case FIELD_TYPE_SCALAR: if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0) return -1; @@ -3083,9 +3086,9 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat) break; case MEM_TYPE_STR: case MEM_TYPE_BIN: - case MEM_TYPE_MAP: lua_pushlstring(L, mem->z, mem->n); break; + case MEM_TYPE_MAP: case MEM_TYPE_ARRAY: luamp_decode(L, luaL_msgpack_default, (const char **)&mem->z); @@ -3220,8 +3223,10 @@ port_lua_get_vdbemem(struct port *base, uint32_t *size) field.sval.len) != 0) goto error; break; + case MP_MAP: case MP_ARRAY: { size_t used = region_used(region); + bool is_map = field.type == MP_MAP; struct mpstream stream; bool is_error = false; mpstream_init(&stream, region, region_reserve_cb, @@ -3244,7 +3249,9 @@ port_lua_get_vdbemem(struct port *base, uint32_t *size) "raw"); goto error; } - if (mem_copy_array(&val[i], raw, size) != 0) + int rc = is_map ? mem_copy_map(&val[i], raw, size) : + mem_copy_array(&val[i], raw, size); + if (rc != 0) goto error; region_truncate(region, used); break; diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h index 41e8d8fa6..11834db20 100644 --- a/src/box/sql/mem.h +++ b/src/box/sql/mem.h @@ -398,6 +398,10 @@ mem_set_map_static(struct Mem *mem, char *value, uint32_t size); void mem_set_map_allocated(struct Mem *mem, char *value, uint32_t size); +/** Copy MAP value to a newly allocated memory. The MEM type becomes MAP. */ +int +mem_copy_map(struct Mem *mem, const char *value, uint32_t size); + /** * Clear MEM and set it to ARRAY. The binary value belongs to another object. * The binary value must be msgpack of ARRAY type. diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index c93125a8b..548004252 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -1843,6 +1843,7 @@ typedef(A) ::= VARBINARY . { A.type = FIELD_TYPE_VARBINARY; } typedef(A) ::= UUID . { A.type = FIELD_TYPE_UUID; } typedef(A) ::= ANY . { A.type = FIELD_TYPE_ANY; } typedef(A) ::= ARRAY . { A.type = FIELD_TYPE_ARRAY; } +typedef(A) ::= MAP . { A.type = FIELD_TYPE_MAP; } /** * Time-like types are temporary disabled, until they are diff --git a/test/box/function1.result b/test/box/function1.result index a1c89850d..334417be7 100644 --- a/test/box/function1.result +++ b/test/box/function1.result @@ -524,11 +524,6 @@ box.execute('SELECT lua(\'return 1 + 1\')') rows: - [2] ... -box.execute('SELECT lua(\'return box.cfg\')') ---- -- null -- 'Failed to execute SQL statement: Unsupported type passed from Lua' -... box.execute('SELECT lua(\'return box.cfg()\')') --- - null diff --git a/test/box/function1.test.lua b/test/box/function1.test.lua index e635b6e18..5b762d08a 100644 --- a/test/box/function1.test.lua +++ b/test/box/function1.test.lua @@ -187,7 +187,6 @@ box.func.SUMMARIZE:drop() -- gh-4113: Valid method to use Lua from SQL -- box.execute('SELECT lua(\'return 1 + 1\')') -box.execute('SELECT lua(\'return box.cfg\')') box.execute('SELECT lua(\'return box.cfg()\')') box.execute('SELECT lua(\'return box.cfg.memtx_memory\')') diff --git a/test/sql-tap/map.test.lua b/test/sql-tap/map.test.lua new file mode 100755 index 000000000..1afbb2b1d --- /dev/null +++ b/test/sql-tap/map.test.lua @@ -0,0 +1,988 @@ +#!/usr/bin/env tarantool +local test = require("sqltester") +test:plan(110) + +box.schema.func.create('M1', { + language = 'Lua', + body = 'function(a, b) local m = {[a] = b} '.. + 'return setmetatable(m, { __serialize = "map" }) end', + returns = 'map', + param_list = {'any', 'any'}, + exports = {'LUA', 'SQL'} +}); + +box.schema.func.create('M2', { + language = 'Lua', + body = 'function(a, b, c, d) local m = {[a] = b, [c] = d} '.. + 'return setmetatable(m, { __serialize = "map" }) end', + returns = 'map', + param_list = {'any', 'any', 'any', 'any'}, + exports = {'LUA', 'SQL'} +}); + +box.schema.func.create('M3', { + language = 'Lua', + body = 'function(a, b, c, d, e, f) local m = {[a] = b, [c] = d, [e] = f} '.. + 'return setmetatable(m, { __serialize = "map" }) end', + returns = 'map', + param_list = {'any', 'any', 'any', 'any', 'any', 'any'}, + exports = {'LUA', 'SQL'} +}); + +-- Make sure it is possible to create tables with field type MAP. +test:do_execsql_test( + "map-1", + [[ + CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT, m MAP); + ]], { + }) + +box.space.T:insert({0, {a = 1, b = 2}}) + +-- Make sure it is possible to select from MAP field. +test:do_execsql_test( + "map-2", + [[ + SELECT i, m FROM t; + ]], { + 0, {a = 1, b = 2}, + }) + +-- Make sure it is possible to insert into MAP field. +test:do_execsql_test( + "map-3", + [[ + INSERT INTO t(m) VALUES(NULL); + INSERT INTO t(m) VALUES(m1('a', 1)); + INSERT INTO t(m) VALUES(m2('b', 2, 'c', 3)); + INSERT INTO t(m) VALUES(m3('d', 4, 'e', 5, 'f', 6)); + SELECT i, m FROM t; + ]], { + 0, {a = 1, b = 2}, + 1, "", + 2, {a = 1}, + 3, {b = 2, c = 3}, + 4, {d = 4, e = 5, f = 6}, + }) + +-- Make sure it is possible to delete from MAP field. +test:do_execsql_test( + "map-4", + [[ + DELETE FROM t WHERE i < 3; + SELECT i, m FROM t; + ]], { + 3, {b = 2, c = 3}, + 4, {d = 4, e = 5, f = 6}, + }) + +-- Make sure it is possible to update MAP field. +test:do_execsql_test( + "map-5", + [[ + UPDATE t SET m = m1('abc', 123) WHERE i = 3; + SELECT i, m FROM t; + ]], { + 3, {abc = 123}, + 4, {d = 4, e = 5, f = 6}, + }) + +-- Make sure MAP can only be explicitly cast to ANY. +test:do_execsql_test( + "map-6.1", + [[ + SELECT CAST(m AS ANY) FROM t; + ]], { + {abc = 123}, + {d = 4, e = 5, f = 6}, + }) + +test:do_catchsql_test( + "map-6.2", + [[ + SELECT CAST(m AS UNSIGNED) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to unsigned' + }) + +test:do_catchsql_test( + "map-6.3", + [[ + SELECT CAST(m AS STRING) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to string' + }) + +test:do_catchsql_test( + "map-6.4", + [[ + SELECT CAST(m AS NUMBER) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to number' + }) + +test:do_catchsql_test( + "map-6.5", + [[ + SELECT CAST(m AS DOUBLE) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to double' + }) + +test:do_catchsql_test( + "map-6.6", + [[ + SELECT CAST(m AS INTEGER) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to integer' + }) + +test:do_catchsql_test( + "map-6.7", + [[ + SELECT CAST(m AS BOOLEAN) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to boolean' + }) + +test:do_catchsql_test( + "map-6.8", + [[ + SELECT CAST(m AS VARBINARY) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to varbinary' + }) + +test:do_catchsql_test( + "map-6.9", + [[ + SELECT CAST(m AS SCALAR) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to scalar' + }) + +test:do_catchsql_test( + "map-6.10", + [[ + SELECT CAST(m AS DECIMAL) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to decimal' + }) + +test:do_catchsql_test( + "map-6.11", + [[ + SELECT CAST(m AS UUID) FROM t; + ]], { + 1, 'Type mismatch: can not convert map({"abc": 123}) to uuid' + }) + +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);]]) +box.execute([[INSERT INTO t1 VALUES(1, m1('a', 1), 1, '1', 1, 1, 1, true, ]].. + [[x'31', 1, 1, ]].. + [[CAST('11111111-1111-1111-1111-111111111111' AS UUID))]]) + +-- +-- Make sure that only ANY value can be explicitly cast to MAP if the value +-- contains MAP. +-- +test:do_execsql_test( + "map-7.1", + [[ + SELECT CAST(a AS MAP) FROM t1; + ]], { + {a = 1} + }) + +test:do_catchsql_test( + "map-7.2", + [[ + SELECT CAST(g AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert integer(1) to map" + }) + +test:do_catchsql_test( + "map-7.3", + [[ + SELECT CAST(t AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert string('1') to map" + }) + +test:do_catchsql_test( + "map-7.4", + [[ + SELECT CAST(n AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert number(1) to map" + }) + +test:do_catchsql_test( + "map-7.5", + [[ + SELECT CAST(f AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert double(1.0) to map" + }) + +test:do_catchsql_test( + "map-7.6", + [[ + SELECT CAST(i AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert integer(1) to map" + }) + +test:do_catchsql_test( + "map-7.7", + [[ + SELECT CAST(b AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to map" + }) + +test:do_catchsql_test( + "map-7.8", + [[ + SELECT CAST(v AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert varbinary(x'31') to map" + }) + +test:do_catchsql_test( + "map-7.9", + [[ + SELECT CAST(s AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert scalar(1) to map" + }) + +test:do_catchsql_test( + "map-7.10", + [[ + SELECT CAST(d AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert decimal(1) to map" + }) + +test:do_catchsql_test( + "map-7.11", + [[ + SELECT CAST(u AS MAP) FROM t1; + ]], { + 1, "Type mismatch: can not convert ".. + "uuid(11111111-1111-1111-1111-111111111111) to map" + }) + +test:do_catchsql_test( + "map-7.12", + [[ + SELECT CAST(CAST(1 AS ANY) AS MAP); + ]], { + 1, "Type mismatch: can not convert any(1) to map" + }) + +-- Make sure that MAP can only be implicitly cast to ANY. +test:do_execsql_test( + "map-8.1", + [[ + INSERT INTO t1(a) VALUES(m1(1, 2)); + SELECT a FROM t1 WHERE a IS NOT NULL; + ]], { + {a = 1}, + {[1] = 2} + }) + +test:do_catchsql_test( + "map-8.2", + [[ + INSERT INTO t1(g) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' + }) + +test:do_catchsql_test( + "map-8.3", + [[ + INSERT INTO t1(t) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to string' + }) + +test:do_catchsql_test( + "map-8.4", + [[ + INSERT INTO t1(n) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to number' + }) + +test:do_catchsql_test( + "map-8.5", + [[ + INSERT INTO t1(f) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to double' + }) + +test:do_catchsql_test( + "map-8.6", + [[ + INSERT INTO t1(i) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to integer' + }) + +test:do_catchsql_test( + "map-8.7", + [[ + INSERT INTO t1(b) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to boolean' + }) + +test:do_catchsql_test( + "map-8.8", + [[ + INSERT INTO t1(v) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to varbinary' + }) + +test:do_catchsql_test( + "map-8.9", + [[ + INSERT INTO t1(s) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to scalar' + }) + +test:do_catchsql_test( + "map-8.10", + [[ + INSERT INTO t1(d) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to decimal' + }) + +test:do_catchsql_test( + "map-8.11", + [[ + INSERT INTO t1(u) VALUES(m1(1, 2)); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to uuid' + }) + +-- Make sure nothing can be implicitly cast to MAP. +test:do_catchsql_test( + "map-9.1", + [[ + INSERT INTO t(m) VALUES(CAST(m1('a', 1) AS ANY)); + ]], { + 1, 'Type mismatch: can not convert any({"a": 1}) to map' + }) + +test:do_catchsql_test( + "map-9.2", + [[ + INSERT INTO t(m) SELECT g FROM t1; + ]], { + 1, "Type mismatch: can not convert integer(1) to map" + }) + +test:do_catchsql_test( + "map-9.3", + [[ + INSERT INTO t(m) SELECT t FROM t1; + ]], { + 1, "Type mismatch: can not convert string('1') to map" + }) + +test:do_catchsql_test( + "map-9.4", + [[ + INSERT INTO t(m) SELECT n FROM t1; + ]], { + 1, "Type mismatch: can not convert number(1) to map" + }) + +test:do_catchsql_test( + "map-9.5", + [[ + INSERT INTO t(m) SELECT f FROM t1; + ]], { + 1, "Type mismatch: can not convert double(1.0) to map" + }) + +test:do_catchsql_test( + "map-9.6", + [[ + INSERT INTO t(m) SELECT i FROM t1; + ]], { + 1, "Type mismatch: can not convert integer(1) to map" + }) + +test:do_catchsql_test( + "map-9.7", + [[ + INSERT INTO t(m) SELECT b FROM t1; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to map" + }) + +test:do_catchsql_test( + "map-9.8", + [[ + INSERT INTO t(m) SELECT v FROM t1; + ]], { + 1, "Type mismatch: can not convert varbinary(x'31') to map" + }) + +test:do_catchsql_test( + "map-9.9", + [[ + INSERT INTO t(m) SELECT s FROM t1; + ]], { + 1, "Type mismatch: can not convert scalar(1) to map" + }) + +test:do_catchsql_test( + "map-9.10", + [[ + INSERT INTO t(m) SELECT d FROM t1; + ]], { + 1, "Type mismatch: can not convert decimal(1) to map" + }) + +test:do_catchsql_test( + "map-9.11", + [[ + INSERT INTO t(m) SELECT u FROM t1; + ]], { + 1, "Type mismatch: can not convert ".. + "uuid(11111111-1111-1111-1111-111111111111) to map" + }) + +-- +-- Make sure MAP cannot participate in arithmetic and bitwise operations and +-- concatenation. +-- +test:do_catchsql_test( + "map-10.1", + [[ + SELECT m1(1, 2) + 1; + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to integer, '.. + "decimal or double" + }) + +test:do_catchsql_test( + "map-10.2", + [[ + SELECT m1(1, 2) - 1; + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to integer, '.. + "decimal or double" + }) + +test:do_catchsql_test( + "map-10.3", + [[ + SELECT m1(1, 2) * 1; + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to integer, '.. + "decimal or double" + }) + +test:do_catchsql_test( + "map-10.4", + [[ + SELECT m1(1, 2) / 1; + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to integer, '.. + "decimal or double" + }) + +test:do_catchsql_test( + "map-10.5", + [[ + SELECT m1(1, 2) % 1; + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to integer' + }) + +test:do_catchsql_test( + "map-10.6", + [[ + SELECT m1(1, 2) >> 1; + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' + }) + +test:do_catchsql_test( + "map-10.7", + [[ + SELECT m1(1, 2) << 1; + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' + }) + +test:do_catchsql_test( + "map-10.8", + [[ + SELECT m1(1, 2) | 1; + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' + }) + +test:do_catchsql_test( + "map-10.9", + [[ + SELECT m1(1, 2) & 1; + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' + }) + +test:do_catchsql_test( + "map-10.10", + [[ + SELECT ~m1(1, 2); + ]], { + 1, 'Type mismatch: can not convert map({1: 2}) to unsigned' + }) + +test:do_catchsql_test( + "map-10.11", + [[ + SELECT m1(1, 2) || 'asd'; + ]], { + 1, 'Inconsistent types: expected string or varbinary got map({1: 2})' + }) + +-- Make sure MAP is not comparable. +test:do_catchsql_test( + "map-11.1", + [[ + SELECT m1('a', 1) > m1('b', 2); + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.2", + [[ + SELECT m1('a', 1) < CAST(1 AS ANY); + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.3", + [[ + SELECT m1('a', 1) == CAST(1 AS SCALAR); + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.4", + [[ + SELECT m1('a', 1) != CAST(1 AS NUMBER); + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.5", + [[ + SELECT m1('a', 1) >= CAST(1 AS DECIMAL);; + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.6", + [[ + SELECT m1('a', 1) <= CAST(1 AS UNSIGNED);; + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.7", + [[ + SELECT m1('a', 1) > 1; + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.8", + [[ + SELECT m1('a', 1) < 1e0; + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.9", + [[ + SELECT m1('a', 1) == 'asd'; + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.10", + [[ + SELECT m1('a', 1) != x'323334'; + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.11", + [[ + SELECT m1('a', 1) >= true; + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-11.12", + [[ + SELECT m1('a', 1) <= CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, 'Type mismatch: can not convert map({"a": 1}) to comparable type' + }) + +test:do_catchsql_test( + "map-12.1", + [[ + SELECT ABS(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ABS()" + }) + +test:do_catchsql_test( + "map-12.2", + [[ + SELECT AVG(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function AVG()" + }) + +test:do_catchsql_test( + "map-12.3", + [[ + SELECT CHAR(m) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function CHAR()" + }) + +test:do_catchsql_test( + "map-12.4", + [[ + SELECT CHARACTER_LENGTH(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "CHARACTER_LENGTH()" + }) + +test:do_catchsql_test( + "map-12.5", + [[ + SELECT CHAR_LENGTH(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "CHAR_LENGTH()" + }) + +test:do_execsql_test( + "map-12.6", + [[ + SELECT COALESCE(NULL, m) FROM t; + ]], { + {abc = 123}, + {d = 4, e = 5, f = 6}, + }) + +test:do_execsql_test( + "map-12.7", + [[ + SELECT COUNT(m) FROM t; + ]], { + 2 + }) + +test:do_catchsql_test( + "map-12.8", + [[ + SELECT GREATEST(1, m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "GREATEST()" + }) + +test:do_catchsql_test( + "map-12.9", + [[ + SELECT GROUP_CONCAT(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "GROUP_CONCAT()" + }) + +test:do_catchsql_test( + "map-12.10", + [[ + SELECT HEX(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function HEX()" + }) + +test:do_execsql_test( + "map-12.11", + [[ + SELECT IFNULL(m, 1) FROM t; + ]], { + {abc = 123}, + {d = 4, e = 5, f = 6}, + }) + +test:do_catchsql_test( + "map-12.12", + [[ + SELECT LEAST(1, m) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function LEAST()" + }) + +test:do_catchsql_test( + "map-12.13", + [[ + SELECT LENGTH(m) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function LENGTH()" + }) + +test:do_catchsql_test( + "map-12.14", + [[ + SELECT 'asd' LIKE m FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function LIKE()" + }) + +test:do_execsql_test( + "map-12.15", + [[ + SELECT LIKELIHOOD(m, 0.5e0) FROM t; + ]], { + {abc = 123}, + {d = 4, e = 5, f = 6}, + }) + +test:do_execsql_test( + "map-12.16", + [[ + SELECT LIKELY(m) FROM t; + ]], { + {abc = 123}, + {d = 4, e = 5, f = 6}, + }) + +test:do_catchsql_test( + "map-12.17", + [[ + SELECT LOWER(m) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function LOWER()" + }) + +test:do_catchsql_test( + "map-12.18", + [[ + SELECT MAX(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function MAX()" + }) + +test:do_catchsql_test( + "map-12.19", + [[ + SELECT MIN(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function MIN()" + }) + +test:do_catchsql_test( + "map-12.20", + [[ + SELECT NULLIF(1, m) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function NULLIF()" + }) + +test:do_catchsql_test( + "map-12.21", + [[ + SELECT POSITION('asd', m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "POSITION()" + }) + +test:do_execsql_test( + "map-12.22", + [[ + SELECT PRINTF(m) FROM t; + ]], { + '{"abc": 123}', + '{"d": 4, "f": 6, "e": 5}', + }) + +test:do_execsql_test( + "map-12.23", + [[ + SELECT QUOTE(m) FROM t; + ]], { + '{"abc": 123}', + '{"d": 4, "f": 6, "e": 5}', + }) + +test:do_catchsql_test( + "map-12.24", + [[ + SELECT RANDOMBLOB(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "RANDOMBLOB()" + }) + +test:do_catchsql_test( + "map-12.25", + [[ + SELECT REPLACE('asd', 'a', m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "REPLACE()" + }) + +test:do_catchsql_test( + "map-12.26", + [[ + SELECT ROUND(m) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function ROUND()" + }) + +test:do_catchsql_test( + "map-12.27", + [[ + SELECT SOUNDEX(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "SOUNDEX()" + }) + +test:do_catchsql_test( + "map-12.28", + [[ + SELECT SUBSTR(m, 1, 1) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function SUBSTR()" + }) + +test:do_catchsql_test( + "map-12.29", + [[ + SELECT SUM(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function SUM()" + }) + +test:do_catchsql_test( + "map-12.30", + [[ + SELECT TOTAL(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "TOTAL()" + }) + +test:do_catchsql_test( + "map-12.31", + [[ + SELECT TRIM(m) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function TRIM()" + }) + +test:do_execsql_test( + "map-12.32", + [[ + SELECT TYPEOF(m) FROM t; + ]], { + "map", "map" + }) + +test:do_catchsql_test( + "map-12.33", + [[ + SELECT UNICODE(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "UNICODE()" + }) + +test:do_execsql_test( + "map-12.34", + [[ + SELECT UNLIKELY(m) FROM t; + ]], { + {abc = 123}, + {d = 4, e = 5, f = 6}, + }) + +test:do_catchsql_test( + "map-12.35", + [[ + SELECT UPPER(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function UPPER()" + }) + +test:do_catchsql_test( + "map-12.36", + [[ + SELECT UUID(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function UUID()" + }) + +test:do_catchsql_test( + "map-12.37", + [[ + SELECT ZEROBLOB(m) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ZEROBLOB()" + }) + +box.execute([[DROP TABLE t1;]]) +box.execute([[DROP TABLE t;]]) + +test:finish_test()
next prev parent reply other threads:[~2021-11-15 16:45 UTC|newest] Thread overview: 8+ messages / expand[flat|nested] mbox.gz Atom feed top 2021-11-11 11:37 [Tarantool-patches] [PATCH v1 0/2] Introduce field type MAP to SQL Mergen Imeev via Tarantool-patches 2021-11-11 11:37 ` [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type MAP Mergen Imeev via Tarantool-patches 2021-11-14 16:24 ` Vladislav Shpilevoy via Tarantool-patches 2021-11-15 16:45 ` Mergen Imeev via Tarantool-patches [this message] 2021-11-11 11:37 ` [Tarantool-patches] [PATCH v1 2/2] sql: introduce MAP() function Mergen Imeev via Tarantool-patches 2021-11-14 16:24 ` Vladislav Shpilevoy via Tarantool-patches 2021-11-15 16:46 ` Mergen Imeev via Tarantool-patches 2021-11-18 21:21 ` [Tarantool-patches] [PATCH v1 0/2] Introduce field type MAP 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=20211115164543.GB171607@tarantool.org \ --to=tarantool-patches@dev.tarantool.org \ --cc=imeevma@tarantool.org \ --cc=v.shpilevoy@tarantool.org \ --subject='Re: [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type MAP' \ /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