[Tarantool-patches] [PATCH 3/4] sql: fix CAST AS NUMBER operator
Nikita Pettik
korablev at tarantool.org
Wed Feb 5 19:19:11 MSK 2020
NUMBER type is supposed to include values of both integer and FP types.
Hence, if numeric value is casted to NUMBER it remains unchanged.
Before this patch cast to NUMBER always resulted in forcing floating
point representation. Furthermore, CAST of blob values to NUMBER always
led the floating point result, even if blob value had precise integer
representation. Since now NUMBER doesn't imply only FP values, let's fix
this and use vdbe_mem_numerify() which provides unified way of casting
to NUMBER type.
Part of #4233
Closes #4463
---
src/box/sql/vdbemem.c | 19 ++---------------
test/sql-tap/cast.test.lua | 32 ++++++++++++++--------------
test/sql-tap/e_select1.test.lua | 2 +-
test/sql-tap/numcast.test.lua | 46 ++++++++++++++++++++++++++++++++++++++++-
test/sql/boolean.result | 9 ++++++--
test/sql/types.result | 2 +-
6 files changed, 72 insertions(+), 38 deletions(-)
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index a533a2fe7..aad030df9 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -668,22 +668,6 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
assert(type < field_type_MAX);
if (pMem->flags & MEM_Null)
return 0;
- if ((pMem->flags & MEM_Blob) != 0 && type == FIELD_TYPE_NUMBER) {
- bool is_neg;
- if (sql_atoi64(pMem->z, (int64_t *) &pMem->u.i, &is_neg,
- pMem->n) == 0) {
- MemSetTypeFlag(pMem, MEM_Real);
- if (is_neg)
- pMem->u.r = pMem->u.i;
- else
- pMem->u.r = pMem->u.u;
- return 0;
- }
- if (sqlAtoF(pMem->z, &pMem->u.r, pMem->n) == 0)
- return -1;
- MemSetTypeFlag(pMem, MEM_Real);
- return 0;
- }
switch (type) {
case FIELD_TYPE_SCALAR:
return 0;
@@ -734,8 +718,9 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
return -1;
return 0;
case FIELD_TYPE_DOUBLE:
- case FIELD_TYPE_NUMBER:
return sqlVdbeMemRealify(pMem);
+ case FIELD_TYPE_NUMBER:
+ return vdbe_mem_numerify(pMem);
case FIELD_TYPE_VARBINARY:
if ((pMem->flags & MEM_Blob) != 0)
return 0;
diff --git a/test/sql-tap/cast.test.lua b/test/sql-tap/cast.test.lua
index 9c937a065..a3f0bc787 100755
--- a/test/sql-tap/cast.test.lua
+++ b/test/sql-tap/cast.test.lua
@@ -628,14 +628,14 @@ test:do_execsql_test(
SELECT CAST(9223372036854774800 AS NUMBER)
]], {
-- <cast-3.2>
- 9223372036854774784
+ 9223372036854774800LL
-- </cast-3.2>
})
test:do_execsql_test(
"cast-3.3",
"SELECT CAST(9223372036854774800 AS NUMBER)",
- {9.22337203685477e+18})
+ {9223372036854774800LL})
test:do_execsql_test(
"cast-3.4",
@@ -643,7 +643,7 @@ test:do_execsql_test(
SELECT CAST(CAST(9223372036854774800 AS NUMBER) AS integer)
]], {
-- <cast-3.4>
- 9223372036854774784LL
+ 9223372036854774800LL
-- </cast-3.4>
})
@@ -663,14 +663,14 @@ test:do_execsql_test(
SELECT CAST(-9223372036854774800 AS NUMBER)
]], {
-- <cast-3.6>
- -9223372036854774784
+ -9223372036854774800LL
-- </cast-3.6>
})
test:do_execsql_test(
"cast-3.7",
"SELECT CAST(-9223372036854774800 AS NUMBER)",
- {-9.22337203685477e+18})
+ {-9223372036854774800LL})
test:do_execsql_test(
"cast-3.8",
@@ -678,7 +678,7 @@ test:do_execsql_test(
SELECT CAST(CAST(-9223372036854774800 AS NUMBER) AS integer)
]], {
-- <cast-3.8>
- -9223372036854774784LL
+ -9223372036854774800LL
-- </cast-3.8>
})
@@ -695,7 +695,7 @@ test:do_execsql_test(
test:do_execsql_test(
"cast-3.12",
[[
- SELECT CAST('9223372036854774800' AS NUMBER)
+ SELECT CAST('9223372036854774800.' AS NUMBER)
]], {
-- <cast-3.12>
9223372036854774784
@@ -705,12 +705,12 @@ test:do_execsql_test(
test:do_execsql_test(
"cast-3.13",
"SELECT CAST(9223372036854774800 AS NUMBER)",
- {9.22337203685477e+18})
+ {9223372036854774800LL})
test:do_execsql_test(
"cast-3.14",
[[
- SELECT CAST(CAST('9223372036854774800' AS NUMBER) AS integer)
+ SELECT CAST(CAST('9223372036854774800.' AS NUMBER) AS integer)
]], {
-- <cast-3.14>
9223372036854774784LL
@@ -732,7 +732,7 @@ test:do_execsql_test(
test:do_execsql_test(
"cast-3.16",
[[
- SELECT CAST('-9223372036854774800' AS NUMBER)
+ SELECT CAST('-9223372036854774800.' AS NUMBER)
]], {
-- <cast-3.16>
-9223372036854774784
@@ -741,13 +741,13 @@ test:do_execsql_test(
test:do_execsql_test(
"cast-3.17",
- "SELECT CAST('-9223372036854774800' AS NUMBER)",
+ "SELECT CAST('-9223372036854774800.' AS NUMBER)",
{-9.22337203685477e+18})
test:do_execsql_test(
"cast-3.18",
[[
- SELECT CAST(CAST('-9223372036854774800' AS NUMBER) AS integer)
+ SELECT CAST(CAST('-9223372036854774800.' AS NUMBER) AS integer)
]], {
-- <cast-3.18>
-9223372036854774784LL
@@ -770,7 +770,7 @@ if true then --test:execsql("PRAGMA encoding")[1][1]=="UTF-8" then
test:do_execsql_test(
"cast-3.22",
[[
- SELECT CAST(x'39323233333732303336383534373734383030' AS NUMBER)
+ SELECT CAST(x'393232333337323033363835343737343830302E' AS NUMBER)
]], {
-- <cast-3.22>
9223372036854774784
@@ -778,7 +778,7 @@ if true then --test:execsql("PRAGMA encoding")[1][1]=="UTF-8" then
})
test:do_execsql_test(
"cast-3.23",
- "SELECT CAST(x'39323233333732303336383534373734383030' AS NUMBER)",
+ "SELECT CAST(x'393232333337323033363835343737343830302E' AS NUMBER)",
{9.22337203685477e+18})
test:do_execsql_test(
@@ -788,7 +788,7 @@ if true then --test:execsql("PRAGMA encoding")[1][1]=="UTF-8" then
AS integer)
]], {
-- <cast-3.24>
- 9223372036854774784LL
+ 9223372036854774800LL
-- </cast-3.24>
})
end
@@ -796,7 +796,7 @@ end
test:do_execsql_test(
"case-3.25",
[[
- SELECT CAST(x'3138343436373434303733373039353531363135' AS NUMBER);
+ SELECT CAST(x'31383434363734343037333730393535313631352E' AS NUMBER);
]], { 1.844674407371e+19 } )
test:do_execsql_test(
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index 48d8c226e..c1818ddb1 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -760,7 +760,7 @@ test:do_execsql_test(
INSERT INTO z1 VALUES(6, 63, '0', -26);
INSERT INTO z2 VALUES(1, NULL, 21);
- INSERT INTO z2 VALUES(2, 36, 6);
+ INSERT INTO z2 VALUES(2, 36.0, 6.0);
INSERT INTO z3 VALUES(1, 123.21, 123.12);
INSERT INTO z3 VALUES(2, 49.17, -67);
diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
index 07117d08e..87c5f6b35 100755
--- a/test/sql-tap/numcast.test.lua
+++ b/test/sql-tap/numcast.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(20)
+test:plan(25)
--!./tcltestrunner.lua
-- 2013 March 20
@@ -147,4 +147,48 @@ test:do_catchsql_test(
1,"Tuple field 1 type does not match one required by operation: expected integer"
})
+--
+-- gh-4233: Make sure that NUMBER can contain UNSIGNED, INTEGER
+-- and DOUBLE and is not automatically converted to DOUBLE.
+--
+test:do_execsql_test(
+ "numcast-3.1",
+ [[
+ SELECT CAST(x'3131313131313131313131313131313131313131' AS NUMBER);
+ ]], {
+ 11111111111111111111ULL
+ })
+
+test:do_execsql_test(
+ "numcast-3.2",
+ [[
+ SELECT CAST(x'31313131313131313131313131313131313131312E' AS NUMBER);
+ ]], {
+ 11111111111111110656
+ })
+
+test:do_execsql_test(
+ "numcast-3.3",
+ [[
+ SELECT CAST('11111111111111111111' AS NUMBER);
+ ]], {
+ 11111111111111111111ULL
+ })
+
+test:do_execsql_test(
+ "numcast-3.4",
+ [[
+ SELECT CAST('101' AS NUMBER) / 10, CAST('101.' AS NUMBER) / 10;
+ ]], {
+ 10, 10.1
+ })
+
+test:do_execsql_test(
+ "numcast-3.5",
+ [[
+ SELECT CAST('101 ' AS NUMBER) / 10, CAST(' 101' AS NUMBER) / 10;
+ ]], {
+ 10, 10
+ })
+
test:finish_test()
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index 7769d0cb3..112e41a12 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -510,8 +510,13 @@ SELECT cast(true AS INTEGER), cast(false AS INTEGER);
| ...
SELECT cast(true AS NUMBER), cast(false AS NUMBER);
| ---
- | - null
- | - 'Type mismatch: can not convert TRUE to number'
+ | - metadata:
+ | - name: cast(true AS NUMBER)
+ | type: number
+ | - name: cast(false AS NUMBER)
+ | type: number
+ | rows:
+ | - [1, 0]
| ...
-- gh-4462: ensure that text representation is uppercase.
SELECT cast(true AS TEXT), cast(false AS TEXT);
diff --git a/test/sql/types.result b/test/sql/types.result
index f0c34b6fa..38e4385ad 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -1023,7 +1023,7 @@ box.execute("SELECT CAST(18446744073709551615 AS NUMBER);")
- name: CAST(18446744073709551615 AS NUMBER)
type: number
rows:
- - [1.844674407371e+19]
+ - [18446744073709551615]
...
box.execute("SELECT CAST(18446744073709551615 AS TEXT);")
---
--
2.15.1
More information about the Tarantool-patches
mailing list