* [Tarantool-patches] [PATCH v1 1/1] sql: introduce field type ARRAY
@ 2021-11-22 8:23 Mergen Imeev via Tarantool-patches
2021-11-24 8:55 ` Kirill Yukhin via Tarantool-patches
0 siblings, 1 reply; 2+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-11-22 8:23 UTC (permalink / raw)
To: kyukhin; +Cc: 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 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.
---
https://github.com/tarantool/tarantool/issues/4762
https://github.com/tarantool/tarantool/tree/imeevma/gh-4762-introduce-array
.../gh-4762-introduce-array-to-sql.md | 3 +
| 1 +
src/box/sql/func.c | 14 +-
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 ++++++++++++++++++
test/sql-tap/lua/sqltester.lua | 4 +-
8 files changed, 1077 insertions(+), 47 deletions(-)
create mode 100644 changelogs/unreleased/gh-4762-introduce-array-to-sql.md
create mode 100755 test/sql-tap/array.test.lua
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).
--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 26ccebf83..fb6a27500 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -1367,8 +1367,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 234a00d57..c1c0a7d19 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -222,6 +222,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;
}
@@ -392,28 +394,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)
{
@@ -469,23 +477,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
@@ -541,6 +533,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)
{
@@ -1188,14 +1186,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)
{
@@ -1307,8 +1297,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:
@@ -1357,7 +1345,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;
}
@@ -1392,6 +1380,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;
@@ -1474,8 +1467,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;
}
@@ -2465,7 +2457,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;
@@ -3116,9 +3109,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;
@@ -3214,7 +3210,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) {
@@ -3248,6 +3245,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 1a3478f9a..691685ff7 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -429,6 +429,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)
--
2.25.1
^ permalink raw reply [flat|nested] 2+ messages in thread* Re: [Tarantool-patches] [PATCH v1 1/1] sql: introduce field type ARRAY
2021-11-22 8:23 [Tarantool-patches] [PATCH v1 1/1] sql: introduce field type ARRAY Mergen Imeev via Tarantool-patches
@ 2021-11-24 8:55 ` Kirill Yukhin via Tarantool-patches
0 siblings, 0 replies; 2+ messages in thread
From: Kirill Yukhin via Tarantool-patches @ 2021-11-24 8:55 UTC (permalink / raw)
To: imeevma; +Cc: tarantool-patches
Hello,
On 22 ноя 11:23, imeevma@tarantool.org 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
>
LGTM.
I've checked your patchset into master.
--
Regards, Kirill Yukhin
^ permalink raw reply [flat|nested] 2+ messages in thread
end of thread, other threads:[~2021-11-24 8:55 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2021-11-22 8:23 [Tarantool-patches] [PATCH v1 1/1] sql: introduce field type ARRAY Mergen Imeev via Tarantool-patches
2021-11-24 8:55 ` Kirill Yukhin via Tarantool-patches
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox