[Tarantool-patches] [PATCH v5 05/17] sql: check args of avg(), sum() and total()

imeevma at tarantool.org imeevma at tarantool.org
Tue Jul 14 18:48:22 MSK 2020


After this patch, the argument types of the avg(), sum() and total()
functions will be checked properly.

Part of #4159
---
 src/box/sql/func.c          |  18 ++---
 test/sql-tap/func.test.lua  |   8 +-
 test/sql-tap/func5.test.lua | 149 +++++++++++++++++++++++++++++++++++-
 3 files changed, 157 insertions(+), 18 deletions(-)

diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 329554731..ed972fdab 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -1927,18 +1927,10 @@ 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]);
+	enum mp_type type = sql_value_type(argv[0]);
 	if (type == MP_NIL || p == NULL)
 		return;
-	if (type != MP_DOUBLE && type != MP_INT && type != MP_UINT) {
-		if (mem_apply_numeric_type(argv[0]) != 0) {
-			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
-				 sql_value_to_diag_str(argv[0]), "number");
-			context->is_aborted = true;
-			return;
-		}
-		type = sql_value_type(argv[0]);
-	}
+	assert(type == MP_DOUBLE || type == MP_INT || type == MP_UINT);
 	p->cnt++;
 	if (type == MP_INT || type == MP_UINT) {
 		int64_t v = sql_value_int64(argv[0]);
@@ -2233,7 +2225,7 @@ static struct {
 	}, {
 	 .name = "AVG",
 	 .param_count = 1,
-	 .first_arg = FIELD_TYPE_ANY,
+	 .first_arg = FIELD_TYPE_NUMBER,
 	 .args = FIELD_TYPE_ANY,
 	 .is_blob_like_str = false,
 	 .returns = FIELD_TYPE_NUMBER,
@@ -2896,7 +2888,7 @@ static struct {
 	}, {
 	 .name = "SUM",
 	 .param_count = 1,
-	 .first_arg = FIELD_TYPE_ANY,
+	 .first_arg = FIELD_TYPE_NUMBER,
 	 .args = FIELD_TYPE_ANY,
 	 .is_blob_like_str = false,
 	 .returns = FIELD_TYPE_NUMBER,
@@ -2922,7 +2914,7 @@ static struct {
 	}, {
 	 .name = "TOTAL",
 	 .param_count = 1,
-	 .first_arg = FIELD_TYPE_ANY,
+	 .first_arg = FIELD_TYPE_NUMBER,
 	 .args = FIELD_TYPE_ANY,
 	 .is_blob_like_str = false,
 	 .returns = FIELD_TYPE_NUMBER,
diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua
index 422c8ce58..ffee8f1c5 100755
--- a/test/sql-tap/func.test.lua
+++ b/test/sql-tap/func.test.lua
@@ -893,7 +893,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "func-8.5",
     [[
-        SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
+        SELECT sum(x) FROM (SELECT CAST('9223372036' || '854775807' AS INTEGER) AS x
                             UNION ALL SELECT -9223372036854775807)
     ]], {
         -- <func-8.5>
@@ -904,7 +904,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "func-8.6",
     [[
-        SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
+        SELECT typeof(sum(x)) FROM (SELECT CAST('9223372036' || '854775807' AS INTEGER) AS x
                             UNION ALL SELECT -9223372036854775807)
     ]], {
         -- <func-8.6>
@@ -915,7 +915,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "func-8.7",
     [[
-        SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
+        SELECT typeof(sum(x)) FROM (SELECT CAST('9223372036' || '854775808' AS INTEGER) AS x
                             UNION ALL SELECT -9223372036854775807)
     ]], {
         -- <func-8.7>
@@ -926,7 +926,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "func-8.8",
     [[
-        SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
+        SELECT sum(x)>0.0 FROM (SELECT CAST('9223372036' || '854775808' AS INTEGER) AS x
                             UNION ALL SELECT -9223372036850000000)
     ]], {
         -- <func-8.8>
diff --git a/test/sql-tap/func5.test.lua b/test/sql-tap/func5.test.lua
index d6d5d4df6..467ba7292 100755
--- a/test/sql-tap/func5.test.lua
+++ b/test/sql-tap/func5.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(29)
+test:plan(50)
 
 --!./tcltestrunner.lua
 -- 2010 August 27
@@ -346,4 +346,151 @@ test:do_catchsql_test(
         1, "Type mismatch: can not convert varbinary to number"
     })
 
+test:do_execsql_test(
+    "func-5-6.2.1", [[
+        SELECT sum("_auto_field_") from (values (NULL), (NULL), (NULL));
+    ]],{
+        ""
+    })
+
+test:do_execsql_test(
+    "func-5-6.2.2", [[
+        SELECT sum("_auto_field_") from (values (123), (123), (123));
+    ]], {
+        369
+    })
+
+test:do_execsql_test(
+    "func-5-6.2.3", [[
+        SELECT sum("_auto_field_") from (values (-123), (-123), (-123));
+    ]], {
+        -369
+    })
+
+test:do_execsql_test(
+    "func-5-6.2.4", [[
+        SELECT sum("_auto_field_") from (values (-5.5), (-5.5), (-5.5));
+    ]], {
+        -16.5
+    })
+
+test:do_catchsql_test(
+    "func-5-6.2.5", [[
+        SELECT sum("_auto_field_") from (values ('-123'), ('-123'), ('-123'));
+    ]], {
+        1, "Type mismatch: can not convert -123 to number"
+    })
+
+test:do_catchsql_test(
+    "func-5-6.2.6", [[
+        SELECT sum("_auto_field_") from (values (false), (false), (false));
+    ]], {
+        1, "Type mismatch: can not convert FALSE to number"
+    })
+
+test:do_catchsql_test(
+    "func-5-6.2.7", [[
+        SELECT sum("_auto_field_") from (values (X'3334'), (X'3334'), (X'3334'));
+    ]], {
+        1, "Type mismatch: can not convert varbinary to number"
+    })
+
+test:do_execsql_test(
+    "func-5-6.3.1", [[
+        SELECT avg("_auto_field_") from (values (NULL), (NULL), (NULL));
+    ]],{
+        ""
+    })
+
+test:do_execsql_test(
+    "func-5-6.3.2", [[
+        SELECT avg("_auto_field_") from (values (123), (123), (123));
+    ]], {
+        123
+    })
+
+test:do_execsql_test(
+    "func-5-6.3.3", [[
+        SELECT avg("_auto_field_") from (values (-123), (-123), (-123));
+    ]], {
+        -123
+    })
+
+test:do_execsql_test(
+    "func-5-6.3.4", [[
+        SELECT avg("_auto_field_") from (values (-5.5), (-5.5), (-5.5));
+    ]], {
+        -5.5
+    })
+
+test:do_catchsql_test(
+    "func-5-6.3.5", [[
+        SELECT avg("_auto_field_") from (values ('-123'), ('-123'), ('-123'));
+    ]], {
+        1, "Type mismatch: can not convert -123 to number"
+    })
+
+test:do_catchsql_test(
+    "func-5-6.3.6", [[
+        SELECT avg("_auto_field_") from (values (false), (false), (false));
+    ]], {
+        1, "Type mismatch: can not convert FALSE to number"
+    })
+
+test:do_catchsql_test(
+    "func-5-6.3.7", [[
+        SELECT avg("_auto_field_") from (values (X'3334'), (X'3334'), (X'3334'));
+    ]], {
+        1, "Type mismatch: can not convert varbinary to number"
+    })
+
+test:do_execsql_test(
+    "func-5-6.4.1", [[
+        SELECT total("_auto_field_") from (values (NULL), (NULL), (NULL));
+    ]],{
+        0
+    })
+
+test:do_execsql_test(
+    "func-5-6.4.2", [[
+        SELECT total("_auto_field_") from (values (123), (123), (123));
+    ]], {
+        369
+    })
+
+test:do_execsql_test(
+    "func-5-6.4.3", [[
+        SELECT total("_auto_field_") from (values (-123), (-123), (-123));
+    ]], {
+        -369
+    })
+
+test:do_execsql_test(
+    "func-5-6.4.4", [[
+        SELECT total("_auto_field_") from (values (-5.5), (-5.5), (-5.5));
+    ]], {
+        -16.5
+    })
+
+test:do_catchsql_test(
+    "func-5-6.4.5", [[
+        SELECT total("_auto_field_") from (values ('-123'), ('-123'), ('-123'));
+    ]], {
+        1, "Type mismatch: can not convert -123 to number"
+    })
+
+test:do_catchsql_test(
+    "func-5-6.4.6", [[
+        SELECT total("_auto_field_") from (values (false), (false), (false));
+    ]], {
+        1, "Type mismatch: can not convert FALSE to number"
+    })
+
+test:do_catchsql_test(
+    "func-5-6.4.7", [[
+        SELECT total("_auto_field_") from (values (X'3334'), (X'3334'), (X'3334'));
+    ]], {
+        1, "Type mismatch: can not convert varbinary to number"
+    })
+
 test:finish_test()
-- 
2.25.1



More information about the Tarantool-patches mailing list