From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 9E41A445325 for ; Mon, 13 Jul 2020 08:33:14 +0300 (MSK) From: imeevma@tarantool.org Date: Mon, 13 Jul 2020 08:33:12 +0300 Message-Id: In-Reply-To: References: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v4 5/5] sql: properly check arguments of functions List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: korablev@tarantool.org, tsafin@tarantool.org, tarantool-patches@dev.tarantool.org After this patch, the function arguments will be checked using ApplyType opcode before they are passed to the function. This means that the rules for implicitly casting values ​​that were specified as arguments are defined by this opcode. Closes #4159 --- src/box/sql/expr.c | 5 + src/box/sql/func.c | 360 +++- src/box/sql/select.c | 31 + src/box/sql/sqlInt.h | 32 + src/box/sql/vdbe.c | 12 +- test/sql-tap/cse.test.lua | 8 +- test/sql-tap/func.test.lua | 48 +- test/sql-tap/orderby1.test.lua | 2 +- test/sql-tap/position.test.lua | 6 +- test/sql/boolean.result | 32 +- test/sql/gh-4159-function-argumens.result | 2131 +++++++++++++++++-- test/sql/gh-4159-function-argumens.test.sql | 272 +++ test/sql/types.result | 76 +- test/sql/types.test.lua | 2 +- 14 files changed, 2584 insertions(+), 433 deletions(-) diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 7aee240a3..aa5477c6a 100644 --- a/src/box/sql/expr.c +++ b/src/box/sql/expr.c @@ -4104,6 +4104,11 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target) } else { r1 = 0; } + if (func->def->language == FUNC_LANGUAGE_SQL_BUILTIN) { + struct func_sql_builtin *f = + (struct func_sql_builtin *)func; + sql_emit_func_types(v, &f->args, r1, nFarg); + } if (sql_func_flag_is_set(func, SQL_FUNC_NEEDCOLL)) { sqlVdbeAddOp4(v, OP_CollSeq, 0, 0, 0, (char *)coll, P4_COLLSEQ); diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 9d4c26081..66edc3792 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -459,17 +459,10 @@ 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_ARRAY: - case MP_MAP: - case MP_INT: - case MP_UINT: - case MP_BOOL: - case MP_DOUBLE:{ + case MP_BIN: { sql_result_uint(context, sql_value_bytes(argv[0])); break; } @@ -482,6 +475,7 @@ lengthFunc(sql_context * context, int argc, sql_value ** argv) break; } default:{ + assert(sql_value_type(argv[0]) == MP_NIL); sql_result_null(context); break; } @@ -510,32 +504,16 @@ absFunc(sql_context * context, int argc, sql_value ** argv) 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: - case MP_ARRAY: - case MP_MAP: { - diag_set(ClientError, ER_INCONSISTENT_TYPES, "number", - mem_type_to_str(argv[0])); - context->is_aborted = true; - return; + case MP_DOUBLE: { + double rVal = sql_value_double(argv[0]); + if (rVal < 0) + rVal = -rVal; + sql_result_double(context, rVal); + break; } - 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; - } + default: + assert(sql_value_type(argv[0]) == MP_NIL); + sql_result_null(context); } } @@ -561,22 +539,8 @@ position_func(struct sql_context *context, int argc, struct Mem **argv) if (haystack_type == MP_NIL || needle_type == MP_NIL) return; - /* - * Position function can be called only with string - * or blob params. - */ - struct Mem *inconsistent_type_arg = NULL; - if (needle_type != MP_STR && needle_type != MP_BIN) - inconsistent_type_arg = needle; - if (haystack_type != MP_STR && haystack_type != MP_BIN) - inconsistent_type_arg = haystack; - if (inconsistent_type_arg != NULL) { - diag_set(ClientError, ER_INCONSISTENT_TYPES, - "text or varbinary", - mem_type_to_str(inconsistent_type_arg)); - context->is_aborted = true; - return; - } + assert(needle_type == MP_STR || needle_type == MP_BIN); + assert(haystack_type == MP_STR || haystack_type == MP_BIN); /* * Both params of Position function must be of the same * type. @@ -731,6 +695,9 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) return; } p0type = sql_value_type(argv[0]); + assert(p0type == MP_NIL || p0type == MP_STR || p0type == MP_BIN); + assert(sql_value_type(argv[1]) == MP_INT || + sql_value_type(argv[1]) == MP_UINT); p1 = sql_value_int(argv[1]); if (p0type == MP_BIN) { len = sql_value_bytes(argv[0]); @@ -747,6 +714,8 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) len = sql_utf8_char_count(z, sql_value_bytes(argv[0])); } if (argc == 3) { + assert(sql_value_type(argv[2]) == MP_INT || + sql_value_type(argv[2]) == MP_UINT); p2 = sql_value_int(argv[2]); if (p2 < 0) { p2 = -p2; @@ -811,6 +780,8 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) SQL_TRANSIENT); } } +enum field_type func_substr_types[] = {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER, + FIELD_TYPE_INTEGER}; /* * Implementation of the round() function @@ -824,19 +795,14 @@ roundFunc(sql_context * context, int argc, sql_value ** argv) if (argc == 2) { if (sql_value_is_null(argv[1])) return; + assert(sql_value_type(argv[1]) == MP_UINT); n = sql_value_int(argv[1]); if (n < 0) n = 0; } if (sql_value_is_null(argv[0])) return; - enum mp_type mp_type = sql_value_type(argv[0]); - if (mp_type_is_bloblike(mp_type)) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[0]), "numeric"); - context->is_aborted = true; - return; - } + assert(sql_value_type(argv[0]) == MP_DOUBLE); r = sql_value_double(argv[0]); /* If Y==0 and X will fit in a 64-bit int, * handle the rounding directly, @@ -852,6 +818,7 @@ roundFunc(sql_context * context, int argc, sql_value ** argv) } sql_result_double(context, r); } +enum field_type func_round_types[] = {FIELD_TYPE_DOUBLE, FIELD_TYPE_UNSIGNED}; /* * Allocate nByte bytes of space using sqlMalloc(). If the @@ -891,13 +858,9 @@ case_type##ICUFunc(sql_context *context, int argc, sql_value **argv) \ const char *z2; \ int n; \ UNUSED_PARAMETER(argc); \ - int arg_type = sql_value_type(argv[0]); \ - if (mp_type_is_bloblike(arg_type)) { \ - diag_set(ClientError, ER_INCONSISTENT_TYPES, "text", \ - "varbinary"); \ - context->is_aborted = true; \ - return; \ - } \ + assert(sql_value_type(argv[0]) == MP_NIL || \ + sql_value_type(argv[0]) == MP_STR || \ + sql_value_type(argv[0]) == MP_BIN); \ z2 = (char *)sql_value_text(argv[0]); \ n = sql_value_bytes(argv[0]); \ /* \ @@ -949,6 +912,7 @@ ICU_CASE_CONVERT(Upper); * is. We might as well use the "version()" function as a substitute. */ #define noopFunc sql_func_version /* Substitute function - never called */ +enum field_type func_likelihood_types[] = {FIELD_TYPE_ANY, FIELD_TYPE_DOUBLE}; /* * Implementation of random(). Return a random integer. @@ -973,12 +937,8 @@ randomBlob(sql_context * context, int argc, sql_value ** argv) unsigned char *p; assert(argc == 1); UNUSED_PARAMETER(argc); - if (mp_type_is_bloblike(sql_value_type(argv[0]))) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[0]), "numeric"); - context->is_aborted = true; - return; - } + assert(sql_value_type(argv[0]) == MP_NIL || + sql_value_type(argv[0]) == MP_UINT); n = sql_value_int(argv[0]); if (n < 1) return; @@ -1218,17 +1178,9 @@ likeFunc(sql_context *context, int argc, sql_value **argv) int rhs_type = sql_value_type(argv[0]); int lhs_type = sql_value_type(argv[1]); - if (lhs_type != MP_STR || rhs_type != MP_STR) { - if (lhs_type == MP_NIL || rhs_type == MP_NIL) - return; - char *inconsistent_type = rhs_type != MP_STR ? - mem_type_to_str(argv[0]) : - mem_type_to_str(argv[1]); - diag_set(ClientError, ER_INCONSISTENT_TYPES, "text", - inconsistent_type); - context->is_aborted = true; + if (lhs_type == MP_NIL || rhs_type == MP_NIL) return; - } + assert(lhs_type == MP_STR && rhs_type == MP_STR); const char *zB = (const char *) sql_value_text(argv[0]); const char *zA = (const char *) sql_value_text(argv[1]); const char *zB_end = zB + sql_value_bytes(argv[0]); @@ -1252,6 +1204,8 @@ likeFunc(sql_context *context, int argc, sql_value **argv) assert(zB == (const char *) sql_value_text(argv[0])); if (argc == 3) { + assert(sql_value_type(argv[2]) == MP_NIL || + sql_value_type(argv[2]) == MP_STR); /* * The escape character string must consist of a * single UTF-8 character. Otherwise, return an @@ -1432,6 +1386,10 @@ quoteFunc(sql_context * context, int argc, sql_value ** argv) static void unicodeFunc(sql_context * context, int argc, sql_value ** argv) { + assert(argc == 1); + assert(sql_value_type(argv[0]) == MP_NIL || + sql_value_type(argv[0]) == MP_STR || + sql_value_type(argv[0]) == MP_BIN); const unsigned char *z = sql_value_text(argv[0]); (void)argc; if (z && z[0]) @@ -1456,10 +1414,11 @@ charFunc(sql_context * context, int argc, sql_value ** argv) for (i = 0; i < argc; i++) { uint64_t x; unsigned c; - if (sql_value_type(argv[i]) == MP_INT) - x = 0xfffd; - else - x = sql_value_uint64(argv[i]); + enum mp_type type = sql_value_type(argv[i]); + if (type == MP_NIL) + return; + assert(sql_value_type(argv[i]) == MP_UINT); + x = sql_value_uint64(argv[i]); if (x > 0x10ffff) x = 0xfffd; c = (unsigned)(x & 0x1fffff); @@ -1493,6 +1452,9 @@ hexFunc(sql_context * context, int argc, sql_value ** argv) const unsigned char *pBlob; char *zHex, *z; assert(argc == 1); + assert(sql_value_type(argv[0]) == MP_NIL || + sql_value_type(argv[0]) == MP_STR || + sql_value_type(argv[0]) == MP_BIN); UNUSED_PARAMETER(argc); pBlob = sql_value_blob(argv[0]); n = sql_value_bytes(argv[0]); @@ -1518,6 +1480,8 @@ zeroblobFunc(sql_context * context, int argc, sql_value ** argv) i64 n; assert(argc == 1); UNUSED_PARAMETER(argc); + assert(sql_value_type(argv[0]) == MP_NIL || + sql_value_type(argv[0]) == MP_UINT); n = sql_value_int64(argv[0]); if (n < 0) n = 0; @@ -1550,6 +1514,15 @@ replaceFunc(sql_context * context, int argc, sql_value ** argv) assert(argc == 3); UNUSED_PARAMETER(argc); + assert(sql_value_type(argv[0]) == MP_NIL || + sql_value_type(argv[0]) == MP_STR || + sql_value_type(argv[0]) == MP_BIN); + assert(sql_value_type(argv[1]) == MP_NIL || + sql_value_type(argv[1]) == MP_STR || + sql_value_type(argv[1]) == MP_BIN); + assert(sql_value_type(argv[2]) == MP_NIL || + sql_value_type(argv[2]) == MP_STR || + sql_value_type(argv[2]) == MP_BIN); zStr = sql_value_text(argv[0]); if (zStr == 0) return; @@ -1858,13 +1831,9 @@ 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); - enum mp_type mp_type = sql_value_type(argv[0]); - if (mp_type_is_bloblike(mp_type)) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[0]), "text"); - context->is_aborted = true; - return; - } + assert(sql_value_type(argv[0]) == MP_NIL || + sql_value_type(argv[0]) == MP_STR || + sql_value_type(argv[0]) == MP_BIN); zIn = (u8 *) sql_value_text(argv[0]); if (zIn == 0) zIn = (u8 *) ""; @@ -1931,15 +1900,7 @@ sum_step(struct sql_context *context, int argc, sql_value **argv) int type = sql_value_type(argv[0]); if (type == MP_NIL || p == NULL) return; - if (type != MP_DOUBLE && type != MP_INT && type != MP_UINT) { - if (mem_apply_numeric_type(argv[0]) != 0) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[0]), "number"); - context->is_aborted = true; - return; - } - type = sql_value_type(argv[0]); - } + assert(mp_type_is_numeric(type)); p->cnt++; if (type == MP_INT || type == MP_UINT) { int64_t v = sql_value_int64(argv[0]); @@ -2230,6 +2191,9 @@ static struct { int param_count; uint32_t min_count; uint32_t max_count; + enum field_type *types; + enum field_type recurrent_type; + bool is_blob_like_str; enum field_type returns; enum func_aggregate aggregate; bool export_to_sql; @@ -2238,6 +2202,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_NUMBER, + .is_blob_like_str = false, .returns = FIELD_TYPE_NUMBER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2250,6 +2217,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_NUMBER, + .is_blob_like_str = false, .returns = FIELD_TYPE_NUMBER, .is_deterministic = false, .aggregate = FUNC_AGGREGATE_GROUP, @@ -2264,6 +2234,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2276,6 +2249,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2286,6 +2262,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = SQL_MAX_FUNCTION_ARG, + .types = NULL, + .recurrent_type = FIELD_TYPE_UNSIGNED, + .is_blob_like_str = false, .returns = FIELD_TYPE_STRING, .is_deterministic = true, .aggregate = FUNC_AGGREGATE_NONE, @@ -2298,6 +2277,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2310,6 +2292,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2322,6 +2307,9 @@ static struct { .param_count = -1, .min_count = 2, .max_count = SQL_MAX_FUNCTION_ARG, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_SCALAR, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2334,6 +2322,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_GROUP, .is_deterministic = false, @@ -2348,6 +2339,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2360,6 +2354,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2372,6 +2369,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2384,6 +2384,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2396,6 +2399,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2408,6 +2414,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2420,6 +2429,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2432,6 +2444,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2444,6 +2459,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2456,6 +2474,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2468,6 +2489,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2478,6 +2502,9 @@ static struct { .param_count = -1, .min_count = 2, .max_count = SQL_MAX_FUNCTION_ARG, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_SCALAR, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2490,6 +2517,9 @@ static struct { .param_count = -1, .min_count = 1, .max_count = 2, + .types = NULL, + .recurrent_type = FIELD_TYPE_SCALAR, + .is_blob_like_str = false, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_GROUP, .is_deterministic = false, @@ -2502,6 +2532,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2514,6 +2547,9 @@ static struct { .param_count = 2, .min_count = 2, .max_count = 2, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2528,6 +2564,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2538,6 +2577,9 @@ static struct { .param_count = -1, .min_count = 2, .max_count = SQL_MAX_FUNCTION_ARG, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_SCALAR, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2550,6 +2592,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2564,6 +2609,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2574,6 +2622,9 @@ static struct { .param_count = -1, .min_count = 2, .max_count = 3, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = false, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2586,6 +2637,9 @@ static struct { .param_count = 2, .min_count = 2, .max_count = 2, + .types = func_likelihood_types, + .recurrent_type = field_type_MAX, + .is_blob_like_str = false, .returns = FIELD_TYPE_BOOLEAN, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2598,6 +2652,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_BOOLEAN, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2612,6 +2669,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2622,6 +2682,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2634,6 +2697,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_SCALAR, .aggregate = FUNC_AGGREGATE_GROUP, .is_deterministic = false, @@ -2646,6 +2712,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_SCALAR, .aggregate = FUNC_AGGREGATE_GROUP, .is_deterministic = false, @@ -2660,6 +2729,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2670,6 +2742,9 @@ static struct { .param_count = 2, .min_count = 2, .max_count = 2, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_SCALAR, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2684,6 +2759,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2694,6 +2772,9 @@ static struct { .param_count = 2, .min_count = 2, .max_count = 2, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2708,6 +2789,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2718,6 +2802,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = SQL_MAX_FUNCTION_ARG, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2730,6 +2817,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2742,6 +2832,9 @@ static struct { .param_count = 0, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2754,6 +2847,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_UNSIGNED, + .is_blob_like_str = false, .returns = FIELD_TYPE_VARBINARY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2766,6 +2862,9 @@ static struct { .param_count = 3, .min_count = 3, .max_count = 3, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2778,6 +2877,9 @@ static struct { .param_count = -1, .min_count = 1, .max_count = 2, + .types = func_round_types, + .recurrent_type = field_type_MAX, + .is_blob_like_str = false, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2790,6 +2892,9 @@ static struct { .param_count = 0, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_INTEGER, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2804,6 +2909,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2814,6 +2922,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2828,6 +2939,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2840,6 +2954,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2850,6 +2967,9 @@ static struct { .param_count = -1, .min_count = 2, .max_count = 3, + .types = func_substr_types, + .recurrent_type = field_type_MAX, + .is_blob_like_str = true, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2862,6 +2982,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_NUMBER, + .is_blob_like_str = false, .returns = FIELD_TYPE_NUMBER, .aggregate = FUNC_AGGREGATE_GROUP, .is_deterministic = false, @@ -2876,6 +2999,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2886,6 +3012,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_NUMBER, + .is_blob_like_str = false, .returns = FIELD_TYPE_NUMBER, .aggregate = FUNC_AGGREGATE_GROUP, .is_deterministic = false, @@ -2898,6 +3027,9 @@ static struct { .param_count = -1, .min_count = 1, .max_count = 3, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2910,6 +3042,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2922,6 +3057,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2934,6 +3072,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_BOOLEAN, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2946,6 +3087,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_STRING, + .is_blob_like_str = true, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2958,6 +3102,9 @@ static struct { .param_count = 0, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_STRING, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2970,6 +3117,9 @@ static struct { .param_count = 1, .min_count = 1, .max_count = 1, + .types = NULL, + .recurrent_type = FIELD_TYPE_UNSIGNED, + .is_blob_like_str = false, .returns = FIELD_TYPE_VARBINARY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = true, @@ -2984,6 +3134,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -2996,6 +3149,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = NULL, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -3008,6 +3164,9 @@ static struct { .param_count = -1, .min_count = 0, .max_count = 0, + .types = false, + .recurrent_type = FIELD_TYPE_ANY, + .is_blob_like_str = false, .returns = FIELD_TYPE_ANY, .aggregate = FUNC_AGGREGATE_NONE, .is_deterministic = false, @@ -3061,6 +3220,9 @@ func_sql_builtin_new(struct func_def *def) func->finalize = sql_builtins[idx].finalize; func->args.min_count = sql_builtins[idx].min_count; func->args.max_count = sql_builtins[idx].max_count; + func->args.types = sql_builtins[idx].types; + func->args.recurrent_type = sql_builtins[idx].recurrent_type; + func->args.is_blob_like_str = sql_builtins[idx].is_blob_like_str; def->param_count = sql_builtins[idx].param_count; def->is_deterministic = sql_builtins[idx].is_deterministic; def->returns = sql_builtins[idx].returns; diff --git a/src/box/sql/select.c b/src/box/sql/select.c index 4b069addb..fe56ede1b 100644 --- a/src/box/sql/select.c +++ b/src/box/sql/select.c @@ -124,6 +124,34 @@ clearSelect(sql * db, Select * p, int bFree) } } +void +sql_emit_func_types(struct Vdbe *vdbe, struct sql_builtin_func_args *args, + int reg, uint32_t argc) +{ + assert(argc <= args->max_count); + enum field_type recurrent_type = args->recurrent_type; + assert(args->max_count > 0 || recurrent_type == FIELD_TYPE_ANY); + /* + * It makes no sense to check types of the MEMs if all + * arguments should be of type ANY. + */ + if (recurrent_type == FIELD_TYPE_ANY) + return; + size_t size = (argc + 1) * sizeof(enum field_type); + enum field_type *types = sqlDbMallocZero(sql_get(), size); + for (uint32_t i = 0; i < argc; ++i) { + if (args->types == NULL) + types[i] = args->recurrent_type; + else + types[i] = args->types[i]; + } + types[argc] = field_type_MAX; + sqlVdbeAddOp4(vdbe, OP_ApplyType, reg, argc, 0, (char *)types, + P4_DYNAMIC); + if (args->is_blob_like_str) + sqlVdbeChangeP5(vdbe, OPFLAG_BLOB_LIKE_STRING); +} + /* * Initialize a SelectDest structure. */ @@ -5414,6 +5442,9 @@ updateAccumulator(Parse * pParse, AggInfo * pAggInfo) sqlVdbeAddOp4(v, OP_CollSeq, regHit, 0, 0, (char *)coll, P4_COLLSEQ); } + struct func_sql_builtin *f = + (struct func_sql_builtin *)pF->func; + sql_emit_func_types(v, &f->args, regAgg, nArg); sqlVdbeAddOp3(v, OP_AggStep0, 0, regAgg, pF->iMem); sqlVdbeAppendP4(v, pF->func, P4_FUNC); sqlVdbeChangeP5(v, (u8) nArg); diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 6af9d7473..f82ae4eeb 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -265,6 +265,8 @@ #include #include +struct sql_builtin_func_args; + typedef long long int sql_int64; typedef unsigned long long int sql_uint64; typedef sql_int64 sql_int64; @@ -2307,6 +2309,8 @@ struct Parse { #define OPFLAG_SYSTEMSP 0x20 /* OP_Open**: set if space pointer * points to system space. */ +/** OP_ApplyType: Treat BLOB as STRING. */ +#define OPFLAG_BLOB_LIKE_STRING 0x01 /** * Prepare vdbe P5 flags for OP_{IdxInsert, IdxReplace, Update} @@ -3881,6 +3885,20 @@ sql_index_type_str(struct sql *db, const struct index_def *idx_def); void sql_emit_table_types(struct Vdbe *v, struct space_def *def, int reg); +/** + * Code an OP_ApplyType opcode that will force types for given + * range of register starting from @a reg. These values then will + * be used as arguments of a function. + * + * @param vdbe VDBE. + * @param args Information about arguments of the function. + * @param reg Register where types will be placed. + * @param argc Number of arguments. + */ +void +sql_emit_func_types(struct Vdbe *vdbe, struct sql_builtin_func_args *args, + int reg, uint32_t argc); + enum field_type sql_type_result(enum field_type lhs, enum field_type rhs); @@ -4406,6 +4424,20 @@ struct sql_builtin_func_args { uint32_t min_count; /** Max number of arguments. */ uint32_t max_count; + /** + * If function arguments may not be of the same type, all + * argument types are described here. + */ + enum field_type *types; + /** + * Contains the type of arguments if all arguments to the + * function are of the same type. + */ + enum field_type recurrent_type; + /** + * TRUE if the function should treat the BLOB as STRING. + */ + bool is_blob_like_str; }; struct func_sql_builtin { diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 863f38f5d..a4bf84bcc 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2882,7 +2882,7 @@ case OP_Fetch: { break; } -/* Opcode: ApplyType P1 P2 * P4 * +/* Opcode: ApplyType P1 P2 * P4 P5 * Synopsis: type(r[P1@P2]) * * Check that types of P2 registers starting from register P1 are @@ -2890,6 +2890,9 @@ case OP_Fetch: { * value and the given type are incompatible according to * field_mp_plain_type_is_compatible(), but both are numeric, * this opcode attempts to convert the value to the type. + * + * If P5 contains the OPFLAG_BLOB_LIKE_STRING flag, the BLOB + * values ​​are processed as if they had the field type STRING. */ case OP_ApplyType: { enum field_type *types = pOp->p4.types; @@ -2904,6 +2907,13 @@ case OP_ApplyType: { pIn1++; continue; } + if ((pOp->p5 & OPFLAG_BLOB_LIKE_STRING) != 0) { + if (type == FIELD_TYPE_STRING && + mem_mp_type(pIn1) == MP_BIN) { + pIn1++; + continue; + } + } if (!mp_type_is_numeric(mem_mp_type(pIn1)) || !sql_type_is_numeric(type) || mem_convert_to_numeric(pIn1, type, false) != 0) { diff --git a/test/sql-tap/cse.test.lua b/test/sql-tap/cse.test.lua index 341b6de01..3c2076a1d 100755 --- a/test/sql-tap/cse.test.lua +++ b/test/sql-tap/cse.test.lua @@ -195,23 +195,23 @@ test:do_execsql_test( -test:do_execsql_test( +test:do_catchsql_test( "cse-1.13", [[ SELECT upper(b), typeof(b), b FROM t1 ]], { -- - "11", "integer", 11, "21", "integer", 21 + 1, "Type mismatch: can not convert 11 to string" -- }) -test:do_execsql_test( +test:do_catchsql_test( "cse-1.14", [[ SELECT b, typeof(b), upper(b), typeof(b), b FROM t1 ]], { -- - 11, "integer", "11", "integer", 11, 21, "integer", "21", "integer", 21 + 1, "Type mismatch: can not convert 11 to string" -- }) diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua index 1d3ef9e2a..ae97994d7 100755 --- a/test/sql-tap/func.test.lua +++ b/test/sql-tap/func.test.lua @@ -92,13 +92,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func-1.4", [[ SELECT coalesce(length(a),-1) FROM t2 ]], { -- - 1, -1, 3, -1, 5 + 1, "Type mismatch: can not convert 1 to string" -- }) @@ -194,23 +194,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func-2.9", [[ SELECT substr(a,1,1) FROM t2 ]], { -- - "1", "", "3", "", "6" + 1, "Type mismatch: can not convert 1 to string" -- }) -test:do_execsql_test( +test:do_catchsql_test( "func-2.10", [[ SELECT substr(a,2,2) FROM t2 ]], { -- - "", "", "45", "", "78" + 1, "Type mismatch: can not convert 1 to string" -- }) @@ -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, "Type mismatch: can not convert this to number" -- }) @@ -502,13 +502,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func-4.13", [[ SELECT round(t1,2) FROM tbl1 ]], { -- - 0.0, 0.0, 0.0, 0.0, 0.0 + 1, "Type mismatch: can not convert this to double" -- }) @@ -760,13 +760,13 @@ 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, "Type mismatch: can not convert 1 to string" -- }) @@ -794,13 +794,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func-6.2", [[ SELECT coalesce(upper(a),'nil') FROM t2 ]], { -- - "1","nil","345","nil","67890" + 1, "Type mismatch: can not convert 1 to string" -- }) @@ -893,7 +893,7 @@ test:do_execsql_test( test:do_execsql_test( "func-8.5", [[ - SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x + SELECT sum(x) FROM (SELECT CAST('9223372036' || '854775807' AS INTEGER) AS x UNION ALL SELECT -9223372036854775807) ]], { -- @@ -904,7 +904,7 @@ test:do_execsql_test( test:do_execsql_test( "func-8.6", [[ - SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x + SELECT typeof(sum(x)) FROM (SELECT CAST('9223372036' || '854775807' AS INTEGER) AS x UNION ALL SELECT -9223372036854775807) ]], { -- @@ -915,7 +915,7 @@ test:do_execsql_test( test:do_execsql_test( "func-8.7", [[ - SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x + SELECT typeof(sum(x)) FROM (SELECT CAST('9223372036' || '854775808' AS INTEGER) AS x UNION ALL SELECT -9223372036854775807) ]], { -- @@ -926,7 +926,7 @@ test:do_execsql_test( test:do_execsql_test( "func-8.8", [[ - SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x + SELECT sum(x)>0.0 FROM (SELECT CAST('9223372036' || '854775808' AS INTEGER) AS x UNION ALL SELECT -9223372036850000000) ]], { -- @@ -982,14 +982,14 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "func-9.5", [[ SELECT length(randomblob(32)), length(randomblob(-5)), length(randomblob(2000)) ]], { -- - 32, "", 2000 + 1, "Type mismatch: can not convert -5 to unsigned" -- }) @@ -2918,7 +2918,7 @@ test:do_catchsql_test( SELECT ROUND(X'FF') ]], { -- - 1, "Type mismatch: can not convert varbinary to numeric" + 1, "Type mismatch: can not convert varbinary to double" -- }) @@ -2928,17 +2928,17 @@ test:do_catchsql_test( SELECT RANDOMBLOB(X'FF') ]], { -- - 1, "Type mismatch: can not convert varbinary to numeric" + 1, "Type mismatch: can not convert varbinary to unsigned" -- }) -test:do_catchsql_test( +test:do_execsql_test( "func-76.3", [[ SELECT SOUNDEX(X'FF') ]], { -- - 1, "Type mismatch: can not convert varbinary to text" + "?000" -- }) diff --git a/test/sql-tap/orderby1.test.lua b/test/sql-tap/orderby1.test.lua index 51e8d301f..95a8de487 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 STRING)) <> 0 ) FROM abc; ]], { diff --git a/test/sql-tap/position.test.lua b/test/sql-tap/position.test.lua index e0455abc9..0d4f6f371 100755 --- a/test/sql-tap/position.test.lua +++ b/test/sql-tap/position.test.lua @@ -228,7 +228,7 @@ test:do_test( return test:catchsql "SELECT position(34, 12345);" end, { -- - 1, "Inconsistent types: expected text or varbinary got unsigned" + 1, "Type mismatch: can not convert 34 to string" -- }) @@ -238,7 +238,7 @@ test:do_test( return test:catchsql "SELECT position(34, 123456.78);" end, { -- - 1, "Inconsistent types: expected text or varbinary got real" + 1, "Type mismatch: can not convert 34 to string" -- }) @@ -248,7 +248,7 @@ test:do_test( return test:catchsql "SELECT position(x'3334', 123456.78);" end, { -- - 1, "Inconsistent types: expected text or varbinary got real" + 1, "Type mismatch: can not convert 123456.78 to string" -- }) diff --git a/test/sql/boolean.result b/test/sql/boolean.result index 112e41a12..7e7f97284 100644 --- a/test/sql/boolean.result +++ b/test/sql/boolean.result @@ -276,29 +276,17 @@ SELECT is_boolean('true'); SELECT abs(a) FROM t0; | --- | - null - | - 'Inconsistent types: expected number got boolean' + | - 'Type mismatch: can not convert FALSE to number' | ... SELECT lower(a) FROM t0; | --- - | - metadata: - | - name: lower(a) - | type: string - | rows: - | - ['false'] - | - ['true'] - | - [null] - | - [null] + | - null + | - 'Type mismatch: can not convert FALSE to string' | ... SELECT upper(a) FROM t0; | --- - | - metadata: - | - name: upper(a) - | type: string - | rows: - | - ['FALSE'] - | - ['TRUE'] - | - [null] - | - [null] + | - null + | - 'Type mismatch: can not convert FALSE to string' | ... SELECT quote(a) FROM t0; | --- @@ -314,14 +302,8 @@ SELECT quote(a) FROM t0; -- gh-4462: LENGTH didn't take BOOLEAN arguments. SELECT length(a) FROM t0; | --- - | - metadata: - | - name: length(a) - | type: integer - | rows: - | - [5] - | - [4] - | - [null] - | - [null] + | - null + | - 'Type mismatch: can not convert FALSE to string' | ... SELECT typeof(a) FROM t0; | --- diff --git a/test/sql/gh-4159-function-argumens.result b/test/sql/gh-4159-function-argumens.result index 48bd550a4..4a276d65b 100644 --- a/test/sql/gh-4159-function-argumens.result +++ b/test/sql/gh-4159-function-argumens.result @@ -19,6 +19,54 @@ SELECT abs(1, 2); | - 'Wrong number of arguments is passed to ABS(): expected 1, got 2' | ... +SELECT abs(NULL); + | --- + | - metadata: + | - name: abs(NULL) + | type: number + | rows: + | - [null] + | ... +SELECT abs(1); + | --- + | - metadata: + | - name: abs(1) + | type: number + | rows: + | - [1] + | ... +SELECT abs(-1); + | --- + | - metadata: + | - name: abs(-1) + | type: number + | rows: + | - [1] + | ... +SELECT abs(-1.5); + | --- + | - metadata: + | - name: abs(-1.5) + | type: number + | rows: + | - [1.5] + | ... +SELECT abs(true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to number' + | ... +SELECT abs('abc'); + | --- + | - null + | - 'Type mismatch: can not convert abc to number' + | ... +SELECT abs(X'3334'); + | --- + | - null + | - 'Type mismatch: can not convert varbinary to number' + | ... + -- Function char(). SELECT char(); | --- @@ -45,6 +93,36 @@ SELECT char(1, 2); | - ["\x01\x02"] | ... +SELECT char(NULL); + | --- + | - metadata: + | - name: char(NULL) + | type: string + | rows: + | - [null] + | ... +SELECT char(1); + | --- + | - metadata: + | - name: char(1) + | type: string + | rows: + | - ["\x01"] + | ... +SELECT char(-1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to unsigned' + | ... +SELECT char(-1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to unsigned' + | ... +-- SELECT char(true); +-- SELECT char('abc'); +-- SELECT char(X'3334'); + -- Function character_length(). SELECT character_length(); | --- @@ -65,6 +143,51 @@ SELECT character_length('1', '2'); | - 'Wrong number of arguments is passed to CHARACTER_LENGTH(): expected 1, got 2' | ... +SELECT character_length(NULL); + | --- + | - metadata: + | - name: character_length(NULL) + | type: integer + | rows: + | - [null] + | ... +SELECT character_length(1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT character_length(-1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT character_length(-1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT character_length(true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT character_length('abc'); + | --- + | - metadata: + | - name: character_length('abc') + | type: integer + | rows: + | - [3] + | ... +SELECT character_length(X'3334'); + | --- + | - metadata: + | - name: character_length(X'3334') + | type: integer + | rows: + | - [2] + | ... + -- Function char_length(). SELECT char_length(); | --- @@ -85,6 +208,51 @@ SELECT char_length('1', '2'); | - 'Wrong number of arguments is passed to CHAR_LENGTH(): expected 1, got 2' | ... +SELECT char_length(NULL); + | --- + | - metadata: + | - name: char_length(NULL) + | type: integer + | rows: + | - [null] + | ... +SELECT char_length(1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT char_length(-1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT char_length(-1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT char_length(true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT char_length('abc'); + | --- + | - metadata: + | - name: char_length('abc') + | type: integer + | rows: + | - [3] + | ... +SELECT char_length(X'3334'); + | --- + | - metadata: + | - name: char_length(X'3334') + | type: integer + | rows: + | - [2] + | ... + -- Function coalesce(). SELECT coalesce(); | --- @@ -115,6 +283,63 @@ SELECT coalesce('1', '2', '3'); | - ['1'] | ... +SELECT coalesce(NULL, NULL); + | --- + | - metadata: + | - name: coalesce(NULL, NULL) + | type: scalar + | rows: + | - [null] + | ... +SELECT coalesce(1, 1); + | --- + | - metadata: + | - name: coalesce(1, 1) + | type: scalar + | rows: + | - [1] + | ... +SELECT coalesce(-1, -1); + | --- + | - metadata: + | - name: coalesce(-1, -1) + | type: scalar + | rows: + | - [-1] + | ... +SELECT coalesce(-1.5, -1.5); + | --- + | - metadata: + | - name: coalesce(-1.5, -1.5) + | type: scalar + | rows: + | - [-1.5] + | ... +SELECT coalesce(true, true); + | --- + | - metadata: + | - name: coalesce(true, true) + | type: scalar + | rows: + | - [true] + | ... +SELECT coalesce('abc', 'abc'); + | --- + | - metadata: + | - name: coalesce('abc', 'abc') + | type: scalar + | rows: + | - ['abc'] + | ... +SELECT coalesce(X'3334', X'3334'); + | --- + | - metadata: + | - name: coalesce(X'3334', X'3334') + | type: scalar + | rows: + | - ['34'] + | ... + -- Function greatest(). SELECT greatest(); | --- @@ -137,6 +362,63 @@ SELECT greatest('1', '2'); | - ['2'] | ... +SELECT greatest(NULL, NULL); + | --- + | - metadata: + | - name: greatest(NULL, NULL) + | type: scalar + | rows: + | - [null] + | ... +SELECT greatest(1, 1); + | --- + | - metadata: + | - name: greatest(1, 1) + | type: scalar + | rows: + | - [1] + | ... +SELECT greatest(-1, -1); + | --- + | - metadata: + | - name: greatest(-1, -1) + | type: scalar + | rows: + | - [-1] + | ... +SELECT greatest(-1.5, -1.5); + | --- + | - metadata: + | - name: greatest(-1.5, -1.5) + | type: scalar + | rows: + | - [-1.5] + | ... +SELECT greatest(true, true); + | --- + | - metadata: + | - name: greatest(true, true) + | type: scalar + | rows: + | - [true] + | ... +SELECT greatest('abc', 'abc'); + | --- + | - metadata: + | - name: greatest('abc', 'abc') + | type: scalar + | rows: + | - ['abc'] + | ... +SELECT greatest(X'3334', X'3334'); + | --- + | - metadata: + | - name: greatest(X'3334', X'3334') + | type: scalar + | rows: + | - ['34'] + | ... + -- Function hex(). SELECT hex(); | --- @@ -157,6 +439,51 @@ SELECT hex(X'33', X'33'); | - 'Wrong number of arguments is passed to HEX(): expected 1, got 2' | ... +SELECT hex(NULL); + | --- + | - metadata: + | - name: hex(NULL) + | type: string + | rows: + | - [''] + | ... +SELECT hex(1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT hex(-1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT hex(-1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT hex(true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT hex('abc'); + | --- + | - metadata: + | - name: hex('abc') + | type: string + | rows: + | - ['616263'] + | ... +SELECT hex(X'3334'); + | --- + | - metadata: + | - name: hex(X'3334') + | type: string + | rows: + | - ['3334'] + | ... + -- Function ifnull SELECT ifnull(); | --- @@ -182,46 +509,205 @@ SELECT ifnull(1, 2, 3); | - 'Wrong number of arguments is passed to IFNULL(): expected 2, got 3' | ... --- Function least(). -SELECT least(); +SELECT ifnull(NULL, NULL); | --- - | - null - | - 'Wrong number of arguments is passed to LEAST(): expected from 2 to 127, got 0' + | - metadata: + | - name: ifnull(NULL, NULL) + | type: integer + | rows: + | - [null] | ... -SELECT least('1'); +SELECT ifnull(1, 1); | --- - | - null - | - 'Wrong number of arguments is passed to LEAST(): expected from 2 to 127, got 1' + | - metadata: + | - name: ifnull(1, 1) + | type: integer + | rows: + | - [1] | ... -SELECT least('1', '2'); +SELECT ifnull(-1, -1); | --- | - metadata: - | - name: least('1', '2') - | type: scalar + | - name: ifnull(-1, -1) + | type: integer | rows: - | - ['1'] + | - [-1] | ... - --- Function length(). -SELECT length(); +SELECT ifnull(-1.5, -1.5); | --- - | - null - | - 'Wrong number of arguments is passed to LENGTH(): expected 1, got 0' + | - metadata: + | - name: ifnull(-1.5, -1.5) + | type: integer + | rows: + | - [-1.5] | ... -SELECT length('1'); +SELECT ifnull(true, true); | --- | - metadata: - | - name: length('1') + | - name: ifnull(true, true) | type: integer | rows: - | - [1] + | - [true] | ... -SELECT length('1', '2'); +SELECT ifnull('abc', 'abc'); | --- - | - null + | - metadata: + | - name: ifnull('abc', 'abc') + | type: integer + | rows: + | - ['abc'] + | ... +SELECT ifnull(X'3334', X'3334'); + | --- + | - metadata: + | - name: ifnull(X'3334', X'3334') + | type: integer + | rows: + | - ['34'] + | ... + +-- Function least(). +SELECT least(); + | --- + | - null + | - 'Wrong number of arguments is passed to LEAST(): expected from 2 to 127, got 0' + | ... +SELECT least('1'); + | --- + | - null + | - 'Wrong number of arguments is passed to LEAST(): expected from 2 to 127, got 1' + | ... +SELECT least('1', '2'); + | --- + | - metadata: + | - name: least('1', '2') + | type: scalar + | rows: + | - ['1'] + | ... + +SELECT least(NULL, NULL); + | --- + | - metadata: + | - name: least(NULL, NULL) + | type: scalar + | rows: + | - [null] + | ... +SELECT least(1, 1); + | --- + | - metadata: + | - name: least(1, 1) + | type: scalar + | rows: + | - [1] + | ... +SELECT least(-1, -1); + | --- + | - metadata: + | - name: least(-1, -1) + | type: scalar + | rows: + | - [-1] + | ... +SELECT least(-1.5, -1.5); + | --- + | - metadata: + | - name: least(-1.5, -1.5) + | type: scalar + | rows: + | - [-1.5] + | ... +SELECT least(true, true); + | --- + | - metadata: + | - name: least(true, true) + | type: scalar + | rows: + | - [true] + | ... +SELECT least('abc', 'abc'); + | --- + | - metadata: + | - name: least('abc', 'abc') + | type: scalar + | rows: + | - ['abc'] + | ... +SELECT least(X'3334', X'3334'); + | --- + | - metadata: + | - name: least(X'3334', X'3334') + | type: scalar + | rows: + | - ['34'] + | ... + +-- Function length(). +SELECT length(); + | --- + | - null + | - 'Wrong number of arguments is passed to LENGTH(): expected 1, got 0' + | ... +SELECT length('1'); + | --- + | - metadata: + | - name: length('1') + | type: integer + | rows: + | - [1] + | ... +SELECT length('1', '2'); + | --- + | - null | - 'Wrong number of arguments is passed to LENGTH(): expected 1, got 2' | ... +SELECT length(NULL); + | --- + | - metadata: + | - name: length(NULL) + | type: integer + | rows: + | - [null] + | ... +SELECT length(1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT length(-1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT length(-1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT length(true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT length('abc'); + | --- + | - metadata: + | - name: length('abc') + | type: integer + | rows: + | - [3] + | ... +SELECT length(X'3334'); + | --- + | - metadata: + | - name: length(X'3334') + | type: integer + | rows: + | - [2] + | ... + -- Function likelihood SELECT likelihood(); | --- @@ -247,6 +733,49 @@ SELECT likelihood(1, 0.5, 3); | - 'Wrong number of arguments is passed to LIKELIHOOD(): expected 2, got 3' | ... +SELECT likelihood(NULL, NULL); + | --- + | - null + | - Illegal parameters, second argument to likelihood() must be a constant between 0.0 + | and 1.0 + | ... +SELECT likelihood(1, 1); + | --- + | - null + | - Illegal parameters, second argument to likelihood() must be a constant between 0.0 + | and 1.0 + | ... +SELECT likelihood(-1, -1); + | --- + | - null + | - Illegal parameters, second argument to likelihood() must be a constant between 0.0 + | and 1.0 + | ... +SELECT likelihood(-1.5, -1.5); + | --- + | - null + | - Illegal parameters, second argument to likelihood() must be a constant between 0.0 + | and 1.0 + | ... +SELECT likelihood(true, true); + | --- + | - null + | - Illegal parameters, second argument to likelihood() must be a constant between 0.0 + | and 1.0 + | ... +SELECT likelihood('abc', 'abc'); + | --- + | - null + | - Illegal parameters, second argument to likelihood() must be a constant between 0.0 + | and 1.0 + | ... +SELECT likelihood(X'3334', X'3334'); + | --- + | - null + | - Illegal parameters, second argument to likelihood() must be a constant between 0.0 + | and 1.0 + | ... + -- Function likely SELECT likely(); | --- @@ -267,6 +796,63 @@ SELECT likely(1, 2); | - 'Wrong number of arguments is passed to LIKELY(): expected 1, got 2' | ... +SELECT likely(NULL); + | --- + | - metadata: + | - name: likely(NULL) + | type: scalar + | rows: + | - [null] + | ... +SELECT likely(1); + | --- + | - metadata: + | - name: likely(1) + | type: integer + | rows: + | - [1] + | ... +SELECT likely(-1); + | --- + | - metadata: + | - name: likely(-1) + | type: integer + | rows: + | - [-1] + | ... +SELECT likely(-1.5); + | --- + | - metadata: + | - name: likely(-1.5) + | type: double + | rows: + | - [-1.5] + | ... +SELECT likely(true); + | --- + | - metadata: + | - name: likely(true) + | type: boolean + | rows: + | - [true] + | ... +SELECT likely('abc'); + | --- + | - metadata: + | - name: likely('abc') + | type: string + | rows: + | - ['abc'] + | ... +SELECT likely(X'3334'); + | --- + | - metadata: + | - name: likely(X'3334') + | type: varbinary + | rows: + | - ['34'] + | ... + -- Function lower SELECT lower(); | --- @@ -287,6 +873,51 @@ SELECT lower('a', 2); | - 'Wrong number of arguments is passed to LOWER(): expected 1, got 2' | ... +SELECT lower(NULL); + | --- + | - metadata: + | - name: lower(NULL) + | type: string + | rows: + | - [null] + | ... +SELECT lower(1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT lower(-1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT lower(-1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT lower(true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT lower('abc'); + | --- + | - metadata: + | - name: lower('abc') + | type: string + | rows: + | - ['abc'] + | ... +SELECT lower(X'3334'); + | --- + | - metadata: + | - name: lower(X'3334') + | type: string + | rows: + | - ['34'] + | ... + -- Function nullif SELECT nullif(); | --- @@ -312,6 +943,63 @@ SELECT nullif(1, 2, 3); | - 'Wrong number of arguments is passed to NULLIF(): expected 2, got 3' | ... +SELECT nullif(NULL, NULL); + | --- + | - metadata: + | - name: nullif(NULL, NULL) + | type: scalar + | rows: + | - [null] + | ... +SELECT nullif(1, 1); + | --- + | - metadata: + | - name: nullif(1, 1) + | type: scalar + | rows: + | - [null] + | ... +SELECT nullif(-1, -1); + | --- + | - metadata: + | - name: nullif(-1, -1) + | type: scalar + | rows: + | - [null] + | ... +SELECT nullif(-1.5, -1.5); + | --- + | - metadata: + | - name: nullif(-1.5, -1.5) + | type: scalar + | rows: + | - [null] + | ... +SELECT nullif(true, true); + | --- + | - metadata: + | - name: nullif(true, true) + | type: scalar + | rows: + | - [null] + | ... +SELECT nullif('abc', 'abc'); + | --- + | - metadata: + | - name: nullif('abc', 'abc') + | type: scalar + | rows: + | - [null] + | ... +SELECT nullif(X'3334', X'3334'); + | --- + | - metadata: + | - name: nullif(X'3334', X'3334') + | type: scalar + | rows: + | - [null] + | ... + -- Function position SELECT position(); | --- @@ -337,366 +1025,1075 @@ SELECT position('12345', '2', 3); | - 'Wrong number of arguments is passed to POSITION(): expected 2, got 3' | ... --- Function printf -SELECT printf(); +SELECT position(NULL, NULL); + | --- + | - metadata: + | - name: position(NULL, NULL) + | type: integer + | rows: + | - [null] + | ... +SELECT position(1, 1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT position(-1, -1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT position(-1.5, -1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT position(true, true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT position('abc', 'abc'); + | --- + | - metadata: + | - name: position('abc', 'abc') + | type: integer + | rows: + | - [1] + | ... +SELECT position(X'3334', X'3334'); + | --- + | - metadata: + | - name: position(X'3334', X'3334') + | type: integer + | rows: + | - [1] + | ... + +-- Function printf +SELECT printf(); + | --- + | - metadata: + | - name: printf() + | type: string + | rows: + | - [null] + | ... +SELECT printf('1'); + | --- + | - metadata: + | - name: printf('1') + | type: string + | rows: + | - ['1'] + | ... +SELECT printf('1', 2); + | --- + | - metadata: + | - name: printf('1', 2) + | type: string + | rows: + | - ['1'] + | ... +SELECT printf('1', 2, 3); + | --- + | - metadata: + | - name: printf('1', 2, 3) + | type: string + | rows: + | - ['1'] + | ... + +SELECT printf(NULL, NULL); + | --- + | - metadata: + | - name: printf(NULL, NULL) + | type: string + | rows: + | - [null] + | ... +SELECT printf(1, 1); + | --- + | - metadata: + | - name: printf(1, 1) + | type: string + | rows: + | - ['1'] + | ... +SELECT printf(-1, -1); + | --- + | - metadata: + | - name: printf(-1, -1) + | type: string + | rows: + | - ['-1'] + | ... +SELECT printf(-1.5, -1.5); + | --- + | - metadata: + | - name: printf(-1.5, -1.5) + | type: string + | rows: + | - ['-1.5'] + | ... +SELECT printf(true, true); + | --- + | - metadata: + | - name: printf(true, true) + | type: string + | rows: + | - ['TRUE'] + | ... +SELECT printf('abc', 'abc'); + | --- + | - metadata: + | - name: printf('abc', 'abc') + | type: string + | rows: + | - ['abc'] + | ... +SELECT printf(X'3334', X'3334'); + | --- + | - metadata: + | - name: printf(X'3334', X'3334') + | type: string + | rows: + | - ['34'] + | ... + +-- Function quote +SELECT quote(); + | --- + | - null + | - 'Wrong number of arguments is passed to QUOTE(): expected 1, got 0' + | ... +SELECT quote('1'); + | --- + | - metadata: + | - name: quote('1') + | type: string + | rows: + | - ['''1'''] + | ... +SELECT quote('1', 2); + | --- + | - null + | - 'Wrong number of arguments is passed to QUOTE(): expected 1, got 2' + | ... + +SELECT quote(NULL); + | --- + | - metadata: + | - name: quote(NULL) + | type: string + | rows: + | - ['NULL'] + | ... +SELECT quote(1); + | --- + | - metadata: + | - name: quote(1) + | type: string + | rows: + | - [1] + | ... +SELECT quote(-1); + | --- + | - metadata: + | - name: quote(-1) + | type: string + | rows: + | - [-1] + | ... +SELECT quote(-1.5); + | --- + | - metadata: + | - name: quote(-1.5) + | type: string + | rows: + | - ['-1.5'] + | ... +SELECT quote(true); + | --- + | - metadata: + | - name: quote(true) + | type: string + | rows: + | - ['TRUE'] + | ... +SELECT quote('abc'); + | --- + | - metadata: + | - name: quote('abc') + | type: string + | rows: + | - ['''abc'''] + | ... +SELECT quote(X'3334'); + | --- + | - metadata: + | - name: quote(X'3334') + | type: string + | rows: + | - ['X''3334'''] + | ... + +-- Function random +SELECT typeof(random()); + | --- + | - metadata: + | - name: typeof(random()) + | type: string + | rows: + | - ['integer'] + | ... +SELECT typeof(random(1)); + | --- + | - null + | - 'Wrong number of arguments is passed to RANDOM(): expected 0, got 1' + | ... + +-- Function randomblob +SELECT typeof(randomblob()); + | --- + | - null + | - 'Wrong number of arguments is passed to RANDOMBLOB(): expected 1, got 0' + | ... +SELECT typeof(randomblob(1)); + | --- + | - metadata: + | - name: typeof(randomblob(1)) + | type: string + | rows: + | - ['varbinary'] + | ... +SELECT typeof(randomblob(1, 2)); + | --- + | - null + | - 'Wrong number of arguments is passed to RANDOMBLOB(): expected 1, got 2' + | ... + +SELECT randomblob(NULL); + | --- + | - metadata: + | - name: randomblob(NULL) + | type: varbinary + | rows: + | - [null] + | ... +SELECT typeof(randomblob(1)); + | --- + | - metadata: + | - name: typeof(randomblob(1)) + | type: string + | rows: + | - ['varbinary'] + | ... +SELECT typeof(randomblob(-1)); + | --- + | - null + | - 'Type mismatch: can not convert -1 to unsigned' + | ... +SELECT typeof(randomblob(-1.5)); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to unsigned' + | ... +SELECT typeof(randomblob(true)); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to unsigned' + | ... +SELECT typeof(randomblob('abc')); + | --- + | - null + | - 'Type mismatch: can not convert abc to unsigned' + | ... +SELECT typeof(randomblob(X'3334')); + | --- + | - null + | - 'Type mismatch: can not convert varbinary to unsigned' + | ... + +-- Function replace +SELECT replace(); + | --- + | - null + | - 'Wrong number of arguments is passed to REPLACE(): expected 3, got 0' + | ... +SELECT replace('12345'); + | --- + | - null + | - 'Wrong number of arguments is passed to REPLACE(): expected 3, got 1' + | ... +SELECT replace('12345', '2'); + | --- + | - null + | - 'Wrong number of arguments is passed to REPLACE(): expected 3, got 2' + | ... +SELECT replace('12345', '2', '3'); + | --- + | - metadata: + | - name: replace('12345', '2', '3') + | type: string + | rows: + | - ['13345'] + | ... +SELECT replace('12345', '2', '3', 4); + | --- + | - null + | - 'Wrong number of arguments is passed to REPLACE(): expected 3, got 4' + | ... + +SELECT replace(NULL, NULL, NULL); + | --- + | - metadata: + | - name: replace(NULL, NULL, NULL) + | type: string + | rows: + | - [null] + | ... +SELECT replace(1, 1, 1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT replace(-1, -1, -1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT replace(-1.5, -1.5, -1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT replace(true, true, true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT replace('abc', 'abc', 'abc'); + | --- + | - metadata: + | - name: replace('abc', 'abc', 'abc') + | type: string + | rows: + | - ['abc'] + | ... +SELECT replace(X'3334', X'3334', X'3334'); + | --- + | - metadata: + | - name: replace(X'3334', X'3334', X'3334') + | type: string + | rows: + | - ['34'] + | ... + +-- Function round +SELECT round(); + | --- + | - null + | - 'Wrong number of arguments is passed to ROUND(): expected from 1 to 2, got 0' + | ... +SELECT round(1.1245); + | --- + | - metadata: + | - name: round(1.1245) + | type: integer + | rows: + | - [1] + | ... +SELECT round(1.1245, 2); + | --- + | - metadata: + | - name: round(1.1245, 2) + | type: integer + | rows: + | - [1.12] + | ... +SELECT round(1.1245, 2, 3); + | --- + | - null + | - 'Wrong number of arguments is passed to ROUND(): expected from 1 to 2, got 3' + | ... + +SELECT round(NULL, NULL); + | --- + | - metadata: + | - name: round(NULL, NULL) + | type: integer + | rows: + | - [null] + | ... +SELECT round(1, 1); + | --- + | - metadata: + | - name: round(1, 1) + | type: integer + | rows: + | - [1] + | ... +SELECT round(-1, -1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to unsigned' + | ... +SELECT round(-1.5, -1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to unsigned' + | ... +SELECT round(true, true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to double' + | ... +SELECT round('abc', 'abc'); + | --- + | - null + | - 'Type mismatch: can not convert abc to double' + | ... +SELECT round(X'3334', X'3334'); + | --- + | - null + | - 'Type mismatch: can not convert varbinary to double' + | ... + +-- Function row_count +SELECT row_count(); + | --- + | - metadata: + | - name: row_count() + | type: integer + | rows: + | - [0] + | ... +SELECT row_count(1); + | --- + | - null + | - 'Wrong number of arguments is passed to ROW_COUNT(): expected 0, got 1' + | ... + +-- Function soundex +SELECT soundex(); + | --- + | - null + | - 'Wrong number of arguments is passed to SOUNDEX(): expected 1, got 0' + | ... +SELECT soundex(1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT soundex(1, 2); + | --- + | - null + | - 'Wrong number of arguments is passed to SOUNDEX(): expected 1, got 2' + | ... + +SELECT soundex(NULL); + | --- + | - metadata: + | - name: soundex(NULL) + | type: string + | rows: + | - ['?000'] + | ... +SELECT soundex(1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT soundex(-1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT soundex(-1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT soundex(true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT soundex('abc'); + | --- + | - metadata: + | - name: soundex('abc') + | type: string + | rows: + | - ['A120'] + | ... +SELECT soundex(X'3334'); + | --- + | - metadata: + | - name: soundex(X'3334') + | type: string + | rows: + | - ['?000'] + | ... + +-- Function substr +SELECT substr(); + | --- + | - null + | - 'Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 0' + | ... +SELECT substr('12345'); + | --- + | - null + | - 'Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 1' + | ... +SELECT substr('12345', 2); + | --- + | - metadata: + | - name: substr('12345', 2) + | type: string + | rows: + | - ['2345'] + | ... +SELECT substr('12345', 2, 3); + | --- + | - metadata: + | - name: substr('12345', 2, 3) + | type: string + | rows: + | - ['234'] + | ... +SELECT substr('12345', 2, 3, 4); + | --- + | - null + | - 'Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 4' + | ... + +SELECT substr(NULL, NULL); + | --- + | - metadata: + | - name: substr(NULL, NULL) + | type: string + | rows: + | - [null] + | ... +SELECT substr(1, 1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT substr(-1, -1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT substr(-1.5, -1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT substr(true, true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT substr('abc', 'abc'); + | --- + | - null + | - 'Type mismatch: can not convert abc to integer' + | ... +SELECT substr(X'3334', X'3334'); + | --- + | - null + | - 'Type mismatch: can not convert varbinary to integer' + | ... + +-- Function typeof +SELECT typeof(); + | --- + | - null + | - 'Wrong number of arguments is passed to TYPEOF(): expected 1, got 0' + | ... +SELECT typeof(1); + | --- + | - metadata: + | - name: typeof(1) + | type: string + | rows: + | - ['integer'] + | ... +SELECT typeof(1, 2); + | --- + | - null + | - 'Wrong number of arguments is passed to TYPEOF(): expected 1, got 2' + | ... + +SELECT typeof(NULL); + | --- + | - metadata: + | - name: typeof(NULL) + | type: string + | rows: + | - ['boolean'] + | ... +SELECT typeof(1); + | --- + | - metadata: + | - name: typeof(1) + | type: string + | rows: + | - ['integer'] + | ... +SELECT typeof(-1); + | --- + | - metadata: + | - name: typeof(-1) + | type: string + | rows: + | - ['integer'] + | ... +SELECT typeof(-1.5); + | --- + | - metadata: + | - name: typeof(-1.5) + | type: string + | rows: + | - ['double'] + | ... +SELECT typeof(true); + | --- + | - metadata: + | - name: typeof(true) + | type: string + | rows: + | - ['boolean'] + | ... +SELECT typeof('abc'); + | --- + | - metadata: + | - name: typeof('abc') + | type: string + | rows: + | - ['string'] + | ... +SELECT typeof(X'3334'); + | --- + | - metadata: + | - name: typeof(X'3334') + | type: string + | rows: + | - ['varbinary'] + | ... + +-- Function unicode +SELECT unicode(); + | --- + | - null + | - 'Wrong number of arguments is passed to UNICODE(): expected 1, got 0' + | ... +SELECT unicode('1'); + | --- + | - metadata: + | - name: unicode('1') + | type: string + | rows: + | - [49] + | ... +SELECT unicode('1', '2'); + | --- + | - null + | - 'Wrong number of arguments is passed to UNICODE(): expected 1, got 2' + | ... + +SELECT unicode(NULL); + | --- + | - metadata: + | - name: unicode(NULL) + | type: string + | rows: + | - [null] + | ... +SELECT unicode(1); + | --- + | - null + | - 'Type mismatch: can not convert 1 to string' + | ... +SELECT unicode(-1); + | --- + | - null + | - 'Type mismatch: can not convert -1 to string' + | ... +SELECT unicode(-1.5); + | --- + | - null + | - 'Type mismatch: can not convert -1.5 to string' + | ... +SELECT unicode(true); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to string' + | ... +SELECT unicode('abc'); + | --- + | - metadata: + | - name: unicode('abc') + | type: string + | rows: + | - [97] + | ... +SELECT unicode(X'3334'); + | --- + | - metadata: + | - name: unicode(X'3334') + | type: string + | rows: + | - [51] + | ... + +-- Function unlikely +SELECT unlikely(); + | --- + | - null + | - 'Wrong number of arguments is passed to UNLIKELY(): expected 1, got 0' + | ... +SELECT unlikely(1); + | --- + | - metadata: + | - name: unlikely(1) + | type: integer + | rows: + | - [1] + | ... +SELECT unlikely(1, 2); + | --- + | - null + | - 'Wrong number of arguments is passed to UNLIKELY(): expected 1, got 2' + | ... + +SELECT unlikely(NULL); + | --- + | - metadata: + | - name: unlikely(NULL) + | type: scalar + | rows: + | - [null] + | ... +SELECT unlikely(1); + | --- + | - metadata: + | - name: unlikely(1) + | type: integer + | rows: + | - [1] + | ... +SELECT unlikely(-1); | --- | - metadata: - | - name: printf() - | type: string + | - name: unlikely(-1) + | type: integer | rows: - | - [null] + | - [-1] | ... -SELECT printf('1'); +SELECT unlikely(-1.5); | --- | - metadata: - | - name: printf('1') - | type: string + | - name: unlikely(-1.5) + | type: double | rows: - | - ['1'] + | - [-1.5] | ... -SELECT printf('1', 2); +SELECT unlikely(true); | --- | - metadata: - | - name: printf('1', 2) - | type: string + | - name: unlikely(true) + | type: boolean | rows: - | - ['1'] + | - [true] | ... -SELECT printf('1', 2, 3); +SELECT unlikely('abc'); | --- | - metadata: - | - name: printf('1', 2, 3) + | - name: unlikely('abc') | type: string | rows: - | - ['1'] + | - ['abc'] | ... - --- Function quote -SELECT quote(); - | --- - | - null - | - 'Wrong number of arguments is passed to QUOTE(): expected 1, got 0' - | ... -SELECT quote('1'); +SELECT unlikely(X'3334'); | --- | - metadata: - | - name: quote('1') - | type: string + | - name: unlikely(X'3334') + | type: varbinary | rows: - | - ['''1'''] + | - ['34'] | ... -SELECT quote('1', 2); + +-- Function upper +SELECT upper(); | --- | - null - | - 'Wrong number of arguments is passed to QUOTE(): expected 1, got 2' + | - 'Wrong number of arguments is passed to UPPER(): expected 1, got 0' | ... - --- Function random -SELECT typeof(random()); +SELECT upper('a'); | --- | - metadata: - | - name: typeof(random()) + | - name: upper('a') | type: string | rows: - | - ['integer'] + | - ['A'] | ... -SELECT typeof(random(1)); +SELECT upper('a', 2); | --- | - null - | - 'Wrong number of arguments is passed to RANDOM(): expected 0, got 1' + | - 'Wrong number of arguments is passed to UPPER(): expected 1, got 2' | ... --- Function randomblob -SELECT typeof(randomblob()); - | --- - | - null - | - 'Wrong number of arguments is passed to RANDOMBLOB(): expected 1, got 0' - | ... -SELECT typeof(randomblob(1)); +SELECT upper(NULL); | --- | - metadata: - | - name: typeof(randomblob(1)) + | - name: upper(NULL) | type: string | rows: - | - ['varbinary'] + | - [null] | ... -SELECT typeof(randomblob(1, 2)); +SELECT upper(1); | --- | - null - | - 'Wrong number of arguments is passed to RANDOMBLOB(): expected 1, got 2' + | - 'Type mismatch: can not convert 1 to string' | ... - --- Function replace -SELECT replace(); +SELECT upper(-1); | --- | - null - | - 'Wrong number of arguments is passed to REPLACE(): expected 3, got 0' + | - 'Type mismatch: can not convert -1 to string' | ... -SELECT replace('12345'); +SELECT upper(-1.5); | --- | - null - | - 'Wrong number of arguments is passed to REPLACE(): expected 3, got 1' + | - 'Type mismatch: can not convert -1.5 to string' | ... -SELECT replace('12345', '2'); +SELECT upper(true); | --- | - null - | - 'Wrong number of arguments is passed to REPLACE(): expected 3, got 2' + | - 'Type mismatch: can not convert TRUE to string' | ... -SELECT replace('12345', '2', '3'); +SELECT upper('abc'); | --- | - metadata: - | - name: replace('12345', '2', '3') + | - name: upper('abc') | type: string | rows: - | - ['13345'] - | ... -SELECT replace('12345', '2', '3', 4); - | --- - | - null - | - 'Wrong number of arguments is passed to REPLACE(): expected 3, got 4' - | ... - --- Function round -SELECT round(); - | --- - | - null - | - 'Wrong number of arguments is passed to ROUND(): expected from 1 to 2, got 0' - | ... -SELECT round(1.1245); - | --- - | - metadata: - | - name: round(1.1245) - | type: integer - | rows: - | - [1] + | - ['ABC'] | ... -SELECT round(1.1245, 2); +SELECT upper(X'3334'); | --- | - metadata: - | - name: round(1.1245, 2) - | type: integer + | - name: upper(X'3334') + | type: string | rows: - | - [1.12] - | ... -SELECT round(1.1245, 2, 3); - | --- - | - null - | - 'Wrong number of arguments is passed to ROUND(): expected from 1 to 2, got 3' + | - ['34'] | ... --- Function row_count -SELECT row_count(); +-- Function version +SELECT typeof(version()); | --- | - metadata: - | - name: row_count() - | type: integer + | - name: typeof(version()) + | type: string | rows: - | - [0] + | - ['string'] | ... -SELECT row_count(1); +SELECT typeof(version(1)); | --- | - null - | - 'Wrong number of arguments is passed to ROW_COUNT(): expected 0, got 1' + | - 'Wrong number of arguments is passed to VERSION(): expected 0, got 1' | ... --- Function soundex -SELECT soundex(); +-- Function zeroblob +SELECT zeroblob(); | --- | - null - | - 'Wrong number of arguments is passed to SOUNDEX(): expected 1, got 0' + | - 'Wrong number of arguments is passed to ZEROBLOB(): expected 1, got 0' | ... -SELECT soundex(1); +SELECT zeroblob(1); | --- | - metadata: - | - name: soundex(1) - | type: string + | - name: zeroblob(1) + | type: varbinary | rows: - | - ['?000'] + | - ["\0"] | ... -SELECT soundex(1, 2); +SELECT zeroblob(1, 2); | --- | - null - | - 'Wrong number of arguments is passed to SOUNDEX(): expected 1, got 2' + | - 'Wrong number of arguments is passed to ZEROBLOB(): expected 1, got 2' | ... --- Function substr -SELECT substr(); - | --- - | - null - | - 'Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 0' - | ... -SELECT substr('12345'); - | --- - | - null - | - 'Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 1' - | ... -SELECT substr('12345', 2); +SELECT zeroblob(NULL); | --- | - metadata: - | - name: substr('12345', 2) - | type: string + | - name: zeroblob(NULL) + | type: varbinary | rows: - | - ['2345'] + | - [''] | ... -SELECT substr('12345', 2, 3); +SELECT zeroblob(1); | --- | - metadata: - | - name: substr('12345', 2, 3) - | type: string + | - name: zeroblob(1) + | type: varbinary | rows: - | - ['234'] + | - ["\0"] | ... -SELECT substr('12345', 2, 3, 4); +SELECT zeroblob(-1); | --- | - null - | - 'Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 4' + | - 'Type mismatch: can not convert -1 to unsigned' | ... - --- Function typeof -SELECT typeof(); +SELECT zeroblob(-1.5); | --- | - null - | - 'Wrong number of arguments is passed to TYPEOF(): expected 1, got 0' + | - 'Type mismatch: can not convert -1.5 to unsigned' | ... -SELECT typeof(1); +SELECT zeroblob(true); | --- - | - metadata: - | - name: typeof(1) - | type: string - | rows: - | - ['integer'] + | - null + | - 'Type mismatch: can not convert TRUE to unsigned' | ... -SELECT typeof(1, 2); +SELECT zeroblob('abc'); | --- | - null - | - 'Wrong number of arguments is passed to TYPEOF(): expected 1, got 2' + | - 'Type mismatch: can not convert abc to unsigned' + | ... +SELECT zeroblob(X'3334'); + | --- + | - null + | - 'Type mismatch: can not convert varbinary to unsigned' | ... --- Function unicode -SELECT unicode(); +-- Function avg +SELECT avg() FROM (values(1), (2), (3)); | --- | - null - | - 'Wrong number of arguments is passed to UNICODE(): expected 1, got 0' + | - 'Wrong number of arguments is passed to AVG(): expected 1, got 0' | ... -SELECT unicode('1'); +SELECT avg("_auto_field_") FROM (values(1), (2), (3)); | --- | - metadata: - | - name: unicode('1') - | type: string + | - name: avg("_auto_field_") + | type: number | rows: - | - [49] + | - [2] | ... -SELECT unicode('1', '2'); +SELECT avg("_auto_field_", 2) FROM (values(1), (2), (3)); | --- | - null - | - 'Wrong number of arguments is passed to UNICODE(): expected 1, got 2' + | - 'Wrong number of arguments is passed to AVG(): expected 1, got 2' | ... --- Function unlikely -SELECT unlikely(); +SELECT avg("_auto_field_") FROM (values(NULL), (NULL), (NULL)); | --- - | - null - | - 'Wrong number of arguments is passed to UNLIKELY(): expected 1, got 0' + | - metadata: + | - name: avg("_auto_field_") + | type: number + | rows: + | - [null] | ... -SELECT unlikely(1); +SELECT avg("_auto_field_") FROM (values(1), (1), (1)); | --- | - metadata: - | - name: unlikely(1) - | type: integer + | - name: avg("_auto_field_") + | type: number | rows: | - [1] | ... -SELECT unlikely(1, 2); - | --- - | - null - | - 'Wrong number of arguments is passed to UNLIKELY(): expected 1, got 2' - | ... - --- Function upper -SELECT upper(); +SELECT avg("_auto_field_") FROM (values(-1), (-1), (-1)); | --- - | - null - | - 'Wrong number of arguments is passed to UPPER(): expected 1, got 0' + | - metadata: + | - name: avg("_auto_field_") + | type: number + | rows: + | - [-1] | ... -SELECT upper('a'); +SELECT avg("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); | --- | - metadata: - | - name: upper('a') - | type: string + | - name: avg("_auto_field_") + | type: number | rows: - | - ['A'] + | - [-1.5] | ... -SELECT upper('a', 2); +SELECT avg("_auto_field_") FROM (values(true), (true), (true)); | --- | - null - | - 'Wrong number of arguments is passed to UPPER(): expected 1, got 2' + | - 'Type mismatch: can not convert TRUE to number' | ... - --- Function version -SELECT typeof(version()); +SELECT avg("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); | --- - | - metadata: - | - name: typeof(version()) - | type: string - | rows: - | - ['string'] + | - null + | - 'Type mismatch: can not convert abc to number' | ... -SELECT typeof(version(1)); +SELECT avg("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); | --- | - null - | - 'Wrong number of arguments is passed to VERSION(): expected 0, got 1' + | - 'Type mismatch: can not convert varbinary to number' | ... --- Function zeroblob -SELECT zeroblob(); +-- Function count +SELECT count() FROM (values(1), (2), (3)); | --- - | - null - | - 'Wrong number of arguments is passed to ZEROBLOB(): expected 1, got 0' + | - metadata: + | - name: count() + | type: integer + | rows: + | - [3] | ... -SELECT zeroblob(1); +SELECT count("_auto_field_") FROM (values(1), (2), (3)); | --- | - metadata: - | - name: zeroblob(1) - | type: varbinary + | - name: count("_auto_field_") + | type: integer | rows: - | - ["\0"] + | - [3] | ... -SELECT zeroblob(1, 2); +SELECT count("_auto_field_", 2) FROM (values(1), (2), (3)); | --- | - null - | - 'Wrong number of arguments is passed to ZEROBLOB(): expected 1, got 2' + | - 'Wrong number of arguments is passed to COUNT(): expected from 0 to 1, got 2' | ... --- Function avg -SELECT avg() FROM (values(1), (2), (3)); +SELECT count("_auto_field_") FROM (values(NULL), (NULL), (NULL)); | --- - | - null - | - 'Wrong number of arguments is passed to AVG(): expected 1, got 0' + | - metadata: + | - name: count("_auto_field_") + | type: integer + | rows: + | - [0] | ... -SELECT avg("_auto_field_") FROM (values(1), (2), (3)); +SELECT count("_auto_field_") FROM (values(1), (1), (1)); | --- | - metadata: - | - name: avg("_auto_field_") - | type: number + | - name: count("_auto_field_") + | type: integer | rows: - | - [2] + | - [3] | ... -SELECT avg("_auto_field_", 2) FROM (values(1), (2), (3)); +SELECT count("_auto_field_") FROM (values(-1), (-1), (-1)); | --- - | - null - | - 'Wrong number of arguments is passed to AVG(): expected 1, got 2' + | - metadata: + | - name: count("_auto_field_") + | type: integer + | rows: + | - [3] | ... - --- Function count -SELECT count() FROM (values(1), (2), (3)); +SELECT count("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); | --- | - metadata: - | - name: count() + | - name: count("_auto_field_") | type: integer | rows: | - [3] | ... -SELECT count("_auto_field_") FROM (values(1), (2), (3)); +SELECT count("_auto_field_") FROM (values(true), (true), (true)); | --- | - metadata: | - name: count("_auto_field_") @@ -704,10 +2101,21 @@ SELECT count("_auto_field_") FROM (values(1), (2), (3)); | rows: | - [3] | ... -SELECT count("_auto_field_", 2) FROM (values(1), (2), (3)); +SELECT count("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); | --- - | - null - | - 'Wrong number of arguments is passed to COUNT(): expected from 0 to 1, got 2' + | - metadata: + | - name: count("_auto_field_") + | type: integer + | rows: + | - [3] + | ... +SELECT count("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + | --- + | - metadata: + | - name: count("_auto_field_") + | type: integer + | rows: + | - [3] | ... -- Function group_concat @@ -740,6 +2148,63 @@ SELECT group_concat("_auto_field_", '2', '3') FROM (values('1'), ('2'), ('3')); | 3' | ... +SELECT group_concat("_auto_field_") FROM (values(NULL), (NULL), (NULL)); + | --- + | - metadata: + | - name: group_concat("_auto_field_") + | type: string + | rows: + | - [null] + | ... +SELECT group_concat("_auto_field_") FROM (values(1), (1), (1)); + | --- + | - metadata: + | - name: group_concat("_auto_field_") + | type: string + | rows: + | - ['1,1,1'] + | ... +SELECT group_concat("_auto_field_") FROM (values(-1), (-1), (-1)); + | --- + | - metadata: + | - name: group_concat("_auto_field_") + | type: string + | rows: + | - ['-1,-1,-1'] + | ... +SELECT group_concat("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); + | --- + | - metadata: + | - name: group_concat("_auto_field_") + | type: string + | rows: + | - ['-1.5,-1.5,-1.5'] + | ... +SELECT group_concat("_auto_field_") FROM (values(true), (true), (true)); + | --- + | - metadata: + | - name: group_concat("_auto_field_") + | type: string + | rows: + | - ['TRUE,TRUE,TRUE'] + | ... +SELECT group_concat("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); + | --- + | - metadata: + | - name: group_concat("_auto_field_") + | type: string + | rows: + | - ['abc,abc,abc'] + | ... +SELECT group_concat("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + | --- + | - metadata: + | - name: group_concat("_auto_field_") + | type: string + | rows: + | - ['34,34,34'] + | ... + -- Function max SELECT max() FROM (values(1), (2), (3)); | --- @@ -760,6 +2225,63 @@ SELECT max("_auto_field_", 2) FROM (values(1), (2), (3)); | - 'Wrong number of arguments is passed to MAX(): expected 1, got 2' | ... +SELECT max("_auto_field_") FROM (values(NULL), (NULL), (NULL)); + | --- + | - metadata: + | - name: max("_auto_field_") + | type: scalar + | rows: + | - [null] + | ... +SELECT max("_auto_field_") FROM (values(1), (1), (1)); + | --- + | - metadata: + | - name: max("_auto_field_") + | type: scalar + | rows: + | - [1] + | ... +SELECT max("_auto_field_") FROM (values(-1), (-1), (-1)); + | --- + | - metadata: + | - name: max("_auto_field_") + | type: scalar + | rows: + | - [-1] + | ... +SELECT max("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); + | --- + | - metadata: + | - name: max("_auto_field_") + | type: scalar + | rows: + | - [-1.5] + | ... +SELECT max("_auto_field_") FROM (values(true), (true), (true)); + | --- + | - metadata: + | - name: max("_auto_field_") + | type: scalar + | rows: + | - [true] + | ... +SELECT max("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); + | --- + | - metadata: + | - name: max("_auto_field_") + | type: scalar + | rows: + | - ['abc'] + | ... +SELECT max("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + | --- + | - metadata: + | - name: max("_auto_field_") + | type: scalar + | rows: + | - ['34'] + | ... + -- Function min SELECT min() FROM (values(1), (2), (3)); | --- @@ -780,6 +2302,63 @@ SELECT min("_auto_field_", 2) FROM (values(1), (2), (3)); | - 'Wrong number of arguments is passed to MIN(): expected 1, got 2' | ... +SELECT min("_auto_field_") FROM (values(NULL), (NULL), (NULL)); + | --- + | - metadata: + | - name: min("_auto_field_") + | type: scalar + | rows: + | - [null] + | ... +SELECT min("_auto_field_") FROM (values(1), (1), (1)); + | --- + | - metadata: + | - name: min("_auto_field_") + | type: scalar + | rows: + | - [1] + | ... +SELECT min("_auto_field_") FROM (values(-1), (-1), (-1)); + | --- + | - metadata: + | - name: min("_auto_field_") + | type: scalar + | rows: + | - [-1] + | ... +SELECT min("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); + | --- + | - metadata: + | - name: min("_auto_field_") + | type: scalar + | rows: + | - [-1.5] + | ... +SELECT min("_auto_field_") FROM (values(true), (true), (true)); + | --- + | - metadata: + | - name: min("_auto_field_") + | type: scalar + | rows: + | - [true] + | ... +SELECT min("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); + | --- + | - metadata: + | - name: min("_auto_field_") + | type: scalar + | rows: + | - ['abc'] + | ... +SELECT min("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + | --- + | - metadata: + | - name: min("_auto_field_") + | type: scalar + | rows: + | - ['34'] + | ... + -- Function sum SELECT sum() FROM (values(1), (2), (3)); | --- @@ -800,6 +2379,54 @@ SELECT sum("_auto_field_", 2) FROM (values(1), (2), (3)); | - 'Wrong number of arguments is passed to SUM(): expected 1, got 2' | ... +SELECT sum("_auto_field_") FROM (values(NULL), (NULL), (NULL)); + | --- + | - metadata: + | - name: sum("_auto_field_") + | type: number + | rows: + | - [null] + | ... +SELECT sum("_auto_field_") FROM (values(1), (1), (1)); + | --- + | - metadata: + | - name: sum("_auto_field_") + | type: number + | rows: + | - [3] + | ... +SELECT sum("_auto_field_") FROM (values(-1), (-1), (-1)); + | --- + | - metadata: + | - name: sum("_auto_field_") + | type: number + | rows: + | - [-3] + | ... +SELECT sum("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); + | --- + | - metadata: + | - name: sum("_auto_field_") + | type: number + | rows: + | - [-4.5] + | ... +SELECT sum("_auto_field_") FROM (values(true), (true), (true)); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to number' + | ... +SELECT sum("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); + | --- + | - null + | - 'Type mismatch: can not convert abc to number' + | ... +SELECT sum("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + | --- + | - null + | - 'Type mismatch: can not convert varbinary to number' + | ... + -- Function total SELECT total() FROM (values(1), (2), (3)); | --- @@ -819,3 +2446,51 @@ SELECT total("_auto_field_", 2) FROM (values(1), (2), (3)); | - null | - 'Wrong number of arguments is passed to TOTAL(): expected 1, got 2' | ... + +SELECT total("_auto_field_") FROM (values(NULL), (NULL), (NULL)); + | --- + | - metadata: + | - name: total("_auto_field_") + | type: number + | rows: + | - [0] + | ... +SELECT total("_auto_field_") FROM (values(1), (1), (1)); + | --- + | - metadata: + | - name: total("_auto_field_") + | type: number + | rows: + | - [3] + | ... +SELECT total("_auto_field_") FROM (values(-1), (-1), (-1)); + | --- + | - metadata: + | - name: total("_auto_field_") + | type: number + | rows: + | - [-3] + | ... +SELECT total("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); + | --- + | - metadata: + | - name: total("_auto_field_") + | type: number + | rows: + | - [-4.5] + | ... +SELECT total("_auto_field_") FROM (values(true), (true), (true)); + | --- + | - null + | - 'Type mismatch: can not convert TRUE to number' + | ... +SELECT total("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); + | --- + | - null + | - 'Type mismatch: can not convert abc to number' + | ... +SELECT total("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + | --- + | - null + | - 'Type mismatch: can not convert varbinary to number' + | ... diff --git a/test/sql/gh-4159-function-argumens.test.sql b/test/sql/gh-4159-function-argumens.test.sql index 7578768cb..e45f62d91 100644 --- a/test/sql/gh-4159-function-argumens.test.sql +++ b/test/sql/gh-4159-function-argumens.test.sql @@ -3,92 +3,228 @@ SELECT abs(); SELECT abs(1); SELECT abs(1, 2); +SELECT abs(NULL); +SELECT abs(1); +SELECT abs(-1); +SELECT abs(-1.5); +SELECT abs(true); +SELECT abs('abc'); +SELECT abs(X'3334'); + -- Function char(). SELECT char(); SELECT char(1); SELECT char(1, 2); +SELECT char(NULL); +SELECT char(1); +SELECT char(-1); +SELECT char(-1.5); +-- SELECT char(true); +-- SELECT char('abc'); +-- SELECT char(X'3334'); + -- Function character_length(). SELECT character_length(); SELECT character_length('1'); SELECT character_length('1', '2'); +SELECT character_length(NULL); +SELECT character_length(1); +SELECT character_length(-1); +SELECT character_length(-1.5); +SELECT character_length(true); +SELECT character_length('abc'); +SELECT character_length(X'3334'); + -- Function char_length(). SELECT char_length(); SELECT char_length('1'); SELECT char_length('1', '2'); +SELECT char_length(NULL); +SELECT char_length(1); +SELECT char_length(-1); +SELECT char_length(-1.5); +SELECT char_length(true); +SELECT char_length('abc'); +SELECT char_length(X'3334'); + -- Function coalesce(). SELECT coalesce(); SELECT coalesce('1'); SELECT coalesce('1', '2'); SELECT coalesce('1', '2', '3'); +SELECT coalesce(NULL, NULL); +SELECT coalesce(1, 1); +SELECT coalesce(-1, -1); +SELECT coalesce(-1.5, -1.5); +SELECT coalesce(true, true); +SELECT coalesce('abc', 'abc'); +SELECT coalesce(X'3334', X'3334'); + -- Function greatest(). SELECT greatest(); SELECT greatest('1'); SELECT greatest('1', '2'); +SELECT greatest(NULL, NULL); +SELECT greatest(1, 1); +SELECT greatest(-1, -1); +SELECT greatest(-1.5, -1.5); +SELECT greatest(true, true); +SELECT greatest('abc', 'abc'); +SELECT greatest(X'3334', X'3334'); + -- Function hex(). SELECT hex(); SELECT hex(X'33'); SELECT hex(X'33', X'33'); +SELECT hex(NULL); +SELECT hex(1); +SELECT hex(-1); +SELECT hex(-1.5); +SELECT hex(true); +SELECT hex('abc'); +SELECT hex(X'3334'); + -- Function ifnull SELECT ifnull(); SELECT ifnull(1); SELECT ifnull(1, 2); SELECT ifnull(1, 2, 3); +SELECT ifnull(NULL, NULL); +SELECT ifnull(1, 1); +SELECT ifnull(-1, -1); +SELECT ifnull(-1.5, -1.5); +SELECT ifnull(true, true); +SELECT ifnull('abc', 'abc'); +SELECT ifnull(X'3334', X'3334'); + -- Function least(). SELECT least(); SELECT least('1'); SELECT least('1', '2'); +SELECT least(NULL, NULL); +SELECT least(1, 1); +SELECT least(-1, -1); +SELECT least(-1.5, -1.5); +SELECT least(true, true); +SELECT least('abc', 'abc'); +SELECT least(X'3334', X'3334'); + -- Function length(). SELECT length(); SELECT length('1'); SELECT length('1', '2'); +SELECT length(NULL); +SELECT length(1); +SELECT length(-1); +SELECT length(-1.5); +SELECT length(true); +SELECT length('abc'); +SELECT length(X'3334'); + -- Function likelihood SELECT likelihood(); SELECT likelihood(1); SELECT likelihood(1, 0.5); SELECT likelihood(1, 0.5, 3); +SELECT likelihood(NULL, NULL); +SELECT likelihood(1, 1); +SELECT likelihood(-1, -1); +SELECT likelihood(-1.5, -1.5); +SELECT likelihood(true, true); +SELECT likelihood('abc', 'abc'); +SELECT likelihood(X'3334', X'3334'); + -- Function likely SELECT likely(); SELECT likely(1); SELECT likely(1, 2); +SELECT likely(NULL); +SELECT likely(1); +SELECT likely(-1); +SELECT likely(-1.5); +SELECT likely(true); +SELECT likely('abc'); +SELECT likely(X'3334'); + -- Function lower SELECT lower(); SELECT lower('a'); SELECT lower('a', 2); +SELECT lower(NULL); +SELECT lower(1); +SELECT lower(-1); +SELECT lower(-1.5); +SELECT lower(true); +SELECT lower('abc'); +SELECT lower(X'3334'); + -- Function nullif SELECT nullif(); SELECT nullif(1); SELECT nullif(1, 2); SELECT nullif(1, 2, 3); +SELECT nullif(NULL, NULL); +SELECT nullif(1, 1); +SELECT nullif(-1, -1); +SELECT nullif(-1.5, -1.5); +SELECT nullif(true, true); +SELECT nullif('abc', 'abc'); +SELECT nullif(X'3334', X'3334'); + -- Function position SELECT position(); SELECT position('12345'); SELECT position('12345', '2'); SELECT position('12345', '2', 3); +SELECT position(NULL, NULL); +SELECT position(1, 1); +SELECT position(-1, -1); +SELECT position(-1.5, -1.5); +SELECT position(true, true); +SELECT position('abc', 'abc'); +SELECT position(X'3334', X'3334'); + -- Function printf SELECT printf(); SELECT printf('1'); SELECT printf('1', 2); SELECT printf('1', 2, 3); +SELECT printf(NULL, NULL); +SELECT printf(1, 1); +SELECT printf(-1, -1); +SELECT printf(-1.5, -1.5); +SELECT printf(true, true); +SELECT printf('abc', 'abc'); +SELECT printf(X'3334', X'3334'); + -- Function quote SELECT quote(); SELECT quote('1'); SELECT quote('1', 2); +SELECT quote(NULL); +SELECT quote(1); +SELECT quote(-1); +SELECT quote(-1.5); +SELECT quote(true); +SELECT quote('abc'); +SELECT quote(X'3334'); + -- Function random SELECT typeof(random()); SELECT typeof(random(1)); @@ -98,6 +234,14 @@ SELECT typeof(randomblob()); SELECT typeof(randomblob(1)); SELECT typeof(randomblob(1, 2)); +SELECT randomblob(NULL); +SELECT typeof(randomblob(1)); +SELECT typeof(randomblob(-1)); +SELECT typeof(randomblob(-1.5)); +SELECT typeof(randomblob(true)); +SELECT typeof(randomblob('abc')); +SELECT typeof(randomblob(X'3334')); + -- Function replace SELECT replace(); SELECT replace('12345'); @@ -105,12 +249,28 @@ SELECT replace('12345', '2'); SELECT replace('12345', '2', '3'); SELECT replace('12345', '2', '3', 4); +SELECT replace(NULL, NULL, NULL); +SELECT replace(1, 1, 1); +SELECT replace(-1, -1, -1); +SELECT replace(-1.5, -1.5, -1.5); +SELECT replace(true, true, true); +SELECT replace('abc', 'abc', 'abc'); +SELECT replace(X'3334', X'3334', X'3334'); + -- Function round SELECT round(); SELECT round(1.1245); SELECT round(1.1245, 2); SELECT round(1.1245, 2, 3); +SELECT round(NULL, NULL); +SELECT round(1, 1); +SELECT round(-1, -1); +SELECT round(-1.5, -1.5); +SELECT round(true, true); +SELECT round('abc', 'abc'); +SELECT round(X'3334', X'3334'); + -- Function row_count SELECT row_count(); SELECT row_count(1); @@ -120,6 +280,14 @@ SELECT soundex(); SELECT soundex(1); SELECT soundex(1, 2); +SELECT soundex(NULL); +SELECT soundex(1); +SELECT soundex(-1); +SELECT soundex(-1.5); +SELECT soundex(true); +SELECT soundex('abc'); +SELECT soundex(X'3334'); + -- Function substr SELECT substr(); SELECT substr('12345'); @@ -127,26 +295,66 @@ SELECT substr('12345', 2); SELECT substr('12345', 2, 3); SELECT substr('12345', 2, 3, 4); +SELECT substr(NULL, NULL); +SELECT substr(1, 1); +SELECT substr(-1, -1); +SELECT substr(-1.5, -1.5); +SELECT substr(true, true); +SELECT substr('abc', 'abc'); +SELECT substr(X'3334', X'3334'); + -- Function typeof SELECT typeof(); SELECT typeof(1); SELECT typeof(1, 2); +SELECT typeof(NULL); +SELECT typeof(1); +SELECT typeof(-1); +SELECT typeof(-1.5); +SELECT typeof(true); +SELECT typeof('abc'); +SELECT typeof(X'3334'); + -- Function unicode SELECT unicode(); SELECT unicode('1'); SELECT unicode('1', '2'); +SELECT unicode(NULL); +SELECT unicode(1); +SELECT unicode(-1); +SELECT unicode(-1.5); +SELECT unicode(true); +SELECT unicode('abc'); +SELECT unicode(X'3334'); + -- Function unlikely SELECT unlikely(); SELECT unlikely(1); SELECT unlikely(1, 2); +SELECT unlikely(NULL); +SELECT unlikely(1); +SELECT unlikely(-1); +SELECT unlikely(-1.5); +SELECT unlikely(true); +SELECT unlikely('abc'); +SELECT unlikely(X'3334'); + -- Function upper SELECT upper(); SELECT upper('a'); SELECT upper('a', 2); +SELECT upper(NULL); +SELECT upper(1); +SELECT upper(-1); +SELECT upper(-1.5); +SELECT upper(true); +SELECT upper('abc'); +SELECT upper(X'3334'); + -- Function version SELECT typeof(version()); SELECT typeof(version(1)); @@ -156,38 +364,102 @@ SELECT zeroblob(); SELECT zeroblob(1); SELECT zeroblob(1, 2); +SELECT zeroblob(NULL); +SELECT zeroblob(1); +SELECT zeroblob(-1); +SELECT zeroblob(-1.5); +SELECT zeroblob(true); +SELECT zeroblob('abc'); +SELECT zeroblob(X'3334'); + -- Function avg SELECT avg() FROM (values(1), (2), (3)); SELECT avg("_auto_field_") FROM (values(1), (2), (3)); SELECT avg("_auto_field_", 2) FROM (values(1), (2), (3)); +SELECT avg("_auto_field_") FROM (values(NULL), (NULL), (NULL)); +SELECT avg("_auto_field_") FROM (values(1), (1), (1)); +SELECT avg("_auto_field_") FROM (values(-1), (-1), (-1)); +SELECT avg("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); +SELECT avg("_auto_field_") FROM (values(true), (true), (true)); +SELECT avg("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); +SELECT avg("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + -- Function count SELECT count() FROM (values(1), (2), (3)); SELECT count("_auto_field_") FROM (values(1), (2), (3)); SELECT count("_auto_field_", 2) FROM (values(1), (2), (3)); +SELECT count("_auto_field_") FROM (values(NULL), (NULL), (NULL)); +SELECT count("_auto_field_") FROM (values(1), (1), (1)); +SELECT count("_auto_field_") FROM (values(-1), (-1), (-1)); +SELECT count("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); +SELECT count("_auto_field_") FROM (values(true), (true), (true)); +SELECT count("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); +SELECT count("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + -- Function group_concat SELECT group_concat() FROM (values('1'), ('2'), ('3')); SELECT group_concat("_auto_field_") FROM (values('1'), ('2'), ('3')); SELECT group_concat("_auto_field_", '2') FROM (values('1'), ('2'), ('3')); SELECT group_concat("_auto_field_", '2', '3') FROM (values('1'), ('2'), ('3')); +SELECT group_concat("_auto_field_") FROM (values(NULL), (NULL), (NULL)); +SELECT group_concat("_auto_field_") FROM (values(1), (1), (1)); +SELECT group_concat("_auto_field_") FROM (values(-1), (-1), (-1)); +SELECT group_concat("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); +SELECT group_concat("_auto_field_") FROM (values(true), (true), (true)); +SELECT group_concat("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); +SELECT group_concat("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + -- Function max SELECT max() FROM (values(1), (2), (3)); SELECT max("_auto_field_") FROM (values(1), (2), (3)); SELECT max("_auto_field_", 2) FROM (values(1), (2), (3)); +SELECT max("_auto_field_") FROM (values(NULL), (NULL), (NULL)); +SELECT max("_auto_field_") FROM (values(1), (1), (1)); +SELECT max("_auto_field_") FROM (values(-1), (-1), (-1)); +SELECT max("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); +SELECT max("_auto_field_") FROM (values(true), (true), (true)); +SELECT max("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); +SELECT max("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + -- Function min SELECT min() FROM (values(1), (2), (3)); SELECT min("_auto_field_") FROM (values(1), (2), (3)); SELECT min("_auto_field_", 2) FROM (values(1), (2), (3)); +SELECT min("_auto_field_") FROM (values(NULL), (NULL), (NULL)); +SELECT min("_auto_field_") FROM (values(1), (1), (1)); +SELECT min("_auto_field_") FROM (values(-1), (-1), (-1)); +SELECT min("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); +SELECT min("_auto_field_") FROM (values(true), (true), (true)); +SELECT min("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); +SELECT min("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + -- Function sum SELECT sum() FROM (values(1), (2), (3)); SELECT sum("_auto_field_") FROM (values(1), (2), (3)); SELECT sum("_auto_field_", 2) FROM (values(1), (2), (3)); +SELECT sum("_auto_field_") FROM (values(NULL), (NULL), (NULL)); +SELECT sum("_auto_field_") FROM (values(1), (1), (1)); +SELECT sum("_auto_field_") FROM (values(-1), (-1), (-1)); +SELECT sum("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); +SELECT sum("_auto_field_") FROM (values(true), (true), (true)); +SELECT sum("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); +SELECT sum("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); + -- Function total SELECT total() FROM (values(1), (2), (3)); SELECT total("_auto_field_") FROM (values(1), (2), (3)); SELECT total("_auto_field_", 2) FROM (values(1), (2), (3)); + +SELECT total("_auto_field_") FROM (values(NULL), (NULL), (NULL)); +SELECT total("_auto_field_") FROM (values(1), (1), (1)); +SELECT total("_auto_field_") FROM (values(-1), (-1), (-1)); +SELECT total("_auto_field_") FROM (values(-1.5), (-1.5), (-1.5)); +SELECT total("_auto_field_") FROM (values(true), (true), (true)); +SELECT total("_auto_field_") FROM (values('abc'), ('abc'), ('abc')); +SELECT total("_auto_field_") FROM (values(X'3334'), (X'3334'), (X'3334')); diff --git a/test/sql/types.result b/test/sql/types.result index 70fbbc5a2..0fdfa1283 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -208,32 +208,29 @@ box.execute("CREATE TABLE t1 (s SCALAR PRIMARY KEY);") --- - row_count: 1 ... -box.execute("INSERT INTO t1 VALUES (randomblob(5));") +box.execute("INSERT INTO t1 VALUES (X'FF3435');") --- - row_count: 1 ... box.execute("SELECT * FROM t1 WHERE s LIKE 'blob';") --- - null -- 'Inconsistent types: expected text got varbinary' +- 'Type mismatch: can not convert varbinary to string' ... box.execute("SELECT * FROM t1 WHERE 'blob' LIKE s;") --- - null -- 'Inconsistent types: expected text got varbinary' +- 'Type mismatch: can not convert varbinary to string' ... box.execute("SELECT * FROM t1 WHERE 'blob' LIKE x'0000';") --- - null -- 'Inconsistent types: expected text got varbinary' +- 'Type mismatch: can not convert varbinary to string' ... box.execute("SELECT s LIKE NULL FROM t1;") --- -- metadata: - - name: s LIKE NULL - type: integer - rows: - - [null] +- null +- 'Type mismatch: can not convert varbinary to string' ... box.execute("DELETE FROM t1;") --- @@ -246,20 +243,17 @@ box.execute("INSERT INTO t1 VALUES (1);") box.execute("SELECT * FROM t1 WHERE s LIKE 'int';") --- - null -- 'Inconsistent types: expected text got unsigned' +- 'Type mismatch: can not convert 1 to string' ... box.execute("SELECT * FROM t1 WHERE 'int' LIKE 4;") --- - null -- 'Inconsistent types: expected text got unsigned' +- 'Type mismatch: can not convert 4 to string' ... box.execute("SELECT NULL LIKE s FROM t1;") --- -- metadata: - - name: NULL LIKE s - type: integer - rows: - - [null] +- null +- 'Type mismatch: can not convert 1 to string' ... box.space.T1:drop() --- @@ -830,19 +824,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 +- 'Type mismatch: can not convert 18446744073709551613 to string' ... box.execute("SELECT upper(i) FROM t;") --- -- metadata: - - name: upper(i) - type: string - rows: - - ['18446744073709551613'] +- null +- 'Type mismatch: can not convert 18446744073709551613 to string' ... box.execute("SELECT abs(i) FROM t;") --- @@ -1315,18 +1303,24 @@ box.execute("SELECT group_concat(v) FROM t;") ... box.execute("SELECT lower(v) FROM t;") --- -- null -- 'Inconsistent types: expected text got varbinary' +- metadata: + - name: lower(v) + type: string + rows: + - ['abc'] ... box.execute("SELECT upper(v) FROM t;") --- -- null -- 'Inconsistent types: expected text got varbinary' +- metadata: + - name: upper(v) + type: string + rows: + - ['ABC'] ... box.execute("SELECT abs(v) FROM t;") --- - null -- 'Inconsistent types: expected number got varbinary' +- 'Type mismatch: can not convert varbinary to number' ... box.execute("SELECT typeof(v) FROM t;") --- @@ -1883,25 +1877,13 @@ box.execute("SELECT group_concat(d) FROM t;") ... box.execute("SELECT lower(d) FROM t;") --- -- metadata: - - name: lower(d) - type: string - rows: - - ['10.0'] - - ['-2.0'] - - ['3.3'] - - ['1.8e+19'] +- null +- 'Type mismatch: can not convert 10.0 to string' ... box.execute("SELECT upper(d) FROM t;") --- -- metadata: - - name: upper(d) - type: string - rows: - - ['10.0'] - - ['-2.0'] - - ['3.3'] - - ['1.8E+19'] +- null +- 'Type mismatch: can not convert 10.0 to string' ... box.execute("SELECT abs(d) FROM t;") --- diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index 2dc70f3c5..07158708e 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -59,7 +59,7 @@ box.execute("VALUES (TYPEOF(randomblob(5) || zeroblob(5)));") -- gh-3954: LIKE accepts only arguments of type TEXT and NULLs. -- box.execute("CREATE TABLE t1 (s SCALAR PRIMARY KEY);") -box.execute("INSERT INTO t1 VALUES (randomblob(5));") +box.execute("INSERT INTO t1 VALUES (X'FF3435');") box.execute("SELECT * FROM t1 WHERE s LIKE 'blob';") box.execute("SELECT * FROM t1 WHERE 'blob' LIKE s;") box.execute("SELECT * FROM t1 WHERE 'blob' LIKE x'0000';") -- 2.25.1