[Tarantool-patches] [PATCH v2 1/6] sql: rework implicit cast fo assignment
imeevma at tarantool.org
imeevma at tarantool.org
Fri Aug 6 09:42:38 MSK 2021
After this patch, the new rules will be applied to implicit cast during
assignment. According to these rules, all scalar values can be cast to
SCALAR, all numeric values can be cast to NUMBER, and any numeric value
can be cast to another numeric type only if the conversion is exact.
No other implicit cast is allowed.
Part of #4470
---
.../gh-4470-implicit-cast-for-assignment.md | 6 ++
src/box/sql/func.c | 2 +-
src/box/sql/mem.c | 46 ++++++---
test/sql-tap/cast.test.lua | 94 ++++++++++++++++++-
test/sql-tap/numcast.test.lua | 5 +-
test/sql-tap/uuid.test.lua | 56 +++++------
test/sql/types.result | 3 +-
7 files changed, 158 insertions(+), 54 deletions(-)
create mode 100644 changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md
diff --git a/changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md b/changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md
new file mode 100644
index 000000000..e3c21f620
--- /dev/null
+++ b/changelogs/unreleased/gh-4470-implicit-cast-for-assignment.md
@@ -0,0 +1,6 @@
+## feature/sql
+
+* Now a numeric value can be cast to another numeric type only if the cast is
+ precise. In addition, a UUID value cannot be implicitly cast to
+ STRING/VARBINARY, and a STRING/VARBINARY value cannot be implicitly cast to
+ a UUID (gh-4470).
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 6ca852dec..e153db24b 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -58,7 +58,7 @@ static const void *
mem_as_bin(struct Mem *mem)
{
const char *s;
- if (mem_cast_implicit(mem, FIELD_TYPE_VARBINARY) != 0 &&
+ if (mem_cast_explicit(mem, FIELD_TYPE_VARBINARY) != 0 &&
mem_to_str(mem) != 0)
return NULL;
if (mem_get_bin(mem, &s) != 0)
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index eebc47f4e..c0ceb98e9 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -624,6 +624,34 @@ int_to_double(struct Mem *mem)
return 0;
}
+static inline int
+int_to_double_precise(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_INT);
+ double d;
+ d = (double)mem->u.i;
+ if (mem->u.i != (int64_t)d)
+ return -1;
+ mem->u.r = d;
+ mem->type = MEM_TYPE_DOUBLE;
+ mem->field_type = FIELD_TYPE_DOUBLE;
+ return 0;
+}
+
+static inline int
+uint_to_double_precise(struct Mem *mem)
+{
+ assert(mem->type == MEM_TYPE_UINT);
+ double d;
+ d = (double)mem->u.u;
+ if (mem->u.u != (uint64_t)d)
+ return -1;
+ mem->u.r = d;
+ mem->type = MEM_TYPE_DOUBLE;
+ mem->field_type = FIELD_TYPE_DOUBLE;
+ return 0;
+}
+
static inline int
int_to_str0(struct Mem *mem)
{
@@ -1083,25 +1111,25 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
if (mem->type == MEM_TYPE_UINT)
return 0;
if (mem->type == MEM_TYPE_DOUBLE)
- return double_to_uint(mem);
+ return double_to_uint_precise(mem);
return -1;
case FIELD_TYPE_STRING:
if (mem->type == MEM_TYPE_STR)
return 0;
- if (mem->type == MEM_TYPE_UUID)
- return uuid_to_str0(mem);
return -1;
case FIELD_TYPE_DOUBLE:
if (mem->type == MEM_TYPE_DOUBLE)
return 0;
- if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0)
- return int_to_double(mem);
+ if (mem->type == MEM_TYPE_INT)
+ return int_to_double_precise(mem);
+ if (mem->type == MEM_TYPE_UINT)
+ return uint_to_double_precise(mem);
return -1;
case FIELD_TYPE_INTEGER:
if ((mem->type & (MEM_TYPE_INT | MEM_TYPE_UINT)) != 0)
return 0;
if (mem->type == MEM_TYPE_DOUBLE)
- return double_to_int(mem);
+ return double_to_int_precise(mem);
return -1;
case FIELD_TYPE_BOOLEAN:
if (mem->type == MEM_TYPE_BOOL)
@@ -1111,8 +1139,6 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
if ((mem->type & (MEM_TYPE_BIN | MEM_TYPE_MAP |
MEM_TYPE_ARRAY)) != 0)
return 0;
- if (mem->type == MEM_TYPE_UUID)
- return uuid_to_bin(mem);
return -1;
case FIELD_TYPE_NUMBER:
if (mem_is_num(mem))
@@ -1133,10 +1159,6 @@ mem_cast_implicit(struct Mem *mem, enum field_type type)
case FIELD_TYPE_UUID:
if (mem->type == MEM_TYPE_UUID)
return 0;
- if (mem->type == MEM_TYPE_STR)
- return str_to_uuid(mem);
- if (mem->type == MEM_TYPE_BIN)
- return bin_to_uuid(mem);
return -1;
case FIELD_TYPE_ANY:
return 0;
diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua
index 799bcc1a8..8af99dbde 100755
--- a/test/sql-tap/cast.test.lua
+++ b/test/sql-tap/cast.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
local test = require("sqltester")
-test:plan(95)
+test:plan(103)
--!./tcltestrunner.lua
-- 2005 June 25
@@ -875,7 +875,7 @@ test:do_test(
-- </cast-4.4>
})
--- gh-4470: Make explicit casts work according to our rules.
+-- gh-4470: Make explicit and implicit casts work according to our rules.
-- Make sure that explicit cast from BOOLEAN to numeric types throws an error.
test:do_catchsql_test(
@@ -1017,4 +1017,94 @@ test:do_execsql_test(
true
})
+-- Make sure that implicit conversion of numeric values is precise.
+test:execsql([[
+ CREATE TABLE t2 (i INTEGER PRIMARY KEY AUTOINCREMENT, a INTEGER, b DOUBLE);
+ CREATE TABLE t3 (i INTEGER PRIMARY KEY AUTOINCREMENT, s STRING);
+ CREATE TABLE t4 (i INTEGER PRIMARY KEY AUTOINCREMENT, v VARBINARY);
+ CREATE TABLE t5 (i INTEGER PRIMARY KEY AUTOINCREMENT, u UUID);
+]])
+
+test:do_execsql_test(
+ "cast-9.1.1",
+ [[
+ INSERT INTO t2(a) VALUES(1.0e0);
+ SELECT a FROM t2 WHERE i = 1;
+ ]], {
+ 1
+ })
+
+test:do_catchsql_test(
+ "cast-9.1.2",
+ [[
+ INSERT INTO t2(a) VALUES(1.5e0);
+ ]], {
+ 1, "Type mismatch: can not convert double(1.5) to integer"
+ })
+
+test:do_execsql_test(
+ "cast-9.1.3",
+ [[
+ INSERT INTO t2(b) VALUES(10000000000000000);
+ SELECT b FROM t2 WHERE i = 2;
+ ]], {
+ 10000000000000000
+ })
+
+test:do_catchsql_test(
+ "cast-9.1.4",
+ [[
+ INSERT INTO t2(b) VALUES(10000000000000001);
+ ]], {
+ 1, "Type mismatch: can not convert integer(10000000000000001) to double"
+ })
+
+-- Make sure that UUID cannot be implicitly cast to STRING.
+local uuid = "CAST('11111111-1111-1111-1111-111111111111' AS UUID)";
+test:do_catchsql_test(
+ "cast-9.2",
+ [[
+ INSERT INTO t3(s) VALUES(]]..uuid..[[);
+ ]], {
+ 1, "Type mismatch: can not convert "..
+ "uuid('11111111-1111-1111-1111-111111111111') to string"
+ })
+
+-- Make sure that UUID cannot be implicitly cast to VARBINARY.
+test:do_catchsql_test(
+ "cast-9.3",
+ [[
+ INSERT INTO t4(v) VALUES(]]..uuid..[[);
+ ]], {
+ 1, "Type mismatch: can not convert "..
+ "uuid('11111111-1111-1111-1111-111111111111') to varbinary"
+ })
+
+-- Make sure that STRING and VARBINARY cannot be implicitly cast to UUID.
+test:do_catchsql_test(
+ "cast-9.4.1",
+ [[
+ INSERT INTO t5(u) VALUES('11111111-1111-1111-1111-111111111111');
+ ]], {
+ 1, "Type mismatch: can not convert "..
+ "string('11111111-1111-1111-1111-111111111111') to uuid"
+ })
+
+test:do_catchsql_test(
+ "cast-9.4.2",
+ [[
+ INSERT INTO t5(u) VALUES(x'11111111111111111111111111111111');
+ ]], {
+ 1, "Type mismatch: can not convert "..
+ "varbinary(x'11111111111111111111111111111111') to uuid"
+ })
+
+test:execsql([[
+ DROP TABLE t1;
+ DROP TABLE t2;
+ DROP TABLE t3;
+ DROP TABLE t4;
+ DROP TABLE t5;
+]])
+
test:finish_test()
diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
index 9ecda6833..b172ca625 100755
--- a/test/sql-tap/numcast.test.lua
+++ b/test/sql-tap/numcast.test.lua
@@ -136,13 +136,12 @@ test:do_catchsql_test(
1,"Type mismatch: can not convert double(2.0e+19) to integer"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"cast-2.9",
[[
INSERT INTO t VALUES(2.1);
- SELECT * FROM t;
]], {
- 2, 9223372036854775808ULL, 18000000000000000000ULL
+ 1, "Type mismatch: can not convert double(2.1) to integer"
})
--
diff --git a/test/sql-tap/uuid.test.lua b/test/sql-tap/uuid.test.lua
index ad91c3e1c..abb5960a7 100755
--- a/test/sql-tap/uuid.test.lua
+++ b/test/sql-tap/uuid.test.lua
@@ -3,7 +3,7 @@ local build_path = os.getenv("BUILDDIR")
package.cpath = build_path..'/test/sql-tap/?.so;'..build_path..'/test/sql-tap/?.dylib;'..package.cpath
local test = require("sqltester")
-test:plan(147)
+test:plan(146)
local uuid = require("uuid")
local uuid1 = uuid.fromstr("11111111-1111-1111-1111-111111111111")
@@ -722,15 +722,12 @@ test:do_catchsql_test(
1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to unsigned"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-8.1.2",
[[
INSERT INTO ts(s) SELECT u FROM t2;
- SELECT * FROM ts;
]], {
- 1, "11111111-1111-1111-1111-111111111111",
- 2, "11111111-3333-1111-1111-111111111111",
- 3, "22222222-1111-1111-1111-111111111111"
+ 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to string"
})
test:do_catchsql_test(
@@ -765,15 +762,12 @@ test:do_catchsql_test(
1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to boolean"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-8.1.7",
[[
INSERT INTO tv(v) SELECT u FROM t2;
- SELECT id, hex(v) FROM tv;
]], {
- 1, "11111111111111111111111111111111",
- 2, "11111111333311111111111111111111",
- 3, "22222222111111111111111111111111"
+ 1, "Type mismatch: can not convert uuid('11111111-1111-1111-1111-111111111111') to varbinary"
})
test:do_execsql_test(
@@ -803,13 +797,12 @@ test:do_catchsql_test(
1, "Type mismatch: can not convert integer(1) to uuid"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-8.2.2",
[[
INSERT INTO tsu VALUES ('2_string_right', '11111111-1111-1111-1111-111111111111');
- SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
]], {
- '2_string_right', uuid1
+ 1, "Type mismatch: can not convert string('11111111-1111-1111-1111-111111111111') to uuid"
})
test:do_catchsql_test(
@@ -844,13 +837,12 @@ test:do_catchsql_test(
1, "Type mismatch: can not convert boolean(TRUE) to uuid"
})
-test:do_execsql_test(
+test:do_catchsql_test(
"uuid-8.2.7",
[[
INSERT INTO tsu SELECT '7_varbinary', x'11111111111111111111111111111111' FROM t2 LIMIT 1;
- SELECT * FROM tsu ORDER BY s DESC LIMIT 1;
]], {
- '7_varbinary', uuid1
+ 1, "Type mismatch: can not convert varbinary(x'11111111111111111111111111111111') to uuid"
})
test:do_catchsql_test(
@@ -877,24 +869,27 @@ test:do_execsql_test(
uuid1, uuid3, uuid2
})
-test:execsql([[
- CREATE TABLE t10 (i INT PRIMARY KEY AUTOINCREMENT, u UUID DEFAULT '11111111-1111-1111-1111-111111111111');
-]])
+local default = "DEFAULT(CAST('11111111-1111-1111-1111-111111111111' AS UUID))"
+test:execsql(
+ "CREATE TABLE t10 (i INT PRIMARY KEY AUTOINCREMENT, u UUID "..default..");"
+)
-- Check that INSERT into UUID field works.
+local uuid2_str = "'22222222-1111-1111-1111-111111111111'";
test:do_execsql_test(
"uuid-10.1.1",
[[
- INSERT INTO t10 VALUES (1, '22222222-1111-1111-1111-111111111111');
+ INSERT INTO t10 VALUES (1, CAST(]]..uuid2_str..[[ AS UUID));
SELECT * FROM t10 WHERE i = 1;
]], {
1, uuid2
})
+local uuid2_bin = "x'22222222111111111111111111111111'";
test:do_execsql_test(
"uuid-10.1.2",
[[
- INSERT INTO t10 VALUES (2, x'22222222111111111111111111111111');
+ INSERT INTO t10 VALUES (2, CAST(]]..uuid2_bin..[[ AS UUID));
SELECT * FROM t10 WHERE i = 2;
]], {
2, uuid2
@@ -920,21 +915,12 @@ test:do_execsql_test(
-- Check that UPDATE of UUID field works.
test:do_execsql_test(
- "uuid-10.2.1",
+ "uuid-10",
[[
- UPDATE t10 SET u = '11111111-3333-1111-1111-111111111111' WHERE i = 1;
- SELECT * FROM t10 WHERE i = 1;
- ]], {
- 1, uuid3
- })
-
-test:do_execsql_test(
- "uuid-10.2.2",
- [[
- UPDATE t10 SET u = x'11111111333311111111111111111111' WHERE i = 2;
- SELECT * FROM t10 WHERE i = 2;
+ UPDATE t10 SET u = CAST(]]..uuid2_str..[[ AS UUID) WHERE i = 3;
+ SELECT * FROM t10 WHERE i = 3;
]], {
- 2, uuid3
+ 3, uuid2
})
-- Check that JOIN by UUID field works.
diff --git a/test/sql/types.result b/test/sql/types.result
index bb0109d6d..00c60cca9 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -2577,7 +2577,8 @@ box.execute([[UPDATE td SET d = 11 WHERE a = 1;]])
...
box.execute([[UPDATE td SET d = 100000000000000001 WHERE a = 1;]])
---
-- row_count: 1
+- null
+- 'Type mismatch: can not convert integer(100000000000000001) to double'
...
box.execute([[UPDATE td SET d = 22.2 WHERE a = 1;]])
---
--
2.25.1
More information about the Tarantool-patches
mailing list