[Tarantool-patches] [PATCH v1 0/2] Introduce field type ARRAY to SQL

[Tarantool-patches] [PATCH v1 0/2] Introduce field type ARRAY to SQL

[Mergen Imeev via Tarantool-patches]

This patch introduces field type ARRAY to SQL. Also, it adds function ARRAY()
that is used to create values of type ARRAY.

https://github.com/tarantool/tarantool/issues/4762
https://github.com/tarantool/tarantool/tree/imeevma/gh-4762-introduce-array

Mergen Imeev (2):
  sql: introduce field type ARRAY
  sql: introduce ARRAY() function

 .../gh-4762-introduce-array-to-sql.md         |    4 +
 extra/mkkeywordhash.c                         |    1 +
 src/box/sql.c                                 |    2 +-
 src/box/sql/func.c                            |   42 +-
 src/box/sql/mem.c                             |  138 ++-
 src/box/sql/mem.h                             |   27 +-
 src/box/sql/parse.y                           |    3 +-
 src/box/sql/vdbe.c                            |    5 +-
 src/box/sql/vdbeapi.c                         |    4 +-
 test/sql-tap/array.test.lua                   | 1016 +++++++++++++++++
 10 files changed, 1173 insertions(+), 69 deletions(-)
 create mode 100644 changelogs/unreleased/gh-4762-introduce-array-to-sql.md
 create mode 100755 test/sql-tap/array.test.lua

-- 
2.25.1

[Tarantool-patches] [PATCH v1 1/2] sql: introduce field type ARRAY

[Mergen Imeev via Tarantool-patches]

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
   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.
---
 extra/mkkeywordhash.c       |   1 +
 src/box/sql/func.c          |  18 +-
 src/box/sql/mem.c           | 112 ++--
 src/box/sql/mem.h           |   4 +
 src/box/sql/parse.y         |   1 +
 test/sql-tap/array.test.lua | 985 ++++++++++++++++++++++++++++++++++++
 6 files changed, 1084 insertions(+), 37 deletions(-)
 create mode 100755 test/sql-tap/array.test.lua

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 43f6fc40a..07f1ed50a 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -339,10 +339,12 @@ typeofFunc(struct sql_context *context, int argc, struct Mem *argv)
 		z = "double";
 		break;
 	case MEM_TYPE_BIN:
-	case MEM_TYPE_ARRAY:
 	case MEM_TYPE_MAP:
 		z = "varbinary";
 		break;
+	case MEM_TYPE_ARRAY:
+		z = "array";
+		break;
 	case MEM_TYPE_BOOL:
 		z = "boolean";
 		break;
@@ -1272,8 +1274,20 @@ quoteFunc(struct sql_context *context, int argc, struct Mem *argv)
 			sql_result_value(context, &argv[0]);
 			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: {
 			char *zText = 0;
 			char const *zBlob = mem_as_bin(&argv[0]);
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
@@ -190,6 +190,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;
 	}
@@ -389,28 +391,34 @@ mem_set_str0_allocated(struct Mem *mem, char *value)
 	mem->flags |= MEM_Term;
 }
 
-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)
 {
@@ -480,23 +488,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
@@ -566,6 +558,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)
 {
@@ -1389,8 +1387,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:
@@ -1439,7 +1435,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;
 		}
@@ -1474,6 +1470,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;
@@ -1556,8 +1557,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;
 		}
@@ -2556,7 +2556,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;
@@ -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);
+			return;
+		}
 		case MEM_TYPE_NULL:
 			lua_pushnil(L);
 			break;
@@ -3341,7 +3353,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) {
@@ -3375,6 +3388,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 a8b132479..a5b590cd3 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -540,6 +540,10 @@ mem_set_array_dynamic(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 2ed0ab09f..4cae943d4 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1838,6 +1838,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..2c0f687c0
--- /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 and 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"
+    })
+
+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.5) 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()
-- 
2.25.1

Re: [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type ARRAY

[Mergen Imeev via Tarantool-patches]

Hi! I'm sorry, I changed the way ARRAY values are showed in results of sql-tap
tests. Differences and new patch below.

On Wed, Nov 03, 2021 at 11:17:50AM +0300, Mergen Imeev via Tarantool-patches wrote:
<cut>

Diff:

diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua
index 2c0f687c0..746462ec6 100755
--- a/test/sql-tap/array.test.lua
+++ b/test/sql-tap/array.test.lua
@@ -42,7 +42,7 @@ test:do_execsql_test(
     [[
         SELECT i, a FROM t;
     ]], {
-        0, 1, 2, 3, 4,
+        0, {1, 2, 3, 4},
     })
 
 -- Make sure it is possible to insert into ARRAY field.
@@ -55,11 +55,11 @@ test:do_execsql_test(
         INSERT INTO t(a) VALUES(a3(4, 5, 6));
         SELECT i, a FROM t;
     ]], {
-        0, 1, 2, 3, 4,
+        0, {1, 2, 3, 4},
         1, "",
-        2, 1,
-        3, 2, 3,
-        4, 4, 5, 6,
+        2, {1},
+        3, {2, 3},
+        4, {4, 5, 6},
     })
 
 -- Make sure it is possible to delete from ARRAY field.
@@ -69,8 +69,8 @@ test:do_execsql_test(
         DELETE FROM t WHERE i < 3;
         SELECT i, a FROM t;
     ]], {
-        3, 2, 3,
-        4, 4, 5, 6,
+        3, {2, 3},
+        4, {4, 5, 6},
     })
 
 -- Make sure it is possible to update ARRAY field.
@@ -80,8 +80,8 @@ test:do_execsql_test(
         UPDATE t SET a = a1(123) WHERE i = 3;
         SELECT i, a FROM t;
     ]], {
-        3, 123,
-        4, 4, 5, 6,
+        3, {123},
+        4, {4, 5, 6},
     })
 
 -- Make sure ARRAY can only be explicitly cast to ANY and STRING.
@@ -90,8 +90,8 @@ test:do_execsql_test(
     [[
         SELECT CAST(a AS ANY) FROM t;
     ]], {
-        123,
-        4, 5, 6,
+        {123},
+        {4, 5, 6},
     })
 
 test:do_catchsql_test(
@@ -190,7 +190,7 @@ test:do_execsql_test(
     [[
         SELECT CAST(a AS ARRAY) FROM t1;
     ]], {
-        1
+        {1}
     })
 
 test:do_catchsql_test(
@@ -289,8 +289,8 @@ test:do_execsql_test(
         INSERT INTO t1(a) VALUES(a2(1, 2));
         SELECT a FROM t1 WHERE a IS NOT NULL;
     ]], {
-        1,
-        1, 2
+        {1},
+        {1, 2}
     })
 
 test:do_catchsql_test(
@@ -705,8 +705,8 @@ test:do_execsql_test(
     [[
         SELECT COALESCE(NULL, a) FROM t;
     ]], {
-        123,
-        4, 5, 6
+        {123},
+        {4, 5, 6}
     })
 
 test:do_execsql_test(
@@ -748,8 +748,8 @@ test:do_execsql_test(
     [[
         SELECT IFNULL(a, 1) FROM t;
     ]], {
-        123,
-        4, 5, 6
+        {123},
+        {4, 5, 6}
     })
 
 test:do_catchsql_test(
@@ -782,10 +782,10 @@ test:do_catchsql_test(
 test:do_execsql_test(
     "array-12.15",
     [[
-        SELECT LIKELIHOOD(a, 0.5) FROM t;
+        SELECT LIKELIHOOD(a, 0.5e0) FROM t;
     ]], {
-        123,
-        4, 5, 6
+        {123},
+        {4, 5, 6}
     })
 
 test:do_execsql_test(
@@ -793,8 +793,8 @@ test:do_execsql_test(
     [[
         SELECT LIKELY(a) FROM t;
     ]], {
-        123,
-        4, 5, 6
+        {123},
+        {4, 5, 6}
     })
 
 test:do_catchsql_test(
@@ -951,8 +951,8 @@ test:do_execsql_test(
     [[
         SELECT UNLIKELY(a) FROM t;
     ]], {
-        123,
-        4, 5, 6
+        {123},
+        {4, 5, 6}
     })
 
 test:do_catchsql_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)


New patch:

commit 23d2d464797c17d117c597e8d1b86461006786b0
Author: Mergen Imeev <imeevma@gmail.com>
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 ofttype 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 concationation operations.

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 43f6fc40a..07f1ed50a 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -339,10 +339,12 @@ typeofFunc(struct sql_context *context, int argc, struct Mem *argv)
 		z = "double";
 		break;
 	case MEM_TYPE_BIN:
-	case MEM_TYPE_ARRAY:
 	case MEM_TYPE_MAP:
 		z = "varbinary";
 		break;
+	case MEM_TYPE_ARRAY:
+		z = "array";
+		break;
 	case MEM_TYPE_BOOL:
 		z = "boolean";
 		break;
@@ -1272,8 +1274,20 @@ quoteFunc(struct sql_context *context, int argc, struct Mem *argv)
 			sql_result_value(context, &argv[0]);
 			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: {
 			char *zText = 0;
 			char const *zBlob = mem_as_bin(&argv[0]);
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
@@ -190,6 +190,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;
 	}
@@ -389,28 +391,34 @@ mem_set_str0_allocated(struct Mem *mem, char *value)
 	mem->flags |= MEM_Term;
 }
 
-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)
 {
@@ -480,23 +488,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
@@ -566,6 +558,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)
 {
@@ -1389,8 +1387,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:
@@ -1439,7 +1435,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;
 		}
@@ -1474,6 +1470,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;
@@ -1556,8 +1557,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;
 		}
@@ -2556,7 +2556,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;
@@ -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);
+			return;
+		}
 		case MEM_TYPE_NULL:
 			lua_pushnil(L);
 			break;
@@ -3341,7 +3353,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) {
@@ -3375,6 +3388,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 a8b132479..a5b590cd3 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -540,6 +540,10 @@ mem_set_array_dynamic(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..746462ec6
--- /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 and 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"
+    })
+
+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)

Re: [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type ARRAY

[Konstantin Osipov via Tarantool-patches]

* Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org> [21/11/03 11:22]:
> 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.

The entire industry is using [] for array subscript and {} for map
subscript. Why did you have to invent () for it?

> +        SELECT a1(1) == CAST(1 AS SCALAR);

-- 
Konstantin Osipov, Moscow, Russia

Re: [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type ARRAY

[Mergen Imeev via Tarantool-patches]

Hi! Thank you for the advice. My answer below.

On Wed, Nov 03, 2021 at 04:53:27PM +0300, Konstantin Osipov wrote:
> * Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org> [21/11/03 11:22]:
> > 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.
> 
> The entire industry is using [] for array subscript and {} for map
> subscript. Why did you have to invent () for it?
> 
Here we have a user-defined function a1() that takes one argument and returns an
ARRAY containing a single element that was given as an argument. There are also
two more functions: a2(), which takes two arguments, and a3(), which takes three
arguments. They work the same way. I created them to test if ARRAY works as
intended. The built-in ARRAY() function takes 1 to 127 arguments and returns
ARRAY, which contains the values passed as arguments to the function.

This patch-set is just a part of the ARRAY addition, and we plan to add [] for
ARRAY and {} for MAP a little later, after we have built-in functions ARRAY()
and MAP().

Or did you mean something else?

> > +        SELECT a1(1) == CAST(1 AS SCALAR);
> 
> -- 
> Konstantin Osipov, Moscow, Russia

Re: [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type ARRAY

[Konstantin Osipov via Tarantool-patches]

* Mergen Imeev <imeevma@tarantool.org> [21/11/03 17:32]:
> > 
> > The entire industry is using [] for array subscript and {} for map
> > subscript. Why did you have to invent () for it?
> > 
> Here we have a user-defined function a1() that takes one argument and returns an
> ARRAY containing a single element that was given as an argument. There are also
> two more functions: a2(), which takes two arguments, and a3(), which takes three
> arguments. They work the same way. I created them to test if ARRAY works as
> intended. The built-in ARRAY() function takes 1 to 127 arguments and returns
> ARRAY, which contains the values passed as arguments to the function.
> 
> This patch-set is just a part of the ARRAY addition, and we plan to add [] for
> ARRAY and {} for MAP a little later, after we have built-in functions ARRAY()
> and MAP().
> 
> Or did you mean something else?

No, I misread your test, thanks for clarification.
> 
> > > +        SELECT a1(1) == CAST(1 AS SCALAR);

-- 
Konstantin Osipov, Moscow, Russia

Re: [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type ARRAY

[Vladislav Shpilevoy via Tarantool-patches]

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.

>    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.

> 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.

> 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.

> +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"
> +    })
> +

Re: [Tarantool-patches] [PATCH v1 1/2] sql: introduce field type ARRAY

[Mergen Imeev via 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 <imeevma@gmail.com>
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)

[Tarantool-patches] [PATCH v1 2/2] sql: introduce ARRAY() function

[Mergen Imeev via Tarantool-patches]

Closes #4762

@TarantoolBot document
Title: SQL built-in function ARRAY()

The SQL built-in function ARRAY() is now available. It takes one or more
arguments of type ANY and returns a value of type ARRAY. The return
value contains all the arguments accepted by the function, in the order
in which they were given.

Example:
```
tarantool> box.execute([[SELECT array(1, array('a', true), 2.5, x'31');]])
---
- metadata:
  - name: COLUMN_1
    type: array
  rows:
  - [[1, ['a', true], 2.5, '1']]
...
```
---
 .../gh-4762-introduce-array-to-sql.md         |  4 +++
 src/box/sql.c                                 |  2 +-
 src/box/sql/func.c                            | 24 +++++++++++++-
 src/box/sql/mem.c                             | 26 +++++++--------
 src/box/sql/mem.h                             | 23 ++++++-------
 src/box/sql/parse.y                           |  2 +-
 src/box/sql/vdbe.c                            |  5 ++-
 src/box/sql/vdbeapi.c                         |  4 +--
 test/sql-tap/array.test.lua                   | 33 ++++++++++++++++++-
 9 files changed, 90 insertions(+), 33 deletions(-)
 create mode 100644 changelogs/unreleased/gh-4762-introduce-array-to-sql.md

diff --git a/changelogs/unreleased/gh-4762-introduce-array-to-sql.md b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md
new file mode 100644
index 000000000..77040d1a4
--- /dev/null
+++ b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md
@@ -0,0 +1,4 @@
+## feature/core
+
+ * Field type ARRAY is now available in SQL. The ARRAY() function can be used to
+   create values of type ARRAY in SQL (gh-4762).
diff --git a/src/box/sql.c b/src/box/sql.c
index d15159d6e..2a78a96d5 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -211,7 +211,7 @@ sql_cursor_seek(struct BtCursor *cur, struct Mem *mems, uint32_t len, int *res)
 	struct region *region = &fiber()->gc;
 	size_t used = region_used(region);
 	uint32_t size;
-	const char *tuple = sql_vdbe_mem_encode_tuple(mems, len, &size, region);
+	const char *tuple = mem_encode_array(mems, len, &size, region);
 	if (tuple == NULL)
 		return -1;
 	if (key_alloc(cur, size) != 0)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 07f1ed50a..63bf58473 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -232,6 +232,26 @@ step_group_concat(struct sql_context *ctx, int argc, struct Mem *argv)
 		ctx->is_aborted = true;
 }
 
+/**
+ * Implementation of the ARRAY() function.
+ *
+ * The ARRAY() function takes one or more ANY arguments and returns ARRAY value
+ * that contains all the arguments received, in the order in which they were
+ * received.
+ */
+static void
+func_array(struct sql_context *ctx, int argc, struct Mem *argv)
+{
+	assert(argc >= 1);
+	uint32_t size;
+	struct region *region = &fiber()->gc;
+	size_t svp = region_used(region);
+	char *array = mem_encode_array(argv, argc, &size, region);
+	if (array == NULL || mem_copy_array(ctx->pOut, array, size) != 0)
+		ctx->is_aborted = true;
+	region_truncate(region, svp);
+}
+
 static const unsigned char *
 mem_as_ustr(struct Mem *mem)
 {
@@ -1867,6 +1887,7 @@ sql_builtin_stub(sql_context *ctx, int argc, struct Mem *argv)
 
 static struct sql_func_dictionary dictionaries[] = {
 	{"ABS", 1, 1, 0, true, 0, NULL},
+	{"ARRAY", 1, SQL_MAX_FUNCTION_ARG, 0, true, 0, NULL},
 	{"AVG", 1, 1, SQL_FUNC_AGG, false, 0, NULL},
 	{"CHAR", 0, SQL_MAX_FUNCTION_ARG, 0, true, 0, NULL},
 	{"CHARACTER_LENGTH", 1, 1, 0, true, 0, NULL},
@@ -1948,6 +1969,7 @@ struct sql_func_definition {
 static struct sql_func_definition definitions[] = {
 	{"ABS", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, absFunc, NULL},
 	{"ABS", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, absFunc, NULL},
+	{"ARRAY", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_ARRAY, func_array, NULL},
 	{"AVG", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, step_avg, fin_avg},
 	{"AVG", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, step_avg, fin_avg},
 	{"CHAR", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, charFunc, NULL},
@@ -2148,7 +2170,7 @@ is_upcast(int op, enum field_type a, enum field_type b)
 	return is_exact(op, a, b) ||
 	       (a == FIELD_TYPE_NUMBER && sql_type_is_numeric(b)) ||
 	       (a == FIELD_TYPE_SCALAR && b != FIELD_TYPE_MAP &&
-		b != FIELD_TYPE_ARRAY);
+		b != FIELD_TYPE_ARRAY) || a == FIELD_TYPE_ANY;
 }
 
 /**
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index c84bbe8fe..1d55a1209 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -3131,7 +3131,7 @@ mem_from_mp(struct Mem *mem, const char *buf, uint32_t *len)
 }
 
 void
-mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
+mem_encode_to_stream(const struct Mem *var, struct mpstream *stream)
 {
 	assert(memIsValid(var));
 	switch (var->type) {
@@ -3173,31 +3173,31 @@ mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
 }
 
 char *
-sql_vdbe_mem_encode_tuple(struct Mem *fields, uint32_t field_count,
-			  uint32_t *tuple_size, struct region *region)
+mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size,
+		 struct region *region)
 {
 	size_t used = region_used(region);
 	bool is_error = false;
 	struct mpstream stream;
 	mpstream_init(&stream, region, region_reserve_cb, region_alloc_cb,
 		      set_encode_error, &is_error);
-	mpstream_encode_array(&stream, field_count);
-	for (struct Mem *field = fields; field < fields + field_count; field++)
-		mpstream_encode_vdbe_mem(&stream, field);
+	mpstream_encode_array(&stream, count);
+	for (const struct Mem *mem = mems; mem < mems + count; mem++)
+		mem_encode_to_stream(mem, &stream);
 	mpstream_flush(&stream);
 	if (is_error) {
 		diag_set(OutOfMemory, stream.pos - stream.buf,
 			 "mpstream_flush", "stream");
 		return NULL;
 	}
-	*tuple_size = region_used(region) - used;
-	char *tuple = region_join(region, *tuple_size);
-	if (tuple == NULL) {
-		diag_set(OutOfMemory, *tuple_size, "region_join", "tuple");
+	*size = region_used(region) - used;
+	char *array = region_join(region, *size);
+	if (array == NULL) {
+		diag_set(OutOfMemory, *size, "region_join", "array");
 		return NULL;
 	}
-	mp_tuple_assert(tuple, tuple + *tuple_size);
-	return tuple;
+	mp_tuple_assert(array, array + *size);
+	return array;
 }
 
 /**
@@ -3288,7 +3288,7 @@ port_vdbemem_get_msgpack(struct port *base, uint32_t *size)
 		      set_encode_error, &is_error);
 	mpstream_encode_array(&stream, port->mem_count);
 	for (uint32_t i = 0; i < port->mem_count && !is_error; i++)
-		mpstream_encode_vdbe_mem(&stream, (struct Mem *)port->mem + i);
+		mem_encode_to_stream((struct Mem *)port->mem + i, &stream);
 	mpstream_flush(&stream);
 	*size = region_used(region) - region_svp;
 	if (is_error)
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index a5b590cd3..afef6d842 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -994,23 +994,24 @@ int
 mem_from_mp(struct Mem *mem, const char *buf, uint32_t *len);
 
 /**
- * Perform encoding memory variable to stream.
+ * Perform encoding of MEM to stream.
+ *
+ * @param var MEM to encode to stream.
  * @param stream Initialized mpstream encoder object.
- * @param var Vdbe memory variable to encode with stream.
  */
 void
-mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var);
+mem_encode_to_stream(const struct Mem *var, struct mpstream *stream);
 
 /**
- * Perform encoding field_count Vdbe memory fields on region as
- * msgpack array.
- * @param fields The first Vdbe memory field to encode.
- * @param field_count Count of fields to encode.
- * @param[out] tuple_size Size of encoded tuple.
+ * Encode array of MEMs as msgpack array on region.
+ *
+ * @param mems array of MEMs to encode.
+ * @param count number of elements in the array.
+ * @param[out] size Size of encoded msgpack array.
  * @param region Region to use.
  * @retval NULL on error, diag message is set.
- * @retval Pointer to valid tuple on success.
+ * @retval Pointer to valid msgpack array on success.
  */
 char *
-sql_vdbe_mem_encode_tuple(struct Mem *fields, uint32_t field_count,
-			  uint32_t *tuple_size, struct region *region);
+mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size,
+		 struct region *region);
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 4cae943d4..2b4b92e10 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -268,7 +268,7 @@ columnlist ::= tcons.
   CONFLICT DEFERRED END ENGINE FAIL
   IGNORE INITIALLY INSTEAD NO MATCH PLAN
   QUERY KEY OFFSET RAISE RELEASE REPLACE RESTRICT
-  RENAME CTIME_KW IF ENABLE DISABLE UUID
+  RENAME CTIME_KW IF ENABLE DISABLE UUID ARRAY
   .
 %wildcard WILDCARD.
 
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 323cbf04c..fd69afc74 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -2024,8 +2024,7 @@ case OP_MakeRecord: {
 	struct region *region = &fiber()->gc;
 	size_t used = region_used(region);
 	uint32_t tuple_size;
-	char *tuple =
-		sql_vdbe_mem_encode_tuple(pData0, nField, &tuple_size, region);
+	char *tuple = mem_encode_array(pData0, nField, &tuple_size, region);
 	if (tuple == NULL)
 		goto abort_due_to_error;
 	if ((int64_t)tuple_size > db->aLimit[SQL_LIMIT_LENGTH])
@@ -3485,7 +3484,7 @@ case OP_Update: {
 		mpstream_encode_array(&stream, 3);
 		mpstream_encode_strn(&stream, "=", 1);
 		mpstream_encode_uint(&stream, field_idx);
-		mpstream_encode_vdbe_mem(&stream, new_tuple + field_idx);
+		mem_encode_to_stream(new_tuple + field_idx, &stream);
 	}
 	mpstream_flush(&stream);
 	if (is_error) {
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index 8f7e28ffb..ae7783e4c 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -358,8 +358,8 @@ sql_stmt_result_to_msgpack(struct sql_stmt *stmt, uint32_t *tuple_size,
 			   struct region *region)
 {
 	struct Vdbe *vdbe = (struct Vdbe *)stmt;
-	return sql_vdbe_mem_encode_tuple(vdbe->pResultSet, vdbe->nResColumn,
-					 tuple_size, region);
+	return mem_encode_array(vdbe->pResultSet, vdbe->nResColumn, tuple_size,
+				region);
 }
 
 /*
diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua
index 2c0f687c0..44a2cc5e8 100755
--- a/test/sql-tap/array.test.lua
+++ b/test/sql-tap/array.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(110)
+test:plan(113)
 
 box.schema.func.create('A1', {
     language = 'Lua',
@@ -979,6 +979,37 @@ test:do_catchsql_test(
         1, "Failed to execute SQL statement: wrong arguments for function ZEROBLOB()"
     })
 
+box.execute([[CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, a ANY,
+                               g UNSIGNED, t STRING, n NUMBER, f DOUBLE,
+                               i INTEGER, b BOOLEAN, v VARBINARY, s SCALAR,
+                               d DECIMAL, u UUID);]])
+
+-- Make sure ARRAY() function works as intended.
+test:do_execsql_test(
+    "array-13.1",
+    [[
+        SELECT ARRAY(a, g, t, n, f, i, b, v, s, d, u) FROM t1 WHERE id = 1;
+    ]], {
+        1, 1, '1', 1, 1, 1, true, '1', 1, require('decimal').new(1),
+        require('uuid').fromstr('11111111-1111-1111-1111-111111111111')
+    })
+
+test:do_execsql_test(
+    "array-13.2",
+    [[
+        SELECT ARRAY(1, true, 1.5, ARRAY('asd', x'32'), cast(1234 as DECIMAL));
+    ]], {
+        1, true, 1.5, 'asd', '2', require('decimal').new(1234)
+    })
+
+test:do_execsql_test(
+    "array-13.3",
+    [[
+        SELECT typeof(ARRAY(1));
+    ]], {
+        "array"
+    })
+
 box.execute([[DROP TABLE t1;]])
 box.execute([[DROP TABLE t;]])
 
-- 
2.25.1

Re: [Tarantool-patches] [PATCH v1 2/2] sql: introduce ARRAY() function

[Mergen Imeev via Tarantool-patches]

In the new version of prevoius patch I changed the way ARRAY values are showed
in results of sql-tap tests. Diff and new patch below.

On Wed, Nov 03, 2021 at 11:17:52AM +0300, Mergen Imeev via Tarantool-patches wrote:
<cut>


Diff:

diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua
index 918854b47..4d292bcd8 100755
--- a/test/sql-tap/array.test.lua
+++ b/test/sql-tap/array.test.lua
@@ -990,16 +990,16 @@ test:do_execsql_test(
     [[
         SELECT ARRAY(a, g, t, n, f, i, b, v, s, d, u) FROM t1 WHERE id = 1;
     ]], {
-        1, 1, '1', 1, 1, 1, true, '1', 1, require('decimal').new(1),
-        require('uuid').fromstr('11111111-1111-1111-1111-111111111111')
+        {{1}, 1, '1', 1, 1, 1, true, '1', 1, require('decimal').new(1),
+         require('uuid').fromstr('11111111-1111-1111-1111-111111111111')}
     })
 
 test:do_execsql_test(
     "array-13.2",
     [[
-        SELECT ARRAY(1, true, 1.5, ARRAY('asd', x'32'), cast(1234 as DECIMAL));
+        SELECT ARRAY(1, true, 1.5e0, ARRAY('asd', x'32'), 1234.0);
     ]], {
-        1, true, 1.5, 'asd', '2', require('decimal').new(1234)
+        {1, true, 1.5, {'asd', '2'}, require('decimal').new(1234)}
     })
 
 test:do_execsql_test(


New patch:

commit 528165624bc88897ff88705a1299e7a7f50b4d4a
Author: Mergen Imeev <imeevma@gmail.com>
Date:   Tue Nov 2 15:35:55 2021 +0300

    sql: introduce ARRAY() function
    
    Closes #4762
    
    @TarantoolBot document
    Title: SQL built-in function ARRAY()
    
    The SQL built-in function ARRAY() is now available. It takes one or more
    arguments of type ANY and returns a value of type ARRAY. The return
    value contains all the arguments accepted by the function, in the order
    in which they were given.
    
    Example:
    ```
    tarantool> box.execute([[SELECT array(1, array('a', true), 2.5, x'31');]])
    ---
    - metadata:
      - name: COLUMN_1
        type: array
      rows:
      - [[1, ['a', true], 2.5, '1']]
    ...
    ```

diff --git a/changelogs/unreleased/gh-4762-introduce-array-to-sql.md b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md
new file mode 100644
index 000000000..77040d1a4
--- /dev/null
+++ b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md
@@ -0,0 +1,4 @@
+## feature/core
+
+ * Field type ARRAY is now available in SQL. The ARRAY() function can be used to
+   create values of type ARRAY in SQL (gh-4762).
diff --git a/src/box/sql.c b/src/box/sql.c
index d15159d6e..2a78a96d5 100644
--- a/src/box/sql.c
+++ b/src/box/sql.c
@@ -211,7 +211,7 @@ sql_cursor_seek(struct BtCursor *cur, struct Mem *mems, uint32_t len, int *res)
 	struct region *region = &fiber()->gc;
 	size_t used = region_used(region);
 	uint32_t size;
-	const char *tuple = sql_vdbe_mem_encode_tuple(mems, len, &size, region);
+	const char *tuple = mem_encode_array(mems, len, &size, region);
 	if (tuple == NULL)
 		return -1;
 	if (key_alloc(cur, size) != 0)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 07f1ed50a..63bf58473 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -232,6 +232,26 @@ step_group_concat(struct sql_context *ctx, int argc, struct Mem *argv)
 		ctx->is_aborted = true;
 }
 
+/**
+ * Implementation of the ARRAY() function.
+ *
+ * The ARRAY() function takes one or more ANY arguments and returns ARRAY value
+ * that contains all the arguments received, in the order in which they were
+ * received.
+ */
+static void
+func_array(struct sql_context *ctx, int argc, struct Mem *argv)
+{
+	assert(argc >= 1);
+	uint32_t size;
+	struct region *region = &fiber()->gc;
+	size_t svp = region_used(region);
+	char *array = mem_encode_array(argv, argc, &size, region);
+	if (array == NULL || mem_copy_array(ctx->pOut, array, size) != 0)
+		ctx->is_aborted = true;
+	region_truncate(region, svp);
+}
+
 static const unsigned char *
 mem_as_ustr(struct Mem *mem)
 {
@@ -1867,6 +1887,7 @@ sql_builtin_stub(sql_context *ctx, int argc, struct Mem *argv)
 
 static struct sql_func_dictionary dictionaries[] = {
 	{"ABS", 1, 1, 0, true, 0, NULL},
+	{"ARRAY", 1, SQL_MAX_FUNCTION_ARG, 0, true, 0, NULL},
 	{"AVG", 1, 1, SQL_FUNC_AGG, false, 0, NULL},
 	{"CHAR", 0, SQL_MAX_FUNCTION_ARG, 0, true, 0, NULL},
 	{"CHARACTER_LENGTH", 1, 1, 0, true, 0, NULL},
@@ -1948,6 +1969,7 @@ struct sql_func_definition {
 static struct sql_func_definition definitions[] = {
 	{"ABS", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, absFunc, NULL},
 	{"ABS", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, absFunc, NULL},
+	{"ARRAY", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_ARRAY, func_array, NULL},
 	{"AVG", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, step_avg, fin_avg},
 	{"AVG", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, step_avg, fin_avg},
 	{"CHAR", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, charFunc, NULL},
@@ -2148,7 +2170,7 @@ is_upcast(int op, enum field_type a, enum field_type b)
 	return is_exact(op, a, b) ||
 	       (a == FIELD_TYPE_NUMBER && sql_type_is_numeric(b)) ||
 	       (a == FIELD_TYPE_SCALAR && b != FIELD_TYPE_MAP &&
-		b != FIELD_TYPE_ARRAY);
+		b != FIELD_TYPE_ARRAY) || a == FIELD_TYPE_ANY;
 }
 
 /**
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index c84bbe8fe..1d55a1209 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -3131,7 +3131,7 @@ mem_from_mp(struct Mem *mem, const char *buf, uint32_t *len)
 }
 
 void
-mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
+mem_encode_to_stream(const struct Mem *var, struct mpstream *stream)
 {
 	assert(memIsValid(var));
 	switch (var->type) {
@@ -3173,31 +3173,31 @@ mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
 }
 
 char *
-sql_vdbe_mem_encode_tuple(struct Mem *fields, uint32_t field_count,
-			  uint32_t *tuple_size, struct region *region)
+mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size,
+		 struct region *region)
 {
 	size_t used = region_used(region);
 	bool is_error = false;
 	struct mpstream stream;
 	mpstream_init(&stream, region, region_reserve_cb, region_alloc_cb,
 		      set_encode_error, &is_error);
-	mpstream_encode_array(&stream, field_count);
-	for (struct Mem *field = fields; field < fields + field_count; field++)
-		mpstream_encode_vdbe_mem(&stream, field);
+	mpstream_encode_array(&stream, count);
+	for (const struct Mem *mem = mems; mem < mems + count; mem++)
+		mem_encode_to_stream(mem, &stream);
 	mpstream_flush(&stream);
 	if (is_error) {
 		diag_set(OutOfMemory, stream.pos - stream.buf,
 			 "mpstream_flush", "stream");
 		return NULL;
 	}
-	*tuple_size = region_used(region) - used;
-	char *tuple = region_join(region, *tuple_size);
-	if (tuple == NULL) {
-		diag_set(OutOfMemory, *tuple_size, "region_join", "tuple");
+	*size = region_used(region) - used;
+	char *array = region_join(region, *size);
+	if (array == NULL) {
+		diag_set(OutOfMemory, *size, "region_join", "array");
 		return NULL;
 	}
-	mp_tuple_assert(tuple, tuple + *tuple_size);
-	return tuple;
+	mp_tuple_assert(array, array + *size);
+	return array;
 }
 
 /**
@@ -3288,7 +3288,7 @@ port_vdbemem_get_msgpack(struct port *base, uint32_t *size)
 		      set_encode_error, &is_error);
 	mpstream_encode_array(&stream, port->mem_count);
 	for (uint32_t i = 0; i < port->mem_count && !is_error; i++)
-		mpstream_encode_vdbe_mem(&stream, (struct Mem *)port->mem + i);
+		mem_encode_to_stream((struct Mem *)port->mem + i, &stream);
 	mpstream_flush(&stream);
 	*size = region_used(region) - region_svp;
 	if (is_error)
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index a5b590cd3..afef6d842 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -994,23 +994,24 @@ int
 mem_from_mp(struct Mem *mem, const char *buf, uint32_t *len);
 
 /**
- * Perform encoding memory variable to stream.
+ * Perform encoding of MEM to stream.
+ *
+ * @param var MEM to encode to stream.
  * @param stream Initialized mpstream encoder object.
- * @param var Vdbe memory variable to encode with stream.
  */
 void
-mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var);
+mem_encode_to_stream(const struct Mem *var, struct mpstream *stream);
 
 /**
- * Perform encoding field_count Vdbe memory fields on region as
- * msgpack array.
- * @param fields The first Vdbe memory field to encode.
- * @param field_count Count of fields to encode.
- * @param[out] tuple_size Size of encoded tuple.
+ * Encode array of MEMs as msgpack array on region.
+ *
+ * @param mems array of MEMs to encode.
+ * @param count number of elements in the array.
+ * @param[out] size Size of encoded msgpack array.
  * @param region Region to use.
  * @retval NULL on error, diag message is set.
- * @retval Pointer to valid tuple on success.
+ * @retval Pointer to valid msgpack array on success.
  */
 char *
-sql_vdbe_mem_encode_tuple(struct Mem *fields, uint32_t field_count,
-			  uint32_t *tuple_size, struct region *region);
+mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size,
+		 struct region *region);
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index c93125a8b..a3ba5da08 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -268,7 +268,7 @@ columnlist ::= tcons.
   CONFLICT DEFERRED END ENGINE FAIL
   IGNORE INITIALLY INSTEAD NO MATCH PLAN
   QUERY KEY OFFSET RAISE RELEASE REPLACE RESTRICT
-  RENAME CTIME_KW IF ENABLE DISABLE UUID
+  RENAME CTIME_KW IF ENABLE DISABLE UUID ARRAY
   .
 %wildcard WILDCARD.
 
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 0c4e38557..02916cec3 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -2036,8 +2036,7 @@ case OP_MakeRecord: {
 	struct region *region = &fiber()->gc;
 	size_t used = region_used(region);
 	uint32_t tuple_size;
-	char *tuple =
-		sql_vdbe_mem_encode_tuple(pData0, nField, &tuple_size, region);
+	char *tuple = mem_encode_array(pData0, nField, &tuple_size, region);
 	if (tuple == NULL)
 		goto abort_due_to_error;
 	if ((int64_t)tuple_size > db->aLimit[SQL_LIMIT_LENGTH])
@@ -3497,7 +3496,7 @@ case OP_Update: {
 		mpstream_encode_array(&stream, 3);
 		mpstream_encode_strn(&stream, "=", 1);
 		mpstream_encode_uint(&stream, field_idx);
-		mpstream_encode_vdbe_mem(&stream, new_tuple + field_idx);
+		mem_encode_to_stream(new_tuple + field_idx, &stream);
 	}
 	mpstream_flush(&stream);
 	if (is_error) {
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index 486f797fb..d30e216cb 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -358,8 +358,8 @@ sql_stmt_result_to_msgpack(struct sql_stmt *stmt, uint32_t *tuple_size,
 			   struct region *region)
 {
 	struct Vdbe *vdbe = (struct Vdbe *)stmt;
-	return sql_vdbe_mem_encode_tuple(vdbe->pResultSet, vdbe->nResColumn,
-					 tuple_size, region);
+	return mem_encode_array(vdbe->pResultSet, vdbe->nResColumn, tuple_size,
+				region);
 }
 
 /*
diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua
index 746462ec6..4d292bcd8 100755
--- a/test/sql-tap/array.test.lua
+++ b/test/sql-tap/array.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(110)
+test:plan(113)
 
 box.schema.func.create('A1', {
     language = 'Lua',
@@ -979,6 +979,37 @@ test:do_catchsql_test(
         1, "Failed to execute SQL statement: wrong arguments for function ZEROBLOB()"
     })
 
+box.execute([[CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, a ANY,
+                               g UNSIGNED, t STRING, n NUMBER, f DOUBLE,
+                               i INTEGER, b BOOLEAN, v VARBINARY, s SCALAR,
+                               d DECIMAL, u UUID);]])
+
+-- Make sure ARRAY() function works as intended.
+test:do_execsql_test(
+    "array-13.1",
+    [[
+        SELECT ARRAY(a, g, t, n, f, i, b, v, s, d, u) FROM t1 WHERE id = 1;
+    ]], {
+        {{1}, 1, '1', 1, 1, 1, true, '1', 1, require('decimal').new(1),
+         require('uuid').fromstr('11111111-1111-1111-1111-111111111111')}
+    })
+
+test:do_execsql_test(
+    "array-13.2",
+    [[
+        SELECT ARRAY(1, true, 1.5e0, ARRAY('asd', x'32'), 1234.0);
+    ]], {
+        {1, true, 1.5, {'asd', '2'}, require('decimal').new(1234)}
+    })
+
+test:do_execsql_test(
+    "array-13.3",
+    [[
+        SELECT typeof(ARRAY(1));
+    ]], {
+        "array"
+    })
+
 box.execute([[DROP TABLE t1;]])
 box.execute([[DROP TABLE t;]])
 

Re: [Tarantool-patches] [PATCH v1 2/2] sql: introduce ARRAY() function

[Vladislav Shpilevoy via Tarantool-patches]

Thanks for the patch!

Is this new function really needed? It will become obsolete when proper
syntax for arrays will be introduced. And it does not help much now anyway
because there are no ways to set/get an item from an array.

Re: [Tarantool-patches] [PATCH v1 2/2] sql: introduce ARRAY() function

[Mergen Imeev via Tarantool-patches]

Thank you for the review! My answer below.

On Sun, Nov 14, 2021 at 05:12:16PM +0100, Vladislav Shpilevoy wrote:
> Thanks for the patch!
> 
> Is this new function really needed? It will become obsolete when proper
> syntax for arrays will be introduced. And it does not help much now anyway
> because there are no ways to set/get an item from an array.
True. I dropped this function on the branch.

Re: [Tarantool-patches] [PATCH v1 0/2] Introduce field type ARRAY to SQL

[Vladislav Shpilevoy via Tarantool-patches]

Hi! Thanks for the patch!

LGTM.