From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp40.i.mail.ru (smtp40.i.mail.ru [94.100.177.100]) (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 17883469719 for ; Wed, 14 Oct 2020 01:45:02 +0300 (MSK) Date: Wed, 14 Oct 2020 01:44:59 +0300 From: Mergen Imeev Message-ID: <20201013224459.GA335783@tarantool.org> References: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: Subject: Re: [Tarantool-patches] [PATCH v1 1/1] sql: check arguments types of built-in functions List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: Vladislav Shpilevoy Cc: tarantool-patches@dev.tarantool.org Hi! Thank you for the review! My answers, diff and new patch below. On Fri, Oct 09, 2020 at 11:08:32PM +0200, Vladislav Shpilevoy wrote: > Hi! Thanks for the patch! > > See 12 comments/questions below. > > 1. > > tarantool> box.execute("SELECT GREATEST(1, '2')") > --- > - metadata: > - name: COLUMN_1 > type: scalar > rows: > - ['2'] > ... > > Is it supposed to happen? Shouldn't the function check that all > arguments are of the same type? The same for LEAST(). > At the moment LEAST() ang GREATEST() works using SCALAR rules from BOX for comparison. Not sure if this is right, though. > > 2. A common problem for all functions where you check for their > arguments being NULL: > > tarantool> box.execute([[SELECT POSITION(100, NULL)]]) > --- > - metadata: > - name: COLUMN_1 > type: integer > rows: > - [null] > ... > > Yes, the second argument is NULL, but the first one mismatches its > type. So this should be an error (I think so, but may be wrong). > The same for SUBSTR(), ROUND(), LIKE(), REPLACE(). > Fixed, added tests. > > 3. Is it intended? > > tarantool> box.execute([[SELECT PRINTF(100)]]) > --- > - metadata: > - name: COLUMN_1 > type: string > rows: > - ['100'] > ... > > AFAIR, the function is not a standard one, so I don't know if we > want to allow to omit format argument and just stringify whatever > is passed first. Nonetheless I mention it here in case you wanted > to fix it but missed. > Yes, in this case it is intended. I decided to not change anything in this functions since I am not sure how it should work. > > 4. HEX() in the comment says its argument is blob. But it takes > anything: > > tarantool> box.execute([[SELECT HEX(100)]]) > --- > - metadata: > - name: COLUMN_1 > type: string > rows: > - ['313030'] > ... > > Don't know if it is intended. Likely not. > The comment says that the function 'treats its argument as blob', so I thought that it would be right to allow it to accept anything. However, most likely you are right. However, I changed type of argument to STRING/VARBINARY, mostly because of test that contains hex(substr(...)). Currently substr returns only STRING, even if its argument is of varbinary type. I plan to create an issue. In this issue I plan to suggest to rework built-in functions so they could work right according to ANSI. Defining argument types will be part of the issue. > > 5. TRIM() takes non-string arguments. Definitely a mistake. > > tarantool> box.execute([[SELECT TRIM(100)]]) > --- > - metadata: > - name: COLUMN_1 > type: string > rows: > - ['100'] > ... > Fixed. > > diff --git a/src/box/sql/func.c b/src/box/sql/func.c > > index 0aedb2d3d..d348c3d09 100644 > > --- a/src/box/sql/func.c > > +++ b/src/box/sql/func.c > > @@ -467,30 +467,21 @@ lengthFunc(sql_context * context, int argc, sql_value ** argv) > > > > 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:{ > > - sql_result_uint(context, sql_value_bytes(argv[0])); > > - break; > > - } > > - case MP_STR:{ > > - const unsigned char *z = sql_value_text(argv[0]); > > - if (z == 0) > > - return; > > - len = sql_utf8_char_count(z, sql_value_bytes(argv[0])); > > - sql_result_uint(context, len); > > - break; > > - } > > - default:{ > > - sql_result_null(context); > > - break; > > - } > > - } > > + enum mp_type type = mem_mp_type(argv[0]); > > + if (type == MP_NIL) > > + return sql_result_null(context); > > + if (type == MP_STR) { > > + const unsigned char *z = sql_value_text(argv[0]); > > + if (z == NULL) > > + return sql_result_null(context); > > + len = sql_utf8_char_count(z, sql_value_bytes(argv[0])); > > + return sql_result_uint(context, len); > > + } > > + if (type == MP_BIN) > > + return sql_result_uint(context, sql_value_bytes(argv[0])); > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(argv[0]), "string or varbinary"); > > + context->is_aborted = true; > > 6. What was wrong with leaving it a switch-case? Here and in other places. > For more than 2 similarly looking checks switch usually looks better, and > probably works better as well (but this I don't know for sure, I just > read some stuff about comilers being able to generate kind of smart goto > table for swithches). > Mostly because shich can be used only in functions that accepts only one argument. I think it is better that all of built-in functions (except typeof()) have the same structure. Just my thoughts, though. > > } > > > > /* > > @@ -566,29 +537,23 @@ 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)); > > + if (needle_type != MP_STR && needle_type != MP_BIN) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(needle), "string or varbinary"); > > + context->is_aborted = true; > > + return; > > + } > > + if (haystack_type != MP_STR && haystack_type != MP_BIN) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(haystack), > > + "string or varbinary"); > > context->is_aborted = true; > > return; > > 7. I think you can drop this condition. Because if needle_type is > STR or BIN and bellow you check haystack_type == needle_type, > then haystack_type is also correct. > True, however due to NULL it becomes a bit more complicated that just dropping. Fixed. > > } > > - /* > > - * Both params of Position function must be of the same > > - * type. > > - */ > > if (haystack_type != needle_type) { > > - diag_set(ClientError, ER_INCONSISTENT_TYPES, > > - mem_type_to_str(needle), mem_type_to_str(haystack)); > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(haystack), > > + mem_type_to_str(needle)); > > context->is_aborted = true; > > return; > > } > > @@ -735,12 +700,24 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) > > context->is_aborted = true; > > return; > > } > > - if (sql_value_is_null(argv[1]) > > + if (sql_value_is_null(argv[0]) || sql_value_is_null(argv[1]) > > || (argc == 3 && sql_value_is_null(argv[2])) > > ) { > > return; > > 8. I realized that it looks strange, that some functions allow to pass > NULL into non-main parameters and then return NULL. But some does not. > For example, this function allows to pass NULL instead of the integer > parameters. But function randomBlob() does not. Is it intended? > > Not related to your patch. But if it is a bug, then better file a ticket. > (Kirill will throw it into the wishlist, but at least our conscience will > be clean.) > For now I added 'zeroblob(NULL) == NULL' and 'randomblob(NULL) == NULL'. I plan to mention this case in the issue above. > > @@ -988,9 +979,9 @@ 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]))) { > > + if (!mp_type_is_numeric(sql_value_type(argv[0]))) { > > diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > - sql_value_to_diag_str(argv[0]), "numeric"); > > + sql_value_to_diag_str(argv[0]), "integer"); > > 9. You check for numeric, but print integer? So if I pass 1.1 it will > pass, and will return 1 random byte. But should throw an error. Is there > a test on that? > According to our implicit cast rules, numbers of all types may be converted to INTEGER. However, currently these functions cannot fully work with our INTEGER type. They may only wor with int64. This also will be mentioned in the issue. > > context->is_aborted = true; > > return; > > } > > @@ -1238,14 +1229,17 @@ 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); > > + if (lhs_type == MP_NIL || rhs_type == MP_NIL) > > + return; > > 10. So if I pass NIL and 100, it wil pass? Not sure it should. The > second type still mismatches. The same in some other updated functions. > Fixed. > > + if (rhs_type != MP_STR) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(argv[0]), "string"); > > + context->is_aborted = true; > > + return; > > + } > > + if (lhs_type != MP_STR) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(argv[1]), "string"); > > context->is_aborted = true; > > return; > > } > > @@ -1476,10 +1479,16 @@ 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]); > > + if (sql_value_is_null(argv[i])) > > + continue; > > + if (!mp_type_is_numeric(mem_mp_type(argv[i]))) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(argv[i]), "integer"); > > + context->is_aborted = true; > > + return; > > + } > > + int y = sql_value_int(argv[i]); > > + x = y < 0 ? 0xfffd : y; > > 11. Why was it using 64bit, but now uses simple int? > I did this since sql_value_int() returns int. However, I changed to int64. > > if (x > 0x10ffff) > > x = 0xfffd; > > c = (unsigned)(x & 0x1fffff); > > @@ -1570,6 +1585,43 @@ replaceFunc(sql_context * context, int argc, sql_value ** argv) > > > > assert(argc == 3); > > UNUSED_PARAMETER(argc); > > + enum mp_type type0 = mem_mp_type(argv[0]); > > + enum mp_type type1 = mem_mp_type(argv[1]); > > + enum mp_type type2 = mem_mp_type(argv[2]); > > + if (type0 == MP_NIL || type1 == MP_NIL || type2 == MP_NIL) > > + return; > > + if (type0 != MP_STR && type0 != MP_BIN) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(argv[0]), "string or varbinary"); > > + context->is_aborted = true; > > + return; > > + } > > + if (type1 != MP_STR && type1 != MP_BIN) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(argv[1]), "string or varbinary"); > > + context->is_aborted = true; > > + return; > > + } > > + if (type2 != MP_STR && type2 != MP_BIN) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(argv[2]), "string or varbinary"); > > + context->is_aborted = true; > > + return; > > + } > > 12. It is enough to check type0 and that the other types are equal > to type0. > Fixed. > > + if (type1 != type0) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(argv[1]), > > + mem_type_to_str(argv[0])); > > + context->is_aborted = true; > > + return; > > + } > > + if (type2 != type0) { > > + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > + sql_value_to_diag_str(argv[2]), > > + mem_type_to_str(argv[0])); > > + context->is_aborted = true; > > + return; > > + } > > zStr = sql_value_text(argv[0]); > > if (zStr == 0) > > return; Diff: diff --git a/src/box/sql/func.c b/src/box/sql/func.c index d348c3d09..0da6c8f06 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -535,28 +535,31 @@ position_func(struct sql_context *context, int argc, struct Mem **argv) enum mp_type needle_type = sql_value_type(needle); enum mp_type haystack_type = sql_value_type(haystack); - if (haystack_type == MP_NIL || needle_type == MP_NIL) - return; - if (needle_type != MP_STR && needle_type != MP_BIN) { + if (needle_type != MP_NIL && needle_type != MP_STR && + needle_type != MP_BIN) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(needle), "string or varbinary"); context->is_aborted = true; return; } - if (haystack_type != MP_STR && haystack_type != MP_BIN) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(haystack), - "string or varbinary"); - context->is_aborted = true; - return; + if (haystack_type != MP_NIL && haystack_type != needle_type) { + if (needle_type != MP_NIL) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(haystack), + mem_type_to_str(needle)); + context->is_aborted = true; + return; + } + if (haystack_type != MP_STR && haystack_type != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(haystack), + "string or varbinary"); + context->is_aborted = true; + return; + } } - if (haystack_type != needle_type) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(haystack), - mem_type_to_str(needle)); - context->is_aborted = true; + if (needle_type == MP_NIL || haystack_type == MP_NIL) return; - } int n_needle_bytes = sql_value_bytes(needle); int n_haystack_bytes = sql_value_bytes(haystack); @@ -672,6 +675,17 @@ printfFunc(sql_context * context, int argc, sql_value ** argv) } } +static bool +is_num_to_int_possible(struct Mem *mem) +{ + enum mp_type type = mem_mp_type(mem); + assert(mp_type_is_numeric(type)); + if (type == MP_INT || type == MP_UINT) + return true; + assert(type == MP_DOUBLE); + return mem->u.r >= (double)INT64_MIN && mem->u.r < (double)UINT64_MAX; +} + /* * Implementation of the substr() function. * @@ -690,7 +704,6 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) const unsigned char *z; const unsigned char *z2; int len; - enum mp_type p0type; i64 p1, p2; int negP2 = 0; @@ -700,55 +713,60 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) context->is_aborted = true; return; } - if (sql_value_is_null(argv[0]) || sql_value_is_null(argv[1]) - || (argc == 3 && sql_value_is_null(argv[2])) - ) { - return; - } - p0type = sql_value_type(argv[0]); - if (p0type != MP_STR && p0type != MP_BIN) { + enum mp_type type0 = mem_mp_type(argv[0]); + if (type0 != MP_NIL && type0 != MP_STR && type0 != MP_BIN) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[0]), "string or varbinary"); context->is_aborted = true; return; } - if (!mp_type_is_numeric(mem_mp_type(argv[1]))) { + enum mp_type type1 = mem_mp_type(argv[1]); + if (type1 != MP_NIL && (!mp_type_is_numeric(type1) || + !is_num_to_int_possible(argv[1]))) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[1]), "integer"); context->is_aborted = true; return; } + enum mp_type type2 = MP_UINT; + if (argc == 3) { + type2 = mem_mp_type(argv[2]); + if (type2 != MP_NIL && (!mp_type_is_numeric(type2) || + !is_num_to_int_possible(argv[2]))) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[2]), "integer"); + context->is_aborted = true; + return; + } + } + if (type0 == MP_NIL || type1 == MP_NIL || type2 == MP_NIL) + return; + p1 = sql_value_int(argv[1]); - if (p0type == MP_BIN) { + if (type0 == MP_BIN) { len = sql_value_bytes(argv[0]); z = sql_value_blob(argv[0]); - if (z == 0) + if (z == NULL) return; assert(len == sql_value_bytes(argv[0])); } else { z = sql_value_text(argv[0]); - if (z == 0) + if (z == NULL) return; len = 0; if (p1 < 0) len = sql_utf8_char_count(z, sql_value_bytes(argv[0])); } if (argc == 3) { - if (!mp_type_is_numeric(mem_mp_type(argv[2]))) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[2]), "integer"); - context->is_aborted = true; - return; - } p2 = sql_value_int(argv[2]); if (p2 < 0) { p2 = -p2; negP2 = 1; } } else { - p2 = sql_context_db_handle(context)-> - aLimit[SQL_LIMIT_LENGTH]; + p2 = sql_context_db_handle(context)->aLimit[SQL_LIMIT_LENGTH]; } + if (p1 < 0) { p1 += len; if (p1 < 0) { @@ -770,7 +788,7 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) } } assert(p1 >= 0 && p2 >= 0); - if (p0type != MP_BIN) { + if (type0 != MP_BIN) { /* * In the code below 'cnt' and 'n_chars' is * used because '\0' is not supposed to be @@ -819,28 +837,32 @@ roundFunc(sql_context * context, int argc, sql_value ** argv) context->is_aborted = true; return; } + enum mp_type type0 = mem_mp_type(argv[0]); + if (type0!= MP_NIL && !mp_type_is_numeric(type0)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "numeric"); + context->is_aborted = true; + return; + } + enum mp_type type1 = MP_UINT; if (argc == 2) { - if (sql_value_is_null(argv[1])) - return; - if (!mp_type_is_numeric(mem_mp_type(argv[1]))) { + type1 = mem_mp_type(argv[1]); + if (type1 != MP_NIL && (!mp_type_is_numeric(type1) || + !is_num_to_int_possible(argv[1]))) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[1]), "integer"); context->is_aborted = true; return; } + } + if (type0 == MP_NIL || type1 == MP_NIL) + return; + + if (argc == 2) { 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_numeric(mp_type)) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[0]), "numeric"); - context->is_aborted = true; - return; - } r = sql_value_double(argv[0]); /* If Y==0 and X will fit in a 64-bit int, * handle the rounding directly, @@ -979,7 +1001,10 @@ randomBlob(sql_context * context, int argc, sql_value ** argv) unsigned char *p; assert(argc == 1); UNUSED_PARAMETER(argc); - if (!mp_type_is_numeric(sql_value_type(argv[0]))) { + if (sql_value_is_null(argv[0])) + return; + if (!mp_type_is_numeric(mem_mp_type(argv[0])) || + !is_num_to_int_possible(argv[0])) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[0]), "integer"); context->is_aborted = true; @@ -1229,20 +1254,20 @@ 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_NIL || rhs_type == MP_NIL) - return; - if (rhs_type != MP_STR) { + if (rhs_type != MP_NIL && rhs_type != MP_STR) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[0]), "string"); context->is_aborted = true; return; } - if (lhs_type != MP_STR) { + if (lhs_type != MP_NIL && lhs_type != MP_STR) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[1]), "string"); context->is_aborted = true; return; } + if (rhs_type == MP_NIL || lhs_type == MP_NIL) + return; 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]); @@ -1481,13 +1506,14 @@ charFunc(sql_context * context, int argc, sql_value ** argv) unsigned c; if (sql_value_is_null(argv[i])) continue; - if (!mp_type_is_numeric(mem_mp_type(argv[i]))) { + if (!mp_type_is_numeric(mem_mp_type(argv[i])) || + !is_num_to_int_possible(argv[i])) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[i]), "integer"); context->is_aborted = true; return; } - int y = sql_value_int(argv[i]); + int64_t y = sql_value_int(argv[i]); x = y < 0 ? 0xfffd : y; if (x > 0x10ffff) x = 0xfffd; @@ -1523,6 +1549,15 @@ hexFunc(sql_context * context, int argc, sql_value ** argv) char *zHex, *z; assert(argc == 1); UNUSED_PARAMETER(argc); + enum mp_type type = mem_mp_type(argv[0]); + if (type == MP_NIL) + return; + if (type != MP_BIN && type != MP_STR) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "string or varbinary"); + context->is_aborted = true; + return; + } pBlob = sql_value_blob(argv[0]); n = sql_value_bytes(argv[0]); assert(pBlob == sql_value_blob(argv[0])); /* No encoding change */ @@ -1547,7 +1582,10 @@ zeroblobFunc(sql_context * context, int argc, sql_value ** argv) i64 n; assert(argc == 1); UNUSED_PARAMETER(argc); - if (!mp_type_is_numeric(sql_value_type(argv[0]))) { + if (sql_value_is_null(argv[0])) + return; + if (!mp_type_is_numeric(mem_mp_type(argv[0])) || + !is_num_to_int_possible(argv[0])) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[0]), "integer"); context->is_aborted = true; @@ -1586,42 +1624,48 @@ replaceFunc(sql_context * context, int argc, sql_value ** argv) assert(argc == 3); UNUSED_PARAMETER(argc); enum mp_type type0 = mem_mp_type(argv[0]); - enum mp_type type1 = mem_mp_type(argv[1]); - enum mp_type type2 = mem_mp_type(argv[2]); - if (type0 == MP_NIL || type1 == MP_NIL || type2 == MP_NIL) - return; - if (type0 != MP_STR && type0 != MP_BIN) { + if (type0 != MP_NIL && type0 != MP_STR && type0 != MP_BIN) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[0]), "string or varbinary"); context->is_aborted = true; return; } - if (type1 != MP_STR && type1 != MP_BIN) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[1]), "string or varbinary"); - context->is_aborted = true; - return; - } - if (type2 != MP_STR && type2 != MP_BIN) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[2]), "string or varbinary"); - context->is_aborted = true; - return; + enum mp_type type1 = mem_mp_type(argv[1]); + if (type1 != MP_NIL && type1 != type0) { + if (type0 != MP_NIL) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), + mem_type_to_str(argv[0])); + context->is_aborted = true; + return; + } + if (type1 != MP_STR && type1 != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), + "string or varbinary"); + context->is_aborted = true; + return; + } } - if (type1 != type0) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[1]), - mem_type_to_str(argv[0])); - context->is_aborted = true; - return; + enum mp_type type2 = mem_mp_type(argv[2]); + if (type2 != MP_NIL && type2 != type0) { + if (type0 != MP_NIL) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[2]), + mem_type_to_str(argv[0])); + context->is_aborted = true; + return; + } + if (type2 != MP_STR && type2 != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[2]), + "string or varbinary"); + context->is_aborted = true; + return; + } } - if (type2 != type0) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[2]), - mem_type_to_str(argv[0])); - context->is_aborted = true; + if (type0 == MP_NIL || type1 == MP_NIL || type2 == MP_NIL) return; - } zStr = sql_value_text(argv[0]); if (zStr == 0) return; @@ -1802,6 +1846,12 @@ trim_func_one_arg(struct sql_context *context, sql_value *arg) enum mp_type val_type = sql_value_type(arg); if (val_type == MP_NIL) return; + if (val_type != MP_STR && !mp_type_is_bloblike(val_type)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg), "string or varbinary"); + context->is_aborted = true; + return; + } if (mp_type_is_bloblike(val_type)) default_trim = (const unsigned char *) "\0"; else @@ -1829,26 +1879,47 @@ trim_func_two_args(struct sql_context *context, sql_value *arg1, sql_value *arg2) { const unsigned char *input_str, *trim_set; - if ((input_str = sql_value_text(arg2)) == NULL) + enum mp_type type2 = sql_value_type(arg2); + if (type2 != MP_NIL && type2 != MP_STR && !mp_type_is_bloblike(type2)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg2), "string or varbinary"); + context->is_aborted = true; return; + } - int input_str_sz = sql_value_bytes(arg2); + enum mp_type type1 = sql_value_type(arg1); if (sql_value_type(arg1) == MP_INT || sql_value_type(arg1) == MP_UINT) { + if ((input_str = sql_value_text(arg2)) == NULL) + return; + int input_str_sz = sql_value_bytes(arg2); uint8_t len_one = 1; trim_procedure(context, sql_value_int(arg1), (const unsigned char *) " ", &len_one, 1, input_str, input_str_sz); - } else if ((trim_set = sql_value_text(arg1)) != NULL) { - int trim_set_sz = sql_value_bytes(arg1); - uint8_t *char_len; - int char_cnt = trim_prepare_char_len(context, trim_set, - trim_set_sz, &char_len); - if (char_cnt == -1) - return; - trim_procedure(context, TRIM_BOTH, trim_set, char_len, char_cnt, - input_str, input_str_sz); - sql_free(char_len); + return; + } + if (type1 != MP_NIL && type1 != MP_STR && !mp_type_is_bloblike(type1)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg1), "string or varbinary"); + context->is_aborted = true; + return; } + input_str = sql_value_text(arg2); + if (input_str == NULL) + return; + trim_set = sql_value_text(arg1); + if (trim_set == NULL) + return; + int input_str_sz = sql_value_bytes(arg2); + int trim_set_sz = sql_value_bytes(arg1); + uint8_t *char_len; + int char_cnt = trim_prepare_char_len(context, trim_set, trim_set_sz, + &char_len); + if (char_cnt == -1) + return; + trim_procedure(context, TRIM_BOTH, trim_set, char_len, char_cnt, + input_str, input_str_sz); + sql_free(char_len); } /** @@ -1863,6 +1934,20 @@ trim_func_three_args(struct sql_context *context, sql_value *arg1, sql_value *arg2, sql_value *arg3) { assert(sql_value_type(arg1) == MP_INT || sql_value_type(arg1) == MP_UINT); + enum mp_type type2 = sql_value_type(arg2); + if (type2 != MP_NIL && type2 != MP_STR && !mp_type_is_bloblike(type2)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg2), "string or varbinary"); + context->is_aborted = true; + return; + } + enum mp_type type3 = sql_value_type(arg3); + if (type3 != MP_NIL && type3 != MP_STR && !mp_type_is_bloblike(type3)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg2), "string or varbinary"); + context->is_aborted = true; + return; + } const unsigned char *input_str, *trim_set; if ((input_str = sql_value_text(arg3)) == NULL || (trim_set = sql_value_text(arg2)) == NULL) @@ -2173,9 +2258,7 @@ groupConcatStep(sql_context * context, int argc, sql_value ** argv) return; } enum mp_type type0 = mem_mp_type(argv[0]); - if (type0 == MP_NIL) - return; - if (type0 != MP_STR && type0 != MP_BIN) { + if (type0 != MP_NIL && type0 != MP_STR && type0 != MP_BIN) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[0]), "string or varbinary"); context->is_aborted = true; @@ -2184,14 +2267,26 @@ groupConcatStep(sql_context * context, int argc, sql_value ** argv) if (argc == 2) { enum mp_type type1 = mem_mp_type(argv[1]); if (type1 != MP_NIL && type1 != type0) { - diag_set(ClientError, - ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[1]), - mem_type_to_str(argv[0])); - context->is_aborted = true; - return; + if (type0 != MP_NIL) { + diag_set(ClientError, + ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), + mem_type_to_str(argv[0])); + context->is_aborted = true; + return; + } + if (type1 != MP_STR && type1 != MP_BIN) { + diag_set(ClientError, + ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), + "string or varbinary"); + context->is_aborted = true; + return; + } } } + if (type0 == MP_NIL) + return; pAccum = (StrAccum *) sql_aggregate_context(context, sizeof(*pAccum)); diff --git a/test/sql-tap/position.test.lua b/test/sql-tap/position.test.lua index c3fce6673..0a2c42173 100755 --- a/test/sql-tap/position.test.lua +++ b/test/sql-tap/position.test.lua @@ -248,7 +248,7 @@ test:do_test( return test:catchsql "SELECT position(x'3334', 123456.78);" end, { -- - 1, "Type mismatch: can not convert 123456.78 to string or varbinary" + 1, "Type mismatch: can not convert 123456.78 to varbinary" -- }) diff --git a/test/sql/types.result b/test/sql/types.result index daebf9b39..262b54605 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -229,11 +229,8 @@ box.execute("SELECT * FROM t1 WHERE 'blob' LIKE x'0000';") ... box.execute("SELECT s LIKE NULL FROM t1;") --- -- metadata: - - name: COLUMN_1 - type: integer - rows: - - [null] +- null +- 'Type mismatch: can not convert varbinary to string' ... box.execute("DELETE FROM t1;") --- @@ -255,11 +252,8 @@ box.execute("SELECT * FROM t1 WHERE 'int' LIKE 4;") ... box.execute("SELECT NULL LIKE s FROM t1;") --- -- metadata: - - name: COLUMN_1 - type: integer - rows: - - [null] +- null +- 'Type mismatch: can not convert 1 to string' ... box.space.T1:drop() --- @@ -3142,43 +3136,28 @@ box.execute([[SELECT greatest(1, NULL);]]) ... box.execute([[SELECT hex(-1);]]) --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['2D31'] +- null +- 'Type mismatch: can not convert -1 to string or varbinary' ... box.execute([[SELECT hex(1);]]) --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['31'] +- null +- 'Type mismatch: can not convert 1 to string or varbinary' ... box.execute([[SELECT hex(1.5);]]) --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['312E35'] +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' ... box.execute([[SELECT hex(-1.5);]]) --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['2D312E35'] +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' ... box.execute([[SELECT hex(true);]]) --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['54525545'] +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' ... box.execute([[SELECT hex('a');]]) --- @@ -3202,7 +3181,7 @@ box.execute([[SELECT hex(NULL);]]) - name: COLUMN_1 type: string rows: - - [''] + - [null] ... box.execute([[SELECT ifnull(-1, -1);]]) --- @@ -3699,7 +3678,7 @@ box.execute([[SELECT position('a', 'a');]]) box.execute([[SELECT position('a', 1);]]) --- - null -- 'Type mismatch: can not convert 1 to string or varbinary' +- 'Type mismatch: can not convert 1 to text' ... box.execute([[SELECT position(1, 'a');]]) --- @@ -3717,7 +3696,7 @@ box.execute([[SELECT position(X'33', X'33');]]) box.execute([[SELECT position(X'33', 1);]]) --- - null -- 'Type mismatch: can not convert 1 to string or varbinary' +- 'Type mismatch: can not convert 1 to varbinary' ... box.execute([[SELECT position(NULL, NULL);]]) --- @@ -3743,6 +3722,16 @@ box.execute([[SELECT position('a', NULL);]]) rows: - [null] ... +box.execute([[SELECT position(NULL, 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT position(1, NULL);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... box.execute([[SELECT printf(-1);]]) --- - metadata: @@ -3920,8 +3909,11 @@ box.execute([[SELECT randomblob(X'33');]]) ... box.execute([[SELECT randomblob(NULL);]]) --- -- null -- 'Type mismatch: can not convert (null) to integer' +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - [null] ... box.execute([[SELECT replace(-1, -1, -1);]]) --- @@ -3959,12 +3951,12 @@ box.execute([[SELECT replace('a', 'a', 'a');]]) box.execute([[SELECT replace('a', 'a', 1);]]) --- - null -- 'Type mismatch: can not convert 1 to string or varbinary' +- 'Type mismatch: can not convert 1 to text' ... box.execute([[SELECT replace('a', 1, 'a');]]) --- - null -- 'Type mismatch: can not convert 1 to string or varbinary' +- 'Type mismatch: can not convert 1 to text' ... box.execute([[SELECT replace(1, 'a', 'a');]]) --- @@ -4002,12 +3994,12 @@ box.execute([[SELECT replace(X'33', X'33', X'33');]]) box.execute([[SELECT replace(X'33', X'33', 1);]]) --- - null -- 'Type mismatch: can not convert 1 to string or varbinary' +- 'Type mismatch: can not convert 1 to varbinary' ... box.execute([[SELECT replace(X'33', 1, X'33');]]) --- - null -- 'Type mismatch: can not convert 1 to string or varbinary' +- 'Type mismatch: can not convert 1 to varbinary' ... box.execute([[SELECT replace(1, X'33', X'33');]]) --- @@ -4070,6 +4062,21 @@ box.execute([[SELECT replace('a', 'a', NULL);]]) rows: - [null] ... +box.execute([[SELECT replace(NULL, 1, 'a');]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT replace('a', NULL, 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to text' +... +box.execute([[SELECT replace(1, 'a', NULL);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... box.execute([[SELECT round(-1, -1);]]) --- - metadata: @@ -4115,17 +4122,17 @@ box.execute([[SELECT round(true, 1.5);]]) box.execute([[SELECT round(true, true);]]) --- - null -- 'Type mismatch: can not convert TRUE to integer' +- 'Type mismatch: can not convert TRUE to numeric' ... box.execute([[SELECT round('a', 'a');]]) --- - null -- 'Type mismatch: can not convert a to integer' +- 'Type mismatch: can not convert a to numeric' ... box.execute([[SELECT round(X'33', X'33');]]) --- - null -- 'Type mismatch: can not convert varbinary to integer' +- 'Type mismatch: can not convert varbinary to numeric' ... box.execute([[SELECT round(NULL, NULL);]]) --- @@ -4305,6 +4312,21 @@ box.execute([[SELECT substr('a', 1, NULL);]]) rows: - [null] ... +box.execute([[SELECT substr(NULL, '1', 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to integer' +... +box.execute([[SELECT substr('a', NULL, '1');]]) +--- +- null +- 'Type mismatch: can not convert 1 to integer' +... +box.execute([[SELECT substr(1, 1, NULL);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... box.execute([[SELECT typeof(-1);]]) --- - metadata: @@ -4577,48 +4599,36 @@ box.execute([[SELECT zeroblob(X'33');]]) ... box.execute([[SELECT zeroblob(NULL);]]) --- -- null -- 'Type mismatch: can not convert (null) to integer' -... -box.execute([[SELECT trim(-1);]]) ---- - metadata: - name: COLUMN_1 - type: string + type: varbinary rows: - - ['-1'] + - [null] +... +box.execute([[SELECT trim(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' ... box.execute([[SELECT trim(1);]]) --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['1'] +- null +- 'Type mismatch: can not convert 1 to string or varbinary' ... box.execute([[SELECT trim(1.5);]]) --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['1.5'] +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' ... box.execute([[SELECT trim(-1.5);]]) --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['-1.5'] +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' ... box.execute([[SELECT trim(true);]]) --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['TRUE'] +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' ... box.execute([[SELECT trim('a');]]) --- @@ -4711,6 +4721,16 @@ box.execute([[SELECT NULL like 'a';]]) rows: - [null] ... +box.execute([[SELECT 1 like NULL;]]) +--- +- null +- 'Type mismatch: can not convert 1 to string' +... +box.execute([[SELECT NULL like 1;]]) +--- +- null +- 'Type mismatch: can not convert 1 to string' +... box.execute([[CREATE TABLE t (i INTEGER PRIMARY KEY, u UNSIGNED, d DOUBLE, o DOUBLE, b BOOLEAN, s STRING, v VARBINARY, n SCALAR);]]) --- - row_count: 1 @@ -4943,6 +4963,16 @@ box.execute([[SELECT group_concat(n, n) FROM t;]]) rows: - [null] ... +box.execute([[SELECT group_concat(n, i) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT group_concat(i, n) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... box.execute([[SELECT max(i) FROM t;]]) --- - metadata: diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index 5f6a658b1..f82223236 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -777,6 +777,8 @@ box.execute([[SELECT position(X'33', 1);]]) box.execute([[SELECT position(NULL, NULL);]]) box.execute([[SELECT position(NULL, 'a');]]) box.execute([[SELECT position('a', NULL);]]) +box.execute([[SELECT position(NULL, 1);]]) +box.execute([[SELECT position(1, NULL);]]) box.execute([[SELECT printf(-1);]]) box.execute([[SELECT printf(1);]]) @@ -829,6 +831,9 @@ box.execute([[SELECT replace(NULL, 'a', 'a');]]) box.execute([[SELECT replace('a', NULL, NULL);]]) box.execute([[SELECT replace('a', NULL, 'a');]]) box.execute([[SELECT replace('a', 'a', NULL);]]) +box.execute([[SELECT replace(NULL, 1, 'a');]]) +box.execute([[SELECT replace('a', NULL, 1);]]) +box.execute([[SELECT replace(1, 'a', NULL);]]) box.execute([[SELECT round(-1, -1);]]) box.execute([[SELECT round(1, 1);]]) @@ -870,6 +875,9 @@ box.execute([[SELECT substr(NULL, NULL, NULL);]]) box.execute([[SELECT substr(NULL, 1, 1);]]) box.execute([[SELECT substr('a', NULL, 1);]]) box.execute([[SELECT substr('a', 1, NULL);]]) +box.execute([[SELECT substr(NULL, '1', 1);]]) +box.execute([[SELECT substr('a', NULL, '1');]]) +box.execute([[SELECT substr(1, 1, NULL);]]) box.execute([[SELECT typeof(-1);]]) box.execute([[SELECT typeof(1);]]) @@ -936,6 +944,8 @@ box.execute([[SELECT X'33' like X'33';]]) box.execute([[SELECT NULL like NULL;]]) box.execute([[SELECT 'a' like NULL;]]) box.execute([[SELECT NULL like 'a';]]) +box.execute([[SELECT 1 like NULL;]]) +box.execute([[SELECT NULL like 1;]]) box.execute([[CREATE TABLE t (i INTEGER PRIMARY KEY, u UNSIGNED, d DOUBLE, o DOUBLE, b BOOLEAN, s STRING, v VARBINARY, n SCALAR);]]) box.execute([[INSERT INTO t VALUES (-1, 1, 1.5, -1.5, true, 'a', X'33', NULL);]]) @@ -976,6 +986,8 @@ box.execute([[SELECT group_concat(s, s) FROM t;]]) box.execute([[SELECT group_concat(s, v) FROM t;]]) box.execute([[SELECT group_concat(v, s) FROM t;]]) box.execute([[SELECT group_concat(n, n) FROM t;]]) +box.execute([[SELECT group_concat(n, i) FROM t;]]) +box.execute([[SELECT group_concat(i, n) FROM t;]]) box.execute([[SELECT max(i) FROM t;]]) box.execute([[SELECT max(u) FROM t;]]) New patch: >From d1a97a66e58ad09aa9b1f7dea02b42f5d560c0bc Mon Sep 17 00:00:00 2001 From: Mergen Imeev Date: Thu, 8 Oct 2020 00:23:18 +0300 Subject: [PATCH] sql: check arguments types of built-in functions After this patch, the argument types of the SQL built-in functions will be checked. Implicit casting rules will be applied during this check. Closes #4159 diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 0aedb2d3d..0da6c8f06 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -467,30 +467,21 @@ lengthFunc(sql_context * context, int argc, sql_value ** argv) 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:{ - sql_result_uint(context, sql_value_bytes(argv[0])); - break; - } - case MP_STR:{ - const unsigned char *z = sql_value_text(argv[0]); - if (z == 0) - return; - len = sql_utf8_char_count(z, sql_value_bytes(argv[0])); - sql_result_uint(context, len); - break; - } - default:{ - sql_result_null(context); - break; - } - } + enum mp_type type = mem_mp_type(argv[0]); + if (type == MP_NIL) + return sql_result_null(context); + if (type == MP_STR) { + const unsigned char *z = sql_value_text(argv[0]); + if (z == NULL) + return sql_result_null(context); + len = sql_utf8_char_count(z, sql_value_bytes(argv[0])); + return sql_result_uint(context, len); + } + if (type == MP_BIN) + return sql_result_uint(context, sql_value_bytes(argv[0])); + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "string or varbinary"); + context->is_aborted = true; } /* @@ -504,44 +495,24 @@ absFunc(sql_context * context, int argc, sql_value ** argv) { assert(argc == 1); UNUSED_PARAMETER(argc); - switch (sql_value_type(argv[0])) { - case MP_UINT: { - sql_result_uint(context, sql_value_uint64(argv[0])); - break; - } - case MP_INT: { + enum mp_type type = mem_mp_type(argv[0]); + if (type == MP_NIL) + return sql_result_null(context); + if (type == MP_UINT) + return sql_result_uint(context, sql_value_uint64(argv[0])); + if (type == MP_INT) { int64_t value = sql_value_int64(argv[0]); - assert(value < 0); - sql_result_uint(context, -value); - break; - } - case MP_NIL:{ - /* IMP: R-37434-19929 Abs(X) returns NULL if X is NULL. */ - sql_result_null(context); - break; - } - case MP_BOOL: - case MP_BIN: - case MP_ARRAY: - case MP_MAP: { - diag_set(ClientError, ER_INCONSISTENT_TYPES, "number", - mem_type_to_str(argv[0])); - context->is_aborted = true; - return; + return sql_result_uint(context, (uint64_t)(-value)); } - 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; - } + if (type == MP_DOUBLE) { + double value = sql_value_double(argv[0]); + if (value < 0) + value = -value; + return sql_result_double(context, value); } + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "number"); + context->is_aborted = true; } /** @@ -564,34 +535,31 @@ position_func(struct sql_context *context, int argc, struct Mem **argv) enum mp_type needle_type = sql_value_type(needle); enum mp_type haystack_type = sql_value_type(haystack); - 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)); + if (needle_type != MP_NIL && needle_type != MP_STR && + needle_type != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(needle), "string or varbinary"); context->is_aborted = true; return; } - /* - * Both params of Position function must be of the same - * type. - */ - if (haystack_type != needle_type) { - diag_set(ClientError, ER_INCONSISTENT_TYPES, - mem_type_to_str(needle), mem_type_to_str(haystack)); - context->is_aborted = true; - return; + if (haystack_type != MP_NIL && haystack_type != needle_type) { + if (needle_type != MP_NIL) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(haystack), + mem_type_to_str(needle)); + context->is_aborted = true; + return; + } + if (haystack_type != MP_STR && haystack_type != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(haystack), + "string or varbinary"); + context->is_aborted = true; + return; + } } + if (needle_type == MP_NIL || haystack_type == MP_NIL) + return; int n_needle_bytes = sql_value_bytes(needle); int n_haystack_bytes = sql_value_bytes(haystack); @@ -707,6 +675,17 @@ printfFunc(sql_context * context, int argc, sql_value ** argv) } } +static bool +is_num_to_int_possible(struct Mem *mem) +{ + enum mp_type type = mem_mp_type(mem); + assert(mp_type_is_numeric(type)); + if (type == MP_INT || type == MP_UINT) + return true; + assert(type == MP_DOUBLE); + return mem->u.r >= (double)INT64_MIN && mem->u.r < (double)UINT64_MAX; +} + /* * Implementation of the substr() function. * @@ -725,7 +704,6 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) const unsigned char *z; const unsigned char *z2; int len; - int p0type; i64 p1, p2; int negP2 = 0; @@ -735,22 +713,45 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) context->is_aborted = true; return; } - if (sql_value_is_null(argv[1]) - || (argc == 3 && sql_value_is_null(argv[2])) - ) { + enum mp_type type0 = mem_mp_type(argv[0]); + if (type0 != MP_NIL && type0 != MP_STR && type0 != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "string or varbinary"); + context->is_aborted = true; return; } - p0type = sql_value_type(argv[0]); + enum mp_type type1 = mem_mp_type(argv[1]); + if (type1 != MP_NIL && (!mp_type_is_numeric(type1) || + !is_num_to_int_possible(argv[1]))) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), "integer"); + context->is_aborted = true; + return; + } + enum mp_type type2 = MP_UINT; + if (argc == 3) { + type2 = mem_mp_type(argv[2]); + if (type2 != MP_NIL && (!mp_type_is_numeric(type2) || + !is_num_to_int_possible(argv[2]))) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[2]), "integer"); + context->is_aborted = true; + return; + } + } + if (type0 == MP_NIL || type1 == MP_NIL || type2 == MP_NIL) + return; + p1 = sql_value_int(argv[1]); - if (p0type == MP_BIN) { + if (type0 == MP_BIN) { len = sql_value_bytes(argv[0]); z = sql_value_blob(argv[0]); - if (z == 0) + if (z == NULL) return; assert(len == sql_value_bytes(argv[0])); } else { z = sql_value_text(argv[0]); - if (z == 0) + if (z == NULL) return; len = 0; if (p1 < 0) @@ -763,9 +764,9 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) negP2 = 1; } } else { - p2 = sql_context_db_handle(context)-> - aLimit[SQL_LIMIT_LENGTH]; + p2 = sql_context_db_handle(context)->aLimit[SQL_LIMIT_LENGTH]; } + if (p1 < 0) { p1 += len; if (p1 < 0) { @@ -787,7 +788,7 @@ substrFunc(sql_context * context, int argc, sql_value ** argv) } } assert(p1 >= 0 && p2 >= 0); - if (p0type != MP_BIN) { + if (type0 != MP_BIN) { /* * In the code below 'cnt' and 'n_chars' is * used because '\0' is not supposed to be @@ -836,22 +837,32 @@ roundFunc(sql_context * context, int argc, sql_value ** argv) context->is_aborted = true; return; } + enum mp_type type0 = mem_mp_type(argv[0]); + if (type0!= MP_NIL && !mp_type_is_numeric(type0)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "numeric"); + context->is_aborted = true; + return; + } + enum mp_type type1 = MP_UINT; if (argc == 2) { - if (sql_value_is_null(argv[1])) + type1 = mem_mp_type(argv[1]); + if (type1 != MP_NIL && (!mp_type_is_numeric(type1) || + !is_num_to_int_possible(argv[1]))) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), "integer"); + context->is_aborted = true; return; + } + } + if (type0 == MP_NIL || type1 == MP_NIL) + return; + + if (argc == 2) { 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; - } r = sql_value_double(argv[0]); /* If Y==0 and X will fit in a 64-bit int, * handle the rounding directly, @@ -907,9 +918,11 @@ case_type##ICUFunc(sql_context *context, int argc, sql_value **argv) \ 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"); \ + if (arg_type == MP_NIL) \ + return; \ + if (arg_type != MP_STR) { \ + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, \ + sql_value_to_diag_str(argv[0]), "string"); \ context->is_aborted = true; \ return; \ } \ @@ -988,9 +1001,12 @@ 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]))) { + if (sql_value_is_null(argv[0])) + return; + if (!mp_type_is_numeric(mem_mp_type(argv[0])) || + !is_num_to_int_possible(argv[0])) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(argv[0]), "numeric"); + sql_value_to_diag_str(argv[0]), "integer"); context->is_aborted = true; return; } @@ -1238,17 +1254,20 @@ 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); + if (rhs_type != MP_NIL && rhs_type != MP_STR) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "string"); + context->is_aborted = true; + return; + } + if (lhs_type != MP_NIL && lhs_type != MP_STR) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), "string"); context->is_aborted = true; return; } + if (rhs_type == MP_NIL || lhs_type == MP_NIL) + return; 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]); @@ -1452,6 +1471,15 @@ quoteFunc(sql_context * context, int argc, sql_value ** argv) static void unicodeFunc(sql_context * context, int argc, sql_value ** argv) { + enum mp_type type = mem_mp_type(argv[0]); + if (type == MP_NIL) + return; + if (type != MP_STR && type != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "string or varbinary"); + context->is_aborted = true; + return; + } const unsigned char *z = sql_value_text(argv[0]); (void)argc; if (z && z[0]) @@ -1476,10 +1504,17 @@ 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]); + if (sql_value_is_null(argv[i])) + continue; + if (!mp_type_is_numeric(mem_mp_type(argv[i])) || + !is_num_to_int_possible(argv[i])) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[i]), "integer"); + context->is_aborted = true; + return; + } + int64_t y = sql_value_int(argv[i]); + x = y < 0 ? 0xfffd : y; if (x > 0x10ffff) x = 0xfffd; c = (unsigned)(x & 0x1fffff); @@ -1514,6 +1549,15 @@ hexFunc(sql_context * context, int argc, sql_value ** argv) char *zHex, *z; assert(argc == 1); UNUSED_PARAMETER(argc); + enum mp_type type = mem_mp_type(argv[0]); + if (type == MP_NIL) + return; + if (type != MP_BIN && type != MP_STR) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "string or varbinary"); + context->is_aborted = true; + return; + } pBlob = sql_value_blob(argv[0]); n = sql_value_bytes(argv[0]); assert(pBlob == sql_value_blob(argv[0])); /* No encoding change */ @@ -1538,6 +1582,15 @@ zeroblobFunc(sql_context * context, int argc, sql_value ** argv) i64 n; assert(argc == 1); UNUSED_PARAMETER(argc); + if (sql_value_is_null(argv[0])) + return; + if (!mp_type_is_numeric(mem_mp_type(argv[0])) || + !is_num_to_int_possible(argv[0])) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "integer"); + context->is_aborted = true; + return; + } n = sql_value_int64(argv[0]); if (n < 0) n = 0; @@ -1570,6 +1623,49 @@ replaceFunc(sql_context * context, int argc, sql_value ** argv) assert(argc == 3); UNUSED_PARAMETER(argc); + enum mp_type type0 = mem_mp_type(argv[0]); + if (type0 != MP_NIL && type0 != MP_STR && type0 != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "string or varbinary"); + context->is_aborted = true; + return; + } + enum mp_type type1 = mem_mp_type(argv[1]); + if (type1 != MP_NIL && type1 != type0) { + if (type0 != MP_NIL) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), + mem_type_to_str(argv[0])); + context->is_aborted = true; + return; + } + if (type1 != MP_STR && type1 != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), + "string or varbinary"); + context->is_aborted = true; + return; + } + } + enum mp_type type2 = mem_mp_type(argv[2]); + if (type2 != MP_NIL && type2 != type0) { + if (type0 != MP_NIL) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[2]), + mem_type_to_str(argv[0])); + context->is_aborted = true; + return; + } + if (type2 != MP_STR && type2 != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[2]), + "string or varbinary"); + context->is_aborted = true; + return; + } + } + if (type0 == MP_NIL || type1 == MP_NIL || type2 == MP_NIL) + return; zStr = sql_value_text(argv[0]); if (zStr == 0) return; @@ -1750,6 +1846,12 @@ trim_func_one_arg(struct sql_context *context, sql_value *arg) enum mp_type val_type = sql_value_type(arg); if (val_type == MP_NIL) return; + if (val_type != MP_STR && !mp_type_is_bloblike(val_type)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg), "string or varbinary"); + context->is_aborted = true; + return; + } if (mp_type_is_bloblike(val_type)) default_trim = (const unsigned char *) "\0"; else @@ -1777,26 +1879,47 @@ trim_func_two_args(struct sql_context *context, sql_value *arg1, sql_value *arg2) { const unsigned char *input_str, *trim_set; - if ((input_str = sql_value_text(arg2)) == NULL) + enum mp_type type2 = sql_value_type(arg2); + if (type2 != MP_NIL && type2 != MP_STR && !mp_type_is_bloblike(type2)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg2), "string or varbinary"); + context->is_aborted = true; return; + } - int input_str_sz = sql_value_bytes(arg2); + enum mp_type type1 = sql_value_type(arg1); if (sql_value_type(arg1) == MP_INT || sql_value_type(arg1) == MP_UINT) { + if ((input_str = sql_value_text(arg2)) == NULL) + return; + int input_str_sz = sql_value_bytes(arg2); uint8_t len_one = 1; trim_procedure(context, sql_value_int(arg1), (const unsigned char *) " ", &len_one, 1, input_str, input_str_sz); - } else if ((trim_set = sql_value_text(arg1)) != NULL) { - int trim_set_sz = sql_value_bytes(arg1); - uint8_t *char_len; - int char_cnt = trim_prepare_char_len(context, trim_set, - trim_set_sz, &char_len); - if (char_cnt == -1) - return; - trim_procedure(context, TRIM_BOTH, trim_set, char_len, char_cnt, - input_str, input_str_sz); - sql_free(char_len); + return; + } + if (type1 != MP_NIL && type1 != MP_STR && !mp_type_is_bloblike(type1)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg1), "string or varbinary"); + context->is_aborted = true; + return; } + input_str = sql_value_text(arg2); + if (input_str == NULL) + return; + trim_set = sql_value_text(arg1); + if (trim_set == NULL) + return; + int input_str_sz = sql_value_bytes(arg2); + int trim_set_sz = sql_value_bytes(arg1); + uint8_t *char_len; + int char_cnt = trim_prepare_char_len(context, trim_set, trim_set_sz, + &char_len); + if (char_cnt == -1) + return; + trim_procedure(context, TRIM_BOTH, trim_set, char_len, char_cnt, + input_str, input_str_sz); + sql_free(char_len); } /** @@ -1811,6 +1934,20 @@ trim_func_three_args(struct sql_context *context, sql_value *arg1, sql_value *arg2, sql_value *arg3) { assert(sql_value_type(arg1) == MP_INT || sql_value_type(arg1) == MP_UINT); + enum mp_type type2 = sql_value_type(arg2); + if (type2 != MP_NIL && type2 != MP_STR && !mp_type_is_bloblike(type2)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg2), "string or varbinary"); + context->is_aborted = true; + return; + } + enum mp_type type3 = sql_value_type(arg3); + if (type3 != MP_NIL && type3 != MP_STR && !mp_type_is_bloblike(type3)) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(arg2), "string or varbinary"); + context->is_aborted = true; + return; + } const unsigned char *input_str, *trim_set; if ((input_str = sql_value_text(arg3)) == NULL || (trim_set = sql_value_text(arg2)) == NULL) @@ -1880,7 +2017,9 @@ soundexFunc(sql_context * context, int argc, sql_value ** argv) }; assert(argc == 1); enum mp_type mp_type = sql_value_type(argv[0]); - if (mp_type_is_bloblike(mp_type)) { + if (mp_type == MP_NIL) + return; + if (mp_type != MP_STR) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(argv[0]), "text"); context->is_aborted = true; @@ -1953,13 +2092,10 @@ sum_step(struct sql_context *context, int argc, sql_value **argv) 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]); + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "number"); + context->is_aborted = true; + return; } p->cnt++; if (type == MP_INT || type == MP_UINT) { @@ -2121,7 +2257,35 @@ groupConcatStep(sql_context * context, int argc, sql_value ** argv) context->is_aborted = true; return; } - if (sql_value_is_null(argv[0])) + enum mp_type type0 = mem_mp_type(argv[0]); + if (type0 != MP_NIL && type0 != MP_STR && type0 != MP_BIN) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[0]), "string or varbinary"); + context->is_aborted = true; + return; + } + if (argc == 2) { + enum mp_type type1 = mem_mp_type(argv[1]); + if (type1 != MP_NIL && type1 != type0) { + if (type0 != MP_NIL) { + diag_set(ClientError, + ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), + mem_type_to_str(argv[0])); + context->is_aborted = true; + return; + } + if (type1 != MP_STR && type1 != MP_BIN) { + diag_set(ClientError, + ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(argv[1]), + "string or varbinary"); + context->is_aborted = true; + return; + } + } + } + if (type0 == MP_NIL) return; pAccum = (StrAccum *) sql_aggregate_context(context, sizeof(*pAccum)); diff --git a/test/sql-tap/aggnested.test.lua b/test/sql-tap/aggnested.test.lua index 67a9ba891..a09e654e7 100755 --- a/test/sql-tap/aggnested.test.lua +++ b/test/sql-tap/aggnested.test.lua @@ -28,7 +28,7 @@ test:do_execsql_test( INSERT INTO t1 VALUES(1), (2), (3); CREATE TABLE t2(b1 INTEGER PRIMARY KEY); INSERT INTO t2 VALUES(4), (5); - SELECT (SELECT group_concat(a1,'x') FROM t2 LIMIT 1) FROM t1; + SELECT (SELECT group_concat(CAST(a1 AS STRING),'x') FROM t2 LIMIT 1) FROM t1; ]], { -- @@ -40,7 +40,7 @@ test:do_execsql_test( "aggnested-1.2", [[ SELECT - (SELECT group_concat(a1,'x') || '-' || group_concat(b1,'y') FROM t2) + (SELECT group_concat(CAST(a1 AS STRING),'x') || '-' || group_concat(CAST(b1 AS STRING),'y') FROM t2) FROM t1; ]], { @@ -51,7 +51,7 @@ test:do_execsql_test( test:do_execsql_test("aggnested-1.3", [[ - SELECT (SELECT group_concat(b1,a1) FROM t2) FROM t1; + SELECT (SELECT group_concat(CAST(b1 AS STRING),CAST(a1 AS STRING)) FROM t2) FROM t1; ]], { -- @@ -61,7 +61,7 @@ test:do_execsql_test("aggnested-1.3", test:do_execsql_test("aggnested-1.4", [[ - SELECT (SELECT group_concat(a1,b1) FROM t2) FROM t1; + SELECT (SELECT group_concat(CAST(a1 AS STRING),CAST(b1 AS STRING)) FROM t2) FROM t1; ]], { -- diff --git a/test/sql-tap/cse.test.lua b/test/sql-tap/cse.test.lua index 341b6de01..cfb642cfe 100755 --- a/test/sql-tap/cse.test.lua +++ b/test/sql-tap/cse.test.lua @@ -198,7 +198,7 @@ test:do_execsql_test( test:do_execsql_test( "cse-1.13", [[ - SELECT upper(b), typeof(b), b FROM t1 + SELECT upper(CAST(b AS STRING)), typeof(b), b FROM t1 ]], { -- "11", "integer", 11, "21", "integer", 21 @@ -208,7 +208,7 @@ test:do_execsql_test( test:do_execsql_test( "cse-1.14", [[ - SELECT b, typeof(b), upper(b), typeof(b), b FROM t1 + SELECT b, typeof(b), upper(CAST(b AS STRING)), typeof(b), b FROM t1 ]], { -- 11, "integer", "11", "integer", 11, 21, "integer", "21", "integer", 21 diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua index 578620fca..438779898 100755 --- a/test/sql-tap/e_select1.test.lua +++ b/test/sql-tap/e_select1.test.lua @@ -940,7 +940,7 @@ test:do_select_tests( {"4", "SELECT *, count(*) FROM a1 JOIN a2", {4, 10, 10, 4, 16}}, {"5", "SELECT *, sum(three) FROM a1 NATURAL JOIN a2", {3, 6, 2, 3}}, {"6", "SELECT *, sum(three) FROM a1 NATURAL JOIN a2", {3, 6, 2, 3}}, - {"7", "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2", {"12", 3, 6}}, + {"7", "SELECT group_concat(CAST(three AS STRING), ''), a1.* FROM a1 NATURAL JOIN a2", {"12", 3, 6}}, }) -- EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then @@ -1022,10 +1022,10 @@ test:do_execsql_test( test:do_select_tests( "e_select-4.9", { - {"1", "SELECT group_concat(one), two FROM b1 GROUP BY two", {"4,5","f","1","o","6,7","s","2,3","t"}}, - {"2", "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)", {"1,2,3,4",10,"5,6,7",18}}, - {"3", "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2", {"4","1,5","2,6","3,7"}}, - {"4", "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')", {"3,4,5,6,7","1,2"}}, + {"1", "SELECT group_concat(CAST(one AS STRING)), two FROM b1 GROUP BY two", {"4,5","f","1","o","6,7","s","2,3","t"}}, + {"2", "SELECT group_concat(CAST(one AS STRING)), sum(one) FROM b1 GROUP BY (one>4)", {"1,2,3,4",10,"5,6,7",18}}, + {"3", "SELECT group_concat(CAST(one AS STRING)) FROM b1 GROUP BY (two>'o'), one%2", {"4","1,5","2,6","3,7"}}, + {"4", "SELECT group_concat(CAST(one AS STRING)) FROM b1 GROUP BY (one==2 OR two=='o')", {"3,4,5,6,7","1,2"}}, }) -- EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL @@ -1034,7 +1034,7 @@ test:do_select_tests( test:do_select_tests( "e_select-4.10", { - {"1", "SELECT group_concat(y) FROM b2 GROUP BY x", {"0,1","3","2,4"}}, + {"1", "SELECT group_concat(CAST(y AS STRING)) FROM b2 GROUP BY x", {"0,1","3","2,4"}}, {"2", "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END", {4, 1}}, }) diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua index 3c088920f..71da86220 100755 --- a/test/sql-tap/func.test.lua +++ b/test/sql-tap/func.test.lua @@ -95,7 +95,7 @@ test:do_execsql_test( test:do_execsql_test( "func-1.4", [[ - SELECT coalesce(length(a),-1) FROM t2 + SELECT coalesce(length(CAST(a AS STRING)),-1) FROM t2 ]], { -- 1, -1, 3, -1, 5 @@ -197,7 +197,7 @@ test:do_execsql_test( test:do_execsql_test( "func-2.9", [[ - SELECT substr(a,1,1) FROM t2 + SELECT substr(CAST(a AS STRING),1,1) FROM t2 ]], { -- "1", "", "3", "", "6" @@ -207,7 +207,7 @@ test:do_execsql_test( test:do_execsql_test( "func-2.10", [[ - SELECT substr(a,2,2) FROM t2 + SELECT substr(CAST(a AS STRING),2,2) FROM t2 ]], { -- "", "", "45", "", "78" @@ -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 numeric" -- }) @@ -763,7 +763,7 @@ test:do_execsql_test( test:do_execsql_test( "func-5.3", [[ - SELECT upper(a), lower(a) FROM t2 + SELECT upper(CAST(a AS STRING)), lower(CAST(a AS STRING)) FROM t2 ]], { -- "1","1","","","345","345","","","67890","67890" @@ -797,7 +797,7 @@ test:do_execsql_test( test:do_execsql_test( "func-6.2", [[ - SELECT coalesce(upper(a),'nil') FROM t2 + SELECT coalesce(upper(CAST(a AS STRING)),'nil') FROM t2 ]], { -- "1","nil","345","nil","67890" @@ -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 NUMBER) 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 NUMBER) 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 NUMBER) 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 NUMBER) AS x UNION ALL SELECT -9223372036850000000) ]], { -- @@ -2928,7 +2928,7 @@ 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 integer" -- }) 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..0a2c42173 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 or varbinary" -- }) @@ -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 or varbinary" -- }) @@ -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 varbinary" -- }) @@ -554,7 +554,7 @@ test:do_test( return test:catchsql("SELECT position('x', x'78c3a4e282ac79');") end, { -- - 1, "Inconsistent types: expected text got varbinary" + 1, "Type mismatch: can not convert varbinary to text" -- }) @@ -564,7 +564,7 @@ test:do_test( return test:catchsql "SELECT position('y', x'78c3a4e282ac79');" end, { -- - 1, "Inconsistent types: expected text got varbinary" + 1, "Type mismatch: can not convert varbinary to text" -- }) @@ -614,7 +614,7 @@ test:do_test( return test:catchsql "SELECT position(x'79', 'xä€y');" end, { -- - 1, "Inconsistent types: expected varbinary got text" + 1, "Type mismatch: can not convert xä€y to varbinary" -- }) @@ -624,7 +624,7 @@ test:do_test( return test:catchsql "SELECT position(x'a4', 'xä€y');" end, { -- - 1, "Inconsistent types: expected varbinary got text" + 1, "Type mismatch: can not convert xä€y to varbinary" -- }) @@ -634,7 +634,7 @@ test:do_test( return test:catchsql "SELECT position('y', x'78c3a4e282ac79');" end, { -- - 1, "Inconsistent types: expected text got varbinary" + 1, "Type mismatch: can not convert varbinary to text" -- }) diff --git a/test/sql-tap/tkt2942.test.lua b/test/sql-tap/tkt2942.test.lua index f83d30c2b..cac833472 100755 --- a/test/sql-tap/tkt2942.test.lua +++ b/test/sql-tap/tkt2942.test.lua @@ -40,7 +40,7 @@ test:do_execsql_test( insert into t1 values (2, 1); insert into t1 values (3, 3); insert into t1 values (4, 4); - SELECT group_concat("num") FROM (SELECT "num" FROM t1 ORDER BY "num" DESC); + SELECT group_concat(CAST("num" AS STRING)) FROM (SELECT "num" FROM t1 ORDER BY "num" DESC); ]], { -- "4,3,2,1" @@ -50,7 +50,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt2942.2", [[ - SELECT group_concat("num") FROM (SELECT "num" FROM t1 ORDER BY "num"); + SELECT group_concat(CAST("num" AS STRING)) FROM (SELECT "num" FROM t1 ORDER BY "num"); ]], { -- "1,2,3,4" @@ -60,7 +60,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt2942.3", [[ - SELECT group_concat("num") FROM (SELECT "num" FROM t1); + SELECT group_concat(CAST("num" AS STRING)) FROM (SELECT "num" FROM t1); ]], { -- "2,1,3,4" @@ -70,7 +70,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt2942.4", [[ - SELECT group_concat("num") FROM (SELECT "num" FROM t1 ORDER BY id DESC); + SELECT group_concat(CAST("num" AS STRING)) FROM (SELECT "num" FROM t1 ORDER BY id DESC); ]], { -- "4,3,1,2" diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua index 60dfc927a..19ad171cb 100755 --- a/test/sql-tap/view.test.lua +++ b/test/sql-tap/view.test.lua @@ -1317,7 +1317,7 @@ test:do_execsql_test( a(t) AS ( SELECT group_concat( substr('a', 1+least(iter/7,4), 1), '') FROM m2 GROUP BY cy ) - SELECT group_concat(trim(t),x'0a') FROM a; + SELECT group_concat(trim(t),CAST(x'0a' AS STRING)) FROM a; SELECT * FROM v; ]], { -- diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua index 75935108c..21d18b9a8 100755 --- a/test/sql-tap/with1.test.lua +++ b/test/sql-tap/with1.test.lua @@ -550,7 +550,7 @@ test:do_execsql_test("8.1-mandelbrot", [[ SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') FROM m2 GROUP BY cy ) - SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a; + SELECT group_concat(TRIM(TRAILING FROM t),CAST(x'0a' AS STRING)) FROM a; ]], { -- <8.1-mandelbrot> [[ ....# diff --git a/test/sql/boolean.result b/test/sql/boolean.result index 51ec5820b..9ccef44bb 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: COLUMN_1 - | type: string - | rows: - | - ['false'] - | - ['true'] - | - [null] - | - [null] + | - null + | - 'Type mismatch: can not convert FALSE to string' | ... SELECT upper(a) FROM t0; | --- - | - metadata: - | - name: COLUMN_1 - | 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: COLUMN_1 - | type: integer - | rows: - | - [5] - | - [4] - | - [null] - | - [null] + | - null + | - 'Type mismatch: can not convert FALSE to string or varbinary' | ... SELECT typeof(a) FROM t0; | --- @@ -377,11 +359,8 @@ SELECT TOTAL(a) FROM t0; | ... SELECT GROUP_CONCAT(a, ' +++ ') FROM t0; | --- - | - metadata: - | - name: COLUMN_1 - | type: string - | rows: - | - ['FALSE +++ TRUE'] + | - null + | - 'Type mismatch: can not convert FALSE to string or varbinary' | ... -- Check BOOLEAN as binding parameter. diff --git a/test/sql/prepared.result b/test/sql/prepared.result index 0db2cc03f..45933f4af 100644 --- a/test/sql/prepared.result +++ b/test/sql/prepared.result @@ -471,7 +471,7 @@ s = prepare([[WITH RECURSIVE \ a(t) AS ( \ SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') \ FROM m2 GROUP BY cy) \ - SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;]]) + SELECT group_concat(TRIM(TRAILING FROM t),CAST(x'0a' AS STRING)) FROM a;]]) | --- | ... diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua index d8e8a44cb..7df3857ee 100644 --- a/test/sql/prepared.test.lua +++ b/test/sql/prepared.test.lua @@ -176,7 +176,7 @@ s = prepare([[WITH RECURSIVE \ a(t) AS ( \ SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') \ FROM m2 GROUP BY cy) \ - SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;]]) + SELECT group_concat(TRIM(TRAILING FROM t),CAST(x'0a' AS STRING)) FROM a;]]) res = execute(s.stmt_id) res.metadata diff --git a/test/sql/types.result b/test/sql/types.result index 442245186..262b54605 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -215,25 +215,22 @@ box.execute("INSERT INTO t1 VALUES (randomblob(5));") 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: COLUMN_1 - 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: COLUMN_1 - type: integer - rows: - - [null] +- null +- 'Type mismatch: can not convert 1 to string' ... box.space.T1:drop() --- @@ -818,11 +812,8 @@ box.execute("SELECT count(i) FROM t;") ... box.execute("SELECT group_concat(i) FROM t;") --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['1,-1,18446744073709551613'] +- null +- 'Type mismatch: can not convert 1 to string or varbinary' ... box.execute("DELETE FROM t WHERE i < 18446744073709551613;") --- @@ -830,19 +821,13 @@ box.execute("DELETE FROM t WHERE i < 18446744073709551613;") ... box.execute("SELECT lower(i) FROM t;") --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['18446744073709551613'] +- null +- 'Type mismatch: can not convert 18446744073709551613 to string' ... box.execute("SELECT upper(i) FROM t;") --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['18446744073709551613'] +- null +- 'Type mismatch: can not convert 18446744073709551613 to string' ... box.execute("SELECT abs(i) FROM t;") --- @@ -1312,17 +1297,17 @@ box.execute("SELECT group_concat(v) FROM t;") box.execute("SELECT lower(v) FROM t;") --- - null -- 'Inconsistent types: expected text got varbinary' +- 'Type mismatch: can not convert varbinary to string' ... box.execute("SELECT upper(v) FROM t;") --- - null -- 'Inconsistent types: expected text got varbinary' +- 'Type mismatch: can not convert varbinary to string' ... 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;") --- @@ -1871,33 +1856,18 @@ box.execute("SELECT count(d) FROM t;") ... box.execute("SELECT group_concat(d) FROM t;") --- -- metadata: - - name: COLUMN_1 - type: string - rows: - - ['10.0,-2.0,3.3,1.8e+19'] +- null +- 'Type mismatch: can not convert 10.0 to string or varbinary' ... box.execute("SELECT lower(d) FROM t;") --- -- metadata: - - name: COLUMN_1 - 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: COLUMN_1 - 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;") --- @@ -2795,3 +2765,2453 @@ box.execute([[DROP TABLE ts;]]) --- - row_count: 1 ... +-- Make sure the function argument types are checked. +box.execute([[SELECT abs(-1);]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1] +... +box.execute([[SELECT abs(1);]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1] +... +box.execute([[SELECT abs(1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1.5] +... +box.execute([[SELECT abs(-1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1.5] +... +box.execute([[SELECT abs(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to number' +... +box.execute([[SELECT abs('a');]]) +--- +- null +- 'Type mismatch: can not convert a to number' +... +box.execute([[SELECT abs(X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to number' +... +box.execute([[SELECT abs(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [null] +... +box.execute([[SELECT char(-1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['�'] +... +box.execute([[SELECT char(1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ["\x01"] +... +box.execute([[SELECT char(1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ["\x01"] +... +box.execute([[SELECT char(-1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['�'] +... +box.execute([[SELECT char(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to integer' +... +box.execute([[SELECT char('a');]]) +--- +- null +- 'Type mismatch: can not convert a to integer' +... +box.execute([[SELECT char(X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to integer' +... +box.execute([[SELECT char(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [''] +... +box.execute([[SELECT character_length(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT character_length(1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT character_length(1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT character_length(-1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT character_length(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT character_length('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT character_length(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT character_length(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT char_length(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT char_length(1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT char_length(1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT char_length(-1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT char_length(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT char_length('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT char_length(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT char_length(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT coalesce(-1, -1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1] +... +box.execute([[SELECT coalesce(1, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1] +... +box.execute([[SELECT coalesce(1.5, 1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1.5] +... +box.execute([[SELECT coalesce(-1.5, -1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1.5] +... +box.execute([[SELECT coalesce(true, true);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [true] +... +box.execute([[SELECT coalesce('a', 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['a'] +... +box.execute([[SELECT coalesce(X'33', X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['3'] +... +box.execute([[SELECT coalesce(NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT coalesce(1, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1] +... +box.execute([[SELECT coalesce(NULL, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1] +... +box.execute([[SELECT greatest(-1, -1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1] +... +box.execute([[SELECT greatest(1, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1] +... +box.execute([[SELECT greatest(1.5, 1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1.5] +... +box.execute([[SELECT greatest(-1.5, -1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1.5] +... +box.execute([[SELECT greatest(true, true);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [true] +... +box.execute([[SELECT greatest('a', 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['a'] +... +box.execute([[SELECT greatest(X'33', X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['3'] +... +box.execute([[SELECT greatest(NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT greatest(NULL, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT greatest(1, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT hex(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT hex(1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT hex(1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT hex(-1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT hex(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT hex('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['61'] +... +box.execute([[SELECT hex(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['33'] +... +box.execute([[SELECT hex(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT ifnull(-1, -1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [-1] +... +box.execute([[SELECT ifnull(1, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT ifnull(1.5, 1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1.5] +... +box.execute([[SELECT ifnull(-1.5, -1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [-1.5] +... +box.execute([[SELECT ifnull(true, true);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [true] +... +box.execute([[SELECT ifnull('a', 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - ['a'] +... +box.execute([[SELECT ifnull(X'33', X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - ['3'] +... +box.execute([[SELECT ifnull(NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT ifnull(NULL, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT ifnull(1, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT least(-1, -1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1] +... +box.execute([[SELECT least(1, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1] +... +box.execute([[SELECT least(1.5, 1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1.5] +... +box.execute([[SELECT least(-1.5, -1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1.5] +... +box.execute([[SELECT least(true, true);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [true] +... +box.execute([[SELECT least('a', 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['a'] +... +box.execute([[SELECT least(X'33', X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['3'] +... +box.execute([[SELECT least(NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT least(NULL, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT least(1, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT length(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT length(1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT length(1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT length(-1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT length(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT length('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT length(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT length(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT likelihood(-1, -1);]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likelihood(1, 1);]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likelihood(1.5, 1.5);]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likelihood(-1.5, -1.5);]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likelihood(true, true);]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likelihood('a', 'a');]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likelihood(X'33', X'33');]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likelihood(NULL, NULL);]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likelihood(NULL, 1);]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likelihood(1, NULL);]]) +--- +- null +- Illegal parameters, second argument to likelihood() must be a constant between 0.0 + and 1.0 +... +box.execute([[SELECT likely(-1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [-1] +... +box.execute([[SELECT likely(1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT likely(1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: double + rows: + - [1.5] +... +box.execute([[SELECT likely(-1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: double + rows: + - [-1.5] +... +box.execute([[SELECT likely(true);]]) +--- +- metadata: + - name: COLUMN_1 + type: boolean + rows: + - [true] +... +box.execute([[SELECT likely('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['a'] +... +box.execute([[SELECT likely(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - ['3'] +... +box.execute([[SELECT likely(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT lower(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string' +... +box.execute([[SELECT lower(1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string' +... +box.execute([[SELECT lower(1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string' +... +box.execute([[SELECT lower(-1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string' +... +box.execute([[SELECT lower(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string' +... +box.execute([[SELECT lower('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['a'] +... +box.execute([[SELECT lower(X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to string' +... +box.execute([[SELECT lower(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT nullif(-1, -1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT nullif(1, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT nullif(1.5, 1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT nullif(-1.5, -1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT nullif(true, true);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT nullif('a', 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT nullif(X'33', X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT nullif(NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT nullif(NULL, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT nullif(1, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1] +... +box.execute([[SELECT position(-1, -1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT position(1, 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT position(1.5, 1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT position(-1.5, -1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT position(true, true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT position('a', 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT position('a', 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to text' +... +box.execute([[SELECT position(1, 'a');]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT position(X'33', X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT position(X'33', 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to varbinary' +... +box.execute([[SELECT position(NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT position(NULL, 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT position('a', NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT position(NULL, 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT position(1, NULL);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT printf(-1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['-1'] +... +box.execute([[SELECT printf(1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['1'] +... +box.execute([[SELECT printf(1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['1.5'] +... +box.execute([[SELECT printf(-1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['-1.5'] +... +box.execute([[SELECT printf(true);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['TRUE'] +... +box.execute([[SELECT printf('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['a'] +... +box.execute([[SELECT printf(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['3'] +... +box.execute([[SELECT printf(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT quote(-1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [-1] +... +box.execute([[SELECT quote(1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [1] +... +box.execute([[SELECT quote(1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['1.5'] +... +box.execute([[SELECT quote(-1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['-1.5'] +... +box.execute([[SELECT quote(true);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['TRUE'] +... +box.execute([[SELECT quote('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['''a'''] +... +box.execute([[SELECT quote(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['X''33'''] +... +box.execute([[SELECT quote(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['NULL'] +... +box.execute([[SELECT randomblob(-1);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - [null] +... +box.execute([[SELECT randomblob(0);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - [null] +... +box.execute([[SELECT randomblob(0.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - [null] +... +box.execute([[SELECT randomblob(-0.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - [null] +... +box.execute([[SELECT randomblob(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to integer' +... +box.execute([[SELECT randomblob('a');]]) +--- +- null +- 'Type mismatch: can not convert a to integer' +... +box.execute([[SELECT randomblob(X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to integer' +... +box.execute([[SELECT randomblob(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - [null] +... +box.execute([[SELECT replace(-1, -1, -1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT replace(1, 1, 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT replace(1.5, 1.5, 1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT replace(-1.5, -1.5, -1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT replace(true, true, true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT replace('a', 'a', 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['a'] +... +box.execute([[SELECT replace('a', 'a', 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to text' +... +box.execute([[SELECT replace('a', 1, 'a');]]) +--- +- null +- 'Type mismatch: can not convert 1 to text' +... +box.execute([[SELECT replace(1, 'a', 'a');]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT replace('a', 'a', X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to text' +... +box.execute([[SELECT replace('a', X'33', 'a');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to text' +... +box.execute([[SELECT replace(X'33', 'a', X'33');]]) +--- +- null +- 'Type mismatch: can not convert a to varbinary' +... +box.execute([[SELECT replace(X'33', X'33', 'a');]]) +--- +- null +- 'Type mismatch: can not convert a to varbinary' +... +box.execute([[SELECT replace(X'33', X'33', X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['3'] +... +box.execute([[SELECT replace(X'33', X'33', 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to varbinary' +... +box.execute([[SELECT replace(X'33', 1, X'33');]]) +--- +- null +- 'Type mismatch: can not convert 1 to varbinary' +... +box.execute([[SELECT replace(1, X'33', X'33');]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT replace(NULL, NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT replace(NULL, NULL, 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT replace(NULL, 'a', NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT replace(NULL, 'a', 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT replace('a', NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT replace('a', NULL, 'a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT replace('a', 'a', NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT replace(NULL, 1, 'a');]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT replace('a', NULL, 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to text' +... +box.execute([[SELECT replace(1, 'a', NULL);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT round(-1, -1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [-1] +... +box.execute([[SELECT round(1, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT round(1.5, 1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1.5] +... +box.execute([[SELECT round(-1.5, -1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [-2] +... +box.execute([[SELECT round(1.5, true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to integer' +... +box.execute([[SELECT round(true, 1.5);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to numeric' +... +box.execute([[SELECT round(true, true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to numeric' +... +box.execute([[SELECT round('a', 'a');]]) +--- +- null +- 'Type mismatch: can not convert a to numeric' +... +box.execute([[SELECT round(X'33', X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to numeric' +... +box.execute([[SELECT round(NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT round(NULL, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT round(1, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT soundex(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to text' +... +box.execute([[SELECT soundex(1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to text' +... +box.execute([[SELECT soundex(1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to text' +... +box.execute([[SELECT soundex(-1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to text' +... +box.execute([[SELECT soundex(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to text' +... +box.execute([[SELECT soundex('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['A000'] +... +box.execute([[SELECT soundex(X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to text' +... +box.execute([[SELECT soundex(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT substr(-1, -1, -1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT substr(1, 1, 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT substr(1.5, 1.5, 1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT substr(true, true, true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT substr('a', 'a', 'a');]]) +--- +- null +- 'Type mismatch: can not convert a to integer' +... +box.execute([[SELECT substr('a', 'a', 1);]]) +--- +- null +- 'Type mismatch: can not convert a to integer' +... +box.execute([[SELECT substr('a', 1, 'a');]]) +--- +- null +- 'Type mismatch: can not convert a to integer' +... +box.execute([[SELECT substr('a', 1, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['a'] +... +box.execute([[SELECT substr(X'33', X'33', X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to integer' +... +box.execute([[SELECT substr(X'33', X'33', 1);]]) +--- +- null +- 'Type mismatch: can not convert varbinary to integer' +... +box.execute([[SELECT substr(X'33', 1, X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to integer' +... +box.execute([[SELECT substr(X'33', 1, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['3'] +... +box.execute([[SELECT substr(1, X'33', 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT substr(1, 1, X'33');]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT substr(NULL, NULL, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT substr(NULL, 1, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT substr('a', NULL, 1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT substr('a', 1, NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT substr(NULL, '1', 1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to integer' +... +box.execute([[SELECT substr('a', NULL, '1');]]) +--- +- null +- 'Type mismatch: can not convert 1 to integer' +... +box.execute([[SELECT substr(1, 1, NULL);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT typeof(-1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['integer'] +... +box.execute([[SELECT typeof(1);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['integer'] +... +box.execute([[SELECT typeof(1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['double'] +... +box.execute([[SELECT typeof(-1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['double'] +... +box.execute([[SELECT typeof(true);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['boolean'] +... +box.execute([[SELECT typeof('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['string'] +... +box.execute([[SELECT typeof(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['varbinary'] +... +box.execute([[SELECT typeof(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['boolean'] +... +box.execute([[SELECT unicode(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT unicode(1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT unicode(1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT unicode(-1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT unicode(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT unicode('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [97] +... +box.execute([[SELECT unicode(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [51] +... +box.execute([[SELECT unicode(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT unlikely(-1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [-1] +... +box.execute([[SELECT unlikely(1);]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT unlikely(1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: double + rows: + - [1.5] +... +box.execute([[SELECT unlikely(-1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: double + rows: + - [-1.5] +... +box.execute([[SELECT unlikely(true);]]) +--- +- metadata: + - name: COLUMN_1 + type: boolean + rows: + - [true] +... +box.execute([[SELECT unlikely('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['a'] +... +box.execute([[SELECT unlikely(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - ['3'] +... +box.execute([[SELECT unlikely(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT upper(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string' +... +box.execute([[SELECT upper(1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string' +... +box.execute([[SELECT upper(1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string' +... +box.execute([[SELECT upper(-1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string' +... +box.execute([[SELECT upper(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string' +... +box.execute([[SELECT upper('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['A'] +... +box.execute([[SELECT upper(X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to string' +... +box.execute([[SELECT upper(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT zeroblob(-1);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - [''] +... +box.execute([[SELECT zeroblob(1);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - ["\0"] +... +box.execute([[SELECT zeroblob(1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - ["\0"] +... +box.execute([[SELECT zeroblob(-1.5);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - [''] +... +box.execute([[SELECT zeroblob(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to integer' +... +box.execute([[SELECT zeroblob('a');]]) +--- +- null +- 'Type mismatch: can not convert a to integer' +... +box.execute([[SELECT zeroblob(X'33');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to integer' +... +box.execute([[SELECT zeroblob(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: varbinary + rows: + - [null] +... +box.execute([[SELECT trim(-1);]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT trim(1);]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT trim(1.5);]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT trim(-1.5);]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT trim(true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT trim('a');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['a'] +... +box.execute([[SELECT trim(X'33');]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['3'] +... +box.execute([[SELECT trim(NULL);]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT -1 like -1;]]) +--- +- null +- 'Type mismatch: can not convert -1 to string' +... +box.execute([[SELECT 1 like 1;]]) +--- +- null +- 'Type mismatch: can not convert 1 to string' +... +box.execute([[SELECT 1.5 like 1.5;]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string' +... +box.execute([[SELECT true like true;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string' +... +box.execute([[SELECT 'a' like 'a';]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [true] +... +box.execute([[SELECT 'a' like 1;]]) +--- +- null +- 'Type mismatch: can not convert 1 to string' +... +box.execute([[SELECT 1 like 'a';]]) +--- +- null +- 'Type mismatch: can not convert 1 to string' +... +box.execute([[SELECT X'33' like X'33';]]) +--- +- null +- 'Type mismatch: can not convert varbinary to string' +... +box.execute([[SELECT NULL like NULL;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT 'a' like NULL;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT NULL like 'a';]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [null] +... +box.execute([[SELECT 1 like NULL;]]) +--- +- null +- 'Type mismatch: can not convert 1 to string' +... +box.execute([[SELECT NULL like 1;]]) +--- +- null +- 'Type mismatch: can not convert 1 to string' +... +box.execute([[CREATE TABLE t (i INTEGER PRIMARY KEY, u UNSIGNED, d DOUBLE, o DOUBLE, b BOOLEAN, s STRING, v VARBINARY, n SCALAR);]]) +--- +- row_count: 1 +... +box.execute([[INSERT INTO t VALUES (-1, 1, 1.5, -1.5, true, 'a', X'33', NULL);]]) +--- +- row_count: 1 +... +box.execute([[SELECT avg(i) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [-1] +... +box.execute([[SELECT avg(u) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1] +... +box.execute([[SELECT avg(d) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1.5] +... +box.execute([[SELECT avg(0) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [0] +... +box.execute([[SELECT avg(b) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to number' +... +box.execute([[SELECT avg(s) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert a to number' +... +box.execute([[SELECT avg(v) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert varbinary to number' +... +box.execute([[SELECT avg(n) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [null] +... +box.execute([[SELECT count(i) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT count(u) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT count(d) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT count(0) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT count(b) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT count(s) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT count(v) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [1] +... +box.execute([[SELECT count(n) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: integer + rows: + - [0] +... +box.execute([[SELECT group_concat(i) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT group_concat(u) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT group_concat(d) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT group_concat(o) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT group_concat(b) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT group_concat(s) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['a'] +... +box.execute([[SELECT group_concat(v) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['3'] +... +box.execute([[SELECT group_concat(n) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT group_concat(i, i) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT group_concat(u, u) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert 1 to string or varbinary' +... +box.execute([[SELECT group_concat(d, d) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert 1.5 to string or varbinary' +... +box.execute([[SELECT group_concat(o, o) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert -1.5 to string or varbinary' +... +box.execute([[SELECT group_concat(b, b) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string or varbinary' +... +box.execute([[SELECT group_concat(s, i) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert -1 to text' +... +box.execute([[SELECT group_concat(s, s) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - ['a'] +... +box.execute([[SELECT group_concat(s, v) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert varbinary to text' +... +box.execute([[SELECT group_concat(v, s) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert a to varbinary' +... +box.execute([[SELECT group_concat(n, n) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: string + rows: + - [null] +... +box.execute([[SELECT group_concat(n, i) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT group_concat(i, n) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert -1 to string or varbinary' +... +box.execute([[SELECT max(i) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1] +... +box.execute([[SELECT max(u) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1] +... +box.execute([[SELECT max(d) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1.5] +... +box.execute([[SELECT max(o) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1.5] +... +box.execute([[SELECT max(b) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [true] +... +box.execute([[SELECT max(s) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['a'] +... +box.execute([[SELECT max(v) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['3'] +... +box.execute([[SELECT max(n) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT min(i) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1] +... +box.execute([[SELECT min(u) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1] +... +box.execute([[SELECT min(d) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [1.5] +... +box.execute([[SELECT min(o) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [-1.5] +... +box.execute([[SELECT min(b) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [true] +... +box.execute([[SELECT min(s) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['a'] +... +box.execute([[SELECT min(v) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - ['3'] +... +box.execute([[SELECT min(n) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: scalar + rows: + - [null] +... +box.execute([[SELECT sum(i) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [-1] +... +box.execute([[SELECT sum(u) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1] +... +box.execute([[SELECT sum(d) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1.5] +... +box.execute([[SELECT sum(o) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [-1.5] +... +box.execute([[SELECT sum(b) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to number' +... +box.execute([[SELECT sum(s) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert a to number' +... +box.execute([[SELECT sum(v) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert varbinary to number' +... +box.execute([[SELECT sum(n) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [null] +... +box.execute([[SELECT total(i) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [-1] +... +box.execute([[SELECT total(u) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1] +... +box.execute([[SELECT total(d) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [1.5] +... +box.execute([[SELECT total(o) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [-1.5] +... +box.execute([[SELECT total(b) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to number' +... +box.execute([[SELECT total(s) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert a to number' +... +box.execute([[SELECT total(v) FROM t;]]) +--- +- null +- 'Type mismatch: can not convert varbinary to number' +... +box.execute([[SELECT total(n) FROM t;]]) +--- +- metadata: + - name: COLUMN_1 + type: number + rows: + - [0] +... +box.execute([[DROP TABLE t;]]) +--- +- row_count: 1 +... diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index 0270d9f8a..f82223236 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -623,3 +623,406 @@ box.execute([[DROP TABLE tb;]]) box.execute([[DROP TABLE tt;]]) box.execute([[DROP TABLE tv;]]) box.execute([[DROP TABLE ts;]]) + + +-- Make sure the function argument types are checked. +box.execute([[SELECT abs(-1);]]) +box.execute([[SELECT abs(1);]]) +box.execute([[SELECT abs(1.5);]]) +box.execute([[SELECT abs(-1.5);]]) +box.execute([[SELECT abs(true);]]) +box.execute([[SELECT abs('a');]]) +box.execute([[SELECT abs(X'33');]]) +box.execute([[SELECT abs(NULL);]]) + +box.execute([[SELECT char(-1);]]) +box.execute([[SELECT char(1);]]) +box.execute([[SELECT char(1.5);]]) +box.execute([[SELECT char(-1.5);]]) +box.execute([[SELECT char(true);]]) +box.execute([[SELECT char('a');]]) +box.execute([[SELECT char(X'33');]]) +box.execute([[SELECT char(NULL);]]) + +box.execute([[SELECT character_length(-1);]]) +box.execute([[SELECT character_length(1);]]) +box.execute([[SELECT character_length(1.5);]]) +box.execute([[SELECT character_length(-1.5);]]) +box.execute([[SELECT character_length(true);]]) +box.execute([[SELECT character_length('a');]]) +box.execute([[SELECT character_length(X'33');]]) +box.execute([[SELECT character_length(NULL);]]) + +box.execute([[SELECT char_length(-1);]]) +box.execute([[SELECT char_length(1);]]) +box.execute([[SELECT char_length(1.5);]]) +box.execute([[SELECT char_length(-1.5);]]) +box.execute([[SELECT char_length(true);]]) +box.execute([[SELECT char_length('a');]]) +box.execute([[SELECT char_length(X'33');]]) +box.execute([[SELECT char_length(NULL);]]) + +box.execute([[SELECT coalesce(-1, -1);]]) +box.execute([[SELECT coalesce(1, 1);]]) +box.execute([[SELECT coalesce(1.5, 1.5);]]) +box.execute([[SELECT coalesce(-1.5, -1.5);]]) +box.execute([[SELECT coalesce(true, true);]]) +box.execute([[SELECT coalesce('a', 'a');]]) +box.execute([[SELECT coalesce(X'33', X'33');]]) +box.execute([[SELECT coalesce(NULL, NULL);]]) +box.execute([[SELECT coalesce(1, NULL);]]) +box.execute([[SELECT coalesce(NULL, 1);]]) + +box.execute([[SELECT greatest(-1, -1);]]) +box.execute([[SELECT greatest(1, 1);]]) +box.execute([[SELECT greatest(1.5, 1.5);]]) +box.execute([[SELECT greatest(-1.5, -1.5);]]) +box.execute([[SELECT greatest(true, true);]]) +box.execute([[SELECT greatest('a', 'a');]]) +box.execute([[SELECT greatest(X'33', X'33');]]) +box.execute([[SELECT greatest(NULL, NULL);]]) +box.execute([[SELECT greatest(NULL, 1);]]) +box.execute([[SELECT greatest(1, NULL);]]) + +box.execute([[SELECT hex(-1);]]) +box.execute([[SELECT hex(1);]]) +box.execute([[SELECT hex(1.5);]]) +box.execute([[SELECT hex(-1.5);]]) +box.execute([[SELECT hex(true);]]) +box.execute([[SELECT hex('a');]]) +box.execute([[SELECT hex(X'33');]]) +box.execute([[SELECT hex(NULL);]]) + +box.execute([[SELECT ifnull(-1, -1);]]) +box.execute([[SELECT ifnull(1, 1);]]) +box.execute([[SELECT ifnull(1.5, 1.5);]]) +box.execute([[SELECT ifnull(-1.5, -1.5);]]) +box.execute([[SELECT ifnull(true, true);]]) +box.execute([[SELECT ifnull('a', 'a');]]) +box.execute([[SELECT ifnull(X'33', X'33');]]) +box.execute([[SELECT ifnull(NULL, NULL);]]) +box.execute([[SELECT ifnull(NULL, 1);]]) +box.execute([[SELECT ifnull(1, NULL);]]) + +box.execute([[SELECT least(-1, -1);]]) +box.execute([[SELECT least(1, 1);]]) +box.execute([[SELECT least(1.5, 1.5);]]) +box.execute([[SELECT least(-1.5, -1.5);]]) +box.execute([[SELECT least(true, true);]]) +box.execute([[SELECT least('a', 'a');]]) +box.execute([[SELECT least(X'33', X'33');]]) +box.execute([[SELECT least(NULL, NULL);]]) +box.execute([[SELECT least(NULL, 1);]]) +box.execute([[SELECT least(1, NULL);]]) + +box.execute([[SELECT length(-1);]]) +box.execute([[SELECT length(1);]]) +box.execute([[SELECT length(1.5);]]) +box.execute([[SELECT length(-1.5);]]) +box.execute([[SELECT length(true);]]) +box.execute([[SELECT length('a');]]) +box.execute([[SELECT length(X'33');]]) +box.execute([[SELECT length(NULL);]]) + +box.execute([[SELECT likelihood(-1, -1);]]) +box.execute([[SELECT likelihood(1, 1);]]) +box.execute([[SELECT likelihood(1.5, 1.5);]]) +box.execute([[SELECT likelihood(-1.5, -1.5);]]) +box.execute([[SELECT likelihood(true, true);]]) +box.execute([[SELECT likelihood('a', 'a');]]) +box.execute([[SELECT likelihood(X'33', X'33');]]) +box.execute([[SELECT likelihood(NULL, NULL);]]) +box.execute([[SELECT likelihood(NULL, 1);]]) +box.execute([[SELECT likelihood(1, NULL);]]) + +box.execute([[SELECT likely(-1);]]) +box.execute([[SELECT likely(1);]]) +box.execute([[SELECT likely(1.5);]]) +box.execute([[SELECT likely(-1.5);]]) +box.execute([[SELECT likely(true);]]) +box.execute([[SELECT likely('a');]]) +box.execute([[SELECT likely(X'33');]]) +box.execute([[SELECT likely(NULL);]]) + +box.execute([[SELECT lower(-1);]]) +box.execute([[SELECT lower(1);]]) +box.execute([[SELECT lower(1.5);]]) +box.execute([[SELECT lower(-1.5);]]) +box.execute([[SELECT lower(true);]]) +box.execute([[SELECT lower('a');]]) +box.execute([[SELECT lower(X'33');]]) +box.execute([[SELECT lower(NULL);]]) + +box.execute([[SELECT nullif(-1, -1);]]) +box.execute([[SELECT nullif(1, 1);]]) +box.execute([[SELECT nullif(1.5, 1.5);]]) +box.execute([[SELECT nullif(-1.5, -1.5);]]) +box.execute([[SELECT nullif(true, true);]]) +box.execute([[SELECT nullif('a', 'a');]]) +box.execute([[SELECT nullif(X'33', X'33');]]) +box.execute([[SELECT nullif(NULL, NULL);]]) +box.execute([[SELECT nullif(NULL, 1);]]) +box.execute([[SELECT nullif(1, NULL);]]) + +box.execute([[SELECT position(-1, -1);]]) +box.execute([[SELECT position(1, 1);]]) +box.execute([[SELECT position(1.5, 1.5);]]) +box.execute([[SELECT position(-1.5, -1.5);]]) +box.execute([[SELECT position(true, true);]]) +box.execute([[SELECT position('a', 'a');]]) +box.execute([[SELECT position('a', 1);]]) +box.execute([[SELECT position(1, 'a');]]) +box.execute([[SELECT position(X'33', X'33');]]) +box.execute([[SELECT position(X'33', 1);]]) +box.execute([[SELECT position(NULL, NULL);]]) +box.execute([[SELECT position(NULL, 'a');]]) +box.execute([[SELECT position('a', NULL);]]) +box.execute([[SELECT position(NULL, 1);]]) +box.execute([[SELECT position(1, NULL);]]) + +box.execute([[SELECT printf(-1);]]) +box.execute([[SELECT printf(1);]]) +box.execute([[SELECT printf(1.5);]]) +box.execute([[SELECT printf(-1.5);]]) +box.execute([[SELECT printf(true);]]) +box.execute([[SELECT printf('a');]]) +box.execute([[SELECT printf(X'33');]]) +box.execute([[SELECT printf(NULL);]]) + +box.execute([[SELECT quote(-1);]]) +box.execute([[SELECT quote(1);]]) +box.execute([[SELECT quote(1.5);]]) +box.execute([[SELECT quote(-1.5);]]) +box.execute([[SELECT quote(true);]]) +box.execute([[SELECT quote('a');]]) +box.execute([[SELECT quote(X'33');]]) +box.execute([[SELECT quote(NULL);]]) + +box.execute([[SELECT randomblob(-1);]]) +box.execute([[SELECT randomblob(0);]]) +box.execute([[SELECT randomblob(0.5);]]) +box.execute([[SELECT randomblob(-0.5);]]) +box.execute([[SELECT randomblob(true);]]) +box.execute([[SELECT randomblob('a');]]) +box.execute([[SELECT randomblob(X'33');]]) +box.execute([[SELECT randomblob(NULL);]]) + +box.execute([[SELECT replace(-1, -1, -1);]]) +box.execute([[SELECT replace(1, 1, 1);]]) +box.execute([[SELECT replace(1.5, 1.5, 1.5);]]) +box.execute([[SELECT replace(-1.5, -1.5, -1.5);]]) +box.execute([[SELECT replace(true, true, true);]]) +box.execute([[SELECT replace('a', 'a', 'a');]]) +box.execute([[SELECT replace('a', 'a', 1);]]) +box.execute([[SELECT replace('a', 1, 'a');]]) +box.execute([[SELECT replace(1, 'a', 'a');]]) +box.execute([[SELECT replace('a', 'a', X'33');]]) +box.execute([[SELECT replace('a', X'33', 'a');]]) +box.execute([[SELECT replace(X'33', 'a', X'33');]]) +box.execute([[SELECT replace(X'33', X'33', 'a');]]) +box.execute([[SELECT replace(X'33', X'33', X'33');]]) +box.execute([[SELECT replace(X'33', X'33', 1);]]) +box.execute([[SELECT replace(X'33', 1, X'33');]]) +box.execute([[SELECT replace(1, X'33', X'33');]]) +box.execute([[SELECT replace(NULL, NULL, NULL);]]) +box.execute([[SELECT replace(NULL, NULL, 'a');]]) +box.execute([[SELECT replace(NULL, 'a', NULL);]]) +box.execute([[SELECT replace(NULL, 'a', 'a');]]) +box.execute([[SELECT replace('a', NULL, NULL);]]) +box.execute([[SELECT replace('a', NULL, 'a');]]) +box.execute([[SELECT replace('a', 'a', NULL);]]) +box.execute([[SELECT replace(NULL, 1, 'a');]]) +box.execute([[SELECT replace('a', NULL, 1);]]) +box.execute([[SELECT replace(1, 'a', NULL);]]) + +box.execute([[SELECT round(-1, -1);]]) +box.execute([[SELECT round(1, 1);]]) +box.execute([[SELECT round(1.5, 1.5);]]) +box.execute([[SELECT round(-1.5, -1.5);]]) +box.execute([[SELECT round(1.5, true);]]) +box.execute([[SELECT round(true, 1.5);]]) +box.execute([[SELECT round(true, true);]]) +box.execute([[SELECT round('a', 'a');]]) +box.execute([[SELECT round(X'33', X'33');]]) +box.execute([[SELECT round(NULL, NULL);]]) +box.execute([[SELECT round(NULL, 1);]]) +box.execute([[SELECT round(1, NULL);]]) + +box.execute([[SELECT soundex(-1);]]) +box.execute([[SELECT soundex(1);]]) +box.execute([[SELECT soundex(1.5);]]) +box.execute([[SELECT soundex(-1.5);]]) +box.execute([[SELECT soundex(true);]]) +box.execute([[SELECT soundex('a');]]) +box.execute([[SELECT soundex(X'33');]]) +box.execute([[SELECT soundex(NULL);]]) + +box.execute([[SELECT substr(-1, -1, -1);]]) +box.execute([[SELECT substr(1, 1, 1);]]) +box.execute([[SELECT substr(1.5, 1.5, 1.5);]]) +box.execute([[SELECT substr(true, true, true);]]) +box.execute([[SELECT substr('a', 'a', 'a');]]) +box.execute([[SELECT substr('a', 'a', 1);]]) +box.execute([[SELECT substr('a', 1, 'a');]]) +box.execute([[SELECT substr('a', 1, 1);]]) +box.execute([[SELECT substr(X'33', X'33', X'33');]]) +box.execute([[SELECT substr(X'33', X'33', 1);]]) +box.execute([[SELECT substr(X'33', 1, X'33');]]) +box.execute([[SELECT substr(X'33', 1, 1);]]) +box.execute([[SELECT substr(1, X'33', 1);]]) +box.execute([[SELECT substr(1, 1, X'33');]]) +box.execute([[SELECT substr(NULL, NULL, NULL);]]) +box.execute([[SELECT substr(NULL, 1, 1);]]) +box.execute([[SELECT substr('a', NULL, 1);]]) +box.execute([[SELECT substr('a', 1, NULL);]]) +box.execute([[SELECT substr(NULL, '1', 1);]]) +box.execute([[SELECT substr('a', NULL, '1');]]) +box.execute([[SELECT substr(1, 1, NULL);]]) + +box.execute([[SELECT typeof(-1);]]) +box.execute([[SELECT typeof(1);]]) +box.execute([[SELECT typeof(1.5);]]) +box.execute([[SELECT typeof(-1.5);]]) +box.execute([[SELECT typeof(true);]]) +box.execute([[SELECT typeof('a');]]) +box.execute([[SELECT typeof(X'33');]]) +box.execute([[SELECT typeof(NULL);]]) + +box.execute([[SELECT unicode(-1);]]) +box.execute([[SELECT unicode(1);]]) +box.execute([[SELECT unicode(1.5);]]) +box.execute([[SELECT unicode(-1.5);]]) +box.execute([[SELECT unicode(true);]]) +box.execute([[SELECT unicode('a');]]) +box.execute([[SELECT unicode(X'33');]]) +box.execute([[SELECT unicode(NULL);]]) + +box.execute([[SELECT unlikely(-1);]]) +box.execute([[SELECT unlikely(1);]]) +box.execute([[SELECT unlikely(1.5);]]) +box.execute([[SELECT unlikely(-1.5);]]) +box.execute([[SELECT unlikely(true);]]) +box.execute([[SELECT unlikely('a');]]) +box.execute([[SELECT unlikely(X'33');]]) +box.execute([[SELECT unlikely(NULL);]]) + +box.execute([[SELECT upper(-1);]]) +box.execute([[SELECT upper(1);]]) +box.execute([[SELECT upper(1.5);]]) +box.execute([[SELECT upper(-1.5);]]) +box.execute([[SELECT upper(true);]]) +box.execute([[SELECT upper('a');]]) +box.execute([[SELECT upper(X'33');]]) +box.execute([[SELECT upper(NULL);]]) + +box.execute([[SELECT zeroblob(-1);]]) +box.execute([[SELECT zeroblob(1);]]) +box.execute([[SELECT zeroblob(1.5);]]) +box.execute([[SELECT zeroblob(-1.5);]]) +box.execute([[SELECT zeroblob(true);]]) +box.execute([[SELECT zeroblob('a');]]) +box.execute([[SELECT zeroblob(X'33');]]) +box.execute([[SELECT zeroblob(NULL);]]) + +box.execute([[SELECT trim(-1);]]) +box.execute([[SELECT trim(1);]]) +box.execute([[SELECT trim(1.5);]]) +box.execute([[SELECT trim(-1.5);]]) +box.execute([[SELECT trim(true);]]) +box.execute([[SELECT trim('a');]]) +box.execute([[SELECT trim(X'33');]]) +box.execute([[SELECT trim(NULL);]]) + +box.execute([[SELECT -1 like -1;]]) +box.execute([[SELECT 1 like 1;]]) +box.execute([[SELECT 1.5 like 1.5;]]) +box.execute([[SELECT true like true;]]) +box.execute([[SELECT 'a' like 'a';]]) +box.execute([[SELECT 'a' like 1;]]) +box.execute([[SELECT 1 like 'a';]]) +box.execute([[SELECT X'33' like X'33';]]) +box.execute([[SELECT NULL like NULL;]]) +box.execute([[SELECT 'a' like NULL;]]) +box.execute([[SELECT NULL like 'a';]]) +box.execute([[SELECT 1 like NULL;]]) +box.execute([[SELECT NULL like 1;]]) + +box.execute([[CREATE TABLE t (i INTEGER PRIMARY KEY, u UNSIGNED, d DOUBLE, o DOUBLE, b BOOLEAN, s STRING, v VARBINARY, n SCALAR);]]) +box.execute([[INSERT INTO t VALUES (-1, 1, 1.5, -1.5, true, 'a', X'33', NULL);]]) + +box.execute([[SELECT avg(i) FROM t;]]) +box.execute([[SELECT avg(u) FROM t;]]) +box.execute([[SELECT avg(d) FROM t;]]) +box.execute([[SELECT avg(0) FROM t;]]) +box.execute([[SELECT avg(b) FROM t;]]) +box.execute([[SELECT avg(s) FROM t;]]) +box.execute([[SELECT avg(v) FROM t;]]) +box.execute([[SELECT avg(n) FROM t;]]) + +box.execute([[SELECT count(i) FROM t;]]) +box.execute([[SELECT count(u) FROM t;]]) +box.execute([[SELECT count(d) FROM t;]]) +box.execute([[SELECT count(0) FROM t;]]) +box.execute([[SELECT count(b) FROM t;]]) +box.execute([[SELECT count(s) FROM t;]]) +box.execute([[SELECT count(v) FROM t;]]) +box.execute([[SELECT count(n) FROM t;]]) + +box.execute([[SELECT group_concat(i) FROM t;]]) +box.execute([[SELECT group_concat(u) FROM t;]]) +box.execute([[SELECT group_concat(d) FROM t;]]) +box.execute([[SELECT group_concat(o) FROM t;]]) +box.execute([[SELECT group_concat(b) FROM t;]]) +box.execute([[SELECT group_concat(s) FROM t;]]) +box.execute([[SELECT group_concat(v) FROM t;]]) +box.execute([[SELECT group_concat(n) FROM t;]]) +box.execute([[SELECT group_concat(i, i) FROM t;]]) +box.execute([[SELECT group_concat(u, u) FROM t;]]) +box.execute([[SELECT group_concat(d, d) FROM t;]]) +box.execute([[SELECT group_concat(o, o) FROM t;]]) +box.execute([[SELECT group_concat(b, b) FROM t;]]) +box.execute([[SELECT group_concat(s, i) FROM t;]]) +box.execute([[SELECT group_concat(s, s) FROM t;]]) +box.execute([[SELECT group_concat(s, v) FROM t;]]) +box.execute([[SELECT group_concat(v, s) FROM t;]]) +box.execute([[SELECT group_concat(n, n) FROM t;]]) +box.execute([[SELECT group_concat(n, i) FROM t;]]) +box.execute([[SELECT group_concat(i, n) FROM t;]]) + +box.execute([[SELECT max(i) FROM t;]]) +box.execute([[SELECT max(u) FROM t;]]) +box.execute([[SELECT max(d) FROM t;]]) +box.execute([[SELECT max(o) FROM t;]]) +box.execute([[SELECT max(b) FROM t;]]) +box.execute([[SELECT max(s) FROM t;]]) +box.execute([[SELECT max(v) FROM t;]]) +box.execute([[SELECT max(n) FROM t;]]) + +box.execute([[SELECT min(i) FROM t;]]) +box.execute([[SELECT min(u) FROM t;]]) +box.execute([[SELECT min(d) FROM t;]]) +box.execute([[SELECT min(o) FROM t;]]) +box.execute([[SELECT min(b) FROM t;]]) +box.execute([[SELECT min(s) FROM t;]]) +box.execute([[SELECT min(v) FROM t;]]) +box.execute([[SELECT min(n) FROM t;]]) + +box.execute([[SELECT sum(i) FROM t;]]) +box.execute([[SELECT sum(u) FROM t;]]) +box.execute([[SELECT sum(d) FROM t;]]) +box.execute([[SELECT sum(o) FROM t;]]) +box.execute([[SELECT sum(b) FROM t;]]) +box.execute([[SELECT sum(s) FROM t;]]) +box.execute([[SELECT sum(v) FROM t;]]) +box.execute([[SELECT sum(n) FROM t;]]) + +box.execute([[SELECT total(i) FROM t;]]) +box.execute([[SELECT total(u) FROM t;]]) +box.execute([[SELECT total(d) FROM t;]]) +box.execute([[SELECT total(o) FROM t;]]) +box.execute([[SELECT total(b) FROM t;]]) +box.execute([[SELECT total(s) FROM t;]]) +box.execute([[SELECT total(v) FROM t;]]) +box.execute([[SELECT total(n) FROM t;]]) + +box.execute([[DROP TABLE t;]])