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 03C686EC41; Mon, 16 Aug 2021 18:58:33 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 03C686EC41 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1629129513; bh=oVM28YS8XyZFiYa+20ElOdJFl0bMylkzYmG16H4z2bo=; 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=pyAUiQijwcr1fbG+vYyv2yGqAE77M8PPzyJoHyZtIt4K9ocLI0zOp/fyhZaLs8Eve ywRyhlhzcHS7x19NimoI5gQ8WD6Lo3tWo3GwUNUTwflsk2CsMQrGsDEuIU/KYkTdjL ZoKHL2WBfSPndw7fE3c1XBmIu55/N+AlwrcUNYwk= Received: from smtpng2.i.mail.ru (smtpng2.i.mail.ru [94.100.179.3]) (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 E8E906EC45 for ; Mon, 16 Aug 2021 18:57:04 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org E8E906EC45 Received: by smtpng2.m.smailru.net with esmtpa (envelope-from ) id 1mFez2-0003Er-2m; Mon, 16 Aug 2021 18:57:04 +0300 To: korablev@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Mon, 16 Aug 2021 18:57:03 +0300 Message-Id: <2ce4a5bdfd31a3da379dc030a9ffdc21ca0114ed.1629129129.git.imeevma@gmail.com> X-Mailer: git-send-email 2.25.1 In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD92087353F0EC44DD906AB4890CDABF0C5CB76CEE71D3E4007182A05F5380850402154ECA66523169E71D5B4B3871519013658240496BFB112DE2315B0D594EBE7 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE779AAD18609327F83EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F79006377B6FA75B73850C7AEA1F7E6F0F101C6723150C8DA25C47586E58E00D9D99D84E1BDDB23E98D2D38BBCA57AF85F7723F2E3949A77B0408CAE74D44D5F2939E89FCC7F00164DA146DAFE8445B8C89999728AA50765F7900637F6B57BC7E64490618DEB871D839B7333395957E7521B51C2DFABB839C843B9C08941B15DA834481F8AA50765F7900637F3E38EE449E3E2AE389733CBF5DBD5E9B5C8C57E37DE458B9E9CE733340B9D5F3BBE47FD9DD3FB595F5C1EE8F4F765FC72CEEB2601E22B093A03B725D353964B0B7D0EA88DDEDAC722CA9DD8327EE4930A3850AC1BE2E735444A83B712AC0148C4224003CC83647689D4C264860C145E X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975C5B73F950BC6E7FFB04AC74E5A83929A68DDE0E2D0A44BA429C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EFE37876E7723AB534DC48ACC2A39D04F89CDFB48F4795C241BDAD6C7F3747799A X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D34DFB7A809FB537087E21AACAAE5A9EFAC93DEED64FF9E06EEB94F40A725CDE8CA963874E8AF7363401D7E09C32AA3244CB732BB15465D610C8DFB1A2841BC37D23E8609A02908F271729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojIrFL/N5KnVFA/G/GGju+Gw== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D259F8E98A9787672D31FBDAE0454AC9E83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 3/4] sql: introduce cast for decimal 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" This patch introduces implicit and explicit cast of the DECIMAL field in SQL. Implicit cast to and from DECIMAL is precise. Explicit conversion to decimal and back could lead to loss of precision. Part of #4415 --- src/box/sql/mem.c | 334 ++++++++++++++++++++++++++++++- test/sql-tap/decimal.test.lua | 366 +++++++++++++++++++++++++++++++++- 2 files changed, 695 insertions(+), 5 deletions(-) diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c index 016f0e80b..ff8b40d7f 100644 --- a/src/box/sql/mem.c +++ b/src/box/sql/mem.c @@ -678,6 +678,17 @@ int_to_double_forced(struct Mem *mem) return CMP_OLD_NEW(i, d, int64_t); } +static inline int +int_to_dec(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_INT); + int64_t i = mem->u.i; + decimal_from_int64(&mem->u.d, i); + mem->type = MEM_TYPE_DEC; + mem->flags = 0; + return 0; +} + static inline int uint_to_double_precise(struct Mem *mem) { @@ -709,6 +720,17 @@ uint_to_double_forced(struct Mem *mem) return CMP_OLD_NEW(u, d, uint64_t); } +static inline int +uint_to_dec(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_UINT); + int64_t u = mem->u.u; + decimal_from_uint64(&mem->u.d, u); + mem->type = MEM_TYPE_DEC; + mem->flags = 0; + return 0; +} + static inline int int_to_str0(struct Mem *mem) { @@ -855,6 +877,19 @@ str_to_double(struct Mem *mem) return 0; } +static inline int +str_to_dec(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_STR); + decimal_t dec; + decimal_t *d; + d = decimal_from_string(&dec, mem->z); + if (d == NULL) + return -1; + mem_set_dec(mem, &dec); + return 0; +} + static inline int double_to_int(struct Mem *mem) { @@ -988,6 +1023,69 @@ double_to_uint_forced(struct Mem *mem) return res; } +static inline int +double_to_dec(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DOUBLE); + double d = mem->u.r; + if (d >= 1e38 || d <= -1e38) + return -1; + decimal_from_double(&mem->u.d, d); + mem->type = MEM_TYPE_DEC; + mem->flags = 0; + return 0; +} + +static inline int +double_to_dec_precise(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DOUBLE); + double d = mem->u.r; + if (d >= 1e38 || d <= -1e38) + return -1; + decimal_t dec; + decimal_from_double(&dec, d); + if (atof(decimal_str(&mem->u.d)) != d) + return -1; + mem->u.d = dec; + mem->type = MEM_TYPE_DEC; + mem->flags = 0; + return 0; +} + +/** + * Cast MEM with DOUBLE to DECIMAL. Doesn't fail. The return value is < 0 if + * the original value is less than the result, > 0 if the original value is + * greater than the result, and 0 if the cast is precise. + */ +static inline int +double_to_dec_forced(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DOUBLE); + double d = mem->u.r; + mem->type = MEM_TYPE_DEC; + mem->flags = 0; + if (d >= 1e38) { + const char *val = "99999999999999999999999999999999999999"; + assert(strlen(val) == 38); + decimal_from_string(&mem->u.d, val); + return 1; + } + if (d <= -1e38) { + const char *val = "-99999999999999999999999999999999999999"; + assert(strlen(val) == 39); + decimal_from_string(&mem->u.d, val); + return -1; + } + decimal_from_double(&mem->u.d, d); + double tmp = atof(decimal_str(&mem->u.d)); + if (d > tmp) + return 1; + if (d < tmp) + return -1; + return 0; +} + static inline int double_to_str0(struct Mem *mem) { @@ -1001,6 +1099,173 @@ double_to_str0(struct Mem *mem) return 0; } +static inline int +dec_to_int(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + if (decimal_is_neg(&mem->u.d)) { + int64_t i; + if (decimal_to_int64(&mem->u.d, &i) == NULL) + return -1; + assert(i < 0); + mem->u.i = i; + mem->type = MEM_TYPE_INT; + mem->flags = 0; + return 0; + } + uint64_t u; + if (decimal_to_uint64(&mem->u.d, &u) == NULL) + return -1; + mem->u.u = u; + mem->type = MEM_TYPE_UINT; + mem->flags = 0; + return 0; +} + +static inline int +dec_to_int_precise(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + if (!decimal_is_int(&mem->u.d)) + return -1; + return dec_to_int(mem); +} + +static inline int +dec_to_int_forced(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + if (decimal_is_neg(&mem->u.d)) { + int64_t i; + mem->type = MEM_TYPE_INT; + mem->flags = 0; + if (decimal_to_int64(&mem->u.d, &i) == NULL) { + mem->u.i = INT64_MIN; + return -1; + } + assert(i < 0); + mem->u.i = i; + /* + * Decimal is floored when cast to int, which means that after + * cast it becomes bigger if it was not integer. + */ + return decimal_is_int(&mem->u.d) ? 0 : -1; + } + uint64_t u; + mem->type = MEM_TYPE_UINT; + mem->flags = 0; + if (decimal_to_uint64(&mem->u.d, &u) == NULL) { + mem->u.u = UINT64_MAX; + return 1; + } + mem->u.u = u; + /* + * Decimal is floored when cast to uint, which means that after cast it + * becomes less if it was not integer. + */ + return decimal_is_int(&mem->u.d) ? 0 : 1; +} + +static inline int +dec_to_uint(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + uint64_t u; + if (decimal_to_uint64(&mem->u.d, &u) == NULL) + return -1; + mem->u.u = u; + mem->type = MEM_TYPE_UINT; + mem->flags = 0; + return 0; +} + +static inline int +dec_to_uint_precise(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + if (!decimal_is_int(&mem->u.d)) + return -1; + return dec_to_uint(mem); +} + +static inline int +dec_to_uint_forced(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + uint64_t u; + mem->type = MEM_TYPE_UINT; + mem->flags = 0; + if (decimal_to_uint64(&mem->u.d, &u) == NULL) { + if (decimal_is_neg(&mem->u.d)) { + mem->u.u = 0; + return -1; + } + mem->u.u = UINT64_MAX; + return 1; + } + mem->u.u = u; + /* + * Decimal is floored when cast to uint, which means that after cast if + * if was not integer it becomes less if it was positive, and move if it + * was negative. + */ + if (decimal_is_int(&mem->u.d)) + return 0; + return decimal_is_neg(&mem->u.d) ? -1 : 1; +} + +static inline int +dec_to_double(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + double r = atof(decimal_str(&mem->u.d)); + mem->u.r = r; + mem->type = MEM_TYPE_DOUBLE; + mem->flags = 0; + return 0; +} + +static inline int +dec_to_double_precise(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + double r = atof(decimal_str(&mem->u.d)); + decimal_t d; + decimal_t *dec = decimal_from_double(&d, r); + if (dec == NULL || decimal_compare(dec, &mem->u.d) != 0) + return -1; + mem->u.r = r; + mem->type = MEM_TYPE_DOUBLE; + mem->flags = 0; + return 0; +} + +static inline int +dec_to_double_forced(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + mem->type = MEM_TYPE_DOUBLE; + mem->flags = 0; + double r = atof(decimal_str(&mem->u.d)); + int res; + decimal_t d; + if (r <= -1e38) + res = 1; + else if (r >= 1e38) + res = -1; + else + res = decimal_compare(&mem->u.d, decimal_from_double(&d, r)); + mem->u.r = r; + return res; +} + +static inline int +dec_to_str0(struct Mem *mem) +{ + assert(mem->type == MEM_TYPE_DEC); + return mem_copy_str0(mem, decimal_str(&mem->u.d)); +} + static inline int bool_to_str0(struct Mem *mem) { @@ -1053,6 +1318,8 @@ mem_to_int(struct Mem *mem) return str_to_int(mem); if (mem->type == MEM_TYPE_DOUBLE) return double_to_int(mem); + if (mem->type == MEM_TYPE_DEC) + return dec_to_int(mem); return -1; } @@ -1068,6 +1335,8 @@ mem_to_int_precise(struct Mem *mem) return str_to_int(mem); if (mem->type == MEM_TYPE_DOUBLE) return double_to_int_precise(mem); + if (mem->type == MEM_TYPE_DEC) + return dec_to_int(mem); return -1; } @@ -1083,6 +1352,8 @@ mem_to_double(struct Mem *mem) return int_to_double(mem); if (mem->type == MEM_TYPE_STR) return str_to_double(mem); + if (mem->type == MEM_TYPE_DEC) + return dec_to_double(mem); return -1; } @@ -1129,6 +1400,8 @@ mem_to_str0(struct Mem *mem) return array_to_str0(mem); case MEM_TYPE_UUID: return uuid_to_str0(mem); + case MEM_TYPE_DEC: + return dec_to_str0(mem); default: return -1; } @@ -1157,6 +1430,8 @@ mem_to_str(struct Mem *mem) return array_to_str0(mem); case MEM_TYPE_UUID: return uuid_to_str0(mem); + case MEM_TYPE_DEC: + return dec_to_str0(mem); default: return -1; } @@ -1177,6 +1452,8 @@ mem_cast_explicit(struct Mem *mem, enum field_type type) return str_to_uint(mem); case MEM_TYPE_DOUBLE: return double_to_uint(mem); + case MEM_TYPE_DEC: + return dec_to_uint(mem); default: return -1; } @@ -1209,9 +1486,21 @@ mem_cast_explicit(struct Mem *mem, enum field_type type) case FIELD_TYPE_NUMBER: return mem_to_number(mem); case FIELD_TYPE_DECIMAL: - if (mem->type == MEM_TYPE_DEC) + switch (mem->type) { + case MEM_TYPE_INT: + return int_to_dec(mem); + case MEM_TYPE_UINT: + return uint_to_dec(mem); + case MEM_TYPE_STR: + return str_to_dec(mem); + case MEM_TYPE_DOUBLE: + return double_to_dec(mem); + case MEM_TYPE_DEC: + mem->flags = 0; return 0; - return -1; + default: + return -1; + } case FIELD_TYPE_UUID: if (mem->type == MEM_TYPE_UUID) { mem->flags = 0; @@ -1252,6 +1541,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) } if (mem->type == MEM_TYPE_DOUBLE) return double_to_uint_precise(mem); + if (mem->type == MEM_TYPE_DEC) + return dec_to_uint_precise(mem); return -1; case FIELD_TYPE_STRING: if (mem->type == MEM_TYPE_STR) { @@ -1268,6 +1559,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) return int_to_double_precise(mem); if (mem->type == MEM_TYPE_UINT) return uint_to_double_precise(mem); + if (mem->type == MEM_TYPE_DEC) + return dec_to_double_precise(mem); return -1; case FIELD_TYPE_INTEGER: if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0) { @@ -1276,6 +1569,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) } if (mem->type == MEM_TYPE_DOUBLE) return double_to_int_precise(mem); + if (mem->type == MEM_TYPE_DEC) + return dec_to_int_precise(mem); return -1; case FIELD_TYPE_BOOLEAN: if (mem->type == MEM_TYPE_BOOL) { @@ -1296,9 +1591,19 @@ mem_cast_implicit(struct Mem *mem, enum field_type type) mem->flags = MEM_Number; return 0; case FIELD_TYPE_DECIMAL: - if (mem->type == MEM_TYPE_DEC) + switch (mem->type) { + case MEM_TYPE_INT: + return int_to_dec(mem); + case MEM_TYPE_UINT: + return uint_to_dec(mem); + case MEM_TYPE_DOUBLE: + return double_to_dec_precise(mem); + case MEM_TYPE_DEC: + mem->flags = 0; return 0; - return -1; + default: + return -1; + } case FIELD_TYPE_MAP: if (mem->type == MEM_TYPE_MAP) return 0; @@ -1343,6 +1648,8 @@ mem_cast_implicit_number(struct Mem *mem, enum field_type type) return -1; case MEM_TYPE_DOUBLE: return double_to_uint_forced(mem); + case MEM_TYPE_DEC: + return dec_to_uint_forced(mem); default: unreachable(); } @@ -1353,6 +1660,8 @@ mem_cast_implicit_number(struct Mem *mem, enum field_type type) return int_to_double_forced(mem); case MEM_TYPE_UINT: return uint_to_double_forced(mem); + case MEM_TYPE_DEC: + return dec_to_double_forced(mem); case MEM_TYPE_DOUBLE: mem->flags = 0; return 0; @@ -1368,6 +1677,23 @@ mem_cast_implicit_number(struct Mem *mem, enum field_type type) return 0; case MEM_TYPE_DOUBLE: return double_to_int_forced(mem); + case MEM_TYPE_DEC: + return dec_to_int_forced(mem); + default: + unreachable(); + } + break; + case FIELD_TYPE_DECIMAL: + switch (mem->type) { + case MEM_TYPE_INT: + return int_to_dec(mem); + case MEM_TYPE_UINT: + return uint_to_dec(mem); + case MEM_TYPE_DEC: + mem->flags = 0; + return 0; + case MEM_TYPE_DOUBLE: + return double_to_dec_forced(mem); default: unreachable(); } diff --git a/test/sql-tap/decimal.test.lua b/test/sql-tap/decimal.test.lua index dd69ca370..3f1c285cf 100755 --- a/test/sql-tap/decimal.test.lua +++ b/test/sql-tap/decimal.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(43) +test:plan(84) local dec = require("decimal") local dec1 = dec.new("111") @@ -425,6 +425,370 @@ test:do_catchsql_test( 1, "Type mismatch: can not convert varbinary(x'31') to number" }) +-- Check that explicit cast from DECIMAL to another types works as intended. +test:do_execsql_test( + "dec-10.1.1", + [[ + SELECT cast(u AS UNSIGNED) FROM t2; + ]], { + 111, 3333, 55555 + }) + +test:do_execsql_test( + "dec-10.1.2", + [[ + SELECT cast(u AS STRING) FROM t2; + ]], { + "111", "3333", "55555" + }) + +test:do_execsql_test( + "dec-10.1.3", + [[ + SELECT cast(u AS NUMBER) FROM t2; + ]], { + dec1, dec3, dec2 + }) + +test:do_execsql_test( + "dec-10.1.4", + [[ + SELECT cast(u AS DOUBLE) FROM t2; + ]], { + 111, 3333, 55555 + }) + +test:do_execsql_test( + "dec-10.1.5", + [[ + SELECT cast(u AS INTEGER) FROM t2; + ]], { + 111, 3333, 55555 + }) + +test:do_catchsql_test( + "dec-10.1.6", + [[ + SELECT cast(u AS BOOLEAN) FROM t2; + ]], { + 1, "Type mismatch: can not convert decimal(111) to boolean" + }) + +test:do_catchsql_test( + "dec-10.1.7", + [[ + SELECT hex(cast(u AS VARBINARY)) FROM t2; + ]], { + 1, "Type mismatch: can not convert decimal(111) to varbinary" + }) + +test:do_execsql_test( + "dec-10.1.8", + [[ + SELECT cast(u AS SCALAR) FROM t2; + ]], { + dec1, dec3, dec2 + }) + +test:do_catchsql_test( + "dec-10.1.9", + [[ + SELECT cast(u AS UUID) FROM t2; + ]], { + 1, "Type mismatch: can not convert decimal(111) to uuid" + }) + +-- Check that explicit cast from another types to DECIMAL works as intended. +test:do_execsql_test( + "dec-10.2.1", + [[ + SELECT cast(111 AS DECIMAL); + ]], { + dec1 + }) + +test:do_catchsql_test( + "dec-10.2.2", + [[ + SELECT cast(x'1234567890abcdef' AS DECIMAL) FROM t2 LIMIT 1; + ]], { + 1, "Type mismatch: can not convert varbinary(x'1234567890ABCDEF') to decimal" + }) + +test:do_execsql_test( + "dec-10.2.3", + [[ + SELECT cast('111' AS DECIMAL); + ]], { + dec1 + }) + +test:do_execsql_test( + "dec-10.2.4", + [[ + SELECT cast(111.0 AS DECIMAL); + ]], { + dec1 + }) + +test:do_execsql_test( + "dec-10.2.5", + [[ + SELECT cast(-1 AS DECIMAL); + ]], { + dec.new(-1) + }) + +test:do_catchsql_test( + "dec-10.2.6", + [[ + SELECT cast(true AS DECIMAL); + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to decimal" + }) + +test:do_catchsql_test( + "dec-10.2.7", + [[ + SELECT cast(cast(x'11111111111111111111111111111111' AS UUID) AS DECIMAL); + ]], { + 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to decimal" + }) + +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 DECIMAL to another types works as intended. +test:do_execsql_test( + "dec-11.1.1", + [[ + INSERT INTO tu(u) SELECT u FROM t2; + SELECT * FROM tu; + ]], { + 1, 111, 2, 3333, 3, 55555 + }) + +test:do_catchsql_test( + "dec-11.1.2", + [[ + INSERT INTO ts(s) SELECT u FROM t2; + ]], { + 1, "Type mismatch: can not convert decimal(111) to string" + }) + +test:do_execsql_test( + "dec-11.1.3", + [[ + INSERT INTO tn(n) SELECT u FROM t2; + SELECT * FROM tn; + ]], { + 1, dec1, 2, dec3, 3, dec2 + }) + +test:do_execsql_test( + "dec-11.1.4", + [[ + INSERT INTO td(d) SELECT u FROM t2; + SELECT * FROM td; + ]], { + 1, 111, 2, 3333, 3, 55555 + }) + +test:do_execsql_test( + "dec-11.1.5", + [[ + INSERT INTO ti(i) SELECT u FROM t2; + SELECT * FROM ti; + ]], { + 1, 111, 2, 3333, 3, 55555 + }) + +test:do_catchsql_test( + "dec-11.1.6", + [[ + INSERT INTO tb(b) SELECT u FROM t2; + ]], { + 1, "Type mismatch: can not convert decimal(111) to boolean" + }) + +test:do_catchsql_test( + "dec-11.1.7", + [[ + INSERT INTO tv(v) SELECT u FROM t2; + ]], { + 1, "Type mismatch: can not convert decimal(111) to varbinary" + }) + +test:do_execsql_test( + "dec-11.1.8", + [[ + INSERT INTO tsc(sc) SELECT u FROM t2; + SELECT * FROM tsc; + ]], { + 1, dec1, 2, dec3, 3, dec2 + }) + +test:do_catchsql_test( + "dec-11.1.9", + [[ + INSERT INTO tuu(uu) SELECT u FROM t2; + ]], { + 1, "Type mismatch: can not convert decimal(111) to uuid" + }) + +-- Check that implicit cast from another types to DECIMAL works as intended. +test:do_catchsql_test( + "dec-11.2.1", + [[ + INSERT INTO tsu VALUES ('1_unsigned', 1); + ]], { + 1, "Type mismatch: can not convert integer(1) to uuid" + }) + +test:do_catchsql_test( + "dec-11.2.2", + [[ + INSERT INTO tsu VALUES ('2_string_right', '11111111-1111-1111-1111-111111111111'); + ]], { + 1, "Type mismatch: can not convert string('11111111-1111-1111-1111-111111111111') to uuid" + }) + +test:do_catchsql_test( + "dec-11.2.3", + [[ + INSERT INTO tsu VALUES ('3_string_wrong', '1'); + ]], { + 1, "Type mismatch: can not convert string('1') to uuid" + }) + +test:do_catchsql_test( + "dec-11.2.4", + [[ + INSERT INTO tsu VALUES ('4_double', 1.5); + ]], { + 1, "Type mismatch: can not convert double(1.5) to uuid" + }) + +test:do_catchsql_test( + "dec-11.2.5", + [[ + INSERT INTO tsu VALUES ('5_integer', -1); + ]], { + 1, "Type mismatch: can not convert integer(-1) to uuid" + }) + +test:do_catchsql_test( + "dec-11.2.6", + [[ + INSERT INTO tsu VALUES ('6_boolean', true); + ]], { + 1, "Type mismatch: can not convert boolean(TRUE) to uuid" + }) + +test:do_catchsql_test( + "dec-11.2.7", + [[ + INSERT INTO tsu SELECT '7_varbinary', x'11111111111111111111111111111111' FROM t2 LIMIT 1; + ]], { + 1, "Type mismatch: can not convert varbinary(x'11111111111111111111111111111111') to uuid" + }) + +test:do_catchsql_test( + "dec-11.2.8", + [[ + INSERT INTO tsu VALUES ('8_varbinary', x'1234567890abcdef'); + ]], { + 1, "Type mismatch: can not convert varbinary(x'1234567890ABCDEF') to uuid" + }) + +-- Check that LIMIT accepts DECIMAL as argument. +test:do_execsql_test( + "dec-12.1", + [[ + SELECT 1 LIMIT (SELECT u FROM t1 LIMIT 1); + ]], { + 1 + }) + +-- Check that OFFSET accepts DECIMAL as argument. +test:do_execsql_test( + "dec-12.2", + [[ + SELECT 1 LIMIT 1 OFFSET (SELECT u FROM t1 LIMIT 1); + ]], { + }) + +-- Check that other numeric values could be used to search in DECIMAL index. +test:do_execsql_test( + "dec-13.1.1", + [[ + SELECT * FROM t2 WHERE u > 123; + ]], { + dec3, dec2 + }) + +test:do_execsql_test( + "dec-13.1.2", + [[ + SELECT * FROM t2 WHERE u < 123.5; + ]], { + dec1 + }) + +test:execsql([[ + CREATE TABLE t13i (i INTEGER PRIMARY KEY); + CREATE TABLE t13u (u UNSIGNED PRIMARY KEY); + CREATE TABLE t13d (d DOUBLE PRIMARY KEY); + CREATE TABLE t13n (n NUMBER PRIMARY KEY); + INSERT INTO t13i VALUES (1), (1000); + INSERT INTO t13u VALUES (1), (1000); + INSERT INTO t13d VALUES (1), (1000); + INSERT INTO t13n VALUES (1), (1000); +]]) + +-- Check that DECIMAL values could be used to search in other numeric indexes. +test:do_execsql_test( + "dec-13.2.1", + [[ + SELECT * FROM t13i WHERE CAST(111 AS DECIMAL) > i; + ]], { + 1 + }) + +test:do_execsql_test( + "dec-13.2.2", + [[ + SELECT * FROM t13u WHERE CAST(111 AS DECIMAL) < u; + ]], { + 1000 + }) + +test:do_execsql_test( + "dec-13.2.3", + [[ + SELECT * FROM t13d WHERE CAST(111 AS DECIMAL) > d; + ]], { + 1 + }) + +test:do_execsql_test( + "dec-13.2.4", + [[ + SELECT * FROM t13n WHERE CAST(111 AS DECIMAL) < n; + ]], { + 1000 + }) + test:execsql([[ DROP TRIGGER t; DROP VIEW v; -- 2.25.1