Tarantool development patches archive
 help / color / mirror / Atom feed
From: Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org>
To: vdavydov@tarantool.org
Cc: tarantool-patches@dev.tarantool.org
Subject: [Tarantool-patches] [PATCH v1 08/10] sql: check argument types of sum(), avg(), total()
Date: Fri, 13 Aug 2021 06:17:24 +0300	[thread overview]
Message-ID: <b3e67a5ccebe33c4b1f4703d60ce22808bcfab72.1628824421.git.imeevma@gmail.com> (raw)
In-Reply-To: <cover.1628824421.git.imeevma@gmail.com>

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 7ecbb6203..8d9c5a7d9 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -1633,13 +1633,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;
 };
 
 /*
@@ -1658,34 +1653,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
@@ -1693,17 +1679,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
@@ -1711,9 +1690,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
@@ -1721,7 +1706,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);
 }
 
 /*
@@ -2014,7 +2002,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},
@@ -2119,8 +2108,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, {
         -- <func-8.1>
-        68236, 3, 22745.33, 1, 67890, 5
+        68236, 3, 22745, 1, 67890, 5
         -- </func-8.1>
     })
 
@@ -849,76 +849,6 @@ test:do_execsql_test(
         -- </func-8.2>
     })
 
--- 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)
-    ]], {
-        -- <func-8.5>
-        0
-        -- </func-8.5>
-    })
-
-test:do_execsql_test(
-    "func-8.6",
-    [[
-        SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
-                            UNION ALL SELECT -9223372036854775807)
-    ]], {
-        -- <func-8.6>
-        "integer"
-        -- </func-8.6>
-    })
-
-test:do_execsql_test(
-    "func-8.7",
-    [[
-        SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
-                            UNION ALL SELECT -9223372036854775807)
-    ]], {
-        -- <func-8.7>
-        "integer"
-        -- </func-8.7>
-    })
-
-test:do_execsql_test(
-    "func-8.8",
-    [[
-        SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
-                            UNION ALL SELECT -9223372036850000000)
-    ]], {
-        -- <func-8.8>
-        true
-        -- </func-8.8>
-    })
-
-
-
-
-
-
-
 -- 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);
     ]], {
     -- <func-18.15.2>
-    1, "Failed to execute SQL statement: integer overflow"
+    1, "Failed to execute SQL statement: integer is overflowed"
     -- </func-18.15.2>
 })
 
@@ -1669,7 +1599,7 @@ test:do_catchsql_test(
             SELECT -10 AS x);
     ]], {
         -- <func-18.18>
-        1, "Failed to execute SQL statement: integer overflow"
+        1, "Failed to execute SQL statement: integer is overflowed"
         -- </func-18.18>
     })
 
@@ -2892,7 +2822,7 @@ test:do_catchsql_test(
         SELECT SUM(X'FF')
     ]], {
         -- <func-76.4>
-        1, "Type mismatch: can not convert varbinary(x'FF') to number"
+        1, "Failed to execute SQL statement: wrong arguments for function SUM()"
         -- </func-76.4>
     })
 
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, {
         -- <minmax4-2.3>
-        2, 3, 3.0, 1, 5, 1, 1, 1.5, 2, 4
+        2, 3, 3, 1, 5, 1, 1, 1, 2, 4
         -- </minmax4-2.3>
     })
 
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;
     ]], {
         -- <null-3.1>
-        7, 4, 6, 2, 3, 0.5, 0.5, 0, 1
+        7, 4, 6, 2, 3, 0, 0, 0, 1
         -- </null-3.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
     ]], {
         -- <select1-2.17.1>
-        1, "Type mismatch: can not convert string('abc') to number"
+        1, "Failed to execute SQL statement: wrong arguments for function SUM()"
         -- </select1-2.17.1>
     })
 
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
 ]], {
   -- <select3-1.3>
-  1.9375, 1.25
+  1, 1
   -- </select3-1.3>
 })
 
@@ -95,7 +95,7 @@ test:do_execsql_test("select3-2.3.1", [[
   SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log
 ]], {
   -- <select3-2.3.1>
-  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
   -- </select3-2.3.1>
 })
 
@@ -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
 ]], {
   -- <select3-2.3.2>
-  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
   -- </select3-2.3.2>
 })
 
@@ -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
 ]], {
   -- <select3-2.4>
-  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
   -- </select3-2.4>
 })
 
@@ -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
 ]], {
   -- <select3-2.5>
-  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
   -- </select3-2.5>
 })
 
@@ -264,7 +264,7 @@ test:do_execsql_test("select3-5.1", [[
   ORDER BY max(n+log*2)+0, avg(n)+0
 ]], {
   -- <select3-5.1>
-  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
   -- </select3-5.1>
 })
 
@@ -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
 ]], {
   -- <select3-5.2>
-  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
   -- </select3-5.2>
 })
 
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;
     ]], {
     -- <select5-9.13.2>
-    1, "Type mismatch: can not convert string('A') to number"
+    1, "Failed to execute SQL statement: wrong arguments for function AVG()"
     -- </select5-9.13.2>
 })
 
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)
     ]], {
         -- <select6-3.3>
-        10.5, 3.7, 14.2
+        10, 3, 13
         -- </select6-3.3>
     })
 
@@ -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)
     ]], {
         -- <select6-3.4>
-        11.5, 4.0, 15.5
+        11, 4, 15
         -- </select6-3.4>
     })
 
@@ -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)
     ]], {
         -- <select6-3.5>
-        4.0, 3.0, 7.0
+        4, 3, 7
         -- </select6-3.5>
     })
 
@@ -343,7 +343,6 @@ test:do_execsql_test(
         WHERE a>10
     ]], {
         -- <select6-3.6>
-        10.5, 3.7, 14.2
         -- </select6-3.6>
     })
 
@@ -365,7 +364,7 @@ test:do_execsql_test(
         WHERE a>10
     ]], {
         -- <select6-3.8>
-        11.5, 4.0, 15.5
+        11, 4, 15
         -- </select6-3.8>
     })
 
@@ -387,7 +386,7 @@ test:do_execsql_test(
         ORDER BY a
     ]], {
         -- <select6-3.10>
-        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
         -- </select6-3.10>
     })
 
@@ -399,7 +398,7 @@ test:do_execsql_test(
         WHERE b<4 ORDER BY a
     ]], {
         -- <select6-3.11>
-        1.0, 1, 2.0, 2.5, 2, 4.5, 5.5, 3, 8.5
+        1, 1, 2, 2, 2, 4, 5, 3, 8
         -- </select6-3.11>
     })
 
@@ -411,7 +410,7 @@ test:do_execsql_test(
         WHERE b<4 ORDER BY a
     ]], {
         -- <select6-3.12>
-        2.5, 2, 4.5, 5.5, 3, 8.5
+        2, 2, 4, 5, 3, 8
         -- </select6-3.12>
     })
 
@@ -423,7 +422,7 @@ test:do_execsql_test(
         ORDER BY a
     ]], {
         -- <select6-3.13>
-        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
         -- </select6-3.13>
     })
 
@@ -487,7 +486,7 @@ test:do_execsql_test(
         SELECT avg(y) FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
     ]], {
         -- <select6-4.4>
-        2.5
+        2
         -- </select6-4.4>
     })
 
@@ -497,7 +496,7 @@ test:do_execsql_test(
         SELECT avg(y) FROM (SELECT DISTINCT y FROM t1 WHERE y<5) ORDER BY y
     ]], {
         -- <select6-4.5>
-        2.5
+        2
         -- </select6-4.5>
     })
 
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
         -- </100>
     })
 
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));
     ]], {
         -- <subquery-3.4.1>
-        107, 4.0
+        106, 4, 107, 4
         -- </subquery-3.4.1>
     })
 
@@ -482,7 +482,7 @@ test:do_execsql_test(
                              HAVING avg1 > avg2);
     ]], {
         -- <subquery-3.4.2>
-        107, 4.0
+        106, 4, 107, 4
         -- </subquery-3.4.2>
     })
 
@@ -505,7 +505,7 @@ test:do_execsql_test(
          ORDER BY a.x;
     ]], {
         -- <subquery-3.4.3>
-        106, 4.5, false, true, 107, 4.0, true, false
+        106, 4, true, false, 107, 4, true, false
         -- </subquery-3.4.3>
     })
 
@@ -517,7 +517,7 @@ test:do_execsql_test(
         SELECT max((SELECT avg(y) FROM t35b)) FROM t35a;
     ]], {
         -- <subquery-3.5.1>
-        98.5
+        98
         -- </subquery-3.5.1>
     })
 
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
index 998213dbb..7c5477b33 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 f9350d2f2..83b9a59bf 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 5f0ce3543..3017793c6 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


  parent reply	other threads:[~2021-08-13  3:21 UTC|newest]

Thread overview: 13+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2021-08-13  3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
2021-08-13  3:17 ` [Tarantool-patches] [PATCH v1 01/10] sql: modify signature of TRIM() Mergen Imeev via Tarantool-patches
2021-08-13  3:17 ` [Tarantool-patches] [PATCH v1 02/10] sql: rework SQL built-in functions hash table Mergen Imeev via Tarantool-patches
2021-08-16 13:53   ` Vladimir Davydov via Tarantool-patches
2021-08-13  3:17 ` [Tarantool-patches] [PATCH v1 03/10] sql: check number of arguments during parsing Mergen Imeev via Tarantool-patches
2021-08-13  3:17 ` [Tarantool-patches] [PATCH v1 04/10] sql: static type check for SQL built-in functions Mergen Imeev via Tarantool-patches
2021-08-13  3:17 ` [Tarantool-patches] [PATCH v1 05/10] sql: runtime " Mergen Imeev via Tarantool-patches
2021-08-13  3:17 ` [Tarantool-patches] [PATCH v1 06/10] sql: enable types checking for some functions Mergen Imeev via Tarantool-patches
2021-08-13  3:17 ` [Tarantool-patches] [PATCH v1 07/10] sql: fix result type of min() and max() functions Mergen Imeev via Tarantool-patches
2021-08-13  3:17 ` Mergen Imeev via Tarantool-patches [this message]
2021-08-13  3:17 ` [Tarantool-patches] [PATCH v1 09/10] sql: fix quote() function Mergen Imeev via Tarantool-patches
2021-08-13  3:17 ` [Tarantool-patches] [PATCH v1 10/10] sql: arguments check for string value functions Mergen Imeev via Tarantool-patches
2021-08-19 11:49 ` [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Vladimir Davydov via Tarantool-patches

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=b3e67a5ccebe33c4b1f4703d60ce22808bcfab72.1628824421.git.imeevma@gmail.com \
    --to=tarantool-patches@dev.tarantool.org \
    --cc=imeevma@tarantool.org \
    --cc=vdavydov@tarantool.org \
    --subject='Re: [Tarantool-patches] [PATCH v1 08/10] sql: check argument types of sum(), avg(), total()' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox