From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (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 9C4E8445324 for ; Thu, 16 Jul 2020 17:45:50 +0300 (MSK) From: imeevma@tarantool.org Date: Thu, 16 Jul 2020 17:45:49 +0300 Message-Id: In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v6 04/22] sql: check args of avg(), sum() and total() List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: korablev@tarantool.org, tsafin@tarantool.org, tarantool-patches@dev.tarantool.org 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) ]], { -- @@ -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) ]], { -- @@ -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) ]], { -- @@ -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) ]], { -- 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