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 E0D7E71063; Thu, 11 Nov 2021 13:48:59 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org E0D7E71063 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1636627740; bh=ZBK5xgqRf4/s0m1Ug6XQ5MOR6ShLoamxNsWt2T/b/Qs=; 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=iCiQW0hFXTiG4pH9a3af+3jr3pKP8cRNkvGZDkJZz6NHXPNyEyc/+u8tmT+sH3Tsi 1UU9mH3iGIvR3GcEhJPVbtrf2lsJgZOZgA7x2CfquLLC1558Z2rXpGxe5Qt+Dq0GTV kh6y9gg3oy/6Uq1d+29P2NPRYwR+UXJX/7mGk0QA= 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 5B4816CE3E for ; Thu, 11 Nov 2021 13:45:40 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 5B4816CE3E Received: by smtpng1.m.smailru.net with esmtpa (envelope-from ) id 1ml7aN-00034Y-DN; Thu, 11 Nov 2021 13:45:39 +0300 To: kyukhin@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Thu, 11 Nov 2021 13:45:39 +0300 Message-Id: <99cea2a655c9df90ae50e70148efee72ecdc2be3.1636627366.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: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD9731B3922EC0639796A433260860B48E60A001AF1EB3A7EE600894C459B0CD1B9C3590D1F0DF823F0674694C625E6ED81BD7B9CED87D255670D92EFB1A9252998 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE70D278D70F8433719EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637C59BB75D821356298638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D891CF8FE8A485B7AC61B8B7BB91E91CD4117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8BF1175FABE1C0F9B6A471835C12D1D977C4224003CC8364762BB6847A3DEAEFB0F43C7A68FF6260569E8FC8737B5C2249EC8D19AE6D49635B68655334FD4449CB9ECD01F8117BC8BEAAAE862A0553A39223F8577A6DFFEA7CD1D040B6C1ECEA3F43847C11F186F3C59DAA53EE0834AAEE X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975C7E9FEBB9C11794A11486DC52A5CF63075624322DB35286259C2B6934AE262D3EE7EAB7254005DCED7532B743992DF240BDC6A1CF3F042BAD6DF99611D93F60EF309DFB797F6729CB699F904B3F4130E343918A1A30D5E7FCCB5012B2E24CD356 X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D3407FE5477D6A8AF08D9A64427604508D38135DB54772A96D904B492FAC5F80E6B5BAF1F6FDCD8A9B41D7E09C32AA3244C3D8C9396FBC303EF98A777CB3DEC6B7C69B6CAE0477E908D729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2bioj4t8MBgWr8bItB9qIvWkFSQ== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5D379079D1F86B4A35E359AE6318E0915D83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 7/8] sql: rework SUBSTR() function 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 makes SUBSTR() work according to ANSI rules for SUBSTRING() function. Also, SUBSTR() can now work correctly with large INTEGER values. The SUBSTR() syntax has not changed. Part of #4145 @TarantoolBot document Title: SUBSTR() function SUBSTR() now works according to the ANSI rules for SUBSTRING(). Rules for SUBSTR() with 2 arguments: 1) let the first argument be VALUE, and the second argument be START; 2) VALUE should be STRING or VARBINARY, START should be INTEGER; 3) if any of arguments is NULL, NULL is returned; 4) let POS be MAX(START - 1, 0), END be length of the VALUE; 5) if POS >= END, the result is empty string; 6) if POS < END, the result will be substring of VALUE, starting from the position POS to the position END. Rules for SUBSTR() with 3 arguments: 1) let the first argument be VALUE, the second argument be START, and the third argument be LENGTH; 2) VALUE should be STRING or VARBINARY, START and LENGTH should be INTEGERs; 3) if any of arguments is NULL, NULL is returned; 4) if LENGTH < 0, an error is thrown; 5) let POS be MAX(START - 1, 0), END be START + LENGTH - 1; 6) if POS >= END, the result is empty string; 7) if POS < END, the result will be substring of VALUE, starting from the position POS to the position END. --- src/box/sql/func.c | 273 ++++++++++++++++++++--------------- test/sql-tap/func.test.lua | 16 +- test/sql-tap/func2.test.lua | 150 +++++++++---------- test/sql-tap/substr.test.lua | 121 ++++++++++++---- 4 files changed, 339 insertions(+), 221 deletions(-) diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 7914d2ec7..7e71a757b 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -573,6 +573,161 @@ func_position_characters(struct sql_context *ctx, int argc, struct Mem *argv) return mem_set_uint(ctx->pOut, 0); } +/** Implementation of the SUBSTR() function. */ +int +substr_normalize(int64_t base_start, bool is_start_neg, uint64_t base_length, + uint64_t *start, uint64_t *length) +{ + if (!is_start_neg && base_start > 0) { + *start = (uint64_t)base_start - 1; + *length = base_length; + return 0; + } + *start = 0; + if (base_length == 0) { + *length = 0; + return 0; + } + /* + * We are subtracting 1 from base_length instead of subtracting from + * base_start, since base_start can be INT64_MIN. At the same time, + * base_length is not less than 1. + */ + int64_t a = base_start; + int64_t b = (int64_t)(base_length - 1); + int64_t res; + bool is_neg; + /* + * Integer cannot overflow since non-positive value is added to positive + * value. + */ + if (sql_add_int(a, a != 0, b, false, &res, &is_neg) != 0) { + diag_set(ClientError, ER_SQL_EXECUTE, "integer is overflowed"); + return -1; + } + *length = is_neg ? 0 : (uint64_t)res; + return 0; +} + +static void +func_substr_octets(struct sql_context *ctx, int argc, struct Mem *argv) +{ + assert(argc == 2 || argc == 3); + if (mem_is_any_null(&argv[0], &argv[1])) + return; + assert(mem_is_bytes(&argv[0]) && mem_is_int(&argv[1])); + + bool is_str = mem_is_str(&argv[0]); + uint64_t size = argv[0].n; + + if (argc == 2) { + uint64_t start = mem_is_uint(&argv[1]) && argv[1].u.u > 1 ? + argv[1].u.u - 1 : 0; + if (start >= size) { + if (is_str) + return mem_set_str0_static(ctx->pOut, ""); + else + return mem_set_bin_static(ctx->pOut, "", 0); + } + char *s = &argv[0].z[start]; + uint64_t n = size - start; + ctx->is_aborted = is_str ? mem_copy_str(ctx->pOut, s, n) != 0 : + mem_copy_bin(ctx->pOut, s, n) != 0; + return; + } + + assert(argc == 3); + if (mem_is_null(&argv[2])) + return; + assert(mem_is_int(&argv[2])); + if (!mem_is_uint(&argv[2])) { + diag_set(ClientError, ER_SQL_EXECUTE, "Length of the result " + "cannot be less than 0"); + ctx->is_aborted = true; + return; + } + uint64_t start; + uint64_t length; + if (substr_normalize(argv[1].u.i, !mem_is_uint(&argv[1]), argv[2].u.u, + &start, &length) != 0) { + ctx->is_aborted = true; + return; + } + if (start >= size || length == 0) { + if (is_str) + return mem_set_str0_static(ctx->pOut, ""); + else + return mem_set_bin_static(ctx->pOut, "", 0); + } + char *str = &argv[0].z[start]; + uint64_t len = MIN(size - start, length); + ctx->is_aborted = is_str ? mem_copy_str(ctx->pOut, str, len) != 0 : + mem_copy_bin(ctx->pOut, str, len) != 0; +} + +static void +func_substr_characters(struct sql_context *ctx, int argc, struct Mem *argv) +{ + assert(argc == 2 || argc == 3); + (void)argc; + if (mem_is_any_null(&argv[0], &argv[1])) + return; + assert(mem_is_str(&argv[0]) && mem_is_int(&argv[1])); + + const char *str = argv[0].z; + int pos = 0; + int end = argv[0].n; + if (argc == 2) { + uint64_t start = mem_is_uint(&argv[1]) && argv[1].u.u > 1 ? + argv[1].u.u - 1 : 0; + for (uint64_t i = 0; i < start && pos < end; ++i) { + UChar32 c; + U8_NEXT((uint8_t *)str, pos, end, c); + } + if (pos == end) + return mem_set_str_static(ctx->pOut, "", 0); + if (mem_copy_str(ctx->pOut, str + pos, end - pos) != 0) + ctx->is_aborted = true; + return; + } + + assert(argc == 3); + if (mem_is_null(&argv[2])) + return; + assert(mem_is_int(&argv[2])); + if (!mem_is_uint(&argv[2])) { + diag_set(ClientError, ER_SQL_EXECUTE, "Length of the result " + "cannot be less than 0"); + ctx->is_aborted = true; + return; + } + uint64_t start; + uint64_t length; + if (substr_normalize(argv[1].u.i, !mem_is_uint(&argv[1]), argv[2].u.u, + &start, &length) != 0) { + ctx->is_aborted = true; + return; + } + if (length == 0) + return mem_set_str_static(ctx->pOut, "", 0); + + for (uint64_t i = 0; i < start && pos < end; ++i) { + UChar32 c; + U8_NEXT((uint8_t *)str, pos, end, c); + } + if (pos == end) + return mem_set_str_static(ctx->pOut, "", 0); + + int cur = pos; + for (uint64_t i = 0; i < length && cur < end; ++i) { + UChar32 c; + U8_NEXT((uint8_t *)str, cur, end, c); + } + assert(cur > pos); + if (mem_copy_str(ctx->pOut, str + pos, cur - pos) != 0) + ctx->is_aborted = true; +} + static const unsigned char * mem_as_ustr(struct Mem *mem) { @@ -770,116 +925,6 @@ printfFunc(struct sql_context *context, int argc, struct Mem *argv) } } -/* - * Implementation of the substr() function. - * - * substr(x,p1,p2) returns p2 characters of x[] beginning with p1. - * p1 is 1-indexed. So substr(x,1,1) returns the first character - * of x. If x is text, then we actually count UTF-8 characters. - * If x is a blob, then we count bytes. - * - * If p1 is negative, then we begin abs(p1) from the end of x[]. - * - * If p2 is negative, return the p2 characters preceding p1. - */ -static void -substrFunc(struct sql_context *context, int argc, struct Mem *argv) -{ - const unsigned char *z; - const unsigned char *z2; - int len; - int p0type; - int64_t p1, p2; - int negP2 = 0; - - if (argc != 2 && argc != 3) { - diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, "SUBSTR", - "1 or 2", argc); - context->is_aborted = true; - return; - } - if (mem_is_null(&argv[1]) || (argc == 3 && mem_is_null(&argv[2]))) - return; - p0type = sql_value_type(&argv[0]); - p1 = mem_get_int_unsafe(&argv[1]); - if (p0type == MP_BIN) { - z = mem_as_bin(&argv[0]); - len = mem_len_unsafe(&argv[0]); - if (z == 0) - return; - assert(len == mem_len_unsafe(&argv[0])); - } else { - z = mem_as_ustr(&argv[0]); - if (z == 0) - return; - len = 0; - if (p1 < 0) - len = sql_utf8_char_count(z, mem_len_unsafe(&argv[0])); - } - if (argc == 3) { - p2 = mem_get_int_unsafe(&argv[2]); - if (p2 < 0) { - p2 = -p2; - negP2 = 1; - } - } else { - p2 = sql_context_db_handle(context)-> - aLimit[SQL_LIMIT_LENGTH]; - } - if (p1 < 0) { - p1 += len; - if (p1 < 0) { - p2 += p1; - if (p2 < 0) - p2 = 0; - p1 = 0; - } - } else if (p1 > 0) { - p1--; - } else if (p2 > 0) { - p2--; - } - if (negP2) { - p1 -= p2; - if (p1 < 0) { - p2 += p1; - p1 = 0; - } - } - assert(p1 >= 0 && p2 >= 0); - if (p0type != MP_BIN) { - /* - * In the code below 'cnt' and 'n_chars' is - * used because '\0' is not supposed to be - * end-of-string symbol. - */ - int byte_size = mem_len_unsafe(&argv[0]); - int n_chars = sql_utf8_char_count(z, byte_size); - int cnt = 0; - int i = 0; - while (cnt < n_chars && p1) { - SQL_UTF8_FWD_1(z, i, byte_size); - cnt++; - p1--; - } - z += i; - i = 0; - for (z2 = z; cnt < n_chars && p2; p2--) { - SQL_UTF8_FWD_1(z2, i, byte_size); - cnt++; - } - z2 += i; - mem_copy_str(context->pOut, (char *)z, z2 - z); - } else { - if (p1 + p2 > len) { - p2 = len - p1; - if (p2 < 0) - p2 = 0; - } - mem_copy_bin(context->pOut, (char *)&z[p1], p2); - } -} - /* * Implementation of the round() function */ @@ -1933,15 +1978,15 @@ static struct sql_func_definition definitions[] = { {"SOUNDEX", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, soundexFunc, NULL}, {"SUBSTR", 2, {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER}, - FIELD_TYPE_STRING, substrFunc, NULL}, + FIELD_TYPE_STRING, func_substr_characters, NULL}, {"SUBSTR", 3, {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER, FIELD_TYPE_INTEGER}, - FIELD_TYPE_STRING, substrFunc, NULL}, + FIELD_TYPE_STRING, func_substr_characters, NULL}, {"SUBSTR", 2, {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER}, - FIELD_TYPE_VARBINARY, substrFunc, NULL}, + FIELD_TYPE_VARBINARY, func_substr_octets, NULL}, {"SUBSTR", 3, {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER, FIELD_TYPE_INTEGER}, - FIELD_TYPE_VARBINARY, substrFunc, NULL}, + FIELD_TYPE_VARBINARY, func_substr_octets, NULL}, {"SUM", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, step_sum, NULL}, {"SUM", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, step_sum, NULL}, {"TOTAL", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_DOUBLE, step_total, diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua index 29e1c3de2..b52d5bd4c 100755 --- a/test/sql-tap/func.test.lua +++ b/test/sql-tap/func.test.lua @@ -139,7 +139,7 @@ test:do_execsql_test( SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1 ]], { -- - "e", "s", "m", "e", "s" + "", "", "", "", "" -- }) @@ -149,7 +149,7 @@ test:do_execsql_test( SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1 ]], { -- - "e", "s", "m", "e", "s" + "", "", "", "", "" -- }) @@ -159,7 +159,7 @@ test:do_execsql_test( SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1 ]], { -- - "e", "i", "a", "r", "i" + "", "", "", "", "" -- }) @@ -169,7 +169,7 @@ test:do_execsql_test( SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1 ]], { -- - "ee", "is", "am", "re", "is" + "", "", "", "", "" -- }) @@ -179,7 +179,7 @@ test:do_execsql_test( SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1 ]], { -- - "fr", "", "gr", "wa", "th" + "", "", "", "", "" -- }) @@ -288,7 +288,7 @@ if ("ሴ" ~= "u1234") then SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1 ]], { -- - "8", "s", "s", "o" + "", "", "", "" -- }) @@ -298,7 +298,7 @@ if ("ሴ" ~= "u1234") then SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1 ]], { -- - "F-", "er", "in", "ሴh" + "", "", "", "" -- }) @@ -308,7 +308,7 @@ if ("ሴ" ~= "u1234") then SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1 ]], { -- - "TF-", "ter", "ain", "iሴh" + "", "", "", "" -- }) diff --git a/test/sql-tap/func2.test.lua b/test/sql-tap/func2.test.lua index 792f020f1..b786b4d96 100755 --- a/test/sql-tap/func2.test.lua +++ b/test/sql-tap/func2.test.lua @@ -162,7 +162,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -1) ]], { -- - "s" + "Supercalifragilisticexpialidocious" -- }) @@ -172,7 +172,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -2) ]], { -- - "us" + "Supercalifragilisticexpialidocious" -- }) @@ -182,7 +182,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -30) ]], { -- - "rcalifragilisticexpialidocious" + "Supercalifragilisticexpialidocious" -- }) @@ -344,7 +344,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -1, 1) ]], { -- - "s" + "" -- }) @@ -354,7 +354,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -1, 2) ]], { -- - "s" + "" -- }) @@ -364,7 +364,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -2, 1) ]], { -- - "u" + "" -- }) @@ -374,7 +374,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -30, 1) ]], { -- - "r" + "" -- }) @@ -394,7 +394,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -34, 1) ]], { -- - "S" + "" -- }) @@ -404,7 +404,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -34, 2) ]], { -- - "Su" + "" -- }) @@ -424,7 +424,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -35, 2) ]], { -- - "S" + "" -- }) @@ -464,11 +464,13 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -36, 3) ]], { -- - "S" + "" -- }) -- p1 is 1-indexed, p2 length to return, p2<0 return p2 chars before p1 +local err = [[Failed to execute SQL statement: Length of the result cannot ]].. + [[be less than 0]] test:do_execsql_test( "func2-1.31.0", [[ @@ -479,23 +481,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.31.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 0, -1) ]], { -- - "" + 1, err -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.31.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 0, -2) ]], { -- - "" + 1, err -- }) @@ -509,13 +511,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.32.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 1, -1) ]], { -- - "" + 1, err -- }) @@ -529,23 +531,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.33.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, -1) ]], { -- - "S" + 1, err -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.33.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, -2) ]], { -- - "S" + 1, err -- }) @@ -559,63 +561,63 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.34.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 3, -1) ]], { -- - "u" + 1, err -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.34.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 3, -2) ]], { -- - "Su" + 1, err -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.35.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 30, -1) ]], { -- - "o" + 1, err -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.35.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 30, -2) ]], { -- - "do" + 1, err -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.36", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 34, -1) ]], { -- - "u" + 1, err -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.37", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 35, -1) ]], { -- - "s" + 1, err -- }) @@ -629,23 +631,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.38.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 36, -1) ]], { -- - "" + 1, err -- }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.38.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 36, -2) ]], { -- - "s" + 1, err -- }) @@ -993,7 +995,7 @@ if ("ሴ" ~= "u1234") SELECT SUBSTR('ሴ', -1, 1) ]], { -- - "ሴ" + "" -- }) @@ -1003,7 +1005,7 @@ if ("ሴ" ~= "u1234") SELECT SUBSTR('ሴ', -1, 2) ]], { -- - "ሴ" + "" -- }) @@ -1130,21 +1132,21 @@ test:do_test( function() local blob = test:execsql "SELECT SUBSTR(x'1234', -1, 1)" return bin_to_hex(test.lindex(blob, 0)) - end, "34") + end, "") test:do_test( "func2-3.4.2", function() local blob = test:execsql "SELECT SUBSTR(x'1234', -1, 2)" return bin_to_hex(test.lindex(blob, 0)) - end, "34") + end, "") test:do_test( "func2-3.4.3", function() local blob = test:execsql "SELECT SUBSTR(x'1234', -1, 3)" return bin_to_hex(test.lindex(blob, 0)) - end, "34") + end, "12") test:do_test( "func2-3.5.0", @@ -1158,21 +1160,21 @@ test:do_test( function() local blob = test:execsql "SELECT SUBSTR(x'1234', -2, 1)" return bin_to_hex(test.lindex(blob, 0)) - end, "12") + end, "") test:do_test( "func2-3.5.2", function() local blob = test:execsql "SELECT SUBSTR(x'1234', -2, 2)" return bin_to_hex(test.lindex(blob, 0)) - end, "1234") + end, "") test:do_test( "func2-3.5.3", function() local blob = test:execsql "SELECT SUBSTR(x'1234', -2, 3)" return bin_to_hex(test.lindex(blob, 0)) - end, "1234") + end, "") test:do_test( "func2-3.6.0", @@ -1181,26 +1183,28 @@ test:do_test( return bin_to_hex(test.lindex(blob, 0)) end, "") +local err = [[Failed to execute SQL statement: Length of the result cannot ]].. + [[be less than 0]] test:do_test( "func2-3.6.1", function() - local blob = test:execsql "SELECT SUBSTR(x'1234', -1, -1)" - return bin_to_hex(test.lindex(blob, 0)) - end, "12") + return test:catchsql("SELECT SUBSTR(x'1234', -1, -1)") + end, + {1, err}) test:do_test( "func2-3.6.2", function() - local blob = test:execsql "SELECT SUBSTR(x'1234', -1, -2)" - return bin_to_hex(test.lindex(blob, 0)) - end, "12") + return test:catchsql("SELECT SUBSTR(x'1234', -1, -2)") + end, + {1, err}) test:do_test( "func2-3.6.3", function() - local blob = test:execsql "SELECT SUBSTR(x'1234', -1, -3)" - return bin_to_hex(test.lindex(blob, 0)) - end, "12") + return test:catchsql("SELECT SUBSTR(x'1234', -1, -3)") + end, + {1, err}) test:do_test( "func2-3.7.0", @@ -1212,16 +1216,16 @@ test:do_test( test:do_test( "func2-3.7.1", function() - local blob = test:execsql "SELECT SUBSTR(x'1234', -2, -1)" - return bin_to_hex(test.lindex(blob, 0)) - end, "") + return test:catchsql("SELECT SUBSTR(x'1234', -2, -1)") + end, + {1, err}) test:do_test( "func2-3.7.2", function() - local blob = test:execsql "SELECT SUBSTR(x'1234', -2, -2)" - return bin_to_hex(test.lindex(blob, 0)) - end, "") + return test:catchsql("SELECT SUBSTR(x'1234', -2, -2)") + end, + {1, err}) test:do_test( "func2-3.8.0", @@ -1233,16 +1237,16 @@ test:do_test( test:do_test( "func2-3.8.1", function() - local blob = test:execsql "SELECT SUBSTR(x'1234', 1, -1)" - return bin_to_hex(test.lindex(blob, 0)) - end, "") + return test:catchsql("SELECT SUBSTR(x'1234', 1, -1)") + end, + {1, err}) test:do_test( "func2-3.8.2", function() - local blob = test:execsql "SELECT SUBSTR(x'1234', 1, -2)" - return bin_to_hex(test.lindex(blob, 0)) - end, "") + return test:catchsql("SELECT SUBSTR(x'1234', 1, -2)") + end, + {1, err}) test:do_test( "func2-3.9.0", @@ -1254,17 +1258,15 @@ test:do_test( test:do_test( "func2-3.9.1", function() - local blob = test:execsql "SELECT SUBSTR(x'1234', 2, -1)" - return bin_to_hex(test.lindex(blob, 0)) - end, "12") + return test:catchsql("SELECT SUBSTR(x'1234', 2, -2)") + end, + {1, err}) test:do_test( "func2-3.9.2", function() - local blob = test:execsql "SELECT SUBSTR(x'1234', 2, -2)" - return bin_to_hex(test.lindex(blob, 0)) - end, "12") - - + return test:catchsql("SELECT SUBSTR(x'1234', 2, -2)") + end, + {1, err}) test:finish_test() diff --git a/test/sql-tap/substr.test.lua b/test/sql-tap/substr.test.lua index e7e6d7aca..45aae8506 100755 --- a/test/sql-tap/substr.test.lua +++ b/test/sql-tap/substr.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool local test = require("sqltester") -test:plan(93) +test:plan(85) --!./tcltestrunner.lua -- 2007 May 14 @@ -74,17 +74,11 @@ substr_test("1.2", "abcdefg","2","1","b") substr_test("1.3", "abcdefg","1","2","ab") substr_test("1.4", "abcdefg","1","100","abcdefg") substr_test("1.5", "abcdefg","0","2","a") -substr_test("1.6", "abcdefg","-1","1","g") -substr_test("1.7", "abcdefg","-1","10","g") -substr_test("1.8", "abcdefg","-5","3","cde") -substr_test("1.9", "abcdefg","-7","3","abc") -substr_test("1.10", "abcdefg","-100","98","abcde") -substr_test("1.11", "abcdefg","5","-1","d") -substr_test("1.12", "abcdefg","5","-4","abcd") -substr_test("1.13", "abcdefg","5","-5","abcd") -substr_test("1.14", "abcdefg","-5","-1","b") -substr_test("1.15", "abcdefg","-5","-2","ab") -substr_test("1.16", "abcdefg","-5","-3","ab") +substr_test("1.6", "abcdefg","-1","1","") +substr_test("1.7", "abcdefg","-1","10","abcdefg") +substr_test("1.8", "abcdefg","-5","3","") +substr_test("1.9", "abcdefg","-7","3","") +substr_test("1.10", "abcdefg","-100","98","") substr_test("1.17", "abcdefg","100","200","") substr_test("1.18", "abcdefg","200","100","") -- Make sure NULL is returned if any parameter is NULL @@ -144,21 +138,20 @@ test:do_test( substr_test("2.1", "ሴ⍅㑖","1","1","ሴ") substr_test("2.2", "ሴ⍅㑖","2","1","⍅") substr_test("2.3", "ሴ⍅㑖","1","2","ሴ⍅") -substr_test("2.4", "ሴ⍅㑖","-1","1","㑖") -substr_test("2.5", "aሴb⍅c㑖c","-5","3","b⍅c") -substr_test("2.6", "aሴb⍅c㑖c","-2","-3","b⍅c") +substr_test("2.4", "ሴ⍅㑖","-1","1","") +substr_test("2.5", "aሴb⍅c㑖c","-5","3","") -- Basic functionality for BLOBs -- subblob_test("3.1", "61626364656667","1","1","61") subblob_test("3.2", "61626364656667","2","1","62") subblob_test("3.3", "61626364656667","1","2","6162") subblob_test("3.4", "61626364656667","1","100","61626364656667") -subblob_test("3.5", "61626364656667","0","2","61") -subblob_test("3.6", "61626364656667","-1","1","67") -subblob_test("3.7", "61626364656667","-1","10","67") -subblob_test("3.8", "61626364656667","-5","3","636465") -subblob_test("3.9", "61626364656667","-7","3","616263") -subblob_test("3.10", "61626364656667","-100","98","6162636465") +subblob_test("3.5", "61626364656667", "0", "2", "61") +subblob_test("3.6", "61626364656667", "-1", "1", "") +subblob_test("3.7", "61626364656667", "-1", "10", "61626364656667") +subblob_test("3.8", "61626364656667", "-5", "3", "") +subblob_test("3.9", "61626364656667","-7","3", "") +subblob_test("3.10", "61626364656667", "-100", "98", "") subblob_test("3.11", "61626364656667","100","200","") subblob_test("3.12", "61626364656667","200","100","") -- If these blobs were strings, then they would contain multi-byte @@ -168,9 +161,9 @@ subblob_test("3.12", "61626364656667","200","100","") subblob_test("4.1", "61E188B462E28D8563E3919663","1","1","61") subblob_test("4.2", "61E188B462E28D8563E3919663","2","1","E1") subblob_test("4.3", "61E188B462E28D8563E3919663","1","2","61E1") -subblob_test("4.4", "61E188B462E28D8563E3919663","-2","1","96") -subblob_test("4.5", "61E188B462E28D8563E3919663","-5","4","63E39196") -subblob_test("4.6", "61E188B462E28D8563E3919663","-100","98","61E188B462E28D8563E391") +subblob_test("4.4", "61E188B462E28D8563E3919663", "-2", "1", "") +subblob_test("4.5", "61E188B462E28D8563E3919663", "-5", "4", "") +subblob_test("4.6", "61E188B462E28D8563E3919663", "-100", "98", "") -- Two-argument SUBSTR -- local function substr_2_test(id, string, idx, result) @@ -193,7 +186,85 @@ local function substr_2_test(id, string, idx, result) end substr_2_test("5.1","abcdefghijklmnop","5","efghijklmnop") -substr_2_test("5.2","abcdef","-5","bcdef") +substr_2_test("5.2","abcdef","-5","abcdef") +-- +-- gh-4145: Make sure SUBSTR() throws an error if the third argument is +-- negative. +-- +test:do_catchsql_test( + "substr-6.1", + [[ + SELECT SUBSTR('12345', 1, -1); + ]], + { + 1, [[Failed to execute SQL statement: ]].. + [[Length of the result cannot be less than 0]] + } +) + +test:do_catchsql_test( + "substr-6.2", + [[ + SELECT SUBSTR(x'3132333435', 1, -1); + ]], + { + 1, [[Failed to execute SQL statement: ]].. + [[Length of the result cannot be less than 0]] + } +) + +-- gh-4145: Make sure that SUBSTR() works according to ANSI. + +-- +-- Make sure SUBSTR() returns "" if the sum of the second and third arguments is +-- 1 or less. +-- +test:do_execsql_test( + "builtins-6.3", + [[ + SELECT SUBSTR('asdfg', -10, 5), SUBSTR('asdfg', -4, 5); + ]], + { + '', '' + } +) + +-- +-- Make sure that if the sum of the second and third arguments is more than 1 +-- and the second argument is negative, the result starts from the start of the +-- string and length of the result will be one less than sum of the second and +-- third arguments. +-- +test:do_execsql_test( + "builtins-6.4", + [[ + SELECT SUBSTR('123456789', -5, 10); + ]], + { + '1234' + } +) + +-- Make sure SUBSTR() can work with big INTEGERs. +test:do_execsql_test( + "builtins-6.5", + [[ + SELECT SUBSTR('123456789', -9223372036854775808, 9223372036854775812); + ]], + { + '123' + } +) + +test:do_execsql_test( + "builtins-6.6", + [[ + SELECT SUBSTR('123456789', 0, 18000000000000000000); + ]], + { + '123456789' + } +) test:finish_test() -- 2.25.1