From: Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org> To: kyukhin@tarantool.org Cc: tarantool-patches@dev.tarantool.org Subject: [Tarantool-patches] [PATCH v1 7/8] sql: rework SUBSTR() function Date: Thu, 11 Nov 2021 13:45:39 +0300 [thread overview] Message-ID: <99cea2a655c9df90ae50e70148efee72ecdc2be3.1636627366.git.imeevma@gmail.com> (raw) In-Reply-To: <cover.1636627365.git.imeevma@gmail.com> 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 ]], { -- <func-2.3> - "e", "s", "m", "e", "s" + "", "", "", "", "" -- </func-2.3> }) @@ -149,7 +149,7 @@ test:do_execsql_test( SELECT substr(t1,-1,2) FROM tbl1 ORDER BY t1 ]], { -- <func-2.4> - "e", "s", "m", "e", "s" + "", "", "", "", "" -- </func-2.4> }) @@ -159,7 +159,7 @@ test:do_execsql_test( SELECT substr(t1,-2,1) FROM tbl1 ORDER BY t1 ]], { -- <func-2.5> - "e", "i", "a", "r", "i" + "", "", "", "", "" -- </func-2.5> }) @@ -169,7 +169,7 @@ test:do_execsql_test( SELECT substr(t1,-2,2) FROM tbl1 ORDER BY t1 ]], { -- <func-2.6> - "ee", "is", "am", "re", "is" + "", "", "", "", "" -- </func-2.6> }) @@ -179,7 +179,7 @@ test:do_execsql_test( SELECT substr(t1,-4,2) FROM tbl1 ORDER BY t1 ]], { -- <func-2.7> - "fr", "", "gr", "wa", "th" + "", "", "", "", "" -- </func-2.7> }) @@ -288,7 +288,7 @@ if ("ሴ" ~= "u1234") then SELECT substr(t1,-1,1) FROM tbl1 ORDER BY t1 ]], { -- <func-3.8> - "8", "s", "s", "o" + "", "", "", "" -- </func-3.8> }) @@ -298,7 +298,7 @@ if ("ሴ" ~= "u1234") then SELECT substr(t1,-3,2) FROM tbl1 ORDER BY t1 ]], { -- <func-3.9> - "F-", "er", "in", "ሴh" + "", "", "", "" -- </func-3.9> }) @@ -308,7 +308,7 @@ if ("ሴ" ~= "u1234") then SELECT substr(t1,-4,3) FROM tbl1 ORDER BY t1 ]], { -- <func-3.10> - "TF-", "ter", "ain", "iሴh" + "", "", "", "" -- </func-3.10> }) 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) ]], { -- <func2-1.11> - "s" + "Supercalifragilisticexpialidocious" -- </func2-1.11> }) @@ -172,7 +172,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -2) ]], { -- <func2-1.12> - "us" + "Supercalifragilisticexpialidocious" -- </func2-1.12> }) @@ -182,7 +182,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -30) ]], { -- <func2-1.13> - "rcalifragilisticexpialidocious" + "Supercalifragilisticexpialidocious" -- </func2-1.13> }) @@ -344,7 +344,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -1, 1) ]], { -- <func2-1.25.1> - "s" + "" -- </func2-1.25.1> }) @@ -354,7 +354,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -1, 2) ]], { -- <func2-1.25.2> - "s" + "" -- </func2-1.25.2> }) @@ -364,7 +364,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -2, 1) ]], { -- <func2-1.26> - "u" + "" -- </func2-1.26> }) @@ -374,7 +374,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -30, 1) ]], { -- <func2-1.27> - "r" + "" -- </func2-1.27> }) @@ -394,7 +394,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -34, 1) ]], { -- <func2-1.28.1> - "S" + "" -- </func2-1.28.1> }) @@ -404,7 +404,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -34, 2) ]], { -- <func2-1.28.2> - "Su" + "" -- </func2-1.28.2> }) @@ -424,7 +424,7 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -35, 2) ]], { -- <func2-1.29.2> - "S" + "" -- </func2-1.29.2> }) @@ -464,11 +464,13 @@ test:do_execsql_test( SELECT SUBSTR('Supercalifragilisticexpialidocious', -36, 3) ]], { -- <func2-1.30.3> - "S" + "" -- </func2-1.30.3> }) -- 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( -- </func2-1.31.0> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.31.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 0, -1) ]], { -- <func2-1.31.1> - "" + 1, err -- </func2-1.31.1> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.31.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 0, -2) ]], { -- <func2-1.31.2> - "" + 1, err -- </func2-1.31.2> }) @@ -509,13 +511,13 @@ test:do_execsql_test( -- </func2-1.32.0> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.32.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 1, -1) ]], { -- <func2-1.32.1> - "" + 1, err -- </func2-1.32.1> }) @@ -529,23 +531,23 @@ test:do_execsql_test( -- </func2-1.33.0> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.33.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, -1) ]], { -- <func2-1.33.1> - "S" + 1, err -- </func2-1.33.1> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.33.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 2, -2) ]], { -- <func2-1.33.2> - "S" + 1, err -- </func2-1.33.2> }) @@ -559,63 +561,63 @@ test:do_execsql_test( -- </func2-1.34.0> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.34.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 3, -1) ]], { -- <func2-1.34.1> - "u" + 1, err -- </func2-1.34.1> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.34.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 3, -2) ]], { -- <func2-1.34.2> - "Su" + 1, err -- </func2-1.34.2> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.35.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 30, -1) ]], { -- <func2-1.35.1> - "o" + 1, err -- </func2-1.35.1> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.35.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 30, -2) ]], { -- <func2-1.35.2> - "do" + 1, err -- </func2-1.35.2> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.36", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 34, -1) ]], { -- <func2-1.36> - "u" + 1, err -- </func2-1.36> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.37", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 35, -1) ]], { -- <func2-1.37> - "s" + 1, err -- </func2-1.37> }) @@ -629,23 +631,23 @@ test:do_execsql_test( -- </func2-1.38.0> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.38.1", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 36, -1) ]], { -- <func2-1.38.1> - "" + 1, err -- </func2-1.38.1> }) -test:do_execsql_test( +test:do_catchsql_test( "func2-1.38.2", [[ SELECT SUBSTR('Supercalifragilisticexpialidocious', 36, -2) ]], { -- <func2-1.38.2> - "s" + 1, err -- </func2-1.38.2> }) @@ -993,7 +995,7 @@ if ("ሴ" ~= "u1234") SELECT SUBSTR('ሴ', -1, 1) ]], { -- <func2-2.8.1> - "ሴ" + "" -- </func2-2.8.1> }) @@ -1003,7 +1005,7 @@ if ("ሴ" ~= "u1234") SELECT SUBSTR('ሴ', -1, 2) ]], { -- <func2-2.8.2> - "ሴ" + "" -- </func2-2.8.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
next prev parent reply other threads:[~2021-11-11 10:48 UTC|newest] Thread overview: 15+ messages / expand[flat|nested] mbox.gz Atom feed top 2021-11-11 10:45 [Tarantool-patches] [PATCH v1 0/8] Rework standard function Mergen Imeev via Tarantool-patches 2021-11-11 10:45 ` [Tarantool-patches] [PATCH v1 1/8] sql: refactor ABS() funcion Mergen Imeev via Tarantool-patches 2021-11-11 10:45 ` [Tarantool-patches] [PATCH v1 2/8] sql: rework CHAR_LENGTH() function Mergen Imeev via Tarantool-patches 2021-11-11 10:45 ` [Tarantool-patches] [PATCH v1 3/8] sql: refactor UPPER() and LOWER() functions Mergen Imeev via Tarantool-patches 2021-11-11 10:45 ` [Tarantool-patches] [PATCH v1 4/8] sql: refactor NULLIF() function Mergen Imeev via Tarantool-patches 2021-11-11 10:45 ` [Tarantool-patches] [PATCH v1 5/8] sql: rework TRIM() function Mergen Imeev via Tarantool-patches 2021-11-11 10:45 ` [Tarantool-patches] [PATCH v1 6/8] sql: rework POSITION() function Mergen Imeev via Tarantool-patches 2021-11-11 10:45 ` Mergen Imeev via Tarantool-patches [this message] 2021-11-11 10:45 ` [Tarantool-patches] [PATCH v1 8/8] sql: refactor LIKE() function Mergen Imeev via Tarantool-patches 2021-11-11 10:59 ` [Tarantool-patches] [PATCH v1 0/8] Rework standard function Kirill Yukhin via Tarantool-patches -- strict thread matches above, loose matches on Subject: below -- 2021-10-01 16:29 Mergen Imeev via Tarantool-patches 2021-10-01 16:29 ` [Tarantool-patches] [PATCH v1 7/8] sql: rework SUBSTR() function Mergen Imeev via Tarantool-patches 2021-10-08 22:02 ` Vladislav Shpilevoy via Tarantool-patches 2021-10-20 17:15 ` Mergen Imeev via Tarantool-patches 2021-10-28 22:13 ` Vladislav Shpilevoy via Tarantool-patches 2021-11-01 10:45 ` Mergen Imeev via Tarantool-patches
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=99cea2a655c9df90ae50e70148efee72ecdc2be3.1636627366.git.imeevma@gmail.com \ --to=tarantool-patches@dev.tarantool.org \ --cc=imeevma@tarantool.org \ --cc=kyukhin@tarantool.org \ --subject='Re: [Tarantool-patches] [PATCH v1 7/8] sql: rework SUBSTR() function' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox