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 556B16F852; Mon, 15 Nov 2021 19:38:11 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 556B16F852 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1636994291; bh=n6J0lFvFvwOLFAzHQT+4Bnxb1W8VUl3ZMJJc2ZdhqOc=; h=Date:To:Cc:References:In-Reply-To:Subject:List-Id: List-Unsubscribe:List-Archive:List-Post:List-Help:List-Subscribe: From:Reply-To:From; b=FaecxAU3eenpQDhDq66BJfZ/v6JnqjxdZBbl2gLS99MPoidgu3RqTJ8Q5eqam8IdJ Ce2yo53XvgsBx6ec1wM3TNAP6hFUj8izQX2Yz4HW0iuOR/juVwNTk1qj1d4xV9WCCe L/Zu46zpIZTf6VjVkvFVO4jr2jL8eZZQtXRBa9Bs= 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 4CA0C6F852 for ; Mon, 15 Nov 2021 19:38:10 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 4CA0C6F852 Received: by smtpng1.m.smailru.net with esmtpa (envelope-from ) id 1mmezh-0003SG-GX; Mon, 15 Nov 2021 19:38:10 +0300 Date: Mon, 15 Nov 2021 19:38:08 +0300 To: Vladislav Shpilevoy Cc: tarantool-patches@dev.tarantool.org Message-ID: <20211115163808.GA170810@tarantool.org> References: <8a954f84706769e4d685cedac987c2de8eb947cc.1635927295.git.imeevma@gmail.com> <7c124335-9796-90a4-9c2f-8bf3bfab353a@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <7c124335-9796-90a4-9c2f-8bf3bfab353a@tarantool.org> X-4EC0790: 10 X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD9731B3922EC063979B1F6335FDD7DA46529643274CFE4D7E800894C459B0CD1B9EACB37C9116A2E2ACF348E01FDE8689A9CF1D0C919C85256D1FFACA8F48D2A96 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE70D278D70F8433719EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F790063788758EA7442DD2858638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D8E80F11BF93D0A07F6516742805063648117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BAA867293B0326636D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B6B1CFA6D474D4A6A4089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-C1DE0DAB: 0D63561A33F958A502E5CBB5944912799DFAEAB65F570CFFF6507D71D44BBCB0D59269BC5F550898D99A6476B3ADF6B47008B74DF8BB9EF7333BD3B22AA88B938A852937E12ACA759F66ED85EB5F25FD410CA545F18667F91A7EA1CDA0B5A7A0 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D345C110A855FC09999FD212F84B39C1EA1105C73FB94191F42A1B89F2D9E3F6113921B818422908B811D7E09C32AA3244CE18000B385397267F446BE49871F9DFD30452B15D76AEC14729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojjLiSo6osCe41rnE34/EutA== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5DF7972392307487E4325F4B7AF3CE0A8D83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: Re: [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type ARRAY 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" Hi! Thank you for the review! My answers, diff and new patch below. Also, I dropped second patch on the branch. On Sun, Nov 14, 2021 at 05:12:03PM +0100, Vladislav Shpilevoy wrote: > Hi! Thanks for the patch! > > See 4 comments below. > > On 03.11.2021 09:17, Mergen Imeev via Tarantool-patches wrote: > > This patch introduces ARRAY to SQL. After this patch, all SQL operations > > and built-in functions should work correctly with ARRAY values. However, > > there is currently no way to create ARRAY values using only SQL tools. > > > > Part of #4762 > > > > @TarantoolBot document > > Title: Field type ARRAY in SQL > > > > Properties of type ARRAY in SQL: > > 1) a value ofttype ARRAY can be implicitly and explicitly cast only > > 1. ofttype -> of type. > Fixed. > > to ANY; > > 2) only a value of type ANY with primitive type ARRAY can be explicitly > > cast to ARRAY; > > 3) a value of any other type cannot be implicitly cast to ARRAY; > > 4) a value of type ARRAY cannot participate in arithmetic, bitwise, > > comparison, and concationation operations. > > 2. concationation -> concatenation. > Fixed. > > diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c > > index 244415e02..c84bbe8fe 100644 > > --- a/src/box/sql/mem.c > > +++ b/src/box/sql/mem.c > > @@ -3243,9 +3244,20 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat) > > case MEM_TYPE_STR: > > case MEM_TYPE_BIN: > > case MEM_TYPE_MAP: > > - case MEM_TYPE_ARRAY: > > lua_pushlstring(L, mem->z, mem->n); > > break; > > + case MEM_TYPE_ARRAY: { > > + const char *data = mem->z; > > + uint32_t size = mp_decode_array(&data); > > + lua_createtable(L, size, 0); > > + for (uint32_t i = 0; i < size; i++) { > > + luamp_decode(L, luaL_msgpack_default, &data); > > + lua_rawseti(L, -2, i + 1); > > + } > > + if (luaL_msgpack_default->decode_save_metatables) > > + luaL_setarrayhint(L, -1); > > 3. Why didn't you call luamp_decode() on the root? It does exactly > the same for arrays. I wouldn't mind if not the last 2 lines: > if we ever add more format options for arrays, we will forget to > patch this place for sure. > Thank you, I didn't know about this function. Fixed. > > diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua > > new file mode 100755 > > index 000000000..2c0f687c0 > > --- /dev/null > > +++ b/test/sql-tap/array.test.lua > > @@ -0,0 +1,985 @@ > > <...> > > > +-- Make sure it is possible to update ARRAY field. > > +test:do_execsql_test( > > + "array-5", > > + [[ > > + UPDATE t SET a = a1(123) WHERE i = 3; > > + SELECT i, a FROM t; > > + ]], { > > + 3, 123, > > + 4, 4, 5, 6, > > + }) > > + > > +-- Make sure ARRAY can only be explicitly cast to ANY and STRING. > > 4. But in 6.3 test the STRING cast raises an error. > True, ARRAY and MAP cannot be cast to STRING. > > +test:do_execsql_test( > > + "array-6.1", > > + [[ > > + SELECT CAST(a AS ANY) FROM t; > > + ]], { > > + 123, > > + 4, 5, 6, > > + }) > > + > > +test:do_catchsql_test( > > + "array-6.2", > > + [[ > > + SELECT CAST(a AS UNSIGNED) FROM t; > > + ]], { > > + 1, "Type mismatch: can not convert array([123]) to unsigned" > > + }) > > + > > +test:do_catchsql_test( > > + "array-6.3", > > + [[ > > + SELECT CAST(a AS STRING) FROM t; > > + ]], { > > + 1, "Type mismatch: can not convert array([123]) to string" > > + }) > > + Diff: 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..1446ab1cb --- /dev/null +++ b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md @@ -0,0 +1,3 @@ +## feature/core + + * Field type ARRAY is now available in SQL (gh-4762). diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index 0d8b0e716..e18bb24a2 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -1161,14 +1161,6 @@ bool_to_str0(struct Mem *mem) return mem_copy_str0(mem, str); } -static inline int -array_to_str0(struct Mem *mem) -{ - assert(mem->type == MEM_TYPE_ARRAY); - const char *str = mp_str(mem->z); - return mem_copy_str0(mem, str); -} - static inline int map_to_str0(struct Mem *mem) { @@ -3094,18 +3086,10 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat) case MEM_TYPE_MAP: lua_pushlstring(L, mem->z, mem->n); break; - case MEM_TYPE_ARRAY: { - const char *data = mem->z; - uint32_t size = mp_decode_array(&data); - lua_createtable(L, size, 0); - for (uint32_t i = 0; i < size; i++) { - luamp_decode(L, luaL_msgpack_default, &data); - lua_rawseti(L, -2, i + 1); - } - if (luaL_msgpack_default->decode_save_metatables) - luaL_setarrayhint(L, -1); - return; - } + case MEM_TYPE_ARRAY: + luamp_decode(L, luaL_msgpack_default, + (const char **)&mem->z); + break; case MEM_TYPE_NULL: lua_pushnil(L); break; diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua index 0b3b41bff..752cb24f2 100755 --- a/test/sql-tap/array.test.lua +++ b/test/sql-tap/array.test.lua @@ -84,7 +84,7 @@ test:do_execsql_test( 4, {4, 5, 6}, }) --- Make sure ARRAY can only be explicitly cast to ANY and STRING. +-- Make sure ARRAY can only be explicitly cast to ANY. test:do_execsql_test( "array-6.1", [[ New patch: commit 28c5b0d7969d6420177be9e8e8050c01443836aa Author: Mergen Imeev Date: Mon Nov 1 14:36:11 2021 +0300 sql: introduce field type ARRAY This patch introduces ARRAY to SQL. After this patch, all SQL operations and built-in functions should work correctly with ARRAY values. However, there is currently no way to create ARRAY values using only SQL tools. Part of #4762 @TarantoolBot document Title: Field type ARRAY in SQL Properties of type ARRAY in SQL: 1) a value of type ARRAY can be implicitly and explicitly cast only to ANY; 2) only a value of type ANY with primitive type ARRAY can be explicitly cast to ARRAY; 3) a value of any other type cannot be implicitly cast to ARRAY; 4) a value of type ARRAY cannot participate in arithmetic, bitwise, comparison, and concatination operations. 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..1446ab1cb --- /dev/null +++ b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md @@ -0,0 +1,3 @@ +## feature/core + + * Field type ARRAY is now available in SQL (gh-4762). diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c index aaeb7d51b..c7e7d4e8d 100644 --- a/extra/mkkeywordhash.c +++ b/extra/mkkeywordhash.c @@ -61,6 +61,7 @@ static Keyword aKeywordTable[] = { { "ALTER", "TK_ALTER", true }, { "ANALYZE", "TK_STANDARD", true }, { "AND", "TK_AND", true }, + { "ARRAY", "TK_ARRAY", true }, { "AS", "TK_AS", true }, { "ASC", "TK_ASC", true }, { "AUTOINCREMENT", "TK_AUTOINCR", false }, diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 0f8f0b5cc..20d62bce7 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -1370,8 +1370,20 @@ quoteFunc(struct sql_context *context, int argc, const struct Mem *argv) context->is_aborted = true; break; } + case MEM_TYPE_ARRAY: { + char *buf = NULL; + int size = mp_snprint(buf, 0, argv[0].z) + 1; + assert(size > 0); + buf = sqlDbMallocRawNN(sql_get(), size); + if (buf == NULL) { + context->is_aborted = true; + return; + } + mp_snprint(buf, size, argv[0].z); + mem_set_str0_allocated(context->pOut, buf); + break; + } case MEM_TYPE_BIN: - case MEM_TYPE_ARRAY: case MEM_TYPE_MAP: { const char *zBlob = argv[0].z; int nBlob = argv[0].n; diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index 6e1729f32..e18bb24a2 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -197,6 +197,8 @@ mem_type_class_to_str(const struct Mem *mem) return "boolean"; case MEM_TYPE_UUID: return "uuid"; + case MEM_TYPE_ARRAY: + return "array"; default: break; } @@ -367,28 +369,34 @@ mem_set_str0_allocated(struct Mem *mem, char *value) set_str_dynamic(mem, value, strlen(value), 0); } -int -mem_copy_str(struct Mem *mem, const char *value, uint32_t len) +static int +mem_copy_bytes(struct Mem *mem, const char *value, uint32_t size, + enum mem_type type) { - if (((mem->type & (MEM_TYPE_STR | MEM_TYPE_BIN)) != 0) && - mem->z == value) { + if (mem_is_bytes(mem) && mem->z == value) { /* Own value, but might be ephemeral. Make it own if so. */ - if (sqlVdbeMemGrow(mem, len, 1) != 0) + if (sqlVdbeMemGrow(mem, size, 1) != 0) return -1; - mem->type = MEM_TYPE_STR; + mem->type = type; mem->flags = 0; return 0; } mem_clear(mem); - if (sqlVdbeMemGrow(mem, len, 0) != 0) + if (sqlVdbeMemGrow(mem, size, 0) != 0) return -1; - memcpy(mem->z, value, len); - mem->n = len; - mem->type = MEM_TYPE_STR; + memcpy(mem->z, value, size); + mem->n = size; + mem->type = type; assert(mem->flags == 0); return 0; } +int +mem_copy_str(struct Mem *mem, const char *value, uint32_t len) +{ + return mem_copy_bytes(mem, value, len, MEM_TYPE_STR); +} + int mem_copy_str0(struct Mem *mem, const char *value) { @@ -444,23 +452,7 @@ mem_set_bin_allocated(struct Mem *mem, char *value, uint32_t size) int mem_copy_bin(struct Mem *mem, const char *value, uint32_t size) { - if (((mem->type & (MEM_TYPE_STR | MEM_TYPE_BIN)) != 0) && - mem->z == value) { - /* Own value, but might be ephemeral. Make it own if so. */ - if (sqlVdbeMemGrow(mem, size, 1) != 0) - return -1; - mem->type = MEM_TYPE_BIN; - mem->flags = 0; - return 0; - } - mem_clear(mem); - if (sqlVdbeMemGrow(mem, size, 0) != 0) - return -1; - memcpy(mem->z, value, size); - mem->n = size; - mem->type = MEM_TYPE_BIN; - assert(mem->flags == 0); - return 0; + return mem_copy_bytes(mem, value, size, MEM_TYPE_BIN); } static inline void @@ -516,6 +508,12 @@ mem_set_array_allocated(struct Mem *mem, char *value, uint32_t size) set_msgpack_value(mem, value, size, 0, MEM_TYPE_ARRAY); } +int +mem_copy_array(struct Mem *mem, const char *value, uint32_t size) +{ + return mem_copy_bytes(mem, value, size, MEM_TYPE_ARRAY); +} + void mem_set_invalid(struct Mem *mem) { @@ -1163,14 +1161,6 @@ bool_to_str0(struct Mem *mem) return mem_copy_str0(mem, str); } -static inline int -array_to_str0(struct Mem *mem) -{ - assert(mem->type == MEM_TYPE_ARRAY); - const char *str = mp_str(mem->z); - return mem_copy_str0(mem, str); -} - static inline int map_to_str0(struct Mem *mem) { @@ -1282,8 +1272,6 @@ mem_to_str(struct Mem *mem) return bin_to_str(mem); case MEM_TYPE_MAP: return map_to_str0(mem); - case MEM_TYPE_ARRAY: - return array_to_str0(mem); case MEM_TYPE_UUID: return uuid_to_str0(mem); case MEM_TYPE_DEC: @@ -1332,7 +1320,7 @@ mem_cast_explicit(struct Mem *mem, enum field_type type) case FIELD_TYPE_VARBINARY: if (mem->type == MEM_TYPE_STR) return str_to_bin(mem); - if (mem_is_bytes(mem)) { + if (mem_is_bin(mem)) { mem->flags &= ~(MEM_Scalar | MEM_Any); return 0; } @@ -1367,6 +1355,11 @@ mem_cast_explicit(struct Mem *mem, enum field_type type) if (mem->type == MEM_TYPE_BIN) return bin_to_uuid(mem); return -1; + case FIELD_TYPE_ARRAY: + if (mem->type != MEM_TYPE_ARRAY) + return -1; + mem->flags &= ~MEM_Any; + return 0; case FIELD_TYPE_SCALAR: if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0) return -1; @@ -1449,8 +1442,7 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) } return -1; case FIELD_TYPE_VARBINARY: - if ((mem->type & (MEM_TYPE_BIN | MEM_TYPE_MAP | - MEM_TYPE_ARRAY)) != 0) { + if (mem->type == MEM_TYPE_BIN) { mem->flags &= ~(MEM_Scalar | MEM_Any); return 0; } @@ -2440,7 +2432,8 @@ mem_cmp(const struct Mem *a, const struct Mem *b, int *result, *result = 1; return 0; } - if (((a->flags | b->flags) & MEM_Any) != 0) { + if (((a->flags | b->flags) & MEM_Any) != 0 || + ((a->type | b->type) & (MEM_TYPE_ARRAY | MEM_TYPE_MAP)) != 0) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(a), "comparable type"); return -1; @@ -3091,9 +3084,12 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat) case MEM_TYPE_STR: case MEM_TYPE_BIN: case MEM_TYPE_MAP: - case MEM_TYPE_ARRAY: lua_pushlstring(L, mem->z, mem->n); break; + case MEM_TYPE_ARRAY: + luamp_decode(L, luaL_msgpack_default, + (const char **)&mem->z); + break; case MEM_TYPE_NULL: lua_pushnil(L); break; @@ -3189,7 +3185,8 @@ port_lua_get_vdbemem(struct port *base, uint32_t *size) return NULL; for (int i = 0; i < argc; i++) { struct luaL_field field; - if (luaL_tofield(L, luaL_msgpack_default, -1 - i, &field) < 0) + int index = -1 - i; + if (luaL_tofield(L, luaL_msgpack_default, index, &field) < 0) goto error; mem_clear(&val[i]); switch (field.type) { @@ -3223,6 +3220,35 @@ port_lua_get_vdbemem(struct port *base, uint32_t *size) field.sval.len) != 0) goto error; break; + case MP_ARRAY: { + size_t used = region_used(region); + struct mpstream stream; + bool is_error = false; + mpstream_init(&stream, region, region_reserve_cb, + region_alloc_cb, set_encode_error, + &is_error); + lua_pushvalue(L, index); + luamp_encode_r(L, luaL_msgpack_default, &stream, + &field, 0); + lua_pop(L, 1); + mpstream_flush(&stream); + if (is_error) { + diag_set(OutOfMemory, stream.pos - stream.buf, + "mpstream_flush", "stream"); + return NULL; + } + uint32_t size = region_used(region) - used; + char *raw = region_join(region, size); + if (raw == NULL) { + diag_set(OutOfMemory, size, "region_join", + "raw"); + goto error; + } + if (mem_copy_array(&val[i], raw, size) != 0) + goto error; + region_truncate(region, used); + break; + } case MP_EXT: { if (field.ext_type == MP_UUID) { mem_set_uuid(&val[i], field.uuidval); diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h index 62830c3f5..41e8d8fa6 100644 --- a/src/box/sql/mem.h +++ b/src/box/sql/mem.h @@ -422,6 +422,10 @@ mem_set_array_static(struct Mem *mem, char *value, uint32_t size); void mem_set_array_allocated(struct Mem *mem, char *value, uint32_t size); +/** Copy ARRAY value to a newly allocated memory. The MEM type becomes ARRAY. */ +int +mem_copy_array(struct Mem *mem, const char *value, uint32_t size); + /** Clear MEM and set it to invalid state. */ void mem_set_invalid(struct Mem *mem); diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index 4b32f33f5..c93125a8b 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -1842,6 +1842,7 @@ typedef(A) ::= BOOLEAN . { A.type = FIELD_TYPE_BOOLEAN; } 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; } /** * Time-like types are temporary disabled, until they are diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua new file mode 100755 index 000000000..752cb24f2 --- /dev/null +++ b/test/sql-tap/array.test.lua @@ -0,0 +1,985 @@ +#!/usr/bin/env tarantool +local test = require("sqltester") +test:plan(110) + +box.schema.func.create('A1', { + language = 'Lua', + body = 'function(a) return {a} end', + returns = 'array', + param_list = {'any'}, + exports = {'LUA', 'SQL'} +}); + +box.schema.func.create('A2', { + language = 'Lua', + body = 'function(a, b) return {a, b} end', + returns = 'array', + param_list = {'any', 'any'}, + exports = {'LUA', 'SQL'} +}); + +box.schema.func.create('A3', { + language = 'Lua', + body = 'function(a, b, c) return {a, b, c} end', + returns = 'array', + param_list = {'any', 'any', 'any'}, + exports = {'LUA', 'SQL'} +}); + +-- Make sure it is possible to create tables with field type ARRAY. +test:do_execsql_test( + "array-1", + [[ + CREATE TABLE t (i INT PRIMARY KEY AUTOINCREMENT, a ARRAY); + ]], { + }) + +box.space.T:insert({0, {1, 2, 3, 4}}) + +-- Make sure it is possible to select from ARRAY field. +test:do_execsql_test( + "array-2", + [[ + SELECT i, a FROM t; + ]], { + 0, {1, 2, 3, 4}, + }) + +-- Make sure it is possible to insert into ARRAY field. +test:do_execsql_test( + "array-3", + [[ + INSERT INTO t(a) VALUES(NULL); + INSERT INTO t(a) VALUES(a1(1)); + INSERT INTO t(a) VALUES(a2(2, 3)); + INSERT INTO t(a) VALUES(a3(4, 5, 6)); + SELECT i, a FROM t; + ]], { + 0, {1, 2, 3, 4}, + 1, "", + 2, {1}, + 3, {2, 3}, + 4, {4, 5, 6}, + }) + +-- Make sure it is possible to delete from ARRAY field. +test:do_execsql_test( + "array-4", + [[ + DELETE FROM t WHERE i < 3; + SELECT i, a FROM t; + ]], { + 3, {2, 3}, + 4, {4, 5, 6}, + }) + +-- Make sure it is possible to update ARRAY field. +test:do_execsql_test( + "array-5", + [[ + UPDATE t SET a = a1(123) WHERE i = 3; + SELECT i, a FROM t; + ]], { + 3, {123}, + 4, {4, 5, 6}, + }) + +-- Make sure ARRAY can only be explicitly cast to ANY. +test:do_execsql_test( + "array-6.1", + [[ + SELECT CAST(a AS ANY) FROM t; + ]], { + {123}, + {4, 5, 6}, + }) + +test:do_catchsql_test( + "array-6.2", + [[ + SELECT CAST(a AS UNSIGNED) FROM t; + ]], { + 1, "Type mismatch: can not convert array([123]) to unsigned" + }) + +test:do_catchsql_test( + "array-6.3", + [[ + SELECT CAST(a AS STRING) FROM t; + ]], { + 1, "Type mismatch: can not convert array([123]) to string" + }) + +test:do_catchsql_test( + "array-6.4", + [[ + SELECT CAST(a AS NUMBER) FROM t; + ]], { + 1, "Type mismatch: can not convert array([123]) to number" + }) + +test:do_catchsql_test( + "array-6.5", + [[ + SELECT CAST(a AS DOUBLE) FROM t; + ]], { + 1, "Type mismatch: can not convert array([123]) to double" + }) + +test:do_catchsql_test( + "array-6.6", + [[ + SELECT CAST(a AS INTEGER) FROM t; + ]], { + 1, "Type mismatch: can not convert array([123]) to integer" + }) + +test:do_catchsql_test( + "array-6.7", + [[ + SELECT CAST(a AS BOOLEAN) FROM t; + ]], { + 1, "Type mismatch: can not convert array([123]) to boolean" + }) + +test:do_catchsql_test( + "array-6.8", + [[ + SELECT CAST(a AS VARBINARY) FROM t; + ]], { + 1, "Type mismatch: can not convert array([123]) to varbinary" + }) + +test:do_catchsql_test( + "array-6.9", + [[ + SELECT CAST(a AS SCALAR) FROM t; + ]], { + 1, "Type mismatch: can not convert array([123]) to scalar" + }) + +test:do_catchsql_test( + "array-6.10", + [[ + SELECT CAST(a AS DECIMAL) FROM t; + ]], { + 1, "Type mismatch: can not convert array([123]) to decimal" + }) + +test:do_catchsql_test( + "array-6.11", + [[ + SELECT CAST(a AS UUID) FROM t; + ]], { + 1, "Type mismatch: can not convert array([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, a1(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 ARRAY if the value +-- contains ARRAY. +-- +test:do_execsql_test( + "array-7.1", + [[ + SELECT CAST(a AS ARRAY) FROM t1; + ]], { + {1} + }) + +test:do_catchsql_test( + "array-7.2", + [[ + SELECT CAST(g AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert integer(1) to array" + }) + +test:do_catchsql_test( + "array-7.3", + [[ + SELECT CAST(t AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert string('1') to array" + }) + +test:do_catchsql_test( + "array-7.4", + [[ + SELECT CAST(n AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert number(1) to array" + }) + +test:do_catchsql_test( + "array-7.5", + [[ + SELECT CAST(f AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert double(1.0) to array" + }) + +test:do_catchsql_test( + "array-7.6", + [[ + SELECT CAST(i AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert integer(1) to array" + }) + +test:do_catchsql_test( + "array-7.7", + [[ + SELECT CAST(b AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to array" + }) + +test:do_catchsql_test( + "array-7.8", + [[ + SELECT CAST(v AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert varbinary(x'31') to array" + }) + +test:do_catchsql_test( + "array-7.9", + [[ + SELECT CAST(s AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert scalar(1) to array" + }) + +test:do_catchsql_test( + "array-7.10", + [[ + SELECT CAST(d AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert decimal(1) to array" + }) + +test:do_catchsql_test( + "array-7.11", + [[ + SELECT CAST(u AS ARRAY) FROM t1; + ]], { + 1, "Type mismatch: can not convert ".. + "uuid(11111111-1111-1111-1111-111111111111) to array" + }) + +test:do_catchsql_test( + "array-7.12", + [[ + SELECT CAST(CAST(1 AS ANY) AS ARRAY); + ]], { + 1, "Type mismatch: can not convert any(1) to array" + }) + +-- Make sure that ARRAY can only be implicitly cast to ANY. +test:do_execsql_test( + "array-8.1", + [[ + INSERT INTO t1(a) VALUES(a2(1, 2)); + SELECT a FROM t1 WHERE a IS NOT NULL; + ]], { + {1}, + {1, 2} + }) + +test:do_catchsql_test( + "array-8.2", + [[ + INSERT INTO t1(g) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to unsigned" + }) + +test:do_catchsql_test( + "array-8.3", + [[ + INSERT INTO t1(t) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to string" + }) + +test:do_catchsql_test( + "array-8.4", + [[ + INSERT INTO t1(n) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to number" + }) + +test:do_catchsql_test( + "array-8.5", + [[ + INSERT INTO t1(f) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to double" + }) + +test:do_catchsql_test( + "array-8.6", + [[ + INSERT INTO t1(i) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to integer" + }) + +test:do_catchsql_test( + "array-8.7", + [[ + INSERT INTO t1(b) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to boolean" + }) + +test:do_catchsql_test( + "array-8.8", + [[ + INSERT INTO t1(v) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to varbinary" + }) + +test:do_catchsql_test( + "array-8.9", + [[ + INSERT INTO t1(s) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to scalar" + }) + +test:do_catchsql_test( + "array-8.10", + [[ + INSERT INTO t1(d) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to decimal" + }) + +test:do_catchsql_test( + "array-8.11", + [[ + INSERT INTO t1(u) VALUES(a2(1, 2)); + ]], { + 1, "Type mismatch: can not convert array([1, 2]) to uuid" + }) + +-- Make sure nothing can be implicitly cast to ARRAY. +test:do_catchsql_test( + "array-9.1", + [[ + INSERT INTO t(a) VALUES(CAST(a1(1) AS ANY)); + ]], { + 1, "Type mismatch: can not convert any([1]) to array" + }) + +test:do_catchsql_test( + "array-9.2", + [[ + INSERT INTO t(a) SELECT g FROM t1; + ]], { + 1, "Type mismatch: can not convert integer(1) to array" + }) + +test:do_catchsql_test( + "array-9.3", + [[ + INSERT INTO t(a) SELECT t FROM t1; + ]], { + 1, "Type mismatch: can not convert string('1') to array" + }) + +test:do_catchsql_test( + "array-9.4", + [[ + INSERT INTO t(a) SELECT n FROM t1; + ]], { + 1, "Type mismatch: can not convert number(1) to array" + }) + +test:do_catchsql_test( + "array-9.5", + [[ + INSERT INTO t(a) SELECT f FROM t1; + ]], { + 1, "Type mismatch: can not convert double(1.0) to array" + }) + +test:do_catchsql_test( + "array-9.6", + [[ + INSERT INTO t(a) SELECT i FROM t1; + ]], { + 1, "Type mismatch: can not convert integer(1) to array" + }) + +test:do_catchsql_test( + "array-9.7", + [[ + INSERT INTO t(a) SELECT b FROM t1; + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to array" + }) + +test:do_catchsql_test( + "array-9.8", + [[ + INSERT INTO t(a) SELECT v FROM t1; + ]], { + 1, "Type mismatch: can not convert varbinary(x'31') to array" + }) + +test:do_catchsql_test( + "array-9.9", + [[ + INSERT INTO t(a) SELECT s FROM t1; + ]], { + 1, "Type mismatch: can not convert scalar(1) to array" + }) + +test:do_catchsql_test( + "array-9.10", + [[ + INSERT INTO t(a) SELECT d FROM t1; + ]], { + 1, "Type mismatch: can not convert decimal(1) to array" + }) + +test:do_catchsql_test( + "array-9.11", + [[ + INSERT INTO t(a) SELECT u FROM t1; + ]], { + 1, "Type mismatch: can not convert ".. + "uuid(11111111-1111-1111-1111-111111111111) to array" + }) + +-- +-- Make sure ARRAY cannot participate in arithmetic and bitwise operations and +-- concatenation. +-- +test:do_catchsql_test( + "array-10.1", + [[ + SELECT a3(1, 2, 3) + 1; + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to integer, ".. + "decimal or double" + }) + +test:do_catchsql_test( + "array-10.2", + [[ + SELECT a3(1, 2, 3) - 1; + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to integer, ".. + "decimal or double" + }) + +test:do_catchsql_test( + "array-10.3", + [[ + SELECT a3(1, 2, 3) * 1; + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to integer, ".. + "decimal or double" + }) + +test:do_catchsql_test( + "array-10.4", + [[ + SELECT a3(1, 2, 3) / 1; + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to integer, ".. + "decimal or double" + }) + +test:do_catchsql_test( + "array-10.5", + [[ + SELECT a3(1, 2, 3) % 1; + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to integer" + }) + +test:do_catchsql_test( + "array-10.6", + [[ + SELECT a3(1, 2, 3) >> 1; + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to unsigned" + }) + +test:do_catchsql_test( + "array-10.7", + [[ + SELECT a3(1, 2, 3) << 1; + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to unsigned" + }) + +test:do_catchsql_test( + "array-10.8", + [[ + SELECT a3(1, 2, 3) | 1; + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to unsigned" + }) + +test:do_catchsql_test( + "array-10.9", + [[ + SELECT a3(1, 2, 3) & 1; + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to unsigned" + }) + +test:do_catchsql_test( + "array-10.10", + [[ + SELECT ~a3(1, 2, 3); + ]], { + 1, "Type mismatch: can not convert array([1, 2, 3]) to unsigned" + }) + +test:do_catchsql_test( + "array-10.11", + [[ + SELECT a3(1, 2, 3) || 'asd'; + ]], { + 1, "Inconsistent types: expected string or varbinary got ".. + "array([1, 2, 3])" + }) + +-- Make sure ARRAY is not comparable. +test:do_catchsql_test( + "array-11.1", + [[ + SELECT a1(1) > a1(2); + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.2", + [[ + SELECT a1(1) < CAST(1 AS ANY); + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.3", + [[ + SELECT a1(1) == CAST(1 AS SCALAR); + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.4", + [[ + SELECT a1(1) != CAST(1 AS NUMBER); + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.5", + [[ + SELECT a1(1) >= CAST(1 AS DECIMAL);; + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.6", + [[ + SELECT a1(1) <= CAST(1 AS UNSIGNED);; + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.7", + [[ + SELECT a1(1) > 1; + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.8", + [[ + SELECT a1(1) < 1e0; + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.9", + [[ + SELECT a1(1) == 'asd'; + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.10", + [[ + SELECT a1(1) != x'323334'; + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.11", + [[ + SELECT a1(1) >= true; + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-11.12", + [[ + SELECT a1(1) <= CAST('11111111-1111-1111-1111-111111111111' AS UUID); + ]], { + 1, "Type mismatch: can not convert array([1]) to comparable type" + }) + +test:do_catchsql_test( + "array-12.1", + [[ + SELECT ABS(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ABS()" + }) + +test:do_catchsql_test( + "array-12.2", + [[ + SELECT AVG(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function AVG()" + }) + +test:do_catchsql_test( + "array-12.3", + [[ + SELECT CHAR(a) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function CHAR()" + }) + +test:do_catchsql_test( + "array-12.4", + [[ + SELECT CHARACTER_LENGTH(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "CHARACTER_LENGTH()" + }) + +test:do_catchsql_test( + "array-12.5", + [[ + SELECT CHAR_LENGTH(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "CHAR_LENGTH()" + }) + +test:do_execsql_test( + "array-12.6", + [[ + SELECT COALESCE(NULL, a) FROM t; + ]], { + {123}, + {4, 5, 6} + }) + +test:do_execsql_test( + "array-12.7", + [[ + SELECT COUNT(a) FROM t; + ]], { + 2 + }) + +test:do_catchsql_test( + "array-12.8", + [[ + SELECT GREATEST(1, a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "GREATEST()" + }) + +test:do_catchsql_test( + "array-12.9", + [[ + SELECT GROUP_CONCAT(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "GROUP_CONCAT()" + }) + +test:do_catchsql_test( + "array-12.10", + [[ + SELECT HEX(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function HEX()" + }) + +test:do_execsql_test( + "array-12.11", + [[ + SELECT IFNULL(a, 1) FROM t; + ]], { + {123}, + {4, 5, 6} + }) + +test:do_catchsql_test( + "array-12.12", + [[ + SELECT LEAST(1, a) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function LEAST()" + }) + +test:do_catchsql_test( + "array-12.13", + [[ + SELECT LENGTH(a) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function LENGTH()" + }) + +test:do_catchsql_test( + "array-12.14", + [[ + SELECT 'asd' LIKE a FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function LIKE()" + }) + +test:do_execsql_test( + "array-12.15", + [[ + SELECT LIKELIHOOD(a, 0.5e0) FROM t; + ]], { + {123}, + {4, 5, 6} + }) + +test:do_execsql_test( + "array-12.16", + [[ + SELECT LIKELY(a) FROM t; + ]], { + {123}, + {4, 5, 6} + }) + +test:do_catchsql_test( + "array-12.17", + [[ + SELECT LOWER(a) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function LOWER()" + }) + +test:do_catchsql_test( + "array-12.18", + [[ + SELECT MAX(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function MAX()" + }) + +test:do_catchsql_test( + "array-12.19", + [[ + SELECT MIN(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function MIN()" + }) + +test:do_catchsql_test( + "array-12.20", + [[ + SELECT NULLIF(1, a) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function NULLIF()" + }) + +test:do_catchsql_test( + "array-12.21", + [[ + SELECT POSITION('asd', a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "POSITION()" + }) + +test:do_execsql_test( + "array-12.22", + [[ + SELECT PRINTF(a) FROM t; + ]], { + "[123]", + "[4, 5, 6]" + }) + +test:do_execsql_test( + "array-12.23", + [[ + SELECT QUOTE(a) FROM t; + ]], { + "[123]", + "[4, 5, 6]" + }) + +test:do_catchsql_test( + "array-12.24", + [[ + SELECT RANDOMBLOB(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "RANDOMBLOB()" + }) + +test:do_catchsql_test( + "array-12.25", + [[ + SELECT REPLACE('asd', 'a', a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "REPLACE()" + }) + +test:do_catchsql_test( + "array-12.26", + [[ + SELECT ROUND(a) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function ROUND()" + }) + +test:do_catchsql_test( + "array-12.27", + [[ + SELECT SOUNDEX(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "SOUNDEX()" + }) + +test:do_catchsql_test( + "array-12.28", + [[ + SELECT SUBSTR(a, 1, 1) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function SUBSTR()" + }) + +test:do_catchsql_test( + "array-12.29", + [[ + SELECT SUM(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function SUM()" + }) + +test:do_catchsql_test( + "array-12.30", + [[ + SELECT TOTAL(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "TOTAL()" + }) + +test:do_catchsql_test( + "array-12.31", + [[ + SELECT TRIM(a) FROM t; + ]], { + 1, + "Failed to execute SQL statement: wrong arguments for function TRIM()" + }) + +test:do_execsql_test( + "array-12.32", + [[ + SELECT TYPEOF(a) FROM t; + ]], { + "array", "array" + }) + +test:do_catchsql_test( + "array-12.33", + [[ + SELECT UNICODE(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function ".. + "UNICODE()" + }) + +test:do_execsql_test( + "array-12.34", + [[ + SELECT UNLIKELY(a) FROM t; + ]], { + {123}, + {4, 5, 6} + }) + +test:do_catchsql_test( + "array-12.35", + [[ + SELECT UPPER(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function UPPER()" + }) + +test:do_catchsql_test( + "array-12.36", + [[ + SELECT UUID(a) FROM t; + ]], { + 1, "Failed to execute SQL statement: wrong arguments for function UUID()" + }) + +test:do_catchsql_test( + "array-12.37", + [[ + SELECT ZEROBLOB(a) 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() diff --git a/test/sql-tap/lua/sqltester.lua b/test/sql-tap/lua/sqltester.lua index e3e35267b..adc799625 100644 --- a/test/sql-tap/lua/sqltester.lua +++ b/test/sql-tap/lua/sqltester.lua @@ -14,9 +14,7 @@ local function flatten(arr) local function flatten(arr) for _, v in ipairs(arr) do - if type(v) == "table" then - flatten(v) - elseif box.tuple.is(v) then + if box.tuple.is(v) then flatten(v:totable()) else table.insert(result, v)