From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from [87.239.111.99] (localhost [127.0.0.1]) by dev.tarantool.org (Postfix) with ESMTP id AE97374173; Fri, 6 Aug 2021 02:27:31 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org AE97374173 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1628206051; bh=8N8bQ71gHjsdlWpXlo1jW1Q7yp49R9aA9xqHmmxurlw=; h=Date:To:Cc:References:In-Reply-To:Subject:List-Id: List-Unsubscribe:List-Archive:List-Post:List-Help:List-Subscribe: From:Reply-To:From; b=LOEbrvJrlicMCbhRvLsW92/8+p7FQoyna0U7MIVIcn907uzpFYwoM3LlN4Zj1+imo 2w9n1c8/wcY0A7bNvIEquRS1sHmcD1XfGYzLYd/zch6ngtJgEkW99C3tSyM+RoFSqY e536u0Y1VaV35OucSPJ8fUU0qFlcyNFNJqgwKYX4= Received: from smtp47.i.mail.ru (smtp47.i.mail.ru [94.100.177.107]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id E937C7034B for ; Fri, 6 Aug 2021 02:27:29 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org E937C7034B Received: by smtp47.i.mail.ru with esmtpa (envelope-from ) id 1mBmls-0005Ja-W0; Fri, 06 Aug 2021 02:27:29 +0300 Date: Fri, 6 Aug 2021 02:27:27 +0300 To: Vladislav Shpilevoy Cc: tarantool-patches@dev.tarantool.org Message-ID: <20210805232727.GA81912@tarantool.org> References: <8a2c3321-0251-56b5-cc47-475a407c722f@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <8a2c3321-0251-56b5-cc47-475a407c722f@tarantool.org> X-4EC0790: 10 X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD92087353F0EC44DD910164DC12A5633065676A9727AC27C74182A05F538085040A2425EE852EBEFF8ED1B6081A72ED20B98F65DEAC04A26060284097F7424E556 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE74D0D2DEF2EB846B0EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F79006371ECA800C705ED7E68638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D830ABA5CE923A66E4BFAAE4C1290583D5117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BF1175FABE1C0F9B6A471835C12D1D977C4224003CC836476EB9C4185024447017B076A6E789B0E975F5C1EE8F4F765FC515FDB7F7FD27AD53AA81AA40904B5D9CF19DD082D7633A078D18283394535A93AA81AA40904B5D98AA50765F790063744F53BFAB09560F8D81D268191BDAD3D698AB9A7B718F8C4D1B931868CE1C5781A620F70A64A45A98AA50765F79006372E808ACE2090B5E1725E5C173C3A84C3C5EA940A35A165FF2DBA43225CD8A89F83C798A30B85E16B156CCFE7AF13BCA4B5C8C57E37DE458BEDA766A37F9254B7 X-B7AD71C0: AC4F5C86D027EB782CDD5689AFBDA7A213B5FB47DCBC3458834459D11680B5054D77E0D74EC5E3A746385A7DA904B770 X-C1DE0DAB: 0D63561A33F958A54B8A9D666BA6EFDA4ECDF86640BAE730A6146590278A61F9D59269BC5F550898D99A6476B3ADF6B47008B74DF8BB9EF7333BD3B22AA88B938A852937E12ACA7501A9DF589746230F410CA545F18667F91A7EA1CDA0B5A7A0 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34BD150993ED26DDF45484549EC6B6C283CED7ADA4654281AF4EDA4503958546A649B4C37E2D1C99E61D7E09C32AA3244C8341A1AB52BD517B5DC9074A1E39D15AB038C9161EF167A1729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojFhlvmGwdUwQlOPwexSMj8w== X-Mailru-Sender: 5C3750E245F362008BC1685FEC6306ED9B1085A7001B6657ED1B6081A72ED20B9FD6E2B231121C055105BD0848736F9966FEC6BF5C9C28D97E07721503EA2E00ED97202A5A4E92BF7402F9BA4338D657ED14614B50AE0675 X-Mras: Ok Subject: Re: [Tarantool-patches] [PATCH v1 1/7] sql: rework implicit cast fo assignment X-BeenThere: tarantool-patches@dev.tarantool.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , From: Mergen Imeev via Tarantool-patches Reply-To: Mergen Imeev Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" Hi! Thank you for the review! My answer and new patch below. I didn't include diffs in all my answers in this patch-set since these were some merge-conflicts due to rebase on current master. On Fri, Jul 30, 2021 at 11:55:05PM +0200, Vladislav Shpilevoy wrote: > Thanks for the patch! > > I will return later to continue the review of the next commits. > Sending first comments so as I could switch to other patches. > > See 10 comments below. > > > diff --git a/changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md b/changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md > > new file mode 100644 > > index 000000000..c758494eb > > --- /dev/null > > +++ b/changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md > > @@ -0,0 +1,3 @@ > > +## feature/sql > > + > > +* Implicit cast for assignment now works according to defined rules (gh-4470). > > 1. Please, be more specific about what exactly has changed. > Fixed. > > diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c > > index 351d80b76..e804dba67 100644 > > --- a/src/box/sql/mem.c > > +++ b/src/box/sql/mem.c > > @@ -45,6 +45,8 @@ > > #include "uuid/mp_uuid.h" > > #include "mp_decimal.h" > > > > +#define CMP_OLD_NEW(a, b, type) ((int)(a > (type)b) - (int)(a < (type)b)) > > 2. Please, wrap 'a' and 'b' into parentheses. Otherwise the macro might > work in unexpected ways if these are expressions. > Fixed. However, I moved this code to another patch. > > @@ -900,6 +902,92 @@ uuid_to_bin(struct Mem *mem) > > return mem_copy_bin(mem, (char *)&mem->u.uuid, UUID_LEN); > > } > > > > +static inline int > > +forced_int_to_double(struct Mem *mem) > > +{ > > + assert(mem->type == MEM_TYPE_INT || mem->type == MEM_TYPE_UINT); > > + double d; > > + int res; > > + if (mem->type == MEM_TYPE_INT) { > > + d = (double)mem->u.i; > > + res = CMP_OLD_NEW(mem->u.i, d, int64_t); > > + } else { > > + d = (double)mem->u.u; > > + res = CMP_OLD_NEW(mem->u.u, d, uint64_t); > > + } > > + mem->u.r = d; > > + mem->type = MEM_TYPE_DOUBLE; > > + mem->field_type = FIELD_TYPE_DOUBLE; > > 3. Why do you need not to fail in case of imprecise conversion? It don't > see -1 and 1 values used. Only != 0 in the end where the implicit casts > are applied. > True, however I need this functions to work with indexes. I moved this function to patch that fixes work with indexes. > > + return res; > > +} > > + > > +static inline int > > +forced_int_to_uint(struct Mem *mem) > > +{ > > + assert(mem->type == MEM_TYPE_INT || mem->type == MEM_TYPE_UINT); > > 4. It is never used with MEM_TYPE_UINT from what I see. > True, in another patch already fixed function. > > + if (mem->type == MEM_TYPE_UINT) > > + return 0; > > + mem->u.u = 0; > > + mem->type = MEM_TYPE_UINT; > > + mem->field_type = FIELD_TYPE_UNSIGNED; > > + return -1; > > +} > > @@ -1228,6 +1316,53 @@ mem_cast_implicit_old(struct Mem *mem, enum field_type type) > > return -1; > > } > > > > +int > > +mem_cast_implicit_number(struct Mem *mem, enum field_type type) > > +{ > > + assert(mem_is_num(mem) && sql_type_is_numeric(type)); > > + switch (type) { > > + case FIELD_TYPE_UNSIGNED: > > + switch (mem->type) { > > + case MEM_TYPE_UINT: > > + mem->field_type = FIELD_TYPE_UNSIGNED; > > + return 0; > > + case MEM_TYPE_INT: > > + return forced_int_to_uint(mem); > > + case MEM_TYPE_DOUBLE: > > + return forced_double_to_uint(mem); > > + default: > > + unreachable(); > > + } > > + break; > > + case FIELD_TYPE_DOUBLE: > > + switch (mem->type) { > > + case MEM_TYPE_INT: > > + case MEM_TYPE_UINT: > > + return forced_int_to_double(mem); > > 5. You already have the switch here and yet you have a branch > by MEM_TYPE_UINT vs MEM_TYPE_INT inside forced_int_to_double. > Maybe split it 2 for INT and UINT separately and use them > right under each 'case'? > Done, in another patch. > > diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h > > index 645d0ee27..9766bb836 100644 > > --- a/src/box/sql/mem.h > > +++ b/src/box/sql/mem.h > > @@ -779,6 +779,16 @@ mem_cast_implicit(struct Mem *mem, enum field_type type); > > int > > mem_cast_implicit_old(struct Mem *mem, enum field_type type); > > > > +/** > > + * Convert the given MEM that contains numeric value to given numeric type > > + * according to implicit cast rules. This function cannot fail. Returns: > > + * -1 if before conversion value was more that after conversion; > > 6. 'more' -> 'greater', 'that' -> 'than'. > Fixed. Also added comments to all "forced" functions. > > + * +1 if before conversion value was more that after conversion; > > + * 0 if conversion is precise. > > + */ > > +int > > +mem_cast_implicit_number(struct Mem *mem, enum field_type type); > > diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c > > index 9e763ed85..d143ce364 100644 > > --- a/src/box/sql/vdbe.c > > +++ b/src/box/sql/vdbe.c > > @@ -2157,11 +2157,24 @@ case OP_ApplyType: { > > while((type = *(types++)) != field_type_MAX) { > > assert(pIn1 <= &p->aMem[(p->nMem+1 - p->nCursor)]); > > assert(memIsValid(pIn1)); > > - if (mem_cast_implicit(pIn1, type) != 0) { > > + if (mem_is_field_compatible(pIn1, type)) { > > + pIn1++; > > + continue; > > + } > > + if (!mem_is_num(pIn1) || !sql_type_is_numeric(type)) { > > diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > mem_str(pIn1), field_type_strs[type]); > > goto abort_due_to_error; > > } > > + struct Mem mem; > > + mem.type = pIn1->type; > > + mem.u = pIn1->u; > > + mem.flags = 0; > > 7. The mem is only used for the error message. Please, move it > under the 'if' right before diag_set so as not to create it for > success path. And why can't you use pIn1 in mem_str() below? > I did this because after sql_type_is_numeric() MEM would change. However, I dropped this diff since I decided that it is quite inconvenient not eaily understandable change. > > + if (mem_cast_implicit_number(pIn1, type) != 0) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + mem_str(&mem), field_type_strs[type]); > > + goto abort_due_to_error; > > + } > > pIn1++; > > } > > break; > > diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua > > index 802fe712c..be1366260 100755 > > --- a/test/sql-tap/numcast.test.lua > > +++ b/test/sql-tap/numcast.test.lua > > @@ -136,13 +136,13 @@ test:do_catchsql_test( > > 1,"Type mismatch: can not convert double(2.0e+19) to integer" > > }) > > > > -test:do_execsql_test( > > +test:do_catchsql_test( > > "cast-2.9", > > [[ > > INSERT INTO t VALUES(2.1); > > SELECT * FROM t; > > 8. The select is not needed now. > Fixed. > > ]], { > > - 2, 9223372036854775808ULL, 18000000000000000000ULL > > + 1, "Type mismatch: can not convert double(2.1) to integer" > > }) > > > > -- > > diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua > > index 70683a4fd..613f4c865 100755 > > --- a/test/sql-tap/uuid.test.lua > > +++ b/test/sql-tap/uuid.test.lua > > @@ -844,13 +837,13 @@ test:do_catchsql_test( > > 1, "Type mismatch: can not convert boolean(TRUE) to uuid" > > }) > > > > -test:do_execsql_test( > > +test:do_catchsql_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; > > 9. The select is unreachable. > Fixed. > > ]], { > > - '7_varbinary', uuid1 > > + 1, "Type mismatch: can not convert varbinary(x'11111111111111111111111111111111') to uuid" > > }) > > @@ -882,59 +875,48 @@ test:execsql([[ > > <...> > > > -test:do_execsql_test( > > +test:do_catchsql_test( > > "uuid-10.1.3", > > [[ > > INSERT INTO t10(i) VALUES (3); > > - SELECT * FROM t10 WHERE i = 3; > > ]], { > > - 3, uuid1 > > + 1, "Type mismatch: can not convert string('11111111-1111-1111-1111-111111111111') to uuid" > > 10. I suspect this test was about checking if UUID DEFAULT works. > Maybe better fix its DEFAULT in the table definition so as not to > use a string. Is it possible to use CAST in DEFAULT? Fixed. It is possible to work with functions using "()" for DEFAULT. New patch: commit 99407b480ec338bfb629e7ddd9ae168f76674c8d Author: Mergen Imeev Date: Tue Jul 27 23:54:20 2021 +0300 sql: rework implicit cast fo assignment After this patch, the new rules will be applied to implicit cast during assignment. According to these rules, all scalar values can be cast to SCALAR, all numeric values can be cast to NUMBER, and any numeric value can be cast to another numeric type only if the conversion is exact. No other implicit cast is allowed. Part of #4470 diff --git a/changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md b/changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md new file mode 100644 index 000000000..6e3e78cf7 --- /dev/null +++ b/changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md @@ -0,0 +1,6 @@ +## feature/sql + +* Now a numeric value can be cast to another numeric type only if the cast is + precise. In addition, a UUID value cannot be implicitly cast to + STRING/VARBINARY, and a STRING/VARBINARY value cannot be implicitly cast to + a UUID (gh-4470). \ No newline at end of file diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 6ca852dec..e153db24b 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -58,7 +58,7 @@ static const void * mem_as_bin(struct Mem *mem) { const char *s; - if (mem_cast_implicit(mem, FIELD_TYPE_VARBINARY) != 0 && + if (mem_cast_explicit(mem, FIELD_TYPE_VARBINARY) != 0 && mem_to_str(mem) != 0) return NULL; if (mem_get_bin(mem, &s) != 0) diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index eebc47f4e..c0ceb98e9 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -624,6 +624,34 @@ int_to_double(struct Mem *mem) return 0; } +static inline int +int_to_double_precise(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_INT); + double d; + d = (double)mem->u.i; + if (mem->u.i != (int64_t)d) + return -1; + mem->u.r = d; + mem->type = MEM_TYPE_DOUBLE; + mem->field_type = FIELD_TYPE_DOUBLE; + return 0; +} + +static inline int +uint_to_double_precise(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_UINT); + double d; + d = (double)mem->u.u; + if (mem->u.u != (uint64_t)d) + return -1; + mem->u.r = d; + mem->type = MEM_TYPE_DOUBLE; + mem->field_type = FIELD_TYPE_DOUBLE; + return 0; +} + static inline int int_to_str0(struct Mem *mem) { @@ -1083,25 +1111,25 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) if (mem->type == MEM_TYPE_UINT) return 0; if (mem->type == MEM_TYPE_DOUBLE) - return double_to_uint(mem); + return double_to_uint_precise(mem); return -1; 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) return 0; - if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0) - return int_to_double(mem); + if (mem->type == MEM_TYPE_INT) + return int_to_double_precise(mem); + if (mem->type == MEM_TYPE_UINT) + return uint_to_double_precise(mem); return -1; case FIELD_TYPE_INTEGER: if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0) return 0; if (mem->type == MEM_TYPE_DOUBLE) - return double_to_int(mem); + return double_to_int_precise(mem); return -1; case FIELD_TYPE_BOOLEAN: if (mem->type == MEM_TYPE_BOOL) @@ -1111,8 +1139,6 @@ 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)) @@ -1133,10 +1159,6 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) 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; diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua index 799bcc1a8..8af99dbde 100755 --- a/test/sql-tap/cast.test.lua +++ b/test/sql-tap/cast.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(95) +test:plan(103) --!./tcltestrunner.lua -- 2005 June 25 @@ -875,7 +875,7 @@ test:do_test( -- }) --- gh-4470: Make explicit casts work according to our rules. +-- gh-4470: Make explicit and implicit casts work according to our rules. -- Make sure that explicit cast from BOOLEAN to numeric types throws an error. test:do_catchsql_test( @@ -1017,4 +1017,94 @@ test:do_execsql_test( true }) +-- Make sure that implicit conversion of numeric values is precise. +test:execsql([[ + CREATE TABLE t2 (i INTEGER PRIMARY KEY AUTOINCREMENT, a INTEGER, b DOUBLE); + CREATE TABLE t3 (i INTEGER PRIMARY KEY AUTOINCREMENT, s STRING); + CREATE TABLE t4 (i INTEGER PRIMARY KEY AUTOINCREMENT, v VARBINARY); + CREATE TABLE t5 (i INTEGER PRIMARY KEY AUTOINCREMENT, u UUID); +]]) + +test:do_execsql_test( + "cast-9.1.1", + [[ + INSERT INTO t2(a) VALUES(1.0e0); + SELECT a FROM t2 WHERE i = 1; + ]], { + 1 + }) + +test:do_catchsql_test( + "cast-9.1.2", + [[ + INSERT INTO t2(a) VALUES(1.5e0); + ]], { + 1, "Type mismatch: can not convert double(1.5) to integer" + }) + +test:do_execsql_test( + "cast-9.1.3", + [[ + INSERT INTO t2(b) VALUES(10000000000000000); + SELECT b FROM t2 WHERE i = 2; + ]], { + 10000000000000000 + }) + +test:do_catchsql_test( + "cast-9.1.4", + [[ + INSERT INTO t2(b) VALUES(10000000000000001); + ]], { + 1, "Type mismatch: can not convert integer(10000000000000001) to double" + }) + +-- Make sure that UUID cannot be implicitly cast to STRING. +local uuid = "CAST('11111111-1111-1111-1111-111111111111' AS UUID)"; +test:do_catchsql_test( + "cast-9.2", + [[ + INSERT INTO t3(s) VALUES(]]..uuid..[[); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to string" + }) + +-- Make sure that UUID cannot be implicitly cast to VARBINARY. +test:do_catchsql_test( + "cast-9.3", + [[ + INSERT INTO t4(v) VALUES(]]..uuid..[[); + ]], { + 1, "Type mismatch: can not convert ".. + "uuid('11111111-1111-1111-1111-111111111111') to varbinary" + }) + +-- Make sure that STRING and VARBINARY cannot be implicitly cast to UUID. +test:do_catchsql_test( + "cast-9.4.1", + [[ + INSERT INTO t5(u) VALUES('11111111-1111-1111-1111-111111111111'); + ]], { + 1, "Type mismatch: can not convert ".. + "string('11111111-1111-1111-1111-111111111111') to uuid" + }) + +test:do_catchsql_test( + "cast-9.4.2", + [[ + INSERT INTO t5(u) VALUES(x'11111111111111111111111111111111'); + ]], { + 1, "Type mismatch: can not convert ".. + "varbinary(x'11111111111111111111111111111111') to uuid" + }) + +test:execsql([[ + DROP TABLE t1; + DROP TABLE t2; + DROP TABLE t3; + DROP TABLE t4; + DROP TABLE t5; +]]) + test:finish_test() diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua index 9ecda6833..b172ca625 100755 --- a/test/sql-tap/numcast.test.lua +++ b/test/sql-tap/numcast.test.lua @@ -136,13 +136,12 @@ test:do_catchsql_test( 1,"Type mismatch: can not convert double(2.0e+19) to integer" }) -test:do_execsql_test( +test:do_catchsql_test( "cast-2.9", [[ INSERT INTO t VALUES(2.1); - SELECT * FROM t; ]], { - 2, 9223372036854775808ULL, 18000000000000000000ULL + 1, "Type mismatch: can not convert double(2.1) to integer" }) -- diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua index ad91c3e1c..abb5960a7 100755 --- a/test/sql-tap/uuid.test.lua +++ b/test/sql-tap/uuid.test.lua @@ -3,7 +3,7 @@ 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(147) +test:plan(146) local uuid = require("uuid") local uuid1 = uuid.fromstr("11111111-1111-1111-1111-111111111111") @@ -722,15 +722,12 @@ test:do_catchsql_test( 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to unsigned" }) -test:do_execsql_test( +test:do_catchsql_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" + 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to string" }) test:do_catchsql_test( @@ -765,15 +762,12 @@ test:do_catchsql_test( 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to boolean" }) -test:do_execsql_test( +test:do_catchsql_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" + 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to varbinary" }) test:do_execsql_test( @@ -803,13 +797,12 @@ test:do_catchsql_test( 1, "Type mismatch: can not convert integer(1) to uuid" }) -test:do_execsql_test( +test:do_catchsql_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 + 1, "Type mismatch: can not convert string('11111111-1111-1111-1111-111111111111') to uuid" }) test:do_catchsql_test( @@ -844,13 +837,12 @@ test:do_catchsql_test( 1, "Type mismatch: can not convert boolean(TRUE) to uuid" }) -test:do_execsql_test( +test:do_catchsql_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 + 1, "Type mismatch: can not convert varbinary(x'11111111111111111111111111111111') to uuid" }) test:do_catchsql_test( @@ -877,24 +869,27 @@ test:do_execsql_test( uuid1, uuid3, uuid2 }) -test:execsql([[ - CREATE TABLE t10 (i INT PRIMARY KEY AUTOINCREMENT, u UUID DEFAULT '11111111-1111-1111-1111-111111111111'); -]]) +local default = "DEFAULT(CAST('11111111-1111-1111-1111-111111111111' AS UUID))" +test:execsql( + "CREATE TABLE t10 (i INT PRIMARY KEY AUTOINCREMENT, u UUID "..default..");" +) -- Check that INSERT into UUID field works. +local uuid2_str = "'22222222-1111-1111-1111-111111111111'"; test:do_execsql_test( "uuid-10.1.1", [[ - INSERT INTO t10 VALUES (1, '22222222-1111-1111-1111-111111111111'); + INSERT INTO t10 VALUES (1, CAST(]]..uuid2_str..[[ AS UUID)); SELECT * FROM t10 WHERE i = 1; ]], { 1, uuid2 }) +local uuid2_bin = "x'22222222111111111111111111111111'"; test:do_execsql_test( "uuid-10.1.2", [[ - INSERT INTO t10 VALUES (2, x'22222222111111111111111111111111'); + INSERT INTO t10 VALUES (2, CAST(]]..uuid2_bin..[[ AS UUID)); SELECT * FROM t10 WHERE i = 2; ]], { 2, uuid2 @@ -920,21 +915,12 @@ test:do_execsql_test( -- Check that UPDATE of UUID field works. test:do_execsql_test( - "uuid-10.2.1", + "uuid-10", [[ - 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; + UPDATE t10 SET u = CAST(]]..uuid2_str..[[ AS UUID) WHERE i = 3; + SELECT * FROM t10 WHERE i = 3; ]], { - 2, uuid3 + 3, uuid2 }) -- Check that JOIN by UUID field works. diff --git a/test/sql/types.result b/test/sql/types.result index bb0109d6d..00c60cca9 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -2577,7 +2577,8 @@ box.execute([[UPDATE td SET d = 11 WHERE a = 1;]]) ... box.execute([[UPDATE td SET d = 100000000000000001 WHERE a = 1;]]) --- -- row_count: 1 +- null +- 'Type mismatch: can not convert integer(100000000000000001) to double' ... box.execute([[UPDATE td SET d = 22.2 WHERE a = 1;]]) ---