From: Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org> To: v.shpilevoy@tarantool.org Cc: tarantool-patches@dev.tarantool.org Subject: [Tarantool-patches] [PATCH v1 1/1] sql: define default types for built-in functions Date: Fri, 12 Nov 2021 14:41:05 +0300 [thread overview] Message-ID: <abd741469c235c008e7e1a2da0ab7319ce7f15b7.1636717230.git.imeevma@gmail.com> (raw) After this patch, all functions that take arguments of one of two or more types have defined a default type, which is used when the argument type cannot be determined. Closes #6483 @TarantoolBot document Title: Default types of SQL built-in functions In case a function takes an argument of one of two or more types, this function has defined the default type for this argument. This type is used when the type of the argument cannot be determined, for example, in the case of a bound variable. Rules for determining default types as follows: 1) When there is only one possible type, it is default. 2) When possible types are INTEGER, DOUBLE or DECIMAL, DECIMAL is default. 3) When possible types are STRING or VARBINARY, STRING is default. 4) When possible data types are any other scalar data type, SCALAR is default. 5) Otherwise, there is no default type. --- https://github.com/tarantool/tarantool/issues/6483 https://github.com/tarantool/tarantool/tree/imeevma/gh-6483-default-types-for-sql-builtins .../gh-6483-default-type-for-builtins.md | 4 + src/box/sql/func.c | 12 +- test/sql-tap/built-in-functions.test.lua | 127 +++++++++++++++++- test/sql-tap/metatypes.test.lua | 8 +- 4 files changed, 136 insertions(+), 15 deletions(-) create mode 100644 changelogs/unreleased/gh-6483-default-type-for-builtins.md diff --git a/changelogs/unreleased/gh-6483-default-type-for-builtins.md b/changelogs/unreleased/gh-6483-default-type-for-builtins.md new file mode 100644 index 000000000..134c065f1 --- /dev/null +++ b/changelogs/unreleased/gh-6483-default-type-for-builtins.md @@ -0,0 +1,4 @@ +## feature/sql + +* A default type is now defined in case the argument type of a SQL built-in + function cannot be determined during parsing (gh-4415). diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 684fcc275..1fd3458a2 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -1704,15 +1704,15 @@ struct sql_func_definition { * function should be defined in succession. */ static struct sql_func_definition definitions[] = { + {"ABS", 1, {FIELD_TYPE_DECIMAL}, FIELD_TYPE_DECIMAL, func_abs_dec, + NULL}, {"ABS", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, func_abs_int, NULL}, {"ABS", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, func_abs_double, NULL}, - {"ABS", 1, {FIELD_TYPE_DECIMAL}, FIELD_TYPE_DECIMAL, func_abs_dec, - NULL}, + {"AVG", 1, {FIELD_TYPE_DECIMAL}, FIELD_TYPE_DECIMAL, step_avg, fin_avg}, {"AVG", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, step_avg, fin_avg}, {"AVG", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, step_avg, fin_avg}, - {"AVG", 1, {FIELD_TYPE_DECIMAL}, FIELD_TYPE_DECIMAL, step_avg, fin_avg}, {"CHAR", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, func_char, NULL}, {"CHAR_LENGTH", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, func_char_length, NULL}, @@ -1837,15 +1837,15 @@ static struct sql_func_definition definitions[] = { {"SUBSTR", 3, {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER, FIELD_TYPE_INTEGER}, FIELD_TYPE_VARBINARY, func_substr_octets, NULL}, + {"SUM", 1, {FIELD_TYPE_DECIMAL}, FIELD_TYPE_DECIMAL, step_sum, NULL}, {"SUM", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, step_sum, NULL}, {"SUM", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, step_sum, NULL}, - {"SUM", 1, {FIELD_TYPE_DECIMAL}, FIELD_TYPE_DECIMAL, step_sum, NULL}, + {"TOTAL", 1, {FIELD_TYPE_DECIMAL}, FIELD_TYPE_DOUBLE, step_total, + fin_total}, {"TOTAL", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_DOUBLE, step_total, fin_total}, {"TOTAL", 1, {FIELD_TYPE_DOUBLE}, FIELD_TYPE_DOUBLE, step_total, fin_total}, - {"TOTAL", 1, {FIELD_TYPE_DECIMAL}, FIELD_TYPE_DOUBLE, step_total, - fin_total}, {"TRIM", 2, {FIELD_TYPE_STRING, FIELD_TYPE_INTEGER}, FIELD_TYPE_STRING, func_trim_str, NULL}, diff --git a/test/sql-tap/built-in-functions.test.lua b/test/sql-tap/built-in-functions.test.lua index 6fae811dc..e7b06cf86 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(66) -- -- Make sure that number of arguments check is checked properly for SQL built-in @@ -395,7 +395,7 @@ test:do_test( local res = {pcall(box.execute, [[SELECT ABS(?);]], {'1'})} return {tostring(res[3])} end, { - "Type mismatch: can not convert string('1') to integer" + "Type mismatch: can not convert string('1') to decimal" }) test:do_catchsql_test( @@ -455,7 +455,7 @@ test:do_test( local res = {pcall(box.execute, [[SELECT SUM(?);]], {'1'})} return {tostring(res[3])} end, { - "Type mismatch: can not convert string('1') to integer" + "Type mismatch: can not convert string('1') to decimal" }) test:do_catchsql_test( @@ -475,7 +475,7 @@ test:do_test( local res = {pcall(box.execute, [[SELECT AVG(?);]], {'1'})} return {tostring(res[3])} end, { - "Type mismatch: can not convert string('1') to integer" + "Type mismatch: can not convert string('1') to decimal" }) test:do_catchsql_test( @@ -495,7 +495,7 @@ test:do_test( local res = {pcall(box.execute, [[SELECT TOTAL(?);]], {'1'})} return {tostring(res[3])} end, { - "Type mismatch: can not convert string('1') to integer" + "Type mismatch: can not convert string('1') to decimal" }) -- @@ -545,4 +545,121 @@ test:do_test( {name = "COLUMN_2", type = "scalar"}, }) +-- gh-6483: Make sure functions have correct default type. +test:do_test( + "builtins-4.1", + function() + return box.execute([[SELECT ABS(?);]], {1}).metadata[1] + end, { + name = "COLUMN_1", type = 'decimal' + }) + +test:do_test( + "builtins-4.2", + function() + return box.execute([[SELECT AVG(?);]], {1}).metadata[1] + end, { + name = "COLUMN_1", type = 'decimal' + }) + +test:do_test( + "builtins-4.3", + function() + return box.execute([[SELECT GREATEST(?, 1);]], {1}).metadata[1] + end, { + name = "COLUMN_1", type = 'scalar' + }) + +test:do_test( + "builtins-4.4", + function() + return box.execute([[SELECT GROUP_CONCAT(?);]], {'a'}).metadata[1] + end, { + name = "COLUMN_1", type = 'string' + }) + +test:do_test( + "builtins-4.5", + function() + return box.execute([[SELECT LEAST(?, 1);]], {1}).metadata[1] + end, { + name = "COLUMN_1", type = 'scalar' + }) + +test:do_test( + "builtins-4.6", + 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_test( + "builtins-4.7", + function() + return box.execute([[SELECT MAX(?);]], {1}).metadata[1] + end, { + name = "COLUMN_1", type = 'scalar' + }) + +test:do_test( + "builtins-4.8", + function() + return box.execute([[SELECT MIN(?);]], {1}).metadata[1] + end, { + name = "COLUMN_1", type = 'scalar' + }) + +test:do_test( + "builtins-4.9", + 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_test( + "builtins-4.10", + function() + return box.execute([[SELECT REPLACE(@1, @1, @1);]], {'a'}).metadata[1] + end, { + name = "COLUMN_1", type = 'string' + }) + +test:do_test( + "builtins-4.11", + function() + return box.execute([[SELECT SUBSTR(?, 1, 2);]], {'asd'}).metadata[1] + end, { + name = "COLUMN_1", type = 'string' + }) + +test:do_test( + "builtins-4.12", + function() + return box.execute([[SELECT SUM(?);]], {1}).metadata[1] + end, { + name = "COLUMN_1", type = 'decimal' + }) + +test:do_test( + "builtins-4.13", + function() + local res = {pcall(box.execute, [[SELECT TOTAL(?);]], {'a'})} + return {tostring(res[3])} + end, { + "Type mismatch: can not convert string('a') to decimal" + }) + +test:do_test( + "builtins-4.14", + function() + local res = {pcall(box.execute, [[SELECT TRIM(?);]], {1})} + return {tostring(res[3])} + end, { + "Type mismatch: can not convert integer(1) to string" + }) + test:finish_test() diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua index 12c3ce06d..aa9ccb165 100755 --- a/test/sql-tap/metatypes.test.lua +++ b/test/sql-tap/metatypes.test.lua @@ -406,7 +406,7 @@ test:do_catchsql_test( [[ SELECT ABS(a) FROM t; ]], { - 1, "Type mismatch: can not convert any(1) to integer" + 1, "Type mismatch: can not convert any(1) to decimal" }) test:do_catchsql_test( @@ -414,7 +414,7 @@ test:do_catchsql_test( [[ SELECT AVG(a) FROM t; ]], { - 1, "Type mismatch: can not convert any(1) to integer" + 1, "Type mismatch: can not convert any(1) to decimal" }) test:do_catchsql_test( @@ -635,7 +635,7 @@ test:do_catchsql_test( [[ SELECT SUM(a) FROM t; ]], { - 1, "Type mismatch: can not convert any(1) to integer" + 1, "Type mismatch: can not convert any(1) to decimal" }) test:do_catchsql_test( @@ -643,7 +643,7 @@ test:do_catchsql_test( [[ SELECT TOTAL(a) FROM t; ]], { - 1, "Type mismatch: can not convert any(1) to integer" + 1, "Type mismatch: can not convert any(1) to decimal" }) test:do_catchsql_test( -- 2.25.1
next reply other threads:[~2021-11-12 11:41 UTC|newest] Thread overview: 6+ messages / expand[flat|nested] mbox.gz Atom feed top 2021-11-12 11:41 Mergen Imeev via Tarantool-patches [this message] 2021-11-15 21:53 ` Vladislav Shpilevoy via Tarantool-patches 2021-11-19 8:15 ` Mergen Imeev via Tarantool-patches 2021-11-21 15:23 ` Vladislav Shpilevoy via Tarantool-patches 2021-11-22 7:43 Mergen Imeev via Tarantool-patches 2021-11-22 12:32 ` Kirill Yukhin via Tarantool-patches
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=abd741469c235c008e7e1a2da0ab7319ce7f15b7.1636717230.git.imeevma@gmail.com \ --to=tarantool-patches@dev.tarantool.org \ --cc=imeevma@tarantool.org \ --cc=v.shpilevoy@tarantool.org \ --subject='Re: [Tarantool-patches] [PATCH v1 1/1] sql: define default types for built-in functions' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox