* [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
@ 2021-05-20 9:44 Mergen Imeev via Tarantool-patches
2021-05-21 7:16 ` Mergen Imeev via Tarantool-patches
2021-05-22 16:04 ` Vladislav Shpilevoy via Tarantool-patches
0 siblings, 2 replies; 8+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-05-20 9:44 UTC (permalink / raw)
To: v.shpilevoy; +Cc: tarantool-patches
This patch introduces UUID to SQL. UUID is now available as a new field
type.
Closes #5886
@TarantoolBot document
Title: Field type UUID is now available in SQL
The UUID field type is now available in SQL. This means that we can
create spaces and indexes with UUID, use it in SELECT, UPDATE and
DELETE. UUID can be accepted and returned by built-in functions and
user-defined functions.
During comparison, UUID cannot be implicitly converted to any other
types, but in any other case, UUID can be implicitly converted to STRING
or VARBINARY. UUID can be explicitly converted to STRING or VARBINARY
using CAST(). STRING and VARBINARY can be implicitly converted to UUID,
except for comparison. STRING and VARBINARY can be explicitly converted
to UUID using CAST().
---
https://github.com/tarantool/tarantool/issues/5886
https://github.com/tarantool/tarantool/tree/imeevma/gh-5886-introduce-uuid-type-in-sql
.../unreleased/introduce-uuid-to-sql.md | 3 +
| 1 +
src/box/sql/func.c | 30 +-
src/box/sql/mem.c | 223 ++-
src/box/sql/mem.h | 38 +-
src/box/sql/parse.y | 1 +
src/box/sql/vdbe.c | 15 +-
test/sql-tap/CMakeLists.txt | 1 +
.../gh-5913-segfault-on-select-uuid.test.lua | 42 +-
.../sql-tap/gh-6024-funcs-return-bin.test.lua | 8 +-
test/sql-tap/sql_uuid.c | 57 +
test/sql-tap/uuid.test.lua | 1259 +++++++++++++++++
12 files changed, 1607 insertions(+), 71 deletions(-)
create mode 100644 changelogs/unreleased/introduce-uuid-to-sql.md
create mode 100644 test/sql-tap/sql_uuid.c
create mode 100755 test/sql-tap/uuid.test.lua
diff --git a/changelogs/unreleased/introduce-uuid-to-sql.md b/changelogs/unreleased/introduce-uuid-to-sql.md
new file mode 100644
index 000000000..120fb145a
--- /dev/null
+++ b/changelogs/unreleased/introduce-uuid-to-sql.md
@@ -0,0 +1,3 @@
+## feature/core
+
+ * Field type UUID is now available in SQL (gh-5886).
--git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 7480c0211..0d998506c 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -172,6 +172,7 @@ static Keyword aKeywordTable[] = {
{ "UNSIGNED", "TK_UNSIGNED", true },
{ "UPDATE", "TK_UPDATE", true },
{ "USING", "TK_USING", true },
+ { "UUID" , "TK_UUID" , true },
{ "VALUES", "TK_VALUES", true },
{ "VARBINARY", "TK_VARBINARY", true },
{ "VIEW", "TK_VIEW", true },
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 90e8e152f..9c4480a92 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -58,7 +58,8 @@ static const void *
mem_as_bin(struct Mem *mem)
{
const char *s;
- if (!mem_is_bytes(mem) && mem_to_str(mem) != 0)
+ if (mem_cast_implicit(mem, FIELD_TYPE_VARBINARY) != 0 &&
+ mem_to_str(mem) != 0)
return NULL;
if (mem_get_bin(mem, &s) != 0)
return NULL;
@@ -142,26 +143,29 @@ typeofFunc(sql_context * context, int NotUsed, sql_value ** argv)
-1, SQL_STATIC);
return;
}
- switch (sql_value_type(argv[0])) {
- case MP_INT:
- case MP_UINT:
+ switch (argv[0]->type) {
+ case MEM_TYPE_INT:
+ case MEM_TYPE_UINT:
z = "integer";
break;
- case MP_STR:
+ case MEM_TYPE_STR:
z = "string";
break;
- case MP_DOUBLE:
+ case MEM_TYPE_DOUBLE:
z = "double";
break;
- case MP_BIN:
- case MP_ARRAY:
- case MP_MAP:
+ case MEM_TYPE_BIN:
+ case MEM_TYPE_ARRAY:
+ case MEM_TYPE_MAP:
z = "varbinary";
break;
- case MP_BOOL:
- case MP_NIL:
+ case MEM_TYPE_BOOL:
+ case MEM_TYPE_NULL:
z = "boolean";
break;
+ case MEM_TYPE_UUID:
+ z = "uuid";
+ break;
default:
unreachable();
break;
@@ -191,6 +195,7 @@ lengthFunc(sql_context * context, int argc, sql_value ** argv)
sql_result_uint(context, mem_len_unsafe(argv[0]));
break;
}
+ case MP_EXT:
case MP_STR:{
const unsigned char *z = mem_as_ustr(argv[0]);
if (z == 0)
@@ -235,6 +240,7 @@ absFunc(sql_context * context, int argc, sql_value ** argv)
}
case MP_BOOL:
case MP_BIN:
+ case MP_EXT:
case MP_ARRAY:
case MP_MAP: {
diag_set(ClientError, ER_INCONSISTENT_TYPES, "number",
@@ -1461,8 +1467,8 @@ trim_func_one_arg(struct sql_context *context, sql_value *arg)
default_trim = (const unsigned char *) "\0";
else
default_trim = (const unsigned char *) " ";
- int input_str_sz = mem_len_unsafe(arg);
const unsigned char *input_str = mem_as_ustr(arg);
+ int input_str_sz = mem_len_unsafe(arg);
uint8_t trim_char_len[1] = { 1 };
trim_procedure(context, TRIM_BOTH, default_trim, trim_char_len, 1,
input_str, input_str_sz);
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 47845599e..9288ef341 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -58,10 +58,22 @@ enum {
BUF_SIZE = 32,
};
+bool
+mem_is_field_compatible(const struct Mem *mem, enum field_type type,
+ bool is_nullable)
+{
+ if (mem->type == MEM_TYPE_UUID)
+ return (field_ext_type[type] & (1U << MP_UUID)) != 0;
+ enum mp_type mp_type = mem_mp_type(mem);
+ assert(mp_type != MP_EXT);
+ return field_mp_plain_type_is_compatible(type, mp_type, is_nullable);
+}
+
const char *
mem_str(const struct Mem *mem)
{
- char buf[BUF_SIZE];
+ assert((int)UUID_STR_LEN > (int)BUF_SIZE);
+ char buf[UUID_STR_LEN + 1];
switch (mem->type) {
case MEM_TYPE_NULL:
return "NULL";
@@ -81,6 +93,9 @@ mem_str(const struct Mem *mem)
case MEM_TYPE_MAP:
case MEM_TYPE_ARRAY:
return mp_str(mem->z);
+ case MEM_TYPE_UUID:
+ tt_uuid_to_string(&mem->u.uuid, &buf[0]);
+ return tt_sprintf("%s", buf);
case MEM_TYPE_BOOL:
return mem->u.b ? "TRUE" : "FALSE";
default:
@@ -190,6 +205,16 @@ mem_set_double(struct Mem *mem, double value)
mem->type = MEM_TYPE_DOUBLE;
}
+void
+mem_set_uuid(struct Mem *mem, struct tt_uuid *uuid)
+{
+ mem_clear(mem);
+ mem->field_type = FIELD_TYPE_UUID;
+ mem->u.uuid = *uuid;
+ mem->type = MEM_TYPE_UUID;
+ mem->flags = 0;
+}
+
static inline void
set_str_const(struct Mem *mem, char *value, uint32_t len, int alloc_type)
{
@@ -585,6 +610,18 @@ str_to_bin(struct Mem *mem)
return 0;
}
+static inline int
+str_to_uuid(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_STR);
+ if (tt_uuid_from_string(tt_cstr(mem->z, mem->n), &mem->u.uuid) != 0)
+ return -1;
+ mem->type = MEM_TYPE_UUID;
+ mem->flags = 0;
+ mem->field_type = FIELD_TYPE_UUID;
+ return 0;
+}
+
static inline int
str_to_bool(struct Mem *mem)
{
@@ -639,6 +676,20 @@ bin_to_str0(struct Mem *mem)
return 0;
}
+static inline int
+bin_to_uuid(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_BIN);
+ if (mem->n != UUID_LEN ||
+ tt_uuid_validate((struct tt_uuid *)mem->z) != 0)
+ return -1;
+ mem->u.uuid = *(struct tt_uuid *)mem->z;
+ mem->type = MEM_TYPE_UUID;
+ mem->flags = 0;
+ mem->field_type = FIELD_TYPE_UUID;
+ return 0;
+}
+
static inline int
bytes_to_int(struct Mem *mem)
{
@@ -810,6 +861,22 @@ map_to_str0(struct Mem *mem)
return mem_copy_str0(mem, str);
}
+static inline int
+uuid_to_str0(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_UUID);
+ char buf[UUID_STR_LEN + 1];
+ tt_uuid_to_string(&mem->u.uuid, &buf[0]);
+ return mem_copy_str0(mem, &buf[0]);
+}
+
+static inline int
+uuid_to_bin(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_UUID);
+ return mem_copy_bin(mem, (char *)&mem->u.uuid, UUID_LEN);
+}
+
int
mem_to_int(struct Mem *mem)
{
@@ -889,6 +956,8 @@ mem_to_str0(struct Mem *mem)
return map_to_str0(mem);
case MEM_TYPE_ARRAY:
return array_to_str0(mem);
+ case MEM_TYPE_UUID:
+ return uuid_to_str0(mem);
default:
return -1;
}
@@ -914,6 +983,8 @@ mem_to_str(struct Mem *mem)
return map_to_str0(mem);
case MEM_TYPE_ARRAY:
return array_to_str0(mem);
+ case MEM_TYPE_UUID:
+ return uuid_to_str0(mem);
default:
return -1;
}
@@ -966,9 +1037,19 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
return str_to_bin(mem);
if (mem_is_bytes(mem))
return 0;
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_bin(mem);
return -1;
case FIELD_TYPE_NUMBER:
return mem_to_number(mem);
+ case FIELD_TYPE_UUID:
+ if (mem->type == MEM_TYPE_UUID)
+ return 0;
+ if (mem->type == MEM_TYPE_STR)
+ return str_to_uuid(mem);
+ if (mem->type == MEM_TYPE_BIN)
+ return bin_to_uuid(mem);
+ return -1;
case FIELD_TYPE_SCALAR:
if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
return -1;
@@ -996,6 +1077,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
case FIELD_TYPE_STRING:
if (mem->type == MEM_TYPE_STR)
return 0;
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_str0(mem);
return -1;
case FIELD_TYPE_DOUBLE:
if (mem->type == MEM_TYPE_DOUBLE)
@@ -1017,6 +1100,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
if ((mem->type & (MEM_TYPE_BIN | MEM_TYPE_MAP |
MEM_TYPE_ARRAY)) != 0)
return 0;
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_bin(mem);
return -1;
case FIELD_TYPE_NUMBER:
if (mem_is_num(mem))
@@ -1034,6 +1119,14 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
return -1;
return 0;
+ case FIELD_TYPE_UUID:
+ if (mem->type == MEM_TYPE_UUID)
+ return 0;
+ if (mem->type == MEM_TYPE_STR)
+ return str_to_uuid(mem);
+ if (mem->type == MEM_TYPE_BIN)
+ return bin_to_uuid(mem);
+ return -1;
case FIELD_TYPE_ANY:
return 0;
default:
@@ -1063,6 +1156,8 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
return int_to_str0(mem);
if (mem->type == MEM_TYPE_DOUBLE)
return double_to_str0(mem);
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_str0(mem);
return -1;
case FIELD_TYPE_DOUBLE:
if (mem->type == MEM_TYPE_DOUBLE)
@@ -1087,6 +1182,8 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
case FIELD_TYPE_VARBINARY:
if (mem->type == MEM_TYPE_BIN)
return 0;
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_bin(mem);
return -1;
case FIELD_TYPE_NUMBER:
if (mem_is_num(mem))
@@ -1106,6 +1203,14 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
return -1;
return 0;
+ case FIELD_TYPE_UUID:
+ if (mem->type == MEM_TYPE_UUID)
+ return 0;
+ if (mem->type == MEM_TYPE_STR)
+ return str_to_uuid(mem);
+ if (mem->type == MEM_TYPE_BIN)
+ return bin_to_uuid(mem);
+ return -1;
default:
break;
}
@@ -1238,6 +1343,24 @@ mem_len(const struct Mem *mem, uint32_t *len)
return 0;
}
+int
+mem_get_uuid(const struct Mem *mem, struct tt_uuid *uuid)
+{
+ if ((mem->type & (MEM_TYPE_UUID | MEM_TYPE_STR | MEM_TYPE_BIN)) == 0)
+ return -1;
+ if (mem->type == MEM_TYPE_STR)
+ return tt_uuid_from_string(tt_cstr(mem->z, mem->n), uuid);
+ if (mem->type == MEM_TYPE_UUID) {
+ *uuid = mem->u.uuid;
+ return 0;
+ }
+ if (mem->n != UUID_LEN ||
+ tt_uuid_validate((struct tt_uuid *)mem->z) != 0)
+ return -1;
+ *uuid = *(struct tt_uuid *)mem->z;
+ return 0;
+}
+
int
mem_get_agg(const struct Mem *mem, void **accum)
{
@@ -1898,6 +2021,15 @@ mem_cmp_str(const struct Mem *left, const struct Mem *right, int *result,
return 0;
}
+int
+mem_cmp_uuid(const struct Mem *a, const struct Mem *b, int *result)
+{
+ if ((a->type & b->type & MEM_TYPE_UUID) == 0)
+ return -1;
+ *result = memcmp(&a->u.uuid, &b->u.uuid, UUID_LEN);
+ return 0;
+}
+
/*
* Both *pMem1 and *pMem2 contain string values. Compare the two values
* using the collation sequence pColl. As usual, return a negative , zero
@@ -1950,13 +2082,15 @@ mem_type_to_str(const struct Mem *p)
return "varbinary";
case MEM_TYPE_BOOL:
return "boolean";
+ case MEM_TYPE_UUID:
+ return "uuid";
default:
unreachable();
}
}
enum mp_type
-mem_mp_type(struct Mem *mem)
+mem_mp_type(const struct Mem *mem)
{
assert(mem->type < MEM_TYPE_INVALID);
switch (mem->type) {
@@ -1978,6 +2112,8 @@ mem_mp_type(struct Mem *mem)
return MP_BOOL;
case MEM_TYPE_DOUBLE:
return MP_DOUBLE;
+ case MEM_TYPE_UUID:
+ return MP_EXT;
default:
unreachable();
}
@@ -2359,6 +2495,14 @@ sqlMemCompare(const Mem * pMem1, const Mem * pMem2, const struct coll * pColl)
return -1;
}
+ if (((type1 | type2) & MEM_TYPE_UUID) != 0) {
+ if (mem_cmp_uuid(pMem1, pMem2, &res) == 0)
+ return res;
+ if (type1 != MEM_TYPE_UUID)
+ return +1;
+ return -1;
+ }
+
/* At least one of the two values is a number
*/
if (((type1 | type2) &
@@ -2566,11 +2710,24 @@ sqlVdbeCompareMsgpack(const char **key1,
case MP_ARRAY:
case MP_MAP:
case MP_EXT:{
- mem1.z = (char *)aKey1;
- mp_next(&aKey1);
- mem1.n = aKey1 - (char *)mem1.z;
- goto do_blob;
+ int8_t type;
+ const char *buf = aKey1;
+ uint32_t len = mp_decode_extl(&buf, &type);
+ buf = aKey1;
+ mp_next(&aKey1);
+ (void)len;
+ if (type == MP_UUID) {
+ assert(len == UUID_LEN);
+ mem1.type = MEM_TYPE_UUID;
+ if (mp_decode_uuid(&buf, &mem1.u.uuid) == NULL ||
+ mem_cmp_uuid(&mem1, pKey2, &rc) != 0)
+ rc = 1;
+ break;
}
+ mem1.z = (char *)buf;
+ mem1.n = aKey1 - buf;
+ goto do_blob;
+ }
}
*key1 = aKey1;
return rc;
@@ -2624,9 +2781,23 @@ mem_from_mp_ephemeral(struct Mem *mem, const char *buf, uint32_t *len)
break;
}
case MP_EXT: {
- mem->z = (char *)buf;
- mp_next(&buf);
- mem->n = buf - mem->z;
+ int8_t type;
+ const char *svp = buf;
+ uint32_t len = mp_decode_extl(&buf, &type);
+ (void)len;
+ if (type == MP_UUID) {
+ assert(len == UUID_LEN);
+ buf = svp;
+ if (mp_decode_uuid(&buf, &mem->u.uuid) == NULL)
+ return -1;
+ mem->type = MEM_TYPE_UUID;
+ mem->flags = 0;
+ mem->field_type = FIELD_TYPE_UUID;
+ break;
+ }
+ mem->z = (char *)svp;
+ mp_next(&svp);
+ mem->n = svp - mem->z;
mem->type = MEM_TYPE_BIN;
mem->flags = MEM_Ephem;
mem->field_type = FIELD_TYPE_VARBINARY;
@@ -2763,6 +2934,9 @@ mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
case MEM_TYPE_BOOL:
mpstream_encode_bool(stream, var->u.b);
return;
+ case MEM_TYPE_UUID:
+ mpstream_encode_uuid(stream, &var->u.uuid);
+ return;
default:
unreachable();
}
@@ -2849,6 +3023,11 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat)
case MEM_TYPE_BOOL:
lua_pushboolean(L, mem->u.b);
break;
+ case MEM_TYPE_UUID: {
+ struct tt_uuid *uuid = luaL_pushuuid(L);
+ *uuid = mem->u.uuid;
+ break;
+ }
default:
unreachable();
}
@@ -2972,16 +3151,13 @@ port_lua_get_vdbemem(struct port *base, uint32_t *size)
char buf[BUF_SIZE];
assert(field.ext_type == MP_UUID ||
field.ext_type == MP_DECIMAL);
- uint32_t size;
if (field.ext_type == MP_UUID) {
- size = mp_sizeof_uuid();
- assert(size < BUF_SIZE);
- mp_encode_uuid(&buf[0], field.uuidval);
- } else {
- size = mp_sizeof_decimal(field.decval);
- assert(size < BUF_SIZE);
- mp_encode_decimal(&buf[0], field.decval);
+ mem_set_uuid(&val[i], field.uuidval);
+ break;
}
+ uint32_t size = mp_sizeof_decimal(field.decval);
+ assert(size < BUF_SIZE);
+ mp_encode_decimal(&buf[0], field.decval);
if (mem_copy_bin(&val[i], buf, size) != 0)
goto error;
break;
@@ -3073,7 +3249,18 @@ port_c_get_vdbemem(struct port *base, uint32_t *size)
break;
case MP_EXT:
str = data;
- mp_next(&data);
+ int8_t type;
+ len = mp_decode_extl(&data, &type);
+ if (type == MP_UUID) {
+ assert(len == UUID_LEN);
+ struct tt_uuid *uuid = &val[i].u.uuid;
+ data = str;
+ if (mp_decode_uuid(&data, uuid) == NULL)
+ goto error;
+ val[i].type = MEM_TYPE_UUID;
+ break;
+ }
+ data += len;
if (mem_copy_bin(&val[i], str, data - str) != 0)
goto error;
break;
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index 15d97da0e..564d48067 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -30,6 +30,7 @@
* SUCH DAMAGE.
*/
#include "box/field_def.h"
+#include "uuid/tt_uuid.h"
struct sql;
struct Vdbe;
@@ -47,10 +48,11 @@ enum mem_type {
MEM_TYPE_MAP = 1 << 6,
MEM_TYPE_BOOL = 1 << 7,
MEM_TYPE_DOUBLE = 1 << 8,
- MEM_TYPE_INVALID = 1 << 9,
- MEM_TYPE_FRAME = 1 << 10,
- MEM_TYPE_PTR = 1 << 11,
- MEM_TYPE_AGG = 1 << 12,
+ MEM_TYPE_UUID = 1 << 9,
+ MEM_TYPE_INVALID = 1 << 10,
+ MEM_TYPE_FRAME = 1 << 11,
+ MEM_TYPE_PTR = 1 << 12,
+ MEM_TYPE_AGG = 1 << 13,
};
/*
@@ -72,6 +74,7 @@ struct Mem {
*/
struct func *func;
struct VdbeFrame *pFrame; /* Used when flags==MEM_Frame */
+ struct tt_uuid uuid;
} u;
/** Type of the value this MEM contains. */
enum mem_type type;
@@ -255,6 +258,11 @@ mem_is_any_null(const struct Mem *mem1, const struct Mem *mem2)
return ((mem1->type| mem2->type) & MEM_TYPE_NULL) != 0;
}
+/** Check if MEM is compatible with field type. */
+bool
+mem_is_field_compatible(const struct Mem *mem, enum field_type type,
+ bool is_nullable);
+
/**
* Return a string that represent content of MEM. String is either allocated
* using static_alloc() of just a static variable.
@@ -290,6 +298,10 @@ mem_set_bool(struct Mem *mem, bool value);
void
mem_set_double(struct Mem *mem, double value);
+/** Clear MEM and set it to UUID. */
+void
+mem_set_uuid(struct Mem *mem, struct tt_uuid *uuid);
+
/** Clear MEM and set it to STRING. The string belongs to another object. */
void
mem_set_str_ephemeral(struct Mem *mem, char *value, uint32_t len);
@@ -677,6 +689,14 @@ mem_cmp_str(const struct Mem *left, const struct Mem *right, int *result,
int
mem_cmp_num(const struct Mem *a, const struct Mem *b, int *result);
+/**
+ * Compare two MEMs and return the result of comparison. MEMs should be of
+ * UUID type or their values are converted to UUID according to
+ * implicit cast rules. Original MEMs are not changed.
+ */
+int
+mem_cmp_uuid(const struct Mem *left, const struct Mem *right, int *result);
+
/**
* Convert the given MEM to INTEGER. This function and the function below define
* the rules that are used to convert values of all other types to INTEGER. In
@@ -864,6 +884,14 @@ mem_get_bin(const struct Mem *mem, const char **s);
int
mem_len(const struct Mem *mem, uint32_t *len);
+/**
+ * Return value for MEM of UUID type. For MEM of all other types convert value
+ * of the MEM to UUID if possible and return converted value. Original MEM is
+ * not changed.
+ */
+int
+mem_get_uuid(const struct Mem *mem, struct tt_uuid *uuid);
+
/**
* Return length of value for MEM of STRING or VARBINARY type. This function is
* not safe since there is no proper processing in case mem_len() return an
@@ -898,7 +926,7 @@ mem_type_to_str(const struct Mem *p);
* transparent memory cell.
*/
enum mp_type
-mem_mp_type(struct Mem *mem);
+mem_mp_type(const struct Mem *mem);
enum mp_type
sql_value_type(struct Mem *);
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index abc363951..4c9cf475e 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1834,6 +1834,7 @@ typedef(A) ::= SCALAR . { A.type = FIELD_TYPE_SCALAR; }
typedef(A) ::= BOOL . { A.type = FIELD_TYPE_BOOLEAN; }
typedef(A) ::= BOOLEAN . { A.type = FIELD_TYPE_BOOLEAN; }
typedef(A) ::= VARBINARY . { A.type = FIELD_TYPE_VARBINARY; }
+typedef(A) ::= UUID . { A.type = FIELD_TYPE_UUID; }
/**
* Time-like types are temporary disabled, until they are
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 12ec703a2..c78674882 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1322,10 +1322,10 @@ case OP_FunctionByName: {
region_truncate(region, region_svp);
if (mem == NULL)
goto abort_due_to_error;
- enum mp_type type = sql_value_type((sql_value *)pOut);
- if (!field_mp_plain_type_is_compatible(returns, type, true)) {
+ if (!mem_is_field_compatible(pOut, returns, true)) {
diag_set(ClientError, ER_FUNC_INVALID_RETURN_TYPE, pOp->p4.z,
- field_type_strs[returns], mp_type_strs[type]);
+ field_type_strs[returns],
+ mp_type_strs[mem_mp_type(pOut)]);
goto abort_due_to_error;
}
@@ -1634,6 +1634,15 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
"boolean");
goto abort_due_to_error;
}
+ } else if (((pIn3->type | pIn1->type) & MEM_TYPE_UUID) != 0) {
+ if (mem_cmp_uuid(pIn3, pIn1, &res) != 0) {
+ char *str = pIn3->type != MEM_TYPE_UUID ?
+ mem_type_to_str(pIn3) :
+ mem_type_to_str(pIn1);
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH, str,
+ "uuid");
+ goto abort_due_to_error;
+ }
} else if (mem_is_bin(pIn3) || mem_is_bin(pIn1)) {
if (mem_cmp_bin(pIn3, pIn1, &res) != 0) {
char *str = !mem_is_bin(pIn3) ?
diff --git a/test/sql-tap/CMakeLists.txt b/test/sql-tap/CMakeLists.txt
index bf0c3a11d..bd2b9f33f 100644
--- a/test/sql-tap/CMakeLists.txt
+++ b/test/sql-tap/CMakeLists.txt
@@ -1,3 +1,4 @@
include_directories(${MSGPUCK_INCLUDE_DIRS})
build_module(gh-5938-wrong-string-length gh-5938-wrong-string-length.c)
build_module(gh-6024-funcs-return-bin gh-6024-funcs-return-bin.c)
+build_module(sql_uuid sql_uuid.c)
diff --git a/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua b/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua
index 60978c2b5..34b22002a 100755
--- a/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua
+++ b/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(6)
+test:plan(4)
local uuid = require("uuid").fromstr("11111111-1111-1111-1111-111111111111")
local decimal = require("decimal").new(111.111)
@@ -13,16 +13,16 @@ box.space.T:insert({1, uuid, decimal})
--
-- Make sure that there is no segmentation fault on select from field that
--- contains UUID or DECIMAL. Currently SQL does not support UUID and DECIMAL,
--- so they treated as VARBINARY.
+-- contains UUID or DECIMAL. Currently SQL does not support DECIMAL, so it is
+-- treated as VARBINARY.
--
test:do_execsql_test(
"gh-5913-1",
[[
SELECT i, u, d FROM t;
- SELECT i from t;
+ SELECT i, u from t;
]], {
- 1
+ 1, uuid
})
box.schema.create_space('T1')
@@ -32,19 +32,11 @@ box.space.T1:format({{name = "I", type = "integer"},
box.space.T1:create_index("primary")
--
--- Since SQL does not support UUID and DECIMAL and they treated as VARBINARY,
--- they cannot be inserted from SQL.
+-- Since SQL does not support DECIMAL and it is treated as VARBINARY, it cannot
+-- be inserted from SQL.
--
test:do_catchsql_test(
"gh-5913-2",
- [[
- INSERT INTO t1 SELECT i, u, NULL FROM t;
- ]], {
- 1, "Type mismatch: can not convert varbinary to uuid"
- })
-
-test:do_catchsql_test(
- "gh-5913-3",
[[
INSERT INTO t1 SELECT i, NULL, d FROM t;
]], {
@@ -52,11 +44,11 @@ test:do_catchsql_test(
})
--
--- Still, if UUID or DECIMAL fields does not selected directly, insert is
--- working properly.
+-- Still, if DECIMAL fields does not selected directly, insert is working
+-- properly in case the space which receives these values is empty.
--
test:do_execsql_test(
- "gh-5913-4",
+ "gh-5913-3",
[[
INSERT INTO t1 SELECT * FROM t;
SELECT count() FROM t1;
@@ -77,19 +69,11 @@ box.space.TD:create_index("primary")
box.space.TD:insert({1, decimal})
--
--- Update of UUID or VARBINARY also does not lead to segfault, however throws an
--- error since after changing value cannot be inserted into the field from SQL.
+-- Update of VARBINARY also does not lead to segfault, however throws an error
+-- since after changing value cannot be inserted into the field from SQL.
--
test:do_catchsql_test(
- "gh-5913-5",
- [[
- UPDATE tu SET u = u;
- ]], {
- 1, "Type mismatch: can not convert varbinary to uuid"
- })
-
-test:do_catchsql_test(
- "gh-5913-6",
+ "gh-5913-4",
[[
UPDATE td SET d = d;
]], {
diff --git a/test/sql-tap/gh-6024-funcs-return-bin.test.lua b/test/sql-tap/gh-6024-funcs-return-bin.test.lua
index 9069379e1..c2954bba0 100755
--- a/test/sql-tap/gh-6024-funcs-return-bin.test.lua
+++ b/test/sql-tap/gh-6024-funcs-return-bin.test.lua
@@ -23,7 +23,7 @@ test:do_execsql_test(
box.schema.func.create("gh-6024-funcs-return-bin.ret_uuid", {
language = "C",
param_list = {},
- returns = "varbinary",
+ returns = "uuid",
exports = {"SQL"},
})
@@ -32,7 +32,7 @@ test:do_execsql_test(
[[
SELECT typeof("gh-6024-funcs-return-bin.ret_uuid"());
]], {
- "varbinary"
+ "uuid"
})
box.schema.func.create("gh-6024-funcs-return-bin.ret_decimal", {
@@ -53,7 +53,7 @@ test:do_execsql_test(
box.schema.func.create("get_uuid", {
language = "LUA",
param_list = {},
- returns = "varbinary",
+ returns = "uuid",
body = "function(x) return require('uuid').fromstr('11111111-1111-1111-1111-111111111111') end",
exports = {"SQL"},
})
@@ -63,7 +63,7 @@ test:do_execsql_test(
[[
SELECT typeof("get_uuid"()), "get_uuid"() == "gh-6024-funcs-return-bin.ret_uuid"();
]], {
- "varbinary", true
+ "uuid", true
})
box.schema.func.create("get_decimal", {
diff --git a/test/sql-tap/sql_uuid.c b/test/sql-tap/sql_uuid.c
new file mode 100644
index 000000000..592b9e48f
--- /dev/null
+++ b/test/sql-tap/sql_uuid.c
@@ -0,0 +1,57 @@
+#include "msgpuck.h"
+#include "module.h"
+#include "uuid/mp_uuid.h"
+#include "mp_extension_types.h"
+
+enum {
+ BUF_SIZE = 512,
+};
+
+int
+is_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
+{
+ uint32_t arg_count = mp_decode_array(&args);
+ if (arg_count != 1) {
+ return box_error_set(__FILE__, __LINE__, ER_PROC_C,
+ "invalid argument count");
+ }
+ bool is_uuid;
+ if (mp_typeof(*args) == MP_EXT) {
+ const char *str = args;
+ int8_t type;
+ mp_decode_extl(&str, &type);
+ is_uuid = type == MP_UUID;
+ } else {
+ is_uuid = false;
+ }
+
+ char tuple_buf[BUF_SIZE];
+ assert(mp_sizeof_array(1) + mp_sizeof_bool(is_uuid) < BUF_SIZE);
+ char *d = tuple_buf;
+ d = mp_encode_array(d, 1);
+ d = mp_encode_bool(d, is_uuid);
+
+ box_tuple_format_t *fmt = box_tuple_format_default();
+ box_tuple_t *tuple = box_tuple_new(fmt, tuple_buf, d);
+ if (tuple == NULL)
+ return -1;
+ return box_return_tuple(ctx, tuple);
+}
+
+int
+ret_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
+{
+ struct tt_uuid uuid;
+ memset(&uuid, 0x11, sizeof(uuid));
+ char tuple_buf[BUF_SIZE];
+ assert(mp_sizeof_array(1) + mp_sizeof_uuid() < BUF_SIZE);
+ char *d = tuple_buf;
+ d = mp_encode_array(d, 1);
+ d = mp_encode_uuid(d, &uuid);
+
+ box_tuple_format_t *fmt = box_tuple_format_default();
+ box_tuple_t *tuple = box_tuple_new(fmt, tuple_buf, d);
+ if (tuple == NULL)
+ return -1;
+ return box_return_tuple(ctx, tuple);
+}
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
new file mode 100755
index 000000000..9210d05db
--- /dev/null
+++ b/test/sql-tap/uuid.test.lua
@@ -0,0 +1,1259 @@
+#!/usr/bin/env tarantool
+local build_path = os.getenv("BUILDDIR")
+package.cpath = build_path..'/test/sql-tap/?.so;'..build_path..'/test/sql-tap/?.dylib;'..package.cpath
+
+local test = require("sqltester")
+test:plan(137)
+
+local uuid = require("uuid")
+local uuid1 = uuid.fromstr("11111111-1111-1111-1111-111111111111")
+local uuid2 = uuid.fromstr("22222222-1111-1111-1111-111111111111")
+local uuid3 = uuid.fromstr("11111111-3333-1111-1111-111111111111")
+
+-- Check that it is possible to create spaces with UUID field.
+test:do_execsql_test(
+ "uuid-1",
+ [[
+ CREATE TABLE t1 (i INT PRIMARY KEY, u UUID);
+ CREATE TABLE t2 (u UUID PRIMARY KEY);
+ ]], {
+ })
+
+box.space.T1:insert({1, uuid1})
+box.space.T1:insert({2, uuid2})
+box.space.T1:insert({3, uuid3})
+box.space.T1:insert({4, uuid1})
+box.space.T1:insert({5, uuid1})
+box.space.T1:insert({6, uuid2})
+box.space.T2:insert({uuid1})
+box.space.T2:insert({uuid2})
+box.space.T2:insert({uuid3})
+
+-- Check that SELECT can work with UUID.
+test:do_execsql_test(
+ "uuid-2.1.1",
+ [[
+ SELECT * FROM t1;
+ ]], {
+ 1, uuid1, 2, uuid2, 3, uuid3, 4, uuid1, 5, uuid1, 6, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-2.1.2",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+-- Check that ORDER BY can work with UUID.
+test:do_execsql_test(
+ "uuid-2.2.1",
+ [[
+ SELECT * FROM t1 ORDER BY u;
+ ]], {
+ 1, uuid1, 4, uuid1, 5, uuid1, 3, uuid3, 2, uuid2, 6, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-2.2.2",
+ [[
+ SELECT * FROM t1 ORDER BY u DESC;
+ ]], {
+ 2, uuid2, 6, uuid2, 3, uuid3, 1, uuid1, 4, uuid1, 5, uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-2.2.3",
+ [[
+ SELECT * FROM t2 ORDER BY u;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-2.2.4",
+ [[
+ SELECT * FROM t2 ORDER BY u DESC;
+ ]], {
+ uuid2, uuid3, uuid1
+ })
+
+-- Check that GROUP BY can work with UUID.
+test:do_execsql_test(
+ "uuid-2.3.1",
+ [[
+ SELECT count(*), u FROM t1 GROUP BY u;
+ ]], {
+ 3, uuid1, 1, uuid3, 2, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-2.3.2",
+ [[
+ SELECT count(*), u FROM t2 GROUP BY u;
+ ]], {
+ 1, uuid1, 1, uuid3, 1, uuid2
+ })
+
+-- Check that subselects can work with UUID.
+test:do_execsql_test(
+ "uuid-2.4",
+ [[
+ SELECT * FROM (SELECT * FROM (SELECT * FROM t2 LIMIT 2) LIMIT 2 OFFSET 1);
+ ]], {
+ uuid3
+ })
+
+-- Check that DISTINCT can work with UUID.
+test:do_execsql_test(
+ "uuid-2.5",
+ [[
+ SELECT DISTINCT u FROM t1;
+ ]], {
+ uuid1, uuid2, uuid3
+ })
+
+-- Check that VIEW can work with UUID.
+test:do_execsql_test(
+ "uuid-2.6",
+ [[
+ CREATE VIEW v AS SELECT u FROM t1;
+ SELECT * FROM v;
+ ]], {
+ uuid1, uuid2, uuid3, uuid1, uuid1, uuid2
+ })
+
+-- Check that LIMIT does not accept UUID as argument.
+test:do_catchsql_test(
+ "uuid-3.1",
+ [[
+ SELECT 1 LIMIT (SELECT u FROM t1 LIMIT 1);
+ ]], {
+ 1, "Failed to execute SQL statement: Only positive integers are allowed in the LIMIT clause"
+ })
+
+-- Check that OFFSET does not accept UUID as argument.
+test:do_catchsql_test(
+ "uuid-3.2",
+ [[
+ SELECT 1 LIMIT 1 OFFSET (SELECT u FROM t1 LIMIT 1);
+ ]], {
+ 1, "Failed to execute SQL statement: Only positive integers are allowed in the OFFSET clause"
+ })
+
+-- Check that ephemeral space can work with UUID.
+test:do_execsql_test(
+ "uuid-4",
+ [[
+ EXPLAIN SELECT * from (VALUES(1)), t2;
+ ]], {
+ "/OpenTEphemeral/"
+ })
+
+test:execsql([[
+ CREATE TABLE t5f (u UUID PRIMARY KEY, f UUID REFERENCES t5f(u));
+ CREATE TABLE t5c (i INT PRIMARY KEY, f UUID, CONSTRAINT ck CHECK(CAST(f AS STRING) != '11111111-1111-1111-1111-111111111111'));
+ CREATE TABLE t5u (i INT PRIMARY KEY, f UUID UNIQUE);
+]])
+
+-- Check that FOREIGN KEY constraint can work with UUID.
+test:do_catchsql_test(
+ "uuid-5.1.1",
+ [[
+ INSERT INTO t5f SELECT (SELECT u from t2 LIMIT 1 OFFSET 1), (SELECT u from t2 LIMIT 1);
+ SELECT * from t5f;
+ ]], {
+ 1, "Failed to execute SQL statement: FOREIGN KEY constraint failed"
+ })
+
+test:do_execsql_test(
+ "uuid-5.1.2",
+ [[
+ INSERT INTO t5f SELECT u, u from t2 LIMIT 1;
+ SELECT * from t5f;
+ ]], {
+ uuid1, uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-5.1.3",
+ [[
+ INSERT INTO t5f SELECT (SELECT u from t2 LIMIT 1 OFFSET 1), (SELECT u from t2 LIMIT 1);
+ SELECT * from t5f;
+ ]], {
+ uuid1, uuid1, uuid3, uuid1
+ })
+
+-- Check that CHECK constraint can work with UUID.
+test:do_catchsql_test(
+ "uuid-5.2.1",
+ [[
+ INSERT INTO t5c SELECT 1, u FROM t2 LIMIT 1;
+ SELECT * from t5c;
+ ]], {
+ 1, "Check constraint failed 'CK': CAST(f AS STRING) != '11111111-1111-1111-1111-111111111111'"
+ })
+
+test:do_execsql_test(
+ "uuid-5.2.2",
+ [[
+ INSERT INTO t5c SELECT 2, u FROM t2 LIMIT 1 OFFSET 1;
+ SELECT * from t5c;
+ ]], {
+ 2, uuid3
+ })
+
+-- Check that UNIQUE constraint can work with UUID.
+test:do_execsql_test(
+ "uuid-5.3.1",
+ [[
+ INSERT INTO t5u SELECT 1, u FROM t2 LIMIT 1;
+ SELECT * from t5u;
+ ]], {
+ 1, uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-5.3.2",
+ [[
+ INSERT INTO t5u SELECT 2, u FROM t2 LIMIT 1;
+ SELECT * from t5u;
+ ]], {
+ 1, 'Duplicate key exists in unique index "unique_unnamed_T5U_2" '..
+ 'in space "T5U" with old tuple - '..
+ '[1, 11111111-1111-1111-1111-111111111111] and new tuple - '..
+ '[2, 11111111-1111-1111-1111-111111111111]'
+ })
+
+-- Check that built-in functions work with UUIDs as intended.
+test:do_catchsql_test(
+ "uuid-6.1.1",
+ [[
+ SELECT ABS(u) from t2;
+ ]], {
+ 1, "Inconsistent types: expected number got uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.2",
+ [[
+ SELECT AVG(u) from t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.3",
+ [[
+ SELECT CHAR(u) from t2;
+ ]], {
+ "\0", "\0", "\0"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.4",
+ [[
+ SELECT CHARACTER_LENGTH(u) from t2;
+ ]], {
+ 36, 36, 36
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.5",
+ [[
+ SELECT CHAR_LENGTH(u) from t2;
+ ]], {
+ 36, 36, 36
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.6",
+ [[
+ SELECT COALESCE(NULL, u, NULL, NULL) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.7",
+ [[
+ SELECT COUNT(u) from t2;
+ ]], {
+ 3
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.8",
+ [[
+ SELECT GREATEST((SELECT u FROM t2 LIMIT 1), (SELECT u FROM t2 LIMIT 1 OFFSET 1));
+ ]], {
+ uuid3
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.9",
+ [[
+ SELECT GROUP_CONCAT(u) from t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111,"..
+ "11111111-3333-1111-1111-111111111111,"..
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.10",
+ [[
+ SELECT HEX(u) from t2;
+ ]], {
+ "11111111111111111111111111111111",
+ "11111111333311111111111111111111",
+ "22222222111111111111111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.11",
+ [[
+ SELECT IFNULL(u, NULL) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.12",
+ [[
+ SELECT LEAST((SELECT u FROM t2 LIMIT 1), (SELECT u FROM t2 LIMIT 1 OFFSET 1));
+ ]], {
+ uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.13",
+ [[
+ SELECT LENGTH(u) from t2;
+ ]], {
+ 36, 36, 36
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.14",
+ [[
+ SELECT u LIKE 'a' from t2;
+ ]], {
+ 1, "Inconsistent types: expected text got uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.15",
+ [[
+ SELECT LIKELIHOOD(u, 0.5) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.16",
+ [[
+ SELECT LIKELY(u) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.17",
+ [[
+ SELECT LOWER(u) from t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111",
+ "11111111-3333-1111-1111-111111111111",
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.18",
+ [[
+ SELECT MAX(u) from t2;
+ ]], {
+ uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.19",
+ [[
+ SELECT MIN(u) from t2;
+ ]], {
+ uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.20",
+ [[
+ SELECT NULLIF(u, 1) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.21",
+ [[
+ SELECT POSITION(u, '1') from t2;
+ ]], {
+ 1, "Inconsistent types: expected text or varbinary got uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.22",
+ [[
+ SELECT RANDOMBLOB(u) from t2;
+ ]], {
+ "", "", ""
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.23",
+ [[
+ SELECT REPLACE(u, '1', '2') from t2;
+ ]], {
+ "22222222-2222-2222-2222-222222222222",
+ "22222222-3333-2222-2222-222222222222",
+ "22222222-2222-2222-2222-222222222222"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.24",
+ [[
+ SELECT ROUND(u) from t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.25",
+ [[
+ SELECT SOUNDEX(u) from t2;
+ ]], {
+ "?000", "?000", "?000"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.26",
+ [[
+ SELECT SUBSTR(u, 3, 3) from t2;
+ ]], {
+ "111", "111", "222"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.27",
+ [[
+ SELECT SUM(u) from t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.28",
+ [[
+ SELECT TOTAL(u) from t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.29",
+ [[
+ SELECT TRIM(u) from t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111",
+ "11111111-3333-1111-1111-111111111111",
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.30",
+ [[
+ SELECT TYPEOF(u) from t2;
+ ]], {
+ "uuid", "uuid", "uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.31",
+ [[
+ SELECT UNICODE(u) from t2;
+ ]], {
+ 49, 49, 50
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.32",
+ [[
+ SELECT UNLIKELY(u) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.33",
+ [[
+ SELECT UPPER(u) from t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111",
+ "11111111-3333-1111-1111-111111111111",
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.33",
+ [[
+ SELECT u || u from t2;
+ ]], {
+ 1, "Inconsistent types: expected text or varbinary got uuid"
+ })
+
+local func = {language = 'Lua', body = 'function(x) return type(x) end',
+ returns = 'string', param_list = {'any'}, exports = {'SQL'}}
+box.schema.func.create('RETURN_TYPE', func);
+
+-- Check that Lua user-defined functions can accept UUID.
+test:do_execsql_test(
+ "uuid-6.2",
+ [[
+ SELECT RETURN_TYPE(u) FROM t2;
+ ]], {
+ "cdata", "cdata", "cdata"
+ })
+
+func = {language = 'Lua', returns = 'uuid', param_list = {}, exports = {'SQL'},
+ body = 'function(x) return require("uuid").fromstr("11111111-1111-1111-1111-111111111111") end'}
+box.schema.func.create('GET_UUID', func);
+
+-- Check that Lua user-defined functions can return UUID.
+test:do_execsql_test(
+ "uuid-6.3",
+ [[
+ SELECT GET_UUID();
+ ]], {
+ uuid1
+ })
+
+func = {language = 'C', returns = 'boolean', param_list = {'any'}, exports = {'SQL'}}
+box.schema.func.create("sql_uuid.is_uuid", func)
+
+-- Check that C user-defined functions can accept UUID.
+test:do_execsql_test(
+ "uuid-6.4",
+ [[
+ SELECT "sql_uuid.is_uuid"(i), "sql_uuid.is_uuid"(u) FROM t1 LIMIT 1;
+ ]], {
+ false, true
+ })
+
+func = {language = 'C', returns = 'uuid', param_list = {}, exports = {'SQL'}}
+box.schema.func.create("sql_uuid.ret_uuid", func)
+
+-- Check that C user-defined functions can return UUID.
+test:do_execsql_test(
+ "uuid-6.5",
+ [[
+ SELECT "sql_uuid.ret_uuid"();
+ ]], {
+ uuid1
+ })
+
+-- Check that explicit cast from UUID to another types works as intended.
+test:do_catchsql_test(
+ "uuid-7.1.1",
+ [[
+ SELECT cast(u AS UNSIGNED) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to unsigned"
+ })
+
+test:do_execsql_test(
+ "uuid-7.1.2",
+ [[
+ SELECT cast(u AS STRING) FROM t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111",
+ "11111111-3333-1111-1111-111111111111",
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.1.3",
+ [[
+ SELECT cast(u AS NUMBER) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.1.4",
+ [[
+ SELECT cast(u AS DOUBLE) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to double"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.1.5",
+ [[
+ SELECT cast(u AS INTEGER) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.1.6",
+ [[
+ SELECT cast(u AS BOOLEAN) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_execsql_test(
+ "uuid-7.1.7",
+ [[
+ SELECT hex(cast(u AS VARBINARY)) FROM t2;
+ ]], {
+ "11111111111111111111111111111111",
+ "11111111333311111111111111111111",
+ "22222222111111111111111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-7.1.8",
+ [[
+ SELECT cast(u AS SCALAR) FROM t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-7.1.9",
+ [[
+ SELECT cast(u AS UUID) FROM t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+-- Check that explicit cast from another types to UUID works as intended.
+test:do_catchsql_test(
+ "uuid-7.2.1",
+ [[
+ SELECT cast(1 AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert 1 to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-7.2.2",
+ [[
+ SELECT cast('11111111-1111-1111-1111-111111111111' AS UUID);
+ ]], {
+ uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.3",
+ [[
+ SELECT cast('1' AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert 1 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.4",
+ [[
+ SELECT cast(1.5 AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert 1.5 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.5",
+ [[
+ SELECT cast(-1 AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert -1 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.6",
+ [[
+ SELECT cast(true AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert TRUE to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-7.2.7",
+ [[
+ SELECT cast(x'11111111111111111111111111111111' AS UUID);
+ ]], {
+ uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.8",
+ [[
+ SELECT cast(randomblob(10) as UUID) FROM t2 LIMIT 1;
+ ]], {
+ 1, "Type mismatch: can not convert varbinary to uuid"
+ })
+
+test:execsql([[
+ CREATE TABLE tu (id INT PRIMARY KEY AUTOINCREMENT, u UNSIGNED);
+ CREATE TABLE ts (id INT PRIMARY KEY AUTOINCREMENT, s STRING);
+ CREATE TABLE tn (id INT PRIMARY KEY AUTOINCREMENT, n NUMBER);
+ CREATE TABLE td (id INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);
+ CREATE TABLE ti (id INT PRIMARY KEY AUTOINCREMENT, i INTEGER);
+ CREATE TABLE tb (id INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);
+ CREATE TABLE tv (id INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);
+ CREATE TABLE tsc (id INT PRIMARY KEY AUTOINCREMENT, sc SCALAR);
+ CREATE TABLE tuu (id INT PRIMARY KEY AUTOINCREMENT, uu UUID);
+ CREATE TABLE tsu (s STRING PRIMARY KEY, u UUID);
+]])
+
+-- Check that implcit cast from UUID to another types works as intended.
+test:do_catchsql_test(
+ "uuid-8.1.1",
+ [[
+ INSERT INTO tu(u) SELECT u FROM t2;
+ SELECT * FROM tu;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to unsigned"
+ })
+
+test:do_execsql_test(
+ "uuid-8.1.2",
+ [[
+ INSERT INTO ts(s) SELECT u FROM t2;
+ SELECT * FROM ts;
+ ]], {
+ 1, "11111111-1111-1111-1111-111111111111",
+ 2, "11111111-3333-1111-1111-111111111111",
+ 3, "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.1.3",
+ [[
+ INSERT INTO tn(n) SELECT u FROM t2;
+ SELECT * FROM tn;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.1.4",
+ [[
+ INSERT INTO td(d) SELECT u FROM t2;
+ SELECT * FROM td;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to double"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.1.5",
+ [[
+ INSERT INTO ti(i) SELECT u FROM t2;
+ SELECT * FROM ti;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.1.6",
+ [[
+ INSERT INTO tb(b) SELECT u FROM t2;
+ SELECT * FROM tb;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_execsql_test(
+ "uuid-8.1.7",
+ [[
+ INSERT INTO tv(v) SELECT u FROM t2;
+ SELECT id, hex(v) FROM tv;
+ ]], {
+ 1, "11111111111111111111111111111111",
+ 2, "11111111333311111111111111111111",
+ 3, "22222222111111111111111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-8.1.8",
+ [[
+ INSERT INTO tsc(sc) SELECT u FROM t2;
+ SELECT * FROM tsc;
+ ]], {
+ 1, uuid1, 2, uuid3, 3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-8.1.9",
+ [[
+ INSERT INTO tuu(uu) SELECT u FROM t2;
+ SELECT * FROM tuu;
+ ]], {
+ 1, uuid1, 2, uuid3, 3, uuid2
+ })
+
+-- Check that implicit cast from another types to UUID works as intended.
+test:do_catchsql_test(
+ "uuid-8.2.1",
+ [[
+ INSERT INTO tsu VALUES ('1_unsigned', 1);
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ 1, "Type mismatch: can not convert 1 to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-8.2.2",
+ [[
+ INSERT INTO tsu VALUES ('2_string_right', '11111111-1111-1111-1111-111111111111');
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ '2_string_right', uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.3",
+ [[
+ INSERT INTO tsu VALUES ('3_string_wrong', '1');
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ 1, "Type mismatch: can not convert 1 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.4",
+ [[
+ INSERT INTO tsu VALUES ('4_double', 1.5);
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ 1, "Type mismatch: can not convert 1.5 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.5",
+ [[
+ INSERT INTO tsu VALUES ('5_integer', -1);
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ 1, "Type mismatch: can not convert -1 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.6",
+ [[
+ INSERT INTO tsu VALUES ('6_boolean', true);
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ 1, "Type mismatch: can not convert TRUE to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-8.2.7",
+ [[
+ INSERT INTO tsu SELECT '7_varbinary', x'11111111111111111111111111111111' FROM t2 LIMIT 1;
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ '7_varbinary', uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.8",
+ [[
+ INSERT INTO tsu VALUES ('8_varbinary', randomblob(10));
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ 1, "Type mismatch: can not convert varbinary to uuid"
+ })
+
+test:execsql([[
+ CREATE TABLE t9 (i INT PRIMARY KEY AUTOINCREMENT, u UUID);
+ CREATE TABLE t9t (u UUID PRIMARY KEY);
+ CREATE TRIGGER t AFTER INSERT ON t9 FOR EACH ROW BEGIN INSERT INTO t9t SELECT new.u; END;
+]])
+
+-- Check that trigger can work with UUID.
+test:do_execsql_test(
+ "uuid-9",
+ [[
+ INSERT INTO t9(u) SELECT * FROM t2;
+ SELECT * FROM t9t;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:execsql([[
+ CREATE TABLE t10 (i INT PRIMARY KEY AUTOINCREMENT, u UUID DEFAULT '11111111-1111-1111-1111-111111111111');
+]])
+
+-- Check that INSERT into UUID field works.
+test:do_execsql_test(
+ "uuid-10.1.1",
+ [[
+ INSERT INTO t10 VALUES (1, '22222222-1111-1111-1111-111111111111');
+ SELECT * FROM t10 WHERE i = 1;
+ ]], {
+ 1, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-10.1.2",
+ [[
+ INSERT INTO t10 VALUES (2, x'22222222111111111111111111111111');
+ SELECT * FROM t10 WHERE i = 2;
+ ]], {
+ 2, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-10.1.3",
+ [[
+ INSERT INTO t10(i) VALUES (3);
+ SELECT * FROM t10 WHERE i = 3;
+ ]], {
+ 3, uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-10.1.4",
+ [[
+ INSERT INTO t10 VALUES (4, NULL);
+ SELECT * FROM t10 WHERE i = 4;
+ ]], {
+ 4, ''
+ })
+
+-- Check that UPDATE of UUID field works.
+test:do_execsql_test(
+ "uuid-10.2.1",
+ [[
+ UPDATE t10 SET u = '11111111-3333-1111-1111-111111111111' WHERE i = 1;
+ SELECT * FROM t10 WHERE i = 1;
+ ]], {
+ 1, uuid3
+ })
+
+test:do_execsql_test(
+ "uuid-10.2.2",
+ [[
+ UPDATE t10 SET u = x'11111111333311111111111111111111' WHERE i = 2;
+ SELECT * FROM t10 WHERE i = 2;
+ ]], {
+ 2, uuid3
+ })
+
+-- Check that JOIN by UUID field works.
+test:do_execsql_test(
+ "uuid-11.1",
+ [[
+ SELECT * FROM t1 JOIN t2 on t1.u = t2.u;
+ ]], {
+ 1, uuid1, uuid1, 2, uuid2, uuid2, 3, uuid3, uuid3,
+ 4, uuid1, uuid1, 5, uuid1, uuid1, 6, uuid2, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-11.2",
+ [[
+ SELECT * FROM t1 LEFT JOIN t2 on t1.u = t2.u;
+ ]], {
+ 1, uuid1, uuid1, 2, uuid2, uuid2, 3, uuid3, uuid3,
+ 4, uuid1, uuid1, 5, uuid1, uuid1, 6, uuid2, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-11.3",
+ [[
+ SELECT * FROM t1 INNER JOIN t2 on t1.u = t2.u;
+ ]], {
+ 1, uuid1, uuid1, 2, uuid2, uuid2, 3, uuid3, uuid3,
+ 4, uuid1, uuid1, 5, uuid1, uuid1, 6, uuid2, uuid2
+ })
+
+-- Check that arithmetic operations work with UUIDs as intended.
+test:do_catchsql_test(
+ "uuid-12.1.1",
+ [[
+ SELECT -u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.2",
+ [[
+ SELECT u + 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.3",
+ [[
+ SELECT u - 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.4",
+ [[
+ SELECT u * 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.5",
+ [[
+ SELECT u / 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.6",
+ [[
+ SELECT u % 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+-- Check that bitwise operations work with UUIDs as intended.
+test:do_catchsql_test(
+ "uuid-12.2.1",
+ [[
+ SELECT ~u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.2.2",
+ [[
+ SELECT u >> 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.2.3",
+ [[
+ SELECT u << 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.2.4",
+ [[
+ SELECT u | 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.2.5",
+ [[
+ SELECT u & 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+-- Check that logical operations work with UUIDs as intended.
+test:do_catchsql_test(
+ "uuid-12.3.1",
+ [[
+ SELECT NOT u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.3.2",
+ [[
+ SELECT u AND true FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.3.3",
+ [[
+ SELECT u OR true FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.3.4",
+ [[
+ SELECT true AND u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.3.5",
+ [[
+ SELECT true OR u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+-- Check that comparison with UUID works as intended.
+test:do_catchsql_test(
+ "uuid-13.1.1",
+ [[
+ SELECT u > 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert unsigned to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-13.1.2",
+ [[
+ SELECT u > CAST('11111111-1111-1111-1111-111111111111' AS UUID) FROM t2;
+ ]], {
+ false, true, true
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.3",
+ [[
+ SELECT u > '1' FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert text to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.4",
+ [[
+ SELECT u > 1.5 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert real to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.5",
+ [[
+ SELECT u > -1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert integer to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.6",
+ [[
+ SELECT u > true FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert uuid to boolean"
+ })
+
+test:do_execsql_test(
+ "uuid-13.1.7",
+ [[
+ SELECT u > CAST(x'11111111111111111111111111111111' AS UUID) FROM t2;
+ ]], {
+ false, true, true
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.8",
+ [[
+ SELECT u > x'31' FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert varbinary to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.1",
+ [[
+ SELECT u = 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert unsigned to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-13.2.2",
+ [[
+ SELECT u = CAST('11111111-1111-1111-1111-111111111111' AS UUID) FROM t2;
+ ]], {
+ true, false, false
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.3",
+ [[
+ SELECT u = '1' FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert text to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.4",
+ [[
+ SELECT u = 1.5 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert real to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.5",
+ [[
+ SELECT u = -1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert integer to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.6",
+ [[
+ SELECT u = true FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert uuid to boolean"
+ })
+
+test:do_execsql_test(
+ "uuid-13.2.7",
+ [[
+ SELECT u = CAST(x'11111111111111111111111111111111' AS UUID) FROM t2;
+ ]], {
+ true, false, false
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.8",
+ [[
+ SELECT u = x'31' FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert varbinary to uuid"
+ })
+
+test:execsql([[
+ CREATE TABLE t14 (s SCALAR PRIMARY KEY);
+]])
+
+-- Check that SCALAR field can contain UUID and use it in index.
+test:do_execsql_test(
+ "uuid-14",
+ [[
+ INSERT INTO t14 VALUES (1), (true), (1.5), (-1);
+ INSERT INTO t14 VALUES (x'11111111111111111111111111111111');
+ INSERT INTO t14 VALUES (CAST(x'11111111111111111111111111111111' AS UUID));
+ INSERT INTO t14 VALUES ('11111111-1111-1111-1111-111111111111');
+ SELECT typeof(s) FROM t14;
+ ]], {
+ "boolean", "integer", "integer", "double", "string", "varbinary", "uuid"
+ })
+
+test:finish_test()
--
2.25.1
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
2021-05-20 9:44 [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type Mergen Imeev via Tarantool-patches
@ 2021-05-21 7:16 ` Mergen Imeev via Tarantool-patches
2021-05-22 16:04 ` Vladislav Shpilevoy via Tarantool-patches
1 sibling, 0 replies; 8+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-05-21 7:16 UTC (permalink / raw)
To: v.shpilevoy, tarantool-patches
Hi! I found pissible memleak in my patch. Diff:
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 9288ef341..bf16f92ba 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -614,11 +614,10 @@ static inline int
str_to_uuid(struct Mem *mem)
{
assert(mem->type == MEM_TYPE_STR);
- if (tt_uuid_from_string(tt_cstr(mem->z, mem->n), &mem->u.uuid) != 0)
+ struct tt_uuid uuid;
+ if (tt_uuid_from_string(tt_cstr(mem->z, mem->n), &uuid) != 0)
return -1;
- mem->type = MEM_TYPE_UUID;
- mem->flags = 0;
- mem->field_type = FIELD_TYPE_UUID;
+ mem_set_uuid(mem, &uuid);
return 0;
}
@@ -683,10 +682,8 @@ bin_to_uuid(struct Mem *mem)
if (mem->n != UUID_LEN ||
tt_uuid_validate((struct tt_uuid *)mem->z) != 0)
return -1;
- mem->u.uuid = *(struct tt_uuid *)mem->z;
- mem->type = MEM_TYPE_UUID;
- mem->flags = 0;
- mem->field_type = FIELD_TYPE_UUID;
+ struct tt_uuid uuid = *(struct tt_uuid *)mem->z;
+ mem_set_uuid(mem, &uuid);
return 0;
}
On Thu, May 20, 2021 at 12:44:58PM +0300, Mergen Imeev via Tarantool-patches wrote:
> This patch introduces UUID to SQL. UUID is now available as a new field
> type.
>
> Closes #5886
>
> @TarantoolBot document
> Title: Field type UUID is now available in SQL
>
> The UUID field type is now available in SQL. This means that we can
> create spaces and indexes with UUID, use it in SELECT, UPDATE and
> DELETE. UUID can be accepted and returned by built-in functions and
> user-defined functions.
>
> During comparison, UUID cannot be implicitly converted to any other
> types, but in any other case, UUID can be implicitly converted to STRING
> or VARBINARY. UUID can be explicitly converted to STRING or VARBINARY
> using CAST(). STRING and VARBINARY can be implicitly converted to UUID,
> except for comparison. STRING and VARBINARY can be explicitly converted
> to UUID using CAST().
> ---
>
> https://github.com/tarantool/tarantool/issues/5886
> https://github.com/tarantool/tarantool/tree/imeevma/gh-5886-introduce-uuid-type-in-sql
>
> .../unreleased/introduce-uuid-to-sql.md | 3 +
> extra/mkkeywordhash.c | 1 +
> src/box/sql/func.c | 30 +-
> src/box/sql/mem.c | 223 ++-
> src/box/sql/mem.h | 38 +-
> src/box/sql/parse.y | 1 +
> src/box/sql/vdbe.c | 15 +-
> test/sql-tap/CMakeLists.txt | 1 +
> .../gh-5913-segfault-on-select-uuid.test.lua | 42 +-
> .../sql-tap/gh-6024-funcs-return-bin.test.lua | 8 +-
> test/sql-tap/sql_uuid.c | 57 +
> test/sql-tap/uuid.test.lua | 1259 +++++++++++++++++
> 12 files changed, 1607 insertions(+), 71 deletions(-)
> create mode 100644 changelogs/unreleased/introduce-uuid-to-sql.md
> create mode 100644 test/sql-tap/sql_uuid.c
> create mode 100755 test/sql-tap/uuid.test.lua
>
> diff --git a/changelogs/unreleased/introduce-uuid-to-sql.md b/changelogs/unreleased/introduce-uuid-to-sql.md
> new file mode 100644
> index 000000000..120fb145a
> --- /dev/null
> +++ b/changelogs/unreleased/introduce-uuid-to-sql.md
> @@ -0,0 +1,3 @@
> +## feature/core
> +
> + * Field type UUID is now available in SQL (gh-5886).
> diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
> index 7480c0211..0d998506c 100644
> --- a/extra/mkkeywordhash.c
> +++ b/extra/mkkeywordhash.c
> @@ -172,6 +172,7 @@ static Keyword aKeywordTable[] = {
> { "UNSIGNED", "TK_UNSIGNED", true },
> { "UPDATE", "TK_UPDATE", true },
> { "USING", "TK_USING", true },
> + { "UUID" , "TK_UUID" , true },
> { "VALUES", "TK_VALUES", true },
> { "VARBINARY", "TK_VARBINARY", true },
> { "VIEW", "TK_VIEW", true },
> diff --git a/src/box/sql/func.c b/src/box/sql/func.c
> index 90e8e152f..9c4480a92 100644
> --- a/src/box/sql/func.c
> +++ b/src/box/sql/func.c
> @@ -58,7 +58,8 @@ static const void *
> mem_as_bin(struct Mem *mem)
> {
> const char *s;
> - if (!mem_is_bytes(mem) && mem_to_str(mem) != 0)
> + if (mem_cast_implicit(mem, FIELD_TYPE_VARBINARY) != 0 &&
> + mem_to_str(mem) != 0)
> return NULL;
> if (mem_get_bin(mem, &s) != 0)
> return NULL;
> @@ -142,26 +143,29 @@ typeofFunc(sql_context * context, int NotUsed, sql_value ** argv)
> -1, SQL_STATIC);
> return;
> }
> - switch (sql_value_type(argv[0])) {
> - case MP_INT:
> - case MP_UINT:
> + switch (argv[0]->type) {
> + case MEM_TYPE_INT:
> + case MEM_TYPE_UINT:
> z = "integer";
> break;
> - case MP_STR:
> + case MEM_TYPE_STR:
> z = "string";
> break;
> - case MP_DOUBLE:
> + case MEM_TYPE_DOUBLE:
> z = "double";
> break;
> - case MP_BIN:
> - case MP_ARRAY:
> - case MP_MAP:
> + case MEM_TYPE_BIN:
> + case MEM_TYPE_ARRAY:
> + case MEM_TYPE_MAP:
> z = "varbinary";
> break;
> - case MP_BOOL:
> - case MP_NIL:
> + case MEM_TYPE_BOOL:
> + case MEM_TYPE_NULL:
> z = "boolean";
> break;
> + case MEM_TYPE_UUID:
> + z = "uuid";
> + break;
> default:
> unreachable();
> break;
> @@ -191,6 +195,7 @@ lengthFunc(sql_context * context, int argc, sql_value ** argv)
> sql_result_uint(context, mem_len_unsafe(argv[0]));
> break;
> }
> + case MP_EXT:
> case MP_STR:{
> const unsigned char *z = mem_as_ustr(argv[0]);
> if (z == 0)
> @@ -235,6 +240,7 @@ absFunc(sql_context * context, int argc, sql_value ** argv)
> }
> case MP_BOOL:
> case MP_BIN:
> + case MP_EXT:
> case MP_ARRAY:
> case MP_MAP: {
> diag_set(ClientError, ER_INCONSISTENT_TYPES, "number",
> @@ -1461,8 +1467,8 @@ trim_func_one_arg(struct sql_context *context, sql_value *arg)
> default_trim = (const unsigned char *) "\0";
> else
> default_trim = (const unsigned char *) " ";
> - int input_str_sz = mem_len_unsafe(arg);
> const unsigned char *input_str = mem_as_ustr(arg);
> + int input_str_sz = mem_len_unsafe(arg);
> uint8_t trim_char_len[1] = { 1 };
> trim_procedure(context, TRIM_BOTH, default_trim, trim_char_len, 1,
> input_str, input_str_sz);
> diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
> index 47845599e..9288ef341 100644
> --- a/src/box/sql/mem.c
> +++ b/src/box/sql/mem.c
> @@ -58,10 +58,22 @@ enum {
> BUF_SIZE = 32,
> };
>
> +bool
> +mem_is_field_compatible(const struct Mem *mem, enum field_type type,
> + bool is_nullable)
> +{
> + if (mem->type == MEM_TYPE_UUID)
> + return (field_ext_type[type] & (1U << MP_UUID)) != 0;
> + enum mp_type mp_type = mem_mp_type(mem);
> + assert(mp_type != MP_EXT);
> + return field_mp_plain_type_is_compatible(type, mp_type, is_nullable);
> +}
> +
> const char *
> mem_str(const struct Mem *mem)
> {
> - char buf[BUF_SIZE];
> + assert((int)UUID_STR_LEN > (int)BUF_SIZE);
> + char buf[UUID_STR_LEN + 1];
> switch (mem->type) {
> case MEM_TYPE_NULL:
> return "NULL";
> @@ -81,6 +93,9 @@ mem_str(const struct Mem *mem)
> case MEM_TYPE_MAP:
> case MEM_TYPE_ARRAY:
> return mp_str(mem->z);
> + case MEM_TYPE_UUID:
> + tt_uuid_to_string(&mem->u.uuid, &buf[0]);
> + return tt_sprintf("%s", buf);
> case MEM_TYPE_BOOL:
> return mem->u.b ? "TRUE" : "FALSE";
> default:
> @@ -190,6 +205,16 @@ mem_set_double(struct Mem *mem, double value)
> mem->type = MEM_TYPE_DOUBLE;
> }
>
> +void
> +mem_set_uuid(struct Mem *mem, struct tt_uuid *uuid)
> +{
> + mem_clear(mem);
> + mem->field_type = FIELD_TYPE_UUID;
> + mem->u.uuid = *uuid;
> + mem->type = MEM_TYPE_UUID;
> + mem->flags = 0;
> +}
> +
> static inline void
> set_str_const(struct Mem *mem, char *value, uint32_t len, int alloc_type)
> {
> @@ -585,6 +610,18 @@ str_to_bin(struct Mem *mem)
> return 0;
> }
>
> +static inline int
> +str_to_uuid(struct Mem *mem)
> +{
> + assert(mem->type == MEM_TYPE_STR);
> + if (tt_uuid_from_string(tt_cstr(mem->z, mem->n), &mem->u.uuid) != 0)
> + return -1;
> + mem->type = MEM_TYPE_UUID;
> + mem->flags = 0;
> + mem->field_type = FIELD_TYPE_UUID;
> + return 0;
> +}
> +
> static inline int
> str_to_bool(struct Mem *mem)
> {
> @@ -639,6 +676,20 @@ bin_to_str0(struct Mem *mem)
> return 0;
> }
>
> +static inline int
> +bin_to_uuid(struct Mem *mem)
> +{
> + assert(mem->type == MEM_TYPE_BIN);
> + if (mem->n != UUID_LEN ||
> + tt_uuid_validate((struct tt_uuid *)mem->z) != 0)
> + return -1;
> + mem->u.uuid = *(struct tt_uuid *)mem->z;
> + mem->type = MEM_TYPE_UUID;
> + mem->flags = 0;
> + mem->field_type = FIELD_TYPE_UUID;
> + return 0;
> +}
> +
> static inline int
> bytes_to_int(struct Mem *mem)
> {
> @@ -810,6 +861,22 @@ map_to_str0(struct Mem *mem)
> return mem_copy_str0(mem, str);
> }
>
> +static inline int
> +uuid_to_str0(struct Mem *mem)
> +{
> + assert(mem->type == MEM_TYPE_UUID);
> + char buf[UUID_STR_LEN + 1];
> + tt_uuid_to_string(&mem->u.uuid, &buf[0]);
> + return mem_copy_str0(mem, &buf[0]);
> +}
> +
> +static inline int
> +uuid_to_bin(struct Mem *mem)
> +{
> + assert(mem->type == MEM_TYPE_UUID);
> + return mem_copy_bin(mem, (char *)&mem->u.uuid, UUID_LEN);
> +}
> +
> int
> mem_to_int(struct Mem *mem)
> {
> @@ -889,6 +956,8 @@ mem_to_str0(struct Mem *mem)
> return map_to_str0(mem);
> case MEM_TYPE_ARRAY:
> return array_to_str0(mem);
> + case MEM_TYPE_UUID:
> + return uuid_to_str0(mem);
> default:
> return -1;
> }
> @@ -914,6 +983,8 @@ mem_to_str(struct Mem *mem)
> return map_to_str0(mem);
> case MEM_TYPE_ARRAY:
> return array_to_str0(mem);
> + case MEM_TYPE_UUID:
> + return uuid_to_str0(mem);
> default:
> return -1;
> }
> @@ -966,9 +1037,19 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
> return str_to_bin(mem);
> if (mem_is_bytes(mem))
> return 0;
> + if (mem->type == MEM_TYPE_UUID)
> + return uuid_to_bin(mem);
> return -1;
> case FIELD_TYPE_NUMBER:
> return mem_to_number(mem);
> + case FIELD_TYPE_UUID:
> + if (mem->type == MEM_TYPE_UUID)
> + return 0;
> + if (mem->type == MEM_TYPE_STR)
> + return str_to_uuid(mem);
> + if (mem->type == MEM_TYPE_BIN)
> + return bin_to_uuid(mem);
> + return -1;
> case FIELD_TYPE_SCALAR:
> if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
> return -1;
> @@ -996,6 +1077,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
> case FIELD_TYPE_STRING:
> if (mem->type == MEM_TYPE_STR)
> return 0;
> + if (mem->type == MEM_TYPE_UUID)
> + return uuid_to_str0(mem);
> return -1;
> case FIELD_TYPE_DOUBLE:
> if (mem->type == MEM_TYPE_DOUBLE)
> @@ -1017,6 +1100,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
> if ((mem->type & (MEM_TYPE_BIN | MEM_TYPE_MAP |
> MEM_TYPE_ARRAY)) != 0)
> return 0;
> + if (mem->type == MEM_TYPE_UUID)
> + return uuid_to_bin(mem);
> return -1;
> case FIELD_TYPE_NUMBER:
> if (mem_is_num(mem))
> @@ -1034,6 +1119,14 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
> if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
> return -1;
> return 0;
> + case FIELD_TYPE_UUID:
> + if (mem->type == MEM_TYPE_UUID)
> + return 0;
> + if (mem->type == MEM_TYPE_STR)
> + return str_to_uuid(mem);
> + if (mem->type == MEM_TYPE_BIN)
> + return bin_to_uuid(mem);
> + return -1;
> case FIELD_TYPE_ANY:
> return 0;
> default:
> @@ -1063,6 +1156,8 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
> return int_to_str0(mem);
> if (mem->type == MEM_TYPE_DOUBLE)
> return double_to_str0(mem);
> + if (mem->type == MEM_TYPE_UUID)
> + return uuid_to_str0(mem);
> return -1;
> case FIELD_TYPE_DOUBLE:
> if (mem->type == MEM_TYPE_DOUBLE)
> @@ -1087,6 +1182,8 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
> case FIELD_TYPE_VARBINARY:
> if (mem->type == MEM_TYPE_BIN)
> return 0;
> + if (mem->type == MEM_TYPE_UUID)
> + return uuid_to_bin(mem);
> return -1;
> case FIELD_TYPE_NUMBER:
> if (mem_is_num(mem))
> @@ -1106,6 +1203,14 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
> if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
> return -1;
> return 0;
> + case FIELD_TYPE_UUID:
> + if (mem->type == MEM_TYPE_UUID)
> + return 0;
> + if (mem->type == MEM_TYPE_STR)
> + return str_to_uuid(mem);
> + if (mem->type == MEM_TYPE_BIN)
> + return bin_to_uuid(mem);
> + return -1;
> default:
> break;
> }
> @@ -1238,6 +1343,24 @@ mem_len(const struct Mem *mem, uint32_t *len)
> return 0;
> }
>
> +int
> +mem_get_uuid(const struct Mem *mem, struct tt_uuid *uuid)
> +{
> + if ((mem->type & (MEM_TYPE_UUID | MEM_TYPE_STR | MEM_TYPE_BIN)) == 0)
> + return -1;
> + if (mem->type == MEM_TYPE_STR)
> + return tt_uuid_from_string(tt_cstr(mem->z, mem->n), uuid);
> + if (mem->type == MEM_TYPE_UUID) {
> + *uuid = mem->u.uuid;
> + return 0;
> + }
> + if (mem->n != UUID_LEN ||
> + tt_uuid_validate((struct tt_uuid *)mem->z) != 0)
> + return -1;
> + *uuid = *(struct tt_uuid *)mem->z;
> + return 0;
> +}
> +
> int
> mem_get_agg(const struct Mem *mem, void **accum)
> {
> @@ -1898,6 +2021,15 @@ mem_cmp_str(const struct Mem *left, const struct Mem *right, int *result,
> return 0;
> }
>
> +int
> +mem_cmp_uuid(const struct Mem *a, const struct Mem *b, int *result)
> +{
> + if ((a->type & b->type & MEM_TYPE_UUID) == 0)
> + return -1;
> + *result = memcmp(&a->u.uuid, &b->u.uuid, UUID_LEN);
> + return 0;
> +}
> +
> /*
> * Both *pMem1 and *pMem2 contain string values. Compare the two values
> * using the collation sequence pColl. As usual, return a negative , zero
> @@ -1950,13 +2082,15 @@ mem_type_to_str(const struct Mem *p)
> return "varbinary";
> case MEM_TYPE_BOOL:
> return "boolean";
> + case MEM_TYPE_UUID:
> + return "uuid";
> default:
> unreachable();
> }
> }
>
> enum mp_type
> -mem_mp_type(struct Mem *mem)
> +mem_mp_type(const struct Mem *mem)
> {
> assert(mem->type < MEM_TYPE_INVALID);
> switch (mem->type) {
> @@ -1978,6 +2112,8 @@ mem_mp_type(struct Mem *mem)
> return MP_BOOL;
> case MEM_TYPE_DOUBLE:
> return MP_DOUBLE;
> + case MEM_TYPE_UUID:
> + return MP_EXT;
> default:
> unreachable();
> }
> @@ -2359,6 +2495,14 @@ sqlMemCompare(const Mem * pMem1, const Mem * pMem2, const struct coll * pColl)
> return -1;
> }
>
> + if (((type1 | type2) & MEM_TYPE_UUID) != 0) {
> + if (mem_cmp_uuid(pMem1, pMem2, &res) == 0)
> + return res;
> + if (type1 != MEM_TYPE_UUID)
> + return +1;
> + return -1;
> + }
> +
> /* At least one of the two values is a number
> */
> if (((type1 | type2) &
> @@ -2566,11 +2710,24 @@ sqlVdbeCompareMsgpack(const char **key1,
> case MP_ARRAY:
> case MP_MAP:
> case MP_EXT:{
> - mem1.z = (char *)aKey1;
> - mp_next(&aKey1);
> - mem1.n = aKey1 - (char *)mem1.z;
> - goto do_blob;
> + int8_t type;
> + const char *buf = aKey1;
> + uint32_t len = mp_decode_extl(&buf, &type);
> + buf = aKey1;
> + mp_next(&aKey1);
> + (void)len;
> + if (type == MP_UUID) {
> + assert(len == UUID_LEN);
> + mem1.type = MEM_TYPE_UUID;
> + if (mp_decode_uuid(&buf, &mem1.u.uuid) == NULL ||
> + mem_cmp_uuid(&mem1, pKey2, &rc) != 0)
> + rc = 1;
> + break;
> }
> + mem1.z = (char *)buf;
> + mem1.n = aKey1 - buf;
> + goto do_blob;
> + }
> }
> *key1 = aKey1;
> return rc;
> @@ -2624,9 +2781,23 @@ mem_from_mp_ephemeral(struct Mem *mem, const char *buf, uint32_t *len)
> break;
> }
> case MP_EXT: {
> - mem->z = (char *)buf;
> - mp_next(&buf);
> - mem->n = buf - mem->z;
> + int8_t type;
> + const char *svp = buf;
> + uint32_t len = mp_decode_extl(&buf, &type);
> + (void)len;
> + if (type == MP_UUID) {
> + assert(len == UUID_LEN);
> + buf = svp;
> + if (mp_decode_uuid(&buf, &mem->u.uuid) == NULL)
> + return -1;
> + mem->type = MEM_TYPE_UUID;
> + mem->flags = 0;
> + mem->field_type = FIELD_TYPE_UUID;
> + break;
> + }
> + mem->z = (char *)svp;
> + mp_next(&svp);
> + mem->n = svp - mem->z;
> mem->type = MEM_TYPE_BIN;
> mem->flags = MEM_Ephem;
> mem->field_type = FIELD_TYPE_VARBINARY;
> @@ -2763,6 +2934,9 @@ mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
> case MEM_TYPE_BOOL:
> mpstream_encode_bool(stream, var->u.b);
> return;
> + case MEM_TYPE_UUID:
> + mpstream_encode_uuid(stream, &var->u.uuid);
> + return;
> default:
> unreachable();
> }
> @@ -2849,6 +3023,11 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat)
> case MEM_TYPE_BOOL:
> lua_pushboolean(L, mem->u.b);
> break;
> + case MEM_TYPE_UUID: {
> + struct tt_uuid *uuid = luaL_pushuuid(L);
> + *uuid = mem->u.uuid;
> + break;
> + }
> default:
> unreachable();
> }
> @@ -2972,16 +3151,13 @@ port_lua_get_vdbemem(struct port *base, uint32_t *size)
> char buf[BUF_SIZE];
> assert(field.ext_type == MP_UUID ||
> field.ext_type == MP_DECIMAL);
> - uint32_t size;
> if (field.ext_type == MP_UUID) {
> - size = mp_sizeof_uuid();
> - assert(size < BUF_SIZE);
> - mp_encode_uuid(&buf[0], field.uuidval);
> - } else {
> - size = mp_sizeof_decimal(field.decval);
> - assert(size < BUF_SIZE);
> - mp_encode_decimal(&buf[0], field.decval);
> + mem_set_uuid(&val[i], field.uuidval);
> + break;
> }
> + uint32_t size = mp_sizeof_decimal(field.decval);
> + assert(size < BUF_SIZE);
> + mp_encode_decimal(&buf[0], field.decval);
> if (mem_copy_bin(&val[i], buf, size) != 0)
> goto error;
> break;
> @@ -3073,7 +3249,18 @@ port_c_get_vdbemem(struct port *base, uint32_t *size)
> break;
> case MP_EXT:
> str = data;
> - mp_next(&data);
> + int8_t type;
> + len = mp_decode_extl(&data, &type);
> + if (type == MP_UUID) {
> + assert(len == UUID_LEN);
> + struct tt_uuid *uuid = &val[i].u.uuid;
> + data = str;
> + if (mp_decode_uuid(&data, uuid) == NULL)
> + goto error;
> + val[i].type = MEM_TYPE_UUID;
> + break;
> + }
> + data += len;
> if (mem_copy_bin(&val[i], str, data - str) != 0)
> goto error;
> break;
> diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
> index 15d97da0e..564d48067 100644
> --- a/src/box/sql/mem.h
> +++ b/src/box/sql/mem.h
> @@ -30,6 +30,7 @@
> * SUCH DAMAGE.
> */
> #include "box/field_def.h"
> +#include "uuid/tt_uuid.h"
>
> struct sql;
> struct Vdbe;
> @@ -47,10 +48,11 @@ enum mem_type {
> MEM_TYPE_MAP = 1 << 6,
> MEM_TYPE_BOOL = 1 << 7,
> MEM_TYPE_DOUBLE = 1 << 8,
> - MEM_TYPE_INVALID = 1 << 9,
> - MEM_TYPE_FRAME = 1 << 10,
> - MEM_TYPE_PTR = 1 << 11,
> - MEM_TYPE_AGG = 1 << 12,
> + MEM_TYPE_UUID = 1 << 9,
> + MEM_TYPE_INVALID = 1 << 10,
> + MEM_TYPE_FRAME = 1 << 11,
> + MEM_TYPE_PTR = 1 << 12,
> + MEM_TYPE_AGG = 1 << 13,
> };
>
> /*
> @@ -72,6 +74,7 @@ struct Mem {
> */
> struct func *func;
> struct VdbeFrame *pFrame; /* Used when flags==MEM_Frame */
> + struct tt_uuid uuid;
> } u;
> /** Type of the value this MEM contains. */
> enum mem_type type;
> @@ -255,6 +258,11 @@ mem_is_any_null(const struct Mem *mem1, const struct Mem *mem2)
> return ((mem1->type| mem2->type) & MEM_TYPE_NULL) != 0;
> }
>
> +/** Check if MEM is compatible with field type. */
> +bool
> +mem_is_field_compatible(const struct Mem *mem, enum field_type type,
> + bool is_nullable);
> +
> /**
> * Return a string that represent content of MEM. String is either allocated
> * using static_alloc() of just a static variable.
> @@ -290,6 +298,10 @@ mem_set_bool(struct Mem *mem, bool value);
> void
> mem_set_double(struct Mem *mem, double value);
>
> +/** Clear MEM and set it to UUID. */
> +void
> +mem_set_uuid(struct Mem *mem, struct tt_uuid *uuid);
> +
> /** Clear MEM and set it to STRING. The string belongs to another object. */
> void
> mem_set_str_ephemeral(struct Mem *mem, char *value, uint32_t len);
> @@ -677,6 +689,14 @@ mem_cmp_str(const struct Mem *left, const struct Mem *right, int *result,
> int
> mem_cmp_num(const struct Mem *a, const struct Mem *b, int *result);
>
> +/**
> + * Compare two MEMs and return the result of comparison. MEMs should be of
> + * UUID type or their values are converted to UUID according to
> + * implicit cast rules. Original MEMs are not changed.
> + */
> +int
> +mem_cmp_uuid(const struct Mem *left, const struct Mem *right, int *result);
> +
> /**
> * Convert the given MEM to INTEGER. This function and the function below define
> * the rules that are used to convert values of all other types to INTEGER. In
> @@ -864,6 +884,14 @@ mem_get_bin(const struct Mem *mem, const char **s);
> int
> mem_len(const struct Mem *mem, uint32_t *len);
>
> +/**
> + * Return value for MEM of UUID type. For MEM of all other types convert value
> + * of the MEM to UUID if possible and return converted value. Original MEM is
> + * not changed.
> + */
> +int
> +mem_get_uuid(const struct Mem *mem, struct tt_uuid *uuid);
> +
> /**
> * Return length of value for MEM of STRING or VARBINARY type. This function is
> * not safe since there is no proper processing in case mem_len() return an
> @@ -898,7 +926,7 @@ mem_type_to_str(const struct Mem *p);
> * transparent memory cell.
> */
> enum mp_type
> -mem_mp_type(struct Mem *mem);
> +mem_mp_type(const struct Mem *mem);
>
> enum mp_type
> sql_value_type(struct Mem *);
> diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
> index abc363951..4c9cf475e 100644
> --- a/src/box/sql/parse.y
> +++ b/src/box/sql/parse.y
> @@ -1834,6 +1834,7 @@ typedef(A) ::= SCALAR . { A.type = FIELD_TYPE_SCALAR; }
> typedef(A) ::= BOOL . { A.type = FIELD_TYPE_BOOLEAN; }
> typedef(A) ::= BOOLEAN . { A.type = FIELD_TYPE_BOOLEAN; }
> typedef(A) ::= VARBINARY . { A.type = FIELD_TYPE_VARBINARY; }
> +typedef(A) ::= UUID . { A.type = FIELD_TYPE_UUID; }
>
> /**
> * Time-like types are temporary disabled, until they are
> diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
> index 12ec703a2..c78674882 100644
> --- a/src/box/sql/vdbe.c
> +++ b/src/box/sql/vdbe.c
> @@ -1322,10 +1322,10 @@ case OP_FunctionByName: {
> region_truncate(region, region_svp);
> if (mem == NULL)
> goto abort_due_to_error;
> - enum mp_type type = sql_value_type((sql_value *)pOut);
> - if (!field_mp_plain_type_is_compatible(returns, type, true)) {
> + if (!mem_is_field_compatible(pOut, returns, true)) {
> diag_set(ClientError, ER_FUNC_INVALID_RETURN_TYPE, pOp->p4.z,
> - field_type_strs[returns], mp_type_strs[type]);
> + field_type_strs[returns],
> + mp_type_strs[mem_mp_type(pOut)]);
> goto abort_due_to_error;
> }
>
> @@ -1634,6 +1634,15 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
> "boolean");
> goto abort_due_to_error;
> }
> + } else if (((pIn3->type | pIn1->type) & MEM_TYPE_UUID) != 0) {
> + if (mem_cmp_uuid(pIn3, pIn1, &res) != 0) {
> + char *str = pIn3->type != MEM_TYPE_UUID ?
> + mem_type_to_str(pIn3) :
> + mem_type_to_str(pIn1);
> + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, str,
> + "uuid");
> + goto abort_due_to_error;
> + }
> } else if (mem_is_bin(pIn3) || mem_is_bin(pIn1)) {
> if (mem_cmp_bin(pIn3, pIn1, &res) != 0) {
> char *str = !mem_is_bin(pIn3) ?
> diff --git a/test/sql-tap/CMakeLists.txt b/test/sql-tap/CMakeLists.txt
> index bf0c3a11d..bd2b9f33f 100644
> --- a/test/sql-tap/CMakeLists.txt
> +++ b/test/sql-tap/CMakeLists.txt
> @@ -1,3 +1,4 @@
> include_directories(${MSGPUCK_INCLUDE_DIRS})
> build_module(gh-5938-wrong-string-length gh-5938-wrong-string-length.c)
> build_module(gh-6024-funcs-return-bin gh-6024-funcs-return-bin.c)
> +build_module(sql_uuid sql_uuid.c)
> diff --git a/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua b/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua
> index 60978c2b5..34b22002a 100755
> --- a/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua
> +++ b/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua
> @@ -1,6 +1,6 @@
> #!/usr/bin/env tarantool
> local test = require("sqltester")
> -test:plan(6)
> +test:plan(4)
>
> local uuid = require("uuid").fromstr("11111111-1111-1111-1111-111111111111")
> local decimal = require("decimal").new(111.111)
> @@ -13,16 +13,16 @@ box.space.T:insert({1, uuid, decimal})
>
> --
> -- Make sure that there is no segmentation fault on select from field that
> --- contains UUID or DECIMAL. Currently SQL does not support UUID and DECIMAL,
> --- so they treated as VARBINARY.
> +-- contains UUID or DECIMAL. Currently SQL does not support DECIMAL, so it is
> +-- treated as VARBINARY.
> --
> test:do_execsql_test(
> "gh-5913-1",
> [[
> SELECT i, u, d FROM t;
> - SELECT i from t;
> + SELECT i, u from t;
> ]], {
> - 1
> + 1, uuid
> })
>
> box.schema.create_space('T1')
> @@ -32,19 +32,11 @@ box.space.T1:format({{name = "I", type = "integer"},
> box.space.T1:create_index("primary")
>
> --
> --- Since SQL does not support UUID and DECIMAL and they treated as VARBINARY,
> --- they cannot be inserted from SQL.
> +-- Since SQL does not support DECIMAL and it is treated as VARBINARY, it cannot
> +-- be inserted from SQL.
> --
> test:do_catchsql_test(
> "gh-5913-2",
> - [[
> - INSERT INTO t1 SELECT i, u, NULL FROM t;
> - ]], {
> - 1, "Type mismatch: can not convert varbinary to uuid"
> - })
> -
> -test:do_catchsql_test(
> - "gh-5913-3",
> [[
> INSERT INTO t1 SELECT i, NULL, d FROM t;
> ]], {
> @@ -52,11 +44,11 @@ test:do_catchsql_test(
> })
>
> --
> --- Still, if UUID or DECIMAL fields does not selected directly, insert is
> --- working properly.
> +-- Still, if DECIMAL fields does not selected directly, insert is working
> +-- properly in case the space which receives these values is empty.
> --
> test:do_execsql_test(
> - "gh-5913-4",
> + "gh-5913-3",
> [[
> INSERT INTO t1 SELECT * FROM t;
> SELECT count() FROM t1;
> @@ -77,19 +69,11 @@ box.space.TD:create_index("primary")
> box.space.TD:insert({1, decimal})
>
> --
> --- Update of UUID or VARBINARY also does not lead to segfault, however throws an
> --- error since after changing value cannot be inserted into the field from SQL.
> +-- Update of VARBINARY also does not lead to segfault, however throws an error
> +-- since after changing value cannot be inserted into the field from SQL.
> --
> test:do_catchsql_test(
> - "gh-5913-5",
> - [[
> - UPDATE tu SET u = u;
> - ]], {
> - 1, "Type mismatch: can not convert varbinary to uuid"
> - })
> -
> -test:do_catchsql_test(
> - "gh-5913-6",
> + "gh-5913-4",
> [[
> UPDATE td SET d = d;
> ]], {
> diff --git a/test/sql-tap/gh-6024-funcs-return-bin.test.lua b/test/sql-tap/gh-6024-funcs-return-bin.test.lua
> index 9069379e1..c2954bba0 100755
> --- a/test/sql-tap/gh-6024-funcs-return-bin.test.lua
> +++ b/test/sql-tap/gh-6024-funcs-return-bin.test.lua
> @@ -23,7 +23,7 @@ test:do_execsql_test(
> box.schema.func.create("gh-6024-funcs-return-bin.ret_uuid", {
> language = "C",
> param_list = {},
> - returns = "varbinary",
> + returns = "uuid",
> exports = {"SQL"},
> })
>
> @@ -32,7 +32,7 @@ test:do_execsql_test(
> [[
> SELECT typeof("gh-6024-funcs-return-bin.ret_uuid"());
> ]], {
> - "varbinary"
> + "uuid"
> })
>
> box.schema.func.create("gh-6024-funcs-return-bin.ret_decimal", {
> @@ -53,7 +53,7 @@ test:do_execsql_test(
> box.schema.func.create("get_uuid", {
> language = "LUA",
> param_list = {},
> - returns = "varbinary",
> + returns = "uuid",
> body = "function(x) return require('uuid').fromstr('11111111-1111-1111-1111-111111111111') end",
> exports = {"SQL"},
> })
> @@ -63,7 +63,7 @@ test:do_execsql_test(
> [[
> SELECT typeof("get_uuid"()), "get_uuid"() == "gh-6024-funcs-return-bin.ret_uuid"();
> ]], {
> - "varbinary", true
> + "uuid", true
> })
>
> box.schema.func.create("get_decimal", {
> diff --git a/test/sql-tap/sql_uuid.c b/test/sql-tap/sql_uuid.c
> new file mode 100644
> index 000000000..592b9e48f
> --- /dev/null
> +++ b/test/sql-tap/sql_uuid.c
> @@ -0,0 +1,57 @@
> +#include "msgpuck.h"
> +#include "module.h"
> +#include "uuid/mp_uuid.h"
> +#include "mp_extension_types.h"
> +
> +enum {
> + BUF_SIZE = 512,
> +};
> +
> +int
> +is_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
> +{
> + uint32_t arg_count = mp_decode_array(&args);
> + if (arg_count != 1) {
> + return box_error_set(__FILE__, __LINE__, ER_PROC_C,
> + "invalid argument count");
> + }
> + bool is_uuid;
> + if (mp_typeof(*args) == MP_EXT) {
> + const char *str = args;
> + int8_t type;
> + mp_decode_extl(&str, &type);
> + is_uuid = type == MP_UUID;
> + } else {
> + is_uuid = false;
> + }
> +
> + char tuple_buf[BUF_SIZE];
> + assert(mp_sizeof_array(1) + mp_sizeof_bool(is_uuid) < BUF_SIZE);
> + char *d = tuple_buf;
> + d = mp_encode_array(d, 1);
> + d = mp_encode_bool(d, is_uuid);
> +
> + box_tuple_format_t *fmt = box_tuple_format_default();
> + box_tuple_t *tuple = box_tuple_new(fmt, tuple_buf, d);
> + if (tuple == NULL)
> + return -1;
> + return box_return_tuple(ctx, tuple);
> +}
> +
> +int
> +ret_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
> +{
> + struct tt_uuid uuid;
> + memset(&uuid, 0x11, sizeof(uuid));
> + char tuple_buf[BUF_SIZE];
> + assert(mp_sizeof_array(1) + mp_sizeof_uuid() < BUF_SIZE);
> + char *d = tuple_buf;
> + d = mp_encode_array(d, 1);
> + d = mp_encode_uuid(d, &uuid);
> +
> + box_tuple_format_t *fmt = box_tuple_format_default();
> + box_tuple_t *tuple = box_tuple_new(fmt, tuple_buf, d);
> + if (tuple == NULL)
> + return -1;
> + return box_return_tuple(ctx, tuple);
> +}
> diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
> new file mode 100755
> index 000000000..9210d05db
> --- /dev/null
> +++ b/test/sql-tap/uuid.test.lua
> @@ -0,0 +1,1259 @@
> +#!/usr/bin/env tarantool
> +local build_path = os.getenv("BUILDDIR")
> +package.cpath = build_path..'/test/sql-tap/?.so;'..build_path..'/test/sql-tap/?.dylib;'..package.cpath
> +
> +local test = require("sqltester")
> +test:plan(137)
> +
> +local uuid = require("uuid")
> +local uuid1 = uuid.fromstr("11111111-1111-1111-1111-111111111111")
> +local uuid2 = uuid.fromstr("22222222-1111-1111-1111-111111111111")
> +local uuid3 = uuid.fromstr("11111111-3333-1111-1111-111111111111")
> +
> +-- Check that it is possible to create spaces with UUID field.
> +test:do_execsql_test(
> + "uuid-1",
> + [[
> + CREATE TABLE t1 (i INT PRIMARY KEY, u UUID);
> + CREATE TABLE t2 (u UUID PRIMARY KEY);
> + ]], {
> + })
> +
> +box.space.T1:insert({1, uuid1})
> +box.space.T1:insert({2, uuid2})
> +box.space.T1:insert({3, uuid3})
> +box.space.T1:insert({4, uuid1})
> +box.space.T1:insert({5, uuid1})
> +box.space.T1:insert({6, uuid2})
> +box.space.T2:insert({uuid1})
> +box.space.T2:insert({uuid2})
> +box.space.T2:insert({uuid3})
> +
> +-- Check that SELECT can work with UUID.
> +test:do_execsql_test(
> + "uuid-2.1.1",
> + [[
> + SELECT * FROM t1;
> + ]], {
> + 1, uuid1, 2, uuid2, 3, uuid3, 4, uuid1, 5, uuid1, 6, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-2.1.2",
> + [[
> + SELECT * FROM t2;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +-- Check that ORDER BY can work with UUID.
> +test:do_execsql_test(
> + "uuid-2.2.1",
> + [[
> + SELECT * FROM t1 ORDER BY u;
> + ]], {
> + 1, uuid1, 4, uuid1, 5, uuid1, 3, uuid3, 2, uuid2, 6, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-2.2.2",
> + [[
> + SELECT * FROM t1 ORDER BY u DESC;
> + ]], {
> + 2, uuid2, 6, uuid2, 3, uuid3, 1, uuid1, 4, uuid1, 5, uuid1
> + })
> +
> +test:do_execsql_test(
> + "uuid-2.2.3",
> + [[
> + SELECT * FROM t2 ORDER BY u;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-2.2.4",
> + [[
> + SELECT * FROM t2 ORDER BY u DESC;
> + ]], {
> + uuid2, uuid3, uuid1
> + })
> +
> +-- Check that GROUP BY can work with UUID.
> +test:do_execsql_test(
> + "uuid-2.3.1",
> + [[
> + SELECT count(*), u FROM t1 GROUP BY u;
> + ]], {
> + 3, uuid1, 1, uuid3, 2, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-2.3.2",
> + [[
> + SELECT count(*), u FROM t2 GROUP BY u;
> + ]], {
> + 1, uuid1, 1, uuid3, 1, uuid2
> + })
> +
> +-- Check that subselects can work with UUID.
> +test:do_execsql_test(
> + "uuid-2.4",
> + [[
> + SELECT * FROM (SELECT * FROM (SELECT * FROM t2 LIMIT 2) LIMIT 2 OFFSET 1);
> + ]], {
> + uuid3
> + })
> +
> +-- Check that DISTINCT can work with UUID.
> +test:do_execsql_test(
> + "uuid-2.5",
> + [[
> + SELECT DISTINCT u FROM t1;
> + ]], {
> + uuid1, uuid2, uuid3
> + })
> +
> +-- Check that VIEW can work with UUID.
> +test:do_execsql_test(
> + "uuid-2.6",
> + [[
> + CREATE VIEW v AS SELECT u FROM t1;
> + SELECT * FROM v;
> + ]], {
> + uuid1, uuid2, uuid3, uuid1, uuid1, uuid2
> + })
> +
> +-- Check that LIMIT does not accept UUID as argument.
> +test:do_catchsql_test(
> + "uuid-3.1",
> + [[
> + SELECT 1 LIMIT (SELECT u FROM t1 LIMIT 1);
> + ]], {
> + 1, "Failed to execute SQL statement: Only positive integers are allowed in the LIMIT clause"
> + })
> +
> +-- Check that OFFSET does not accept UUID as argument.
> +test:do_catchsql_test(
> + "uuid-3.2",
> + [[
> + SELECT 1 LIMIT 1 OFFSET (SELECT u FROM t1 LIMIT 1);
> + ]], {
> + 1, "Failed to execute SQL statement: Only positive integers are allowed in the OFFSET clause"
> + })
> +
> +-- Check that ephemeral space can work with UUID.
> +test:do_execsql_test(
> + "uuid-4",
> + [[
> + EXPLAIN SELECT * from (VALUES(1)), t2;
> + ]], {
> + "/OpenTEphemeral/"
> + })
> +
> +test:execsql([[
> + CREATE TABLE t5f (u UUID PRIMARY KEY, f UUID REFERENCES t5f(u));
> + CREATE TABLE t5c (i INT PRIMARY KEY, f UUID, CONSTRAINT ck CHECK(CAST(f AS STRING) != '11111111-1111-1111-1111-111111111111'));
> + CREATE TABLE t5u (i INT PRIMARY KEY, f UUID UNIQUE);
> +]])
> +
> +-- Check that FOREIGN KEY constraint can work with UUID.
> +test:do_catchsql_test(
> + "uuid-5.1.1",
> + [[
> + INSERT INTO t5f SELECT (SELECT u from t2 LIMIT 1 OFFSET 1), (SELECT u from t2 LIMIT 1);
> + SELECT * from t5f;
> + ]], {
> + 1, "Failed to execute SQL statement: FOREIGN KEY constraint failed"
> + })
> +
> +test:do_execsql_test(
> + "uuid-5.1.2",
> + [[
> + INSERT INTO t5f SELECT u, u from t2 LIMIT 1;
> + SELECT * from t5f;
> + ]], {
> + uuid1, uuid1
> + })
> +
> +test:do_execsql_test(
> + "uuid-5.1.3",
> + [[
> + INSERT INTO t5f SELECT (SELECT u from t2 LIMIT 1 OFFSET 1), (SELECT u from t2 LIMIT 1);
> + SELECT * from t5f;
> + ]], {
> + uuid1, uuid1, uuid3, uuid1
> + })
> +
> +-- Check that CHECK constraint can work with UUID.
> +test:do_catchsql_test(
> + "uuid-5.2.1",
> + [[
> + INSERT INTO t5c SELECT 1, u FROM t2 LIMIT 1;
> + SELECT * from t5c;
> + ]], {
> + 1, "Check constraint failed 'CK': CAST(f AS STRING) != '11111111-1111-1111-1111-111111111111'"
> + })
> +
> +test:do_execsql_test(
> + "uuid-5.2.2",
> + [[
> + INSERT INTO t5c SELECT 2, u FROM t2 LIMIT 1 OFFSET 1;
> + SELECT * from t5c;
> + ]], {
> + 2, uuid3
> + })
> +
> +-- Check that UNIQUE constraint can work with UUID.
> +test:do_execsql_test(
> + "uuid-5.3.1",
> + [[
> + INSERT INTO t5u SELECT 1, u FROM t2 LIMIT 1;
> + SELECT * from t5u;
> + ]], {
> + 1, uuid1
> + })
> +
> +test:do_catchsql_test(
> + "uuid-5.3.2",
> + [[
> + INSERT INTO t5u SELECT 2, u FROM t2 LIMIT 1;
> + SELECT * from t5u;
> + ]], {
> + 1, 'Duplicate key exists in unique index "unique_unnamed_T5U_2" '..
> + 'in space "T5U" with old tuple - '..
> + '[1, 11111111-1111-1111-1111-111111111111] and new tuple - '..
> + '[2, 11111111-1111-1111-1111-111111111111]'
> + })
> +
> +-- Check that built-in functions work with UUIDs as intended.
> +test:do_catchsql_test(
> + "uuid-6.1.1",
> + [[
> + SELECT ABS(u) from t2;
> + ]], {
> + 1, "Inconsistent types: expected number got uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-6.1.2",
> + [[
> + SELECT AVG(u) from t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.3",
> + [[
> + SELECT CHAR(u) from t2;
> + ]], {
> + "\0", "\0", "\0"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.4",
> + [[
> + SELECT CHARACTER_LENGTH(u) from t2;
> + ]], {
> + 36, 36, 36
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.5",
> + [[
> + SELECT CHAR_LENGTH(u) from t2;
> + ]], {
> + 36, 36, 36
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.6",
> + [[
> + SELECT COALESCE(NULL, u, NULL, NULL) from t2;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.7",
> + [[
> + SELECT COUNT(u) from t2;
> + ]], {
> + 3
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.8",
> + [[
> + SELECT GREATEST((SELECT u FROM t2 LIMIT 1), (SELECT u FROM t2 LIMIT 1 OFFSET 1));
> + ]], {
> + uuid3
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.9",
> + [[
> + SELECT GROUP_CONCAT(u) from t2;
> + ]], {
> + "11111111-1111-1111-1111-111111111111,"..
> + "11111111-3333-1111-1111-111111111111,"..
> + "22222222-1111-1111-1111-111111111111"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.10",
> + [[
> + SELECT HEX(u) from t2;
> + ]], {
> + "11111111111111111111111111111111",
> + "11111111333311111111111111111111",
> + "22222222111111111111111111111111"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.11",
> + [[
> + SELECT IFNULL(u, NULL) from t2;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.12",
> + [[
> + SELECT LEAST((SELECT u FROM t2 LIMIT 1), (SELECT u FROM t2 LIMIT 1 OFFSET 1));
> + ]], {
> + uuid1
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.13",
> + [[
> + SELECT LENGTH(u) from t2;
> + ]], {
> + 36, 36, 36
> + })
> +
> +test:do_catchsql_test(
> + "uuid-6.1.14",
> + [[
> + SELECT u LIKE 'a' from t2;
> + ]], {
> + 1, "Inconsistent types: expected text got uuid"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.15",
> + [[
> + SELECT LIKELIHOOD(u, 0.5) from t2;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.16",
> + [[
> + SELECT LIKELY(u) from t2;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.17",
> + [[
> + SELECT LOWER(u) from t2;
> + ]], {
> + "11111111-1111-1111-1111-111111111111",
> + "11111111-3333-1111-1111-111111111111",
> + "22222222-1111-1111-1111-111111111111"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.18",
> + [[
> + SELECT MAX(u) from t2;
> + ]], {
> + uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.19",
> + [[
> + SELECT MIN(u) from t2;
> + ]], {
> + uuid1
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.20",
> + [[
> + SELECT NULLIF(u, 1) from t2;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +test:do_catchsql_test(
> + "uuid-6.1.21",
> + [[
> + SELECT POSITION(u, '1') from t2;
> + ]], {
> + 1, "Inconsistent types: expected text or varbinary got uuid"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.22",
> + [[
> + SELECT RANDOMBLOB(u) from t2;
> + ]], {
> + "", "", ""
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.23",
> + [[
> + SELECT REPLACE(u, '1', '2') from t2;
> + ]], {
> + "22222222-2222-2222-2222-222222222222",
> + "22222222-3333-2222-2222-222222222222",
> + "22222222-2222-2222-2222-222222222222"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-6.1.24",
> + [[
> + SELECT ROUND(u) from t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.25",
> + [[
> + SELECT SOUNDEX(u) from t2;
> + ]], {
> + "?000", "?000", "?000"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.26",
> + [[
> + SELECT SUBSTR(u, 3, 3) from t2;
> + ]], {
> + "111", "111", "222"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-6.1.27",
> + [[
> + SELECT SUM(u) from t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-6.1.28",
> + [[
> + SELECT TOTAL(u) from t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.29",
> + [[
> + SELECT TRIM(u) from t2;
> + ]], {
> + "11111111-1111-1111-1111-111111111111",
> + "11111111-3333-1111-1111-111111111111",
> + "22222222-1111-1111-1111-111111111111"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.30",
> + [[
> + SELECT TYPEOF(u) from t2;
> + ]], {
> + "uuid", "uuid", "uuid"
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.31",
> + [[
> + SELECT UNICODE(u) from t2;
> + ]], {
> + 49, 49, 50
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.32",
> + [[
> + SELECT UNLIKELY(u) from t2;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-6.1.33",
> + [[
> + SELECT UPPER(u) from t2;
> + ]], {
> + "11111111-1111-1111-1111-111111111111",
> + "11111111-3333-1111-1111-111111111111",
> + "22222222-1111-1111-1111-111111111111"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-6.1.33",
> + [[
> + SELECT u || u from t2;
> + ]], {
> + 1, "Inconsistent types: expected text or varbinary got uuid"
> + })
> +
> +local func = {language = 'Lua', body = 'function(x) return type(x) end',
> + returns = 'string', param_list = {'any'}, exports = {'SQL'}}
> +box.schema.func.create('RETURN_TYPE', func);
> +
> +-- Check that Lua user-defined functions can accept UUID.
> +test:do_execsql_test(
> + "uuid-6.2",
> + [[
> + SELECT RETURN_TYPE(u) FROM t2;
> + ]], {
> + "cdata", "cdata", "cdata"
> + })
> +
> +func = {language = 'Lua', returns = 'uuid', param_list = {}, exports = {'SQL'},
> + body = 'function(x) return require("uuid").fromstr("11111111-1111-1111-1111-111111111111") end'}
> +box.schema.func.create('GET_UUID', func);
> +
> +-- Check that Lua user-defined functions can return UUID.
> +test:do_execsql_test(
> + "uuid-6.3",
> + [[
> + SELECT GET_UUID();
> + ]], {
> + uuid1
> + })
> +
> +func = {language = 'C', returns = 'boolean', param_list = {'any'}, exports = {'SQL'}}
> +box.schema.func.create("sql_uuid.is_uuid", func)
> +
> +-- Check that C user-defined functions can accept UUID.
> +test:do_execsql_test(
> + "uuid-6.4",
> + [[
> + SELECT "sql_uuid.is_uuid"(i), "sql_uuid.is_uuid"(u) FROM t1 LIMIT 1;
> + ]], {
> + false, true
> + })
> +
> +func = {language = 'C', returns = 'uuid', param_list = {}, exports = {'SQL'}}
> +box.schema.func.create("sql_uuid.ret_uuid", func)
> +
> +-- Check that C user-defined functions can return UUID.
> +test:do_execsql_test(
> + "uuid-6.5",
> + [[
> + SELECT "sql_uuid.ret_uuid"();
> + ]], {
> + uuid1
> + })
> +
> +-- Check that explicit cast from UUID to another types works as intended.
> +test:do_catchsql_test(
> + "uuid-7.1.1",
> + [[
> + SELECT cast(u AS UNSIGNED) FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to unsigned"
> + })
> +
> +test:do_execsql_test(
> + "uuid-7.1.2",
> + [[
> + SELECT cast(u AS STRING) FROM t2;
> + ]], {
> + "11111111-1111-1111-1111-111111111111",
> + "11111111-3333-1111-1111-111111111111",
> + "22222222-1111-1111-1111-111111111111"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-7.1.3",
> + [[
> + SELECT cast(u AS NUMBER) FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-7.1.4",
> + [[
> + SELECT cast(u AS DOUBLE) FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to double"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-7.1.5",
> + [[
> + SELECT cast(u AS INTEGER) FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-7.1.6",
> + [[
> + SELECT cast(u AS BOOLEAN) FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
> + })
> +
> +test:do_execsql_test(
> + "uuid-7.1.7",
> + [[
> + SELECT hex(cast(u AS VARBINARY)) FROM t2;
> + ]], {
> + "11111111111111111111111111111111",
> + "11111111333311111111111111111111",
> + "22222222111111111111111111111111"
> + })
> +
> +test:do_execsql_test(
> + "uuid-7.1.8",
> + [[
> + SELECT cast(u AS SCALAR) FROM t2;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-7.1.9",
> + [[
> + SELECT cast(u AS UUID) FROM t2;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +-- Check that explicit cast from another types to UUID works as intended.
> +test:do_catchsql_test(
> + "uuid-7.2.1",
> + [[
> + SELECT cast(1 AS UUID);
> + ]], {
> + 1, "Type mismatch: can not convert 1 to uuid"
> + })
> +
> +test:do_execsql_test(
> + "uuid-7.2.2",
> + [[
> + SELECT cast('11111111-1111-1111-1111-111111111111' AS UUID);
> + ]], {
> + uuid1
> + })
> +
> +test:do_catchsql_test(
> + "uuid-7.2.3",
> + [[
> + SELECT cast('1' AS UUID);
> + ]], {
> + 1, "Type mismatch: can not convert 1 to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-7.2.4",
> + [[
> + SELECT cast(1.5 AS UUID);
> + ]], {
> + 1, "Type mismatch: can not convert 1.5 to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-7.2.5",
> + [[
> + SELECT cast(-1 AS UUID);
> + ]], {
> + 1, "Type mismatch: can not convert -1 to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-7.2.6",
> + [[
> + SELECT cast(true AS UUID);
> + ]], {
> + 1, "Type mismatch: can not convert TRUE to uuid"
> + })
> +
> +test:do_execsql_test(
> + "uuid-7.2.7",
> + [[
> + SELECT cast(x'11111111111111111111111111111111' AS UUID);
> + ]], {
> + uuid1
> + })
> +
> +test:do_catchsql_test(
> + "uuid-7.2.8",
> + [[
> + SELECT cast(randomblob(10) as UUID) FROM t2 LIMIT 1;
> + ]], {
> + 1, "Type mismatch: can not convert varbinary to uuid"
> + })
> +
> +test:execsql([[
> + CREATE TABLE tu (id INT PRIMARY KEY AUTOINCREMENT, u UNSIGNED);
> + CREATE TABLE ts (id INT PRIMARY KEY AUTOINCREMENT, s STRING);
> + CREATE TABLE tn (id INT PRIMARY KEY AUTOINCREMENT, n NUMBER);
> + CREATE TABLE td (id INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);
> + CREATE TABLE ti (id INT PRIMARY KEY AUTOINCREMENT, i INTEGER);
> + CREATE TABLE tb (id INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);
> + CREATE TABLE tv (id INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);
> + CREATE TABLE tsc (id INT PRIMARY KEY AUTOINCREMENT, sc SCALAR);
> + CREATE TABLE tuu (id INT PRIMARY KEY AUTOINCREMENT, uu UUID);
> + CREATE TABLE tsu (s STRING PRIMARY KEY, u UUID);
> +]])
> +
> +-- Check that implcit cast from UUID to another types works as intended.
> +test:do_catchsql_test(
> + "uuid-8.1.1",
> + [[
> + INSERT INTO tu(u) SELECT u FROM t2;
> + SELECT * FROM tu;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to unsigned"
> + })
> +
> +test:do_execsql_test(
> + "uuid-8.1.2",
> + [[
> + INSERT INTO ts(s) SELECT u FROM t2;
> + SELECT * FROM ts;
> + ]], {
> + 1, "11111111-1111-1111-1111-111111111111",
> + 2, "11111111-3333-1111-1111-111111111111",
> + 3, "22222222-1111-1111-1111-111111111111"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-8.1.3",
> + [[
> + INSERT INTO tn(n) SELECT u FROM t2;
> + SELECT * FROM tn;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-8.1.4",
> + [[
> + INSERT INTO td(d) SELECT u FROM t2;
> + SELECT * FROM td;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to double"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-8.1.5",
> + [[
> + INSERT INTO ti(i) SELECT u FROM t2;
> + SELECT * FROM ti;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-8.1.6",
> + [[
> + INSERT INTO tb(b) SELECT u FROM t2;
> + SELECT * FROM tb;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
> + })
> +
> +test:do_execsql_test(
> + "uuid-8.1.7",
> + [[
> + INSERT INTO tv(v) SELECT u FROM t2;
> + SELECT id, hex(v) FROM tv;
> + ]], {
> + 1, "11111111111111111111111111111111",
> + 2, "11111111333311111111111111111111",
> + 3, "22222222111111111111111111111111"
> + })
> +
> +test:do_execsql_test(
> + "uuid-8.1.8",
> + [[
> + INSERT INTO tsc(sc) SELECT u FROM t2;
> + SELECT * FROM tsc;
> + ]], {
> + 1, uuid1, 2, uuid3, 3, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-8.1.9",
> + [[
> + INSERT INTO tuu(uu) SELECT u FROM t2;
> + SELECT * FROM tuu;
> + ]], {
> + 1, uuid1, 2, uuid3, 3, uuid2
> + })
> +
> +-- Check that implicit cast from another types to UUID works as intended.
> +test:do_catchsql_test(
> + "uuid-8.2.1",
> + [[
> + INSERT INTO tsu VALUES ('1_unsigned', 1);
> + SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
> + ]], {
> + 1, "Type mismatch: can not convert 1 to uuid"
> + })
> +
> +test:do_execsql_test(
> + "uuid-8.2.2",
> + [[
> + INSERT INTO tsu VALUES ('2_string_right', '11111111-1111-1111-1111-111111111111');
> + SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
> + ]], {
> + '2_string_right', uuid1
> + })
> +
> +test:do_catchsql_test(
> + "uuid-8.2.3",
> + [[
> + INSERT INTO tsu VALUES ('3_string_wrong', '1');
> + SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
> + ]], {
> + 1, "Type mismatch: can not convert 1 to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-8.2.4",
> + [[
> + INSERT INTO tsu VALUES ('4_double', 1.5);
> + SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
> + ]], {
> + 1, "Type mismatch: can not convert 1.5 to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-8.2.5",
> + [[
> + INSERT INTO tsu VALUES ('5_integer', -1);
> + SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
> + ]], {
> + 1, "Type mismatch: can not convert -1 to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-8.2.6",
> + [[
> + INSERT INTO tsu VALUES ('6_boolean', true);
> + SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
> + ]], {
> + 1, "Type mismatch: can not convert TRUE to uuid"
> + })
> +
> +test:do_execsql_test(
> + "uuid-8.2.7",
> + [[
> + INSERT INTO tsu SELECT '7_varbinary', x'11111111111111111111111111111111' FROM t2 LIMIT 1;
> + SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
> + ]], {
> + '7_varbinary', uuid1
> + })
> +
> +test:do_catchsql_test(
> + "uuid-8.2.8",
> + [[
> + INSERT INTO tsu VALUES ('8_varbinary', randomblob(10));
> + SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
> + ]], {
> + 1, "Type mismatch: can not convert varbinary to uuid"
> + })
> +
> +test:execsql([[
> + CREATE TABLE t9 (i INT PRIMARY KEY AUTOINCREMENT, u UUID);
> + CREATE TABLE t9t (u UUID PRIMARY KEY);
> + CREATE TRIGGER t AFTER INSERT ON t9 FOR EACH ROW BEGIN INSERT INTO t9t SELECT new.u; END;
> +]])
> +
> +-- Check that trigger can work with UUID.
> +test:do_execsql_test(
> + "uuid-9",
> + [[
> + INSERT INTO t9(u) SELECT * FROM t2;
> + SELECT * FROM t9t;
> + ]], {
> + uuid1, uuid3, uuid2
> + })
> +
> +test:execsql([[
> + CREATE TABLE t10 (i INT PRIMARY KEY AUTOINCREMENT, u UUID DEFAULT '11111111-1111-1111-1111-111111111111');
> +]])
> +
> +-- Check that INSERT into UUID field works.
> +test:do_execsql_test(
> + "uuid-10.1.1",
> + [[
> + INSERT INTO t10 VALUES (1, '22222222-1111-1111-1111-111111111111');
> + SELECT * FROM t10 WHERE i = 1;
> + ]], {
> + 1, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-10.1.2",
> + [[
> + INSERT INTO t10 VALUES (2, x'22222222111111111111111111111111');
> + SELECT * FROM t10 WHERE i = 2;
> + ]], {
> + 2, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-10.1.3",
> + [[
> + INSERT INTO t10(i) VALUES (3);
> + SELECT * FROM t10 WHERE i = 3;
> + ]], {
> + 3, uuid1
> + })
> +
> +test:do_execsql_test(
> + "uuid-10.1.4",
> + [[
> + INSERT INTO t10 VALUES (4, NULL);
> + SELECT * FROM t10 WHERE i = 4;
> + ]], {
> + 4, ''
> + })
> +
> +-- Check that UPDATE of UUID field works.
> +test:do_execsql_test(
> + "uuid-10.2.1",
> + [[
> + UPDATE t10 SET u = '11111111-3333-1111-1111-111111111111' WHERE i = 1;
> + SELECT * FROM t10 WHERE i = 1;
> + ]], {
> + 1, uuid3
> + })
> +
> +test:do_execsql_test(
> + "uuid-10.2.2",
> + [[
> + UPDATE t10 SET u = x'11111111333311111111111111111111' WHERE i = 2;
> + SELECT * FROM t10 WHERE i = 2;
> + ]], {
> + 2, uuid3
> + })
> +
> +-- Check that JOIN by UUID field works.
> +test:do_execsql_test(
> + "uuid-11.1",
> + [[
> + SELECT * FROM t1 JOIN t2 on t1.u = t2.u;
> + ]], {
> + 1, uuid1, uuid1, 2, uuid2, uuid2, 3, uuid3, uuid3,
> + 4, uuid1, uuid1, 5, uuid1, uuid1, 6, uuid2, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-11.2",
> + [[
> + SELECT * FROM t1 LEFT JOIN t2 on t1.u = t2.u;
> + ]], {
> + 1, uuid1, uuid1, 2, uuid2, uuid2, 3, uuid3, uuid3,
> + 4, uuid1, uuid1, 5, uuid1, uuid1, 6, uuid2, uuid2
> + })
> +
> +test:do_execsql_test(
> + "uuid-11.3",
> + [[
> + SELECT * FROM t1 INNER JOIN t2 on t1.u = t2.u;
> + ]], {
> + 1, uuid1, uuid1, 2, uuid2, uuid2, 3, uuid3, uuid3,
> + 4, uuid1, uuid1, 5, uuid1, uuid1, 6, uuid2, uuid2
> + })
> +
> +-- Check that arithmetic operations work with UUIDs as intended.
> +test:do_catchsql_test(
> + "uuid-12.1.1",
> + [[
> + SELECT -u FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.1.2",
> + [[
> + SELECT u + 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.1.3",
> + [[
> + SELECT u - 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.1.4",
> + [[
> + SELECT u * 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.1.5",
> + [[
> + SELECT u / 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.1.6",
> + [[
> + SELECT u % 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
> + })
> +
> +-- Check that bitwise operations work with UUIDs as intended.
> +test:do_catchsql_test(
> + "uuid-12.2.1",
> + [[
> + SELECT ~u FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.2.2",
> + [[
> + SELECT u >> 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.2.3",
> + [[
> + SELECT u << 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.2.4",
> + [[
> + SELECT u | 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.2.5",
> + [[
> + SELECT u & 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
> + })
> +
> +-- Check that logical operations work with UUIDs as intended.
> +test:do_catchsql_test(
> + "uuid-12.3.1",
> + [[
> + SELECT NOT u FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.3.2",
> + [[
> + SELECT u AND true FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.3.3",
> + [[
> + SELECT u OR true FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.3.4",
> + [[
> + SELECT true AND u FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-12.3.5",
> + [[
> + SELECT true OR u FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
> + })
> +
> +-- Check that comparison with UUID works as intended.
> +test:do_catchsql_test(
> + "uuid-13.1.1",
> + [[
> + SELECT u > 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert unsigned to uuid"
> + })
> +
> +test:do_execsql_test(
> + "uuid-13.1.2",
> + [[
> + SELECT u > CAST('11111111-1111-1111-1111-111111111111' AS UUID) FROM t2;
> + ]], {
> + false, true, true
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.1.3",
> + [[
> + SELECT u > '1' FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert text to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.1.4",
> + [[
> + SELECT u > 1.5 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert real to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.1.5",
> + [[
> + SELECT u > -1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert integer to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.1.6",
> + [[
> + SELECT u > true FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert uuid to boolean"
> + })
> +
> +test:do_execsql_test(
> + "uuid-13.1.7",
> + [[
> + SELECT u > CAST(x'11111111111111111111111111111111' AS UUID) FROM t2;
> + ]], {
> + false, true, true
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.1.8",
> + [[
> + SELECT u > x'31' FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert varbinary to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.2.1",
> + [[
> + SELECT u = 1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert unsigned to uuid"
> + })
> +
> +test:do_execsql_test(
> + "uuid-13.2.2",
> + [[
> + SELECT u = CAST('11111111-1111-1111-1111-111111111111' AS UUID) FROM t2;
> + ]], {
> + true, false, false
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.2.3",
> + [[
> + SELECT u = '1' FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert text to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.2.4",
> + [[
> + SELECT u = 1.5 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert real to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.2.5",
> + [[
> + SELECT u = -1 FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert integer to uuid"
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.2.6",
> + [[
> + SELECT u = true FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert uuid to boolean"
> + })
> +
> +test:do_execsql_test(
> + "uuid-13.2.7",
> + [[
> + SELECT u = CAST(x'11111111111111111111111111111111' AS UUID) FROM t2;
> + ]], {
> + true, false, false
> + })
> +
> +test:do_catchsql_test(
> + "uuid-13.2.8",
> + [[
> + SELECT u = x'31' FROM t2;
> + ]], {
> + 1, "Type mismatch: can not convert varbinary to uuid"
> + })
> +
> +test:execsql([[
> + CREATE TABLE t14 (s SCALAR PRIMARY KEY);
> +]])
> +
> +-- Check that SCALAR field can contain UUID and use it in index.
> +test:do_execsql_test(
> + "uuid-14",
> + [[
> + INSERT INTO t14 VALUES (1), (true), (1.5), (-1);
> + INSERT INTO t14 VALUES (x'11111111111111111111111111111111');
> + INSERT INTO t14 VALUES (CAST(x'11111111111111111111111111111111' AS UUID));
> + INSERT INTO t14 VALUES ('11111111-1111-1111-1111-111111111111');
> + SELECT typeof(s) FROM t14;
> + ]], {
> + "boolean", "integer", "integer", "double", "string", "varbinary", "uuid"
> + })
> +
> +test:finish_test()
> --
> 2.25.1
>
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
2021-05-20 9:44 [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type Mergen Imeev via Tarantool-patches
2021-05-21 7:16 ` Mergen Imeev via Tarantool-patches
@ 2021-05-22 16:04 ` Vladislav Shpilevoy via Tarantool-patches
2021-05-25 14:13 ` Mergen Imeev via Tarantool-patches
1 sibling, 1 reply; 8+ messages in thread
From: Vladislav Shpilevoy via Tarantool-patches @ 2021-05-22 16:04 UTC (permalink / raw)
To: imeevma; +Cc: tarantool-patches
Hi! Thanks for the patch!
See 15 comments below.
> sql: introduce UUID field type
>
> This patch introduces UUID to SQL. UUID is now available as a new field
> type.
>
> Closes #5886
>
> @TarantoolBot document
> Title: Field type UUID is now available in SQL
>
> The UUID field type is now available in SQL. This means that we can
> create spaces and indexes with UUID, use it in SELECT, UPDATE and
> DELETE. UUID can be accepted and returned by built-in functions and
> user-defined functions.
>
> During comparison, UUID cannot be implicitly converted to any other
> types, but in any other case, UUID can be implicitly converted to STRING
> or VARBINARY.
1. I couldn't parse this - can it be implicitly converted to STRING/VARBINARY
or not? What is this "any other case"?
In the RFC I see that UUID should be ok to be implicitly converted to STRING
and VARBINARY.
> UUID can be explicitly converted to STRING or VARBINARY
> using CAST(). STRING and VARBINARY can be implicitly converted to UUID,
> except for comparison. STRING and VARBINARY can be explicitly converted
> to UUID using CAST().
>
> diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
> index 47845599e..9288ef341 100644
> --- a/src/box/sql/mem.c
> +++ b/src/box/sql/mem.c
> @@ -58,10 +58,22 @@ enum {
> BUF_SIZE = 32,
> };
>
> +bool
> +mem_is_field_compatible(const struct Mem *mem, enum field_type type,
> + bool is_nullable)
2. Nullable flag is passed as true in the only usage place of this function.
I propose to inline it here to true and drop the argument.
> +{
> + if (mem->type == MEM_TYPE_UUID)
> + return (field_ext_type[type] & (1U << MP_UUID)) != 0;
> + enum mp_type mp_type = mem_mp_type(mem);
> + assert(mp_type != MP_EXT);
> + return field_mp_plain_type_is_compatible(type, mp_type, is_nullable);
> +}
> @@ -81,6 +93,9 @@ mem_str(const struct Mem *mem)
> case MEM_TYPE_MAP:
> case MEM_TYPE_ARRAY:
> return mp_str(mem->z);
> + case MEM_TYPE_UUID:
> + tt_uuid_to_string(&mem->u.uuid, &buf[0]);
> + return tt_sprintf("%s", buf);
3. You don't need to change buf size. Use tt_uuid_str().
> case MEM_TYPE_BOOL:
> return mem->u.b ? "TRUE" : "FALSE";
> default:
> @@ -190,6 +205,16 @@ mem_set_double(struct Mem *mem, double value)
> mem->type = MEM_TYPE_DOUBLE;
> }
>
> +void
> +mem_set_uuid(struct Mem *mem, struct tt_uuid *uuid)
4. You can make uuid argument const.
> +{
> + mem_clear(mem);
> + mem->field_type = FIELD_TYPE_UUID;
> + mem->u.uuid = *uuid;
> + mem->type = MEM_TYPE_UUID;
> + mem->flags = 0;
5. Flags should be already 0 after clear().
> +}
> +
> static inline void
> set_str_const(struct Mem *mem, char *value, uint32_t len, int alloc_type)
> {
> @@ -585,6 +610,18 @@ str_to_bin(struct Mem *mem)
> return 0;
> }
>
> +static inline int
> +str_to_uuid(struct Mem *mem)
> +{
> + assert(mem->type == MEM_TYPE_STR);
> + if (tt_uuid_from_string(tt_cstr(mem->z, mem->n), &mem->u.uuid) != 0)
> + return -1;
6. This is going to crash if mem->n is more than the static buffer size.
I would suggest to introduce some way to extract UUID from a non-terminated
string, and add a test maybe.
The same in mem_get_uuid(). Which is not used anywhere btw. Perhaps you can
drop it.
> @@ -639,6 +676,20 @@ bin_to_str0(struct Mem *mem)
> return 0;
> }
>
> +static inline int
> +bin_to_uuid(struct Mem *mem)
> +{
> + assert(mem->type == MEM_TYPE_BIN);
> + if (mem->n != UUID_LEN ||
> + tt_uuid_validate((struct tt_uuid *)mem->z) != 0)
7. What if this is MEM_Zero and z is not allocated yet?
> + return -1;
> + mem->u.uuid = *(struct tt_uuid *)mem->z;
> + mem->type = MEM_TYPE_UUID;
> + mem->flags = 0;
> + mem->field_type = FIELD_TYPE_UUID;
> + return 0;
> +}
> @@ -2566,11 +2710,24 @@ sqlVdbeCompareMsgpack(const char **key1,
> case MP_ARRAY:
> case MP_MAP:
> case MP_EXT:{
> - mem1.z = (char *)aKey1;
> - mp_next(&aKey1);
> - mem1.n = aKey1 - (char *)mem1.z;
> - goto do_blob;
> + int8_t type;
> + const char *buf = aKey1;
> + uint32_t len = mp_decode_extl(&buf, &type);
8. This is going to crash when type is MP_ARRAY or MP_MAP. Is it
possible to add a test?
> + buf = aKey1;
> + mp_next(&aKey1);
9. You can skip faster, using buf + len which you already decoded.
The same in mem_from_mp_ephemeral().
> @@ -2849,6 +3023,11 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat)
> case MEM_TYPE_BOOL:
> lua_pushboolean(L, mem->u.b);
> break;
> + case MEM_TYPE_UUID: {
> + struct tt_uuid *uuid = luaL_pushuuid(L);
> + *uuid = mem->u.uuid;
10. You could do *luaL_pushuuid(L) = mem->u.uuid right away, without
the temporary variable. Up to you.
> @@ -3073,7 +3249,18 @@ port_c_get_vdbemem(struct port *base, uint32_t *size)
> break;
> case MP_EXT:
> str = data;
> - mp_next(&data);
> + int8_t type;
> + len = mp_decode_extl(&data, &type);
> + if (type == MP_UUID) {
> + assert(len == UUID_LEN);
> + struct tt_uuid *uuid = &val[i].u.uuid;
> + data = str;
> + if (mp_decode_uuid(&data, uuid) == NULL)
> + goto error;
> + val[i].type = MEM_TYPE_UUID;
> + break;
> + }
> + data += len;
> if (mem_copy_bin(&val[i], str, data - str) != 0)
> goto error;
> break;
11. You need to patch memTracePrint() as well.
> diff --git a/test/sql-tap/sql_uuid.c b/test/sql-tap/sql_uuid.c
> new file mode 100644
> index 000000000..592b9e48f
> --- /dev/null
> +++ b/test/sql-tap/sql_uuid.c
12. It is already in sql* folder, no need to prefix the file name
with 'sql_'.
> @@ -0,0 +1,57 @@
> +#include "msgpuck.h"
> +#include "module.h"
> +#include "uuid/mp_uuid.h"
> +#include "mp_extension_types.h"
> +
> +enum {
> + BUF_SIZE = 512,
> +};
> +
> +int
> +is_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
> +{
> + uint32_t arg_count = mp_decode_array(&args);
> + if (arg_count != 1) {
> + return box_error_set(__FILE__, __LINE__, ER_PROC_C,
> + "invalid argument count");
> + }
> + bool is_uuid;
> + if (mp_typeof(*args) == MP_EXT) {
> + const char *str = args;
> + int8_t type;
> + mp_decode_extl(&str, &type);
> + is_uuid = type == MP_UUID;
> + } else {
> + is_uuid = false;
> + }
> +
> + char tuple_buf[BUF_SIZE];
> + assert(mp_sizeof_array(1) + mp_sizeof_bool(is_uuid) < BUF_SIZE);
> + char *d = tuple_buf;
> + d = mp_encode_array(d, 1);
> + d = mp_encode_bool(d, is_uuid);
> +
> + box_tuple_format_t *fmt = box_tuple_format_default();
> + box_tuple_t *tuple = box_tuple_new(fmt, tuple_buf, d);
> + if (tuple == NULL)
> + return -1;
> + return box_return_tuple(ctx, tuple);
13. Use box_return_mp() instead of all that. The same below.
> +}
> +
> +int
> +ret_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
> +{
> + struct tt_uuid uuid;
> + memset(&uuid, 0x11, sizeof(uuid));
> + char tuple_buf[BUF_SIZE];
> + assert(mp_sizeof_array(1) + mp_sizeof_uuid() < BUF_SIZE);
> + char *d = tuple_buf;
> + d = mp_encode_array(d, 1);
> + d = mp_encode_uuid(d, &uuid);
> +
> + box_tuple_format_t *fmt = box_tuple_format_default();
> + box_tuple_t *tuple = box_tuple_new(fmt, tuple_buf, d);
> + if (tuple == NULL)
> + return -1;
> + return box_return_tuple(ctx, tuple);
> +}
> diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
> new file mode 100755
> index 000000000..9210d05db
> --- /dev/null
> +++ b/test/sql-tap/uuid.test.lua
> @@ -0,0 +1,1259 @@
<...>
> +
> +-- Check that CHECK constraint can work with UUID.
> +test:do_catchsql_test(
> + "uuid-5.2.1",
> + [[
> + INSERT INTO t5c SELECT 1, u FROM t2 LIMIT 1;
> + SELECT * from t5c;
14. This SELECT is unreachable. The same in 5.3.2, 8.1.3,
8.1.4, 8.1.5, 8.1.6, 8.2.1, 8.2.3, 8.2.4, 8.2.5, 8.2.6, 8.2.8.
> + ]], {
> + 1, "Check constraint failed 'CK': CAST(f AS STRING) != '11111111-1111-1111-1111-111111111111'"
> + })
15. It might worth dropping the created spaces in order to clear
the test artifacts.
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
2021-05-22 16:04 ` Vladislav Shpilevoy via Tarantool-patches
@ 2021-05-25 14:13 ` Mergen Imeev via Tarantool-patches
2021-05-25 21:50 ` Vladislav Shpilevoy via Tarantool-patches
2021-05-25 22:58 ` Timur Safin via Tarantool-patches
0 siblings, 2 replies; 8+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-05-25 14:13 UTC (permalink / raw)
To: Vladislav Shpilevoy; +Cc: tarantool-patches
Hi! Thank you for the review! My answers and new patch below. I didn't include
diff since there were some merge conflicts with patch-set reviewed by Nikita.
On Sat, May 22, 2021 at 06:04:15PM +0200, Vladislav Shpilevoy wrote:
> Hi! Thanks for the patch!
>
> See 15 comments below.
>
> > sql: introduce UUID field type
> >
> > This patch introduces UUID to SQL. UUID is now available as a new field
> > type.
> >
> > Closes #5886
> >
> > @TarantoolBot document
> > Title: Field type UUID is now available in SQL
> >
> > The UUID field type is now available in SQL. This means that we can
> > create spaces and indexes with UUID, use it in SELECT, UPDATE and
> > DELETE. UUID can be accepted and returned by built-in functions and
> > user-defined functions.
> >
> > During comparison, UUID cannot be implicitly converted to any other
> > types, but in any other case, UUID can be implicitly converted to STRING
> > or VARBINARY.
>
> 1. I couldn't parse this - can it be implicitly converted to STRING/VARBINARY
> or not? What is this "any other case"?
>
'Any other case' here means 'in all operations except comparison'
> In the RFC I see that UUID should be ok to be implicitly converted to STRING
> and VARBINARY.
>
True, except comparison.
> > UUID can be explicitly converted to STRING or VARBINARY
> > using CAST(). STRING and VARBINARY can be implicitly converted to UUID,
> > except for comparison. STRING and VARBINARY can be explicitly converted
> > to UUID using CAST().
> >
> > diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
> > index 47845599e..9288ef341 100644
> > --- a/src/box/sql/mem.c
> > +++ b/src/box/sql/mem.c
> > @@ -58,10 +58,22 @@ enum {
> > BUF_SIZE = 32,
> > };
> >
> > +bool
> > +mem_is_field_compatible(const struct Mem *mem, enum field_type type,
> > + bool is_nullable)
>
> 2. Nullable flag is passed as true in the only usage place of this function.
> I propose to inline it here to true and drop the argument.
>
Fixed.
> > +{
> > + if (mem->type == MEM_TYPE_UUID)
> > + return (field_ext_type[type] & (1U << MP_UUID)) != 0;
> > + enum mp_type mp_type = mem_mp_type(mem);
> > + assert(mp_type != MP_EXT);
> > + return field_mp_plain_type_is_compatible(type, mp_type, is_nullable);
> > +}
> > @@ -81,6 +93,9 @@ mem_str(const struct Mem *mem)
> > case MEM_TYPE_MAP:
> > case MEM_TYPE_ARRAY:
> > return mp_str(mem->z);
> > + case MEM_TYPE_UUID:
> > + tt_uuid_to_string(&mem->u.uuid, &buf[0]);
> > + return tt_sprintf("%s", buf);
>
> 3. You don't need to change buf size. Use tt_uuid_str().
>
Thanks! Fixed.
> > case MEM_TYPE_BOOL:
> > return mem->u.b ? "TRUE" : "FALSE";
> > default:
> > @@ -190,6 +205,16 @@ mem_set_double(struct Mem *mem, double value)
> > mem->type = MEM_TYPE_DOUBLE;
> > }
> >
> > +void
> > +mem_set_uuid(struct Mem *mem, struct tt_uuid *uuid)
>
> 4. You can make uuid argument const.
>
Fixed.
> > +{
> > + mem_clear(mem);
> > + mem->field_type = FIELD_TYPE_UUID;
> > + mem->u.uuid = *uuid;
> > + mem->type = MEM_TYPE_UUID;
> > + mem->flags = 0;
>
> 5. Flags should be already 0 after clear().
>
Fixed.
> > +}
> > +
> > static inline void
> > set_str_const(struct Mem *mem, char *value, uint32_t len, int alloc_type)
> > {
> > @@ -585,6 +610,18 @@ str_to_bin(struct Mem *mem)
> > return 0;
> > }
> >
> > +static inline int
> > +str_to_uuid(struct Mem *mem)
> > +{
> > + assert(mem->type == MEM_TYPE_STR);
> > + if (tt_uuid_from_string(tt_cstr(mem->z, mem->n), &mem->u.uuid) != 0)
> > + return -1;
>
> 6. This is going to crash if mem->n is more than the static buffer size.
> I would suggest to introduce some way to extract UUID from a non-terminated
> string, and add a test maybe.
>
I added check for length. I think there should be no issues anymore since
string representation of UUID has length of 36 bytes. By the way, varbinary
representation of UUID has length of 16 bytes. This is a bt strange since
usually string representation == varbinary representation.
> The same in mem_get_uuid(). Which is not used anywhere btw. Perhaps you can
> drop it.
>
I couldn't think of any way to use mem_get_uuid(), so I dropped it.
> > @@ -639,6 +676,20 @@ bin_to_str0(struct Mem *mem)
> > return 0;
> > }
> >
> > +static inline int
> > +bin_to_uuid(struct Mem *mem)
> > +{
> > + assert(mem->type == MEM_TYPE_BIN);
> > + if (mem->n != UUID_LEN ||
> > + tt_uuid_validate((struct tt_uuid *)mem->z) != 0)
>
> 7. What if this is MEM_Zero and z is not allocated yet?
>
Fixed, added ExpandBlob()
> > + return -1;
> > + mem->u.uuid = *(struct tt_uuid *)mem->z;
> > + mem->type = MEM_TYPE_UUID;
> > + mem->flags = 0;
> > + mem->field_type = FIELD_TYPE_UUID;
> > + return 0;
> > +}
> > @@ -2566,11 +2710,24 @@ sqlVdbeCompareMsgpack(const char **key1,
> > case MP_ARRAY:
> > case MP_MAP:
> > case MP_EXT:{
> > - mem1.z = (char *)aKey1;
> > - mp_next(&aKey1);
> > - mem1.n = aKey1 - (char *)mem1.z;
> > - goto do_blob;
> > + int8_t type;
> > + const char *buf = aKey1;
> > + uint32_t len = mp_decode_extl(&buf, &type);
>
> 8. This is going to crash when type is MP_ARRAY or MP_MAP. Is it
> possible to add a test?
>
True, fixed. Added a test (uuid-15.1 and uuid-15.2). I believe they works not
the way they should, but at least there is no error and change of behaviour.
> > + buf = aKey1;
> > + mp_next(&aKey1);
>
> 9. You can skip faster, using buf + len which you already decoded.
> The same in mem_from_mp_ephemeral().
>
> > @@ -2849,6 +3023,11 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat)
> > case MEM_TYPE_BOOL:
> > lua_pushboolean(L, mem->u.b);
> > break;
> > + case MEM_TYPE_UUID: {
> > + struct tt_uuid *uuid = luaL_pushuuid(L);
> > + *uuid = mem->u.uuid;
>
> 10. You could do *luaL_pushuuid(L) = mem->u.uuid right away, without
> the temporary variable. Up to you.
>
Thanks! Fixed.
> > @@ -3073,7 +3249,18 @@ port_c_get_vdbemem(struct port *base, uint32_t *size)
> > break;
> > case MP_EXT:
> > str = data;
> > - mp_next(&data);
> > + int8_t type;
> > + len = mp_decode_extl(&data, &type);
> > + if (type == MP_UUID) {
> > + assert(len == UUID_LEN);
> > + struct tt_uuid *uuid = &val[i].u.uuid;
> > + data = str;
> > + if (mp_decode_uuid(&data, uuid) == NULL)
> > + goto error;
> > + val[i].type = MEM_TYPE_UUID;
> > + break;
> > + }
> > + data += len;
> > if (mem_copy_bin(&val[i], str, data - str) != 0)
> > goto error;
> > break;
>
> 11. You need to patch memTracePrint() as well.
>
Fixed.
> > diff --git a/test/sql-tap/sql_uuid.c b/test/sql-tap/sql_uuid.c
> > new file mode 100644
> > index 000000000..592b9e48f
> > --- /dev/null
> > +++ b/test/sql-tap/sql_uuid.c
>
> 12. It is already in sql* folder, no need to prefix the file name
> with 'sql_'.
>
I added 'sql_' prefix since CMake already has a target with name 'uuid'.
> > @@ -0,0 +1,57 @@
> > +#include "msgpuck.h"
> > +#include "module.h"
> > +#include "uuid/mp_uuid.h"
> > +#include "mp_extension_types.h"
> > +
> > +enum {
> > + BUF_SIZE = 512,
> > +};
> > +
> > +int
> > +is_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
> > +{
> > + uint32_t arg_count = mp_decode_array(&args);
> > + if (arg_count != 1) {
> > + return box_error_set(__FILE__, __LINE__, ER_PROC_C,
> > + "invalid argument count");
> > + }
> > + bool is_uuid;
> > + if (mp_typeof(*args) == MP_EXT) {
> > + const char *str = args;
> > + int8_t type;
> > + mp_decode_extl(&str, &type);
> > + is_uuid = type == MP_UUID;
> > + } else {
> > + is_uuid = false;
> > + }
> > +
> > + char tuple_buf[BUF_SIZE];
> > + assert(mp_sizeof_array(1) + mp_sizeof_bool(is_uuid) < BUF_SIZE);
> > + char *d = tuple_buf;
> > + d = mp_encode_array(d, 1);
> > + d = mp_encode_bool(d, is_uuid);
> > +
> > + box_tuple_format_t *fmt = box_tuple_format_default();
> > + box_tuple_t *tuple = box_tuple_new(fmt, tuple_buf, d);
> > + if (tuple == NULL)
> > + return -1;
> > + return box_return_tuple(ctx, tuple);
>
> 13. Use box_return_mp() instead of all that. The same below.
>
Thanks! Fixed.
> > +}
> > +
> > +int
> > +ret_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
> > +{
> > + struct tt_uuid uuid;
> > + memset(&uuid, 0x11, sizeof(uuid));
> > + char tuple_buf[BUF_SIZE];
> > + assert(mp_sizeof_array(1) + mp_sizeof_uuid() < BUF_SIZE);
> > + char *d = tuple_buf;
> > + d = mp_encode_array(d, 1);
> > + d = mp_encode_uuid(d, &uuid);
> > +
> > + box_tuple_format_t *fmt = box_tuple_format_default();
> > + box_tuple_t *tuple = box_tuple_new(fmt, tuple_buf, d);
> > + if (tuple == NULL)
> > + return -1;
> > + return box_return_tuple(ctx, tuple);
> > +}
> > diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
> > new file mode 100755
> > index 000000000..9210d05db
> > --- /dev/null
> > +++ b/test/sql-tap/uuid.test.lua
> > @@ -0,0 +1,1259 @@
>
> <...>
>
> > +
> > +-- Check that CHECK constraint can work with UUID.
> > +test:do_catchsql_test(
> > + "uuid-5.2.1",
> > + [[
> > + INSERT INTO t5c SELECT 1, u FROM t2 LIMIT 1;
> > + SELECT * from t5c;
>
> 14. This SELECT is unreachable. The same in 5.3.2, 8.1.3,
> 8.1.4, 8.1.5, 8.1.6, 8.2.1, 8.2.3, 8.2.4, 8.2.5, 8.2.6, 8.2.8.
>
Removed all unreachable SELECTs.
> > + ]], {
> > + 1, "Check constraint failed 'CK': CAST(f AS STRING) != '11111111-1111-1111-1111-111111111111'"
> > + })
> 15. It might worth dropping the created spaces in order to clear
> the test artifacts.
>
Fixed.
New patch:
commit 00d4600460156ed0e2efed9902f9804c4248d39a
Author: Mergen Imeev <imeevma@gmail.com>
Date: Thu Apr 22 15:01:58 2021 +0300
sql: introduce UUID field type
This patch introduces UUID to SQL. UUID is now available as a new field
type.
Closes #5886
@TarantoolBot document
Title: Field type UUID is now available in SQL
The UUID field type is now available in SQL. This means that we can
create spaces and indexes with UUID, use it in SELECT, UPDATE and
DELETE. UUID can be accepted and returned by built-in functions and
user-defined functions.
According to the comparison rules, there will be no implicit casting in
the comparison. This rule also applies to UUID values: if a value is not
part of a SCALAR field, it cannot be compared to a value of any other
type. If the value is in a SCALAR field, it can be compared to any other
scalar value according to the comparison rules for a SCALAR field.
In case a UUID value is used in an operation that is not a comparison,
it can be implicitly converted to STRING or VARBINARY.
If a STRING or VARBINARY value is used in an operation that is not a
comparison, it can be implicitly converted to a UUID.
UUID value can always be explicitly converted to STRING or VARBINARY.
A STRING or VARBINARY value can be explicitly converted to a UUID if it
conforms to the UUID standard.
diff --git a/changelogs/unreleased/introduce-uuid-to-sql.md b/changelogs/unreleased/introduce-uuid-to-sql.md
new file mode 100644
index 000000000..120fb145a
--- /dev/null
+++ b/changelogs/unreleased/introduce-uuid-to-sql.md
@@ -0,0 +1,3 @@
+## feature/core
+
+ * Field type UUID is now available in SQL (gh-5886).
diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 7480c0211..0d998506c 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -172,6 +172,7 @@ static Keyword aKeywordTable[] = {
{ "UNSIGNED", "TK_UNSIGNED", true },
{ "UPDATE", "TK_UPDATE", true },
{ "USING", "TK_USING", true },
+ { "UUID" , "TK_UUID" , true },
{ "VALUES", "TK_VALUES", true },
{ "VARBINARY", "TK_VARBINARY", true },
{ "VIEW", "TK_VIEW", true },
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 90e8e152f..9c4480a92 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -58,7 +58,8 @@ static const void *
mem_as_bin(struct Mem *mem)
{
const char *s;
- if (!mem_is_bytes(mem) && mem_to_str(mem) != 0)
+ if (mem_cast_implicit(mem, FIELD_TYPE_VARBINARY) != 0 &&
+ mem_to_str(mem) != 0)
return NULL;
if (mem_get_bin(mem, &s) != 0)
return NULL;
@@ -142,26 +143,29 @@ typeofFunc(sql_context * context, int NotUsed, sql_value ** argv)
-1, SQL_STATIC);
return;
}
- switch (sql_value_type(argv[0])) {
- case MP_INT:
- case MP_UINT:
+ switch (argv[0]->type) {
+ case MEM_TYPE_INT:
+ case MEM_TYPE_UINT:
z = "integer";
break;
- case MP_STR:
+ case MEM_TYPE_STR:
z = "string";
break;
- case MP_DOUBLE:
+ case MEM_TYPE_DOUBLE:
z = "double";
break;
- case MP_BIN:
- case MP_ARRAY:
- case MP_MAP:
+ case MEM_TYPE_BIN:
+ case MEM_TYPE_ARRAY:
+ case MEM_TYPE_MAP:
z = "varbinary";
break;
- case MP_BOOL:
- case MP_NIL:
+ case MEM_TYPE_BOOL:
+ case MEM_TYPE_NULL:
z = "boolean";
break;
+ case MEM_TYPE_UUID:
+ z = "uuid";
+ break;
default:
unreachable();
break;
@@ -191,6 +195,7 @@ lengthFunc(sql_context * context, int argc, sql_value ** argv)
sql_result_uint(context, mem_len_unsafe(argv[0]));
break;
}
+ case MP_EXT:
case MP_STR:{
const unsigned char *z = mem_as_ustr(argv[0]);
if (z == 0)
@@ -235,6 +240,7 @@ absFunc(sql_context * context, int argc, sql_value ** argv)
}
case MP_BOOL:
case MP_BIN:
+ case MP_EXT:
case MP_ARRAY:
case MP_MAP: {
diag_set(ClientError, ER_INCONSISTENT_TYPES, "number",
@@ -1461,8 +1467,8 @@ trim_func_one_arg(struct sql_context *context, sql_value *arg)
default_trim = (const unsigned char *) "\0";
else
default_trim = (const unsigned char *) " ";
- int input_str_sz = mem_len_unsafe(arg);
const unsigned char *input_str = mem_as_ustr(arg);
+ int input_str_sz = mem_len_unsafe(arg);
uint8_t trim_char_len[1] = { 1 };
trim_procedure(context, TRIM_BOTH, default_trim, trim_char_len, 1,
input_str, input_str_sz);
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index a518fd0c6..9a6689d7d 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -58,6 +58,16 @@ enum {
BUF_SIZE = 32,
};
+bool
+mem_is_field_compatible(const struct Mem *mem, enum field_type type)
+{
+ if (mem->type == MEM_TYPE_UUID)
+ return (field_ext_type[type] & (1U << MP_UUID)) != 0;
+ enum mp_type mp_type = mem_mp_type(mem);
+ assert(mp_type != MP_EXT);
+ return field_mp_plain_type_is_compatible(type, mp_type, true);
+}
+
const char *
mem_str(const struct Mem *mem)
{
@@ -81,6 +91,8 @@ mem_str(const struct Mem *mem)
case MEM_TYPE_MAP:
case MEM_TYPE_ARRAY:
return mp_str(mem->z);
+ case MEM_TYPE_UUID:
+ return tt_sprintf("%s", tt_uuid_str(&mem->u.uuid));
case MEM_TYPE_BOOL:
return mem->u.b ? "TRUE" : "FALSE";
default:
@@ -190,6 +202,16 @@ mem_set_double(struct Mem *mem, double value)
mem->type = MEM_TYPE_DOUBLE;
}
+void
+mem_set_uuid(struct Mem *mem, const struct tt_uuid *uuid)
+{
+ mem_clear(mem);
+ mem->field_type = FIELD_TYPE_UUID;
+ mem->u.uuid = *uuid;
+ mem->type = MEM_TYPE_UUID;
+ assert(mem->flags == 0);
+}
+
static inline void
set_str_const(struct Mem *mem, char *value, uint32_t len, int alloc_type)
{
@@ -585,6 +607,18 @@ str_to_bin(struct Mem *mem)
return 0;
}
+static inline int
+str_to_uuid(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_STR);
+ struct tt_uuid uuid;
+ if (mem->n != UUID_STR_LEN ||
+ tt_uuid_from_string(tt_cstr(mem->z, mem->n), &uuid) != 0)
+ return -1;
+ mem_set_uuid(mem, &uuid);
+ return 0;
+}
+
static inline int
str_to_bool(struct Mem *mem)
{
@@ -639,6 +673,19 @@ bin_to_str0(struct Mem *mem)
return 0;
}
+static inline int
+bin_to_uuid(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_BIN);
+ if (ExpandBlob(mem) != 0)
+ return -1;
+ if (mem->n != UUID_LEN ||
+ tt_uuid_validate((struct tt_uuid *)mem->z) != 0)
+ return -1;
+ mem_set_uuid(mem, (struct tt_uuid *)mem->z);
+ return 0;
+}
+
static inline int
bytes_to_int(struct Mem *mem)
{
@@ -810,6 +857,22 @@ map_to_str0(struct Mem *mem)
return mem_copy_str0(mem, str);
}
+static inline int
+uuid_to_str0(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_UUID);
+ char buf[UUID_STR_LEN + 1];
+ tt_uuid_to_string(&mem->u.uuid, &buf[0]);
+ return mem_copy_str0(mem, &buf[0]);
+}
+
+static inline int
+uuid_to_bin(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_UUID);
+ return mem_copy_bin(mem, (char *)&mem->u.uuid, UUID_LEN);
+}
+
int
mem_to_int(struct Mem *mem)
{
@@ -889,6 +952,8 @@ mem_to_str0(struct Mem *mem)
return map_to_str0(mem);
case MEM_TYPE_ARRAY:
return array_to_str0(mem);
+ case MEM_TYPE_UUID:
+ return uuid_to_str0(mem);
default:
return -1;
}
@@ -914,6 +979,8 @@ mem_to_str(struct Mem *mem)
return map_to_str0(mem);
case MEM_TYPE_ARRAY:
return array_to_str0(mem);
+ case MEM_TYPE_UUID:
+ return uuid_to_str0(mem);
default:
return -1;
}
@@ -966,9 +1033,19 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
return str_to_bin(mem);
if (mem_is_bytes(mem))
return 0;
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_bin(mem);
return -1;
case FIELD_TYPE_NUMBER:
return mem_to_number(mem);
+ case FIELD_TYPE_UUID:
+ if (mem->type == MEM_TYPE_UUID)
+ return 0;
+ if (mem->type == MEM_TYPE_STR)
+ return str_to_uuid(mem);
+ if (mem->type == MEM_TYPE_BIN)
+ return bin_to_uuid(mem);
+ return -1;
case FIELD_TYPE_SCALAR:
if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
return -1;
@@ -996,6 +1073,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
case FIELD_TYPE_STRING:
if (mem->type == MEM_TYPE_STR)
return 0;
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_str0(mem);
return -1;
case FIELD_TYPE_DOUBLE:
if (mem->type == MEM_TYPE_DOUBLE)
@@ -1017,6 +1096,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
if ((mem->type & (MEM_TYPE_BIN | MEM_TYPE_MAP |
MEM_TYPE_ARRAY)) != 0)
return 0;
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_bin(mem);
return -1;
case FIELD_TYPE_NUMBER:
if (mem_is_num(mem))
@@ -1034,6 +1115,14 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
return -1;
return 0;
+ case FIELD_TYPE_UUID:
+ if (mem->type == MEM_TYPE_UUID)
+ return 0;
+ if (mem->type == MEM_TYPE_STR)
+ return str_to_uuid(mem);
+ if (mem->type == MEM_TYPE_BIN)
+ return bin_to_uuid(mem);
+ return -1;
case FIELD_TYPE_ANY:
return 0;
default:
@@ -1063,6 +1152,8 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
return int_to_str0(mem);
if (mem->type == MEM_TYPE_DOUBLE)
return double_to_str0(mem);
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_str0(mem);
return -1;
case FIELD_TYPE_DOUBLE:
if (mem->type == MEM_TYPE_DOUBLE)
@@ -1087,6 +1178,8 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
case FIELD_TYPE_VARBINARY:
if (mem->type == MEM_TYPE_BIN)
return 0;
+ if (mem->type == MEM_TYPE_UUID)
+ return uuid_to_bin(mem);
return -1;
case FIELD_TYPE_NUMBER:
if (mem_is_num(mem))
@@ -1106,6 +1199,14 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type)
if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
return -1;
return 0;
+ case FIELD_TYPE_UUID:
+ if (mem->type == MEM_TYPE_UUID)
+ return 0;
+ if (mem->type == MEM_TYPE_STR)
+ return str_to_uuid(mem);
+ if (mem->type == MEM_TYPE_BIN)
+ return bin_to_uuid(mem);
+ return -1;
default:
break;
}
@@ -1899,6 +2000,15 @@ mem_cmp_str(const struct Mem *left, const struct Mem *right, int *result,
return 0;
}
+int
+mem_cmp_uuid(const struct Mem *a, const struct Mem *b, int *result)
+{
+ if ((a->type & b->type & MEM_TYPE_UUID) == 0)
+ return -1;
+ *result = memcmp(&a->u.uuid, &b->u.uuid, UUID_LEN);
+ return 0;
+}
+
/*
* Both *pMem1 and *pMem2 contain string values. Compare the two values
* using the collation sequence pColl. As usual, return a negative , zero
@@ -1951,13 +2061,15 @@ mem_type_to_str(const struct Mem *p)
return "varbinary";
case MEM_TYPE_BOOL:
return "boolean";
+ case MEM_TYPE_UUID:
+ return "uuid";
default:
unreachable();
}
}
enum mp_type
-mem_mp_type(struct Mem *mem)
+mem_mp_type(const struct Mem *mem)
{
assert(mem->type < MEM_TYPE_INVALID);
switch (mem->type) {
@@ -1979,6 +2091,8 @@ mem_mp_type(struct Mem *mem)
return MP_BOOL;
case MEM_TYPE_DOUBLE:
return MP_DOUBLE;
+ case MEM_TYPE_UUID:
+ return MP_EXT;
default:
unreachable();
}
@@ -2144,6 +2258,9 @@ memTracePrint(Mem *p)
case MEM_TYPE_BOOL:
printf(" bool:%s", SQL_TOKEN_BOOLEAN(p->u.b));
return;
+ case MEM_TYPE_UUID:
+ printf(" uuid:%s", tt_uuid_str(&p->u.uuid));
+ return;
default: {
char zBuf[200];
sqlVdbeMemPrettyPrint(p, zBuf);
@@ -2360,6 +2477,14 @@ sqlMemCompare(const Mem * pMem1, const Mem * pMem2, const struct coll * pColl)
return -1;
}
+ if (((type1 | type2) & MEM_TYPE_UUID) != 0) {
+ if (mem_cmp_uuid(pMem1, pMem2, &res) == 0)
+ return res;
+ if (type1 != MEM_TYPE_UUID)
+ return +1;
+ return -1;
+ }
+
/* At least one of the two values is a number
*/
if (((type1 | type2) &
@@ -2565,13 +2690,30 @@ sqlVdbeCompareMsgpack(const char **key1,
break;
}
case MP_ARRAY:
- case MP_MAP:
- case MP_EXT:{
+ case MP_MAP: {
mem1.z = (char *)aKey1;
mp_next(&aKey1);
mem1.n = aKey1 - (char *)mem1.z;
goto do_blob;
}
+ case MP_EXT: {
+ int8_t type;
+ const char *buf = aKey1;
+ uint32_t len = mp_decode_extl(&aKey1, &type);
+ if (type == MP_UUID) {
+ assert(len == UUID_LEN);
+ mem1.type = MEM_TYPE_UUID;
+ aKey1 = buf;
+ if (mp_decode_uuid(&aKey1, &mem1.u.uuid) == NULL ||
+ mem_cmp_uuid(&mem1, pKey2, &rc) != 0)
+ rc = 1;
+ break;
+ }
+ aKey1 += len;
+ mem1.z = (char *)buf;
+ mem1.n = aKey1 - buf;
+ goto do_blob;
+ }
}
*key1 = aKey1;
return rc;
@@ -2625,9 +2767,25 @@ mem_from_mp_ephemeral(struct Mem *mem, const char *buf, uint32_t *len)
break;
}
case MP_EXT: {
- mem->z = (char *)buf;
- mp_next(&buf);
- mem->n = buf - mem->z;
+ int8_t type;
+ const char *svp = buf;
+ uint32_t size = mp_decode_extl(&buf, &type);
+ if (type == MP_UUID) {
+ assert(size == UUID_LEN);
+ buf = svp;
+ if (mp_decode_uuid(&buf, &mem->u.uuid) == NULL) {
+ diag_set(ClientError, ER_INVALID_MSGPACK,
+ "Invalid MP_UUID MsgPack format");
+ return -1;
+ }
+ mem->type = MEM_TYPE_UUID;
+ mem->flags = 0;
+ mem->field_type = FIELD_TYPE_UUID;
+ break;
+ }
+ buf += size;
+ mem->z = (char *)svp;
+ mem->n = buf - svp;
mem->type = MEM_TYPE_BIN;
mem->flags = MEM_Ephem;
mem->field_type = FIELD_TYPE_VARBINARY;
@@ -2764,6 +2922,9 @@ mpstream_encode_vdbe_mem(struct mpstream *stream, struct Mem *var)
case MEM_TYPE_BOOL:
mpstream_encode_bool(stream, var->u.b);
return;
+ case MEM_TYPE_UUID:
+ mpstream_encode_uuid(stream, &var->u.uuid);
+ return;
default:
unreachable();
}
@@ -2850,6 +3011,9 @@ port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat)
case MEM_TYPE_BOOL:
lua_pushboolean(L, mem->u.b);
break;
+ case MEM_TYPE_UUID:
+ *luaL_pushuuid(L) = mem->u.uuid;
+ break;
default:
unreachable();
}
@@ -2976,14 +3140,8 @@ port_lua_get_vdbemem(struct port *base, uint32_t *size)
uint32_t size;
uint32_t svp = region_used(&fiber()->gc);
if (field.ext_type == MP_UUID) {
- size = mp_sizeof_uuid();
- buf = region_alloc(&fiber()->gc, size);
- if (buf == NULL) {
- diag_set(OutOfMemory, size,
- "region_alloc", "buf");
- goto error;
- }
- mp_encode_uuid(buf, field.uuidval);
+ mem_set_uuid(&val[i], field.uuidval);
+ break;
} else {
size = mp_sizeof_decimal(field.decval);
buf = region_alloc(&fiber()->gc, size);
@@ -3086,7 +3244,22 @@ port_c_get_vdbemem(struct port *base, uint32_t *size)
break;
case MP_EXT:
str = data;
- mp_next(&data);
+ int8_t type;
+ len = mp_decode_extl(&data, &type);
+ if (type == MP_UUID) {
+ assert(len == UUID_LEN);
+ struct tt_uuid *uuid = &val[i].u.uuid;
+ data = str;
+ if (mp_decode_uuid(&data, uuid) == NULL) {
+ diag_set(ClientError,
+ ER_INVALID_MSGPACK, "Invalid "
+ "MP_UUID MsgPack format");
+ goto error;
+ }
+ val[i].type = MEM_TYPE_UUID;
+ break;
+ }
+ data += len;
if (mem_copy_bin(&val[i], str, data - str) != 0)
goto error;
break;
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index 15d97da0e..b3cd5c545 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -30,6 +30,7 @@
* SUCH DAMAGE.
*/
#include "box/field_def.h"
+#include "uuid/tt_uuid.h"
struct sql;
struct Vdbe;
@@ -47,10 +48,11 @@ enum mem_type {
MEM_TYPE_MAP = 1 << 6,
MEM_TYPE_BOOL = 1 << 7,
MEM_TYPE_DOUBLE = 1 << 8,
- MEM_TYPE_INVALID = 1 << 9,
- MEM_TYPE_FRAME = 1 << 10,
- MEM_TYPE_PTR = 1 << 11,
- MEM_TYPE_AGG = 1 << 12,
+ MEM_TYPE_UUID = 1 << 9,
+ MEM_TYPE_INVALID = 1 << 10,
+ MEM_TYPE_FRAME = 1 << 11,
+ MEM_TYPE_PTR = 1 << 12,
+ MEM_TYPE_AGG = 1 << 13,
};
/*
@@ -72,6 +74,7 @@ struct Mem {
*/
struct func *func;
struct VdbeFrame *pFrame; /* Used when flags==MEM_Frame */
+ struct tt_uuid uuid;
} u;
/** Type of the value this MEM contains. */
enum mem_type type;
@@ -255,6 +258,10 @@ mem_is_any_null(const struct Mem *mem1, const struct Mem *mem2)
return ((mem1->type| mem2->type) & MEM_TYPE_NULL) != 0;
}
+/** Check if MEM is compatible with field type. */
+bool
+mem_is_field_compatible(const struct Mem *mem, enum field_type type);
+
/**
* Return a string that represent content of MEM. String is either allocated
* using static_alloc() of just a static variable.
@@ -290,6 +297,10 @@ mem_set_bool(struct Mem *mem, bool value);
void
mem_set_double(struct Mem *mem, double value);
+/** Clear MEM and set it to UUID. */
+void
+mem_set_uuid(struct Mem *mem, const struct tt_uuid *uuid);
+
/** Clear MEM and set it to STRING. The string belongs to another object. */
void
mem_set_str_ephemeral(struct Mem *mem, char *value, uint32_t len);
@@ -677,6 +688,14 @@ mem_cmp_str(const struct Mem *left, const struct Mem *right, int *result,
int
mem_cmp_num(const struct Mem *a, const struct Mem *b, int *result);
+/**
+ * Compare two MEMs and return the result of comparison. MEMs should be of
+ * UUID type or their values are converted to UUID according to
+ * implicit cast rules. Original MEMs are not changed.
+ */
+int
+mem_cmp_uuid(const struct Mem *left, const struct Mem *right, int *result);
+
/**
* Convert the given MEM to INTEGER. This function and the function below define
* the rules that are used to convert values of all other types to INTEGER. In
@@ -898,7 +917,7 @@ mem_type_to_str(const struct Mem *p);
* transparent memory cell.
*/
enum mp_type
-mem_mp_type(struct Mem *mem);
+mem_mp_type(const struct Mem *mem);
enum mp_type
sql_value_type(struct Mem *);
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index abc363951..4c9cf475e 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1834,6 +1834,7 @@ typedef(A) ::= SCALAR . { A.type = FIELD_TYPE_SCALAR; }
typedef(A) ::= BOOL . { A.type = FIELD_TYPE_BOOLEAN; }
typedef(A) ::= BOOLEAN . { A.type = FIELD_TYPE_BOOLEAN; }
typedef(A) ::= VARBINARY . { A.type = FIELD_TYPE_VARBINARY; }
+typedef(A) ::= UUID . { A.type = FIELD_TYPE_UUID; }
/**
* Time-like types are temporary disabled, until they are
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 12ec703a2..32d02d96e 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1322,10 +1322,10 @@ case OP_FunctionByName: {
region_truncate(region, region_svp);
if (mem == NULL)
goto abort_due_to_error;
- enum mp_type type = sql_value_type((sql_value *)pOut);
- if (!field_mp_plain_type_is_compatible(returns, type, true)) {
+ if (!mem_is_field_compatible(pOut, returns)) {
diag_set(ClientError, ER_FUNC_INVALID_RETURN_TYPE, pOp->p4.z,
- field_type_strs[returns], mp_type_strs[type]);
+ field_type_strs[returns],
+ mp_type_strs[mem_mp_type(pOut)]);
goto abort_due_to_error;
}
@@ -1634,6 +1634,15 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */
"boolean");
goto abort_due_to_error;
}
+ } else if (((pIn3->type | pIn1->type) & MEM_TYPE_UUID) != 0) {
+ if (mem_cmp_uuid(pIn3, pIn1, &res) != 0) {
+ char *str = pIn3->type != MEM_TYPE_UUID ?
+ mem_type_to_str(pIn3) :
+ mem_type_to_str(pIn1);
+ diag_set(ClientError, ER_SQL_TYPE_MISMATCH, str,
+ "uuid");
+ goto abort_due_to_error;
+ }
} else if (mem_is_bin(pIn3) || mem_is_bin(pIn1)) {
if (mem_cmp_bin(pIn3, pIn1, &res) != 0) {
char *str = !mem_is_bin(pIn3) ?
diff --git a/test/sql-tap/CMakeLists.txt b/test/sql-tap/CMakeLists.txt
index bf0c3a11d..bd2b9f33f 100644
--- a/test/sql-tap/CMakeLists.txt
+++ b/test/sql-tap/CMakeLists.txt
@@ -1,3 +1,4 @@
include_directories(${MSGPUCK_INCLUDE_DIRS})
build_module(gh-5938-wrong-string-length gh-5938-wrong-string-length.c)
build_module(gh-6024-funcs-return-bin gh-6024-funcs-return-bin.c)
+build_module(sql_uuid sql_uuid.c)
diff --git a/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua b/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua
index 60978c2b5..7dcebe5d3 100755
--- a/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua
+++ b/test/sql-tap/gh-5913-segfault-on-select-uuid.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(6)
+test:plan(4)
local uuid = require("uuid").fromstr("11111111-1111-1111-1111-111111111111")
local decimal = require("decimal").new(111.111)
@@ -13,16 +13,16 @@ box.space.T:insert({1, uuid, decimal})
--
-- Make sure that there is no segmentation fault on select from field that
--- contains UUID or DECIMAL. Currently SQL does not support UUID and DECIMAL,
--- so they treated as VARBINARY.
+-- contains UUID or DECIMAL. Currently SQL does not support DECIMAL, so it is
+-- treated as VARBINARY.
--
test:do_execsql_test(
"gh-5913-1",
[[
SELECT i, u, d FROM t;
- SELECT i from t;
+ SELECT i, u from t;
]], {
- 1
+ 1, uuid
})
box.schema.create_space('T1')
@@ -32,19 +32,11 @@ box.space.T1:format({{name = "I", type = "integer"},
box.space.T1:create_index("primary")
--
--- Since SQL does not support UUID and DECIMAL and they treated as VARBINARY,
--- they cannot be inserted from SQL.
+-- Since SQL does not support DECIMAL and it is treated as VARBINARY, it cannot
+-- be inserted from SQL.
--
test:do_catchsql_test(
"gh-5913-2",
- [[
- INSERT INTO t1 SELECT i, u, NULL FROM t;
- ]], {
- 1, "Type mismatch: can not convert varbinary to uuid"
- })
-
-test:do_catchsql_test(
- "gh-5913-3",
[[
INSERT INTO t1 SELECT i, NULL, d FROM t;
]], {
@@ -52,11 +44,11 @@ test:do_catchsql_test(
})
--
--- Still, if UUID or DECIMAL fields does not selected directly, insert is
--- working properly.
+-- Still, if DECIMAL fields does not selected directly, insert is working
+-- properly in case the space which receives these values is empty.
--
test:do_execsql_test(
- "gh-5913-4",
+ "gh-5913-3",
[[
INSERT INTO t1 SELECT * FROM t;
SELECT count() FROM t1;
@@ -77,19 +69,11 @@ box.space.TD:create_index("primary")
box.space.TD:insert({1, decimal})
--
--- Update of UUID or VARBINARY also does not lead to segfault, however throws an
--- error since after changing value cannot be inserted into the field from SQL.
+-- Update of DECIMAL also does not lead to segfault, however throws an error
+-- since after changing value cannot be inserted into the field from SQL.
--
test:do_catchsql_test(
- "gh-5913-5",
- [[
- UPDATE tu SET u = u;
- ]], {
- 1, "Type mismatch: can not convert varbinary to uuid"
- })
-
-test:do_catchsql_test(
- "gh-5913-6",
+ "gh-5913-4",
[[
UPDATE td SET d = d;
]], {
diff --git a/test/sql-tap/gh-6024-funcs-return-bin.test.lua b/test/sql-tap/gh-6024-funcs-return-bin.test.lua
index bf406ab7b..68d8a9a86 100755
--- a/test/sql-tap/gh-6024-funcs-return-bin.test.lua
+++ b/test/sql-tap/gh-6024-funcs-return-bin.test.lua
@@ -23,7 +23,7 @@ test:do_execsql_test(
box.schema.func.create("gh-6024-funcs-return-bin.ret_uuid", {
language = "C",
param_list = {},
- returns = "varbinary",
+ returns = "uuid",
exports = {"SQL"},
})
@@ -32,7 +32,7 @@ test:do_execsql_test(
[[
SELECT typeof("gh-6024-funcs-return-bin.ret_uuid"());
]], {
- "varbinary"
+ "uuid"
})
box.schema.func.create("gh-6024-funcs-return-bin.ret_decimal", {
@@ -53,7 +53,7 @@ test:do_execsql_test(
box.schema.func.create("get_uuid", {
language = "LUA",
param_list = {},
- returns = "varbinary",
+ returns = "uuid",
body = "function(x) return require('uuid').fromstr('11111111-1111-1111-1111-111111111111') end",
exports = {"SQL"},
})
@@ -63,7 +63,7 @@ test:do_execsql_test(
[[
SELECT typeof("get_uuid"()), "get_uuid"() == "gh-6024-funcs-return-bin.ret_uuid"();
]], {
- "varbinary", true
+ "uuid", true
})
box.schema.func.create("get_decimal", {
diff --git a/test/sql-tap/sql_uuid.c b/test/sql-tap/sql_uuid.c
new file mode 100644
index 000000000..4032bba5e
--- /dev/null
+++ b/test/sql-tap/sql_uuid.c
@@ -0,0 +1,46 @@
+#include "msgpuck.h"
+#include "module.h"
+#include "uuid/mp_uuid.h"
+#include "mp_extension_types.h"
+
+enum {
+ BUF_SIZE = 512,
+};
+
+int
+is_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
+{
+ (void)args_end;
+ uint32_t arg_count = mp_decode_array(&args);
+ if (arg_count != 1) {
+ return box_error_set(__FILE__, __LINE__, ER_PROC_C,
+ "invalid argument count");
+ }
+ bool is_uuid;
+ if (mp_typeof(*args) == MP_EXT) {
+ const char *str = args;
+ int8_t type;
+ mp_decode_extl(&str, &type);
+ is_uuid = type == MP_UUID;
+ } else {
+ is_uuid = false;
+ }
+
+ char res[BUF_SIZE];
+ char *end = mp_encode_bool(res, is_uuid);
+ box_return_mp(ctx, res, end);
+ return 0;
+}
+
+int
+ret_uuid(box_function_ctx_t *ctx, const char *args, const char *args_end)
+{
+ (void)args;
+ (void)args_end;
+ struct tt_uuid uuid;
+ memset(&uuid, 0x11, sizeof(uuid));
+ char res[BUF_SIZE];
+ char *end = mp_encode_uuid(res, &uuid);
+ box_return_mp(ctx, res, end);
+ return 0;
+}
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
new file mode 100755
index 000000000..4287f0921
--- /dev/null
+++ b/test/sql-tap/uuid.test.lua
@@ -0,0 +1,1294 @@
+#!/usr/bin/env tarantool
+local build_path = os.getenv("BUILDDIR")
+package.cpath = build_path..'/test/sql-tap/?.so;'..build_path..'/test/sql-tap/?.dylib;'..package.cpath
+
+local test = require("sqltester")
+test:plan(139)
+
+local uuid = require("uuid")
+local uuid1 = uuid.fromstr("11111111-1111-1111-1111-111111111111")
+local uuid2 = uuid.fromstr("22222222-1111-1111-1111-111111111111")
+local uuid3 = uuid.fromstr("11111111-3333-1111-1111-111111111111")
+
+-- Check that it is possible to create spaces with UUID field.
+test:do_execsql_test(
+ "uuid-1",
+ [[
+ CREATE TABLE t1 (i INT PRIMARY KEY, u UUID);
+ CREATE TABLE t2 (u UUID PRIMARY KEY);
+ ]], {
+ })
+
+box.space.T1:insert({1, uuid1})
+box.space.T1:insert({2, uuid2})
+box.space.T1:insert({3, uuid3})
+box.space.T1:insert({4, uuid1})
+box.space.T1:insert({5, uuid1})
+box.space.T1:insert({6, uuid2})
+box.space.T2:insert({uuid1})
+box.space.T2:insert({uuid2})
+box.space.T2:insert({uuid3})
+
+-- Check that SELECT can work with UUID.
+test:do_execsql_test(
+ "uuid-2.1.1",
+ [[
+ SELECT * FROM t1;
+ ]], {
+ 1, uuid1, 2, uuid2, 3, uuid3, 4, uuid1, 5, uuid1, 6, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-2.1.2",
+ [[
+ SELECT * FROM t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+-- Check that ORDER BY can work with UUID.
+test:do_execsql_test(
+ "uuid-2.2.1",
+ [[
+ SELECT * FROM t1 ORDER BY u;
+ ]], {
+ 1, uuid1, 4, uuid1, 5, uuid1, 3, uuid3, 2, uuid2, 6, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-2.2.2",
+ [[
+ SELECT * FROM t1 ORDER BY u DESC;
+ ]], {
+ 2, uuid2, 6, uuid2, 3, uuid3, 1, uuid1, 4, uuid1, 5, uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-2.2.3",
+ [[
+ SELECT * FROM t2 ORDER BY u;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-2.2.4",
+ [[
+ SELECT * FROM t2 ORDER BY u DESC;
+ ]], {
+ uuid2, uuid3, uuid1
+ })
+
+-- Check that GROUP BY can work with UUID.
+test:do_execsql_test(
+ "uuid-2.3.1",
+ [[
+ SELECT count(*), u FROM t1 GROUP BY u;
+ ]], {
+ 3, uuid1, 1, uuid3, 2, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-2.3.2",
+ [[
+ SELECT count(*), u FROM t2 GROUP BY u;
+ ]], {
+ 1, uuid1, 1, uuid3, 1, uuid2
+ })
+
+-- Check that subselects can work with UUID.
+test:do_execsql_test(
+ "uuid-2.4",
+ [[
+ SELECT * FROM (SELECT * FROM (SELECT * FROM t2 LIMIT 2) LIMIT 2 OFFSET 1);
+ ]], {
+ uuid3
+ })
+
+-- Check that DISTINCT can work with UUID.
+test:do_execsql_test(
+ "uuid-2.5",
+ [[
+ SELECT DISTINCT u FROM t1;
+ ]], {
+ uuid1, uuid2, uuid3
+ })
+
+-- Check that VIEW can work with UUID.
+test:do_execsql_test(
+ "uuid-2.6",
+ [[
+ CREATE VIEW v AS SELECT u FROM t1;
+ SELECT * FROM v;
+ ]], {
+ uuid1, uuid2, uuid3, uuid1, uuid1, uuid2
+ })
+
+-- Check that LIMIT does not accept UUID as argument.
+test:do_catchsql_test(
+ "uuid-3.1",
+ [[
+ SELECT 1 LIMIT (SELECT u FROM t1 LIMIT 1);
+ ]], {
+ 1, "Failed to execute SQL statement: Only positive integers are allowed in the LIMIT clause"
+ })
+
+-- Check that OFFSET does not accept UUID as argument.
+test:do_catchsql_test(
+ "uuid-3.2",
+ [[
+ SELECT 1 LIMIT 1 OFFSET (SELECT u FROM t1 LIMIT 1);
+ ]], {
+ 1, "Failed to execute SQL statement: Only positive integers are allowed in the OFFSET clause"
+ })
+
+-- Check that ephemeral space can work with UUID.
+test:do_execsql_test(
+ "uuid-4",
+ [[
+ EXPLAIN SELECT * from (VALUES(1)), t2;
+ ]], {
+ "/OpenTEphemeral/"
+ })
+
+test:execsql([[
+ CREATE TABLE t5f (u UUID PRIMARY KEY, f UUID REFERENCES t5f(u));
+ CREATE TABLE t5c (i INT PRIMARY KEY, f UUID, CONSTRAINT ck CHECK(CAST(f AS STRING) != '11111111-1111-1111-1111-111111111111'));
+ CREATE TABLE t5u (i INT PRIMARY KEY, f UUID UNIQUE);
+]])
+
+-- Check that FOREIGN KEY constraint can work with UUID.
+test:do_catchsql_test(
+ "uuid-5.1.1",
+ [[
+ INSERT INTO t5f SELECT (SELECT u from t2 LIMIT 1 OFFSET 1), (SELECT u from t2 LIMIT 1);
+ ]], {
+ 1, "Failed to execute SQL statement: FOREIGN KEY constraint failed"
+ })
+
+test:do_execsql_test(
+ "uuid-5.1.2",
+ [[
+ INSERT INTO t5f SELECT u, u from t2 LIMIT 1;
+ SELECT * from t5f;
+ ]], {
+ uuid1, uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-5.1.3",
+ [[
+ INSERT INTO t5f SELECT (SELECT u from t2 LIMIT 1 OFFSET 1), (SELECT u from t2 LIMIT 1);
+ SELECT * from t5f;
+ ]], {
+ uuid1, uuid1, uuid3, uuid1
+ })
+
+-- Check that CHECK constraint can work with UUID.
+test:do_catchsql_test(
+ "uuid-5.2.1",
+ [[
+ INSERT INTO t5c SELECT 1, u FROM t2 LIMIT 1;
+ ]], {
+ 1, "Check constraint failed 'CK': CAST(f AS STRING) != '11111111-1111-1111-1111-111111111111'"
+ })
+
+test:do_execsql_test(
+ "uuid-5.2.2",
+ [[
+ INSERT INTO t5c SELECT 2, u FROM t2 LIMIT 1 OFFSET 1;
+ SELECT * from t5c;
+ ]], {
+ 2, uuid3
+ })
+
+-- Check that UNIQUE constraint can work with UUID.
+test:do_execsql_test(
+ "uuid-5.3.1",
+ [[
+ INSERT INTO t5u SELECT 1, u FROM t2 LIMIT 1;
+ SELECT * from t5u;
+ ]], {
+ 1, uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-5.3.2",
+ [[
+ INSERT INTO t5u SELECT 2, u FROM t2 LIMIT 1;
+ ]], {
+ 1, 'Duplicate key exists in unique index "unique_unnamed_T5U_2" '..
+ 'in space "T5U" with old tuple - '..
+ '[1, 11111111-1111-1111-1111-111111111111] and new tuple - '..
+ '[2, 11111111-1111-1111-1111-111111111111]'
+ })
+
+-- Check that built-in functions work with UUIDs as intended.
+test:do_catchsql_test(
+ "uuid-6.1.1",
+ [[
+ SELECT ABS(u) from t2;
+ ]], {
+ 1, "Inconsistent types: expected number got uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.2",
+ [[
+ SELECT AVG(u) from t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.3",
+ [[
+ SELECT CHAR(u) from t2;
+ ]], {
+ "\0", "\0", "\0"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.4",
+ [[
+ SELECT CHARACTER_LENGTH(u) from t2;
+ ]], {
+ 36, 36, 36
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.5",
+ [[
+ SELECT CHAR_LENGTH(u) from t2;
+ ]], {
+ 36, 36, 36
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.6",
+ [[
+ SELECT COALESCE(NULL, u, NULL, NULL) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.7",
+ [[
+ SELECT COUNT(u) from t2;
+ ]], {
+ 3
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.8",
+ [[
+ SELECT GREATEST((SELECT u FROM t2 LIMIT 1), (SELECT u FROM t2 LIMIT 1 OFFSET 1));
+ ]], {
+ uuid3
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.9",
+ [[
+ SELECT GROUP_CONCAT(u) from t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111,"..
+ "11111111-3333-1111-1111-111111111111,"..
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.10",
+ [[
+ SELECT HEX(u) from t2;
+ ]], {
+ "11111111111111111111111111111111",
+ "11111111333311111111111111111111",
+ "22222222111111111111111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.11",
+ [[
+ SELECT IFNULL(u, NULL) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.12",
+ [[
+ SELECT LEAST((SELECT u FROM t2 LIMIT 1), (SELECT u FROM t2 LIMIT 1 OFFSET 1));
+ ]], {
+ uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.13",
+ [[
+ SELECT LENGTH(u) from t2;
+ ]], {
+ 36, 36, 36
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.14",
+ [[
+ SELECT u LIKE 'a' from t2;
+ ]], {
+ 1, "Inconsistent types: expected text got uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.15",
+ [[
+ SELECT LIKELIHOOD(u, 0.5) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.16",
+ [[
+ SELECT LIKELY(u) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.17",
+ [[
+ SELECT LOWER(u) from t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111",
+ "11111111-3333-1111-1111-111111111111",
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.18",
+ [[
+ SELECT MAX(u) from t2;
+ ]], {
+ uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.19",
+ [[
+ SELECT MIN(u) from t2;
+ ]], {
+ uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.20",
+ [[
+ SELECT NULLIF(u, 1) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.21",
+ [[
+ SELECT POSITION(u, '1') from t2;
+ ]], {
+ 1, "Inconsistent types: expected text or varbinary got uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.22",
+ [[
+ SELECT RANDOMBLOB(u) from t2;
+ ]], {
+ "", "", ""
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.23",
+ [[
+ SELECT REPLACE(u, '1', '2') from t2;
+ ]], {
+ "22222222-2222-2222-2222-222222222222",
+ "22222222-3333-2222-2222-222222222222",
+ "22222222-2222-2222-2222-222222222222"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.24",
+ [[
+ SELECT ROUND(u) from t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.25",
+ [[
+ SELECT SOUNDEX(u) from t2;
+ ]], {
+ "?000", "?000", "?000"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.26",
+ [[
+ SELECT SUBSTR(u, 3, 3) from t2;
+ ]], {
+ "111", "111", "222"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.27",
+ [[
+ SELECT SUM(u) from t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.28",
+ [[
+ SELECT TOTAL(u) from t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.29",
+ [[
+ SELECT TRIM(u) from t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111",
+ "11111111-3333-1111-1111-111111111111",
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.30",
+ [[
+ SELECT TYPEOF(u) from t2;
+ ]], {
+ "uuid", "uuid", "uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.31",
+ [[
+ SELECT UNICODE(u) from t2;
+ ]], {
+ 49, 49, 50
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.32",
+ [[
+ SELECT UNLIKELY(u) from t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-6.1.33",
+ [[
+ SELECT UPPER(u) from t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111",
+ "11111111-3333-1111-1111-111111111111",
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_catchsql_test(
+ "uuid-6.1.33",
+ [[
+ SELECT u || u from t2;
+ ]], {
+ 1, "Inconsistent types: expected text or varbinary got uuid"
+ })
+
+local func = {language = 'Lua', body = 'function(x) return type(x) end',
+ returns = 'string', param_list = {'any'}, exports = {'SQL'}}
+box.schema.func.create('RETURN_TYPE', func);
+
+-- Check that Lua user-defined functions can accept UUID.
+test:do_execsql_test(
+ "uuid-6.2",
+ [[
+ SELECT RETURN_TYPE(u) FROM t2;
+ ]], {
+ "cdata", "cdata", "cdata"
+ })
+
+func = {language = 'Lua', returns = 'uuid', param_list = {}, exports = {'SQL'},
+ body = 'function(x) return require("uuid").fromstr("11111111-1111-1111-1111-111111111111") end'}
+box.schema.func.create('GET_UUID', func);
+
+-- Check that Lua user-defined functions can return UUID.
+test:do_execsql_test(
+ "uuid-6.3",
+ [[
+ SELECT GET_UUID();
+ ]], {
+ uuid1
+ })
+
+func = {language = 'C', returns = 'boolean', param_list = {'any'}, exports = {'SQL'}}
+box.schema.func.create("sql_uuid.is_uuid", func)
+
+-- Check that C user-defined functions can accept UUID.
+test:do_execsql_test(
+ "uuid-6.4",
+ [[
+ SELECT "sql_uuid.is_uuid"(i), "sql_uuid.is_uuid"(u) FROM t1 LIMIT 1;
+ ]], {
+ false, true
+ })
+
+func = {language = 'C', returns = 'uuid', param_list = {}, exports = {'SQL'}}
+box.schema.func.create("sql_uuid.ret_uuid", func)
+
+-- Check that C user-defined functions can return UUID.
+test:do_execsql_test(
+ "uuid-6.5",
+ [[
+ SELECT "sql_uuid.ret_uuid"();
+ ]], {
+ uuid1
+ })
+
+-- Check that explicit cast from UUID to another types works as intended.
+test:do_catchsql_test(
+ "uuid-7.1.1",
+ [[
+ SELECT cast(u AS UNSIGNED) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to unsigned"
+ })
+
+test:do_execsql_test(
+ "uuid-7.1.2",
+ [[
+ SELECT cast(u AS STRING) FROM t2;
+ ]], {
+ "11111111-1111-1111-1111-111111111111",
+ "11111111-3333-1111-1111-111111111111",
+ "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.1.3",
+ [[
+ SELECT cast(u AS NUMBER) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.1.4",
+ [[
+ SELECT cast(u AS DOUBLE) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to double"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.1.5",
+ [[
+ SELECT cast(u AS INTEGER) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.1.6",
+ [[
+ SELECT cast(u AS BOOLEAN) FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_execsql_test(
+ "uuid-7.1.7",
+ [[
+ SELECT hex(cast(u AS VARBINARY)) FROM t2;
+ ]], {
+ "11111111111111111111111111111111",
+ "11111111333311111111111111111111",
+ "22222222111111111111111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-7.1.8",
+ [[
+ SELECT cast(u AS SCALAR) FROM t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-7.1.9",
+ [[
+ SELECT cast(u AS UUID) FROM t2;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+-- Check that explicit cast from another types to UUID works as intended.
+test:do_catchsql_test(
+ "uuid-7.2.1",
+ [[
+ SELECT cast(1 AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert 1 to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-7.2.2",
+ [[
+ SELECT cast('11111111-1111-1111-1111-111111111111' AS UUID);
+ ]], {
+ uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.3",
+ [[
+ SELECT cast('1' AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert 1 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.4",
+ [[
+ SELECT cast(1.5 AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert 1.5 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.5",
+ [[
+ SELECT cast(-1 AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert -1 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.6",
+ [[
+ SELECT cast(true AS UUID);
+ ]], {
+ 1, "Type mismatch: can not convert TRUE to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-7.2.7",
+ [[
+ SELECT cast(x'11111111111111111111111111111111' AS UUID);
+ ]], {
+ uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-7.2.8",
+ [[
+ SELECT cast(randomblob(10) as UUID) FROM t2 LIMIT 1;
+ ]], {
+ 1, "Type mismatch: can not convert varbinary to uuid"
+ })
+
+test:execsql([[
+ CREATE TABLE tu (id INT PRIMARY KEY AUTOINCREMENT, u UNSIGNED);
+ CREATE TABLE ts (id INT PRIMARY KEY AUTOINCREMENT, s STRING);
+ CREATE TABLE tn (id INT PRIMARY KEY AUTOINCREMENT, n NUMBER);
+ CREATE TABLE td (id INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);
+ CREATE TABLE ti (id INT PRIMARY KEY AUTOINCREMENT, i INTEGER);
+ CREATE TABLE tb (id INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);
+ CREATE TABLE tv (id INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);
+ CREATE TABLE tsc (id INT PRIMARY KEY AUTOINCREMENT, sc SCALAR);
+ CREATE TABLE tuu (id INT PRIMARY KEY AUTOINCREMENT, uu UUID);
+ CREATE TABLE tsu (s STRING PRIMARY KEY, u UUID);
+]])
+
+-- Check that implcit cast from UUID to another types works as intended.
+test:do_catchsql_test(
+ "uuid-8.1.1",
+ [[
+ INSERT INTO tu(u) SELECT u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to unsigned"
+ })
+
+test:do_execsql_test(
+ "uuid-8.1.2",
+ [[
+ INSERT INTO ts(s) SELECT u FROM t2;
+ SELECT * FROM ts;
+ ]], {
+ 1, "11111111-1111-1111-1111-111111111111",
+ 2, "11111111-3333-1111-1111-111111111111",
+ 3, "22222222-1111-1111-1111-111111111111"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.1.3",
+ [[
+ INSERT INTO tn(n) SELECT u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to number"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.1.4",
+ [[
+ INSERT INTO td(d) SELECT u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to double"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.1.5",
+ [[
+ INSERT INTO ti(i) SELECT u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.1.6",
+ [[
+ INSERT INTO tb(b) SELECT u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_execsql_test(
+ "uuid-8.1.7",
+ [[
+ INSERT INTO tv(v) SELECT u FROM t2;
+ SELECT id, hex(v) FROM tv;
+ ]], {
+ 1, "11111111111111111111111111111111",
+ 2, "11111111333311111111111111111111",
+ 3, "22222222111111111111111111111111"
+ })
+
+test:do_execsql_test(
+ "uuid-8.1.8",
+ [[
+ INSERT INTO tsc(sc) SELECT u FROM t2;
+ SELECT * FROM tsc;
+ ]], {
+ 1, uuid1, 2, uuid3, 3, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-8.1.9",
+ [[
+ INSERT INTO tuu(uu) SELECT u FROM t2;
+ SELECT * FROM tuu;
+ ]], {
+ 1, uuid1, 2, uuid3, 3, uuid2
+ })
+
+-- Check that implicit cast from another types to UUID works as intended.
+test:do_catchsql_test(
+ "uuid-8.2.1",
+ [[
+ INSERT INTO tsu VALUES ('1_unsigned', 1);
+ ]], {
+ 1, "Type mismatch: can not convert 1 to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-8.2.2",
+ [[
+ INSERT INTO tsu VALUES ('2_string_right', '11111111-1111-1111-1111-111111111111');
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ '2_string_right', uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.3",
+ [[
+ INSERT INTO tsu VALUES ('3_string_wrong', '1');
+ ]], {
+ 1, "Type mismatch: can not convert 1 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.4",
+ [[
+ INSERT INTO tsu VALUES ('4_double', 1.5);
+ ]], {
+ 1, "Type mismatch: can not convert 1.5 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.5",
+ [[
+ INSERT INTO tsu VALUES ('5_integer', -1);
+ ]], {
+ 1, "Type mismatch: can not convert -1 to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.6",
+ [[
+ INSERT INTO tsu VALUES ('6_boolean', true);
+ ]], {
+ 1, "Type mismatch: can not convert TRUE to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-8.2.7",
+ [[
+ INSERT INTO tsu SELECT '7_varbinary', x'11111111111111111111111111111111' FROM t2 LIMIT 1;
+ SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
+ ]], {
+ '7_varbinary', uuid1
+ })
+
+test:do_catchsql_test(
+ "uuid-8.2.8",
+ [[
+ INSERT INTO tsu VALUES ('8_varbinary', randomblob(10));
+ ]], {
+ 1, "Type mismatch: can not convert varbinary to uuid"
+ })
+
+test:execsql([[
+ CREATE TABLE t9 (i INT PRIMARY KEY AUTOINCREMENT, u UUID);
+ CREATE TABLE t9t (u UUID PRIMARY KEY);
+ CREATE TRIGGER t AFTER INSERT ON t9 FOR EACH ROW BEGIN INSERT INTO t9t SELECT new.u; END;
+]])
+
+-- Check that trigger can work with UUID.
+test:do_execsql_test(
+ "uuid-9",
+ [[
+ INSERT INTO t9(u) SELECT * FROM t2;
+ SELECT * FROM t9t;
+ ]], {
+ uuid1, uuid3, uuid2
+ })
+
+test:execsql([[
+ CREATE TABLE t10 (i INT PRIMARY KEY AUTOINCREMENT, u UUID DEFAULT '11111111-1111-1111-1111-111111111111');
+]])
+
+-- Check that INSERT into UUID field works.
+test:do_execsql_test(
+ "uuid-10.1.1",
+ [[
+ INSERT INTO t10 VALUES (1, '22222222-1111-1111-1111-111111111111');
+ SELECT * FROM t10 WHERE i = 1;
+ ]], {
+ 1, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-10.1.2",
+ [[
+ INSERT INTO t10 VALUES (2, x'22222222111111111111111111111111');
+ SELECT * FROM t10 WHERE i = 2;
+ ]], {
+ 2, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-10.1.3",
+ [[
+ INSERT INTO t10(i) VALUES (3);
+ SELECT * FROM t10 WHERE i = 3;
+ ]], {
+ 3, uuid1
+ })
+
+test:do_execsql_test(
+ "uuid-10.1.4",
+ [[
+ INSERT INTO t10 VALUES (4, NULL);
+ SELECT * FROM t10 WHERE i = 4;
+ ]], {
+ 4, ''
+ })
+
+-- Check that UPDATE of UUID field works.
+test:do_execsql_test(
+ "uuid-10.2.1",
+ [[
+ UPDATE t10 SET u = '11111111-3333-1111-1111-111111111111' WHERE i = 1;
+ SELECT * FROM t10 WHERE i = 1;
+ ]], {
+ 1, uuid3
+ })
+
+test:do_execsql_test(
+ "uuid-10.2.2",
+ [[
+ UPDATE t10 SET u = x'11111111333311111111111111111111' WHERE i = 2;
+ SELECT * FROM t10 WHERE i = 2;
+ ]], {
+ 2, uuid3
+ })
+
+-- Check that JOIN by UUID field works.
+test:do_execsql_test(
+ "uuid-11.1",
+ [[
+ SELECT * FROM t1 JOIN t2 on t1.u = t2.u;
+ ]], {
+ 1, uuid1, uuid1, 2, uuid2, uuid2, 3, uuid3, uuid3,
+ 4, uuid1, uuid1, 5, uuid1, uuid1, 6, uuid2, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-11.2",
+ [[
+ SELECT * FROM t1 LEFT JOIN t2 on t1.u = t2.u;
+ ]], {
+ 1, uuid1, uuid1, 2, uuid2, uuid2, 3, uuid3, uuid3,
+ 4, uuid1, uuid1, 5, uuid1, uuid1, 6, uuid2, uuid2
+ })
+
+test:do_execsql_test(
+ "uuid-11.3",
+ [[
+ SELECT * FROM t1 INNER JOIN t2 on t1.u = t2.u;
+ ]], {
+ 1, uuid1, uuid1, 2, uuid2, uuid2, 3, uuid3, uuid3,
+ 4, uuid1, uuid1, 5, uuid1, uuid1, 6, uuid2, uuid2
+ })
+
+-- Check that arithmetic operations work with UUIDs as intended.
+test:do_catchsql_test(
+ "uuid-12.1.1",
+ [[
+ SELECT -u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.2",
+ [[
+ SELECT u + 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.3",
+ [[
+ SELECT u - 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.4",
+ [[
+ SELECT u * 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.5",
+ [[
+ SELECT u / 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.1.6",
+ [[
+ SELECT u % 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to numeric"
+ })
+
+-- Check that bitwise operations work with UUIDs as intended.
+test:do_catchsql_test(
+ "uuid-12.2.1",
+ [[
+ SELECT ~u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.2.2",
+ [[
+ SELECT u >> 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.2.3",
+ [[
+ SELECT u << 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.2.4",
+ [[
+ SELECT u | 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.2.5",
+ [[
+ SELECT u & 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to integer"
+ })
+
+-- Check that logical operations work with UUIDs as intended.
+test:do_catchsql_test(
+ "uuid-12.3.1",
+ [[
+ SELECT NOT u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.3.2",
+ [[
+ SELECT u AND true FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.3.3",
+ [[
+ SELECT u OR true FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.3.4",
+ [[
+ SELECT true AND u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+test:do_catchsql_test(
+ "uuid-12.3.5",
+ [[
+ SELECT true OR u FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert 11111111-1111-1111-1111-111111111111 to boolean"
+ })
+
+-- Check that comparison with UUID works as intended.
+test:do_catchsql_test(
+ "uuid-13.1.1",
+ [[
+ SELECT u > 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert unsigned to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-13.1.2",
+ [[
+ SELECT u > CAST('11111111-1111-1111-1111-111111111111' AS UUID) FROM t2;
+ ]], {
+ false, true, true
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.3",
+ [[
+ SELECT u > '1' FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert text to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.4",
+ [[
+ SELECT u > 1.5 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert real to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.5",
+ [[
+ SELECT u > -1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert integer to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.6",
+ [[
+ SELECT u > true FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert uuid to boolean"
+ })
+
+test:do_execsql_test(
+ "uuid-13.1.7",
+ [[
+ SELECT u > CAST(x'11111111111111111111111111111111' AS UUID) FROM t2;
+ ]], {
+ false, true, true
+ })
+
+test:do_catchsql_test(
+ "uuid-13.1.8",
+ [[
+ SELECT u > x'31' FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert varbinary to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.1",
+ [[
+ SELECT u = 1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert unsigned to uuid"
+ })
+
+test:do_execsql_test(
+ "uuid-13.2.2",
+ [[
+ SELECT u = CAST('11111111-1111-1111-1111-111111111111' AS UUID) FROM t2;
+ ]], {
+ true, false, false
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.3",
+ [[
+ SELECT u = '1' FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert text to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.4",
+ [[
+ SELECT u = 1.5 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert real to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.5",
+ [[
+ SELECT u = -1 FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert integer to uuid"
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.6",
+ [[
+ SELECT u = true FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert uuid to boolean"
+ })
+
+test:do_execsql_test(
+ "uuid-13.2.7",
+ [[
+ SELECT u = CAST(x'11111111111111111111111111111111' AS UUID) FROM t2;
+ ]], {
+ true, false, false
+ })
+
+test:do_catchsql_test(
+ "uuid-13.2.8",
+ [[
+ SELECT u = x'31' FROM t2;
+ ]], {
+ 1, "Type mismatch: can not convert varbinary to uuid"
+ })
+
+test:execsql([[
+ CREATE TABLE t14 (s SCALAR PRIMARY KEY);
+]])
+
+-- Check that SCALAR field can contain UUID and use it in index.
+test:do_execsql_test(
+ "uuid-14",
+ [[
+ INSERT INTO t14 VALUES (1), (true), (1.5), (-1);
+ INSERT INTO t14 VALUES (x'11111111111111111111111111111111');
+ INSERT INTO t14 VALUES (CAST(x'11111111111111111111111111111111' AS UUID));
+ INSERT INTO t14 VALUES ('11111111-1111-1111-1111-111111111111');
+ SELECT typeof(s) FROM t14;
+ ]], {
+ "boolean", "integer", "integer", "double", "string", "varbinary", "uuid"
+ })
+
+local s = box.schema.space.create('T15', {format={{'I', 'integer'}, {'M', 'map'}, {'A', 'array'}}})
+s:create_index('i15')
+s:insert({1, {['1'] = 1, ['2'] = 2, ['3']= 3}, {1,2,3}})
+s:insert({2, {['1'] = 1, ['2'] = 2}, {1,2,3,4}})
+s:insert({3, {['1'] = 1}, {1,2,3,4,5}})
+
+-- Make sure that addition of UUID does not change behaviour of MAP and ARRAY.
+-- Currently it works wrong, but there should not segmentation faults.
+test:do_execsql_test(
+ "uuid-15.1",
+ [[
+ SELECT i FROM t15 ORDER BY m;
+ ]], {
+ 3,2,1
+ })
+
+test:do_execsql_test(
+ "uuid-15.2",
+ [[
+ SELECT i FROM t15 ORDER BY a;
+ ]], {
+ 3,2,1
+ })
+
+test:execsql([[
+ DROP TRIGGER t;
+ DROP VIEW v;
+ DROP TABLE t15;
+ DROP TABLE t14;
+ DROP TABLE t10;
+ DROP TABLE t9t;
+ DROP TABLE t9;
+ DROP TABLE tsu;
+ DROP TABLE tuu;
+ DROP TABLE tsc;
+ DROP TABLE tv;
+ DROP TABLE tb;
+ DROP TABLE ti;
+ DROP TABLE td;
+ DROP TABLE tn;
+ DROP TABLE ts;
+ DROP TABLE tu;
+ DROP TABLE t5u;
+ DROP TABLE t5c;
+ DROP TABLE t5f;
+ DROP TABLE t2;
+ DROP TABLE t1;
+]])
+
+test:finish_test()
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
2021-05-25 14:13 ` Mergen Imeev via Tarantool-patches
@ 2021-05-25 21:50 ` Vladislav Shpilevoy via Tarantool-patches
2021-05-27 16:40 ` Mergen Imeev via Tarantool-patches
2021-05-25 22:58 ` Timur Safin via Tarantool-patches
1 sibling, 1 reply; 8+ messages in thread
From: Vladislav Shpilevoy via Tarantool-patches @ 2021-05-25 21:50 UTC (permalink / raw)
To: Mergen Imeev; +Cc: tarantool-patches
Nice fixes!
> diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
> index a518fd0c6..9a6689d7d 100644
> --- a/src/box/sql/mem.c
> +++ b/src/box/sql/mem.c
> @@ -81,6 +91,8 @@ mem_str(const struct Mem *mem)
> case MEM_TYPE_MAP:
> case MEM_TYPE_ARRAY:
> return mp_str(mem->z);
> + case MEM_TYPE_UUID:
> + return tt_sprintf("%s", tt_uuid_str(&mem->u.uuid));
You don't need tt_sprintf() either. You need to simply
return tt_uuid_str().
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
2021-05-25 14:13 ` Mergen Imeev via Tarantool-patches
2021-05-25 21:50 ` Vladislav Shpilevoy via Tarantool-patches
@ 2021-05-25 22:58 ` Timur Safin via Tarantool-patches
2021-05-27 15:33 ` Mergen Imeev via Tarantool-patches
1 sibling, 1 reply; 8+ messages in thread
From: Timur Safin via Tarantool-patches @ 2021-05-25 22:58 UTC (permalink / raw)
To: 'Mergen Imeev', 'Vladislav Shpilevoy'; +Cc: TML
There is one major issue I'd like to discuss, please see below...
: From: Mergen Imeev
: Subject: Re: [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
:
...
: +static inline int
: +str_to_uuid(struct Mem *mem)
: +{
: + assert(mem->type == MEM_TYPE_STR);
: + struct tt_uuid uuid;
: + if (mem->n != UUID_STR_LEN ||
: + tt_uuid_from_string(tt_cstr(mem->z, mem->n), &uuid) != 0)
Here lies the limitation I've specifically wanted to avoid and put
below to RFC:
"The most complete implementation seemingly is in PostgreSQL,
which allows various relaxed formats for string literals
which may be accepted as UUID - the plan is to be as close
as possible to PostgreSQL here."
Checking for UUID_STR_LEN restricts only to 1 specific form with
dashes in specific positions, but we would rather want to allow
relaxed formats also, with some dashes omitted. Also with optional
braces around. i.e. putting this as regexp
\{?[0-9a-f]{8}-?[0-9a-f]{4}-?[0-9a-f]{4}-?[0-9a-f]{4}-?[0-9a-f]{12}\}?
And yes, I mean we need to reimplement tt_uuid_from_string() differently.
(If you were asking me then I'd use RE2C for that purpose, but that up
to you)
: + return -1;
: + mem_set_uuid(mem, &uuid);
: + return 0;
: +}
: +
: static inline int
: str_to_bool(struct Mem *mem)
: {
...
: diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
: index 15d97da0e..b3cd5c545 100644
: --- a/src/box/sql/mem.h
: +++ b/src/box/sql/mem.h
: @@ -30,6 +30,7 @@
: * SUCH DAMAGE.
: */
: #include "box/field_def.h"
: +#include "uuid/tt_uuid.h"
:
: struct sql;
: struct Vdbe;
: @@ -47,10 +48,11 @@ enum mem_type {
: MEM_TYPE_MAP = 1 << 6,
: MEM_TYPE_BOOL = 1 << 7,
: MEM_TYPE_DOUBLE = 1 << 8,
: - MEM_TYPE_INVALID = 1 << 9,
: - MEM_TYPE_FRAME = 1 << 10,
: - MEM_TYPE_PTR = 1 << 11,
: - MEM_TYPE_AGG = 1 << 12,
: + MEM_TYPE_UUID = 1 << 9,
: + MEM_TYPE_INVALID = 1 << 10,
: + MEM_TYPE_FRAME = 1 << 11,
: + MEM_TYPE_PTR = 1 << 12,
: + MEM_TYPE_AGG = 1 << 13,
I guess there should be no incompatibility problems here, but just
in case I ask - why did you insert new constant to the middle of prior list?
Also I see there is another omission from RFC - there is nothing added
for SQL built-in support to generate UUID from within SQL mode.
"Introduce UUID([version#]) functions which would allow generating
any particular type of GUID. If version argument is omitted then
we generate UUID v4 (as used by default in box);"
Regards,
Timur
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
2021-05-25 22:58 ` Timur Safin via Tarantool-patches
@ 2021-05-27 15:33 ` Mergen Imeev via Tarantool-patches
0 siblings, 0 replies; 8+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-05-27 15:33 UTC (permalink / raw)
To: Timur Safin; +Cc: 'Vladislav Shpilevoy', TML
Hi! Thank you for suggestions. My answers below.
On Wed, May 26, 2021 at 01:58:41AM +0300, Timur Safin wrote:
> There is one major issue I'd like to discuss, please see below...
>
> : From: Mergen Imeev
> : Subject: Re: [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
> :
> ...
>
> : +static inline int
> : +str_to_uuid(struct Mem *mem)
> : +{
> : + assert(mem->type == MEM_TYPE_STR);
> : + struct tt_uuid uuid;
> : + if (mem->n != UUID_STR_LEN ||
> : + tt_uuid_from_string(tt_cstr(mem->z, mem->n), &uuid) != 0)
>
> Here lies the limitation I've specifically wanted to avoid and put
> below to RFC:
>
> "The most complete implementation seemingly is in PostgreSQL,
> which allows various relaxed formats for string literals
> which may be accepted as UUID - the plan is to be as close
> as possible to PostgreSQL here."
>
> Checking for UUID_STR_LEN restricts only to 1 specific form with
> dashes in specific positions, but we would rather want to allow
> relaxed formats also, with some dashes omitted. Also with optional
> braces around. i.e. putting this as regexp
>
> \{?[0-9a-f]{8}-?[0-9a-f]{4}-?[0-9a-f]{4}-?[0-9a-f]{4}-?[0-9a-f]{12}\}?
>
> And yes, I mean we need to reimplement tt_uuid_from_string() differently.
> (If you were asking me then I'd use RE2C for that purpose, but that up
> to you)
>
Addition of support of other versions of UUID should not be done as part of this
issue, since it is problem of BOX, not just SQL.
> : + return -1;
> : + mem_set_uuid(mem, &uuid);
> : + return 0;
> : +}
> : +
> : static inline int
> : str_to_bool(struct Mem *mem)
> : {
> ...
> : diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
> : index 15d97da0e..b3cd5c545 100644
> : --- a/src/box/sql/mem.h
> : +++ b/src/box/sql/mem.h
> : @@ -30,6 +30,7 @@
> : * SUCH DAMAGE.
> : */
> : #include "box/field_def.h"
> : +#include "uuid/tt_uuid.h"
> :
> : struct sql;
> : struct Vdbe;
> : @@ -47,10 +48,11 @@ enum mem_type {
> : MEM_TYPE_MAP = 1 << 6,
> : MEM_TYPE_BOOL = 1 << 7,
> : MEM_TYPE_DOUBLE = 1 << 8,
> : - MEM_TYPE_INVALID = 1 << 9,
> : - MEM_TYPE_FRAME = 1 << 10,
> : - MEM_TYPE_PTR = 1 << 11,
> : - MEM_TYPE_AGG = 1 << 12,
> : + MEM_TYPE_UUID = 1 << 9,
> : + MEM_TYPE_INVALID = 1 << 10,
> : + MEM_TYPE_FRAME = 1 << 11,
> : + MEM_TYPE_PTR = 1 << 12,
> : + MEM_TYPE_AGG = 1 << 13,
>
> I guess there should be no incompatibility problems here, but just
> in case I ask - why did you insert new constant to the middle of prior list?
>
Before MEM_TYPE_INVALID are types that are accesisble to user. Starting with
MEM_TYPE_INVALID are for internal use.
> Also I see there is another omission from RFC - there is nothing added
> for SQL built-in support to generate UUID from within SQL mode.
>
> "Introduce UUID([version#]) functions which would allow generating
> any particular type of GUID. If version argument is omitted then
> we generate UUID v4 (as used by default in box);"
>
I will add this function.
> Regards,
> Timur
>
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
2021-05-25 21:50 ` Vladislav Shpilevoy via Tarantool-patches
@ 2021-05-27 16:40 ` Mergen Imeev via Tarantool-patches
0 siblings, 0 replies; 8+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-05-27 16:40 UTC (permalink / raw)
To: Vladislav Shpilevoy; +Cc: tarantool-patches
Hi! Thank you for the review! My answer and diff below. Also, I plan to send you
this patch one more time as part of patch-set, that contains this patch and
patch that introduces SQL built-in function uuid().
On Tue, May 25, 2021 at 11:50:45PM +0200, Vladislav Shpilevoy wrote:
> Nice fixes!
>
> > diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
> > index a518fd0c6..9a6689d7d 100644
> > --- a/src/box/sql/mem.c
> > +++ b/src/box/sql/mem.c
> > @@ -81,6 +91,8 @@ mem_str(const struct Mem *mem)
> > case MEM_TYPE_MAP:
> > case MEM_TYPE_ARRAY:
> > return mp_str(mem->z);
> > + case MEM_TYPE_UUID:
> > + return tt_sprintf("%s", tt_uuid_str(&mem->u.uuid));
>
> You don't need tt_sprintf() either. You need to simply
> return tt_uuid_str().
Thanks, fixed.
Diff:
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index 9a6689d7d..2ad104e1b 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -92,7 +92,7 @@ mem_str(const struct Mem *mem)
case MEM_TYPE_ARRAY:
return mp_str(mem->z);
case MEM_TYPE_UUID:
- return tt_sprintf("%s", tt_uuid_str(&mem->u.uuid));
+ return tt_uuid_str(&mem->u.uuid);
case MEM_TYPE_BOOL:
return mem->u.b ? "TRUE" : "FALSE";
default:
^ permalink raw reply [flat|nested] 8+ messages in thread
end of thread, other threads:[~2021-05-27 16:40 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2021-05-20 9:44 [Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type Mergen Imeev via Tarantool-patches
2021-05-21 7:16 ` Mergen Imeev via Tarantool-patches
2021-05-22 16:04 ` Vladislav Shpilevoy via Tarantool-patches
2021-05-25 14:13 ` Mergen Imeev via Tarantool-patches
2021-05-25 21:50 ` Vladislav Shpilevoy via Tarantool-patches
2021-05-27 16:40 ` Mergen Imeev via Tarantool-patches
2021-05-25 22:58 ` Timur Safin via Tarantool-patches
2021-05-27 15:33 ` Mergen Imeev 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