[Tarantool-patches] [PATCH v1 0/3] Introduce field type ANY to SQL

[Tarantool-patches] [PATCH v1 0/3] Introduce field type ANY to SQL

[Mergen Imeev via Tarantool-patches]

This patch-set introduces field type ANY to SQL.

https://github.com/tarantool/tarantool/issues/3174
https://github.com/tarantool/tarantool/tree/imeevma/gh-3174-introduce-field-type-any

Mergen Imeev (3):
  sql: properly set INTEGER type in struct Expr
  sql: use field_type_MAX instead of FIELD_TYPE_ANY
  sql: introduce field type ANY

 extra/mkkeywordhash.c           |   3 +-
 src/box/sql/expr.c              |   2 +
 src/box/sql/func.c              |  24 +-
 src/box/sql/mem.c               |  51 ++-
 src/box/sql/mem.h               |   4 +-
 src/box/sql/parse.y             |   3 +-
 src/box/sql/resolve.c           |   1 +
 src/box/sql/select.c            |   1 +
 src/box/sql/vdbe.c              |   4 +-
 test/sql-tap/metatypes.test.lua | 568 ++++++++++++++++++++++++++++++--
 10 files changed, 609 insertions(+), 52 deletions(-)

-- 
2.25.1

[Tarantool-patches] [PATCH v1 1/3] sql: properly set INTEGER type in struct Expr

[Mergen Imeev via Tarantool-patches]

Prior to this patch, in some cases a struct Expr value with an opcode
TK_INTEGER could have field type ANY. This patch sets such values to the
INTEGER field type.

Part of #3174
---
 src/box/sql/expr.c    | 2 ++
 src/box/sql/resolve.c | 1 +
 src/box/sql/select.c  | 1 +
 3 files changed, 4 insertions(+)

diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index db8355f33..d3fde9501 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -1010,6 +1010,7 @@ sql_expr_new_int(struct sql *db, int value)
 {
 	struct Expr *e = sql_expr_new_empty(db, TK_INTEGER, 0);
 	if (e != NULL) {
+		e->type = FIELD_TYPE_INTEGER;
 		e->flags |= EP_IntValue;
 		e->u.iValue = value;
 	}
@@ -3886,6 +3887,7 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 				return target;
 			} else {
 				tempX.op = TK_INTEGER;
+				tempX.type = FIELD_TYPE_INTEGER;
 				tempX.flags = EP_IntValue | EP_TokenOnly;
 				tempX.u.iValue = 0;
 				r1 = sqlExprCodeTemp(pParse, &tempX,
diff --git a/src/box/sql/resolve.c b/src/box/sql/resolve.c
index 21fe124d7..22b4e6799 100644
--- a/src/box/sql/resolve.c
+++ b/src/box/sql/resolve.c
@@ -928,6 +928,7 @@ resolveCompoundOrderBy(Parse * pParse,	/* Parsing context.  Leave error messages
 				}
 				pNew->flags |= EP_IntValue;
 				pNew->u.iValue = iCol;
+				pNew->type = FIELD_TYPE_INTEGER;
 				if (pItem->pExpr == pE) {
 					pItem->pExpr = pNew;
 				} else {
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 8ca967108..2f97d121d 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -3566,6 +3566,7 @@ multiSelectOrderBy(Parse * pParse,	/* Parsing context */
 				}
 				pNew->flags |= EP_IntValue;
 				pNew->u.iValue = i;
+				pNew->type = FIELD_TYPE_INTEGER;
 				pOrderBy = sql_expr_list_append(pParse->db,
 								pOrderBy, pNew);
 				if (pOrderBy)
-- 
2.25.1

[Tarantool-patches] [PATCH v1 2/3] sql: use field_type_MAX instead of FIELD_TYPE_ANY

[Mergen Imeev via Tarantool-patches]

After this patch, the built-in SQL function implementation definitions
will use field_type_MAX to indicate that they accept values of any type,
instead of FIELD_TYPE_ANY.

Part of #3174
---
 src/box/sql/func.c | 22 +++++++++++-----------
 src/box/sql/mem.c  |  2 +-
 2 files changed, 12 insertions(+), 12 deletions(-)

diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 0cf17572b..63d7536d9 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -1937,10 +1937,10 @@ static struct sql_func_definition definitions[] = {
 	{"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,
+	{"COALESCE", -1, {field_type_MAX}, FIELD_TYPE_SCALAR, sql_builtin_stub,
 	 NULL},
 	{"COUNT", 0, {}, FIELD_TYPE_INTEGER, step_count, fin_count},
-	{"COUNT", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_INTEGER, step_count,
+	{"COUNT", 1, {field_type_MAX}, FIELD_TYPE_INTEGER, step_count,
 	 fin_count},
 
 	{"GREATEST", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxFunc,
@@ -1967,7 +1967,7 @@ static struct sql_func_definition definitions[] = {
 	 FIELD_TYPE_VARBINARY, step_group_concat, NULL},
 
 	{"HEX", 1, {FIELD_TYPE_VARBINARY}, FIELD_TYPE_STRING, hexFunc, NULL},
-	{"IFNULL", 2, {FIELD_TYPE_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
+	{"IFNULL", 2, {field_type_MAX, field_type_MAX}, FIELD_TYPE_SCALAR,
 	 sql_builtin_stub, NULL},
 
 	{"LEAST", -1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_INTEGER, minmaxFunc,
@@ -1988,9 +1988,9 @@ static struct sql_func_definition definitions[] = {
 	 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},
+	{"LIKELIHOOD", 2, {field_type_MAX, FIELD_TYPE_DOUBLE},
 	 FIELD_TYPE_BOOLEAN, sql_builtin_stub, NULL},
-	{"LIKELY", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_BOOLEAN, sql_builtin_stub,
+	{"LIKELY", 1, {field_type_MAX}, FIELD_TYPE_BOOLEAN, sql_builtin_stub,
 	 NULL},
 	{"LOWER", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, LowerICUFunc,
 	 NULL},
@@ -2013,13 +2013,13 @@ 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_ANY, FIELD_TYPE_ANY}, FIELD_TYPE_SCALAR,
+	{"NULLIF", 2, {field_type_MAX, field_type_MAX}, FIELD_TYPE_SCALAR,
 	 nullifFunc, NULL},
 	{"POSITION", 2, {FIELD_TYPE_STRING, FIELD_TYPE_STRING},
 	 FIELD_TYPE_INTEGER, position_func, NULL},
-	{"PRINTF", -1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, printfFunc, 
+	{"PRINTF", -1, {field_type_MAX}, FIELD_TYPE_STRING, printfFunc,
 	 NULL},
-	{"QUOTE", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, quoteFunc, NULL},
+	{"QUOTE", 1, {field_type_MAX}, FIELD_TYPE_STRING, quoteFunc, NULL},
 	{"RANDOM", 0, {}, FIELD_TYPE_INTEGER, randomFunc, NULL},
 	{"RANDOMBLOB", 1, {FIELD_TYPE_INTEGER}, FIELD_TYPE_VARBINARY,
 	 randomBlob, NULL},
@@ -2062,10 +2062,10 @@ static struct sql_func_definition definitions[] = {
 	 {FIELD_TYPE_VARBINARY, FIELD_TYPE_INTEGER, FIELD_TYPE_VARBINARY},
 	 FIELD_TYPE_VARBINARY, trim_func, NULL},
 
-	{"TYPEOF", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_STRING, typeofFunc, NULL},
+	{"TYPEOF", 1, {field_type_MAX}, FIELD_TYPE_STRING, typeofFunc, NULL},
 	{"UNICODE", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_INTEGER, unicodeFunc,
 	 NULL},
-	{"UNLIKELY", 1, {FIELD_TYPE_ANY}, FIELD_TYPE_BOOLEAN, sql_builtin_stub,
+	{"UNLIKELY", 1, {field_type_MAX}, FIELD_TYPE_BOOLEAN, sql_builtin_stub,
 	 NULL},
 	{"UPPER", 1, {FIELD_TYPE_STRING}, FIELD_TYPE_STRING, UpperICUFunc,
 	 NULL},
@@ -2112,7 +2112,7 @@ built_in_func_put(struct sql_func_dictionary *dict)
 static inline bool
 is_exact(int op, enum field_type a, enum field_type b)
 {
-	return op == TK_NULL || a == b || a == FIELD_TYPE_ANY ||
+	return op == TK_NULL || a == b || a == field_type_MAX ||
 	       (a == FIELD_TYPE_INTEGER && b == FIELD_TYPE_UNSIGNED);
 }
 
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index dc629aee3..f648c5d62 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -1489,7 +1489,7 @@ mem_cast_explicit(struct Mem *mem, enum field_type type)
 int
 mem_cast_implicit(struct Mem *mem, enum field_type type)
 {
-	if (mem->type == MEM_TYPE_NULL || type == FIELD_TYPE_ANY)
+	if (mem->type == MEM_TYPE_NULL || type == field_type_MAX)
 		return 0;
 	if ((mem->flags & MEM_Scalar) != 0 && type != FIELD_TYPE_SCALAR)
 		return -1;
-- 
2.25.1

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

[Mergen Imeev via Tarantool-patches]

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 337df4916..2ed0ab09f 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.
@@ -1837,6 +1837,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 628fcb7be..323cbf04c 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1922,7 +1922,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 3e4091675..3d60ef268 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, 2.0, 2.0);
+        INSERT INTO t VALUES(1, 1, 1, 1);
+        INSERT INTO t VALUES(2, 2.0, 2.0, 2.0);
         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.5) 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

Re: [Tarantool-patches] [PATCH v1 0/3] Introduce field type ANY to SQL

[Vladislav Shpilevoy via Tarantool-patches]

Hi! Thanks for the patchset!

LGTM.

[Tarantool-patches] [PATCH v1 1/3] sql: properly set INTEGER type in struct Expr

[Mergen Imeev via Tarantool-patches]

Prior to this patch, in some cases a struct Expr value with an opcode
TK_INTEGER could have field type ANY. This patch sets such values to the
INTEGER field type.

Part of #3174
---
 src/box/sql/expr.c    | 2 ++
 src/box/sql/resolve.c | 1 +
 src/box/sql/select.c  | 1 +
 3 files changed, 4 insertions(+)

diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 25c390062..2c8021060 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -1010,6 +1010,7 @@ sql_expr_new_int(struct sql *db, int value)
 {
 	struct Expr *e = sql_expr_new_empty(db, TK_INTEGER, 0);
 	if (e != NULL) {
+		e->type = FIELD_TYPE_INTEGER;
 		e->flags |= EP_IntValue;
 		e->u.iValue = value;
 	}
@@ -3917,6 +3918,7 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 				return target;
 			} else {
 				tempX.op = TK_INTEGER;
+				tempX.type = FIELD_TYPE_INTEGER;
 				tempX.flags = EP_IntValue | EP_TokenOnly;
 				tempX.u.iValue = 0;
 				r1 = sqlExprCodeTemp(pParse, &tempX,
diff --git a/src/box/sql/resolve.c b/src/box/sql/resolve.c
index 21fe124d7..22b4e6799 100644
--- a/src/box/sql/resolve.c
+++ b/src/box/sql/resolve.c
@@ -928,6 +928,7 @@ resolveCompoundOrderBy(Parse * pParse,	/* Parsing context.  Leave error messages
 				}
 				pNew->flags |= EP_IntValue;
 				pNew->u.iValue = iCol;
+				pNew->type = FIELD_TYPE_INTEGER;
 				if (pItem->pExpr == pE) {
 					pItem->pExpr = pNew;
 				} else {
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 8ca967108..2f97d121d 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -3566,6 +3566,7 @@ multiSelectOrderBy(Parse * pParse,	/* Parsing context */
 				}
 				pNew->flags |= EP_IntValue;
 				pNew->u.iValue = i;
+				pNew->type = FIELD_TYPE_INTEGER;
 				pOrderBy = sql_expr_list_append(pParse->db,
 								pOrderBy, pNew);
 				if (pOrderBy)
-- 
2.25.1