From: Nikita Pettik <korablev@tarantool.org>
To: tarantool-patches@dev.tarantool.org
Cc: v.shpilevoy@tarantool.org
Subject: [Tarantool-patches] [PATCH 3/4] sql: fix CAST AS NUMBER operator
Date: Wed, 5 Feb 2020 19:19:11 +0300 [thread overview]
Message-ID: <8e0dd524221265bfba1906e1bc6fb0608b1f0c45.1580841722.git.korablev@tarantool.org> (raw)
In-Reply-To: <cover.1580841722.git.korablev@tarantool.org>
In-Reply-To: <cover.1580841722.git.korablev@tarantool.org>
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
next prev parent reply other threads:[~2020-02-05 16:19 UTC|newest]
Thread overview: 17+ messages / expand[flat|nested] mbox.gz Atom feed top
2020-02-05 16:19 [Tarantool-patches] [PATCH 0/4] sql: fix NUMBER conversion issues Nikita Pettik
2020-02-05 16:19 ` [Tarantool-patches] [PATCH 1/4] sql: remove cast to INT during FP arithmetic ops Nikita Pettik
2020-02-05 16:19 ` [Tarantool-patches] [PATCH 2/4] sql: refactor sqlVdbeMemNumerify() Nikita Pettik
2020-02-10 23:25 ` Vladislav Shpilevoy
2020-02-11 14:14 ` Nikita Pettik
2020-02-11 22:17 ` Vladislav Shpilevoy
2020-02-05 16:19 ` Nikita Pettik [this message]
2020-02-10 23:24 ` [Tarantool-patches] [PATCH 3/4] sql: fix CAST AS NUMBER operator Vladislav Shpilevoy
2020-02-11 14:14 ` Nikita Pettik
2020-02-11 22:17 ` Vladislav Shpilevoy
2020-02-11 23:20 ` Nikita Pettik
2020-02-11 23:27 ` Vladislav Shpilevoy
2020-02-12 14:10 ` Nikita Pettik
2020-02-05 16:19 ` [Tarantool-patches] [PATCH 4/4] sql: do not force FP representation for NUMBER field Nikita Pettik
2020-02-10 23:24 ` Vladislav Shpilevoy
2020-02-11 14:14 ` Nikita Pettik
2020-02-09 17:39 ` [Tarantool-patches] [PATCH 0/4] sql: fix NUMBER conversion issues Vladislav Shpilevoy
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=8e0dd524221265bfba1906e1bc6fb0608b1f0c45.1580841722.git.korablev@tarantool.org \
--to=korablev@tarantool.org \
--cc=tarantool-patches@dev.tarantool.org \
--cc=v.shpilevoy@tarantool.org \
--subject='Re: [Tarantool-patches] [PATCH 3/4] sql: fix CAST AS NUMBER operator' \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox