From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 470E522C2A for ; Wed, 11 Sep 2019 03:01:40 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HYvDSKr0YFJs for ; Wed, 11 Sep 2019 03:01:40 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 2945020DC5 for ; Wed, 11 Sep 2019 03:01:39 -0400 (EDT) From: imeevma@tarantool.org Subject: [tarantool-patches] [PATCH v2 1/1] sql: check argument types for SQL built-in functions Date: Wed, 11 Sep 2019 10:01:36 +0300 Message-Id: <6b68c5c716224bc8d968986b925b6c442a2bd5c0.1568184955.git.imeevma@gmail.com> Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: korablev@tarantool.org Cc: tarantool-patches@freelists.org 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 ]], { -- "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 ]], { -- 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 ]], { -- 1, -1, 3, -1, 5 @@ -412,13 +412,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func-4.4.2", [[ SELECT abs(t1) FROM tbl1 ]], { -- - 0.0, 0.0, 0.0, 0.0, 0.0 + 1, "Inconsistent types: expected NUMBER got TEXT" -- }) @@ -760,17 +760,25 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func-5.3", [[ SELECT upper(a), lower(a) FROM t2 ]], { -- - "1","1","","","345","345","","","67890","67890" + 1, "Inconsistent types: expected TEXT got UNSIGNED" -- }) - +test:do_catchsql_test( + "func-5.4", + [[ + SELECT lower(a) FROM t2 + ]], { + -- + 1, "Inconsistent types: expected TEXT got UNSIGNED" + -- + }) 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 ]], { -- "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)) ]], { -- - 32, "", 2000 + 32, 2000 -- }) +test:do_catchsql_test( + "func-9.6", + [[ + SELECT randomblob(-5); + ]], { + -- + 1, "Inconsistent types: expected UNSIGNED got INTEGER" + -- + }) + -- 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); + ]], { + -- + 9, 5, "" + -- + } +) + +test:do_catchsql_test( + "func-5-6.2", + [[ + SELECT LENGTH(true); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.3", + [[ + SELECT LENGTH(false); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.4", + [[ + SELECT LENGTH(12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got UNSIGNED" + -- + } +) + +test:do_catchsql_test( + "func-5-6.5", + [[ + SELECT LENGTH(-12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got INTEGER" + -- + } +) + +test:do_catchsql_test( + "func-5-6.6", + [[ + SELECT LENGTH(123.45); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got REAL" + -- + } +) + +test:do_execsql_test( + "func-5-6.7", + [[ + SELECT ABS(12345), ABS(-12345), ABS(123.45), ABS(-123.45); + ]], { + -- + 12345, 12345, 123.45, 123.45 + -- + } +) + +test:do_catchsql_test( + "func-5-6.8", + [[ + SELECT ABS('12345'); + ]], + { + -- + 1, "Inconsistent types: expected NUMBER got TEXT" + -- + } +) + +test:do_catchsql_test( + "func-5-6.9", + [[ + SELECT ABS(X'102030'); + ]], + { + -- + 1, "Inconsistent types: expected NUMBER got VARBINARY" + -- + } +) + +test:do_catchsql_test( + "func-5-6.10", + [[ + SELECT ABS(false); + ]], + { + -- + 1, "Inconsistent types: expected NUMBER got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.11", + [[ + SELECT ABS(true); + ]], + { + -- + 1, "Inconsistent types: expected NUMBER got BOOLEAN" + -- + } +) + +test:do_execsql_test( + "func-5-6.12", + [[ + SELECT UPPER('sOmE tExt'), LOWER('sOmE tExt'), UPPER(NULL), LOWER(NULL); + ]], { + -- + "SOME TEXT", "some text", "", "" + -- + } +) + +test:do_catchsql_test( + "func-5-6.13", + [[ + SELECT UPPER(true); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.14", + [[ + SELECT UPPER(false); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.15", + [[ + SELECT UPPER(12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got UNSIGNED" + -- + } +) + +test:do_catchsql_test( + "func-5-6.16", + [[ + SELECT UPPER(-12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got INTEGER" + -- + } +) + +test:do_catchsql_test( + "func-5-6.17", + [[ + SELECT UPPER(123.45); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got REAL" + -- + } +) + +test:do_catchsql_test( + "func-5-6.18", + [[ + SELECT UPPER(X'102030'); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got VARBINARY" + -- + } +) + +test:do_catchsql_test( + "func-5-6.19", + [[ + SELECT LOWER(true); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.20", + [[ + SELECT LOWER(false); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.21", + [[ + SELECT LOWER(12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got UNSIGNED" + -- + } +) + +test:do_catchsql_test( + "func-5-6.22", + [[ + SELECT LOWER(-12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got INTEGER" + -- + } +) + +test:do_catchsql_test( + "func-5-6.23", + [[ + SELECT LOWER(123.45); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got REAL" + -- + } +) + +test:do_catchsql_test( + "func-5-6.24", + [[ + SELECT LOWER(X'102030'); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got VARBINARY" + -- + } +) + +test:do_execsql_test( + "func-5-6.25", + [[ + SELECT LENGTH(a), TYPEOF(a) from (SELECT RANDOMBLOB(12) AS a); + ]], { + -- + 12, "varbinary" + -- + } +) + +test:do_catchsql_test( + "func-5-6.26", + [[ + SELECT RANDOMBLOB(true); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.27", + [[ + SELECT RANDOMBLOB(false); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.28", + [[ + SELECT RANDOMBLOB(-12345); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got INTEGER" + -- + } +) + +test:do_catchsql_test( + "func-5-6.29", + [[ + SELECT RANDOMBLOB(123.45); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got REAL" + -- + } +) + +test:do_catchsql_test( + "func-5-6.30", + [[ + SELECT RANDOMBLOB('102030'); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got TEXT" + -- + } +) + +test:do_catchsql_test( + "func-5-6.31", + [[ + SELECT RANDOMBLOB(X'102030'); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got VARBINARY" + -- + } +) + +test:do_execsql_test( + "func-5-6.32", + [[ + SELECT CHAR(70, NULL, 80, NULL, 90); + ]], { + -- + "F\0P\0Z" + -- + } +) + +test:do_catchsql_test( + "func-5-6.33", + [[ + SELECT CHAR(true); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.34", + [[ + SELECT CHAR(false); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.35", + [[ + SELECT CHAR(-12345); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got INTEGER" + -- + } +) + +test:do_catchsql_test( + "func-5-6.36", + [[ + SELECT CHAR(123.45); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got REAL" + -- + } +) + +test:do_catchsql_test( + "func-5-6.37", + [[ + SELECT CHAR('102030'); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got TEXT" + -- + } +) + +test:do_catchsql_test( + "func-5-6.38", + [[ + SELECT CHAR(X'102030'); + ]], + { + -- + 1, "Inconsistent types: expected UNSIGNED got VARBINARY" + -- + } +) + +test:do_execsql_test( + "func-5-6.39", + [[ + SELECT HEX('some text'), HEX(X'1020304050'), HEX(NULL); + ]], { + -- + "736F6D652074657874", "1020304050", "" + -- + } +) + +test:do_catchsql_test( + "func-5-6.40", + [[ + SELECT HEX(true); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.41", + [[ + SELECT HEX(false); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.42", + [[ + SELECT HEX(12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got UNSIGNED" + -- + } +) + +test:do_catchsql_test( + "func-5-6.43", + [[ + SELECT HEX(-12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got INTEGER" + -- + } +) + +test:do_catchsql_test( + "func-5-6.44", + [[ + SELECT HEX(123.45); + ]], + { + -- + 1, "Inconsistent types: expected TEXT or VARBINARY got REAL" + -- + } +) + +test:do_execsql_test( + "func-5-6.45", + [[ + SELECT SOUNDEX('some text'), SOUNDEX(NULL); + ]], { + -- + "S532","?000" + -- + } +) + +test:do_catchsql_test( + "func-5-6.46", + [[ + SELECT SOUNDEX(true); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.47", + [[ + SELECT SOUNDEX(false); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got BOOLEAN" + -- + } +) + +test:do_catchsql_test( + "func-5-6.48", + [[ + SELECT SOUNDEX(12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got UNSIGNED" + -- + } +) + +test:do_catchsql_test( + "func-5-6.49", + [[ + SELECT SOUNDEX(-12345); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got INTEGER" + -- + } +) + +test:do_catchsql_test( + "func-5-6.50", + [[ + SELECT SOUNDEX(123.45); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got REAL" + -- + } +) + +test:do_catchsql_test( + "func-5-6.51", + [[ + SELECT SOUNDEX(X'102030'); + ]], + { + -- + 1, "Inconsistent types: expected TEXT got VARBINARY" + -- + } +) + 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