Tarantool development patches archive
 help / color / mirror / Atom feed
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


  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