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