* [Tarantool-patches] [PATCH v1 01/10] sql: modify signature of TRIM()
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 02/10] sql: rework SQL built-in functions hash table Mergen Imeev via Tarantool-patches
` (9 subsequent siblings)
10 siblings, 0 replies; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
This patch changes the signature of SQL built-in function TRIM(). This
gives us an easier way to check the types of the arguments to this
function. Additionally, these changes fix a bug where using TRIM with
the BOTH, LEADING, or TRAILING keywords would result in a loss of a
collation.
Needed for #6105
Closes #6299
---
src/box/sql/func.c | 80 ++++++-------------
src/box/sql/parse.y | 36 +++++----
.../gh-6299-lost-collation-on-trim.test.lua | 47 +++++++++++
3 files changed, 90 insertions(+), 73 deletions(-)
create mode 100755 test/sql-tap/gh-6299-lost-collation-on-trim.test.lua
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 1551d3ef2..c19a4dcde 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -1476,37 +1476,11 @@ trim_prepare_char_len(struct sql_context *context,
}
/**
- * Normalize args from @a argv input array when it has one arg
- * only.
+ * Normalize args from @a argv input array when it has two args.
*
* Case: TRIM(<str>)
* Call trimming procedure with TRIM_BOTH as the flags and " " as
* the trimming set.
- */
-static void
-trim_func_one_arg(struct sql_context *context, sql_value *arg)
-{
- /* In case of VARBINARY type default trim octet is X'00'. */
- const unsigned char *default_trim;
- if (mem_is_null(arg))
- return;
- if (mem_is_bin(arg))
- default_trim = (const unsigned char *) "\0";
- else
- default_trim = (const unsigned char *) " ";
- const unsigned char *input_str = mem_as_ustr(arg);
- int input_str_sz = mem_len_unsafe(arg);
- uint8_t trim_char_len[1] = { 1 };
- trim_procedure(context, TRIM_BOTH, default_trim, trim_char_len, 1,
- input_str, input_str_sz);
-}
-
-/**
- * Normalize args from @a argv input array when it has two args.
- *
- * Case: TRIM(<character_set> FROM <str>)
- * If user has specified <character_set> only, call trimming
- * procedure with TRIM_BOTH as the flags and that trimming set.
*
* Case: TRIM(LEADING/TRAILING/BOTH FROM <str>)
* If user has specified side keyword only, then call trimming
@@ -1516,32 +1490,29 @@ static void
trim_func_two_args(struct sql_context *context, sql_value *arg1,
sql_value *arg2)
{
- const unsigned char *input_str, *trim_set;
- if ((input_str = mem_as_ustr(arg2)) == NULL)
+ const unsigned char *trim_set;
+ if (mem_is_bin(arg1))
+ trim_set = (const unsigned char *)"\0";
+ else
+ trim_set = (const unsigned char *)" ";
+ const unsigned char *input_str;
+ if ((input_str = mem_as_ustr(arg1)) == NULL)
return;
- int input_str_sz = mem_len_unsafe(arg2);
- if (sql_value_type(arg1) == MP_INT || sql_value_type(arg1) == MP_UINT) {
- uint8_t len_one = 1;
- trim_procedure(context, mem_get_int_unsafe(arg1),
- (const unsigned char *) " ", &len_one, 1,
- input_str, input_str_sz);
- } else if ((trim_set = mem_as_ustr(arg1)) != NULL) {
- int trim_set_sz = mem_len_unsafe(arg1);
- uint8_t *char_len;
- int char_cnt = trim_prepare_char_len(context, trim_set,
- trim_set_sz, &char_len);
- if (char_cnt == -1)
- return;
- trim_procedure(context, TRIM_BOTH, trim_set, char_len, char_cnt,
- input_str, input_str_sz);
- sql_free(char_len);
- }
+ int input_str_sz = mem_len_unsafe(arg1);
+ assert(arg2->type == MEM_TYPE_UINT);
+ uint8_t len_one = 1;
+ trim_procedure(context, arg2->u.u, trim_set,
+ &len_one, 1, input_str, input_str_sz);
}
/**
* Normalize args from @a argv input array when it has three args.
*
+ * Case: TRIM(<character_set> FROM <str>)
+ * If user has specified <character_set> only, call trimming procedure with
+ * TRIM_BOTH as the flags and that trimming set.
+ *
* Case: TRIM(LEADING/TRAILING/BOTH <character_set> FROM <str>)
* If user has specified side keyword and <character_set>, then
* call trimming procedure with that args.
@@ -1550,20 +1521,20 @@ static void
trim_func_three_args(struct sql_context *context, sql_value *arg1,
sql_value *arg2, sql_value *arg3)
{
- assert(sql_value_type(arg1) == MP_INT || sql_value_type(arg1) == MP_UINT);
+ assert(arg2->type == MEM_TYPE_UINT);
const unsigned char *input_str, *trim_set;
- if ((input_str = mem_as_ustr(arg3)) == NULL ||
- (trim_set = mem_as_ustr(arg2)) == NULL)
+ if ((input_str = mem_as_ustr(arg1)) == NULL ||
+ (trim_set = mem_as_ustr(arg3)) == NULL)
return;
- int trim_set_sz = mem_len_unsafe(arg2);
- int input_str_sz = mem_len_unsafe(arg3);
+ int trim_set_sz = mem_len_unsafe(arg3);
+ int input_str_sz = mem_len_unsafe(arg1);
uint8_t *char_len;
int char_cnt = trim_prepare_char_len(context, trim_set, trim_set_sz,
&char_len);
if (char_cnt == -1)
return;
- trim_procedure(context, mem_get_int_unsafe(arg1), trim_set, char_len,
+ trim_procedure(context, arg2->u.u, trim_set, char_len,
char_cnt, input_str, input_str_sz);
sql_free(char_len);
}
@@ -1579,9 +1550,6 @@ static void
trim_func(struct sql_context *context, int argc, sql_value **argv)
{
switch (argc) {
- case 1:
- trim_func_one_arg(context, argv[0]);
- break;
case 2:
trim_func_two_args(context, argv[0], argv[1]);
break;
@@ -1590,7 +1558,7 @@ trim_func(struct sql_context *context, int argc, sql_value **argv)
break;
default:
diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, "TRIM",
- "1 or 2 or 3", argc);
+ "2 or 3", argc);
context->is_aborted = true;
}
}
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index bd041e862..d06f45fd9 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1132,32 +1132,34 @@ expr(A) ::= TRIM(X) LP trim_operands(Y) RP(E). {
%type trim_operands {struct ExprList *}
%destructor trim_operands {sql_expr_list_delete(pParse->db, $$);}
-trim_operands(A) ::= trim_from_clause(F) expr(Y). {
- A = sql_expr_list_append(pParse->db, F, Y.pExpr);
+trim_operands(A) ::= trim_specification(N) expr(Z) FROM expr(Y). {
+ A = sql_expr_list_append(pParse->db, NULL, Y.pExpr);
+ struct Expr *p = sql_expr_new_dequoted(pParse->db, TK_INTEGER,
+ &sqlIntTokens[N]);
+ A = sql_expr_list_append(pParse->db, A, p);
+ A = sql_expr_list_append(pParse->db, A, Z.pExpr);
}
-trim_operands(A) ::= expr(Y). {
+trim_operands(A) ::= trim_specification(N) FROM expr(Y). {
A = sql_expr_list_append(pParse->db, NULL, Y.pExpr);
+ struct Expr *p = sql_expr_new_dequoted(pParse->db, TK_INTEGER,
+ &sqlIntTokens[N]);
+ A = sql_expr_list_append(pParse->db, A, p);
}
-%type trim_from_clause {struct ExprList *}
-%destructor trim_from_clause {sql_expr_list_delete(pParse->db, $$);}
-
-/*
- * The following two rules cover three cases of keyword
- * (LEADING/TRAILING/BOTH) and <trim_character_set> combination.
- * The case when both of them are absent is disallowed.
- */
-trim_from_clause(A) ::= expr(Y) FROM. {
+trim_operands(A) ::= expr(Z) FROM expr(Y). {
A = sql_expr_list_append(pParse->db, NULL, Y.pExpr);
+ struct Expr *p = sql_expr_new_dequoted(pParse->db, TK_INTEGER,
+ &sqlIntTokens[TRIM_BOTH]);
+ A = sql_expr_list_append(pParse->db, A, p);
+ A = sql_expr_list_append(pParse->db, A, Z.pExpr);
}
-trim_from_clause(A) ::= trim_specification(N) expr_optional(Y) FROM. {
+trim_operands(A) ::= expr(Y). {
+ A = sql_expr_list_append(pParse->db, NULL, Y.pExpr);
struct Expr *p = sql_expr_new_dequoted(pParse->db, TK_INTEGER,
- &sqlIntTokens[N]);
- A = sql_expr_list_append(pParse->db, NULL, p);
- if (Y != NULL)
- A = sql_expr_list_append(pParse->db, A, Y);
+ &sqlIntTokens[TRIM_BOTH]);
+ A = sql_expr_list_append(pParse->db, A, p);
}
%type expr_optional {struct Expr *}
diff --git a/test/sql-tap/gh-6299-lost-collation-on-trim.test.lua b/test/sql-tap/gh-6299-lost-collation-on-trim.test.lua
new file mode 100755
index 000000000..1799da839
--- /dev/null
+++ b/test/sql-tap/gh-6299-lost-collation-on-trim.test.lua
@@ -0,0 +1,47 @@
+#!/usr/bin/env tarantool
+local test = require("sqltester")
+test:plan(4)
+
+--
+-- Make sure that collation is not lost when TRIM called with BOTH, LEADING, or
+-- TRAILING keywords specified.
+--
+
+test:execsql[[
+ CREATE TABLE t (i INT PRIMARY KEY, s STRING COLLATE "unicode_ci");
+ INSERT INTO t VALUES (1,'A'), (2,'a');
+]]
+
+test:do_execsql_test(
+ "gh-6299-2",
+ [[
+ SELECT DISTINCT trim(LEADING FROM s) FROM t;
+ ]], {
+ 'A'
+ })
+
+test:do_execsql_test(
+ "gh-6299-3",
+ [[
+ SELECT DISTINCT trim(TRAILING FROM s) FROM t;
+ ]], {
+ 'A'
+ })
+
+test:do_execsql_test(
+ "gh-6299-4",
+ [[
+ SELECT DISTINCT trim(BOTH FROM s) FROM t;
+ ]], {
+ 'A'
+ })
+
+test:do_execsql_test(
+ "gh-6299-1",
+ [[
+ SELECT DISTINCT trim(s) FROM t;
+ ]], {
+ 'A'
+ })
+
+test:finish_test()
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* [Tarantool-patches] [PATCH v1 02/10] sql: rework SQL built-in functions hash table
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 01/10] sql: modify signature of TRIM() Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-16 13:53 ` Vladimir Davydov via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 03/10] sql: check number of arguments during parsing Mergen Imeev via Tarantool-patches
` (8 subsequent siblings)
10 siblings, 1 reply; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
Currently, the SQL built-in hash table contains one already defined
implementation for each SQL built-in function. This is rather
inconvenient because some built-in SQL functions can accept arguments of
more than one type, and the type of the result can depend on the types
of the arguments. In addition, the number of arguments can be variable
for some built-in SQL functions. For these reasons, we are forced to
check the number of arguments and their type at runtime. To make it
possible to check types of arguments and their number during parsing,
the hash table has been modified so that functions can now have more
than one implementation.
Part of #6105
---
src/box/sql/func.c | 941 ++++++++++--------------------------------
src/box/sql/resolve.c | 44 +-
src/box/sql/sqlInt.h | 9 +
3 files changed, 255 insertions(+), 739 deletions(-)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index c19a4dcde..7d53b1646 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -51,6 +51,7 @@
#include "assoc.h"
static struct mh_strnptr_t *built_in_functions = NULL;
+static struct func_sql_builtin **functions;
static const unsigned char *
mem_as_ustr(struct Mem *mem)
@@ -1907,689 +1908,172 @@ sql_builtin_stub(sql_context *ctx, int argc, sql_value **argv)
}
/**
- * A sequence of SQL builtins definitions in
- * lexicographic order.
+ * A structure that defines the relationship between a function and its
+ * implementations.
*/
-static struct {
+ struct sql_func_dictionary {
+ /** Name of the function. */
+ const char *name;
+ /** The minimum number of arguments for all implementations. */
+ int32_t argc_min;
+ /** The maximum number of arguments for all implementations. */
+ int32_t argc_max;
+ /** Additional informations about the function. */
+ uint32_t flags;
/**
- * Name is used to find corresponding entry in array
- * sql_builtins applying binary search.
+ * True if the function is deterministic (can give only one result with
+ * the given arguments).
*/
+ bool is_deterministic;
+ /** Count of function's implementations. */
+ uint32_t count;
+ /** Array of function implementations. */
+ struct func_sql_builtin **functions;
+};
+
+static struct sql_func_dictionary dictionaries[] = {
+ {"ABS", 1, 1, 0, true, 0, NULL},
+ {"AVG", 1, 1, SQL_FUNC_AGG, false, 0, NULL},
+ {"CHAR", 0, SQL_MAX_FUNCTION_ARG, 0, true, 0, NULL},
+ {"CHARACTER_LENGTH", 1, 1, 0, true, 0, NULL},
+ {"CHAR_LENGTH", 1, 1, 0, true, 0, NULL},
+ {"COALESCE", 2, SQL_MAX_FUNCTION_ARG, SQL_FUNC_COALESCE, true, 0, NULL},
+ {"COUNT", 0, 1, SQL_FUNC_AGG, false, 0, NULL},
+ {"GREATEST", 2, SQL_MAX_FUNCTION_ARG, SQL_FUNC_MAX | SQL_FUNC_NEEDCOLL,
+ true, 0, NULL},
+ {"GROUP_CONCAT", 1, 2, SQL_FUNC_AGG, false, 0, NULL},
+ {"HEX", 1, 1, 0, true, 0, NULL},
+ {"IFNULL", 2, 2, SQL_FUNC_COALESCE, true, 0, NULL},
+ {"LEAST", 2, SQL_MAX_FUNCTION_ARG, SQL_FUNC_MIN | SQL_FUNC_NEEDCOLL,
+ true, 0, NULL},
+ {"LENGTH", 1, 1, SQL_FUNC_LENGTH, true, 0, NULL},
+ {"LIKE", 2, 3, SQL_FUNC_LIKE | SQL_FUNC_NEEDCOLL, true, 0, NULL},
+ {"LIKELIHOOD", 2, 2, SQL_FUNC_UNLIKELY, true, 0, NULL},
+ {"LIKELY", 1, 1, SQL_FUNC_UNLIKELY, true, 0, NULL},
+ {"LOWER", 1, 1, SQL_FUNC_DERIVEDCOLL | SQL_FUNC_NEEDCOLL, true, 0,
+ NULL},
+ {"MAX", 1, 1, SQL_FUNC_MAX | SQL_FUNC_AGG | SQL_FUNC_NEEDCOLL, false, 0,
+ NULL},
+ {"MIN", 1, 1, SQL_FUNC_MIN | SQL_FUNC_AGG | SQL_FUNC_NEEDCOLL, false, 0,
+ NULL},
+ {"NULLIF", 2, 2, SQL_FUNC_NEEDCOLL, true, 0, NULL},
+ {"POSITION", 2, 2, SQL_FUNC_NEEDCOLL, true, 0, NULL},
+ {"PRINTF", 0, SQL_MAX_FUNCTION_ARG, 0, true, 0, NULL},
+ {"QUOTE", 1, 1, 0, true, 0, NULL},
+ {"RANDOM", 0, 0, 0, false, 0, NULL},
+ {"RANDOMBLOB", 1, 1, 0, false, 0, NULL},
+ {"REPLACE", 3, 3, SQL_FUNC_DERIVEDCOLL, true, 0, NULL},
+ {"ROUND", 1, 2, 0, true, 0, NULL},
+ {"ROW_COUNT", 0, 0, 0, true, 0, NULL},
+ {"SOUNDEX", 1, 1, 0, true, 0, NULL},
+ {"SUBSTR", 2, 3, SQL_FUNC_DERIVEDCOLL, true, 0, NULL},
+ {"SUM", 1, 1, SQL_FUNC_AGG, false, 0, NULL},
+ {"TOTAL", 1, 1, SQL_FUNC_AGG, false, 0, NULL},
+ {"TRIM", 2, 3, SQL_FUNC_DERIVEDCOLL, true, 0, NULL},
+ {"TYPEOF", 1, 1, SQL_FUNC_TYPEOF, true, 0, NULL},
+ {"UNICODE", 1, 1, 0, true, 0, NULL},
+ {"UNLIKELY", 1, 1, SQL_FUNC_UNLIKELY, true, 0, NULL},
+ {"UPPER", 1, 1, SQL_FUNC_DERIVEDCOLL | SQL_FUNC_NEEDCOLL, true, 0,
+ NULL},
+ {"UUID", 0, 1, 0, false, 0, NULL},
+ {"VERSION", 0, 0, 0, true, 0, NULL},
+ {"ZEROBLOB", 1, 1, 0, true, 0, NULL},
+};
+
+/**
+ * The structure that defines the implementation of the function. These
+ * definitions are used during initialization to create all descibed
+ * implementations of all built-in SQL functions.
+ */
+struct sql_func_definition {
+ /** Name of the function. */
const char *name;
- /** Members below are related to struct func_sql_builtin. */
- uint16_t flags;
+ /** The number of arguments of the implementation. */
+ int32_t argc;
+ /**
+ * Types of implementation arguments. Only the first three arguments are
+ * described, but this should be sufficient, since all built-in SQL
+ * functions either have up to three arguments, or the number of their
+ * arguments is not limited here (but limited globally). If a function
+ * has an unlimited number of arguments, all arguments are of the same
+ * type.
+ */
+ enum field_type argt[3];
+ /** Type of the result of the implementation. */
+ enum field_type result;
+ /** Call implementation with given arguments. */
void (*call)(sql_context *ctx, int argc, sql_value **argv);
+ /** Call finalization function for this implementation. */
void (*finalize)(sql_context *ctx);
- /** Members below are related to struct func_def. */
- bool is_deterministic;
- int param_count;
- enum field_type returns;
- enum func_aggregate aggregate;
- bool export_to_sql;
-} sql_builtins[] = {
- {.name = "ABS",
- .param_count = 1,
- .returns = FIELD_TYPE_NUMBER,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = absFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "AVG",
- .param_count = 1,
- .returns = FIELD_TYPE_NUMBER,
- .is_deterministic = false,
- .aggregate = FUNC_AGGREGATE_GROUP,
- .flags = 0,
- .call = sum_step,
- .finalize = avgFinalize,
- .export_to_sql = true,
- }, {
- .name = "CEIL",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "CEILING",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "CHAR",
- .param_count = -1,
- .returns = FIELD_TYPE_STRING,
- .is_deterministic = true,
- .aggregate = FUNC_AGGREGATE_NONE,
- .flags = 0,
- .call = charFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "CHARACTER_LENGTH",
- .param_count = 1,
- .returns = FIELD_TYPE_INTEGER,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = lengthFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "CHAR_LENGTH",
- .param_count = 1,
- .returns = FIELD_TYPE_INTEGER,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = lengthFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "COALESCE",
- .param_count = -1,
- .returns = FIELD_TYPE_SCALAR,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_COALESCE,
- .call = sql_builtin_stub,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "COUNT",
- .param_count = -1,
- .returns = FIELD_TYPE_INTEGER,
- .aggregate = FUNC_AGGREGATE_GROUP,
- .is_deterministic = false,
- .flags = 0,
- .call = countStep,
- .finalize = countFinalize,
- .export_to_sql = true,
- }, {
- .name = "CURRENT_DATE",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "CURRENT_TIME",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "CURRENT_TIMESTAMP",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "DATE",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "DATETIME",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "EVERY",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "EXISTS",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "EXP",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "EXTRACT",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "FLOOR",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "GREATER",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "GREATEST",
- .param_count = -1,
- .returns = FIELD_TYPE_SCALAR,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_NEEDCOLL | SQL_FUNC_MAX,
- .call = minmaxFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "GROUP_CONCAT",
- .param_count = -1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_GROUP,
- .is_deterministic = false,
- .flags = 0,
- .call = groupConcatStep,
- .finalize = groupConcatFinalize,
- .export_to_sql = true,
- }, {
- .name = "HEX",
- .param_count = 1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = hexFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "IFNULL",
- .param_count = 2,
- .returns = FIELD_TYPE_SCALAR,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_COALESCE,
- .call = sql_builtin_stub,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "JULIANDAY",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "LEAST",
- .param_count = -1,
- .returns = FIELD_TYPE_SCALAR,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_NEEDCOLL | SQL_FUNC_MIN,
- .call = minmaxFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "LENGTH",
- .param_count = 1,
- .returns = FIELD_TYPE_INTEGER,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_LENGTH,
- .call = lengthFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "LESSER",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "LIKE",
- .param_count = -1,
- .returns = FIELD_TYPE_INTEGER,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_NEEDCOLL | SQL_FUNC_LIKE,
- .call = likeFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "LIKELIHOOD",
- .param_count = 2,
- .returns = FIELD_TYPE_BOOLEAN,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_UNLIKELY,
- .call = sql_builtin_stub,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "LIKELY",
- .param_count = 1,
- .returns = FIELD_TYPE_BOOLEAN,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_UNLIKELY,
- .call = sql_builtin_stub,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "LN",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "LOWER",
- .param_count = 1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_DERIVEDCOLL | SQL_FUNC_NEEDCOLL,
- .call = LowerICUFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "MAX",
- .param_count = 1,
- .returns = FIELD_TYPE_SCALAR,
- .aggregate = FUNC_AGGREGATE_GROUP,
- .is_deterministic = false,
- .flags = SQL_FUNC_NEEDCOLL | SQL_FUNC_MAX,
- .call = minmaxStep,
- .finalize = minMaxFinalize,
- .export_to_sql = true,
- }, {
- .name = "MIN",
- .param_count = 1,
- .returns = FIELD_TYPE_SCALAR,
- .aggregate = FUNC_AGGREGATE_GROUP,
- .is_deterministic = false,
- .flags = SQL_FUNC_NEEDCOLL | SQL_FUNC_MIN,
- .call = minmaxStep,
- .finalize = minMaxFinalize,
- .export_to_sql = true,
- }, {
- .name = "MOD",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "NULLIF",
- .param_count = 2,
- .returns = FIELD_TYPE_SCALAR,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_NEEDCOLL,
- .call = nullifFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "OCTET_LENGTH",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "POSITION",
- .param_count = 2,
- .returns = FIELD_TYPE_INTEGER,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_NEEDCOLL,
- .call = position_func,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "POWER",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "PRINTF",
- .param_count = -1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = printfFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "QUOTE",
- .param_count = 1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = quoteFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "RANDOM",
- .param_count = 0,
- .returns = FIELD_TYPE_INTEGER,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .call = randomFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "RANDOMBLOB",
- .param_count = 1,
- .returns = FIELD_TYPE_VARBINARY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .call = randomBlob,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "REPLACE",
- .param_count = 3,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_DERIVEDCOLL,
- .call = replaceFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "ROUND",
- .param_count = -1,
- .returns = FIELD_TYPE_INTEGER,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = roundFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "ROW_COUNT",
- .param_count = 0,
- .returns = FIELD_TYPE_INTEGER,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = sql_row_count,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "SOME",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "SOUNDEX",
- .param_count = 1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = soundexFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "SQRT",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "STRFTIME",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "SUBSTR",
- .param_count = -1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_DERIVEDCOLL,
- .call = substrFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "SUM",
- .param_count = 1,
- .returns = FIELD_TYPE_NUMBER,
- .aggregate = FUNC_AGGREGATE_GROUP,
- .is_deterministic = false,
- .flags = 0,
- .call = sum_step,
- .finalize = sumFinalize,
- .export_to_sql = true,
- }, {
- .name = "TIME",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "TOTAL",
- .param_count = 1,
- .returns = FIELD_TYPE_NUMBER,
- .aggregate = FUNC_AGGREGATE_GROUP,
- .is_deterministic = false,
- .flags = 0,
- .call = sum_step,
- .finalize = totalFinalize,
- .export_to_sql = true,
- }, {
- .name = "TRIM",
- .param_count = -1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_DERIVEDCOLL,
- .call = trim_func,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "TYPEOF",
- .param_count = 1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_TYPEOF,
- .call = typeofFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "UNICODE",
- .param_count = 1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = unicodeFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "UNLIKELY",
- .param_count = 1,
- .returns = FIELD_TYPE_BOOLEAN,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_UNLIKELY,
- .call = sql_builtin_stub,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "UPPER",
- .param_count = 1,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = SQL_FUNC_DERIVEDCOLL | SQL_FUNC_NEEDCOLL,
- .call = UpperICUFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "UUID",
- .param_count = -1,
- .returns = FIELD_TYPE_UUID,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .call = sql_func_uuid,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "VERSION",
- .param_count = 0,
- .returns = FIELD_TYPE_STRING,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = sql_func_version,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "ZEROBLOB",
- .param_count = 1,
- .returns = FIELD_TYPE_VARBINARY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = true,
- .flags = 0,
- .call = zeroblobFunc,
- .finalize = NULL,
- .export_to_sql = true,
- }, {
- .name = "_sql_stat_get",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "_sql_stat_init",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- }, {
- .name = "_sql_stat_push",
- .call = sql_builtin_stub,
- .export_to_sql = false,
- .param_count = -1,
- .returns = FIELD_TYPE_ANY,
- .aggregate = FUNC_AGGREGATE_NONE,
- .is_deterministic = false,
- .flags = 0,
- .finalize = NULL,
- },
};
-static struct func *
+/**
+ * Array of function implementation definitions. All implementations of the same
+ * function should be defined in succession.
+ */
+static struct sql_func_definition definitions[] = {
+ {"ABS", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, absFunc, NULL},
+ {"AVG", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step, avgFinalize},
+ {"CHAR", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, charFunc, NULL},
+ {"CHAR_LENGTH", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, lengthFunc,
+ NULL},
+ {"COALESCE", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, sql_builtin_stub,
+ NULL},
+ {"COUNT", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, countStep,
+ countFinalize},
+ {"GREATEST", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
+ {"GROUP_CONCAT", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY},
+ FIELD_TYPE_STRING, groupConcatStep, groupConcatFinalize},
+ {"HEX", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, hexFunc, NULL},
+ {"IFNULL", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
+ sql_builtin_stub, NULL},
+ {"LEAST", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
+ {"LENGTH", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, lengthFunc, NULL},
+ {"LIKE", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
+ FIELD_TYPE_INTEGER, likeFunc, NULL},
+ {"LIKELIHOOD", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_BOOLEAN,
+ sql_builtin_stub, NULL},
+ {"LIKELY", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_BOOLEAN, sql_builtin_stub,
+ NULL},
+ {"LOWER", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, LowerICUFunc, NULL},
+ {"MAX", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxStep,
+ minMaxFinalize},
+ {"NULLIF", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
+ nullifFunc, NULL},
+ {"POSITION", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER,
+ position_func, NULL},
+ {"PRINTF", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, printfFunc, NULL},
+ {"QUOTE", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, quoteFunc, NULL},
+ {"RANDOM", 0, {}, FIELD_TYPE_INTEGER, randomFunc, NULL},
+ {"RANDOMBLOB", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_VARBINARY, randomBlob,
+ NULL},
+ {"REPLACE", 3, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
+ FIELD_TYPE_STRING, replaceFunc, NULL},
+ {"ROUND", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER,
+ roundFunc, NULL},
+ {"ROW_COUNT", 0, {}, FIELD_TYPE_INTEGER, sql_row_count, NULL},
+ {"SOUNDEX", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, soundexFunc, NULL},
+ {"SUBSTR", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
+ FIELD_TYPE_STRING, substrFunc, NULL},
+ {"SUM", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step, sumFinalize},
+ {"TOTAL", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step,
+ totalFinalize},
+ {"TRIM", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
+ FIELD_TYPE_STRING, trim_func, NULL},
+ {"TYPEOF", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, typeofFunc, NULL},
+ {"UNICODE", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, unicodeFunc, NULL},
+ {"UNLIKELY", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_BOOLEAN, sql_builtin_stub,
+ NULL},
+ {"UPPER", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, UpperICUFunc, NULL},
+ {"UUID", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_UUID, sql_func_uuid, NULL},
+ {"VERSION", 0, {}, FIELD_TYPE_STRING, sql_func_version, NULL},
+ {"ZEROBLOB", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_VARBINARY, zeroblobFunc,
+ NULL},
+};
+
+static struct sql_func_dictionary *
built_in_func_get(const char *name)
{
uint32_t len = strlen(name);
@@ -2600,14 +2084,14 @@ built_in_func_get(const char *name)
}
static void
-built_in_func_put(struct func *func)
+built_in_func_put(struct sql_func_dictionary *dict)
{
- const char *name = func->def->name;
+ const char *name = dict->name;
uint32_t len = strlen(name);
assert(built_in_func_get(name) == NULL);
uint32_t hash = mh_strn_hash(name, len);
- const struct mh_strnptr_node_t strnode = {name, len, hash, func};
+ const struct mh_strnptr_node_t strnode = {name, len, hash, dict};
mh_int_t k = mh_strnptr_put(built_in_functions, &strnode, NULL, NULL);
if (k == mh_end(built_in_functions)) {
panic("Out of memory on insertion into SQL built-in functions "
@@ -2615,23 +2099,30 @@ built_in_func_put(struct func *func)
}
}
+static struct func *
+find_built_in_func(struct Expr *expr, struct sql_func_dictionary *dict)
+{
+ const char *name = expr->u.zToken;
+ int n = expr->x.pList != NULL ? expr->x.pList->nExpr : 0;
+ struct func *func = &dict->functions[0]->base;
+ assert(func->def->exports.sql);
+ int param_count = func->def->param_count;
+ if (param_count != -1 && param_count != n) {
+ diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, name,
+ tt_sprintf("%d", func->def->param_count), n);
+ return NULL;
+ }
+ return func;
+}
+
struct func *
sql_func_find(struct Expr *expr)
{
const char *name = expr->u.zToken;
- int n = expr->x.pList ? expr->x.pList->nExpr : 0;
- struct func *func = built_in_func_get(name);
- if (func != NULL) {
- assert(func->def->exports.sql);
- int param_count = func->def->param_count;
- if (param_count != -1 && param_count != n) {
- diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, name,
- tt_sprintf("%d", func->def->param_count), n);
- return NULL;
- }
- return func;
- }
- func = func_by_name(name, strlen(name));
+ struct sql_func_dictionary *dict = built_in_func_get(name);
+ if (dict != NULL)
+ return find_built_in_func(expr, dict);
+ struct func *func = func_by_name(name, strlen(name));
if (func == NULL) {
diag_set(ClientError, ER_NO_SUCH_FUNCTION, name);
return NULL;
@@ -2642,6 +2133,7 @@ sql_func_find(struct Expr *expr)
name));
return NULL;
}
+ int n = expr->x.pList != NULL ? expr->x.pList->nExpr : 0;
if (func->def->param_count != n) {
diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, name,
tt_sprintf("%d", func->def->param_count), n);
@@ -2653,14 +2145,10 @@ sql_func_find(struct Expr *expr)
uint32_t
sql_func_flags(const char *name)
{
- struct func *func = built_in_func_get(name);
- if (func == NULL)
+ struct sql_func_dictionary *dict = built_in_func_get(name);
+ if (dict == NULL)
return 0;
- assert(func->def->language == FUNC_LANGUAGE_SQL_BUILTIN);
- uint32_t flags = ((struct func_sql_builtin *)func)->flags;
- if (func->def->aggregate == FUNC_AGGREGATE_GROUP)
- flags |= SQL_FUNC_AGG;
- return flags;
+ return dict->flags;
}
static struct func_vtab func_sql_builtin_vtab;
@@ -2671,10 +2159,16 @@ sql_built_in_functions_cache_init(void)
built_in_functions = mh_strnptr_new();
if (built_in_functions == NULL)
panic("Out of memory on creating SQL built-in functions hash");
- for (uint32_t i = 0; i < nelem(sql_builtins); ++i) {
- const char *name = sql_builtins[i].name;
- if (!sql_builtins[i].export_to_sql)
- continue;
+ for (uint32_t i = 0; i < nelem(dictionaries); ++i)
+ built_in_func_put(&dictionaries[i]);
+
+ functions = malloc(sizeof(*functions) * nelem(definitions));
+ for (uint32_t i = 0; i < nelem(definitions); ++i) {
+ struct sql_func_definition *desc = &definitions[i];
+ const char *name = desc->name;
+ struct sql_func_dictionary *dict = built_in_func_get(name);
+ assert(dict != NULL);
+
uint32_t len = strlen(name);
uint32_t size = sizeof(struct func_def) + len + 1;
struct func_def *def = malloc(size);
@@ -2685,14 +2179,16 @@ sql_built_in_functions_cache_init(void)
def->body = NULL;
def->comment = NULL;
def->setuid = true;
- def->is_deterministic = sql_builtins[i].is_deterministic;
+ def->is_deterministic = dict->is_deterministic;
def->is_sandboxed = false;
- def->param_count = sql_builtins[i].param_count;
- def->returns = sql_builtins[i].returns;
- def->aggregate = sql_builtins[i].aggregate;
+ assert(desc->argc != -1 || dict->argc_min != dict->argc_max);
+ def->param_count = desc->argc;
+ def->returns = desc->result;
+ def->aggregate = desc->finalize == NULL ?
+ FUNC_AGGREGATE_NONE : FUNC_AGGREGATE_GROUP;
def->language = FUNC_LANGUAGE_SQL_BUILTIN;
def->name_len = len;
- def->exports.sql = sql_builtins[i].export_to_sql;
+ def->exports.sql = true;
func_opts_create(&def->opts);
memcpy(def->name, name, len + 1);
@@ -2705,11 +2201,26 @@ sql_built_in_functions_cache_init(void)
credentials_create_empty(&func->base.owner_credentials);
memset(func->base.access, 0, sizeof(func->base.access));
- func->flags = sql_builtins[i].flags;
- func->call = sql_builtins[i].call;
- func->finalize = sql_builtins[i].finalize;
- built_in_func_put(&func->base);
+ func->param_list = desc->argt;
+ func->flags = dict->flags;
+ func->call = desc->call;
+ func->finalize = desc->finalize;
+ functions[i] = func;
+ assert(dict->count == 0 || dict->functions != NULL);
+ if (dict->functions == NULL)
+ dict->functions = &functions[i];
+ ++dict->count;
}
+ /*
+ * Initialization of CHARACTER_LENGTH() function, which is actually
+ * another name for CHAR_LENGTH().
+ */
+ const char *name = "CHARACTER_LENGTH";
+ struct sql_func_dictionary *dict = built_in_func_get(name);
+ name = "CHAR_LENGTH";
+ struct sql_func_dictionary *dict_original = built_in_func_get(name);
+ dict->count = dict_original->count;
+ dict->functions = dict_original->functions;
}
void
@@ -2717,15 +2228,15 @@ sql_built_in_functions_cache_free(void)
{
if (built_in_functions == NULL)
return;
- for (uint32_t i = 0; i < nelem(sql_builtins); ++i) {
- const char *name = sql_builtins[i].name;
+ for (uint32_t i = 0; i < nelem(definitions); ++i)
+ func_delete(&functions[i]->base);
+ for (uint32_t i = 0; i < nelem(dictionaries); ++i) {
+ const char *name = dictionaries[i].name;
uint32_t len = strlen(name);
mh_int_t k = mh_strnptr_find_inp(built_in_functions, name, len);
if (k == mh_end(built_in_functions))
continue;
- struct func *func = mh_strnptr_node(built_in_functions, k)->val;
mh_strnptr_del(built_in_functions, k, NULL);
- func_delete(func);
}
assert(mh_size(built_in_functions) == 0);
mh_strnptr_delete(built_in_functions);
diff --git a/src/box/sql/resolve.c b/src/box/sql/resolve.c
index 35faddab5..21fe124d7 100644
--- a/src/box/sql/resolve.c
+++ b/src/box/sql/resolve.c
@@ -598,19 +598,9 @@ resolveExprStep(Walker * pWalker, Expr * pExpr)
assert(!ExprHasProperty(pExpr, EP_xIsSelect));
zId = pExpr->u.zToken;
nId = sqlStrlen30(zId);
- struct func *func = sql_func_find(pExpr);
- if (func == NULL) {
- pParse->is_aborted = true;
- pNC->nErr++;
- return WRC_Abort;
- }
- bool is_agg = func->def->aggregate ==
- FUNC_AGGREGATE_GROUP;
- assert(!is_agg || func->def->language ==
- FUNC_LANGUAGE_SQL_BUILTIN);
- pExpr->type = func->def->returns;
- if (sql_func_flag_is_set(func, SQL_FUNC_UNLIKELY) &&
- n == 2) {
+ uint32_t flags = sql_func_flags(zId);
+ bool is_agg = (flags & SQL_FUNC_AGG) != 0;
+ if ((flags & SQL_FUNC_UNLIKELY) != 0 && n == 2) {
ExprSetProperty(pExpr, EP_Unlikely | EP_Skip);
pExpr->iTable =
exprProbability(pList->a[1].pExpr);
@@ -623,20 +613,15 @@ resolveExprStep(Walker * pWalker, Expr * pExpr)
pNC->nErr++;
return WRC_Abort;
}
- } else if (sql_func_flag_is_set(func,
- SQL_FUNC_UNLIKELY)) {
+ } else if ((flags & SQL_FUNC_UNLIKELY) != 0) {
ExprSetProperty(pExpr, EP_Unlikely | EP_Skip);
/*
* unlikely() probability is
* 0.0625, likely() is 0.9375
*/
- pExpr->iTable = func->def->name[0] == 'u' ?
+ pExpr->iTable = zId[0] == 'u' ?
8388608 : 125829120;
}
- assert(!func->def->is_deterministic ||
- (pNC->ncFlags & NC_IdxExpr) == 0);
- if (func->def->is_deterministic)
- ExprSetProperty(pExpr, EP_ConstFunc);
if (is_agg && (pNC->ncFlags & NC_AllowAgg) == 0) {
const char *err =
tt_sprintf("misuse of aggregate "\
@@ -649,6 +634,8 @@ resolveExprStep(Walker * pWalker, Expr * pExpr)
if (is_agg)
pNC->ncFlags &= ~NC_AllowAgg;
sqlWalkExprList(pWalker, pList);
+ if (pParse->is_aborted)
+ break;
if (is_agg) {
NameContext *pNC2 = pNC;
pExpr->op = TK_AGG_FUNCTION;
@@ -661,16 +648,25 @@ resolveExprStep(Walker * pWalker, Expr * pExpr)
pExpr->op2++;
pNC2 = pNC2->pNext;
}
- assert(func != NULL);
if (pNC2) {
pNC2->ncFlags |= NC_HasAgg;
- if (sql_func_flag_is_set(func,
- SQL_FUNC_MIN |
- SQL_FUNC_MAX))
+ if ((flags & (SQL_FUNC_MIN |
+ SQL_FUNC_MAX)) != 0)
pNC2->ncFlags |= NC_MinMaxAgg;
}
pNC->ncFlags |= NC_AllowAgg;
}
+ struct func *func = sql_func_find(pExpr);
+ if (func == NULL) {
+ pParse->is_aborted = true;
+ pNC->nErr++;
+ return WRC_Abort;
+ }
+ pExpr->type = func->def->returns;
+ assert(!func->def->is_deterministic ||
+ (pNC->ncFlags & NC_IdxExpr) == 0);
+ if (func->def->is_deterministic)
+ ExprSetProperty(pExpr, EP_ConstFunc);
return WRC_Prune;
}
case TK_SELECT:
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 60fa1678d..b08ee8f68 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -4366,6 +4366,15 @@ struct func_sql_builtin {
struct func base;
/** A bitmask of SQL flags. */
uint16_t flags;
+ /**
+ * Description of the types of implementation arguments. Up to three
+ * arguments are described, but this should be sufficient, since all
+ * built-in SQL functions either have up to three arguments, or the
+ * number of their arguments is not limited here (but limited globally).
+ * If a function has an unlimited number of arguments, all arguments are
+ * of the same type.
+ */
+ enum field_type *param_list;
/**
* A VDBE-memory-compatible call method.
* SQL built-ins don't use func base class "call"
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* Re: [Tarantool-patches] [PATCH v1 02/10] sql: rework SQL built-in functions hash table
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 02/10] sql: rework SQL built-in functions hash table Mergen Imeev via Tarantool-patches
@ 2021-08-16 13:53 ` Vladimir Davydov via Tarantool-patches
0 siblings, 0 replies; 13+ messages in thread
From: Vladimir Davydov via Tarantool-patches @ 2021-08-16 13:53 UTC (permalink / raw)
To: imeevma; +Cc: tarantool-patches
On Fri, Aug 13, 2021 at 06:17:07AM +0300, imeevma@tarantool.org wrote:
> @@ -2615,23 +2099,30 @@ built_in_func_put(struct func *func)
> }
> }
>
> +static struct func *
> +find_built_in_func(struct Expr *expr, struct sql_func_dictionary *dict)
> +{
> + const char *name = expr->u.zToken;
> + int n = expr->x.pList != NULL ? expr->x.pList->nExpr : 0;
> + struct func *func = &dict->functions[0]->base;
> + assert(func->def->exports.sql);
> + int param_count = func->def->param_count;
> + if (param_count != -1 && param_count != n) {
> + diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, name,
> + tt_sprintf("%d", func->def->param_count), n);
> + return NULL;
> + }
> + return func;
> +}
> +
> struct func *
> sql_func_find(struct Expr *expr)
> {
> const char *name = expr->u.zToken;
> - int n = expr->x.pList ? expr->x.pList->nExpr : 0;
> - struct func *func = built_in_func_get(name);
> - if (func != NULL) {
> - assert(func->def->exports.sql);
> - int param_count = func->def->param_count;
> - if (param_count != -1 && param_count != n) {
> - diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, name,
> - tt_sprintf("%d", func->def->param_count), n);
> - return NULL;
> - }
> - return func;
> - }
> - func = func_by_name(name, strlen(name));
> + struct sql_func_dictionary *dict = built_in_func_get(name);
> + if (dict != NULL)
> + return find_built_in_func(expr, dict);
> + struct func *func = func_by_name(name, strlen(name));
> if (func == NULL) {
> diag_set(ClientError, ER_NO_SUCH_FUNCTION, name);
> return NULL;
Having a separate (from _func) two-level (name -> dictionary -> func)
hash for built-in functions looks ugly. Treating built-in functions and
user-defined functions differently here, but using the same struct func
for both kinds looks ugly as well. The old code, where both built-in
functions and user-defined functions lived in the same hash (and in
_func system space), looked consistent and neat.
If our goal is to enable static type checking of function arguments, we
can instead add a callback to struct func ('check'), which would raise
an error if supplied arguments are incorrect without invoking the
function. We wouldn't have a separate 'execute' callback for each
variant of the same function then, because all variants would share the
same struct func, like they do now, but do we really need it?
Another idea is to move built-in function name resolution completely to
the parser. Then we wouldn't need a hash for built-in functions at all
(nor would we need to have them in _func). I find this idea particularly
appealing, because after all built-in functions are like operators -
their names and argument types are known at the parse time - so
theoretically we could generate a separate opcode for them with all the
necessary information prepared by the paser. Please check out if this
is doable.
^ permalink raw reply [flat|nested] 13+ messages in thread
* [Tarantool-patches] [PATCH v1 03/10] sql: check number of arguments during parsing
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 01/10] sql: modify signature of TRIM() Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 02/10] sql: rework SQL built-in functions hash table Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 04/10] sql: static type check for SQL built-in functions Mergen Imeev via Tarantool-patches
` (7 subsequent siblings)
10 siblings, 0 replies; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
Prior to this patch, the number of arguments for functions with a
variable number of arguments was checked at runtime. After this patch,
it will be checked during parsing. For functions with a constant number
of arguments, it is always checked during parsing.
Part of #6105
---
src/box/sql/func.c | 23 +++--
test/sql-tap/built-in-functions.test.lua | 119 +++++++++++++++++++++++
test/sql-tap/engine.cfg | 3 +
test/sql-tap/func.test.lua | 8 +-
test/sql-tap/func2.test.lua | 18 ++--
test/sql-tap/func5.test.lua | 6 +-
test/sql-tap/select1.test.lua | 2 +-
test/sql-tap/uuid.test.lua | 2 +-
test/sql/collation.result | 2 +-
9 files changed, 158 insertions(+), 25 deletions(-)
create mode 100755 test/sql-tap/built-in-functions.test.lua
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 7d53b1646..27e38ec16 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -2104,14 +2104,25 @@ find_built_in_func(struct Expr *expr, struct sql_func_dictionary *dict)
{
const char *name = expr->u.zToken;
int n = expr->x.pList != NULL ? expr->x.pList->nExpr : 0;
- struct func *func = &dict->functions[0]->base;
- assert(func->def->exports.sql);
- int param_count = func->def->param_count;
- if (param_count != -1 && param_count != n) {
- diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, name,
- tt_sprintf("%d", func->def->param_count), n);
+ int argc_min = dict->argc_min;
+ int argc_max = dict->argc_max;
+ if (n < argc_min || n > argc_max) {
+ const char *str;
+ if (argc_min == argc_max)
+ str = tt_sprintf("%d", argc_min);
+ else if (argc_max == SQL_MAX_FUNCTION_ARG && n < argc_min)
+ str = tt_sprintf("at least %d", argc_min);
+ else
+ str = tt_sprintf("from %d to %d", argc_min, argc_max);
+ diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, name, str, n);
return NULL;
}
+ struct func *func = NULL;
+ for (uint32_t i = 0; i < dict->count; ++i) {
+ func = &dict->functions[i]->base;
+ if (func->def->param_count == n)
+ break;
+ }
return func;
}
diff --git a/test/sql-tap/built-in-functions.test.lua b/test/sql-tap/built-in-functions.test.lua
new file mode 100755
index 000000000..c704e71a6
--- /dev/null
+++ b/test/sql-tap/built-in-functions.test.lua
@@ -0,0 +1,119 @@
+#!/usr/bin/env tarantool
+local test = require("sqltester")
+test:plan(10)
+
+--
+-- Make sure that number of arguments check is checked properly for SQL built-in
+-- functions with variable number of arguments.
+--
+test:do_catchsql_test(
+ "builtins-1.1",
+ [[
+ SELECT COUNT(1, 2);
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to COUNT(): ]]..
+ [[expected from 0 to 1, got 2]]
+ }
+)
+
+test:do_catchsql_test(
+ "builtins-1.2",
+ [[
+ SELECT GREATEST();
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to GREATEST(): ]]..
+ [[expected at least 2, got 0]]
+ }
+)
+
+test:do_catchsql_test(
+ "builtins-1.3",
+ [[
+ SELECT GROUP_CONCAT();
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to GROUP_CONCAT(): ]]..
+ [[expected from 1 to 2, got 0]]
+ }
+)
+
+test:do_catchsql_test(
+ "builtins-1.4",
+ [[
+ SELECT GROUP_CONCAT(1, 2, 3);
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to GROUP_CONCAT(): ]]..
+ [[expected from 1 to 2, got 3]]
+ }
+)
+
+test:do_catchsql_test(
+ "builtins-1.5",
+ [[
+ SELECT LEAST();
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to LEAST(): ]]..
+ [[expected at least 2, got 0]]
+ }
+)
+
+test:do_catchsql_test(
+ "builtins-1.6",
+ [[
+ SELECT ROUND();
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to ROUND(): ]]..
+ [[expected from 1 to 2, got 0]]
+ }
+)
+
+test:do_catchsql_test(
+ "builtins-1.7",
+ [[
+ SELECT ROUND(1, 2, 3);
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to ROUND(): ]]..
+ [[expected from 1 to 2, got 3]]
+ }
+)
+
+test:do_catchsql_test(
+ "builtins-1.8",
+ [[
+ SELECT SUBSTR('1');
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to SUBSTR(): ]]..
+ [[expected from 2 to 3, got 1]]
+ }
+)
+
+test:do_catchsql_test(
+ "builtins-1.9",
+ [[
+ SELECT SUBSTR('1', '2', '3', '4');
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to SUBSTR(): ]]..
+ [[expected from 2 to 3, got 4]]
+ }
+)
+
+test:do_catchsql_test(
+ "builtins-1.10",
+ [[
+ SELECT UUID(1, 2);
+ ]],
+ {
+ 1, [[Wrong number of arguments is passed to UUID(): ]]..
+ [[expected from 0 to 1, got 2]]
+ }
+)
+
+test:finish_test()
diff --git a/test/sql-tap/engine.cfg b/test/sql-tap/engine.cfg
index 820c72b00..7d7d281dd 100644
--- a/test/sql-tap/engine.cfg
+++ b/test/sql-tap/engine.cfg
@@ -26,6 +26,9 @@
"metatypes.test.lua": {
"memtx": {"engine": "memtx"}
},
+ "built-in-functions.test.lua": {
+ "memtx": {"engine": "memtx"}
+ },
"gh-4077-iproto-execute-no-bind.test.lua": {},
"*": {
"memtx": {"engine": "memtx"},
diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua
index 4cc722d1e..637d67a30 100755
--- a/test/sql-tap/func.test.lua
+++ b/test/sql-tap/func.test.lua
@@ -427,7 +427,7 @@ test:do_catchsql_test(
SELECT round(a,b,c) FROM t1
]], {
-- <func-4.5>
- 1, "Wrong number of arguments is passed to ROUND(): expected 1 or 2, got 3"
+ 1, "Wrong number of arguments is passed to ROUND(): expected from 1 to 2, got 3"
-- </func-4.5>
})
@@ -487,7 +487,7 @@ test:do_catchsql_test(
SELECT round() FROM t1 ORDER BY a
]], {
-- <func-4.11>
- 1, "Wrong number of arguments is passed to ROUND(): expected 1 or 2, got 0"
+ 1, "Wrong number of arguments is passed to ROUND(): expected from 1 to 2, got 0"
-- </func-4.11>
})
@@ -2553,7 +2553,7 @@ test:do_catchsql_test(
SELECT coalesce()
]], {
-- <func-27.1>
- 1, "Wrong number of arguments is passed to COALESCE(): expected at least two, got 0"
+ 1, "Wrong number of arguments is passed to COALESCE(): expected at least 2, got 0"
-- </func-27.1>
})
@@ -2563,7 +2563,7 @@ test:do_catchsql_test(
SELECT coalesce(1)
]], {
-- <func-27.2>
- 1, "Wrong number of arguments is passed to COALESCE(): expected at least two, got 1"
+ 1, "Wrong number of arguments is passed to COALESCE(): expected at least 2, got 1"
-- </func-27.2>
})
diff --git a/test/sql-tap/func2.test.lua b/test/sql-tap/func2.test.lua
index 95b8a1f5f..792f020f1 100755
--- a/test/sql-tap/func2.test.lua
+++ b/test/sql-tap/func2.test.lua
@@ -50,7 +50,7 @@ test:do_catchsql_test(
SELECT SUBSTR()
]], {
-- <func2-1.2.1>
- 1, "Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 0"
+ 1, "Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 0"
-- </func2-1.2.1>
})
@@ -60,7 +60,7 @@ test:do_catchsql_test(
SELECT SUBSTR('Supercalifragilisticexpialidocious')
]], {
-- <func2-1.2.2>
- 1, "Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 1"
+ 1, "Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 1"
-- </func2-1.2.2>
})
@@ -70,7 +70,7 @@ test:do_catchsql_test(
SELECT SUBSTR('Supercalifragilisticexpialidocious', 1,1,1)
]], {
-- <func2-1.2.3>
- 1, "Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 4"
+ 1, "Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 4"
-- </func2-1.2.3>
})
@@ -673,7 +673,7 @@ if ("ሴ" ~= "u1234")
SELECT SUBSTR()
]], {
-- <func2-2.1.2>
- 1, "Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 0"
+ 1, "Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 0"
-- </func2-2.1.2>
})
@@ -683,7 +683,7 @@ if ("ሴ" ~= "u1234")
SELECT SUBSTR('hiሴho')
]], {
-- <func2-2.1.3>
- 1, "Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 1"
+ 1, "Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 1"
-- </func2-2.1.3>
})
@@ -693,7 +693,7 @@ if ("ሴ" ~= "u1234")
SELECT SUBSTR('hiሴho', 1,1,1)
]], {
-- <func2-2.1.4>
- 1, "Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 4"
+ 1, "Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 4"
-- </func2-2.1.4>
})
@@ -1038,7 +1038,7 @@ test:do_catchsql_test(
SELECT SUBSTR()
]], {
-- <func2-3.1.2>
- 1, "Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 0"
+ 1, "Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 0"
-- </func2-3.1.2>
})
@@ -1048,7 +1048,7 @@ test:do_catchsql_test(
SELECT SUBSTR(x'1234')
]], {
-- <func2-3.1.3>
- 1, "Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 1"
+ 1, "Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 1"
-- </func2-3.1.3>
})
@@ -1058,7 +1058,7 @@ test:do_catchsql_test(
SELECT SUBSTR(x'1234', 1,1,1)
]], {
-- <func2-3.1.4>
- 1, "Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 4"
+ 1, "Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 4"
-- </func2-3.1.4>
})
diff --git a/test/sql-tap/func5.test.lua b/test/sql-tap/func5.test.lua
index 44755b1f7..253c59967 100755
--- a/test/sql-tap/func5.test.lua
+++ b/test/sql-tap/func5.test.lua
@@ -276,19 +276,19 @@ test:do_catchsql_test(
"func-5-5.1",
[[
SELECT LEAST(false);
- ]], { 1, "Wrong number of arguments is passed to LEAST(): expected at least two, got 1" } )
+ ]], { 1, "Wrong number of arguments is passed to LEAST(): expected at least 2, got 1" } )
test:do_catchsql_test(
"func-5-5.2",
[[
SELECT GREATEST('abc');
- ]], { 1, "Wrong number of arguments is passed to GREATEST(): expected at least two, got 1" } )
+ ]], { 1, "Wrong number of arguments is passed to GREATEST(): expected at least 2, got 1" } )
test:do_catchsql_test(
"func-5-5.3",
[[
SELECT LEAST();
- ]], { 1, "Wrong number of arguments is passed to LEAST(): expected at least two, got 0" } )
+ ]], { 1, "Wrong number of arguments is passed to LEAST(): expected at least 2, got 0" } )
-- Make sure that ifnull() returns type of corresponding (i.e. first
-- non-null) argument.
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index dbc6e193d..ba12470f3 100755
--- a/test/sql-tap/select1.test.lua
+++ b/test/sql-tap/select1.test.lua
@@ -250,7 +250,7 @@ test:do_catchsql_test(
SELECT count(f1,f2) FROM test1
]], {
-- <select1-2.1>
- 1, "Wrong number of arguments is passed to COUNT(): expected 0 or 1, got 2"
+ 1, "Wrong number of arguments is passed to COUNT(): expected from 0 to 1, got 2"
-- </select1-2.1>
})
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
index 57e638046..92691773d 100755
--- a/test/sql-tap/uuid.test.lua
+++ b/test/sql-tap/uuid.test.lua
@@ -1290,7 +1290,7 @@ test:do_catchsql_test(
[[
SELECT uuid(4, 5);
]], {
- 1, "Wrong number of arguments is passed to UUID(): expected one or zero, got 2"
+ 1, "Wrong number of arguments is passed to UUID(): expected from 0 to 1, got 2"
})
-- Make sure the uuid() function generates a new UUID each time when called.
diff --git a/test/sql/collation.result b/test/sql/collation.result
index 1fcc212d8..8444cc7ab 100644
--- a/test/sql/collation.result
+++ b/test/sql/collation.result
@@ -298,7 +298,7 @@ box.execute("SELECT * FROM t WHERE a COLLATE \"binary\" = c COLLATE \"unicode\";
box.execute("SELECT * FROM t WHERE a COLLATE \"binary\" = substr();")
---
- null
-- 'Wrong number of arguments is passed to SUBSTR(): expected 1 or 2, got 0'
+- 'Wrong number of arguments is passed to SUBSTR(): expected from 2 to 3, got 0'
...
-- Compound queries perform implicit comparisons between values.
-- Hence, rules for collations compatibilities are the same.
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* [Tarantool-patches] [PATCH v1 04/10] sql: static type check for SQL built-in functions
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
` (2 preceding siblings ...)
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 03/10] sql: check number of arguments during parsing Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 05/10] sql: runtime " Mergen Imeev via Tarantool-patches
` (6 subsequent siblings)
10 siblings, 0 replies; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
This patch introduces a static type checking mechanism for SQL built-in
functions. However, it is currently disabled as the functions themselves
need to be prepared for such changes.
Part of #6105
---
src/box/sql/expr.c | 6 ++-
src/box/sql/func.c | 119 +++++++++++++++++++++++++++++++++++++++---
src/box/sql/prepare.c | 2 +-
src/box/sql/select.c | 6 ++-
4 files changed, 123 insertions(+), 10 deletions(-)
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index c67a7091c..fb778f7e3 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -5390,7 +5390,11 @@ analyzeAggregate(Walker * pWalker, Expr * pExpr)
(pExpr, EP_IntValue));
pItem->func =
sql_func_find(pExpr);
- assert(pItem->func != NULL);
+ if (pItem->func == NULL) {
+ pParse->is_aborted =
+ true;
+ return WRC_Abort;
+ }
assert(pItem->func->def->
language ==
FUNC_LANGUAGE_SQL_BUILTIN &&
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 27e38ec16..b5e6bfe2a 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -2099,6 +2099,106 @@ built_in_func_put(struct sql_func_dictionary *dict)
}
}
+/**
+ * Check if there is no need to cast argument to accepted type. Also, in some
+ * cases operation 'op' may be important, for example when given argument is
+ * NULL or is variable.
+ *
+ * Returns TRUE when:
+ * - when operation is NULL;
+ * - when accepted type and argument type are equal;
+ * - when accepted type is ANY;
+ * - when accepted type is INTEGER and argument type is UNSIGNED.
+ */
+static inline bool
+is_exact(int op, enum field_type a, enum field_type b)
+{
+ return op == TK_NULL || a == b || a == FIELD_TYPE_ANY ||
+ (a == FIELD_TYPE_INTEGER && b == FIELD_TYPE_UNSIGNED);
+}
+
+/**
+ * Check if the argument MEM type will not change during cast. It means that
+ * either is_exact() returns TRUE or accepted type is metatype that includes
+ * argument type.
+ *
+ * Returns TRUE when:
+ * - is_exact() returns TRUE;
+ * - when accepted type is NUMBER and argument type is numeric type;
+ * - when accepted type is SCALAR and argument type is not MAP or ARRAY.
+ */
+static inline bool
+is_upcast(int op, enum field_type a, enum field_type b)
+{
+ return is_exact(op, a, b) ||
+ (a == FIELD_TYPE_NUMBER && sql_type_is_numeric(b)) ||
+ (a == FIELD_TYPE_SCALAR && b != FIELD_TYPE_MAP &&
+ b != FIELD_TYPE_ARRAY);
+}
+
+/**
+ * Check if there is a chance that the argument can be cast to accepted type
+ * according to implicit cast rules.
+ *
+ * Returns TRUE when:
+ * - is_upcast() returns TRUE;
+ * - when accepted type and argument type are numeric types;
+ * - when argument is binded value;
+ * - when argument type is ANY, which means that is was not resolved.
+ */
+static inline bool
+is_castable(int op, enum field_type a, enum field_type b)
+{
+ return is_upcast(op, a, b) || op == TK_VARIABLE ||
+ (sql_type_is_numeric(a) && sql_type_is_numeric(b)) ||
+ b == FIELD_TYPE_ANY;
+}
+
+enum check_type {
+ CHECK_TYPE_EXACT,
+ CHECK_TYPE_UPCAST,
+ CHECK_TYPE_CASTABLE,
+};
+
+static struct func *
+find_compatible(struct Expr *expr, struct sql_func_dictionary *dict,
+ enum check_type check)
+{
+ int n = expr->x.pList != NULL ? expr->x.pList->nExpr : 0;
+ for (uint32_t i = 0; i < dict->count; ++i) {
+ struct func_sql_builtin *func = dict->functions[i];
+ int argc = func->base.def->param_count;
+ if (argc != n && argc != -1)
+ continue;
+ if (n == 0)
+ return &func->base;
+
+ enum field_type *types = func->param_list;
+ bool is_match = true;
+ for (int j = 0; j < n && is_match; ++j) {
+ struct Expr *e = expr->x.pList->a[j].pExpr;
+ enum field_type a = types[argc != -1 ? j : 0];
+ enum field_type b = sql_expr_type(e);
+ switch (check) {
+ case CHECK_TYPE_EXACT:
+ is_match = is_exact(e->op, a, b);
+ break;
+ case CHECK_TYPE_UPCAST:
+ is_match = is_upcast(e->op, a, b);
+ break;
+ case CHECK_TYPE_CASTABLE:
+ is_match = is_castable(e->op, a, b);
+ break;
+ default:
+ unreachable();
+ }
+ }
+ if (is_match)
+ return &func->base;
+ }
+ return NULL;
+}
+
static struct func *
find_built_in_func(struct Expr *expr, struct sql_func_dictionary *dict)
{
@@ -2117,13 +2217,18 @@ find_built_in_func(struct Expr *expr, struct sql_func_dictionary *dict)
diag_set(ClientError, ER_FUNC_WRONG_ARG_COUNT, name, str, n);
return NULL;
}
- struct func *func = NULL;
- for (uint32_t i = 0; i < dict->count; ++i) {
- func = &dict->functions[i]->base;
- if (func->def->param_count == n)
- break;
- }
- return func;
+ struct func *func = find_compatible(expr, dict, CHECK_TYPE_EXACT);
+ if (func != NULL)
+ return func;
+ func = find_compatible(expr, dict, CHECK_TYPE_UPCAST);
+ if (func != NULL)
+ return func;
+ func = find_compatible(expr, dict, CHECK_TYPE_CASTABLE);
+ if (func != NULL)
+ return func;
+ diag_set(ClientError, ER_SQL_EXECUTE,
+ tt_sprintf("wrong arguments for function %s()", name));
+ return NULL;
}
struct func *
diff --git a/src/box/sql/prepare.c b/src/box/sql/prepare.c
index d859a0243..b986630e9 100644
--- a/src/box/sql/prepare.c
+++ b/src/box/sql/prepare.c
@@ -89,7 +89,7 @@ sql_stmt_compile(const char *zSql, int nBytes, struct Vdbe *pReprepare,
} else {
sqlRunParser(&sParse, zSql);
}
- assert(0 == sParse.nQueryLoop);
+ assert(0 == sParse.nQueryLoop || sParse.is_aborted);
if (db->mallocFailed)
sParse.is_aborted = true;
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index a26ca54a3..774d06fa9 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -6264,7 +6264,7 @@ sqlSelect(Parse * pParse, /* The parser context */
sNC.ncFlags &= ~NC_InAggFunc;
}
sAggInfo.mxReg = pParse->nMem;
- if (db->mallocFailed)
+ if (pParse->is_aborted)
goto select_end;
/* Processing for aggregates with GROUP BY is very different and
@@ -6473,6 +6473,8 @@ sqlSelect(Parse * pParse, /* The parser context */
*/
sqlVdbeJumpHere(v, addr1);
updateAccumulator(pParse, &sAggInfo);
+ if (pParse->is_aborted)
+ goto select_end;
sqlVdbeAddOp2(v, OP_Integer, 1, iUseFlag);
VdbeComment((v, "indicate data in accumulator"));
@@ -6636,6 +6638,8 @@ sqlSelect(Parse * pParse, /* The parser context */
goto select_end;
}
updateAccumulator(pParse, &sAggInfo);
+ if (pParse->is_aborted)
+ goto select_end;
assert(pMinMax == 0 || pMinMax->nExpr == 1);
if (sqlWhereIsOrdered(pWInfo) > 0) {
sqlVdbeGoto(v,
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* [Tarantool-patches] [PATCH v1 05/10] sql: runtime type check for SQL built-in functions
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
` (3 preceding siblings ...)
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 04/10] sql: static type check for SQL built-in functions Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 06/10] sql: enable types checking for some functions Mergen Imeev via Tarantool-patches
` (5 subsequent siblings)
10 siblings, 0 replies; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
This patch introduces a runtime type checking mechanism for SQL built-in
functions. However, it is currently disabled as the functions themselves
need to be prepared for such changes.
Part of #6105
---
src/box/sql/expr.c | 27 +++++++++++++++++++++++++++
src/box/sql/mem.c | 2 +-
src/box/sql/select.c | 4 ++++
src/box/sql/sqlInt.h | 4 ++++
4 files changed, 36 insertions(+), 1 deletion(-)
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index fb778f7e3..47005f8e3 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -137,6 +137,29 @@ sql_expr_type(struct Expr *pExpr)
return pExpr->type;
}
+int
+sql_emit_args_types(struct Vdbe *v, int reg, struct func *base, uint32_t argc)
+{
+ if (argc == 0 || base->def->language != FUNC_LANGUAGE_SQL_BUILTIN)
+ return 0;
+ struct func_sql_builtin *func = (struct func_sql_builtin *)base;
+ if (func->base.def->param_count > 0) {
+ sqlVdbeAddOp4(v, OP_ApplyType, reg, argc, 0,
+ (char *)func->param_list, P4_STATIC);
+ return 0;
+ }
+ assert(func->base.def->param_count == -1);
+ uint32_t size = argc * sizeof(enum field_type);
+ enum field_type *types = sqlDbMallocRawNN(sql_get(), size);
+ if (types == NULL)
+ return -1;
+ enum field_type type = func->param_list[0];
+ for (uint32_t i = 0; i < argc; ++i)
+ types[i] = type;
+ sqlVdbeAddOp4(v, OP_ApplyType, reg, argc, 0, (char *)types, P4_DYNAMIC);
+ return 0;
+}
+
enum field_type *
field_type_sequence_dup(struct Parse *parse, enum field_type *types,
uint32_t len)
@@ -4072,6 +4095,10 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
} else {
r1 = 0;
}
+ if (sql_emit_args_types(v, r1, func, nFarg) != 0) {
+ pParse->is_aborted = true;
+ return 0;
+ }
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/mem.c b/src/box/sql/mem.c
index 066940fac..55131e684 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -1216,7 +1216,7 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
int
mem_cast_implicit(struct Mem *mem, enum field_type type)
{
- if (mem->type == MEM_TYPE_NULL)
+ if (mem->type == MEM_TYPE_NULL || type == FIELD_TYPE_ANY)
return 0;
if ((mem->flags & MEM_Scalar) != 0 && type != FIELD_TYPE_SCALAR)
return -1;
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 774d06fa9..33cebf28a 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -5566,6 +5566,10 @@ updateAccumulator(Parse * pParse, AggInfo * pAggInfo)
vdbe_insert_distinct(pParse, pF->iDistinct, pF->reg_eph,
addrNext, 1, regAgg);
}
+ if (sql_emit_args_types(v, regAgg, pF->func, nArg) != 0) {
+ pParse->is_aborted = true;
+ return;
+ }
if (sql_func_flag_is_set(pF->func, SQL_FUNC_NEEDCOLL)) {
struct coll *coll = NULL;
struct ExprList_item *pItem;
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index b08ee8f68..11a685d98 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -4412,6 +4412,10 @@ sql_func_flag_is_set(struct func *func, uint16_t flag)
struct func *
sql_func_find(struct Expr *expr);
+/** Code an OP_ApplyType opcode that will force types onto arguments. */
+int
+sql_emit_args_types(struct Vdbe *v, int reg, struct func *base, uint32_t argc);
+
/**
* Return the parameters of the function with the given name. If the function
* with the given name does not exist, or the function is not a built-in SQL
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* [Tarantool-patches] [PATCH v1 06/10] sql: enable types checking for some functions
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
` (4 preceding siblings ...)
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 05/10] sql: runtime " Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 07/10] sql: fix result type of min() and max() functions Mergen Imeev via Tarantool-patches
` (4 subsequent siblings)
10 siblings, 0 replies; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
This patch enables static and dynamic type checks for functions that do
not need any rework.
Part of #6105
---
src/box/sql/func.c | 60 +++--
test/sql-tap/badutf1.test.lua | 34 ++-
test/sql-tap/built-in-functions.test.lua | 324 ++++++++++++++++++++++-
test/sql-tap/coalesce.test.lua | 2 +-
test/sql-tap/cse.test.lua | 8 +-
test/sql-tap/func.test.lua | 97 +------
test/sql-tap/orderby1.test.lua | 2 +-
test/sql-tap/position.test.lua | 84 +++---
test/sql-tap/sql-errors.test.lua | 2 +-
test/sql-tap/substr.test.lua | 6 +-
test/sql-tap/uuid.test.lua | 56 ++--
test/sql-tap/where3.test.lua | 6 +-
test/sql/boolean.result | 32 +--
test/sql/prepared.result | 4 +-
test/sql/types.result | 68 ++---
15 files changed, 506 insertions(+), 279 deletions(-)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index b5e6bfe2a..b22edda20 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -2012,49 +2012,60 @@ struct sql_func_definition {
* function should be defined in succession.
*/
static struct sql_func_definition definitions[] = {
- {"ABS", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, absFunc, NULL},
+ {"ABS", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, absFunc, NULL},
+ {"ABS", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, absFunc, NULL},
{"AVG", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step, avgFinalize},
- {"CHAR", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, charFunc, NULL},
- {"CHAR_LENGTH", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, lengthFunc,
+ {"CHAR", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, charFunc, NULL},
+ {"CHAR_LENGTH", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, lengthFunc,
NULL},
{"COALESCE", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, sql_builtin_stub,
NULL},
- {"COUNT", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, countStep,
+ {"COUNT", 0, {}, FIELD_TYPE_INTEGER, countStep, countFinalize},
+ {"COUNT", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, countStep,
countFinalize},
{"GREATEST", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
{"GROUP_CONCAT", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY},
FIELD_TYPE_STRING, groupConcatStep, groupConcatFinalize},
- {"HEX", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, hexFunc, NULL},
+ {"HEX", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_STRING, hexFunc, NULL},
{"IFNULL", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
sql_builtin_stub, NULL},
{"LEAST", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
- {"LENGTH", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, lengthFunc, NULL},
- {"LIKE", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
- FIELD_TYPE_INTEGER, likeFunc, NULL},
- {"LIKELIHOOD", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_BOOLEAN,
- sql_builtin_stub, NULL},
+ {"LENGTH", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, lengthFunc,
+ NULL},
+ {"LENGTH", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_INTEGER, lengthFunc,
+ NULL},
+ {"LIKE", 2, {FIELD_TYPE_STRING, FIELD_TYPE_STRING},
+ FIELD_TYPE_BOOLEAN, likeFunc, NULL},
+ {"LIKE", 3, {FIELD_TYPE_STRING, FIELD_TYPE_STRING, FIELD_TYPE_STRING},
+ FIELD_TYPE_BOOLEAN, likeFunc, NULL},
+ {"LIKELIHOOD", 2, {FIELD_TYPE_ANY, FIELD_TYPE_DOUBLE},
+ FIELD_TYPE_BOOLEAN, sql_builtin_stub, NULL},
{"LIKELY", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_BOOLEAN, sql_builtin_stub,
NULL},
- {"LOWER", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, LowerICUFunc, NULL},
+ {"LOWER", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, LowerICUFunc,
+ NULL},
{"MAX", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxStep,
minMaxFinalize},
{"MIN", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxStep,
minMaxFinalize},
{"NULLIF", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
nullifFunc, NULL},
- {"POSITION", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER,
- position_func, NULL},
- {"PRINTF", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, printfFunc, NULL},
+ {"POSITION", 2, {FIELD_TYPE_STRING, FIELD_TYPE_STRING},
+ FIELD_TYPE_INTEGER, position_func, NULL},
+ {"PRINTF", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, printfFunc,
+ NULL},
{"QUOTE", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, quoteFunc, NULL},
{"RANDOM", 0, {}, FIELD_TYPE_INTEGER, randomFunc, NULL},
- {"RANDOMBLOB", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_VARBINARY, randomBlob,
- NULL},
+ {"RANDOMBLOB", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_VARBINARY,
+ randomBlob, NULL},
{"REPLACE", 3, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
FIELD_TYPE_STRING, replaceFunc, NULL},
- {"ROUND", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER,
+ {"ROUND", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, roundFunc, NULL},
+ {"ROUND", 2, {FIELD_TYPE_DOUBLE, FIELD_TYPE_INTEGER}, FIELD_TYPE_DOUBLE,
roundFunc, NULL},
{"ROW_COUNT", 0, {}, FIELD_TYPE_INTEGER, sql_row_count, NULL},
- {"SOUNDEX", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, soundexFunc, NULL},
+ {"SOUNDEX", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, soundexFunc,
+ NULL},
{"SUBSTR", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
FIELD_TYPE_STRING, substrFunc, NULL},
{"SUM", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step, sumFinalize},
@@ -2063,14 +2074,17 @@ static struct sql_func_definition definitions[] = {
{"TRIM", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
FIELD_TYPE_STRING, trim_func, NULL},
{"TYPEOF", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, typeofFunc, NULL},
- {"UNICODE", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, unicodeFunc, NULL},
+ {"UNICODE", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, unicodeFunc,
+ NULL},
{"UNLIKELY", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_BOOLEAN, sql_builtin_stub,
NULL},
- {"UPPER", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, UpperICUFunc, NULL},
- {"UUID", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_UUID, sql_func_uuid, NULL},
- {"VERSION", 0, {}, FIELD_TYPE_STRING, sql_func_version, NULL},
- {"ZEROBLOB", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_VARBINARY, zeroblobFunc,
+ {"UPPER", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, UpperICUFunc,
NULL},
+ {"UUID", 0, {}, FIELD_TYPE_UUID, sql_func_uuid, NULL},
+ {"UUID", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_UUID, sql_func_uuid, NULL},
+ {"VERSION", 0, {}, FIELD_TYPE_STRING, sql_func_version, NULL},
+ {"ZEROBLOB", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_VARBINARY,
+ zeroblobFunc, NULL},
};
static struct sql_func_dictionary *
diff --git a/test/sql-tap/badutf1.test.lua b/test/sql-tap/badutf1.test.lua
index 6de0413ef..b25436186 100755
--- a/test/sql-tap/badutf1.test.lua
+++ b/test/sql-tap/badutf1.test.lua
@@ -25,7 +25,7 @@ test:do_test(
"badutf-1.1",
function()
--test:execsql "PRAGMA encoding='UTF8'"
- return test:execsql2("SELECT hex('\x80') AS x")
+ return test:execsql2("SELECT hex(x'80') AS x")
end, {
-- <badutf-1.1>
"X", "80"
@@ -35,7 +35,7 @@ test:do_test(
test:do_test(
"badutf-1.2",
function()
- return test:execsql2("SELECT hex('\x81') AS x")
+ return test:execsql2("SELECT hex(x'81') AS x")
end, {
-- <badutf-1.2>
"X", "81"
@@ -45,7 +45,7 @@ test:do_test(
test:do_test(
"badutf-1.3",
function()
- return test:execsql2("SELECT hex('\xbf') AS x")
+ return test:execsql2("SELECT hex(x'bf') AS x")
end, {
-- <badutf-1.3>
"X", "BF"
@@ -55,7 +55,7 @@ test:do_test(
test:do_test(
"badutf-1.4",
function()
- return test:execsql2("SELECT hex('\xc0') AS x")
+ return test:execsql2("SELECT hex(x'c0') AS x")
end, {
-- <badutf-1.4>
"X", "C0"
@@ -65,7 +65,7 @@ test:do_test(
test:do_test(
"badutf-1.5",
function()
- return test:execsql2("SELECT hex('\xe0') AS x")
+ return test:execsql2("SELECT hex(x'e0') AS x")
end, {
-- <badutf-1.5>
"X", "E0"
@@ -75,7 +75,7 @@ test:do_test(
test:do_test(
"badutf-1.6",
function()
- return test:execsql2("SELECT hex('\xf0') AS x")
+ return test:execsql2("SELECT hex(x'f0') AS x")
end, {
-- <badutf-1.6>
"X", "F0"
@@ -85,7 +85,7 @@ test:do_test(
test:do_test(
"badutf-1.7",
function()
- return test:execsql2("SELECT hex('\xff') AS x")
+ return test:execsql2("SELECT hex(x'ff') AS x")
end, {
-- <badutf-1.7>
"X", "FF"
@@ -304,7 +304,7 @@ test:do_test(
test:do_test(
"badutf-4.1",
function()
- return test:execsql2("SELECT hex(TRIM('\x80\xff' FROM '\x80\x80\x80\xf0\x80\x80\x80\xff')) AS x")
+ return test:execsql2([[SELECT hex(CAST(TRIM(x'80ff' FROM x'808080f0808080ff') AS VARBINARY)) AS x]])
end, {
-- <badutf-4.1>
"X", "F0"
@@ -314,7 +314,8 @@ test:do_test(
test:do_test(
"badutf-4.2",
function()
- return test:execsql2("SELECT hex(TRIM(LEADING '\x80\xff' FROM '\x80\x80\x80\xf0\x80\x80\x80\xff')) AS x")
+ return test:execsql2([[SELECT hex(CAST(TRIM(LEADING x'80ff' FROM ]]..
+ [[x'808080f0808080ff') AS VARBINARY)) AS x]])
end, {
-- <badutf-4.2>
"X", "F0808080FF"
@@ -324,7 +325,8 @@ test:do_test(
test:do_test(
"badutf-4.3",
function()
- return test:execsql2("SELECT hex(TRIM(TRAILING '\x80\xff' FROM '\x80\x80\x80\xf0\x80\x80\x80\xff')) AS x")
+ return test:execsql2([[SELECT hex(CAST(TRIM(TRAILING x'80ff' FROM ]]..
+ [[x'808080f0808080ff') AS VARBINARY)) AS x]])
end, {
-- <badutf-4.3>
"X", "808080F0"
@@ -334,7 +336,8 @@ test:do_test(
test:do_test(
"badutf-4.4",
function()
- return test:execsql2("SELECT hex(TRIM('\xff\x80' FROM '\x80\x80\x80\xf0\x80\x80\x80\xff')) AS x")
+ return test:execsql2([[SELECT hex(CAST(TRIM(x'ff80' FROM ]]..
+ [[x'808080f0808080ff') AS VARBINARY)) AS x]])
end, {
-- <badutf-4.4>
"X", "808080F0808080FF"
@@ -344,7 +347,8 @@ test:do_test(
test:do_test(
"badutf-4.5",
function()
- return test:execsql2("SELECT hex(TRIM('\xff\x80' FROM '\xff\x80\x80\xf0\x80\x80\x80\xff')) AS x")
+ return test:execsql2([[SELECT hex(CAST(TRIM(x'ff80' FROM ]]..
+ [[x'ff8080f0808080ff') AS VARBINARY)) AS x]])
end, {
-- <badutf-4.5>
"X", "80F0808080FF"
@@ -354,7 +358,8 @@ test:do_test(
test:do_test(
"badutf-4.6",
function()
- return test:execsql2("SELECT hex(TRIM('\xff\x80' FROM '\xff\x80\xf0\x80\x80\x80\xff')) AS x")
+ return test:execsql2([[SELECT hex(CAST(TRIM(x'ff80' FROM ]]..
+ [[x'ff80f0808080ff') AS VARBINARY)) AS x]])
end, {
-- <badutf-4.6>
"X", "F0808080FF"
@@ -364,7 +369,8 @@ test:do_test(
test:do_test(
"badutf-4.7",
function()
- return test:execsql2("SELECT hex(TRIM('\xff\x80\x80' FROM '\xff\x80\xf0\x80\x80\x80\xff')) AS x")
+ return test:execsql2([[SELECT hex(CAST(TRIM(x'ff8080' FROM ]]..
+ [[x'ff80f0808080ff') AS VARBINARY)) AS x]])
end, {
-- <badutf-4.7>
"X", "FF80F0808080FF"
diff --git a/test/sql-tap/built-in-functions.test.lua b/test/sql-tap/built-in-functions.test.lua
index c704e71a6..a495a4295 100755
--- a/test/sql-tap/built-in-functions.test.lua
+++ b/test/sql-tap/built-in-functions.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(10)
+test:plan(42)
--
-- Make sure that number of arguments check is checked properly for SQL built-in
@@ -116,4 +116,326 @@ test:do_catchsql_test(
}
)
+-- Make sure static and dynamic argument type checking is working correctly.
+
+test:do_catchsql_test(
+ "builtins-2.1",
+ [[
+ SELECT CHAR_LENGTH(1);
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function CHAR_LENGTH()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.2",
+ function()
+ local res = {pcall(box.execute, [[SELECT CHAR_LENGTH(?);]], {1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to string"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.3",
+ [[
+ SELECT CHARACTER_LENGTH(1);
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function CHARACTER_LENGTH()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.4",
+ function()
+ local res = {pcall(box.execute, [[SELECT CHARACTER_LENGTH(?);]], {1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to string"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.5",
+ [[
+ SELECT CHAR('1');
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function CHAR()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.6",
+ function()
+ local res = {pcall(box.execute, [[SELECT CHAR(?);]], {'1'})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert string('1') to integer"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.7",
+ [[
+ SELECT HEX(1);
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function HEX()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.8",
+ function()
+ local res = {pcall(box.execute, [[SELECT HEX(?);]], {1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to varbinary"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.9",
+ [[
+ SELECT LENGTH(1);
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function LENGTH()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.10",
+ function()
+ local res = {pcall(box.execute, [[SELECT LENGTH(?);]], {1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to string"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.11",
+ [[
+ SELECT 1 LIKE '%';
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function LIKE()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.12",
+ function()
+ local res = {pcall(box.execute, [[SELECT ? LIKE '%';]], {1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to string"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.13",
+ [[
+ SELECT LOWER(1);
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function LOWER()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.14",
+ function()
+ local res = {pcall(box.execute, [[SELECT LOWER(?);]], {1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to string"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.15",
+ [[
+ SELECT UPPER(1);
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function UPPER()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.16",
+ function()
+ local res = {pcall(box.execute, [[SELECT UPPER(?);]], {1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to string"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.17",
+ [[
+ SELECT POSITION(1, 1);
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function POSITION()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.18",
+ function()
+ local res = {pcall(box.execute, [[SELECT POSITION(?, ?);]], {1, 1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to string"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.19",
+ [[
+ SELECT RANDOMBLOB('1');
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function RANDOMBLOB()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.20",
+ function()
+ local res = {pcall(box.execute, [[SELECT RANDOMBLOB(?);]], {'1'})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert string('1') to integer"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.21",
+ [[
+ SELECT ZEROBLOB('1');
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function ZEROBLOB()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.22",
+ function()
+ local res = {pcall(box.execute, [[SELECT ZEROBLOB(?);]], {'1'})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert string('1') to integer"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.23",
+ [[
+ SELECT SOUNDEX(1);
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function SOUNDEX()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.24",
+ function()
+ local res = {pcall(box.execute, [[SELECT SOUNDEX(?);]], {1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to string"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.25",
+ [[
+ SELECT UNICODE(1);
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function UNICODE()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.26",
+ function()
+ local res = {pcall(box.execute, [[SELECT UNICODE(?);]], {1})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert integer(1) to string"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.27",
+ [[
+ SELECT ABS('1');
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function ABS()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.28",
+ function()
+ local res = {pcall(box.execute, [[SELECT ABS(?);]], {'1'})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert string('1') to integer"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.29",
+ [[
+ SELECT ROUND('1');
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function ROUND()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.30",
+ function()
+ local res = {pcall(box.execute, [[SELECT ROUND(?);]], {'1'})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert string('1') to double"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.31",
+ [[
+ SELECT UUID('1');
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function UUID()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.32",
+ function()
+ local res = {pcall(box.execute, [[SELECT UUID(?);]], {'1'})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert string('1') to integer"
+ })
+
test:finish_test()
diff --git a/test/sql-tap/coalesce.test.lua b/test/sql-tap/coalesce.test.lua
index 366e8667a..da2fe22c2 100755
--- a/test/sql-tap/coalesce.test.lua
+++ b/test/sql-tap/coalesce.test.lua
@@ -103,7 +103,7 @@ test:do_test(
"coalesce-1.6",
function()
return test:execsql [[
- SELECT coalesce(b,NOT b,-b,abs(b),lower(b),length(b),LEAST(b,5),b*123,c)
+ SELECT coalesce(b, NOT b, -b, abs(b), LEAST(b, 5), b * 123, c)
FROM t1 ORDER BY a;
]]
end, {
diff --git a/test/sql-tap/cse.test.lua b/test/sql-tap/cse.test.lua
index 2791d2907..07c7d343f 100755
--- a/test/sql-tap/cse.test.lua
+++ b/test/sql-tap/cse.test.lua
@@ -201,20 +201,20 @@ test:do_execsql_test(
test:do_execsql_test(
"cse-1.13",
[[
- SELECT upper(b), typeof(b), b FROM t1
+ SELECT typeof(b), b FROM t1
]], {
-- <cse-1.13>
- "11", "integer", 11, "21", "integer", 21
+ "integer", 11, "integer", 21
-- </cse-1.13>
})
test:do_execsql_test(
"cse-1.14",
[[
- SELECT b, typeof(b), upper(b), typeof(b), b FROM t1
+ SELECT b, typeof(b), typeof(b), b FROM t1
]], {
-- <cse-1.14>
- 11, "integer", "11", "integer", 11, 21, "integer", "21", "integer", 21
+ 11, "integer", "integer", 11, 21, "integer", "integer", 21
-- </cse-1.14>
})
diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua
index 637d67a30..9a192974b 100755
--- a/test/sql-tap/func.test.lua
+++ b/test/sql-tap/func.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(14694)
+test:plan(14686)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -94,7 +94,7 @@ test:do_execsql_test(
test:do_execsql_test(
"func-1.4",
[[
- SELECT coalesce(length(a),-1) FROM t2
+ SELECT coalesce(length(CAST(a AS STRING)),-1) FROM t2
]], {
-- <func-1.4>
1, -1, 3, -1, 5
@@ -356,7 +356,7 @@ test:do_test(
"func-4.1",
function()
test:execsql([[
- CREATE TABLE t1(id integer primary key, a INT,b NUMBER,c NUMBER);
+ CREATE TABLE t1(id integer primary key, a INT,b DOUBLE,c DOUBLE);
INSERT INTO t1(id, a,b,c) VALUES(1, 1,2,3);
INSERT INTO t1(id, a,b,c) VALUES(2, 2,1.2345678901234,-12345.67890);
INSERT INTO t1(id, a,b,c) VALUES(3, 3,-2,-5);
@@ -411,13 +411,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, "Failed to execute SQL statement: wrong arguments for function ABS()"
-- </func-4.4.2>
})
@@ -501,13 +501,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, "Failed to execute SQL statement: wrong arguments for function ROUND()"
-- </func-4.13>
})
@@ -759,16 +759,6 @@ test:do_execsql_test(
-- </func-5.2>
})
-test:do_execsql_test(
- "func-5.3",
- [[
- SELECT upper(a), lower(a) FROM t2
- ]], {
- -- <func-5.3>
- "1","1","","","345","345","","","67890","67890"
- -- </func-5.3>
- })
-
test:do_catchsql_test(
@@ -793,16 +783,6 @@ test:do_execsql_test(
-- </func-6.1>
})
-test:do_execsql_test(
- "func-6.2",
- [[
- SELECT coalesce(upper(a),'nil') FROM t2
- ]], {
- -- <func-6.2>
- "1","nil","345","nil","67890"
- -- </func-6.2>
- })
-
test:do_execsql_test(
"func-6.3",
[[
@@ -1013,7 +993,7 @@ test:do_execsql_test(
test:do_execsql_test(
"func-9.11-utf8",
[[
- SELECT hex(replace('abcdefg','ef','12'))
+ SELECT HEX(CAST(replace('abcdefg','ef','12') AS VARBINARY))
]], {
-- <func-9.11-utf8>
"61626364313267"
@@ -1023,7 +1003,7 @@ test:do_execsql_test(
test:do_execsql_test(
"func-9.12-utf8",
[[
- SELECT hex(replace('abcdefg','','12'))
+ SELECT HEX(CAST(replace('abcdefg','','12') AS VARBINARY))
]], {
-- <func-9.12-utf8>
"61626364656667"
@@ -1033,7 +1013,7 @@ test:do_execsql_test(
test:do_execsql_test(
"func-9.13-utf8",
[[
- SELECT hex(replace('aabcdefg','a','aaa'))
+ SELECT HEX(CAST(replace('aabcdefg','a','aaa') AS VARBINARY))
]], {
-- <func-9.13-utf8>
"616161616161626364656667"
@@ -2018,37 +1998,6 @@ test:do_execsql_test(
-- </func-22.13>
})
---if X(1091, "X!cmd", "[\"expr\",\"[db one {PRAGMA encoding}]==\\\"UTF-8\\\"\"]") then
-test:do_execsql_test(
- "func-22.14",
- [[
- SELECT hex(TRIM(x'6162e1bfbfc280' FROM x'c280e1bfbff48fbfbf6869'))
- ]], {
- -- <func-22.14>
- "F48FBFBF6869"
- -- </func-22.14>
- })
-
-test:do_execsql_test(
- "func-22.15",
- [[SELECT hex(TRIM(x'6162e1bfbfc280f48fbfbf'
- FROM x'6869c280e1bfbff48fbfbf61'))]], {
- -- <func-22.15>
- "6869"
- -- </func-22.15>
- })
-
-test:do_execsql_test(
- "func-22.16",
- [[
- SELECT hex(TRIM(x'ceb1' FROM x'ceb1ceb2ceb3'));
- ]], {
- -- <func-22.16>
- "CEB2CEB3"
- -- </func-22.16>
- })
-
---end
test:do_execsql_test(
"func-22.20",
[[
@@ -2244,24 +2193,6 @@ test:do_catchsql_test(
-- </func-22.38>
})
-test:do_execsql_test(
- "func-22.39",
- [[
- SELECT HEX(TRIM(X'004420'))
- ]], { "4420" })
-
-test:do_execsql_test(
- "func-22.40",
- [[
- SELECT HEX(TRIM(X'00442000'))
- ]], { "4420" })
-
-test:do_execsql_test(
- "func-22.41",
- [[
- SELECT HEX(TRIM(X'442000'))
- ]], { "4420" })
-
-- This is to test the deprecated sql_aggregate_count() API.
--
--test:do_test(
@@ -2870,7 +2801,7 @@ test:do_execsql_test(
-- HEX
test:do_execsql_test(
"func-68",
- "SELECT HEX(CHAR(00,65,00,65,00));",
+ "SELECT HEX(CAST(CHAR(00,65,00,65,00) AS VARBINARY));",
{'0041004100'})
-- TRIM
@@ -2931,7 +2862,7 @@ test:do_catchsql_test(
SELECT ROUND(X'FF')
]], {
-- <func-76.1>
- 1, "Type mismatch: can not convert varbinary(x'FF') to number"
+ 1, "Failed to execute SQL statement: wrong arguments for function ROUND()"
-- </func-76.1>
})
@@ -2941,7 +2872,7 @@ test:do_catchsql_test(
SELECT RANDOMBLOB(X'FF')
]], {
-- <func-76.2>
- 1, "Type mismatch: can not convert varbinary(x'FF') to number"
+ 1, "Failed to execute SQL statement: wrong arguments for function RANDOMBLOB()"
-- </func-76.2>
})
@@ -2951,7 +2882,7 @@ test:do_catchsql_test(
SELECT SOUNDEX(X'FF')
]], {
-- <func-76.3>
- 1, "Type mismatch: can not convert varbinary(x'FF') to string"
+ 1, "Failed to execute SQL statement: wrong arguments for function SOUNDEX()"
-- </func-76.3>
})
diff --git a/test/sql-tap/orderby1.test.lua b/test/sql-tap/orderby1.test.lua
index 33f437670..bb5ea2d25 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 6877d08fa..6a96ed9bc 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 string or varbinary got integer(12345)"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </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 string or varbinary got double(123456.78)"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </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 string or varbinary got double(123456.78)"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.25>
})
@@ -305,130 +305,130 @@ test:do_test(
test:do_test(
"position-1.31",
function()
- return test:execsql "SELECT position(x'01', x'0102030405');"
+ return test:catchsql "SELECT position(x'01', x'0102030405');"
end, {
-- <position-1.31>
- 1
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.31>
})
test:do_test(
"position-1.32",
function()
- return test:execsql "SELECT position(x'02', x'0102030405');"
+ return test:catchsql "SELECT position(x'02', x'0102030405');"
end, {
-- <position-1.32>
- 2
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.32>
})
test:do_test(
"position-1.33",
function()
- return test:execsql "SELECT position(x'03', x'0102030405');"
+ return test:catchsql "SELECT position(x'03', x'0102030405');"
end, {
-- <position-1.33>
- 3
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.33>
})
test:do_test(
"position-1.34",
function()
- return test:execsql "SELECT position(x'04', x'0102030405');"
+ return test:catchsql "SELECT position(x'04', x'0102030405');"
end, {
-- <position-1.34>
- 4
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.34>
})
test:do_test(
"position-1.35",
function()
- return test:execsql "SELECT position(x'05', x'0102030405');"
+ return test:catchsql "SELECT position(x'05', x'0102030405');"
end, {
-- <position-1.35>
- 5
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.35>
})
test:do_test(
"position-1.36",
function()
- return test:execsql "SELECT position(x'06', x'0102030405');"
+ return test:catchsql "SELECT position(x'06', x'0102030405');"
end, {
-- <position-1.36>
- 0
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.36>
})
test:do_test(
"position-1.37",
function()
- return test:execsql "SELECT position(x'0102030405', x'0102030405');"
+ return test:catchsql "SELECT position(x'0102030405', x'0102030405');"
end, {
-- <position-1.37>
- 1
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.37>
})
test:do_test(
"position-1.38",
function()
- return test:execsql "SELECT position(x'02030405', x'0102030405');"
+ return test:catchsql "SELECT position(x'02030405', x'0102030405');"
end, {
-- <position-1.38>
- 2
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.38>
})
test:do_test(
"position-1.39",
function()
- return test:execsql "SELECT position(x'030405', x'0102030405');"
+ return test:catchsql "SELECT position(x'030405', x'0102030405');"
end, {
-- <position-1.39>
- 3
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.39>
})
test:do_test(
"position-1.40",
function()
- return test:execsql "SELECT position(x'0405', x'0102030405');"
+ return test:catchsql "SELECT position(x'0405', x'0102030405');"
end, {
-- <position-1.40>
- 4
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.40>
})
test:do_test(
"position-1.41",
function()
- return test:execsql "SELECT position(x'0506', x'0102030405');"
+ return test:catchsql "SELECT position(x'0506', x'0102030405');"
end, {
-- <position-1.41>
- 0
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.41>
})
test:do_test(
"position-1.42",
function()
- return test:execsql "SELECT position(x'', x'0102030405');"
+ return test:catchsql "SELECT position(x'', x'0102030405');"
end, {
-- <position-1.42>
- 1
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.42>
})
test:do_test(
"position-1.43",
function()
- return test:execsql "SELECT position(x'', x'');"
+ return test:catchsql "SELECT position(x'', x'');"
end, {
-- <position-1.43>
- 1
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.43>
})
@@ -554,7 +554,7 @@ test:do_test(
return test:catchsql("SELECT position('x', x'78c3a4e282ac79');")
end, {
-- <position-1.54>
- 1, "Inconsistent types: expected string got varbinary(x'78C3A4E282AC79')"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.54>
})
@@ -564,47 +564,47 @@ test:do_test(
return test:catchsql "SELECT position('y', x'78c3a4e282ac79');"
end, {
-- <position-1.55>
- 1, "Inconsistent types: expected string got varbinary(x'78C3A4E282AC79')"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.55>
})
test:do_test(
"position-1.56.1",
function()
- return test:execsql "SELECT position(x'79', x'78c3a4e282ac79');"
+ return test:catchsql "SELECT position(x'79', x'78c3a4e282ac79');"
end, {
-- <position-1.56.1>
- 7
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.56.1>
})
test:do_test(
"position-1.56.2",
function()
- return test:execsql "SELECT position(x'7a', x'78c3a4e282ac79');"
+ return test:catchsql "SELECT position(x'7a', x'78c3a4e282ac79');"
end, {
-- <position-1.56.2>
- 0
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.56.2>
})
test:do_test(
"position-1.56.3",
function()
- return test:execsql "SELECT position(x'78', x'78c3a4e282ac79');"
+ return test:catchsql "SELECT position(x'78', x'78c3a4e282ac79');"
end, {
-- <position-1.56.3>
- 1
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.56.3>
})
test:do_test(
"position-1.56.3",
function()
- return test:execsql "SELECT position(x'a4', x'78c3a4e282ac79');"
+ return test:catchsql "SELECT position(x'a4', x'78c3a4e282ac79');"
end, {
-- <position-1.56.3>
- 3
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.56.3>
})
@@ -614,7 +614,7 @@ test:do_test(
return test:catchsql "SELECT position(x'79', 'xä€y');"
end, {
-- <position-1.57.1>
- 1, "Inconsistent types: expected varbinary got string('xä€y')"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.57.1>
})
@@ -624,7 +624,7 @@ test:do_test(
return test:catchsql "SELECT position(x'a4', 'xä€y');"
end, {
-- <position-1.57.2>
- 1, "Inconsistent types: expected varbinary got string('xä€y')"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.57.2>
})
@@ -634,7 +634,7 @@ test:do_test(
return test:catchsql "SELECT position('y', x'78c3a4e282ac79');"
end, {
-- <position-1.57.3>
- 1, "Inconsistent types: expected string got varbinary(x'78C3A4E282AC79')"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
-- </position-1.57.3>
})
diff --git a/test/sql-tap/sql-errors.test.lua b/test/sql-tap/sql-errors.test.lua
index daf0ee643..b617c5384 100755
--- a/test/sql-tap/sql-errors.test.lua
+++ b/test/sql-tap/sql-errors.test.lua
@@ -869,7 +869,7 @@ test:do_catchsql_test(
[[
SELECT POSITION('abc', 2);
]], {
- 1, "Inconsistent types: expected string or varbinary got integer(2)"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
})
test:finish_test()
diff --git a/test/sql-tap/substr.test.lua b/test/sql-tap/substr.test.lua
index 72224fd7b..f64eb94be 100755
--- a/test/sql-tap/substr.test.lua
+++ b/test/sql-tap/substr.test.lua
@@ -56,12 +56,14 @@ local function subblob_test(id, hex, i1, i2, hexresult)
test:execsql(sql)
test:do_execsql_test(
"substr-"..id..".1",
- string.format("SELECT hex(substr(b, %s, %s)) FROM t1", i1, i2),
+ string.format(
+ "SELECT HEX(CAST(substr(b, %s, %s) AS VARBINARY)) FROM t1", i1, i2),
{hexresult})
--local qstr = string.gsub("' '", string)--"string","map","' ''",["string"]]]=]).."'"
test:do_execsql_test(
"substr-"..id..".2",
- string.format("SELECT hex(substr(x'%s', %s, %s))",hex, i1, i2),
+ string.format("SELECT HEX(CAST(substr(x'%s', %s, %s) AS VARBINARY))",
+ hex, i1, i2),
{hexresult})
end
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
index 92691773d..998213dbb 100755
--- a/test/sql-tap/uuid.test.lua
+++ b/test/sql-tap/uuid.test.lua
@@ -229,7 +229,7 @@ test:do_catchsql_test(
[[
SELECT ABS(u) from t2;
]], {
- 1, "Inconsistent types: expected number got uuid('11111111-1111-1111-1111-111111111111')"
+ 1, "Failed to execute SQL statement: wrong arguments for function ABS()"
})
test:do_catchsql_test(
@@ -240,28 +240,28 @@ test:do_catchsql_test(
1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.3",
[[
SELECT CHAR(u) from t2;
]], {
- "\0", "\0", "\0"
+ 1, "Failed to execute SQL statement: wrong arguments for function CHAR()"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.4",
[[
SELECT CHARACTER_LENGTH(u) from t2;
]], {
- 36, 36, 36
+ 1, "Failed to execute SQL statement: wrong arguments for function CHARACTER_LENGTH()"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.5",
[[
SELECT CHAR_LENGTH(u) from t2;
]], {
- 36, 36, 36
+ 1, "Failed to execute SQL statement: wrong arguments for function CHAR_LENGTH()"
})
test:do_execsql_test(
@@ -298,14 +298,12 @@ test:do_execsql_test(
"22222222-1111-1111-1111-111111111111"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.10",
[[
SELECT HEX(u) from t2;
]], {
- "11111111111111111111111111111111",
- "11111111333311111111111111111111",
- "22222222111111111111111111111111"
+ 1, "Failed to execute SQL statement: wrong arguments for function HEX()"
})
test:do_execsql_test(
@@ -324,12 +322,12 @@ test:do_execsql_test(
uuid1
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.13",
[[
SELECT LENGTH(u) from t2;
]], {
- 36, 36, 36
+ 1, "Failed to execute SQL statement: wrong arguments for function LENGTH()"
})
test:do_catchsql_test(
@@ -337,7 +335,7 @@ test:do_catchsql_test(
[[
SELECT u LIKE 'a' from t2;
]], {
- 1, "Inconsistent types: expected string got uuid('11111111-1111-1111-1111-111111111111')"
+ 1, "Failed to execute SQL statement: wrong arguments for function LIKE()"
})
test:do_execsql_test(
@@ -356,14 +354,12 @@ test:do_execsql_test(
uuid1, uuid3, uuid2
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.17",
[[
SELECT LOWER(u) from t2;
]], {
- "11111111-1111-1111-1111-111111111111",
- "11111111-3333-1111-1111-111111111111",
- "22222222-1111-1111-1111-111111111111"
+ 1, "Failed to execute SQL statement: wrong arguments for function LOWER()"
})
test:do_execsql_test(
@@ -395,15 +391,15 @@ test:do_catchsql_test(
[[
SELECT POSITION(u, '1') from t2;
]], {
- 1, "Inconsistent types: expected string or varbinary got uuid('11111111-1111-1111-1111-111111111111')"
+ 1, "Failed to execute SQL statement: wrong arguments for function POSITION()"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.22",
[[
SELECT RANDOMBLOB(u) from t2;
]], {
- "", "", ""
+ 1, "Failed to execute SQL statement: wrong arguments for function RANDOMBLOB()"
})
test:do_execsql_test(
@@ -421,15 +417,15 @@ test:do_catchsql_test(
[[
SELECT ROUND(u) from t2;
]], {
- 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
+ 1, "Failed to execute SQL statement: wrong arguments for function ROUND()"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.25",
[[
SELECT SOUNDEX(u) from t2;
]], {
- "?000", "?000", "?000"
+ 1, "Failed to execute SQL statement: wrong arguments for function SOUNDEX()"
})
test:do_execsql_test(
@@ -474,12 +470,12 @@ test:do_execsql_test(
"uuid", "uuid", "uuid"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.31",
[[
SELECT UNICODE(u) from t2;
]], {
- 49, 49, 50
+ 1, "Failed to execute SQL statement: wrong arguments for function UNICODE()"
})
test:do_execsql_test(
@@ -490,14 +486,12 @@ test:do_execsql_test(
uuid1, uuid3, uuid2
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.33",
[[
SELECT UPPER(u) from t2;
]], {
- "11111111-1111-1111-1111-111111111111",
- "11111111-3333-1111-1111-111111111111",
- "22222222-1111-1111-1111-111111111111"
+ 1, "Failed to execute SQL statement: wrong arguments for function UPPER()"
})
test:do_catchsql_test(
@@ -1282,7 +1276,7 @@ test:do_catchsql_test(
[[
SELECT uuid('asd');
]], {
- 1, "Type mismatch: can not convert string('asd') to integer"
+ 1, "Failed to execute SQL statement: wrong arguments for function UUID()"
})
test:do_catchsql_test(
diff --git a/test/sql-tap/where3.test.lua b/test/sql-tap/where3.test.lua
index 9b2bc2e25..7e11aaee9 100755
--- a/test/sql-tap/where3.test.lua
+++ b/test/sql-tap/where3.test.lua
@@ -401,9 +401,9 @@ if 0
test:do_execsql_test(
"where3-4.0",
[[
- CREATE TABLE t400(a INTEGER PRIMARY KEY, b INT , c INT );
- CREATE TABLE t401(p INTEGER PRIMARY KEY, q INT , r INT );
- CREATE TABLE t402(x INTEGER PRIMARY KEY, y INT , z INT );
+ CREATE TABLE t400(a INTEGER PRIMARY KEY, b INT , c STRING );
+ CREATE TABLE t401(p INTEGER PRIMARY KEY, q INT , r STRING );
+ CREATE TABLE t402(x INTEGER PRIMARY KEY, y INT , z STRING );
EXPLAIN QUERY PLAN
SELECT * FROM t400, t401, t402 WHERE t402.z LIKE 'abc%';
]], {
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index a9ce37e11..f9350d2f2 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -282,29 +282,17 @@ SELECT is_boolean('true');
SELECT abs(a) FROM t0;
| ---
| - null
- | - 'Inconsistent types: expected number got boolean(FALSE)'
+ | - 'Failed to execute SQL statement: wrong arguments for function ABS()'
| ...
SELECT lower(a) FROM t0;
| ---
- | - metadata:
- | - name: COLUMN_1
- | type: string
- | rows:
- | - ['false']
- | - ['true']
- | - [null]
- | - [null]
+ | - null
+ | - 'Failed to execute SQL statement: wrong arguments for function LOWER()'
| ...
SELECT upper(a) FROM t0;
| ---
- | - metadata:
- | - name: COLUMN_1
- | type: string
- | rows:
- | - ['FALSE']
- | - ['TRUE']
- | - [null]
- | - [null]
+ | - null
+ | - 'Failed to execute SQL statement: wrong arguments for function UPPER()'
| ...
SELECT quote(a) FROM t0;
| ---
@@ -320,14 +308,8 @@ SELECT quote(a) FROM t0;
-- gh-4462: LENGTH didn't take BOOLEAN arguments.
SELECT length(a) FROM t0;
| ---
- | - metadata:
- | - name: COLUMN_1
- | type: integer
- | rows:
- | - [5]
- | - [4]
- | - [null]
- | - [null]
+ | - null
+ | - 'Failed to execute SQL statement: wrong arguments for function LENGTH()'
| ...
SELECT typeof(a) FROM t0;
| ---
diff --git a/test/sql/prepared.result b/test/sql/prepared.result
index ecc16f597..d38b47c61 100644
--- a/test/sql/prepared.result
+++ b/test/sql/prepared.result
@@ -432,7 +432,7 @@ execute(s.stmt_id)
| - name: COLUMN_3
| type: scalar
| - name: COLUMN_4
- | type: number
+ | type: integer
| rows:
| - [1, 1, '3', 1]
| ...
@@ -446,7 +446,7 @@ execute(s.stmt_id)
| - name: COLUMN_3
| type: scalar
| - name: COLUMN_4
- | type: number
+ | type: integer
| rows:
| - [1, 1, '3', 1]
| ...
diff --git a/test/sql/types.result b/test/sql/types.result
index 68bdcd62e..83cec6592 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -215,25 +215,22 @@ box.execute("INSERT INTO t1 VALUES (randomblob(5));")
box.execute("SELECT * FROM t1 WHERE s LIKE 'blob';")
---
- null
-- 'Inconsistent types: expected string got scalar(x''91A0FEE366'')'
+- 'Failed to execute SQL statement: wrong arguments for function LIKE()'
...
box.execute("SELECT * FROM t1 WHERE 'blob' LIKE s;")
---
- null
-- 'Inconsistent types: expected string got scalar(x''91A0FEE366'')'
+- 'Failed to execute SQL statement: wrong arguments for function LIKE()'
...
box.execute("SELECT * FROM t1 WHERE 'blob' LIKE x'0000';")
---
- null
-- 'Inconsistent types: expected string got varbinary(x''0000'')'
+- 'Failed to execute SQL statement: wrong arguments for function LIKE()'
...
box.execute("SELECT s LIKE NULL FROM t1;")
---
-- metadata:
- - name: COLUMN_1
- type: integer
- rows:
- - [null]
+- null
+- 'Failed to execute SQL statement: wrong arguments for function LIKE()'
...
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 string got scalar(1)'
+- 'Failed to execute SQL statement: wrong arguments for function LIKE()'
...
box.execute("SELECT * FROM t1 WHERE 'int' LIKE 4;")
---
- null
-- 'Inconsistent types: expected string got integer(4)'
+- 'Failed to execute SQL statement: wrong arguments for function LIKE()'
...
box.execute("SELECT NULL LIKE s FROM t1;")
---
-- metadata:
- - name: COLUMN_1
- type: integer
- rows:
- - [null]
+- null
+- 'Failed to execute SQL statement: wrong arguments for function LIKE()'
...
box.space.T1:drop()
---
@@ -825,25 +819,19 @@ box.execute("DELETE FROM t WHERE i < 18446744073709551613;")
...
box.execute("SELECT lower(i) FROM t;")
---
-- metadata:
- - name: COLUMN_1
- type: string
- rows:
- - ['18446744073709551613']
+- null
+- 'Failed to execute SQL statement: wrong arguments for function LOWER()'
...
box.execute("SELECT upper(i) FROM t;")
---
-- metadata:
- - name: COLUMN_1
- type: string
- rows:
- - ['18446744073709551613']
+- null
+- 'Failed to execute SQL statement: wrong arguments for function UPPER()'
...
box.execute("SELECT abs(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: number
+ type: integer
rows:
- [18446744073709551613]
...
@@ -1298,17 +1286,17 @@ box.execute("SELECT group_concat(v) FROM t;")
box.execute("SELECT lower(v) FROM t;")
---
- null
-- 'Inconsistent types: expected string got varbinary(x''616263'')'
+- 'Failed to execute SQL statement: wrong arguments for function LOWER()'
...
box.execute("SELECT upper(v) FROM t;")
---
- null
-- 'Inconsistent types: expected string got varbinary(x''616263'')'
+- 'Failed to execute SQL statement: wrong arguments for function UPPER()'
...
box.execute("SELECT abs(v) FROM t;")
---
- null
-- 'Inconsistent types: expected number got varbinary(x''616263'')'
+- 'Failed to execute SQL statement: wrong arguments for function ABS()'
...
box.execute("SELECT typeof(v) FROM t;")
---
@@ -1861,31 +1849,19 @@ box.execute("SELECT group_concat(d) FROM t;")
...
box.execute("SELECT lower(d) FROM t;")
---
-- metadata:
- - name: COLUMN_1
- type: string
- rows:
- - ['10.0']
- - ['-2.0']
- - ['3.3']
- - ['1.8e+19']
+- null
+- 'Failed to execute SQL statement: wrong arguments for function LOWER()'
...
box.execute("SELECT upper(d) FROM t;")
---
-- metadata:
- - name: COLUMN_1
- type: string
- rows:
- - ['10.0']
- - ['-2.0']
- - ['3.3']
- - ['1.8E+19']
+- null
+- 'Failed to execute SQL statement: wrong arguments for function UPPER()'
...
box.execute("SELECT abs(d) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: number
+ type: double
rows:
- [10]
- [2]
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* [Tarantool-patches] [PATCH v1 07/10] sql: fix result type of min() and max() functions
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
` (5 preceding siblings ...)
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 06/10] sql: enable types checking for some functions Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 08/10] sql: check argument types of sum(), avg(), total() Mergen Imeev via Tarantool-patches
` (3 subsequent siblings)
10 siblings, 0 replies; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
Prior to this, the patch functions MIN(), MAX(), LEAST() and GREATEST()
showed SCALAR as result types in metadata. However, in reality, the type
of the result could be any scalar type. After this patch, the type of
the result will always be the same as the type in the metadata. Also,
for MIN() and MAX() functions, the type of the result will be the same
as the type of the argument. For the LEAST() and GREATEST() functions,
the result type will be the same as the type of the arguments if all
arguments are of the same type, or it will be NUMBER if all arguments
are of numeric types, or it will be SCALAR.
Part of #6105
---
src/box/sql/func.c | 59 ++++++++++++++++++++++--
test/sql-tap/built-in-functions.test.lua | 49 +++++++++++++++++++-
test/sql-tap/minmax2.test.lua | 3 +-
test/sql-tap/select3.test.lua | 2 +-
test/sql/iproto.result | 4 +-
test/sql/max-on-index.result | 6 +--
test/sql/prepared.result | 4 +-
test/sql/types.result | 18 ++++----
8 files changed, 122 insertions(+), 23 deletions(-)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index b22edda20..7ecbb6203 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -2023,13 +2023,37 @@ static struct sql_func_definition definitions[] = {
{"COUNT", 0, {}, FIELD_TYPE_INTEGER, countStep, countFinalize},
{"COUNT", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, countStep,
countFinalize},
- {"GREATEST", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
+
+ {"GREATEST", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxFunc,
+ NULL},
+ {"GREATEST", -1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, minmaxFunc,
+ NULL},
+ {"GREATEST", -1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, minmaxFunc,
+ NULL},
+ {"GREATEST", -1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY,
+ minmaxFunc, NULL},
+ {"GREATEST", -1, {FIELD_TYPE_UUID}, FIELD_TYPE_UUID, minmaxFunc, NULL},
+ {"GREATEST", -1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, minmaxFunc,
+ NULL},
+ {"GREATEST", -1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxFunc,
+ NULL},
+
{"GROUP_CONCAT", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY},
FIELD_TYPE_STRING, groupConcatStep, groupConcatFinalize},
{"HEX", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_STRING, hexFunc, NULL},
{"IFNULL", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
sql_builtin_stub, NULL},
- {"LEAST", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
+
+ {"LEAST", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxFunc,
+ NULL},
+ {"LEAST", -1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, minmaxFunc, NULL},
+ {"LEAST", -1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, minmaxFunc, NULL},
+ {"LEAST", -1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY, minmaxFunc,
+ NULL},
+ {"LEAST", -1, {FIELD_TYPE_UUID}, FIELD_TYPE_UUID, minmaxFunc, NULL},
+ {"LEAST", -1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, minmaxFunc, NULL},
+ {"LEAST", -1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxFunc, NULL},
+
{"LENGTH", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, lengthFunc,
NULL},
{"LENGTH", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_INTEGER, lengthFunc,
@@ -2044,10 +2068,37 @@ static struct sql_func_definition definitions[] = {
NULL},
{"LOWER", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, LowerICUFunc,
NULL},
- {"MAX", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxStep,
+
+ {"MAX", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxStep,
+ minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, minmaxStep,
minMaxFinalize},
- {"MIN", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR, minmaxStep,
+ {"MAX", 1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, minmaxStep,
minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY, minmaxStep,
+ minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_UUID}, FIELD_TYPE_UUID, minmaxStep,
+ minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, minmaxStep,
+ minMaxFinalize},
+ {"MAX", 1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxStep,
+ minMaxFinalize},
+
+ {"MIN", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_UUID}, FIELD_TYPE_UUID, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, minmaxStep,
+ minMaxFinalize},
+ {"MIN", 1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxStep,
+ minMaxFinalize},
+
{"NULLIF", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
nullifFunc, NULL},
{"POSITION", 2, {FIELD_TYPE_STRING, FIELD_TYPE_STRING},
diff --git a/test/sql-tap/built-in-functions.test.lua b/test/sql-tap/built-in-functions.test.lua
index a495a4295..353030278 100755
--- a/test/sql-tap/built-in-functions.test.lua
+++ b/test/sql-tap/built-in-functions.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(42)
+test:plan(46)
--
-- Make sure that number of arguments check is checked properly for SQL built-in
@@ -438,4 +438,51 @@ test:do_test(
"Type mismatch: can not convert string('1') to integer"
})
+--
+-- Make sure that the type of result of MAX() and MIN() is the same as the type
+-- of the argument.
+--
+test:do_execsql_test(
+ "builtins-3.1",
+ [[
+ SELECT TYPEOF(1), TYPEOF(MAX(1)), TYPEOF(MIN(1));
+ ]],
+ {
+ 'integer', 'integer', 'integer'
+ }
+)
+
+test:do_test(
+ "builtins-3.2",
+ function()
+ return box.execute([[SELECT 1, MAX(1), MIN(1);]]).metadata
+ end, {
+ {name = "COLUMN_1", type = "integer"},
+ {name = "COLUMN_2", type = "integer"},
+ {name = "COLUMN_3", type = "integer"}
+ })
+
+--
+-- Make sure that the type of result of GREATEST() and LEAST() depends on type
+-- of arguments.
+--
+test:do_execsql_test(
+ "builtins-3.3",
+ [[
+ SELECT TYPEOF(GREATEST('1', 1)), TYPEOF(LEAST('1', 1));
+ ]],
+ {
+ 'scalar', 'scalar'
+ }
+)
+
+test:do_test(
+ "builtins-3.4",
+ function()
+ return box.execute([[SELECT GREATEST('1', 1), LEAST('1', 1);]]).metadata
+ end, {
+ {name = "COLUMN_1", type = "scalar"},
+ {name = "COLUMN_2", type = "scalar"},
+ })
+
test:finish_test()
diff --git a/test/sql-tap/minmax2.test.lua b/test/sql-tap/minmax2.test.lua
index 42dbafea8..0b9f5a63c 100755
--- a/test/sql-tap/minmax2.test.lua
+++ b/test/sql-tap/minmax2.test.lua
@@ -278,7 +278,8 @@ test:do_execsql_test(
test:do_execsql_test(
"minmax2-4.1",
[[
- SELECT coalesce(min(x),-1), coalesce(max(x),-1) FROM
+ SELECT coalesce(min(CAST(x AS INTEGER)),-1),
+ coalesce(max(CAST(x AS INTEGER)),-1) FROM
(SELECT x, y FROM t1 UNION SELECT NULL as "x", NULL as "y")
]], {
-- <minmax2-4.1>
diff --git a/test/sql-tap/select3.test.lua b/test/sql-tap/select3.test.lua
index ffba58ec2..9880c1f69 100755
--- a/test/sql-tap/select3.test.lua
+++ b/test/sql-tap/select3.test.lua
@@ -271,7 +271,7 @@ test:do_execsql_test("select3-5.1", [[
test:do_execsql_test("select3-5.2", [[
SELECT log, count(*), avg(n), max(n+log*2) FROM t1
GROUP BY log
- ORDER BY max(n+log*2)+0, GREATEST(log,avg(n))+0
+ ORDER BY max(n+log*2)+0, CAST(GREATEST(log,avg(n)) AS DOUBLE)+0
]], {
-- <select3-5.2>
0, 1, 1.0, 1, 1, 1, 2.0, 4, 2, 2, 3.5, 8, 3, 4, 6.5, 14, 4, 8, 12.5, 24, 5, 15, 24.0, 41
diff --git a/test/sql/iproto.result b/test/sql/iproto.result
index 4e9e12ad7..6212aa0c0 100644
--- a/test/sql/iproto.result
+++ b/test/sql/iproto.result
@@ -761,7 +761,7 @@ cn:execute("SELECT LEAST(1, 2, 3);")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [1]
...
@@ -769,7 +769,7 @@ cn:execute("SELECT GREATEST(1, 2, 3);")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [3]
...
diff --git a/test/sql/max-on-index.result b/test/sql/max-on-index.result
index a90f30993..fcb5923dd 100644
--- a/test/sql/max-on-index.result
+++ b/test/sql/max-on-index.result
@@ -49,7 +49,7 @@ box.execute("SELECT MAX(f1) FROM test1")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [4]
...
@@ -57,7 +57,7 @@ box.execute("SELECT MAX(f2) FROM test1")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [3]
...
@@ -65,7 +65,7 @@ box.execute("SELECT MAX(f1) FROM test2")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [1]
...
diff --git a/test/sql/prepared.result b/test/sql/prepared.result
index d38b47c61..c4b09e514 100644
--- a/test/sql/prepared.result
+++ b/test/sql/prepared.result
@@ -430,7 +430,7 @@ execute(s.stmt_id)
| - name: COLUMN_2
| type: integer
| - name: COLUMN_3
- | type: scalar
+ | type: string
| - name: COLUMN_4
| type: integer
| rows:
@@ -444,7 +444,7 @@ execute(s.stmt_id)
| - name: COLUMN_2
| type: integer
| - name: COLUMN_3
- | type: scalar
+ | type: string
| - name: COLUMN_4
| type: integer
| rows:
diff --git a/test/sql/types.result b/test/sql/types.result
index 83cec6592..5f0ce3543 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -785,7 +785,7 @@ box.execute("SELECT min(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [-1]
...
@@ -793,7 +793,7 @@ box.execute("SELECT max(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [18446744073709551613]
...
@@ -855,7 +855,7 @@ box.execute("SELECT LEAST(-1, i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: integer
rows:
- [-1]
...
@@ -1255,7 +1255,7 @@ box.execute("SELECT min(v) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: varbinary
rows:
- ['abc']
...
@@ -1263,7 +1263,7 @@ box.execute("SELECT max(v) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: varbinary
rows:
- ['abc']
...
@@ -1318,7 +1318,7 @@ box.execute("SELECT LEAST(v, x'') FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: varbinary
rows:
- ['']
...
@@ -1819,7 +1819,7 @@ box.execute("SELECT min(d) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: double
rows:
- [-2]
...
@@ -1827,7 +1827,7 @@ box.execute("SELECT max(d) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: double
rows:
- [18000000000000000000]
...
@@ -1894,7 +1894,7 @@ box.execute("SELECT LEAST(d, 0) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: scalar
+ type: number
rows:
- [0]
- [-2]
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* [Tarantool-patches] [PATCH v1 08/10] sql: check argument types of sum(), avg(), total()
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
` (6 preceding siblings ...)
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 07/10] sql: fix result type of min() and max() functions Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 09/10] sql: fix quote() function Mergen Imeev via Tarantool-patches
` (2 subsequent siblings)
10 siblings, 0 replies; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
This patch enables static and dynamic type check for aggregate functions
SUM(), AVG() and TOTAL().
Part of #6105
---
src/box/sql/func.c | 88 +++++++++++-------------
test/sql-tap/built-in-functions.test.lua | 62 ++++++++++++++++-
test/sql-tap/e_select1.test.lua | 2 +-
test/sql-tap/func.test.lua | 80 ++-------------------
test/sql-tap/minmax2.test.lua | 2 +-
test/sql-tap/minmax4.test.lua | 2 +-
test/sql-tap/null.test.lua | 2 +-
test/sql-tap/select1.test.lua | 2 +-
test/sql-tap/select3.test.lua | 14 ++--
test/sql-tap/select5.test.lua | 2 +-
test/sql-tap/select6.test.lua | 21 +++---
test/sql-tap/selectG.test.lua | 2 +-
test/sql-tap/subquery.test.lua | 8 +--
test/sql-tap/uuid.test.lua | 6 +-
test/sql/boolean.result | 6 +-
test/sql/types.result | 20 +++---
16 files changed, 150 insertions(+), 169 deletions(-)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 7ecbb6203..8d9c5a7d9 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -1633,13 +1633,8 @@ soundexFunc(sql_context * context, int argc, sql_value ** argv)
*/
typedef struct SumCtx SumCtx;
struct SumCtx {
- double rSum; /* Floating point sum */
- int64_t iSum; /* Integer sum */
- /** True if iSum < 0. */
- bool is_neg;
- i64 cnt; /* Number of elements summed */
- u8 overflow; /* True if integer overflow seen */
- u8 approx; /* True if non-integer value was input to the sum */
+ struct Mem mem;
+ uint32_t count;
};
/*
@@ -1658,34 +1653,25 @@ sum_step(struct sql_context *context, int argc, sql_value **argv)
assert(argc == 1);
UNUSED_PARAMETER(argc);
struct SumCtx *p = sql_aggregate_context(context, sizeof(*p));
- int type = sql_value_type(argv[0]);
- if (type == MP_NIL || p == NULL)
+ if (p == NULL) {
+ context->is_aborted = true;
return;
- if (type != MP_DOUBLE && type != MP_INT && type != MP_UINT) {
- if (type != MP_STR || mem_to_number(argv[0]) != 0) {
- diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
- mem_str(argv[0]), "number");
- context->is_aborted = true;
- return;
- }
- type = sql_value_type(argv[0]);
}
- p->cnt++;
- if (type == MP_INT || type == MP_UINT) {
- int64_t v = mem_get_int_unsafe(argv[0]);
- if (type == MP_INT)
- p->rSum += v;
+ if (p->count == 0) {
+ mem_create(&p->mem);
+ assert(context->func->def->returns == FIELD_TYPE_INTEGER ||
+ context->func->def->returns == FIELD_TYPE_DOUBLE);
+ if (context->func->def->returns == FIELD_TYPE_INTEGER)
+ mem_set_uint(&p->mem, 0);
else
- p->rSum += (uint64_t) v;
- if ((p->approx | p->overflow) == 0 &&
- sql_add_int(p->iSum, p->is_neg, v, type == MP_INT, &p->iSum,
- &p->is_neg) != 0) {
- p->overflow = 1;
- }
- } else {
- p->rSum += mem_get_double_unsafe(argv[0]);
- p->approx = 1;
+ mem_set_double(&p->mem, 0.0);
}
+ if (argv[0]->type == MEM_TYPE_NULL)
+ return;
+ ++p->count;
+ assert(mem_is_num(argv[0]));
+ if (mem_add(&p->mem, argv[0], &p->mem) != 0)
+ context->is_aborted = true;
}
static void
@@ -1693,17 +1679,10 @@ sumFinalize(sql_context * context)
{
SumCtx *p;
p = sql_aggregate_context(context, 0);
- if (p && p->cnt > 0) {
- if (p->overflow) {
- diag_set(ClientError, ER_SQL_EXECUTE, "integer "\
- "overflow");
- context->is_aborted = true;
- } else if (p->approx) {
- sql_result_double(context, p->rSum);
- } else {
- mem_set_int(context->pOut, p->iSum, p->is_neg);
- }
- }
+ if (p == NULL || p->count == 0)
+ mem_set_null(context->pOut);
+ else
+ mem_copy_as_ephemeral(context->pOut, &p->mem);
}
static void
@@ -1711,9 +1690,15 @@ avgFinalize(sql_context * context)
{
SumCtx *p;
p = sql_aggregate_context(context, 0);
- if (p && p->cnt > 0) {
- sql_result_double(context, p->rSum / (double)p->cnt);
+ if (p == NULL || p->count == 0) {
+ mem_set_null(context->pOut);
+ return;
}
+ struct Mem mem;
+ mem_create(&mem);
+ mem_set_uint(&mem, p->count);
+ if (mem_div(&p->mem, &mem, context->pOut) != 0)
+ context->is_aborted = true;
}
static void
@@ -1721,7 +1706,10 @@ totalFinalize(sql_context * context)
{
SumCtx *p;
p = sql_aggregate_context(context, 0);
- sql_result_double(context, p ? p->rSum : (double)0);
+ if (p == NULL || p->count == 0)
+ mem_set_double(context->pOut, 0.0);
+ else
+ mem_copy_as_ephemeral(context->pOut, &p->mem);
}
/*
@@ -2014,7 +2002,8 @@ struct sql_func_definition {
static struct sql_func_definition definitions[] = {
{"ABS", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, absFunc, NULL},
{"ABS", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, absFunc, NULL},
- {"AVG", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step, avgFinalize},
+ {"AVG", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, sum_step, avgFinalize},
+ {"AVG", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, sum_step, avgFinalize},
{"CHAR", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, charFunc, NULL},
{"CHAR_LENGTH", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, lengthFunc,
NULL},
@@ -2119,8 +2108,11 @@ static struct sql_func_definition definitions[] = {
NULL},
{"SUBSTR", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
FIELD_TYPE_STRING, substrFunc, NULL},
- {"SUM", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step, sumFinalize},
- {"TOTAL", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_NUMBER, sum_step,
+ {"SUM", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, sum_step, sumFinalize},
+ {"SUM", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, sum_step, sumFinalize},
+ {"TOTAL", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_DOUBLE, sum_step,
+ totalFinalize},
+ {"TOTAL", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, sum_step,
totalFinalize},
{"TRIM", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
FIELD_TYPE_STRING, trim_func, NULL},
diff --git a/test/sql-tap/built-in-functions.test.lua b/test/sql-tap/built-in-functions.test.lua
index 353030278..6fae811dc 100755
--- a/test/sql-tap/built-in-functions.test.lua
+++ b/test/sql-tap/built-in-functions.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(46)
+test:plan(52)
--
-- Make sure that number of arguments check is checked properly for SQL built-in
@@ -438,6 +438,66 @@ test:do_test(
"Type mismatch: can not convert string('1') to integer"
})
+test:do_catchsql_test(
+ "builtins-2.33",
+ [[
+ SELECT SUM('1');
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function SUM()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.34",
+ function()
+ local res = {pcall(box.execute, [[SELECT SUM(?);]], {'1'})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert string('1') to integer"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.35",
+ [[
+ SELECT AVG('1');
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function AVG()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.36",
+ function()
+ local res = {pcall(box.execute, [[SELECT AVG(?);]], {'1'})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert string('1') to integer"
+ })
+
+test:do_catchsql_test(
+ "builtins-2.37",
+ [[
+ SELECT TOTAL('1');
+ ]],
+ {
+ 1, [[Failed to execute SQL statement: ]]..
+ [[wrong arguments for function TOTAL()]]
+ }
+)
+
+test:do_test(
+ "builtins-2.38",
+ function()
+ local res = {pcall(box.execute, [[SELECT TOTAL(?);]], {'1'})}
+ return {tostring(res[3])}
+ end, {
+ "Type mismatch: can not convert string('1') to integer"
+ })
+
--
-- Make sure that the type of result of MAX() and MIN() is the same as the type
-- of the argument.
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index 27bd44ac4..da4db5a55 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -1181,7 +1181,7 @@ test:do_select_tests(
{"2", "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)", {54, 36, 27, 21, 39, 28}},
{"3", "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)", {80, 36, 40, 21}},
{"4", "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)", {81, 37, 41, 22}},
- {"5", "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2", {3, 4.33, 1, 2.0}},
+ {"5", "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2", {3, 4, 1, 2.0}},
})
-- EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single
diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua
index 9a192974b..8bb20bc79 100755
--- a/test/sql-tap/func.test.lua
+++ b/test/sql-tap/func.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(14686)
+test:plan(14682)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -831,7 +831,7 @@ test:do_test(
]])
end, {
-- <func-8.1>
- 68236, 3, 22745.33, 1, 67890, 5
+ 68236, 3, 22745, 1, 67890, 5
-- </func-8.1>
})
@@ -849,76 +849,6 @@ test:do_execsql_test(
-- </func-8.2>
})
--- ifcapable tempdb {
--- do_test func-8.3 {
--- execsql {
--- CREATE TEMP TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
--- SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
--- }
--- } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
--- } else {
--- do_test func-8.3 {
--- execsql {
--- CREATE TABLE t3 AS SELECT a FROM t2 ORDER BY a DESC;
--- SELECT min('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
--- }
--- } {z+1abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
--- }
--- do_test func-8.4 {
--- execsql {
--- SELECT max('z+'||a||'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP') FROM t3;
--- }
--- } {z+67890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP}
-test:do_execsql_test(
- "func-8.5",
- [[
- SELECT sum(x) FROM (SELECT '9223372036' || '854775807' AS x
- UNION ALL SELECT -9223372036854775807)
- ]], {
- -- <func-8.5>
- 0
- -- </func-8.5>
- })
-
-test:do_execsql_test(
- "func-8.6",
- [[
- SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775807' AS x
- UNION ALL SELECT -9223372036854775807)
- ]], {
- -- <func-8.6>
- "integer"
- -- </func-8.6>
- })
-
-test:do_execsql_test(
- "func-8.7",
- [[
- SELECT typeof(sum(x)) FROM (SELECT '9223372036' || '854775808' AS x
- UNION ALL SELECT -9223372036854775807)
- ]], {
- -- <func-8.7>
- "integer"
- -- </func-8.7>
- })
-
-test:do_execsql_test(
- "func-8.8",
- [[
- SELECT sum(x)>0.0 FROM (SELECT '9223372036' || '854775808' AS x
- UNION ALL SELECT -9223372036850000000)
- ]], {
- -- <func-8.8>
- true
- -- </func-8.8>
- })
-
-
-
-
-
-
-
-- How do you test the random() function in a meaningful, deterministic way?
--
test:do_execsql_test(
@@ -1657,7 +1587,7 @@ test:do_catchsql_test(
UNION ALL SELECT 10 AS x);
]], {
-- <func-18.15.2>
- 1, "Failed to execute SQL statement: integer overflow"
+ 1, "Failed to execute SQL statement: integer is overflowed"
-- </func-18.15.2>
})
@@ -1669,7 +1599,7 @@ test:do_catchsql_test(
SELECT -10 AS x);
]], {
-- <func-18.18>
- 1, "Failed to execute SQL statement: integer overflow"
+ 1, "Failed to execute SQL statement: integer is overflowed"
-- </func-18.18>
})
@@ -2892,7 +2822,7 @@ test:do_catchsql_test(
SELECT SUM(X'FF')
]], {
-- <func-76.4>
- 1, "Type mismatch: can not convert varbinary(x'FF') to number"
+ 1, "Failed to execute SQL statement: wrong arguments for function SUM()"
-- </func-76.4>
})
diff --git a/test/sql-tap/minmax2.test.lua b/test/sql-tap/minmax2.test.lua
index 0b9f5a63c..973187e42 100755
--- a/test/sql-tap/minmax2.test.lua
+++ b/test/sql-tap/minmax2.test.lua
@@ -290,7 +290,7 @@ test:do_execsql_test(
test:do_execsql_test(
"minmax2-4.2",
[[
- SELECT y, coalesce(sum(x),0) FROM
+ SELECT y, coalesce(sum(CAST(x AS INTEGER)),0) FROM
(SELECT null AS x, y+1 AS y FROM t1 UNION SELECT x, y FROM t1)
GROUP BY y ORDER BY y;
]], {
diff --git a/test/sql-tap/minmax4.test.lua b/test/sql-tap/minmax4.test.lua
index 3a71462e0..392e0798d 100755
--- a/test/sql-tap/minmax4.test.lua
+++ b/test/sql-tap/minmax4.test.lua
@@ -251,7 +251,7 @@ test:do_test(
]]
end, {
-- <minmax4-2.3>
- 2, 3, 3.0, 1, 5, 1, 1, 1.5, 2, 4
+ 2, 3, 3, 1, 5, 1, 1, 1, 2, 4
-- </minmax4-2.3>
})
diff --git a/test/sql-tap/null.test.lua b/test/sql-tap/null.test.lua
index ca20e3558..d01761a1f 100755
--- a/test/sql-tap/null.test.lua
+++ b/test/sql-tap/null.test.lua
@@ -157,7 +157,7 @@ test:do_execsql_test(
avg(b), avg(c), min(b), max(b) from t1;
]], {
-- <null-3.1>
- 7, 4, 6, 2, 3, 0.5, 0.5, 0, 1
+ 7, 4, 6, 2, 3, 0, 0, 0, 1
-- </null-3.1>
})
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index ba12470f3..e89c7d2f8 100755
--- a/test/sql-tap/select1.test.lua
+++ b/test/sql-tap/select1.test.lua
@@ -515,7 +515,7 @@ test:do_catchsql_test(
SELECT sum(a) FROM t3
]], {
-- <select1-2.17.1>
- 1, "Type mismatch: can not convert string('abc') to number"
+ 1, "Failed to execute SQL statement: wrong arguments for function SUM()"
-- </select1-2.17.1>
})
diff --git a/test/sql-tap/select3.test.lua b/test/sql-tap/select3.test.lua
index 9880c1f69..535a72167 100755
--- a/test/sql-tap/select3.test.lua
+++ b/test/sql-tap/select3.test.lua
@@ -69,7 +69,7 @@ test:do_execsql_test("select3-1.3", [[
SELECT max(n)/avg(n), max(log)/avg(log) FROM t1
]], {
-- <select3-1.3>
- 1.9375, 1.25
+ 1, 1
-- </select3-1.3>
})
@@ -95,7 +95,7 @@ test:do_execsql_test("select3-2.3.1", [[
SELECT log, avg(n) FROM t1 GROUP BY log ORDER BY log
]], {
-- <select3-2.3.1>
- 0, 1.0, 1, 2.0, 2, 3.5, 3, 6.5, 4, 12.5, 5, 24.0
+ 0, 1, 1, 2, 2, 3, 3, 6, 4, 12, 5, 24
-- </select3-2.3.1>
})
@@ -103,7 +103,7 @@ test:do_execsql_test("select3-2.3.2", [[
SELECT log, avg(n)+1 FROM t1 GROUP BY log ORDER BY log
]], {
-- <select3-2.3.2>
- 0, 2.0, 1, 3.0, 2, 4.5, 3, 7.5, 4, 13.5, 5, 25.0
+ 0, 2, 1, 3, 2, 4, 3, 7, 4, 13, 5, 25
-- </select3-2.3.2>
})
@@ -111,7 +111,7 @@ test:do_execsql_test("select3-2.4", [[
SELECT log, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log
]], {
-- <select3-2.4>
- 0, 0.0, 1, 0.0, 2, 0.5, 3, 1.5, 4, 3.5, 5, 7.0
+ 0, 0, 1, 0, 2, 0, 3, 1, 4, 3, 5, 7
-- </select3-2.4>
})
@@ -119,7 +119,7 @@ test:do_execsql_test("select3-2.5", [[
SELECT log*2+1, avg(n)-min(n) FROM t1 GROUP BY log ORDER BY log
]], {
-- <select3-2.5>
- 1, 0.0, 3, 0.0, 5, 0.5, 7, 1.5, 9, 3.5, 11, 7.0
+ 1, 0, 3, 0, 5, 0, 7, 1, 9, 3, 11, 7
-- </select3-2.5>
})
@@ -264,7 +264,7 @@ test:do_execsql_test("select3-5.1", [[
ORDER BY max(n+log*2)+0, avg(n)+0
]], {
-- <select3-5.1>
- 0, 1, 1.0, 1, 1, 1, 2.0, 4, 2, 2, 3.5, 8, 3, 4, 6.5, 14, 4, 8, 12.5, 24, 5, 15, 24.0, 41
+ 0, 1, 1, 1, 1, 1, 2, 4, 2, 2, 3, 8, 3, 4, 6, 14, 4, 8, 12, 24, 5, 15, 24, 41
-- </select3-5.1>
})
@@ -274,7 +274,7 @@ test:do_execsql_test("select3-5.2", [[
ORDER BY max(n+log*2)+0, CAST(GREATEST(log,avg(n)) AS DOUBLE)+0
]], {
-- <select3-5.2>
- 0, 1, 1.0, 1, 1, 1, 2.0, 4, 2, 2, 3.5, 8, 3, 4, 6.5, 14, 4, 8, 12.5, 24, 5, 15, 24.0, 41
+ 0, 1, 1, 1, 1, 1, 2, 4, 2, 2, 3, 8, 3, 4, 6, 14, 4, 8, 12, 24, 5, 15, 24, 41
-- </select3-5.2>
})
diff --git a/test/sql-tap/select5.test.lua b/test/sql-tap/select5.test.lua
index b04c9af09..b25bf0d59 100755
--- a/test/sql-tap/select5.test.lua
+++ b/test/sql-tap/select5.test.lua
@@ -558,7 +558,7 @@ test:do_catchsql_test(
SELECT 1 FROM jj HAVING avg(s2) = 1 AND avg(s2) = 0;
]], {
-- <select5-9.13.2>
- 1, "Type mismatch: can not convert string('A') to number"
+ 1, "Failed to execute SQL statement: wrong arguments for function AVG()"
-- </select5-9.13.2>
})
diff --git a/test/sql-tap/select6.test.lua b/test/sql-tap/select6.test.lua
index 8ad91390a..031892112 100755
--- a/test/sql-tap/select6.test.lua
+++ b/test/sql-tap/select6.test.lua
@@ -312,7 +312,7 @@ test:do_execsql_test(
SELECT a,b,a+b FROM (SELECT avg(x) as a, avg(y) as b FROM t1)
]], {
-- <select6-3.3>
- 10.5, 3.7, 14.2
+ 10, 3, 13
-- </select6-3.3>
})
@@ -322,7 +322,7 @@ test:do_execsql_test(
SELECT a,b,a+b FROM (SELECT avg(x) as a, avg(y) as b FROM t1 WHERE y=4)
]], {
-- <select6-3.4>
- 11.5, 4.0, 15.5
+ 11, 4, 15
-- </select6-3.4>
})
@@ -332,7 +332,7 @@ test:do_execsql_test(
SELECT x,y,x+y FROM (SELECT avg(a) as x, avg(b) as y FROM t2 WHERE a=4)
]], {
-- <select6-3.5>
- 4.0, 3.0, 7.0
+ 4, 3, 7
-- </select6-3.5>
})
@@ -343,7 +343,6 @@ test:do_execsql_test(
WHERE a>10
]], {
-- <select6-3.6>
- 10.5, 3.7, 14.2
-- </select6-3.6>
})
@@ -365,7 +364,7 @@ test:do_execsql_test(
WHERE a>10
]], {
-- <select6-3.8>
- 11.5, 4.0, 15.5
+ 11, 4, 15
-- </select6-3.8>
})
@@ -387,7 +386,7 @@ test:do_execsql_test(
ORDER BY a
]], {
-- <select6-3.10>
- 1.0, 1, 2.0, 2.5, 2, 4.5, 5.5, 3, 8.5, 11.5, 4, 15.5, 18.0, 5, 23.0
+ 1, 1, 2, 2, 2, 4, 5, 3, 8, 11, 4, 15, 18, 5, 23
-- </select6-3.10>
})
@@ -399,7 +398,7 @@ test:do_execsql_test(
WHERE b<4 ORDER BY a
]], {
-- <select6-3.11>
- 1.0, 1, 2.0, 2.5, 2, 4.5, 5.5, 3, 8.5
+ 1, 1, 2, 2, 2, 4, 5, 3, 8
-- </select6-3.11>
})
@@ -411,7 +410,7 @@ test:do_execsql_test(
WHERE b<4 ORDER BY a
]], {
-- <select6-3.12>
- 2.5, 2, 4.5, 5.5, 3, 8.5
+ 2, 2, 4, 5, 3, 8
-- </select6-3.12>
})
@@ -423,7 +422,7 @@ test:do_execsql_test(
ORDER BY a
]], {
-- <select6-3.13>
- 2.5, 2, 4.5, 5.5, 3, 8.5, 11.5, 4, 15.5, 18.0, 5, 23.0
+ 2, 2, 4, 5, 3, 8, 11, 4, 15, 18, 5, 23
-- </select6-3.13>
})
@@ -487,7 +486,7 @@ test:do_execsql_test(
SELECT avg(y) FROM (SELECT DISTINCT y FROM t1) WHERE y<5 ORDER BY y
]], {
-- <select6-4.4>
- 2.5
+ 2
-- </select6-4.4>
})
@@ -497,7 +496,7 @@ test:do_execsql_test(
SELECT avg(y) FROM (SELECT DISTINCT y FROM t1 WHERE y<5) ORDER BY y
]], {
-- <select6-4.5>
- 2.5
+ 2
-- </select6-4.5>
})
diff --git a/test/sql-tap/selectG.test.lua b/test/sql-tap/selectG.test.lua
index d416381bd..0690b4c86 100755
--- a/test/sql-tap/selectG.test.lua
+++ b/test/sql-tap/selectG.test.lua
@@ -48,7 +48,7 @@ test:do_test(
:format(end_time - start_time, time_quota))
end, {
-- <100>
- 100000, 5000050000, 50000.5, true
+ 100000, 5000050000, 50000, true
-- </100>
})
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index 1c5b3d02e..094269a5a 100755
--- a/test/sql-tap/subquery.test.lua
+++ b/test/sql-tap/subquery.test.lua
@@ -466,7 +466,7 @@ test:do_execsql_test(
HAVING avg(a.y) > avg(b.y));
]], {
-- <subquery-3.4.1>
- 107, 4.0
+ 106, 4, 107, 4
-- </subquery-3.4.1>
})
@@ -482,7 +482,7 @@ test:do_execsql_test(
HAVING avg1 > avg2);
]], {
-- <subquery-3.4.2>
- 107, 4.0
+ 106, 4, 107, 4
-- </subquery-3.4.2>
})
@@ -505,7 +505,7 @@ test:do_execsql_test(
ORDER BY a.x;
]], {
-- <subquery-3.4.3>
- 106, 4.5, false, true, 107, 4.0, true, false
+ 106, 4, true, false, 107, 4, true, false
-- </subquery-3.4.3>
})
@@ -517,7 +517,7 @@ test:do_execsql_test(
SELECT max((SELECT avg(y) FROM t35b)) FROM t35a;
]], {
-- <subquery-3.5.1>
- 98.5
+ 98
-- </subquery-3.5.1>
})
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
index 998213dbb..7c5477b33 100755
--- a/test/sql-tap/uuid.test.lua
+++ b/test/sql-tap/uuid.test.lua
@@ -237,7 +237,7 @@ test:do_catchsql_test(
[[
SELECT AVG(u) from t2;
]], {
- 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
+ 1, "Failed to execute SQL statement: wrong arguments for function AVG()"
})
test:do_catchsql_test(
@@ -441,7 +441,7 @@ test:do_catchsql_test(
[[
SELECT SUM(u) from t2;
]], {
- 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
+ 1, "Failed to execute SQL statement: wrong arguments for function SUM()"
})
test:do_catchsql_test(
@@ -449,7 +449,7 @@ test:do_catchsql_test(
[[
SELECT TOTAL(u) from t2;
]], {
- 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to number"
+ 1, "Failed to execute SQL statement: wrong arguments for function TOTAL()"
})
test:do_execsql_test(
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index f9350d2f2..83b9a59bf 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -327,7 +327,7 @@ SELECT typeof(a) FROM t0;
SELECT AVG(a) FROM t0;
| ---
| - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Failed to execute SQL statement: wrong arguments for function AVG()'
| ...
SELECT MIN(a) FROM t0;
| ---
@@ -348,7 +348,7 @@ SELECT MAX(a) FROM t0;
SELECT SUM(a) FROM t0;
| ---
| - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Failed to execute SQL statement: wrong arguments for function SUM()'
| ...
SELECT COUNT(a) FROM t0;
| ---
@@ -361,7 +361,7 @@ SELECT COUNT(a) FROM t0;
SELECT TOTAL(a) FROM t0;
| ---
| - null
- | - 'Type mismatch: can not convert boolean(FALSE) to number'
+ | - 'Failed to execute SQL statement: wrong arguments for function TOTAL()'
| ...
SELECT GROUP_CONCAT(a, ' +++ ') FROM t0;
| ---
diff --git a/test/sql/types.result b/test/sql/types.result
index 5f0ce3543..3017793c6 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -761,7 +761,7 @@ box.execute("SELECT sum(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: number
+ type: integer
rows:
- [18446744073709551613]
...
@@ -769,15 +769,15 @@ box.execute("SELECT avg(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: number
+ type: integer
rows:
- - [6148914691236516864]
+ - [6148914691236517204]
...
box.execute("SELECT total(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: number
+ type: double
rows:
- [1.844674407371e+19]
...
@@ -1239,17 +1239,17 @@ box.execute("SELECT * FROM t WHERE v = x'616263'")
box.execute("SELECT sum(v) FROM t;")
---
- null
-- 'Type mismatch: can not convert varbinary(x''616263'') to number'
+- 'Failed to execute SQL statement: wrong arguments for function SUM()'
...
box.execute("SELECT avg(v) FROM t;")
---
- null
-- 'Type mismatch: can not convert varbinary(x''616263'') to number'
+- 'Failed to execute SQL statement: wrong arguments for function AVG()'
...
box.execute("SELECT total(v) FROM t;")
---
- null
-- 'Type mismatch: can not convert varbinary(x''616263'') to number'
+- 'Failed to execute SQL statement: wrong arguments for function TOTAL()'
...
box.execute("SELECT min(v) FROM t;")
---
@@ -1795,7 +1795,7 @@ box.execute("SELECT sum(d) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: number
+ type: double
rows:
- [18000000000000000000]
...
@@ -1803,7 +1803,7 @@ box.execute("SELECT avg(d) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: number
+ type: double
rows:
- [4500000000000000000]
...
@@ -1811,7 +1811,7 @@ box.execute("SELECT total(d) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: number
+ type: double
rows:
- [18000000000000000000]
...
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* [Tarantool-patches] [PATCH v1 09/10] sql: fix quote() function
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
` (7 preceding siblings ...)
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 08/10] sql: check argument types of sum(), avg(), total() Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 10/10] sql: arguments check for string value functions Mergen Imeev via Tarantool-patches
2021-08-19 11:49 ` [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Vladimir Davydov via Tarantool-patches
10 siblings, 0 replies; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
Ater this patch SQL built-in function quite will return the same number
in case it receives number as an argument. If the argument is not
number, string representation of the argument will be returned.
Part of #6105
---
src/box/sql/func.c | 18 ++-------
test/sql-tap/built-in-functions.test.lua | 47 +++++++++++++++++++++++-
test/sql-tap/trigger5.test.lua | 3 +-
test/sql/types.result | 14 +++----
4 files changed, 59 insertions(+), 23 deletions(-)
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 8d9c5a7d9..98f7169c0 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -1092,26 +1092,13 @@ quoteFunc(sql_context * context, int argc, sql_value ** argv)
assert(argc == 1);
UNUSED_PARAMETER(argc);
switch (argv[0]->type) {
- case MEM_TYPE_DOUBLE:{
- double r1, r2;
- char zBuf[50];
- r1 = mem_get_double_unsafe(argv[0]);
- sql_snprintf(sizeof(zBuf), zBuf, "%!.15g", r1);
- sqlAtoF(zBuf, &r2, 20);
- if (r1 != r2) {
- sql_snprintf(sizeof(zBuf), zBuf, "%!.20e",
- r1);
- }
- sql_result_text(context, zBuf, -1,
- SQL_TRANSIENT);
- break;
- }
case MEM_TYPE_UUID: {
char buf[UUID_STR_LEN + 1];
tt_uuid_to_string(&argv[0]->u.uuid, &buf[0]);
sql_result_text(context, buf, UUID_STR_LEN, SQL_TRANSIENT);
break;
}
+ case MEM_TYPE_DOUBLE:
case MEM_TYPE_UINT:
case MEM_TYPE_INT: {
sql_result_value(context, argv[0]);
@@ -2094,6 +2081,9 @@ static struct sql_func_definition definitions[] = {
FIELD_TYPE_INTEGER, position_func, NULL},
{"PRINTF", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, printfFunc,
NULL},
+ {"QUOTE", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, quoteFunc, NULL},
+ {"QUOTE", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, quoteFunc, NULL},
+ {"QUOTE", 1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, quoteFunc, NULL},
{"QUOTE", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, quoteFunc, NULL},
{"RANDOM", 0, {}, FIELD_TYPE_INTEGER, randomFunc, NULL},
{"RANDOMBLOB", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_VARBINARY,
diff --git a/test/sql-tap/built-in-functions.test.lua b/test/sql-tap/built-in-functions.test.lua
index 6fae811dc..5fae8f357 100755
--- a/test/sql-tap/built-in-functions.test.lua
+++ b/test/sql-tap/built-in-functions.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(52)
+test:plan(56)
--
-- Make sure that number of arguments check is checked properly for SQL built-in
@@ -545,4 +545,49 @@ test:do_test(
{name = "COLUMN_2", type = "scalar"},
})
+--
+-- Make sure QUOTE() returns the same number if it takes a number as an
+-- argument, otherwise it returns a string.
+--
+test:do_execsql_test(
+ "builtins-4.1",
+ [[
+ SELECT QUOTE(1), 1 = QUOTE(1);
+ ]],
+ {
+ 1, true
+ }
+)
+
+test:do_execsql_test(
+ "builtins-4.2",
+ [[
+ SELECT QUOTE(1.5), 1.5 = QUOTE(1.5);
+ ]],
+ {
+ 1.5, true
+ }
+)
+
+test:do_execsql_test(
+ "builtins-4.3",
+ [[
+ SELECT QUOTE(CAST(1 AS NUMBER)),
+ CAST(1 AS NUMBER) = QUOTE(CAST(1 AS NUMBER));
+ ]],
+ {
+ 1, true
+ }
+)
+
+test:do_execsql_test(
+ "builtins-4.4",
+ [[
+ SELECT QUOTE('1'), QUOTE(x'31'), QUOTE(true);
+ ]],
+ {
+ "'1'","X'31'","TRUE"
+ }
+)
+
test:finish_test()
diff --git a/test/sql-tap/trigger5.test.lua b/test/sql-tap/trigger5.test.lua
index 8336cdcf2..7f3762126 100755
--- a/test/sql-tap/trigger5.test.lua
+++ b/test/sql-tap/trigger5.test.lua
@@ -31,7 +31,8 @@ test:do_execsql_test(
INSERT INTO Undo VALUES
((SELECT coalesce(max(id),0) + 1 FROM Undo),
(SELECT 'INSERT INTO Item (a,b,c) VALUES (' || CAST(coalesce(old.a,'NULL') AS TEXT)
- || ',' || quote(old.b) || ',' || CAST(old.c AS TEXT) || ');'));
+ || ',' || CAST(quote(old.b) AS STRING) || ',' ||
+ CAST(old.c AS TEXT) || ');'));
END;
DELETE FROM Item WHERE a = 1;
SELECT * FROM Undo;
diff --git a/test/sql/types.result b/test/sql/types.result
index 3017793c6..ec80dfc14 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -847,7 +847,7 @@ box.execute("SELECT quote(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: string
+ type: integer
rows:
- [18446744073709551613]
...
@@ -863,7 +863,7 @@ box.execute("SELECT quote(i) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: string
+ type: integer
rows:
- [18446744073709551613]
...
@@ -1883,12 +1883,12 @@ box.execute("SELECT quote(d) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: string
+ type: double
rows:
- - ['10.0']
- - ['-2.0']
- - ['3.3']
- - ['1.8e+19']
+ - [10]
+ - [-2]
+ - [3.3]
+ - [18000000000000000000]
...
box.execute("SELECT LEAST(d, 0) FROM t;")
---
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* [Tarantool-patches] [PATCH v1 10/10] sql: arguments check for string value functions
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
` (8 preceding siblings ...)
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 09/10] sql: fix quote() function Mergen Imeev via Tarantool-patches
@ 2021-08-13 3:17 ` Mergen Imeev via Tarantool-patches
2021-08-19 11:49 ` [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Vladimir Davydov via Tarantool-patches
10 siblings, 0 replies; 13+ messages in thread
From: Mergen Imeev via Tarantool-patches @ 2021-08-13 3:17 UTC (permalink / raw)
To: vdavydov; +Cc: tarantool-patches
This patch enables static and dynamic type check for functions SUBSTR(),
GROUP_CONCAT(), REPLACE(), TRIM(). All these functions afther this patch
will be able to return VARINARY value when VARBINARY arguments are given
instead of STRING arguments.
Closes #6105
---
src/box/sql/expr.c | 3 +-
src/box/sql/func.c | 63 ++++++++++++++++++++++++-------
test/sql-tap/aggnested.test.lua | 8 ++--
test/sql-tap/distinctagg.test.lua | 3 +-
test/sql-tap/e_select1.test.lua | 28 ++++++++------
test/sql-tap/func.test.lua | 32 ++++------------
test/sql-tap/substr.test.lua | 4 +-
test/sql-tap/tkt2942.test.lua | 10 ++---
test/sql-tap/uuid.test.lua | 22 ++++-------
test/sql-tap/view.test.lua | 2 +-
test/sql-tap/with1.test.lua | 2 +-
test/sql/boolean.result | 7 +---
test/sql/prepared.result | 4 +-
test/sql/prepared.test.lua | 2 +-
test/sql/types.result | 16 +++-----
15 files changed, 106 insertions(+), 100 deletions(-)
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 47005f8e3..17d97a871 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -353,7 +353,7 @@ sql_expr_coll(Parse *parse, Expr *p, bool *is_explicit_coll, uint32_t *coll_id,
p->x.pList->nExpr;
uint32_t flags = sql_func_flags(p->u.zToken);
if (((flags & SQL_FUNC_DERIVEDCOLL) != 0) &&
- arg_count > 0) {
+ arg_count > 0 && p->type == FIELD_TYPE_STRING) {
/*
* Now we use quite straightforward
* approach assuming that resulting
@@ -362,7 +362,6 @@ sql_expr_coll(Parse *parse, Expr *p, bool *is_explicit_coll, uint32_t *coll_id,
* built-in functions: trim, upper,
* lower, replace, substr.
*/
- assert(p->type == FIELD_TYPE_STRING);
p = p->x.pList->a->pExpr;
continue;
}
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 98f7169c0..1f787d4e0 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -548,16 +548,14 @@ substrFunc(sql_context * context, int argc, sql_value ** argv)
cnt++;
}
z2 += i;
- sql_result_text64(context, (char *)z, z2 - z,
- SQL_TRANSIENT);
+ mem_copy_str(context->pOut, (char *)z, z2 - z);
} else {
if (p1 + p2 > len) {
p2 = len - p1;
if (p2 < 0)
p2 = 0;
}
- sql_result_blob64(context, (char *)&z[p1], (u64) p2,
- SQL_TRANSIENT);
+ mem_copy_bin(context->pOut, (char *)&z[p1], p2);
}
}
@@ -1363,7 +1361,10 @@ replaceFunc(sql_context * context, int argc, sql_value ** argv)
j += nStr - i;
assert(j <= nOut);
zOut[j] = 0;
- sql_result_text(context, (char *)zOut, j, sql_free);
+ if (context->func->def->returns == FIELD_TYPE_STRING)
+ mem_set_str_dynamic(context->pOut, (char *)zOut, j);
+ else
+ mem_set_bin_dynamic(context->pOut, (char *)zOut, j);
}
/**
@@ -1418,8 +1419,10 @@ trim_procedure(struct sql_context *context, enum trim_side_mask flags,
}
}
finish:
- sql_result_text(context, (char *)input_str, input_str_sz,
- SQL_TRANSIENT);
+ if (context->func->def->returns == FIELD_TYPE_STRING)
+ mem_copy_str(context->pOut, (char *)input_str, input_str_sz);
+ else
+ mem_copy_bin(context->pOut, (char *)input_str, input_str_sz);
}
/**
@@ -1842,9 +1845,13 @@ groupConcatFinalize(sql_context * context)
} else if (pAccum->accError == STRACCUM_NOMEM) {
context->is_aborted = true;
} else {
- sql_result_text(context,
- sqlStrAccumFinish(pAccum),
- pAccum->nChar, sql_free);
+ char *str = sqlStrAccumFinish(pAccum);
+ int len = pAccum->nChar;
+ assert(len >= 0);
+ if (context->func->def->returns == FIELD_TYPE_STRING)
+ mem_set_str_dynamic(context->pOut, str, len);
+ else
+ mem_set_bin_dynamic(context->pOut, str, len);
}
}
}
@@ -2014,8 +2021,15 @@ static struct sql_func_definition definitions[] = {
{"GREATEST", -1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxFunc,
NULL},
- {"GROUP_CONCAT", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY},
+ {"GROUP_CONCAT", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING,
+ groupConcatStep, groupConcatFinalize},
+ {"GROUP_CONCAT", 2, {FIELD_TYPE_STRING, FIELD_TYPE_STRING},
FIELD_TYPE_STRING, groupConcatStep, groupConcatFinalize},
+ {"GROUP_CONCAT", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY,
+ groupConcatStep, groupConcatFinalize},
+ {"GROUP_CONCAT", 2, {FIELD_TYPE_VARBINARY, FIELD_TYPE_VARBINARY},
+ FIELD_TYPE_VARBINARY, groupConcatStep, groupConcatFinalize},
+
{"HEX", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_STRING, hexFunc, NULL},
{"IFNULL", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
sql_builtin_stub, NULL},
@@ -2088,24 +2102,45 @@ static struct sql_func_definition definitions[] = {
{"RANDOM", 0, {}, FIELD_TYPE_INTEGER, randomFunc, NULL},
{"RANDOMBLOB", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_VARBINARY,
randomBlob, NULL},
- {"REPLACE", 3, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
+ {"REPLACE", 3,
+ {FIELD_TYPE_STRING, FIELD_TYPE_STRING, FIELD_TYPE_STRING},
FIELD_TYPE_STRING, replaceFunc, NULL},
+ {"REPLACE", 3,
+ {FIELD_TYPE_VARBINARY, FIELD_TYPE_VARBINARY, FIELD_TYPE_VARBINARY},
+ FIELD_TYPE_VARBINARY, replaceFunc, NULL},
{"ROUND", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, roundFunc, NULL},
{"ROUND", 2, {FIELD_TYPE_DOUBLE, FIELD_TYPE_INTEGER}, FIELD_TYPE_DOUBLE,
roundFunc, NULL},
{"ROW_COUNT", 0, {}, FIELD_TYPE_INTEGER, sql_row_count, NULL},
{"SOUNDEX", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, soundexFunc,
NULL},
- {"SUBSTR", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
+ {"SUBSTR", 2, {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER},
+ FIELD_TYPE_STRING, substrFunc, NULL},
+ {"SUBSTR", 3,
+ {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER, FIELD_TYPE_INTEGER},
FIELD_TYPE_STRING, substrFunc, NULL},
+ {"SUBSTR", 2, {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER},
+ FIELD_TYPE_VARBINARY, substrFunc, NULL},
+ {"SUBSTR", 3,
+ {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER, FIELD_TYPE_INTEGER},
+ FIELD_TYPE_VARBINARY, substrFunc, NULL},
{"SUM", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, sum_step, sumFinalize},
{"SUM", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, sum_step, sumFinalize},
{"TOTAL", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_DOUBLE, sum_step,
totalFinalize},
{"TOTAL", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, sum_step,
totalFinalize},
- {"TRIM", -1, {FIELD_TYPE_ANY, FIELD_TYPE_ANY, FIELD_TYPE_ANY},
+
+ {"TRIM", 2, {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER},
+ FIELD_TYPE_STRING, trim_func, NULL},
+ {"TRIM", 3, {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER, FIELD_TYPE_STRING},
FIELD_TYPE_STRING, trim_func, NULL},
+ {"TRIM", 2, {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER},
+ FIELD_TYPE_VARBINARY, trim_func, NULL},
+ {"TRIM", 3,
+ {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER, FIELD_TYPE_VARBINARY},
+ FIELD_TYPE_VARBINARY, trim_func, NULL},
+
{"TYPEOF", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, typeofFunc, NULL},
{"UNICODE", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, unicodeFunc,
NULL},
diff --git a/test/sql-tap/aggnested.test.lua b/test/sql-tap/aggnested.test.lua
index 6a967461d..1684f2249 100755
--- a/test/sql-tap/aggnested.test.lua
+++ b/test/sql-tap/aggnested.test.lua
@@ -24,10 +24,10 @@ test:plan(7)
test:do_execsql_test(
"aggnested-1.1",
[[
- CREATE TABLE t1(a1 INTEGER PRIMARY KEY);
- INSERT INTO t1 VALUES(1), (2), (3);
- CREATE TABLE t2(b1 INTEGER PRIMARY KEY);
- INSERT INTO t2 VALUES(4), (5);
+ CREATE TABLE t1(a1 STRING PRIMARY KEY);
+ INSERT INTO t1 VALUES('1'), ('2'), ('3');
+ CREATE TABLE t2(b1 STRING PRIMARY KEY);
+ INSERT INTO t2 VALUES('4'), ('5');
SELECT (SELECT group_concat(a1,'x') FROM t2 LIMIT 1) FROM t1;
]],
{
diff --git a/test/sql-tap/distinctagg.test.lua b/test/sql-tap/distinctagg.test.lua
index 9b1346f7d..7d5e05e3f 100755
--- a/test/sql-tap/distinctagg.test.lua
+++ b/test/sql-tap/distinctagg.test.lua
@@ -82,7 +82,8 @@ test:do_catchsql_test(
test:do_catchsql_test(
"distinctagg-2.2",
[[
- SELECT group_concat(distinct a,b) FROM t1;
+ SELECT group_concat(distinct CAST(a AS STRING), CAST(b AS STRING))
+ FROM t1;
]], {
-- <distinctagg-2.2>
1, "DISTINCT aggregates must have exactly one argument"
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index da4db5a55..9f10994ae 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -956,7 +956,7 @@ test:do_select_tests(
{"4", "SELECT *, count(*) FROM a1 JOIN a2", {4, 10, 10, 4, 16}},
{"5", "SELECT *, sum(three) FROM a1 NATURAL JOIN a2", {3, 6, 2, 3}},
{"6", "SELECT *, sum(three) FROM a1 NATURAL JOIN a2", {3, 6, 2, 3}},
- {"7", "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2", {"12", 3, 6}},
+ {"7", "SELECT group_concat('1', ''), a1.* FROM a1 NATURAL JOIN a2", {"11", 3, 6}},
})
-- EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then
@@ -1014,12 +1014,12 @@ test:do_execsql_test(
INSERT INTO b1 VALUES(7, 's');
INSERT INTO b1 VALUES(6, 's');
- CREATE TABLE b2(x TEXT, y INT PRIMARY KEY);
- INSERT INTO b2 VALUES(NULL, 0);
- INSERT INTO b2 VALUES(NULL, 1);
- INSERT INTO b2 VALUES('xyz', 2);
- INSERT INTO b2 VALUES('abc', 3);
- INSERT INTO b2 VALUES('xyz', 4);
+ CREATE TABLE b2(x TEXT, y STRING PRIMARY KEY);
+ INSERT INTO b2 VALUES(NULL, '0');
+ INSERT INTO b2 VALUES(NULL, '1');
+ INSERT INTO b2 VALUES('xyz', '2');
+ INSERT INTO b2 VALUES('abc', '3');
+ INSERT INTO b2 VALUES('xyz', '4');
CREATE TABLE b3(id INT PRIMARY KEY, a TEXT COLLATE "unicode_ci", b TEXT COLLATE "binary");
INSERT INTO b3 VALUES(1, 'abc', 'abc');
@@ -1048,10 +1048,14 @@ test:do_execsql_test(
test:do_select_tests(
"e_select-4.9",
{
- {"1", "SELECT group_concat(one), two FROM b1 GROUP BY two", {"4,5","f","1","o","6,7","s","2,3","t"}},
- {"2", "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)", {"1,2,3,4",10,"5,6,7",18}},
- {"3", "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2", {"4","1,5","2,6","3,7"}},
- {"4", "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')", {"3,4,5,6,7","1,2"}},
+ {"1", [[SELECT group_concat(CAST(one AS STRING)), two FROM b1 GROUP BY
+ two]], {"4,5","f","1","o","6,7","s","2,3","t"}},
+ {"2", [[SELECT group_concat(CAST(one AS STRING)), sum(one) FROM b1
+ GROUP BY (one>4)]], {"1,2,3,4",10,"5,6,7",18}},
+ {"3", [[SELECT group_concat(CAST(one AS STRING)) FROM b1 GROUP BY
+ (two>'o'), one%2]], {"4","1,5","2,6","3,7"}},
+ {"4", [[SELECT group_concat(CAST(one AS STRING)) FROM b1 GROUP BY
+ (one==2 OR two=='o')]], {"3,4,5,6,7","1,2"}},
})
-- EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
@@ -1061,7 +1065,7 @@ test:do_select_tests(
"e_select-4.10",
{
{"1", "SELECT group_concat(y) FROM b2 GROUP BY x", {"0,1","3","2,4"}},
- {"2", "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END", {4, 1}},
+ {"2", "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<'4' THEN NULL ELSE 0 END", {4, 1}},
})
-- EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
diff --git a/test/sql-tap/func.test.lua b/test/sql-tap/func.test.lua
index 8bb20bc79..416f27d69 100755
--- a/test/sql-tap/func.test.lua
+++ b/test/sql-tap/func.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(14682)
+test:plan(14680)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -193,26 +193,6 @@ test:do_execsql_test(
-- </func-2.8>
})
-test:do_execsql_test(
- "func-2.9",
- [[
- SELECT substr(a,1,1) FROM t2
- ]], {
- -- <func-2.9>
- "1", "", "3", "", "6"
- -- </func-2.9>
- })
-
-test:do_execsql_test(
- "func-2.10",
- [[
- SELECT substr(a,2,2) FROM t2
- ]], {
- -- <func-2.10>
- "", "", "45", "", "78"
- -- </func-2.10>
- })
-
-- Only do the following tests if TCL has UTF-8 capabilities
--
if ("ሴ" ~= "u1234") then
@@ -2256,7 +2236,8 @@ test:do_execsql_test(
test:do_execsql_test(
"func-24.10",
[[
- SELECT group_concat(CASE t1 WHEN 'this' THEN null ELSE t1 END) FROM tbl1
+ SELECT group_concat(CAST(CASE t1 WHEN 'this' THEN null ELSE t1 END
+ AS STRING)) FROM tbl1
]], {
-- <func-24.10>
"program,is,free,software"
@@ -2266,7 +2247,8 @@ test:do_execsql_test(
test:do_execsql_test(
"func-24.11",
[[
- SELECT group_concat(CASE WHEN t1!='software' THEN null ELSE t1 END) FROM tbl1
+ SELECT group_concat(CAST(CASE WHEN t1!='software' THEN null ELSE t1 END
+ AS STRING)) FROM tbl1
]], {
-- <func-24.11>
"software"
@@ -2276,8 +2258,8 @@ test:do_execsql_test(
test:do_execsql_test(
"func-24.12",
[[
- SELECT group_concat(CASE t1 WHEN 'this' THEN ''
- WHEN 'program' THEN null ELSE t1 END) FROM tbl1
+ SELECT group_concat(CAST(CASE t1 WHEN 'this' THEN '' WHEN 'program' THEN
+ null ELSE t1 END AS STRING)) FROM tbl1
]], {
-- <func-24.12>
",is,free,software"
diff --git a/test/sql-tap/substr.test.lua b/test/sql-tap/substr.test.lua
index f64eb94be..e7e6d7aca 100755
--- a/test/sql-tap/substr.test.lua
+++ b/test/sql-tap/substr.test.lua
@@ -57,12 +57,12 @@ local function subblob_test(id, hex, i1, i2, hexresult)
test:do_execsql_test(
"substr-"..id..".1",
string.format(
- "SELECT HEX(CAST(substr(b, %s, %s) AS VARBINARY)) FROM t1", i1, i2),
+ "SELECT HEX(substr(CAST(b AS VARBINARY), %s, %s)) FROM t1", i1, i2),
{hexresult})
--local qstr = string.gsub("' '", string)--"string","map","' ''",["string"]]]=]).."'"
test:do_execsql_test(
"substr-"..id..".2",
- string.format("SELECT HEX(CAST(substr(x'%s', %s, %s) AS VARBINARY))",
+ string.format("SELECT HEX(substr(x'%s', %s, %s))",
hex, i1, i2),
{hexresult})
end
diff --git a/test/sql-tap/tkt2942.test.lua b/test/sql-tap/tkt2942.test.lua
index 2c06e013e..10da68583 100755
--- a/test/sql-tap/tkt2942.test.lua
+++ b/test/sql-tap/tkt2942.test.lua
@@ -35,11 +35,11 @@ test:plan(4)
test:do_execsql_test(
"tkt2942.1",
[[
- create table t1(id INT primary key, "num" int);
- insert into t1 values (1, 2);
- insert into t1 values (2, 1);
- insert into t1 values (3, 3);
- insert into t1 values (4, 4);
+ create table t1(id INT primary key, "num" STRING);
+ insert into t1 values (1, '2');
+ insert into t1 values (2, '1');
+ insert into t1 values (3, '3');
+ insert into t1 values (4, '4');
SELECT group_concat("num") FROM (SELECT "num" FROM t1 ORDER BY "num" DESC);
]], {
-- <tkt2942.1>
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
index 7c5477b33..056d0f636 100755
--- a/test/sql-tap/uuid.test.lua
+++ b/test/sql-tap/uuid.test.lua
@@ -288,14 +288,12 @@ test:do_execsql_test(
uuid3
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.9",
[[
SELECT GROUP_CONCAT(u) from t2;
]], {
- "11111111-1111-1111-1111-111111111111,"..
- "11111111-3333-1111-1111-111111111111,"..
- "22222222-1111-1111-1111-111111111111"
+ 1, "Failed to execute SQL statement: wrong arguments for function GROUP_CONCAT()"
})
test:do_catchsql_test(
@@ -402,14 +400,12 @@ test:do_catchsql_test(
1, "Failed to execute SQL statement: wrong arguments for function RANDOMBLOB()"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.23",
[[
SELECT REPLACE(u, '1', '2') from t2;
]], {
- "22222222-2222-2222-2222-222222222222",
- "22222222-3333-2222-2222-222222222222",
- "22222222-2222-2222-2222-222222222222"
+ 1, "Failed to execute SQL statement: wrong arguments for function REPLACE()"
})
test:do_catchsql_test(
@@ -428,12 +424,12 @@ test:do_catchsql_test(
1, "Failed to execute SQL statement: wrong arguments for function SOUNDEX()"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.26",
[[
SELECT SUBSTR(u, 3, 3) from t2;
]], {
- "111", "111", "222"
+ 1, "Failed to execute SQL statement: wrong arguments for function SUBSTR()"
})
test:do_catchsql_test(
@@ -452,14 +448,12 @@ test:do_catchsql_test(
1, "Failed to execute SQL statement: wrong arguments for function TOTAL()"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-6.1.29",
[[
SELECT TRIM(u) from t2;
]], {
- "11111111-1111-1111-1111-111111111111",
- "11111111-3333-1111-1111-111111111111",
- "22222222-1111-1111-1111-111111111111"
+ 1, "Failed to execute SQL statement: wrong arguments for function TRIM()"
})
test:do_execsql_test(
diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua
index bea1f7db2..e84fc033d 100755
--- a/test/sql-tap/view.test.lua
+++ b/test/sql-tap/view.test.lua
@@ -1280,7 +1280,7 @@ test:do_execsql_test(
a(t) AS (
SELECT group_concat( substr('a', 1+least(iter/7,4), 1), '') FROM m2 GROUP BY cy
)
- SELECT group_concat(trim(t),x'0a') FROM a;
+ SELECT group_concat(CAST(trim(t) AS VARBINARY),x'0a') FROM a;
SELECT * FROM v;
]], {
-- <view-24.5>
diff --git a/test/sql-tap/with1.test.lua b/test/sql-tap/with1.test.lua
index 35f3dabb3..761004b23 100755
--- a/test/sql-tap/with1.test.lua
+++ b/test/sql-tap/with1.test.lua
@@ -549,7 +549,7 @@ test:do_execsql_test("8.1-mandelbrot", [[
SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '')
FROM m2 GROUP BY cy
)
- SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;
+ SELECT group_concat(CAST(TRIM(TRAILING FROM t) AS VARBINARY),x'0a') FROM a;
]], {
-- <8.1-mandelbrot>
[[ ....#
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index 83b9a59bf..f82ec8f44 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -365,11 +365,8 @@ SELECT TOTAL(a) FROM t0;
| ...
SELECT GROUP_CONCAT(a, ' +++ ') FROM t0;
| ---
- | - metadata:
- | - name: COLUMN_1
- | type: string
- | rows:
- | - ['FALSE +++ TRUE']
+ | - null
+ | - 'Failed to execute SQL statement: wrong arguments for function GROUP_CONCAT()'
| ...
-- Check BOOLEAN as binding parameter.
diff --git a/test/sql/prepared.result b/test/sql/prepared.result
index c4b09e514..9824a061c 100644
--- a/test/sql/prepared.result
+++ b/test/sql/prepared.result
@@ -471,7 +471,7 @@ s = prepare([[WITH RECURSIVE \
a(t) AS ( \
SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') \
FROM m2 GROUP BY cy) \
- SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;]])
+ SELECT group_concat(CAST(TRIM(TRAILING FROM t) AS VARBINARY), x'0a') FROM a;]])
| ---
| ...
@@ -481,7 +481,7 @@ res = execute(s.stmt_id)
res.metadata
| ---
| - - name: COLUMN_13
- | type: string
+ | type: varbinary
| ...
unprepare(s.stmt_id)
| ---
diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua
index 0a1fdebae..ee2470122 100644
--- a/test/sql/prepared.test.lua
+++ b/test/sql/prepared.test.lua
@@ -176,7 +176,7 @@ s = prepare([[WITH RECURSIVE \
a(t) AS ( \
SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') \
FROM m2 GROUP BY cy) \
- SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;]])
+ SELECT group_concat(CAST(TRIM(TRAILING FROM t) AS VARBINARY), x'0a') FROM a;]])
res = execute(s.stmt_id)
res.metadata
diff --git a/test/sql/types.result b/test/sql/types.result
index ec80dfc14..cf893d857 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -807,11 +807,8 @@ box.execute("SELECT count(i) FROM t;")
...
box.execute("SELECT group_concat(i) FROM t;")
---
-- metadata:
- - name: COLUMN_1
- type: string
- rows:
- - ['1,-1,18446744073709551613']
+- null
+- 'Failed to execute SQL statement: wrong arguments for function GROUP_CONCAT()'
...
box.execute("DELETE FROM t WHERE i < 18446744073709551613;")
---
@@ -1279,7 +1276,7 @@ box.execute("SELECT group_concat(v) FROM t;")
---
- metadata:
- name: COLUMN_1
- type: string
+ type: varbinary
rows:
- ['abc']
...
@@ -1841,11 +1838,8 @@ box.execute("SELECT count(d) FROM t;")
...
box.execute("SELECT group_concat(d) FROM t;")
---
-- metadata:
- - name: COLUMN_1
- type: string
- rows:
- - ['10.0,-2.0,3.3,1.8e+19']
+- null
+- 'Failed to execute SQL statement: wrong arguments for function GROUP_CONCAT()'
...
box.execute("SELECT lower(d) FROM t;")
---
--
2.25.1
^ permalink raw reply [flat|nested] 13+ messages in thread* Re: [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments
2021-08-13 3:17 [Tarantool-patches] [PATCH v1 00/10] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches
` (9 preceding siblings ...)
2021-08-13 3:17 ` [Tarantool-patches] [PATCH v1 10/10] sql: arguments check for string value functions Mergen Imeev via Tarantool-patches
@ 2021-08-19 11:49 ` Vladimir Davydov via Tarantool-patches
10 siblings, 0 replies; 13+ messages in thread
From: Vladimir Davydov via Tarantool-patches @ 2021-08-19 11:49 UTC (permalink / raw)
To: imeevma; +Cc: tarantool-patches
Since moving the function matching to the parser didn't work out
(because this would turn builtin function names, such as 'count', into
reserved keywords, forcing the user to quote them), I think we should
stick with this approach.
LGTM, but I think the function hash is messy:
- I don't like the two-level structure of the builtin function hash:
name -> sql_func_dictionary with array of func_sql_builtin, one
array entry per argument set
because all builtin functions share the same implementation and this
is probably never going to change:
{"MAX", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxStep,
minMaxFinalize},
{"MAX", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, minmaxStep,
minMaxFinalize},
{"MAX", 1, {FIELD_TYPE_NUMBER}, FIELD_TYPE_NUMBER, minmaxStep,
minMaxFinalize},
{"MAX", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_VARBINARY, minmaxStep,
minMaxFinalize},
{"MAX", 1, {FIELD_TYPE_UUID}, FIELD_TYPE_UUID, minmaxStep,
minMaxFinalize},
{"MAX", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, minmaxStep,
minMaxFinalize},
{"MAX", 1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, minmaxStep,
minMaxFinalize},
I think we'd better have a separate hash:
name -> builtin func
The builtin func struct should contain a list or accepted argumented
and the corresponding return value. Or maybe a callback, which would
take arguments from an SQL expression list and return the expected
return value and argument types. This could be more efficient,
because we wouldn't have to scan the list of arguments on resolve.
- Builtin funcs shouldn't refer to struct func or func_def, because
they don't use any information from those structures (permissions,
id, is_sandboxed, etc).
We should consider reworking the implementation accordingly in the
future.
^ permalink raw reply [flat|nested] 13+ messages in thread