From: Mergen Imeev via Tarantool-patches <tarantool-patches@dev.tarantool.org> To: kyukhin@tarantool.org, v.ioffe@tarantool.org Cc: tarantool-patches@dev.tarantool.org Subject: [Tarantool-patches] [PATCH v1 6/9] sql: enable types checking for some functions Date: Thu, 19 Aug 2021 15:03:09 +0300 [thread overview] Message-ID: <9281a3175e1838f4d1d229ac16283ab38005859e.1629374449.git.imeevma@gmail.com> (raw) In-Reply-To: <cover.1629374448.git.imeevma@gmail.com> 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 93f6cc067..281a1b4a8 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -2003,49 +2003,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}, @@ -2054,14 +2065,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 ceb4ecccf..08a675101 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 c22af8e50..140059262 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 000142ebe..a9fa642f2 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 60cb8b713..58f00b30b 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
next prev parent reply other threads:[~2021-08-19 12:05 UTC|newest] Thread overview: 13+ messages / expand[flat|nested] mbox.gz Atom feed top 2021-08-19 12:02 [Tarantool-patches] [PATCH v1 0/9] Check types of SQL built-in functions arguments Mergen Imeev via Tarantool-patches 2021-08-19 12:02 ` [Tarantool-patches] [PATCH v1 1/9] sql: modify signature of TRIM() Mergen Imeev via Tarantool-patches 2021-08-19 12:02 ` [Tarantool-patches] [PATCH v1 2/9] sql: rework SQL built-in functions hash table Mergen Imeev via Tarantool-patches 2021-08-19 12:03 ` [Tarantool-patches] [PATCH v1 3/9] sql: check number of arguments during parsing Mergen Imeev via Tarantool-patches 2021-08-19 12:03 ` [Tarantool-patches] [PATCH v1 4/9] sql: static type check for SQL built-in functions Mergen Imeev via Tarantool-patches 2021-08-19 12:03 ` [Tarantool-patches] [PATCH v1 5/9] sql: runtime " Mergen Imeev via Tarantool-patches 2021-08-19 12:03 ` Mergen Imeev via Tarantool-patches [this message] 2021-08-19 12:03 ` [Tarantool-patches] [PATCH v1 7/9] sql: fix result type of min() and max() functions Mergen Imeev via Tarantool-patches 2021-08-19 12:03 ` [Tarantool-patches] [PATCH v1 8/9] sql: check argument types of sum(), avg(), total() Mergen Imeev via Tarantool-patches 2021-08-19 12:03 ` [Tarantool-patches] [PATCH v1 9/9] sql: arguments check for string value functions Mergen Imeev via Tarantool-patches 2021-08-19 12:26 ` [Tarantool-patches] [PATCH v1 0/9] Check types of SQL built-in functions arguments Kirill Yukhin via Tarantool-patches 2021-08-19 15:50 ` Vitaliia Ioffe via Tarantool-patches 2021-08-19 16:16 ` 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=9281a3175e1838f4d1d229ac16283ab38005859e.1629374449.git.imeevma@gmail.com \ --to=tarantool-patches@dev.tarantool.org \ --cc=imeevma@tarantool.org \ --cc=kyukhin@tarantool.org \ --cc=v.ioffe@tarantool.org \ --subject='Re: [Tarantool-patches] [PATCH v1 6/9] sql: enable types checking for some 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