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
next prev 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