* [tarantool-patches] [PATCH v2 1/1] sql: check argument types for SQL built-in functions
@ 2019-09-11 7:01 imeevma
0 siblings, 0 replies; only message in thread
From: imeevma @ 2019-09-11 7:01 UTC (permalink / raw)
To: korablev; +Cc: tarantool-patches
According to ANSI, some functions must accept arguments of a
certain type. This is currently not always the case. For example,
the LENGTH() function accepts arguments of any type.
This patch fixes the behavior of these functions, allowing them to
accept only certain types of arguments.
Closes #4159
---
https://github.com/tarantool/tarantool/issues/4159
https://github.com/tarantool/tarantool/tree/imeevma/gh-4159-check-function-argument-types
v1:
https://www.freelists.org/post/tarantool-patches/sql-make-LENGTH-to-accept-only-strings
src/box/sql/func.c | 253 ++++++++---------
src/box/sql/sqlInt.h | 2 +
test/sql-tap/cse.test.lua | 4 +-
test/sql-tap/func.test.lua | 39 ++-
test/sql-tap/func5.test.lua | 611 ++++++++++++++++++++++++++++++++++++++++-
test/sql-tap/orderby1.test.lua | 2 +-
test/sql/types.result | 36 +--
7 files changed, 780 insertions(+), 167 deletions(-)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 1d8f28a..ed463c0 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -434,31 +434,27 @@ typeofFunc(sql_context * context, int NotUsed, sql_value ** argv)
static void
lengthFunc(sql_context * context, int argc, sql_value ** argv)
{
- int len;
-
assert(argc == 1);
UNUSED_PARAMETER(argc);
- switch (sql_value_type(argv[0])) {
- case MP_BIN:
- case MP_INT:
- case MP_UINT:
- case MP_DOUBLE:{
- sql_result_uint(context, sql_value_bytes(argv[0]));
- break;
- }
- case MP_STR:{
- const unsigned char *z = sql_value_text(argv[0]);
- if (z == 0)
- return;
- len = sql_utf8_char_count(z, sql_value_bytes(argv[0]));
- sql_result_uint(context, len);
- break;
- }
- default:{
- sql_result_null(context);
- break;
- }
+ enum mp_type arg_type = sql_value_type(argv[0]);
+ if (arg_type != MP_STR && arg_type != MP_BIN && arg_type != MP_NIL) {
+ diag_set(ClientError, ER_INCONSISTENT_TYPES,
+ "TEXT or VARBINARY", mem_type_to_str(argv[0]));
+ context->is_aborted = true;
+ return;
}
+ if (arg_type == MP_NIL) {
+ sql_result_null(context);
+ return;
+ }
+ int len = sql_value_bytes(argv[0]);
+ if (arg_type == MP_STR) {
+ const unsigned char *z = sql_value_text(argv[0]);
+ if (z == NULL)
+ return;
+ len = sql_utf8_char_count(z, len);
+ }
+ sql_result_uint(context, len);
}
/*
@@ -472,41 +468,30 @@ absFunc(sql_context * context, int argc, sql_value ** argv)
{
assert(argc == 1);
UNUSED_PARAMETER(argc);
+ int64_t value;
+ double rVal;
switch (sql_value_type(argv[0])) {
- case MP_UINT: {
+ case MP_UINT:
sql_result_uint(context, sql_value_uint64(argv[0]));
break;
- }
- case MP_INT: {
- int64_t value = sql_value_int64(argv[0]);
+ case MP_INT:
+ value = sql_value_int64(argv[0]);
assert(value < 0);
sql_result_uint(context, -value);
break;
- }
- case MP_NIL:{
- /* IMP: R-37434-19929 Abs(X) returns NULL if X is NULL. */
- sql_result_null(context);
- break;
- }
- case MP_BOOL:
- case MP_BIN: {
- diag_set(ClientError, ER_INCONSISTENT_TYPES, "number",
+ case MP_DOUBLE:
+ rVal = sql_value_double(argv[0]);
+ if (rVal < 0)
+ rVal = -rVal;
+ sql_result_double(context, rVal);
+ break;
+ case MP_NIL:
+ sql_result_null(context);
+ break;
+ default:
+ diag_set(ClientError, ER_INCONSISTENT_TYPES, "NUMBER",
mem_type_to_str(argv[0]));
context->is_aborted = true;
- return;
- }
- default:{
- /* Because sql_value_double() returns 0.0 if the argument is not
- * something that can be converted into a number, we have:
- * IMP: R-01992-00519 Abs(X) returns 0.0 if X is a string or blob
- * that cannot be converted to a numeric value.
- */
- double rVal = sql_value_double(argv[0]);
- if (rVal < 0)
- rVal = -rVal;
- sql_result_double(context, rVal);
- break;
- }
}
}
@@ -861,63 +846,63 @@ contextMalloc(sql_context * context, i64 nByte)
/*
* Implementation of the upper() and lower() SQL functions.
*/
-
-#define ICU_CASE_CONVERT(case_type) \
-static void \
-case_type##ICUFunc(sql_context *context, int argc, sql_value **argv) \
-{ \
- char *z1; \
- const char *z2; \
- int n; \
- UNUSED_PARAMETER(argc); \
- int arg_type = sql_value_type(argv[0]); \
- if (arg_type == MP_BIN) { \
- diag_set(ClientError, ER_INCONSISTENT_TYPES, "TEXT", \
- "VARBINARY"); \
- context->is_aborted = true; \
- return; \
- } \
- z2 = (char *)sql_value_text(argv[0]); \
- n = sql_value_bytes(argv[0]); \
- /* \
- * Verify that the call to _bytes() \
- * does not invalidate the _text() pointer. \
- */ \
- assert(z2 == (char *)sql_value_text(argv[0])); \
- if (!z2) \
- return; \
- z1 = contextMalloc(context, ((i64) n) + 1); \
- if (z1 == NULL) { \
- context->is_aborted = true; \
- return; \
- } \
- UErrorCode status = U_ZERO_ERROR; \
- struct coll *coll = sqlGetFuncCollSeq(context); \
- const char *locale = NULL; \
- if (coll != NULL && coll->type == COLL_TYPE_ICU) { \
- locale = ucol_getLocaleByType(coll->collator, \
- ULOC_VALID_LOCALE, &status); \
- } \
- UCaseMap *case_map = ucasemap_open(locale, 0, &status); \
- assert(case_map != NULL); \
- int len = ucasemap_utf8To##case_type(case_map, z1, n, z2, n, &status); \
- if (len > n) { \
- status = U_ZERO_ERROR; \
- sql_free(z1); \
- z1 = contextMalloc(context, ((i64) len) + 1); \
- if (z1 == NULL) { \
- context->is_aborted = true; \
- return; \
- } \
- ucasemap_utf8To##case_type(case_map, z1, len, z2, n, &status); \
- } \
- ucasemap_close(case_map); \
- sql_result_text(context, z1, len, sql_free); \
-} \
-
-ICU_CASE_CONVERT(Lower);
-ICU_CASE_CONVERT(Upper);
-
+static void
+sql_upper_lower_icu_func(sql_context *context, int argc, sql_value **argv)
+{
+ assert(argc = 1);
+ UNUSED_PARAMETER(argc);
+ enum mp_type arg_type = sql_value_type(argv[0]);
+ if (arg_type != MP_STR && arg_type != MP_NIL) {
+ diag_set(ClientError, ER_INCONSISTENT_TYPES, "TEXT",
+ mem_type_to_str(argv[0]));
+ context->is_aborted = true;
+ return;
+ }
+ if (arg_type == MP_NIL) {
+ sql_result_null(context);
+ return;
+ }
+ bool is_upper = sql_func_flag_is_set(context->func, SQL_FUNC_UPPER);
+ const char *z2 = (char *)sql_value_text(argv[0]);
+ assert(z2 != NULL);
+ int n = sql_value_bytes(argv[0]);
+ /*
+ * Verify that the call to _bytes()
+ * does not invalidate the _text() pointer.
+ */
+ assert(z2 == (char *)sql_value_text(argv[0]));
+ char *z1 = contextMalloc(context, n + 1);
+ if (z1 == NULL) {
+ context->is_aborted = true;
+ return;
+ }
+ UErrorCode status = U_ZERO_ERROR;
+ struct coll *coll = sqlGetFuncCollSeq(context);
+ const char *locale = NULL;
+ if (coll != NULL && coll->type == COLL_TYPE_ICU) {
+ locale = ucol_getLocaleByType(coll->collator,
+ ULOC_VALID_LOCALE, &status);
+ }
+ UCaseMap *case_map = ucasemap_open(locale, 0, &status);
+ assert(case_map != NULL);
+ int len = is_upper ?
+ ucasemap_utf8ToUpper(case_map, z1, n, z2, n, &status) :
+ ucasemap_utf8ToLower(case_map, z1, n, z2, n, &status);
+ if (len > n) {
+ status = U_ZERO_ERROR;
+ sql_free(z1);
+ z1 = contextMalloc(context, len + 1);
+ if (z1 == NULL) {
+ context->is_aborted = true;
+ return;
+ }
+ len = is_upper ?
+ ucasemap_utf8ToUpper(case_map, z1, len, z2, n, &status) :
+ ucasemap_utf8ToLower(case_map, z1, len, z2, n, &status);
+ }
+ ucasemap_close(case_map);
+ sql_result_text(context, z1, len, sql_free);
+}
/*
* Some functions like COALESCE() and IFNULL() and UNLIKELY() are implemented
@@ -952,9 +937,10 @@ randomBlob(sql_context * context, int argc, sql_value ** argv)
unsigned char *p;
assert(argc == 1);
UNUSED_PARAMETER(argc);
- if (sql_value_type(argv[0]) == MP_BIN) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
- sql_value_text(argv[0]), "numeric");
+ enum mp_type arg_type = sql_value_type(argv[0]);
+ if (arg_type != MP_UINT && arg_type != MP_NIL) {
+ diag_set(ClientError, ER_INCONSISTENT_TYPES,
+ "UNSIGNED", mem_type_to_str(argv[0]));
context->is_aborted = true;
return;
}
@@ -1435,12 +1421,16 @@ charFunc(sql_context * context, int argc, sql_value ** argv)
return;
}
for (i = 0; i < argc; i++) {
- uint64_t x;
+ enum mp_type arg_type = sql_value_type(argv[i]);
+ if (arg_type != MP_UINT && arg_type != MP_NIL) {
+ sql_free(z);
+ diag_set(ClientError, ER_INCONSISTENT_TYPES, "UNSIGNED",
+ mem_type_to_str(argv[i]));
+ context->is_aborted = true;
+ return;
+ }
+ uint64_t x = sql_value_uint64(argv[i]);;
unsigned c;
- if (sql_value_type(argv[i]) == MP_INT)
- x = 0xfffd;
- else
- x = sql_value_uint64(argv[i]);
if (x > 0x10ffff)
x = 0xfffd;
c = (unsigned)(x & 0x1fffff);
@@ -1470,17 +1460,27 @@ charFunc(sql_context * context, int argc, sql_value ** argv)
static void
hexFunc(sql_context * context, int argc, sql_value ** argv)
{
- int i, n;
- const unsigned char *pBlob;
- char *zHex, *z;
+ enum mp_type arg_type = sql_value_type(argv[0]);
+
assert(argc == 1);
UNUSED_PARAMETER(argc);
- pBlob = sql_value_blob(argv[0]);
- n = sql_value_bytes(argv[0]);
+ if (arg_type != MP_STR && arg_type != MP_BIN && arg_type != MP_NIL) {
+ diag_set(ClientError, ER_INCONSISTENT_TYPES,
+ "TEXT or VARBINARY", mem_type_to_str(argv[0]));
+ context->is_aborted = true;
+ return;
+ }
+ if (arg_type == MP_NIL) {
+ sql_result_null(context);
+ return;
+ }
+ const unsigned char *pBlob = sql_value_blob(argv[0]);
+ int n = sql_value_bytes(argv[0]);
assert(pBlob == sql_value_blob(argv[0])); /* No encoding change */
+ char *zHex, *z;
z = zHex = contextMalloc(context, ((i64) n) * 2 + 1);
if (zHex) {
- for (i = 0; i < n; i++, pBlob++) {
+ for (int i = 0; i < n; i++, pBlob++) {
unsigned char c = *pBlob;
*(z++) = hexdigits[(c >> 4) & 0xf];
*(z++) = hexdigits[c & 0xf];
@@ -1840,9 +1840,10 @@ soundexFunc(sql_context * context, int argc, sql_value ** argv)
1, 2, 6, 2, 3, 0, 1, 0, 2, 0, 2, 0, 0, 0, 0, 0,
};
assert(argc == 1);
- if (sql_value_type(argv[0]) == MP_BIN) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
- sql_value_text(argv[0]), "TEXT");
+ enum mp_type arg_type = sql_value_type(argv[0]);
+ if (arg_type != MP_STR && arg_type != MP_NIL) {
+ diag_set(ClientError, ER_INCONSISTENT_TYPES,
+ "TEXT", mem_type_to_str(argv[0]));
context->is_aborted = true;
return;
}
@@ -2520,7 +2521,7 @@ static struct {
.aggregate = FUNC_AGGREGATE_NONE,
.is_deterministic = true,
.flags = SQL_FUNC_DERIVEDCOLL | SQL_FUNC_NEEDCOLL,
- .call = LowerICUFunc,
+ .call = sql_upper_lower_icu_func,
.finalize = NULL,
.export_to_sql = true,
}, {
@@ -2789,8 +2790,8 @@ static struct {
.returns = FIELD_TYPE_STRING,
.aggregate = FUNC_AGGREGATE_NONE,
.is_deterministic = true,
- .flags = SQL_FUNC_DERIVEDCOLL | SQL_FUNC_NEEDCOLL,
- .call = UpperICUFunc,
+ .flags = SQL_FUNC_DERIVEDCOLL | SQL_FUNC_NEEDCOLL | SQL_FUNC_UPPER,
+ .call = sql_upper_lower_icu_func,
.finalize = NULL,
.export_to_sql = true,
}, {
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index e617edd..c5b7f7a 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -1237,6 +1237,8 @@ struct type_def {
* argument.
*/
#define SQL_FUNC_DERIVEDCOLL 0x4000
+/** Built-in upper() function. */
+#define SQL_FUNC_UPPER 0x8000
/*
* Trim side mask components. TRIM_LEADING means to trim left side
diff --git a/test/sql-tap/cse.test.lua b/test/sql-tap/cse.test.lua
index 341b6de..498689c 100755
--- a/test/sql-tap/cse.test.lua
+++ b/test/sql-tap/cse.test.lua
@@ -198,7 +198,7 @@ test:do_execsql_test(
test:do_execsql_test(
"cse-1.13",
[[
- SELECT upper(b), typeof(b), b FROM t1
+ SELECT upper(CAST(b AS TEXT)), typeof(b), b FROM t1
]], {
-- <cse-1.13>
"11", "integer", 11, "21", "integer", 21
@@ -208,7 +208,7 @@ test:do_execsql_test(
test:do_execsql_test(
"cse-1.14",
[[
- SELECT b, typeof(b), upper(b), typeof(b), b FROM t1
+ SELECT b, typeof(b), upper(CAST(b AS TEXT)), typeof(b), b FROM t1
]], {
-- <cse-1.14>
11, "integer", "11", "integer", 11, 21, "integer", "21", "integer", 21
diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua
index a96fc61..381f3f8 100755
--- a/test/sql-tap/func.test.lua
+++ b/test/sql-tap/func.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(14606)
+test:plan(14608)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -95,7 +95,7 @@ test:do_execsql_test(
test:do_execsql_test(
"func-1.4",
[[
- SELECT coalesce(length(a),-1) FROM t2
+ SELECT coalesce(length(CAST(a AS TEXT)),-1) FROM t2
]], {
-- <func-1.4>
1, -1, 3, -1, 5
@@ -412,13 +412,13 @@ test:do_execsql_test(
-- </func-4.4.1>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"func-4.4.2",
[[
SELECT abs(t1) FROM tbl1
]], {
-- <func-4.4.2>
- 0.0, 0.0, 0.0, 0.0, 0.0
+ 1, "Inconsistent types: expected NUMBER got TEXT"
-- </func-4.4.2>
})
@@ -760,17 +760,25 @@ test:do_execsql_test(
-- </func-5.2>
})
-test:do_execsql_test(
+test:do_catchsql_test(
"func-5.3",
[[
SELECT upper(a), lower(a) FROM t2
]], {
-- <func-5.3>
- "1","1","","","345","345","","","67890","67890"
+ 1, "Inconsistent types: expected TEXT got UNSIGNED"
-- </func-5.3>
})
-
+test:do_catchsql_test(
+ "func-5.4",
+ [[
+ SELECT lower(a) FROM t2
+ ]], {
+ -- <func-5.4>
+ 1, "Inconsistent types: expected TEXT got UNSIGNED"
+ -- </func-5.4>
+ })
test:do_catchsql_test(
"func-5.5",
@@ -797,7 +805,7 @@ test:do_execsql_test(
test:do_execsql_test(
"func-6.2",
[[
- SELECT coalesce(upper(a),'nil') FROM t2
+ SELECT coalesce(upper(CAST(a AS TEXT)),'nil') FROM t2
]], {
-- <func-6.2>
"1","nil","345","nil","67890"
@@ -985,14 +993,23 @@ test:do_execsql_test(
test:do_execsql_test(
"func-9.5",
[[
- SELECT length(randomblob(32)), length(randomblob(-5)),
- length(randomblob(2000))
+ SELECT length(randomblob(32)), length(randomblob(2000))
]], {
-- <func-9.5>
- 32, "", 2000
+ 32, 2000
-- </func-9.5>
})
+test:do_catchsql_test(
+ "func-9.6",
+ [[
+ SELECT randomblob(-5);
+ ]], {
+ -- <func-9.6>
+ 1, "Inconsistent types: expected UNSIGNED got INTEGER"
+ -- </func-9.6>
+ })
+
-- The "hex()" function was added in order to be able to render blobs
-- generated by randomblob(). So this seems like a good place to test
-- hex().
diff --git a/test/sql-tap/func5.test.lua b/test/sql-tap/func5.test.lua
index 8329e17..db544c8 100755
--- a/test/sql-tap/func5.test.lua
+++ b/test/sql-tap/func5.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(22)
+test:plan(73)
--!./tcltestrunner.lua
-- 2010 August 27
@@ -293,4 +293,613 @@ test:do_catchsql_test(
box.func.COUNTER1:drop()
box.func.COUNTER2:drop()
+--
+-- gh-4159: Make sure that functions accept arguments with right
+-- types.
+--
+test:do_execsql_test(
+ "func-5-6.1",
+ [[
+ SELECT LENGTH('some text'), LENGTH(X'1020304050'), LENGTH(NULL);
+ ]], {
+ -- <func5-6.1>
+ 9, 5, ""
+ -- </func5-6.1>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.2",
+ [[
+ SELECT LENGTH(true);
+ ]],
+ {
+ -- <func5-6.2>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got BOOLEAN"
+ -- </func5-6.2>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.3",
+ [[
+ SELECT LENGTH(false);
+ ]],
+ {
+ -- <func5-6.3>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got BOOLEAN"
+ -- </func5-6.3>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.4",
+ [[
+ SELECT LENGTH(12345);
+ ]],
+ {
+ -- <func5-6.4>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got UNSIGNED"
+ -- </func5-6.4>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.5",
+ [[
+ SELECT LENGTH(-12345);
+ ]],
+ {
+ -- <func5-6.5>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got INTEGER"
+ -- </func5-6.5>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.6",
+ [[
+ SELECT LENGTH(123.45);
+ ]],
+ {
+ -- <func5-6.6>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got REAL"
+ -- </func5-6.6>
+ }
+)
+
+test:do_execsql_test(
+ "func-5-6.7",
+ [[
+ SELECT ABS(12345), ABS(-12345), ABS(123.45), ABS(-123.45);
+ ]], {
+ -- <func5-6.9>
+ 12345, 12345, 123.45, 123.45
+ -- </func5-6.9>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.8",
+ [[
+ SELECT ABS('12345');
+ ]],
+ {
+ -- <func5-6.8>
+ 1, "Inconsistent types: expected NUMBER got TEXT"
+ -- </func5-6.8>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.9",
+ [[
+ SELECT ABS(X'102030');
+ ]],
+ {
+ -- <func5-6.9>
+ 1, "Inconsistent types: expected NUMBER got VARBINARY"
+ -- </func5-6.9>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.10",
+ [[
+ SELECT ABS(false);
+ ]],
+ {
+ -- <func5-6.10>
+ 1, "Inconsistent types: expected NUMBER got BOOLEAN"
+ -- </func5-6.10>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.11",
+ [[
+ SELECT ABS(true);
+ ]],
+ {
+ -- <func5-6.11>
+ 1, "Inconsistent types: expected NUMBER got BOOLEAN"
+ -- </func5-6.11>
+ }
+)
+
+test:do_execsql_test(
+ "func-5-6.12",
+ [[
+ SELECT UPPER('sOmE tExt'), LOWER('sOmE tExt'), UPPER(NULL), LOWER(NULL);
+ ]], {
+ -- <func5-6.12>
+ "SOME TEXT", "some text", "", ""
+ -- </func5-6.12>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.13",
+ [[
+ SELECT UPPER(true);
+ ]],
+ {
+ -- <func5-6.13>
+ 1, "Inconsistent types: expected TEXT got BOOLEAN"
+ -- </func5-6.13>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.14",
+ [[
+ SELECT UPPER(false);
+ ]],
+ {
+ -- <func5-6.14>
+ 1, "Inconsistent types: expected TEXT got BOOLEAN"
+ -- </func5-6.14>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.15",
+ [[
+ SELECT UPPER(12345);
+ ]],
+ {
+ -- <func5-6.15>
+ 1, "Inconsistent types: expected TEXT got UNSIGNED"
+ -- </func5-6.15>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.16",
+ [[
+ SELECT UPPER(-12345);
+ ]],
+ {
+ -- <func5-6.16>
+ 1, "Inconsistent types: expected TEXT got INTEGER"
+ -- </func5-6.16>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.17",
+ [[
+ SELECT UPPER(123.45);
+ ]],
+ {
+ -- <func5-6.17>
+ 1, "Inconsistent types: expected TEXT got REAL"
+ -- </func5-6.17>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.18",
+ [[
+ SELECT UPPER(X'102030');
+ ]],
+ {
+ -- <func5-6.18>
+ 1, "Inconsistent types: expected TEXT got VARBINARY"
+ -- </func5-6.18>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.19",
+ [[
+ SELECT LOWER(true);
+ ]],
+ {
+ -- <func5-6.19>
+ 1, "Inconsistent types: expected TEXT got BOOLEAN"
+ -- </func5-6.19>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.20",
+ [[
+ SELECT LOWER(false);
+ ]],
+ {
+ -- <func5-6.20>
+ 1, "Inconsistent types: expected TEXT got BOOLEAN"
+ -- </func5-6.20>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.21",
+ [[
+ SELECT LOWER(12345);
+ ]],
+ {
+ -- <func5-6.21>
+ 1, "Inconsistent types: expected TEXT got UNSIGNED"
+ -- </func5-6.21>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.22",
+ [[
+ SELECT LOWER(-12345);
+ ]],
+ {
+ -- <func5-6.22>
+ 1, "Inconsistent types: expected TEXT got INTEGER"
+ -- </func5-6.22>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.23",
+ [[
+ SELECT LOWER(123.45);
+ ]],
+ {
+ -- <func5-6.23>
+ 1, "Inconsistent types: expected TEXT got REAL"
+ -- </func5-6.23>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.24",
+ [[
+ SELECT LOWER(X'102030');
+ ]],
+ {
+ -- <func5-6.24>
+ 1, "Inconsistent types: expected TEXT got VARBINARY"
+ -- </func5-6.24>
+ }
+)
+
+test:do_execsql_test(
+ "func-5-6.25",
+ [[
+ SELECT LENGTH(a), TYPEOF(a) from (SELECT RANDOMBLOB(12) AS a);
+ ]], {
+ -- <func5-6.25>
+ 12, "varbinary"
+ -- </func5-6.25>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.26",
+ [[
+ SELECT RANDOMBLOB(true);
+ ]],
+ {
+ -- <func5-6.26>
+ 1, "Inconsistent types: expected UNSIGNED got BOOLEAN"
+ -- </func5-6.26>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.27",
+ [[
+ SELECT RANDOMBLOB(false);
+ ]],
+ {
+ -- <func5-6.27>
+ 1, "Inconsistent types: expected UNSIGNED got BOOLEAN"
+ -- </func5-6.27>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.28",
+ [[
+ SELECT RANDOMBLOB(-12345);
+ ]],
+ {
+ -- <func5-6.28>
+ 1, "Inconsistent types: expected UNSIGNED got INTEGER"
+ -- </func5-6.28>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.29",
+ [[
+ SELECT RANDOMBLOB(123.45);
+ ]],
+ {
+ -- <func5-6.29>
+ 1, "Inconsistent types: expected UNSIGNED got REAL"
+ -- </func5-6.29>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.30",
+ [[
+ SELECT RANDOMBLOB('102030');
+ ]],
+ {
+ -- <func5-6.30>
+ 1, "Inconsistent types: expected UNSIGNED got TEXT"
+ -- </func5-6.30>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.31",
+ [[
+ SELECT RANDOMBLOB(X'102030');
+ ]],
+ {
+ -- <func5-6.31>
+ 1, "Inconsistent types: expected UNSIGNED got VARBINARY"
+ -- </func5-6.31>
+ }
+)
+
+test:do_execsql_test(
+ "func-5-6.32",
+ [[
+ SELECT CHAR(70, NULL, 80, NULL, 90);
+ ]], {
+ -- <func5-6.32>
+ "F\0P\0Z"
+ -- </func5-6.32>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.33",
+ [[
+ SELECT CHAR(true);
+ ]],
+ {
+ -- <func5-6.33>
+ 1, "Inconsistent types: expected UNSIGNED got BOOLEAN"
+ -- </func5-6.33>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.34",
+ [[
+ SELECT CHAR(false);
+ ]],
+ {
+ -- <func5-6.34>
+ 1, "Inconsistent types: expected UNSIGNED got BOOLEAN"
+ -- </func5-6.34>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.35",
+ [[
+ SELECT CHAR(-12345);
+ ]],
+ {
+ -- <func5-6.35>
+ 1, "Inconsistent types: expected UNSIGNED got INTEGER"
+ -- </func5-6.35>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.36",
+ [[
+ SELECT CHAR(123.45);
+ ]],
+ {
+ -- <func5-6.36>
+ 1, "Inconsistent types: expected UNSIGNED got REAL"
+ -- </func5-6.36>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.37",
+ [[
+ SELECT CHAR('102030');
+ ]],
+ {
+ -- <func5-6.37>
+ 1, "Inconsistent types: expected UNSIGNED got TEXT"
+ -- </func5-6.37>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.38",
+ [[
+ SELECT CHAR(X'102030');
+ ]],
+ {
+ -- <func5-6.38>
+ 1, "Inconsistent types: expected UNSIGNED got VARBINARY"
+ -- </func5-6.38>
+ }
+)
+
+test:do_execsql_test(
+ "func-5-6.39",
+ [[
+ SELECT HEX('some text'), HEX(X'1020304050'), HEX(NULL);
+ ]], {
+ -- <func5-6.39>
+ "736F6D652074657874", "1020304050", ""
+ -- </func5-6.39>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.40",
+ [[
+ SELECT HEX(true);
+ ]],
+ {
+ -- <func5-6.40>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got BOOLEAN"
+ -- </func5-6.40>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.41",
+ [[
+ SELECT HEX(false);
+ ]],
+ {
+ -- <func5-6.41>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got BOOLEAN"
+ -- </func5-6.41>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.42",
+ [[
+ SELECT HEX(12345);
+ ]],
+ {
+ -- <func5-6.42>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got UNSIGNED"
+ -- </func5-6.42>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.43",
+ [[
+ SELECT HEX(-12345);
+ ]],
+ {
+ -- <func5-6.43>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got INTEGER"
+ -- </func5-6.43>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.44",
+ [[
+ SELECT HEX(123.45);
+ ]],
+ {
+ -- <func5-6.44>
+ 1, "Inconsistent types: expected TEXT or VARBINARY got REAL"
+ -- </func5-6.44>
+ }
+)
+
+test:do_execsql_test(
+ "func-5-6.45",
+ [[
+ SELECT SOUNDEX('some text'), SOUNDEX(NULL);
+ ]], {
+ -- <func5-6.45>
+ "S532","?000"
+ -- </func5-6.45>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.46",
+ [[
+ SELECT SOUNDEX(true);
+ ]],
+ {
+ -- <func5-6.46>
+ 1, "Inconsistent types: expected TEXT got BOOLEAN"
+ -- </func5-6.46>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.47",
+ [[
+ SELECT SOUNDEX(false);
+ ]],
+ {
+ -- <func5-6.47>
+ 1, "Inconsistent types: expected TEXT got BOOLEAN"
+ -- </func5-6.47>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.48",
+ [[
+ SELECT SOUNDEX(12345);
+ ]],
+ {
+ -- <func5-6.48>
+ 1, "Inconsistent types: expected TEXT got UNSIGNED"
+ -- </func5-6.48>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.49",
+ [[
+ SELECT SOUNDEX(-12345);
+ ]],
+ {
+ -- <func5-6.49>
+ 1, "Inconsistent types: expected TEXT got INTEGER"
+ -- </func5-6.49>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.50",
+ [[
+ SELECT SOUNDEX(123.45);
+ ]],
+ {
+ -- <func5-6.50>
+ 1, "Inconsistent types: expected TEXT got REAL"
+ -- </func5-6.50>
+ }
+)
+
+test:do_catchsql_test(
+ "func-5-6.51",
+ [[
+ SELECT SOUNDEX(X'102030');
+ ]],
+ {
+ -- <func5-6.51>
+ 1, "Inconsistent types: expected TEXT got VARBINARY"
+ -- </func5-6.51>
+ }
+)
+
test:finish_test()
diff --git a/test/sql-tap/orderby1.test.lua b/test/sql-tap/orderby1.test.lua
index 51e8d30..37db9ac 100755
--- a/test/sql-tap/orderby1.test.lua
+++ b/test/sql-tap/orderby1.test.lua
@@ -735,7 +735,7 @@ test:do_execsql_test(
SELECT (
SELECT 'hardware' FROM (
SELECT 'software' ORDER BY 'firmware' ASC, 'sportswear' DESC
- ) GROUP BY 1 HAVING length(b) <> 0
+ ) GROUP BY 1 HAVING length(CAST(b AS TEXT)) <> 0
)
FROM abc;
]], {
diff --git a/test/sql/types.result b/test/sql/types.result
index 5dfe266..3661414 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -637,28 +637,18 @@ box.execute("SELECT group_concat(b) FROM t;")
--
box.execute("SELECT lower(b) FROM t;")
---
-- metadata:
- - name: lower(b)
- type: string
- rows:
- - ['true']
- - ['false']
- - [null]
+- null
+- 'Inconsistent types: expected TEXT got BOOLEAN'
...
box.execute("SELECT upper(b) FROM t;")
---
-- metadata:
- - name: upper(b)
- type: string
- rows:
- - ['TRUE']
- - ['FALSE']
- - [null]
+- null
+- 'Inconsistent types: expected TEXT got BOOLEAN'
...
box.execute("SELECT abs(b) FROM t;")
---
- null
-- 'Inconsistent types: expected number got BOOLEAN'
+- 'Inconsistent types: expected NUMBER got BOOLEAN'
...
box.execute("SELECT typeof(b) FROM t;")
---
@@ -1512,19 +1502,13 @@ box.execute("DELETE FROM t WHERE i < 18446744073709551613;")
...
box.execute("SELECT lower(i) FROM t;")
---
-- metadata:
- - name: lower(i)
- type: string
- rows:
- - ['18446744073709551613']
+- null
+- 'Inconsistent types: expected TEXT got UNSIGNED'
...
box.execute("SELECT upper(i) FROM t;")
---
-- metadata:
- - name: upper(i)
- type: string
- rows:
- - ['18446744073709551613']
+- null
+- 'Inconsistent types: expected TEXT got UNSIGNED'
...
box.execute("SELECT abs(i) FROM t;")
---
@@ -2005,7 +1989,7 @@ box.execute("SELECT upper(v) FROM t;")
box.execute("SELECT abs(v) FROM t;")
---
- null
-- 'Inconsistent types: expected number got VARBINARY'
+- 'Inconsistent types: expected NUMBER got VARBINARY'
...
box.execute("SELECT typeof(v) FROM t;")
---
--
2.7.4
^ permalink raw reply [flat|nested] only message in thread
only message in thread, other threads:[~2019-09-11 7:01 UTC | newest]
Thread overview: (only message) (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2019-09-11 7:01 [tarantool-patches] [PATCH v2 1/1] sql: check argument types for SQL built-in functions imeevma
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox