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 90D926EC40; Fri, 13 Aug 2021 06:22:10 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 90D926EC40 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1628824930; bh=A+lzrWp7xVXYaDNmhVozETnka8FSGK0Y3ZcRLOlckis=; 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=By6yXMiCAasveqqeWAmaUF1XRR/EBdXeelUTEx0Q8ydDflfHC2lsd194BTIGQB6JB q1KMDcoZbBgsAKwUDqVZSM2SdUYwgvMMTNOj+y4zzsL8MZ0af6y44eL+VXRy808Fi7 sobdZF67p0HLd3iTykRrXDcYhNJOrYiq+B/t3Ic8= Received: from smtpng1.i.mail.ru (smtpng1.i.mail.ru [94.100.181.251]) (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 973846F814 for ; Fri, 13 Aug 2021 06:17:30 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 973846F814 Received: by smtpng1.m.smailru.net with esmtpa (envelope-from ) id 1mENhJ-0003Rs-IY; Fri, 13 Aug 2021 06:17:30 +0300 To: vdavydov@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Fri, 13 Aug 2021 06:17:29 +0300 Message-Id: <42ca158d6a04d6cbf2aa0dec30b242415c3a8bce.1628824421.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-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD92087353F0EC44DD906AB4890CDABF0C5CB76CEE71D3E4007182A05F5380850408214183A4530C39824E695DC20302FBDBC2B375C832BBB77ABD34BD09F08A2CA X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE79A02CDD1178524C2EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637DD92412F72BDCE938638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D864B8F0BCEEDE7BE82D84030EF04A1649117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BAA867293B0326636D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B66F6A3E018CF4DC80089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-B7AD71C0: AC4F5C86D027EB782CDD5689AFBDA7A213B5FB47DCBC3458834459D11680B5059A91936EF7B6732D343F16A7722FC62A X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975C69415AB31670C86C9289C9E0CBA033AF853703C6D5BBEDF19C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EFE37876E7723AB534DC48ACC2A39D04F89CDFB48F4795C241BDAD6C7F3747799A X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D343C45ADCD169245FA6026A171A93B98FD55D1961A9892CEC45648E814FD1D25C44433EF8D6ED075841D7E09C32AA3244C3D49C3F3622FF2D559ADF6B88F650D0C9CA7333006C390A0729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2bioj0dLV0c3jbkzXbgSYmv18mg== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5DE2FF6CD82B0B36DA6B778D669AF13C3D83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 10/10] 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 --- 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 +++----- 15 files changed, 106 insertions(+), 100 deletions(-) diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 47005f8e3..17d97a871 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 98f7169c0..1f787d4e0 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -548,16 +548,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); } } @@ -1363,7 +1361,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); } /** @@ -1418,8 +1419,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); } /** @@ -1842,9 +1845,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); } } } @@ -2014,8 +2021,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}, @@ -2088,24 +2102,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 7c5477b33..056d0f636 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 83b9a59bf..f82ec8f44 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 ec80dfc14..cf893d857 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