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 BF81E6EC42; Thu, 19 Aug 2021 15:07:29 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org BF81E6EC42 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1629374849; bh=uP1jIw8icOP4aaR5cqM3U/GqWd7hcvrSJdGbKRfopZM=; 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=N8RaPlpjxjwft0Kiz6Skmpamjb0e7x63j7xDH9exs+Z6EscwfXeeXch6b/6gayVPV mkdM9OFTxOcbzRK2V47jvA39QX/OZce28loaUdjf1mOUZ1EZqpQEhimOjP458mNE9c esQIMPDyGiL/gRq0AJ5pJZmdcvrvQuxy/zvRlqfY= Received: from smtpng2.i.mail.ru (smtpng2.i.mail.ru [94.100.179.3]) (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 E4FAA6EC63 for ; Thu, 19 Aug 2021 15:03:17 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org E4FAA6EC63 Received: by smtpng2.m.smailru.net with esmtpa (envelope-from ) id 1mGglQ-00016g-Jo; Thu, 19 Aug 2021 15:03:17 +0300 To: kyukhin@tarantool.org, v.ioffe@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Thu, 19 Aug 2021 15:03:16 +0300 Message-Id: <89f4e8fe2e76199ffde48ce52468c5361bb8d94f.1629374449.git.imeevma@gmail.com> X-Mailer: git-send-email 2.25.1 In-Reply-To: References: MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-4EC0790: 10 X-7564579A: 78E4E2B564C1792B X-77F55803: 4F1203BC0FB41BD92087353F0EC44DD9ECFD080E047A606F56291F8739A291D6182A05F5380850400E1E31FC8739B51B36E9BB62F6275B3098EE7068336B3F8B18C9CE809FF7638F X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7D3E352D4AD7EBF54EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F790063761A26A2A89ED60DE8638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D833FEE105879FC8A9DCA8A750AB2B1FBB117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BAA867293B0326636D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B6300D3B61E77C8D3B089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975CF160826E4E1956AE4C02AA690A0411A5715A76A27862C66E9C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EF836F5ADB0B4F9314699F904B3F4130E343918A1A30D5E7FCCB5012B2E24CD356 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D344EF254DC71474AA3C4552CC94BF0E64C3FBA2DD72132FB411E1E1F7D29CE0BAC8A045B5B3BFF8CD71D7E09C32AA3244C98F6AD6C68BC7CB852B28B2AAA1C06F2C3B3ADDA61883BB5FACE5A9C96DEB163 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojGSxK+6r6oBGXHpu/mhvJHA== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D2CC1CF53470304E7F5AEA3FC2599382D83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 9/9] sql: arguments check for string value functions 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 functions SUBSTR(), GROUP_CONCAT(), REPLACE(), TRIM(). All these functions afther this patch will be able to return VARINARY value when VARBINARY arguments are given instead of STRING arguments. Closes #6105 --- .../gh-6105-built-in-functions-args.md | 6 ++ src/box/sql/expr.c | 3 +- src/box/sql/func.c | 63 ++++++++++++++----- test/sql-tap/aggnested.test.lua | 8 +-- test/sql-tap/distinctagg.test.lua | 3 +- test/sql-tap/e_select1.test.lua | 28 +++++---- test/sql-tap/func.test.lua | 32 +++------- test/sql-tap/substr.test.lua | 4 +- test/sql-tap/tkt2942.test.lua | 10 +-- test/sql-tap/uuid.test.lua | 22 +++---- test/sql-tap/view.test.lua | 2 +- test/sql-tap/with1.test.lua | 2 +- test/sql/boolean.result | 7 +-- test/sql/prepared.result | 4 +- test/sql/prepared.test.lua | 2 +- test/sql/types.result | 16 ++--- 16 files changed, 112 insertions(+), 100 deletions(-) create mode 100644 changelogs/unreleased/gh-6105-built-in-functions-args.md diff --git a/changelogs/unreleased/gh-6105-built-in-functions-args.md b/changelogs/unreleased/gh-6105-built-in-functions-args.md new file mode 100644 index 000000000..e7dac2bb2 --- /dev/null +++ b/changelogs/unreleased/gh-6105-built-in-functions-args.md @@ -0,0 +1,6 @@ +## feature/sql + +* The argument types of SQL built-in functions are now checked in most cases + during parsing. In addition, the number of arguments is now always checked + during parsing (gh-6105). + diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 88f476794..ee21c1ede 100644 --- a/src/box/sql/expr.c +++ b/src/box/sql/expr.c @@ -353,7 +353,7 @@ sql_expr_coll(Parse *parse, Expr *p, bool *is_explicit_coll, uint32_t *coll_id, p->x.pList->nExpr; uint32_t flags = sql_func_flags(p->u.zToken); if (((flags & SQL_FUNC_DERIVEDCOLL) != 0) && - arg_count > 0) { + arg_count > 0 && p->type == FIELD_TYPE_STRING) { /* * Now we use quite straightforward * approach assuming that resulting @@ -362,7 +362,6 @@ sql_expr_coll(Parse *parse, Expr *p, bool *is_explicit_coll, uint32_t *coll_id, * built-in functions: trim, upper, * lower, replace, substr. */ - assert(p->type == FIELD_TYPE_STRING); p = p->x.pList->a->pExpr; continue; } diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 498e346f5..c063552d6 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -551,16 +551,14 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) cnt++; } z2 += i; - sql_result_text64(context, (char *)z, z2 - z, - SQL_TRANSIENT); + mem_copy_str(context->pOut, (char *)z, z2 - z); } else { if (p1 + p2 > len) { p2 = len - p1; if (p2 < 0) p2 = 0; } - sql_result_blob64(context, (char *)&z[p1], (u64) p2, - SQL_TRANSIENT); + mem_copy_bin(context->pOut, (char *)&z[p1], p2); } } @@ -1367,7 +1365,10 @@ replaceFunc(sql_context * context, int argc, sql_value ** argv) j += nStr - i; assert(j <= nOut); zOut[j] = 0; - sql_result_text(context, (char *)zOut, j, sql_free); + if (context->func->def->returns == FIELD_TYPE_STRING) + mem_set_str_dynamic(context->pOut, (char *)zOut, j); + else + mem_set_bin_dynamic(context->pOut, (char *)zOut, j); } /** @@ -1422,8 +1423,10 @@ trim_procedure(struct sql_context *context, enum trim_side_mask flags, } } finish: - sql_result_text(context, (char *)input_str, input_str_sz, - SQL_TRANSIENT); + if (context->func->def->returns == FIELD_TYPE_STRING) + mem_copy_str(context->pOut, (char *)input_str, input_str_sz); + else + mem_copy_bin(context->pOut, (char *)input_str, input_str_sz); } /** @@ -1846,9 +1849,13 @@ groupConcatFinalize(sql_context * context) } else if (pAccum->accError == STRACCUM_NOMEM) { context->is_aborted = true; } else { - sql_result_text(context, - sqlStrAccumFinish(pAccum), - pAccum->nChar, sql_free); + char *str = sqlStrAccumFinish(pAccum); + int len = pAccum->nChar; + assert(len >= 0); + if (context->func->def->returns == FIELD_TYPE_STRING) + mem_set_str_dynamic(context->pOut, str, len); + else + mem_set_bin_dynamic(context->pOut, str, len); } } } @@ -2018,8 +2025,15 @@ static struct sql_func_definition definitions[] = { {"GREATEST", -1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxFunc, NULL}, - {"GROUP_CONCAT", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, + {"GROUP_CONCAT", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, + groupConcatStep, groupConcatFinalize}, + {"GROUP_CONCAT", 2, {FIELD_TYPE_STRING, FIELD_TYPE_STRING}, FIELD_TYPE_STRING, groupConcatStep, groupConcatFinalize}, + {"GROUP_CONCAT", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY, + groupConcatStep, groupConcatFinalize}, + {"GROUP_CONCAT", 2, {FIELD_TYPE_VARBINARY, FIELD_TYPE_VARBINARY}, + FIELD_TYPE_VARBINARY, groupConcatStep, groupConcatFinalize}, + {"HEX", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_STRING, hexFunc, NULL}, {"IFNULL", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, sql_builtin_stub, NULL}, @@ -2089,24 +2103,45 @@ static struct sql_func_definition definitions[] = { {"RANDOM", 0, {}, FIELD_TYPE_INTEGER, randomFunc, NULL}, {"RANDOMBLOB", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_VARBINARY, randomBlob, NULL}, - {"REPLACE", 3, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY}, + {"REPLACE", 3, + {FIELD_TYPE_STRING, FIELD_TYPE_STRING, FIELD_TYPE_STRING}, FIELD_TYPE_STRING, replaceFunc, NULL}, + {"REPLACE", 3, + {FIELD_TYPE_VARBINARY, FIELD_TYPE_VARBINARY, FIELD_TYPE_VARBINARY}, + FIELD_TYPE_VARBINARY, replaceFunc, NULL}, {"ROUND", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, roundFunc, NULL}, {"ROUND", 2, {FIELD_TYPE_DOUBLE, FIELD_TYPE_INTEGER}, FIELD_TYPE_DOUBLE, roundFunc, NULL}, {"ROW_COUNT", 0, {}, FIELD_TYPE_INTEGER, sql_row_count, NULL}, {"SOUNDEX", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, soundexFunc, NULL}, - {"SUBSTR", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY}, + {"SUBSTR", 2, {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER}, + FIELD_TYPE_STRING, substrFunc, NULL}, + {"SUBSTR", 3, + {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER, FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, substrFunc, NULL}, + {"SUBSTR", 2, {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER}, + FIELD_TYPE_VARBINARY, substrFunc, NULL}, + {"SUBSTR", 3, + {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER, FIELD_TYPE_INTEGER}, + FIELD_TYPE_VARBINARY, substrFunc, NULL}, {"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}, + + {"TRIM", 2, {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER}, + FIELD_TYPE_STRING, trim_func, NULL}, + {"TRIM", 3, {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER, FIELD_TYPE_STRING}, FIELD_TYPE_STRING, trim_func, NULL}, + {"TRIM", 2, {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER}, + FIELD_TYPE_VARBINARY, trim_func, NULL}, + {"TRIM", 3, + {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER, FIELD_TYPE_VARBINARY}, + FIELD_TYPE_VARBINARY, trim_func, NULL}, + {"TYPEOF", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, typeofFunc, NULL}, {"UNICODE", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, unicodeFunc, NULL}, diff --git a/test/sql-tap/aggnested.test.lua b/test/sql-tap/aggnested.test.lua index 6a967461d..1684f2249 100755 --- a/test/sql-tap/aggnested.test.lua +++ b/test/sql-tap/aggnested.test.lua @@ -24,10 +24,10 @@ test:plan(7) test:do_execsql_test( "aggnested-1.1", [[ - CREATE TABLE t1(a1 INTEGER PRIMARY KEY); - INSERT INTO t1 VALUES(1), (2), (3); - CREATE TABLE t2(b1 INTEGER PRIMARY KEY); - INSERT INTO t2 VALUES(4), (5); + CREATE TABLE t1(a1 STRING PRIMARY KEY); + INSERT INTO t1 VALUES('1'), ('2'), ('3'); + CREATE TABLE t2(b1 STRING PRIMARY KEY); + INSERT INTO t2 VALUES('4'), ('5'); SELECT (SELECT group_concat(a1,'x') FROM t2 LIMIT 1) FROM t1; ]], { diff --git a/test/sql-tap/distinctagg.test.lua b/test/sql-tap/distinctagg.test.lua index 9b1346f7d..7d5e05e3f 100755 --- a/test/sql-tap/distinctagg.test.lua +++ b/test/sql-tap/distinctagg.test.lua @@ -82,7 +82,8 @@ test:do_catchsql_test( test:do_catchsql_test( "distinctagg-2.2", [[ - SELECT group_concat(distinct a,b) FROM t1; + SELECT group_concat(distinct CAST(a AS STRING), CAST(b AS STRING)) + FROM t1; ]], { -- 1, "DISTINCT aggregates must have exactly one argument" diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua index da4db5a55..9f10994ae 100755 --- a/test/sql-tap/e_select1.test.lua +++ b/test/sql-tap/e_select1.test.lua @@ -956,7 +956,7 @@ test:do_select_tests( {"4", "SELECT *, count(*) FROM a1 JOIN a2", {4, 10, 10, 4, 16}}, {"5", "SELECT *, sum(three) FROM a1 NATURAL JOIN a2", {3, 6, 2, 3}}, {"6", "SELECT *, sum(three) FROM a1 NATURAL JOIN a2", {3, 6, 2, 3}}, - {"7", "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2", {"12", 3, 6}}, + {"7", "SELECT group_concat('1', ''), a1.* FROM a1 NATURAL JOIN a2", {"11", 3, 6}}, }) -- EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then @@ -1014,12 +1014,12 @@ test:do_execsql_test( INSERT INTO b1 VALUES(7, 's'); INSERT INTO b1 VALUES(6, 's'); - CREATE TABLE b2(x TEXT, y INT PRIMARY KEY); - INSERT INTO b2 VALUES(NULL, 0); - INSERT INTO b2 VALUES(NULL, 1); - INSERT INTO b2 VALUES('xyz', 2); - INSERT INTO b2 VALUES('abc', 3); - INSERT INTO b2 VALUES('xyz', 4); + CREATE TABLE b2(x TEXT, y STRING PRIMARY KEY); + INSERT INTO b2 VALUES(NULL, '0'); + INSERT INTO b2 VALUES(NULL, '1'); + INSERT INTO b2 VALUES('xyz', '2'); + INSERT INTO b2 VALUES('abc', '3'); + INSERT INTO b2 VALUES('xyz', '4'); CREATE TABLE b3(id INT PRIMARY KEY, a TEXT COLLATE "unicode_ci", b TEXT COLLATE "binary"); INSERT INTO b3 VALUES(1, 'abc', 'abc'); @@ -1048,10 +1048,14 @@ test:do_execsql_test( test:do_select_tests( "e_select-4.9", { - {"1", "SELECT group_concat(one), two FROM b1 GROUP BY two", {"4,5","f","1","o","6,7","s","2,3","t"}}, - {"2", "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)", {"1,2,3,4",10,"5,6,7",18}}, - {"3", "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2", {"4","1,5","2,6","3,7"}}, - {"4", "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')", {"3,4,5,6,7","1,2"}}, + {"1", [[SELECT group_concat(CAST(one AS STRING)), two FROM b1 GROUP BY + two]], {"4,5","f","1","o","6,7","s","2,3","t"}}, + {"2", [[SELECT group_concat(CAST(one AS STRING)), sum(one) FROM b1 + GROUP BY (one>4)]], {"1,2,3,4",10,"5,6,7",18}}, + {"3", [[SELECT group_concat(CAST(one AS STRING)) FROM b1 GROUP BY + (two>'o'), one%2]], {"4","1,5","2,6","3,7"}}, + {"4", [[SELECT group_concat(CAST(one AS STRING)) FROM b1 GROUP BY + (one==2 OR two=='o')]], {"3,4,5,6,7","1,2"}}, }) -- EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL @@ -1061,7 +1065,7 @@ test:do_select_tests( "e_select-4.10", { {"1", "SELECT group_concat(y) FROM b2 GROUP BY x", {"0,1","3","2,4"}}, - {"2", "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END", {4, 1}}, + {"2", "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<'4' THEN NULL ELSE 0 END", {4, 1}}, }) -- EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua index 8bb20bc79..416f27d69 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(14682) +test:plan(14680) --!./tcltestrunner.lua -- 2001 September 15 @@ -193,26 +193,6 @@ test:do_execsql_test( -- }) -test:do_execsql_test( - "func-2.9", - [[ - SELECT substr(a,1,1) FROM t2 - ]], { - -- - "1", "", "3", "", "6" - -- - }) - -test:do_execsql_test( - "func-2.10", - [[ - SELECT substr(a,2,2) FROM t2 - ]], { - -- - "", "", "45", "", "78" - -- - }) - -- Only do the following tests if TCL has UTF-8 capabilities -- if ("ሴ" ~= "u1234") then @@ -2256,7 +2236,8 @@ test:do_execsql_test( test:do_execsql_test( "func-24.10", [[ - SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1 + SELECT group_concat(CAST(CASE t1 WHEN 'this' THEN null ELSE t1 END + AS STRING)) FROM tbl1 ]], { -- "program,is,free,software" @@ -2266,7 +2247,8 @@ test:do_execsql_test( test:do_execsql_test( "func-24.11", [[ - SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1 + SELECT group_concat(CAST(CASE WHEN t1!='software' THEN null ELSE t1 END + AS STRING)) FROM tbl1 ]], { -- "software" @@ -2276,8 +2258,8 @@ test:do_execsql_test( test:do_execsql_test( "func-24.12", [[ - SELECT group_concat(CASE t1 WHEN 'this' THEN '' - WHEN 'program' THEN null ELSE t1 END) FROM tbl1 + SELECT group_concat(CAST(CASE t1 WHEN 'this' THEN '' WHEN 'program' THEN + null ELSE t1 END AS STRING)) FROM tbl1 ]], { -- ",is,free,software" diff --git a/test/sql-tap/substr.test.lua b/test/sql-tap/substr.test.lua index f64eb94be..e7e6d7aca 100755 --- a/test/sql-tap/substr.test.lua +++ b/test/sql-tap/substr.test.lua @@ -57,12 +57,12 @@ local function subblob_test(id, hex, i1, i2, hexresult) test:do_execsql_test( "substr-"..id..".1", string.format( - "SELECT HEX(CAST(substr(b, %s, %s) AS VARBINARY)) FROM t1", i1, i2), + "SELECT HEX(substr(CAST(b AS VARBINARY), %s, %s)) FROM t1", i1, i2), {hexresult}) --local qstr = string.gsub("' '", string)--"string","map","' ''",["string"]]]=]).."'" test:do_execsql_test( "substr-"..id..".2", - string.format("SELECT HEX(CAST(substr(x'%s', %s, %s) AS VARBINARY))", + string.format("SELECT HEX(substr(x'%s', %s, %s))", hex, i1, i2), {hexresult}) end diff --git a/test/sql-tap/tkt2942.test.lua b/test/sql-tap/tkt2942.test.lua index 2c06e013e..10da68583 100755 --- a/test/sql-tap/tkt2942.test.lua +++ b/test/sql-tap/tkt2942.test.lua @@ -35,11 +35,11 @@ test:plan(4) test:do_execsql_test( "tkt2942.1", [[ - create table t1(id INT primary key, "num" int); - insert into t1 values (1, 2); - insert into t1 values (2, 1); - insert into t1 values (3, 3); - insert into t1 values (4, 4); + create table t1(id INT primary key, "num" STRING); + insert into t1 values (1, '2'); + insert into t1 values (2, '1'); + insert into t1 values (3, '3'); + insert into t1 values (4, '4'); SELECT group_concat("num") FROM (SELECT "num" FROM t1 ORDER BY "num" DESC); ]], { -- diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua index 4ca4d4037..fea9d9370 100755 --- a/test/sql-tap/uuid.test.lua +++ b/test/sql-tap/uuid.test.lua @@ -288,14 +288,12 @@ test:do_execsql_test( uuid3 }) -test:do_execsql_test( +test:do_catchsql_test( "uuid-6.1.9", [[ SELECT GROUP_CONCAT(u) from t2; ]], { - "11111111-1111-1111-1111-111111111111,".. - "11111111-3333-1111-1111-111111111111,".. - "22222222-1111-1111-1111-111111111111" + 1, "Failed to execute SQL statement: wrong arguments for function GROUP_CONCAT()" }) test:do_catchsql_test( @@ -402,14 +400,12 @@ test:do_catchsql_test( 1, "Failed to execute SQL statement: wrong arguments for function RANDOMBLOB()" }) -test:do_execsql_test( +test:do_catchsql_test( "uuid-6.1.23", [[ SELECT REPLACE(u, '1', '2') from t2; ]], { - "22222222-2222-2222-2222-222222222222", - "22222222-3333-2222-2222-222222222222", - "22222222-2222-2222-2222-222222222222" + 1, "Failed to execute SQL statement: wrong arguments for function REPLACE()" }) test:do_catchsql_test( @@ -428,12 +424,12 @@ test:do_catchsql_test( 1, "Failed to execute SQL statement: wrong arguments for function SOUNDEX()" }) -test:do_execsql_test( +test:do_catchsql_test( "uuid-6.1.26", [[ SELECT SUBSTR(u, 3, 3) from t2; ]], { - "111", "111", "222" + 1, "Failed to execute SQL statement: wrong arguments for function SUBSTR()" }) test:do_catchsql_test( @@ -452,14 +448,12 @@ test:do_catchsql_test( 1, "Failed to execute SQL statement: wrong arguments for function TOTAL()" }) -test:do_execsql_test( +test:do_catchsql_test( "uuid-6.1.29", [[ SELECT TRIM(u) from t2; ]], { - "11111111-1111-1111-1111-111111111111", - "11111111-3333-1111-1111-111111111111", - "22222222-1111-1111-1111-111111111111" + 1, "Failed to execute SQL statement: wrong arguments for function TRIM()" }) test:do_execsql_test( diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua index bea1f7db2..e84fc033d 100755 --- a/test/sql-tap/view.test.lua +++ b/test/sql-tap/view.test.lua @@ -1280,7 +1280,7 @@ test:do_execsql_test( a(t) AS ( SELECT group_concat( substr('a', 1+least(iter/7,4), 1), '') FROM m2 GROUP BY cy ) - SELECT group_concat(trim(t),x'0a') FROM a; + SELECT group_concat(CAST(trim(t) AS VARBINARY),x'0a') FROM a; SELECT * FROM v; ]], { -- diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua index 35f3dabb3..761004b23 100755 --- a/test/sql-tap/with1.test.lua +++ b/test/sql-tap/with1.test.lua @@ -549,7 +549,7 @@ test:do_execsql_test("8.1-mandelbrot", [[ SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') FROM m2 GROUP BY cy ) - SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a; + SELECT group_concat(CAST(TRIM(TRAILING FROM t) AS VARBINARY),x'0a') FROM a; ]], { -- <8.1-mandelbrot> [[ ....# diff --git a/test/sql/boolean.result b/test/sql/boolean.result index 55c3820d1..75a091cce 100644 --- a/test/sql/boolean.result +++ b/test/sql/boolean.result @@ -365,11 +365,8 @@ SELECT TOTAL(a) FROM t0; | ... SELECT GROUP_CONCAT(a, ' +++ ') FROM t0; | --- - | - metadata: - | - name: COLUMN_1 - | type: string - | rows: - | - ['FALSE +++ TRUE'] + | - null + | - 'Failed to execute SQL statement: wrong arguments for function GROUP_CONCAT()' | ... -- Check BOOLEAN as binding parameter. diff --git a/test/sql/prepared.result b/test/sql/prepared.result index c4b09e514..9824a061c 100644 --- a/test/sql/prepared.result +++ b/test/sql/prepared.result @@ -471,7 +471,7 @@ s = prepare([[WITH RECURSIVE \ a(t) AS ( \ SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') \ FROM m2 GROUP BY cy) \ - SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;]]) + SELECT group_concat(CAST(TRIM(TRAILING FROM t) AS VARBINARY), x'0a') FROM a;]]) | --- | ... @@ -481,7 +481,7 @@ res = execute(s.stmt_id) res.metadata | --- | - - name: COLUMN_13 - | type: string + | type: varbinary | ... unprepare(s.stmt_id) | --- diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua index 0a1fdebae..ee2470122 100644 --- a/test/sql/prepared.test.lua +++ b/test/sql/prepared.test.lua @@ -176,7 +176,7 @@ s = prepare([[WITH RECURSIVE \ a(t) AS ( \ SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') \ FROM m2 GROUP BY cy) \ - SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;]]) + SELECT group_concat(CAST(TRIM(TRAILING FROM t) AS VARBINARY), x'0a') FROM a;]]) res = execute(s.stmt_id) res.metadata diff --git a/test/sql/types.result b/test/sql/types.result index bf999be68..b8e0d1fcb 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -807,11 +807,8 @@ box.execute("SELECT count(i) FROM t;") ... box.execute("SELECT group_concat(i) FROM t;") --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['1,-1,18446744073709551613'] +- null +- 'Failed to execute SQL statement: wrong arguments for function GROUP_CONCAT()' ... box.execute("DELETE FROM t WHERE i < 18446744073709551613;") --- @@ -1279,7 +1276,7 @@ box.execute("SELECT group_concat(v) FROM t;") --- - metadata: - name: COLUMN_1 - type: string + type: varbinary rows: - ['abc'] ... @@ -1841,11 +1838,8 @@ box.execute("SELECT count(d) FROM t;") ... box.execute("SELECT group_concat(d) FROM t;") --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['10.0,-2.0,3.3,1.8e+19'] +- null +- 'Failed to execute SQL statement: wrong arguments for function GROUP_CONCAT()' ... box.execute("SELECT lower(d) FROM t;") --- -- 2.25.1