[Tarantool-patches] [PATCH 1/1] sql: introduce UUID field type
Mergen Imeev
imeevma at tarantool.org
Fri May 21 10:16:49 MSK 2021
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
>
More information about the Tarantool-patches
mailing list