[Tarantool-patches] [PATCH v1 3/3] sql: introduce field type ANY

imeevma at tarantool.org imeevma at tarantool.org
Thu Nov 11 13:53:41 MSK 2021


Closes #3174

@TarantoolBot document
Title: Field type ANY in SQL

Properties of type ANY in SQL:
1) typeof() with an argument of type ANY returns "any";
2) any value of any type can be implicitly and explicitly cast to the ANY type;
3) a value of type ANY cannot be implicitly cast to any other type;
4) a value of type ANY cannot participate in arithmetic, bitwise, comparison,
   and concationation operations.
---
 extra/mkkeywordhash.c           |   3 +-
 src/box/sql/func.c              |   4 +-
 src/box/sql/mem.c               |  49 ++-
 src/box/sql/mem.h               |   4 +-
 src/box/sql/parse.y             |   3 +-
 src/box/sql/vdbe.c              |   4 +-
 test/sql-tap/metatypes.test.lua | 568 ++++++++++++++++++++++++++++++--
 7 files changed, 594 insertions(+), 41 deletions(-)

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 3e4200417..aaeb7d51b 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -176,10 +176,11 @@ static Keyword aKeywordTable[] = {
   { "VALUES",                 "TK_VALUES",      true  },
   { "VARBINARY",              "TK_VARBINARY",   true  },
   { "VIEW",                   "TK_VIEW",        true  },
+  { "WILDCARD",               "TK_STANDARD",    true  },
   { "WITH",                   "TK_WITH",        true  },
   { "WHEN",                   "TK_WHEN",        true  },
   { "WHERE",                  "TK_WHERE",       true  },
-  { "ANY",                    "TK_STANDARD",    true  },
+  { "ANY",                    "TK_ANY",         true  },
   { "ASENSITIVE",             "TK_STANDARD",    true  },
   { "BLOB",                   "TK_STANDARD",    true  },
   { "BINARY",                 "TK_ID",          true  },
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 63d7536d9..43f6fc40a 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -318,6 +318,8 @@ typeofFunc(struct sql_context *context, int argc, struct Mem *argv)
 {
 	(void)argc;
 	const char *z = 0;
+	if ((argv[0].flags & MEM_Any) != 0)
+		return mem_set_str0_static(context->pOut, "any");
 	if ((argv[0].flags & MEM_Number) != 0)
 		return mem_set_str0_static(context->pOut, "number");
 	if ((argv[0].flags & MEM_Scalar) != 0)
@@ -2013,7 +2015,7 @@ static struct sql_func_definition definitions[] = {
 	{"MIN", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, step_minmax, NULL},
 	{"MIN", 1, {FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR, step_minmax, NULL},
 
-	{"NULLIF", 2, {field_type_MAX, field_type_MAX}, FIELD_TYPE_SCALAR,
+	{"NULLIF", 2, {FIELD_TYPE_SCALAR, FIELD_TYPE_SCALAR}, FIELD_TYPE_SCALAR,
 	 nullifFunc, NULL},
 	{"POSITION", 2, {FIELD_TYPE_STRING, FIELD_TYPE_STRING},
 	 FIELD_TYPE_INTEGER, position_func, NULL},
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index f648c5d62..244415e02 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -727,7 +727,7 @@ str_to_bin(struct Mem *mem)
 {
 	assert(mem->type == MEM_TYPE_STR);
 	mem->type = MEM_TYPE_BIN;
-	mem->flags &= ~(MEM_Term | MEM_Scalar);
+	mem->flags &= ~(MEM_Term | MEM_Scalar | MEM_Any);
 	return 0;
 }
 
@@ -774,7 +774,7 @@ bin_to_str(struct Mem *mem)
 {
 	assert(mem->type == MEM_TYPE_BIN);
 	mem->type = MEM_TYPE_STR;
-	mem->flags &= ~MEM_Scalar;
+	mem->flags &= ~(MEM_Scalar | MEM_Any);
 	return 0;
 }
 
@@ -1376,7 +1376,7 @@ mem_to_str(struct Mem *mem)
 	assert(mem->type < MEM_TYPE_INVALID);
 	switch (mem->type) {
 	case MEM_TYPE_STR:
-		mem->flags &= ~MEM_Scalar;
+		mem->flags &= ~(MEM_Scalar | MEM_Any);
 		return 0;
 	case MEM_TYPE_INT:
 	case MEM_TYPE_UINT:
@@ -1440,7 +1440,7 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
 		if (mem->type == MEM_TYPE_STR)
 			return str_to_bin(mem);
 		if (mem_is_bytes(mem)) {
-			mem->flags &= ~MEM_Scalar;
+			mem->flags &= ~(MEM_Scalar | MEM_Any);
 			return 0;
 		}
 		if (mem->type == MEM_TYPE_UUID)
@@ -1478,7 +1478,11 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
 		if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
 			return -1;
 		mem->flags |= MEM_Scalar;
-		mem->flags &= ~MEM_Number;
+		mem->flags &= ~(MEM_Number | MEM_Any);
+		return 0;
+	case FIELD_TYPE_ANY:
+		mem->flags |= MEM_Any;
+		mem->flags &= ~(MEM_Number | MEM_Scalar);
 		return 0;
 	default:
 		break;
@@ -1491,11 +1495,21 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
 {
 	if (mem->type == MEM_TYPE_NULL || type == field_type_MAX)
 		return 0;
-	if ((mem->flags & MEM_Scalar) != 0 && type != FIELD_TYPE_SCALAR)
-		return -1;
-	if ((mem->flags & MEM_Number) != 0 && type != FIELD_TYPE_SCALAR &&
-	    type != FIELD_TYPE_NUMBER)
-		return -1;
+	if (mem_is_metatype(mem) && type != FIELD_TYPE_ANY) {
+		if ((mem->flags & MEM_Number) != 0) {
+			if (type != FIELD_TYPE_NUMBER &&
+			    type != FIELD_TYPE_SCALAR &&
+			    type != FIELD_TYPE_ANY)
+				return -1;
+		} else if ((mem->flags & MEM_Scalar) != 0) {
+			if (type != FIELD_TYPE_SCALAR &&
+			    type != FIELD_TYPE_ANY)
+				return -1;
+		} else {
+			if (type != FIELD_TYPE_ANY)
+				return -1;
+		}
+	}
 	switch (type) {
 	case FIELD_TYPE_UNSIGNED:
 		if (mem->type == MEM_TYPE_UINT) {
@@ -1509,7 +1523,7 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
 		return -1;
 	case FIELD_TYPE_STRING:
 		if (mem->type == MEM_TYPE_STR) {
-			mem->flags &= ~MEM_Scalar;
+			mem->flags &= ~(MEM_Scalar | MEM_Any);
 			return 0;
 		}
 		return -1;
@@ -1544,7 +1558,7 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
 	case FIELD_TYPE_VARBINARY:
 		if ((mem->type & (MEM_TYPE_BIN | MEM_TYPE_MAP |
 				  MEM_TYPE_ARRAY)) != 0) {
-			mem->flags &= ~MEM_Scalar;
+			mem->flags &= ~(MEM_Scalar | MEM_Any);
 			return 0;
 		}
 		return -1;
@@ -1579,7 +1593,7 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
 		if ((mem->type & (MEM_TYPE_MAP | MEM_TYPE_ARRAY)) != 0)
 			return -1;
 		mem->flags |= MEM_Scalar;
-		mem->flags &= ~MEM_Number;
+		mem->flags &= ~(MEM_Number | MEM_Any);
 		return 0;
 	case FIELD_TYPE_UUID:
 		if (mem->type != MEM_TYPE_UUID)
@@ -1587,6 +1601,8 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
 		mem->flags = 0;
 		return 0;
 	case FIELD_TYPE_ANY:
+		mem->flags |= MEM_Any;
+		mem->flags &= ~(MEM_Number | MEM_Scalar);
 		return 0;
 	default:
 		break;
@@ -2540,6 +2556,11 @@ mem_cmp(const struct Mem *a, const struct Mem *b, int *result,
 			*result = 1;
 		return 0;
 	}
+	if (((a->flags | b->flags) & MEM_Any) != 0) {
+		diag_set(ClientError, ER_SQL_TYPE_MISMATCH, mem_str(a),
+			 "comparable type");
+		return -1;
+	}
 	if (((a->flags | b->flags) & MEM_Scalar) != 0) {
 		*result = mem_cmp_scalar(a, b, coll);
 		return 0;
@@ -2575,6 +2596,8 @@ char *
 mem_type_to_str(const struct Mem *p)
 {
 	assert(p != NULL);
+	if ((p->flags & MEM_Any) != 0)
+		return "any";
 	if ((p->flags & MEM_Scalar) != 0)
 		return "scalar";
 	if ((p->flags & MEM_Number) != 0)
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index 242f910db..a8b132479 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -98,6 +98,8 @@ struct Mem {
 #define MEM_Number    0x0001
 /** MEM is of SCALAR meta-type. */
 #define MEM_Scalar    0x0002
+/** MEM is of ANY meta-type. */
+#define MEM_Any       0x0004
 #define MEM_Cleared   0x0200	/* NULL set by OP_Null, not from data */
 
 /* Whenever Mem contains a valid string or blob representation, one of
@@ -145,7 +147,7 @@ mem_is_num(const struct Mem *mem)
 static inline bool
 mem_is_metatype(const struct Mem *mem)
 {
-	return (mem->flags & (MEM_Number | MEM_Scalar)) != 0;
+	return (mem->flags & (MEM_Number | MEM_Scalar | MEM_Any)) != 0;
 }
 
 static inline bool
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index ae480a5d1..4b32f33f5 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -270,7 +270,7 @@ columnlist ::= tcons.
   QUERY KEY OFFSET RAISE RELEASE REPLACE RESTRICT
   RENAME CTIME_KW IF ENABLE DISABLE UUID
   .
-%wildcard ANY.
+%wildcard WILDCARD.
 
 
 // And "ids" is an identifer-or-string.
@@ -1841,6 +1841,7 @@ typedef(A) ::= BOOL . { A.type = FIELD_TYPE_BOOLEAN; }
 typedef(A) ::= BOOLEAN . { A.type = FIELD_TYPE_BOOLEAN; }
 typedef(A) ::= VARBINARY . { A.type = FIELD_TYPE_VARBINARY; }
 typedef(A) ::= UUID . { A.type = FIELD_TYPE_UUID; }
+typedef(A) ::= ANY . { A.type = FIELD_TYPE_ANY; }
 
 /**
  * Time-like types are temporary disabled, until they are
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 7b1203bc8..0c4e38557 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1934,7 +1934,9 @@ case OP_Column: {
 	/* Currently PSEUDO cursor does not have info about field types. */
 	if (pC->eCurType == CURTYPE_TARANTOOL)
 		field_type = pC->uc.pCursor->space->def->fields[p2].type;
-	if (field_type == FIELD_TYPE_SCALAR)
+	if (field_type == FIELD_TYPE_ANY)
+		pDest->flags |= MEM_Any;
+	else if (field_type == FIELD_TYPE_SCALAR)
 		pDest->flags |= MEM_Scalar;
 	else if (field_type == FIELD_TYPE_NUMBER)
 		pDest->flags |= MEM_Number;
diff --git a/test/sql-tap/metatypes.test.lua b/test/sql-tap/metatypes.test.lua
index dfa5bbab4..12c3ce06d 100755
--- a/test/sql-tap/metatypes.test.lua
+++ b/test/sql-tap/metatypes.test.lua
@@ -1,32 +1,51 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(19)
+test:plan(80)
 
 -- Check that SCALAR and NUMBER meta-types works as intended.
-box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER);]])
+box.execute([[CREATE TABLE t (i INT PRIMARY KEY, s SCALAR, n NUMBER, a ANY);]])
+box.execute([[CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, a ANY,
+                               g UNSIGNED, t STRING, n NUMBER, f DOUBLE,
+                               i INTEGER, b BOOLEAN, v VARBINARY, s SCALAR,
+                               d DECIMAL, u UUID);]])
+box.execute([[INSERT INTO t1(id) VALUES(NULL);]])
 
 --
 -- Check that implicit cast from numeric types to NUMBER and from scalar types
 -- to SCALAR works properly.
 --
-local uuid = [[CAST('11111111-1111-1111-1111-111111111111' AS UUID)]]
+local uuid_str = [[11111111-1111-1111-1111-111111111111]]
+local uuid = require('uuid').fromstr(uuid_str)
+local dec = require('decimal').new(1.5)
 test:do_execsql_test(
     "metatypes-1.1",
     [[
-        INSERT INTO t VALUES(1, 1, 1);
-        INSERT INTO t VALUES(2, 2e0, 2e0);
+        INSERT INTO t VALUES(1, 1, 1, 1);
+        INSERT INTO t VALUES(2, 2e0, 2e0, 2e0);
         INSERT INTO t(i, s) VALUES(3, '3');
         INSERT INTO t(i, s) VALUES(4, true);
         INSERT INTO t(i, s) VALUES(5, x'35');
-        INSERT INTO t(i, s) VALUES(6, ]]..uuid..[[);
+        INSERT INTO t(i, s) VALUES(6, CAST(']]..uuid_str..[[' AS UUID));
+        INSERT INTO t(i, a) VALUES(7, '3');
+        INSERT INTO t(i, a) VALUES(8, true);
+        INSERT INTO t(i, a) VALUES(9, x'35');
+        INSERT INTO t(i, a) VALUES(10, CAST(']]..uuid_str..[[' AS UUID));
+        INSERT INTO t(i, a) VALUES(11, 3e0);
+        INSERT INTO t(i, a) VALUES(12, CAST(1.5 AS DECIMAL));
         SELECT * FROM t;
     ]], {
-        1,1,1,
-        2,2,2,
-        3,"3","",
-        4,true,"",
-        5,"5","",
-        6,require('uuid').fromstr('11111111-1111-1111-1111-111111111111'),""
+        1, 1, 1, 1,
+        2, 2, 2, 2,
+        3, "3", "", "",
+        4, true, "", "",
+        5, "5", "", "",
+        6, uuid, "", "",
+        7, "", "", "3",
+        8, "", "", true,
+        9, "", "", "5",
+        10, "", "", uuid,
+        11, "", "", 3,
+        12, "", "", dec
     })
 
 -- Check that typeof() returns right result.
@@ -35,7 +54,8 @@ test:do_execsql_test(
     [[
         SELECT typeof(s) FROM t;
     ]], {
-        "scalar","scalar","scalar","scalar","scalar","scalar"
+        "scalar", "scalar", "scalar", "scalar", "scalar", "scalar",
+        "NULL", "NULL", "NULL", "NULL", "NULL", "NULL"
     })
 
 test:do_execsql_test(
@@ -43,7 +63,17 @@ test:do_execsql_test(
     [[
         SELECT typeof(n) FROM t;
     ]], {
-        "number","number","NULL","NULL","NULL","NULL"
+        "number", "number", "NULL", "NULL", "NULL", "NULL", "NULL", "NULL",
+        "NULL", "NULL", "NULL", "NULL"
+    })
+
+test:do_execsql_test(
+    "metatypes-1.4",
+    [[
+        SELECT typeof(a) FROM t;
+    ]], {
+        "any", "any", "NULL", "NULL", "NULL", "NULL", "any", "any", "any",
+        "any", "any", "any"
     })
 
 --
@@ -90,7 +120,98 @@ test:do_catchsql_test(
         1, "Type mismatch: can not convert number(11) to integer"
     })
 
--- Check that arithmetic operations are prohibited for NUMBER and SCALAR values.
+-- Check that implicit cast from ANY to any other type is prohibited.
+test:do_execsql_test(
+    "metatypes-2.6",
+    [[
+        INSERT INTO t1(a) VALUES(CAST(1 AS ANY));
+        SELECT * FROM t1;
+    ]], {
+        1, "", "", "", "", "", "", "", "", "", "", "",
+        2, 1, "", "", "", "", "", "", "", "", "", ""
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.7",
+    [[
+        INSERT INTO t1(g) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to unsigned"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.8",
+    [[
+        INSERT INTO t1(t) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.9",
+    [[
+        INSERT INTO t1(n) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to number"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.10",
+    [[
+        INSERT INTO t1(f) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to double"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.11",
+    [[
+        INSERT INTO t1(i) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.12",
+    [[
+        INSERT INTO t1(b) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to boolean"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.13",
+    [[
+        INSERT INTO t1(v) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to varbinary"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.14",
+    [[
+        INSERT INTO t1(s) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to scalar"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.15",
+    [[
+        INSERT INTO t1(d) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to decimal"
+    })
+
+test:do_catchsql_test(
+    "metatypes-2.16",
+    [[
+        INSERT INTO t1(u) VALUES(CAST(1 AS ANY));
+    ]], {
+        1, "Type mismatch: can not convert any(1) to uuid"
+    })
+
+-- Check that arithmetic operations are prohibited for NUMBER, SCALAR and ANY.
 test:do_catchsql_test(
     "metatypes-3.1",
     [[
@@ -107,7 +228,15 @@ test:do_catchsql_test(
         1, "Type mismatch: can not convert scalar(1) to integer, decimal or double"
     })
 
--- Check that bitwise operations are prohibited for NUMBER and SCALAR values.
+test:do_catchsql_test(
+    "metatypes-3.3",
+    [[
+        SELECT CAST(1 AS ANY) - 1;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer, decimal or double"
+    })
+
+-- Check that bitwise operations are prohibited for NUMBER, SCALAR and ANY.
 test:do_catchsql_test(
     "metatypes-4.1",
     [[
@@ -124,7 +253,15 @@ test:do_catchsql_test(
         1, "Type mismatch: can not convert scalar(1) to unsigned"
     })
 
--- Check that concatination is prohibited for SCALAR values.
+test:do_catchsql_test(
+    "metatypes-4.3",
+    [[
+        SELECT CAST(1 AS ANY) | 1;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to unsigned"
+    })
+
+-- Check that concatination is prohibited for SCALAR and ANY.
 test:do_catchsql_test(
     "metatypes-5",
     [[
@@ -133,13 +270,21 @@ test:do_catchsql_test(
         1, "Inconsistent types: expected string or varbinary got scalar('asd')"
     })
 
+test:do_catchsql_test(
+    "metatypes-5",
+    [[
+        SELECT CAST('asd' AS ANY) || 'dsa';
+    ]], {
+        1, "Inconsistent types: expected string or varbinary got any('asd')"
+    })
+
 -- Check that SCALAR values can be compared to values of any other scalar type.
 test:do_execsql_test(
     "metatypes-6.1",
     [[
         SELECT s > false FROM t;
     ]], {
-        true, true, true, true, true, true
+        true, true, true, true, true, true, "", "", "", "", "", ""
     })
 
 test:do_execsql_test(
@@ -147,7 +292,7 @@ test:do_execsql_test(
     [[
         SELECT s = 1 FROM t;
     ]], {
-        true, false, false, false, false, false
+        true, false, false, false, false, false, "", "", "", "", "", ""
     })
 
 test:do_execsql_test(
@@ -155,7 +300,7 @@ test:do_execsql_test(
     [[
         SELECT s != 1.5 FROM t;
     ]], {
-        true, true, true, true, true, true
+        true, true, true, true, true, true, "", "", "", "", "", ""
     })
 
 test:do_execsql_test(
@@ -163,7 +308,7 @@ test:do_execsql_test(
     [[
         SELECT s <= 'abc' FROM t;
     ]], {
-        true, true, true, true, false, false
+        true, true, true, true, false, false, "", "", "", "", "", ""
     })
 
 test:do_execsql_test(
@@ -171,7 +316,7 @@ test:do_execsql_test(
     [[
         SELECT s < x'10' FROM t;
     ]], {
-        true, true, true, true, false, false
+        true, true, true, true, false, false, "", "", "", "", "", ""
     })
 
 test:do_execsql_test(
@@ -179,9 +324,386 @@ test:do_execsql_test(
     [[
         SELECT s > CAST('11111111-1111-1111-1111-111111111110' AS UUID) FROM t;
     ]], {
-        false, false, false, false, false, true
+        false, false, false, false, false, true, "", "", "", "", "", ""
+    })
+
+-- Check that ANY values cannot be compared to values of any other scalar type.
+test:do_catchsql_test(
+    "metatypes-7.1",
+    [[
+        SELECT a > false FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to comparable type"
+    })
+
+test:do_catchsql_test(
+    "metatypes-7.2",
+    [[
+        SELECT a = 1 FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to comparable type"
+    })
+
+test:do_catchsql_test(
+    "metatypes-7.3",
+    [[
+        SELECT a != 1.5 FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to comparable type"
+    })
+
+test:do_catchsql_test(
+    "metatypes-7.4",
+    [[
+        SELECT a <= 'abc' FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to comparable type"
+    })
+
+test:do_catchsql_test(
+    "metatypes-7.5",
+    [[
+        SELECT a < x'10' FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to comparable type"
+    })
+
+test:do_catchsql_test(
+    "metatypes-7.6",
+    [[
+        SELECT a > CAST('11111111-1111-1111-1111-111111111110' AS UUID) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to comparable type"
+    })
+
+test:do_catchsql_test(
+    "metatypes-7.7",
+    [[
+        SELECT a >= CAST(1 AS DECIMAL) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to comparable type"
+    })
+
+test:do_catchsql_test(
+    "metatypes-7.8",
+    [[
+        SELECT a = CAST(1 AS NUMBER) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to comparable type"
     })
 
+test:do_catchsql_test(
+    "metatypes-7.9",
+    [[
+        SELECT a != CAST(1 AS SCALAR) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to comparable type"
+    })
+
+-- Make sure the SQL built-in functions work correctly with ANY.
+test:do_catchsql_test(
+    "metatypes-8.1",
+    [[
+        SELECT ABS(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.2",
+    [[
+        SELECT AVG(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.3",
+    [[
+        SELECT CHAR(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.4",
+    [[
+        SELECT CHARACTER_LENGTH(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.5",
+    [[
+        SELECT CHAR_LENGTH(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_execsql_test(
+    "metatypes-8.6",
+    [[
+        SELECT COALESCE(s, a) FROM t;
+    ]], {
+        1, 2, "3", true, "5", uuid, "3", true, "5", uuid, 3, dec
+    })
+
+test:do_execsql_test(
+    "metatypes-8.7",
+    [[
+        SELECT COUNT(a) FROM t;
+    ]], {
+        8
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.8",
+    [[
+        SELECT GREATEST(s, a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to scalar"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.9",
+    [[
+        SELECT GROUP_CONCAT(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.10",
+    [[
+        SELECT HEX(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to varbinary"
+    })
+
+test:do_execsql_test(
+    "metatypes-8.11",
+    [[
+        SELECT IFNULL(s, a) FROM t;
+    ]], {
+        1, 2, "3", true, "5", uuid, "3", true, "5", uuid, 3, dec
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.12",
+    [[
+        SELECT LEAST(s, a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to scalar"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.13",
+    [[
+        SELECT LENGTH(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.14",
+    [[
+        SELECT s LIKE a FROM t;
+    ]], {
+        1, [[Failed to execute SQL statement: wrong arguments for function ]]..
+           [[LIKE()]]
+    })
+
+test:do_execsql_test(
+    "metatypes-8.15",
+    [[
+        SELECT LIKELIHOOD(a, 0.5e0) FROM t;
+    ]], {
+        1, 2, "", "", "", "", "3", true, "5", uuid, 3, dec
+    })
+
+test:do_execsql_test(
+    "metatypes-8.16",
+    [[
+        SELECT LIKELY(a) FROM t;
+    ]], {
+        1, 2, "", "", "", "", "3", true, "5", uuid, 3, dec
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.17",
+    [[
+        SELECT LOWER(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.18",
+    [[
+        SELECT MAX(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to scalar"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.19",
+    [[
+        SELECT MIN(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to scalar"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.20",
+    [[
+        SELECT NULLIF(s, a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to scalar"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.21",
+    [[
+        SELECT POSITION(s, a) FROM t;
+    ]], {
+        1, [[Failed to execute SQL statement: wrong arguments for function ]]..
+           [[POSITION()]]
+    })
+
+test:do_execsql_test(
+    "metatypes-8.22",
+    [[
+        SELECT PRINTF(a) FROM t;
+    ]], {
+        "1", "2.0", "", "", "", "", "3", "TRUE", "5",
+        "11111111-1111-1111-1111-111111111111", "3.0", "1.5"
+    })
+
+test:do_execsql_test(
+    "metatypes-8.23",
+    [[
+        SELECT QUOTE(a) FROM t;
+    ]], {
+        1, 2, "NULL", "NULL", "NULL", "NULL", "'3'", "TRUE", "X'35'",
+        "11111111-1111-1111-1111-111111111111", 3, dec
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.24",
+    [[
+        SELECT RANDOMBLOB(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.25",
+    [[
+        SELECT REPLACE(s, n, a) FROM t;
+    ]], {
+        1, [[Failed to execute SQL statement: wrong arguments for function ]]..
+           [[REPLACE()]]
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.26",
+    [[
+        SELECT ROUND(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to double"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.27",
+    [[
+        SELECT SOUNDEX(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.28",
+    [[
+        SELECT SUBSTR(a, 1, 1) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.29",
+    [[
+        SELECT SUM(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.30",
+    [[
+        SELECT TOTAL(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.31",
+    [[
+        SELECT TRIM(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_execsql_test(
+    "metatypes-8.32",
+    [[
+        SELECT TYPEOF(a) FROM t;
+    ]], {
+        "any", "any", "NULL", "NULL", "NULL", "NULL", "any", "any", "any",
+        "any", "any", "any"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.33",
+    [[
+        SELECT UNICODE(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_execsql_test(
+    "metatypes-8.34",
+    [[
+        SELECT UNLIKELY(a) FROM t;
+    ]], {
+        1, 2, "", "", "", "", "3", true, "5", uuid, 3, dec
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.35",
+    [[
+        SELECT UPPER(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to string"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.36",
+    [[
+        SELECT UUID(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer"
+    })
+
+test:do_catchsql_test(
+    "metatypes-8.37",
+    [[
+        SELECT ZEROBLOB(a) FROM t;
+    ]], {
+        1, "Type mismatch: can not convert any(1) to integer"
+    })
+
+
 box.execute([[DROP TABLE t;]])
 
 test:finish_test()
-- 
2.25.1



More information about the Tarantool-patches mailing list