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 8D6B36EC5D; Thu, 19 Aug 2021 15:06:59 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 8D6B36EC5D DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1629374819; bh=oKmR57fqf3EI80zlb/WUgnP25glzXjiHAkG/7EgO+WI=; 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=GGEAXecBhS7gR6HdFCIT6BE2vTcVxWulN2kWAlPfbhkKu9EAYsfuxh2w637jT+4u5 jQYTFz4n114cZTX242yLieqleAx/ZQbvTPvsALmAoGa+f4vjlCSMwix8q1dAcln2wm G0hHd9x9Ndv6k1Teoart6m342tlw6hHRfrDSbZWU= Received: from smtpng1.i.mail.ru (smtpng1.i.mail.ru [94.100.181.251]) (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 C81496EC60 for ; Thu, 19 Aug 2021 15:03:14 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org C81496EC60 Received: by smtpng1.m.smailru.net with esmtpa (envelope-from ) id 1mGglN-0006bW-He; Thu, 19 Aug 2021 15:03:14 +0300 To: kyukhin@tarantool.org, v.ioffe@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Thu, 19 Aug 2021 15:03:13 +0300 Message-Id: <8896d06759a1a6d3c1e8e6312d0659eef1572d4d.1629374449.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-4EC0790: 10 X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD92087353F0EC44DD972FF4A7D76DB5E242D14FEF1BD8BF4AC182A05F538085040457E4206AE731E191DA2F3285764644861094EAD5B384E297D053E0A8482BD2D X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE72847AA60176ABEF3EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F790063770F3384661E7DC4D8638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D87CD32B5D716696C782EF20F1AA551099117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BAA867293B0326636D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B6300D3B61E77C8D3B089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975CF160826E4E1956AEF89EB6A3A338338A71998D55A36A72379C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EF836F5ADB0B4F9314699F904B3F4130E343918A1A30D5E7FCCB5012B2E24CD356 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D344E332383F80D58BB0C2FEDB00FA1004C5B33393C627A342C538A5F4FC8914BB2830CA3FB0448C2B31D7E09C32AA3244CDD01EF2D2E8D95AF65F8393376C7C155A995755A1445935EFACE5A9C96DEB163 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojGSxK+6r6oBEBHALgxBHxZw== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D8D2C572E6BEC70E7D625395E5D91792E83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 8/9] sql: check argument types of sum(), avg(), total() 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 enables static and dynamic type check for aggregate functions SUM(), AVG() and TOTAL(). Part of #6105 --- src/box/sql/func.c | 88 +++++++++++------------- test/sql-tap/built-in-functions.test.lua | 62 ++++++++++++++++- test/sql-tap/e_select1.test.lua | 2 +- test/sql-tap/func.test.lua | 80 ++------------------- test/sql-tap/minmax2.test.lua | 2 +- test/sql-tap/minmax4.test.lua | 2 +- test/sql-tap/null.test.lua | 2 +- test/sql-tap/select1.test.lua | 2 +- test/sql-tap/select3.test.lua | 14 ++-- test/sql-tap/select5.test.lua | 2 +- test/sql-tap/select6.test.lua | 21 +++--- test/sql-tap/selectG.test.lua | 2 +- test/sql-tap/subquery.test.lua | 8 +-- test/sql-tap/uuid.test.lua | 6 +- test/sql/boolean.result | 6 +- test/sql/types.result | 20 +++--- 16 files changed, 150 insertions(+), 169 deletions(-) diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 56ccaea9d..498e346f5 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -1624,13 +1624,8 @@ soundexFunc(sql_context * context, int argc, sql_value ** argv) */ typedef struct SumCtx SumCtx; struct SumCtx { - double rSum; /* Floating point sum */ - int64_t iSum; /* Integer sum */ - /** True if iSum < 0. */ - bool is_neg; - i64 cnt; /* Number of elements summed */ - u8 overflow; /* True if integer overflow seen */ - u8 approx; /* True if non-integer value was input to the sum */ + struct Mem mem; + uint32_t count; }; /* @@ -1649,34 +1644,25 @@ sum_step(struct sql_context *context, int argc, sql_value **argv) assert(argc == 1); UNUSED_PARAMETER(argc); struct SumCtx *p = sql_aggregate_context(context, sizeof(*p)); - int type = sql_value_type(argv[0]); - if (type == MP_NIL || p == NULL) + if (p == NULL) { + context->is_aborted = true; return; - if (type != MP_DOUBLE && type != MP_INT && type != MP_UINT) { - if (type != MP_STR || mem_to_number(argv[0]) != 0) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - mem_str(argv[0]), "number"); - context->is_aborted = true; - return; - } - type = sql_value_type(argv[0]); } - p->cnt++; - if (type == MP_INT || type == MP_UINT) { - int64_t v = mem_get_int_unsafe(argv[0]); - if (type == MP_INT) - p->rSum += v; + if (p->count == 0) { + mem_create(&p->mem); + assert(context->func->def->returns == FIELD_TYPE_INTEGER || + context->func->def->returns == FIELD_TYPE_DOUBLE); + if (context->func->def->returns == FIELD_TYPE_INTEGER) + mem_set_uint(&p->mem, 0); else - p->rSum += (uint64_t) v; - if ((p->approx | p->overflow) == 0 && - sql_add_int(p->iSum, p->is_neg, v, type == MP_INT, &p->iSum, - &p->is_neg) != 0) { - p->overflow = 1; - } - } else { - p->rSum += mem_get_double_unsafe(argv[0]); - p->approx = 1; + mem_set_double(&p->mem, 0.0); } + if (argv[0]->type == MEM_TYPE_NULL) + return; + ++p->count; + assert(mem_is_num(argv[0])); + if (mem_add(&p->mem, argv[0], &p->mem) != 0) + context->is_aborted = true; } static void @@ -1684,17 +1670,10 @@ sumFinalize(sql_context * context) { SumCtx *p; p = sql_aggregate_context(context, 0); - if (p && p->cnt > 0) { - if (p->overflow) { - diag_set(ClientError, ER_SQL_EXECUTE, "integer "\ - "overflow"); - context->is_aborted = true; - } else if (p->approx) { - sql_result_double(context, p->rSum); - } else { - mem_set_int(context->pOut, p->iSum, p->is_neg); - } - } + if (p == NULL || p->count == 0) + mem_set_null(context->pOut); + else + mem_copy_as_ephemeral(context->pOut, &p->mem); } static void @@ -1702,9 +1681,15 @@ avgFinalize(sql_context * context) { SumCtx *p; p = sql_aggregate_context(context, 0); - if (p && p->cnt > 0) { - sql_result_double(context, p->rSum / (double)p->cnt); + if (p == NULL || p->count == 0) { + mem_set_null(context->pOut); + return; } + struct Mem mem; + mem_create(&mem); + mem_set_uint(&mem, p->count); + if (mem_div(&p->mem, &mem, context->pOut) != 0) + context->is_aborted = true; } static void @@ -1712,7 +1697,10 @@ totalFinalize(sql_context * context) { SumCtx *p; p = sql_aggregate_context(context, 0); - sql_result_double(context, p ? p->rSum : (double)0); + if (p == NULL || p->count == 0) + mem_set_double(context->pOut, 0.0); + else + mem_copy_as_ephemeral(context->pOut, &p->mem); } /* @@ -2005,7 +1993,8 @@ struct sql_func_definition { static struct sql_func_definition definitions[] = { {"ABS", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, absFunc, NULL}, {"ABS", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, absFunc, NULL}, - {"AVG", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step, avgFinalize}, + {"AVG", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, sum_step, avgFinalize}, + {"AVG", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, sum_step, avgFinalize}, {"CHAR", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, charFunc, NULL}, {"CHAR_LENGTH", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, lengthFunc, NULL}, @@ -2110,8 +2099,11 @@ static struct sql_func_definition definitions[] = { NULL}, {"SUBSTR", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_STRING, substrFunc, NULL}, - {"SUM", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step, sumFinalize}, - {"TOTAL", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step, + {"SUM", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, sum_step, sumFinalize}, + {"SUM", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, sum_step, sumFinalize}, + {"TOTAL", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_DOUBLE, sum_step, + totalFinalize}, + {"TOTAL", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, sum_step, totalFinalize}, {"TRIM", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_STRING, trim_func, NULL}, diff --git a/test/sql-tap/built-in-functions.test.lua b/test/sql-tap/built-in-functions.test.lua index 353030278..6fae811dc 100755 --- a/test/sql-tap/built-in-functions.test.lua +++ b/test/sql-tap/built-in-functions.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(46) +test:plan(52) -- -- Make sure that number of arguments check is checked properly for SQL built-in @@ -438,6 +438,66 @@ test:do_test( "Type mismatch: can not convert string('1') to integer" }) +test:do_catchsql_test( + "builtins-2.33", + [[ + SELECT SUM('1'); + ]], + { + 1, [[Failed to execute SQL statement: ]].. + [[wrong arguments for function SUM()]] + } +) + +test:do_test( + "builtins-2.34", + function() + local res = {pcall(box.execute, [[SELECT SUM(?);]], {'1'})} + return {tostring(res[3])} + end, { + "Type mismatch: can not convert string('1') to integer" + }) + +test:do_catchsql_test( + "builtins-2.35", + [[ + SELECT AVG('1'); + ]], + { + 1, [[Failed to execute SQL statement: ]].. + [[wrong arguments for function AVG()]] + } +) + +test:do_test( + "builtins-2.36", + function() + local res = {pcall(box.execute, [[SELECT AVG(?);]], {'1'})} + return {tostring(res[3])} + end, { + "Type mismatch: can not convert string('1') to integer" + }) + +test:do_catchsql_test( + "builtins-2.37", + [[ + SELECT TOTAL('1'); + ]], + { + 1, [[Failed to execute SQL statement: ]].. + [[wrong arguments for function TOTAL()]] + } +) + +test:do_test( + "builtins-2.38", + function() + local res = {pcall(box.execute, [[SELECT TOTAL(?);]], {'1'})} + return {tostring(res[3])} + end, { + "Type mismatch: can not convert string('1') to integer" + }) + -- -- Make sure that the type of result of MAX() and MIN() is the same as the type -- of the argument. diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua index 27bd44ac4..da4db5a55 100755 --- a/test/sql-tap/e_select1.test.lua +++ b/test/sql-tap/e_select1.test.lua @@ -1181,7 +1181,7 @@ test:do_select_tests( {"2", "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)", {54, 36, 27, 21, 39, 28}}, {"3", "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)", {80, 36, 40, 21}}, {"4", "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)", {81, 37, 41, 22}}, - {"5", "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2", {3, 4.33, 1, 2.0}}, + {"5", "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2", {3, 4, 1, 2.0}}, }) -- EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua index 9a192974b..8bb20bc79 100755 --- a/test/sql-tap/func.test.lua +++ b/test/sql-tap/func.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(14686) +test:plan(14682) --!./tcltestrunner.lua -- 2001 September 15 @@ -831,7 +831,7 @@ test:do_test( ]]) end, { -- - 68236, 3, 22745.33, 1, 67890, 5 + 68236, 3, 22745, 1, 67890, 5 -- }) @@ -849,76 +849,6 @@ test:do_execsql_test( -- }) --- ifcapable tempdb { --- do_test func-8.3 { --- execsql { --- CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; --- SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; --- } --- } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} --- } else { --- do_test func-8.3 { --- execsql { --- CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC; --- SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; --- } --- } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} --- } --- do_test func-8.4 { --- execsql { --- SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3; --- } --- } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP} -test:do_execsql_test( - "func-8.5", - [[ - SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x - UNION ALL SELECT -9223372036854775807) - ]], { - -- - 0 - -- - }) - -test:do_execsql_test( - "func-8.6", - [[ - SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x - UNION ALL SELECT -9223372036854775807) - ]], { - -- - "integer" - -- - }) - -test:do_execsql_test( - "func-8.7", - [[ - SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x - UNION ALL SELECT -9223372036854775807) - ]], { - -- - "integer" - -- - }) - -test:do_execsql_test( - "func-8.8", - [[ - SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x - UNION ALL SELECT -9223372036850000000) - ]], { - -- - true - -- - }) - - - - - - - -- How do you test the random() function in a meaningful, deterministic way? -- test:do_execsql_test( @@ -1657,7 +1587,7 @@ test:do_catchsql_test( UNION ALL SELECT 10 AS x); ]], { -- - 1, "Failed to execute SQL statement: integer overflow" + 1, "Failed to execute SQL statement: integer is overflowed" -- }) @@ -1669,7 +1599,7 @@ test:do_catchsql_test( SELECT -10 AS x); ]], { -- - 1, "Failed to execute SQL statement: integer overflow" + 1, "Failed to execute SQL statement: integer is overflowed" -- }) @@ -2892,7 +2822,7 @@ test:do_catchsql_test( SELECT SUM(X'FF') ]], { -- - 1, "Type mismatch: can not convert varbinary(x'FF') to number" + 1, "Failed to execute SQL statement: wrong arguments for function SUM()" -- }) diff --git a/test/sql-tap/minmax2.test.lua b/test/sql-tap/minmax2.test.lua index 0b9f5a63c..973187e42 100755 --- a/test/sql-tap/minmax2.test.lua +++ b/test/sql-tap/minmax2.test.lua @@ -290,7 +290,7 @@ test:do_execsql_test( test:do_execsql_test( "minmax2-4.2", [[ - SELECT y, coalesce(sum(x),0) FROM + SELECT y, coalesce(sum(CAST(x AS INTEGER)),0) FROM (SELECT null AS x, y+1 AS y FROM t1 UNION SELECT x, y FROM t1) GROUP BY y ORDER BY y; ]], { diff --git a/test/sql-tap/minmax4.test.lua b/test/sql-tap/minmax4.test.lua index 3a71462e0..392e0798d 100755 --- a/test/sql-tap/minmax4.test.lua +++ b/test/sql-tap/minmax4.test.lua @@ -251,7 +251,7 @@ test:do_test( ]] end, { -- - 2, 3, 3.0, 1, 5, 1, 1, 1.5, 2, 4 + 2, 3, 3, 1, 5, 1, 1, 1, 2, 4 -- }) diff --git a/test/sql-tap/null.test.lua b/test/sql-tap/null.test.lua index ca20e3558..d01761a1f 100755 --- a/test/sql-tap/null.test.lua +++ b/test/sql-tap/null.test.lua @@ -157,7 +157,7 @@ test:do_execsql_test( avg(b), avg(c), min(b), max(b) from t1; ]], { -- - 7, 4, 6, 2, 3, 0.5, 0.5, 0, 1 + 7, 4, 6, 2, 3, 0, 0, 0, 1 -- }) diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua index ba12470f3..e89c7d2f8 100755 --- a/test/sql-tap/select1.test.lua +++ b/test/sql-tap/select1.test.lua @@ -515,7 +515,7 @@ test:do_catchsql_test( SELECT sum(a) FROM t3 ]], { -- - 1, "Type mismatch: can not convert string('abc') to number" + 1, "Failed to execute SQL statement: wrong arguments for function SUM()" -- }) diff --git a/test/sql-tap/select3.test.lua b/test/sql-tap/select3.test.lua index 9880c1f69..535a72167 100755 --- a/test/sql-tap/select3.test.lua +++ b/test/sql-tap/select3.test.lua @@ -69,7 +69,7 @@ test:do_execsql_test("select3-1.3", [[ SELECT max(n)/avg(n), max(log)/avg(log) FROM t1 ]], { -- - 1.9375, 1.25 + 1, 1 -- }) @@ -95,7 +95,7 @@ test:do_execsql_test("select3-2.3.1", [[ SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log ]], { -- - 0, 1.0, 1, 2.0, 2, 3.5, 3, 6.5, 4, 12.5, 5, 24.0 + 0, 1, 1, 2, 2, 3, 3, 6, 4, 12, 5, 24 -- }) @@ -103,7 +103,7 @@ test:do_execsql_test("select3-2.3.2", [[ SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log ]], { -- - 0, 2.0, 1, 3.0, 2, 4.5, 3, 7.5, 4, 13.5, 5, 25.0 + 0, 2, 1, 3, 2, 4, 3, 7, 4, 13, 5, 25 -- }) @@ -111,7 +111,7 @@ test:do_execsql_test("select3-2.4", [[ SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log ]], { -- - 0, 0.0, 1, 0.0, 2, 0.5, 3, 1.5, 4, 3.5, 5, 7.0 + 0, 0, 1, 0, 2, 0, 3, 1, 4, 3, 5, 7 -- }) @@ -119,7 +119,7 @@ test:do_execsql_test("select3-2.5", [[ SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log ]], { -- - 1, 0.0, 3, 0.0, 5, 0.5, 7, 1.5, 9, 3.5, 11, 7.0 + 1, 0, 3, 0, 5, 0, 7, 1, 9, 3, 11, 7 -- }) @@ -264,7 +264,7 @@ test:do_execsql_test("select3-5.1", [[ ORDER BY max(n+log*2)+0, avg(n)+0 ]], { -- - 0, 1, 1.0, 1, 1, 1, 2.0, 4, 2, 2, 3.5, 8, 3, 4, 6.5, 14, 4, 8, 12.5, 24, 5, 15, 24.0, 41 + 0, 1, 1, 1, 1, 1, 2, 4, 2, 2, 3, 8, 3, 4, 6, 14, 4, 8, 12, 24, 5, 15, 24, 41 -- }) @@ -274,7 +274,7 @@ test:do_execsql_test("select3-5.2", [[ ORDER BY max(n+log*2)+0, CAST(GREATEST(log,avg(n)) AS DOUBLE)+0 ]], { -- - 0, 1, 1.0, 1, 1, 1, 2.0, 4, 2, 2, 3.5, 8, 3, 4, 6.5, 14, 4, 8, 12.5, 24, 5, 15, 24.0, 41 + 0, 1, 1, 1, 1, 1, 2, 4, 2, 2, 3, 8, 3, 4, 6, 14, 4, 8, 12, 24, 5, 15, 24, 41 -- }) diff --git a/test/sql-tap/select5.test.lua b/test/sql-tap/select5.test.lua index b04c9af09..b25bf0d59 100755 --- a/test/sql-tap/select5.test.lua +++ b/test/sql-tap/select5.test.lua @@ -558,7 +558,7 @@ test:do_catchsql_test( SELECT 1 FROM jj HAVING avg(s2) = 1 AND avg(s2) = 0; ]], { -- - 1, "Type mismatch: can not convert string('A') to number" + 1, "Failed to execute SQL statement: wrong arguments for function AVG()" -- }) diff --git a/test/sql-tap/select6.test.lua b/test/sql-tap/select6.test.lua index 8ad91390a..031892112 100755 --- a/test/sql-tap/select6.test.lua +++ b/test/sql-tap/select6.test.lua @@ -312,7 +312,7 @@ test:do_execsql_test( SELECT a,b,a+b FROM (SELECT avg(x) as a, avg(y) as b FROM t1) ]], { -- - 10.5, 3.7, 14.2 + 10, 3, 13 -- }) @@ -322,7 +322,7 @@ test:do_execsql_test( SELECT a,b,a+b FROM (SELECT avg(x) as a, avg(y) as b FROM t1 WHERE y=4) ]], { -- - 11.5, 4.0, 15.5 + 11, 4, 15 -- }) @@ -332,7 +332,7 @@ test:do_execsql_test( SELECT x,y,x+y FROM (SELECT avg(a) as x, avg(b) as y FROM t2 WHERE a=4) ]], { -- - 4.0, 3.0, 7.0 + 4, 3, 7 -- }) @@ -343,7 +343,6 @@ test:do_execsql_test( WHERE a>10 ]], { -- - 10.5, 3.7, 14.2 -- }) @@ -365,7 +364,7 @@ test:do_execsql_test( WHERE a>10 ]], { -- - 11.5, 4.0, 15.5 + 11, 4, 15 -- }) @@ -387,7 +386,7 @@ test:do_execsql_test( ORDER BY a ]], { -- - 1.0, 1, 2.0, 2.5, 2, 4.5, 5.5, 3, 8.5, 11.5, 4, 15.5, 18.0, 5, 23.0 + 1, 1, 2, 2, 2, 4, 5, 3, 8, 11, 4, 15, 18, 5, 23 -- }) @@ -399,7 +398,7 @@ test:do_execsql_test( WHERE b<4 ORDER BY a ]], { -- - 1.0, 1, 2.0, 2.5, 2, 4.5, 5.5, 3, 8.5 + 1, 1, 2, 2, 2, 4, 5, 3, 8 -- }) @@ -411,7 +410,7 @@ test:do_execsql_test( WHERE b<4 ORDER BY a ]], { -- - 2.5, 2, 4.5, 5.5, 3, 8.5 + 2, 2, 4, 5, 3, 8 -- }) @@ -423,7 +422,7 @@ test:do_execsql_test( ORDER BY a ]], { -- - 2.5, 2, 4.5, 5.5, 3, 8.5, 11.5, 4, 15.5, 18.0, 5, 23.0 + 2, 2, 4, 5, 3, 8, 11, 4, 15, 18, 5, 23 -- }) @@ -487,7 +486,7 @@ test:do_execsql_test( SELECT avg(y) FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y ]], { -- - 2.5 + 2 -- }) @@ -497,7 +496,7 @@ test:do_execsql_test( SELECT avg(y) FROM (SELECT DISTINCT y FROM t1 WHERE y<5) ORDER BY y ]], { -- - 2.5 + 2 -- }) diff --git a/test/sql-tap/selectG.test.lua b/test/sql-tap/selectG.test.lua index d416381bd..0690b4c86 100755 --- a/test/sql-tap/selectG.test.lua +++ b/test/sql-tap/selectG.test.lua @@ -48,7 +48,7 @@ test:do_test( :format(end_time - start_time, time_quota)) end, { -- <100> - 100000, 5000050000, 50000.5, true + 100000, 5000050000, 50000, true -- }) diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua index 1c5b3d02e..094269a5a 100755 --- a/test/sql-tap/subquery.test.lua +++ b/test/sql-tap/subquery.test.lua @@ -466,7 +466,7 @@ test:do_execsql_test( HAVING avg(a.y) > avg(b.y)); ]], { -- - 107, 4.0 + 106, 4, 107, 4 -- }) @@ -482,7 +482,7 @@ test:do_execsql_test( HAVING avg1 > avg2); ]], { -- - 107, 4.0 + 106, 4, 107, 4 -- }) @@ -505,7 +505,7 @@ test:do_execsql_test( ORDER BY a.x; ]], { -- - 106, 4.5, false, true, 107, 4.0, true, false + 106, 4, true, false, 107, 4, true, false -- }) @@ -517,7 +517,7 @@ test:do_execsql_test( SELECT max((SELECT avg(y) FROM t35b)) FROM t35a; ]], { -- - 98.5 + 98 -- }) diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua index 140059262..4ca4d4037 100755 --- a/test/sql-tap/uuid.test.lua +++ b/test/sql-tap/uuid.test.lua @@ -237,7 +237,7 @@ test:do_catchsql_test( [[ SELECT AVG(u) from t2; ]], { - 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number" + 1, "Failed to execute SQL statement: wrong arguments for function AVG()" }) test:do_catchsql_test( @@ -441,7 +441,7 @@ test:do_catchsql_test( [[ SELECT SUM(u) from t2; ]], { - 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number" + 1, "Failed to execute SQL statement: wrong arguments for function SUM()" }) test:do_catchsql_test( @@ -449,7 +449,7 @@ test:do_catchsql_test( [[ SELECT TOTAL(u) from t2; ]], { - 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number" + 1, "Failed to execute SQL statement: wrong arguments for function TOTAL()" }) test:do_execsql_test( diff --git a/test/sql/boolean.result b/test/sql/boolean.result index a9fa642f2..55c3820d1 100644 --- a/test/sql/boolean.result +++ b/test/sql/boolean.result @@ -327,7 +327,7 @@ SELECT typeof(a) FROM t0; SELECT AVG(a) FROM t0; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Failed to execute SQL statement: wrong arguments for function AVG()' | ... SELECT MIN(a) FROM t0; | --- @@ -348,7 +348,7 @@ SELECT MAX(a) FROM t0; SELECT SUM(a) FROM t0; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Failed to execute SQL statement: wrong arguments for function SUM()' | ... SELECT COUNT(a) FROM t0; | --- @@ -361,7 +361,7 @@ SELECT COUNT(a) FROM t0; SELECT TOTAL(a) FROM t0; | --- | - null - | - 'Type mismatch: can not convert boolean(FALSE) to number' + | - 'Failed to execute SQL statement: wrong arguments for function TOTAL()' | ... SELECT GROUP_CONCAT(a, ' +++ ') FROM t0; | --- diff --git a/test/sql/types.result b/test/sql/types.result index cdef9646e..bf999be68 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -761,7 +761,7 @@ box.execute("SELECT sum(i) FROM t;") --- - metadata: - name: COLUMN_1 - type: number + type: integer rows: - [18446744073709551613] ... @@ -769,15 +769,15 @@ box.execute("SELECT avg(i) FROM t;") --- - metadata: - name: COLUMN_1 - type: number + type: integer rows: - - [6148914691236516864] + - [6148914691236517204] ... box.execute("SELECT total(i) FROM t;") --- - metadata: - name: COLUMN_1 - type: number + type: double rows: - [1.844674407371e+19] ... @@ -1239,17 +1239,17 @@ box.execute("SELECT * FROM t WHERE v = x'616263'") box.execute("SELECT sum(v) FROM t;") --- - null -- 'Type mismatch: can not convert varbinary(x''616263'') to number' +- 'Failed to execute SQL statement: wrong arguments for function SUM()' ... box.execute("SELECT avg(v) FROM t;") --- - null -- 'Type mismatch: can not convert varbinary(x''616263'') to number' +- 'Failed to execute SQL statement: wrong arguments for function AVG()' ... box.execute("SELECT total(v) FROM t;") --- - null -- 'Type mismatch: can not convert varbinary(x''616263'') to number' +- 'Failed to execute SQL statement: wrong arguments for function TOTAL()' ... box.execute("SELECT min(v) FROM t;") --- @@ -1795,7 +1795,7 @@ box.execute("SELECT sum(d) FROM t;") --- - metadata: - name: COLUMN_1 - type: number + type: double rows: - [18000000000000000000] ... @@ -1803,7 +1803,7 @@ box.execute("SELECT avg(d) FROM t;") --- - metadata: - name: COLUMN_1 - type: number + type: double rows: - [4500000000000000000] ... @@ -1811,7 +1811,7 @@ box.execute("SELECT total(d) FROM t;") --- - metadata: - name: COLUMN_1 - type: number + type: double rows: - [18000000000000000000] ... -- 2.25.1