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 5A31C6EC56; Wed, 28 Jul 2021 23:51:38 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 5A31C6EC56 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1627505498; bh=8Ae+Dt8VhSFFYkBVefpocQYsL2oTeEoV1IASqLX05Ro=; h=To:Cc:Date:In-Reply-To:References:Subject:List-Id: List-Unsubscribe:List-Archive:List-Post:List-Help:List-Subscribe: From:Reply-To:From; b=uqklzulYuKZn8sQUoLC+cUDd6zM4e/UCAfd2xFLSF/k8EZ5wbprnQpGR+ylAAlQ+F 9CQVhtkGSoOO+B0/ugIJvNEuglTxGO9z0clIYUOfIHX+bV8+ih+Lay4H2JC2W7e/HS vh+0WAlFTDeSDhV3FLyM2Hwsf7eNjIjAlVDK3uHc= Received: from smtp44.i.mail.ru (smtp44.i.mail.ru [94.100.177.104]) (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 8C53C6EC56 for ; Wed, 28 Jul 2021 23:51:09 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 8C53C6EC56 Received: by smtp44.i.mail.ru with esmtpa (envelope-from ) id 1m8qWC-0007Vv-Oh; Wed, 28 Jul 2021 23:51:09 +0300 To: v.shpilevoy@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Wed, 28 Jul 2021 23:51:08 +0300 Message-Id: X-Mailer: git-send-email 2.25.1 In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-4EC0790: 10 X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD941C43E597735A9C33D83595CA30D6DC5179D1C9A908C47E5182A05F53808504069619B71D25B24568EA79DE78BF4E7A7998AAF1C6EB692836853E04672FEEEC7 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7ECD3FEFFF0C7120DC2099A533E45F2D0395957E7521B51C2CFCAF695D4D8E9FCEA1F7E6F0F101C6778DA827A17800CE7A8883CB087864CAC8F08D7030A58E5AD1A62830130A00468AEEEE3FBA3A834EE7353EFBB553375667BEA09003D200E08818103C63808846D2B12B1D75B5A6D59A471835C12D1D9774AD6D5ED66289B5278DA827A17800CE7328B01A8D746D8839FA2833FD35BB23D2EF20D2F80756B5F868A13BD56FB6657A471835C12D1D977725E5C173C3A84C34964A708C60C975A117882F4460429728AD0CFFFB425014E868A13BD56FB6657D81D268191BDAD3DC09775C1D3CA48CF41C516418418B71EBA3038C0950A5D36C8A9BA7A39EFB766EC990983EF5C0329BA3038C0950A5D36D5E8D9A59859A8B6557FE90A798C3D3A76E601842F6C81A1F004C906525384307823802FF610243DF43C7A68FF6260569E8FC8737B5C2249EC8D19AE6D49635B68655334FD4449CB9ECD01F8117BC8BEAAAE862A0553A39223F8577A6DFFEA7CE31A2885C41F97C443847C11F186F3C59DAA53EE0834AAEE X-B7AD71C0: AC4F5C86D027EB782CDD5689AFBDA7A213B5FB47DCBC3458F0AFF96BAACF4158235E5A14AD4A4A4625E192CAD1D9E79DB53CE843736870892225C3F6AE968453 X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975C7BEA09003D200E08818103C63808846D2B12B1D75B5A6D599C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EF795D7D556640A06E699F904B3F4130E343918A1A30D5E7FCCB5012B2E24CD356 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34A5BF8FA0BD61F994E0DA2E28917DCB50C160A8EFAC7BB500F644A29BB2F85A6C44A0885FDF4C15481D7E09C32AA3244C6B1C57198D25B10FDADF4F2AA0137CB1C86C126E7119A0FE729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojbL9S8ysBdXi4QUX63t/5tmv5g1r6TYqA X-Mailru-Sender: 3A338A78718AEC5ABE350BD77BF82E153DBE501EF66389DC546960C7A0D32C971AB84C443E612F09A3E7B4BFDCAD2EFE027D9DD7AE851095A2E8D17B49942DB0CBEE3F9BE14373499437F6177E88F7363CDA0F3B3F5B9367 X-Mras: Ok Subject: [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: imeevma@tarantool.org Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" 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 --- .../gh-4470-implicit-cast-for-assignment.md | 3 + src/box/sql/mem.c | 135 ++++++++++++++++++ src/box/sql/mem.h | 10 ++ src/box/sql/vdbe.c | 15 +- test/sql-tap/cast.test.lua | 94 +++++++++++- test/sql-tap/numcast.test.lua | 4 +- test/sql-tap/uuid.test.lua | 64 +++------ test/sql/types.result | 3 +- 8 files changed, 281 insertions(+), 47 deletions(-) create mode 100644 changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md 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). 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)) + /* * Make sure pMem->z points to a writable allocation of at least * min(n,32) bytes. @@ -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; + return res; +} + +static inline int +forced_int_to_uint(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_INT || mem->type == MEM_TYPE_UINT); + 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; +} + +static inline int +forced_double_to_uint(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DOUBLE); + double d = mem->u.r; + uint64_t u; + int res; + if (d < 0.0) { + u = 0; + res = -1; + } else if (d >= (double)UINT64_MAX) { + u = UINT64_MAX; + res = 1; + } else { + u = (uint64_t)d; + res = CMP_OLD_NEW(d, u, double); + } + mem->u.u = u; + mem->type = MEM_TYPE_UINT; + mem->field_type = FIELD_TYPE_UNSIGNED; + return res; +} + +static inline int +forced_double_to_int(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DOUBLE); + double d = mem->u.r; + int64_t i; + enum mem_type type; + int res; + if (d < (double)INT64_MIN) { + i = INT64_MIN; + type = MEM_TYPE_INT; + res = -1; + } else if (d >= (double)UINT64_MAX) { + i = (int64_t)UINT64_MAX; + type = MEM_TYPE_UINT; + res = 1; + } else if (d <= -1.0) { + i = (int64_t)d; + type = MEM_TYPE_INT; + res = CMP_OLD_NEW(d, i, double); + } else { + uint64_t u = (uint64_t)d; + i = (int64_t)u; + type = MEM_TYPE_UINT; + res = CMP_OLD_NEW(d, u, double); + } + mem->u.i = i; + mem->type = type; + mem->field_type = FIELD_TYPE_INTEGER; + return res; +} + int mem_to_int(struct Mem *mem) { @@ -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); + case MEM_TYPE_DOUBLE: + return 0; + default: + unreachable(); + } + break; + case FIELD_TYPE_INTEGER: + switch (mem->type) { + case MEM_TYPE_UINT: + case MEM_TYPE_INT: + mem->field_type = FIELD_TYPE_INTEGER; + return 0; + case MEM_TYPE_DOUBLE: + return forced_double_to_int(mem); + default: + unreachable(); + } + break; + default: + unreachable(); + } + return 0; +} + int mem_get_int(const struct Mem *mem, int64_t *i, bool *is_neg) { 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; + * +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); + /** * Return value for MEM of INTEGER type. For MEM of all other types convert * value of the MEM to INTEGER if possible and return converted value. Original 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; + 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/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 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; ]], { - 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 @@ -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,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; ]], { - '7_varbinary', uuid1 + 1, "Type mismatch: can not convert varbinary(x'11111111111111111111111111111111') to uuid" }) test:do_catchsql_test( @@ -882,59 +875,48 @@ test:execsql([[ ]]) -- Check that INSERT into UUID field works. -test:do_execsql_test( +test:do_catchsql_test( "uuid-10.1.1", [[ INSERT INTO t10 VALUES (1, '22222222-1111-1111-1111-111111111111'); - SELECT * FROM t10 WHERE i = 1; ]], { - 1, uuid2 + 1, "Type mismatch: can not convert string('22222222-1111-1111-1111-111111111111') to uuid" }) -test:do_execsql_test( +test:do_catchsql_test( "uuid-10.1.2", [[ INSERT INTO t10 VALUES (2, x'22222222111111111111111111111111'); - SELECT * FROM t10 WHERE i = 2; ]], { - 2, uuid2 + 1, "Type mismatch: can not convert varbinary(x'22222222111111111111111111111111') to uuid" }) -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" }) test:do_execsql_test( "uuid-10.1.4", [[ - INSERT INTO t10 VALUES (4, NULL); - SELECT * FROM t10 WHERE i = 4; + INSERT INTO t10 VALUES (1, CAST(']]..uuid2:str()..[[' AS UUID)); + INSERT INTO t10 VALUES (2, NULL); + SELECT * FROM t10; ]], { - 4, '' + 1, uuid2, 2, '' }) -- 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", + "uuid-10.2", [[ - UPDATE t10 SET u = x'11111111333311111111111111111111' WHERE i = 2; - SELECT * FROM t10 WHERE i = 2; + UPDATE t10 SET u = CAST(']]..uuid3:str()..[[' AS UUID) WHERE i = 1; + SELECT * FROM t10; ]], { - 2, uuid3 + 1, uuid3, 2, '' }) -- Check that JOIN by UUID field works. diff --git a/test/sql/types.result b/test/sql/types.result index 8da94d126..25d4dbefc 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -2580,7 +2580,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;]]) --- -- 2.25.1