From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from [87.239.111.99] (localhost [127.0.0.1]) by dev.tarantool.org (Postfix) with ESMTP id 4A3A56FC82; Fri, 12 Nov 2021 14:41:08 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 4A3A56FC82 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=tarantool.org; s=dev; t=1636717268; bh=rwanLrOvmOSAY7LUIAJNT974h4gk2Pt0CTUUniy0qmg=; h=To:Cc:Date:Subject:List-Id:List-Unsubscribe:List-Archive: List-Post:List-Help:List-Subscribe:From:Reply-To:From; b=OjnJ4KFHQ26sVWe03kHeUm8eJmeU6V5O/gzfH6g+tJfKzfpUtDHekHt9B9dpbMzUr cygq1piwqFwfkswGAWbR79SQ8y2Dm9fLR1RAt8Ym6/ymeICu9HfBWWxihn6OPtRGLk 4fUuRXLZWVuJxyRwLqvVxPzoLP+C2nqvJlHXLDR4= Received: from smtpng1.i.mail.ru (smtpng1.i.mail.ru [94.100.181.251]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 954F56FC82 for ; Fri, 12 Nov 2021 14:41:06 +0300 (MSK) DKIM-Filter: OpenDKIM Filter v2.11.0 dev.tarantool.org 954F56FC82 Received: by smtpng1.m.smailru.net with esmtpa (envelope-from ) id 1mlUvZ-0005zu-VI; Fri, 12 Nov 2021 14:41:06 +0300 To: v.shpilevoy@tarantool.org Cc: tarantool-patches@dev.tarantool.org Date: Fri, 12 Nov 2021 14:41:05 +0300 Message-Id: X-Mailer: git-send-email 2.25.1 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-7564579A: 646B95376F6C166E X-77F55803: 4F1203BC0FB41BD9731B3922EC063979CAB00300CDA98DC51CCA794E1CC6B18A00894C459B0CD1B9A3082595408285D5C9C1913466DB57B89350BE1CF0C528FE1C9689494080BA7D X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE7BB17EE3498E810FEEA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637F4EDC07301919F278638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D8466007EABFE7734CE165FD5BA474449E117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCF1175FABE1C0F9B6A471835C12D1D9774AD6D5ED66289B52BA9C0B312567BB23117882F446042972877693876707352033AC447995A7AD18C26CFBAC0749D213D2E47CDBA5A96583BA9C0B312567BB231DD303D21008E29813377AFFFEAFD269A417C69337E82CC2E827F84554CEF50127C277FBC8AE2E8BA83251EDC214901ED5E8D9A59859A8B66F6A3E018CF4DC80089D37D7C0E48F6C5571747095F342E88FB05168BE4CE3AF X-C1DE0DAB: C20DE7B7AB408E4181F030C43753B8186998911F362727C414F749A5E30D975C7E9FEBB9C11794A11C6ADACE66DA17F9FFC9D16F4BD783879C2B6934AE262D3EE7EAB7254005DCED43CD4744DEF647E51E0A4E2319210D9B64D260DF9561598F01A9E91200F654B02F433CA60753AEF28E8E86DC7131B365E7726E8460B7C23C X-C8649E89: 4E36BF7865823D7055A7F0CF078B5EC49A30900B95165D343F22CE0A71FDB37EA0E109F8F4C53DFE71B63D5A4167CE24AADF39179220D23ADDBA6D33CE309D851D7E09C32AA3244C11218A416B9F165B17B3F319680EFA1781560E2432555DBB729B2BEF169E0186 X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojLk+X/GdHsno8XIPkZ1Wk7g== X-Mailru-Sender: 689FA8AB762F7393C37E3C1AEC41BA5DAFBA36CA37C8D0FDDB2FED9174474B8B83D72C36FC87018B9F80AB2734326CD2FB559BB5D741EB96352A0ABBE4FDA4210A04DAD6CC59E33667EA787935ED9F1B X-Mras: Ok Subject: [Tarantool-patches] [PATCH v1 1/1] sql: define default types for built-in functions X-BeenThere: tarantool-patches@dev.tarantool.org X-Mailman-Version: 2.1.34 Precedence: list List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , From: Mergen Imeev via Tarantool-patches Reply-To: imeevma@tarantool.org Errors-To: tarantool-patches-bounces@dev.tarantool.org Sender: "Tarantool-patches" 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