Tarantool development patches archive
 help / color / mirror / Atom feed
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


  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