[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