From: imeevma@tarantool.org
To: korablev@tarantool.org, tsafin@tarantool.org,
tarantool-patches@dev.tarantool.org
Subject: [Tarantool-patches] [PATCH v3 1/2] sql: fix CAST() from STRING to INTEGER
Date: Wed, 25 Mar 2020 14:38:06 +0300 [thread overview]
Message-ID: <8f687a7553ab76d61eb125631bba4655f10b919e.1585135623.git.imeevma@gmail.com> (raw)
In-Reply-To: <cover.1585135623.git.imeevma@gmail.com>
Prior to this patch, STRING, which contains the DOUBLE value,
could be cast to INTEGER. This was done by converting STRING to
DOUBLE and then converting this DOUBLE value to INTEGER. This may
affect the accuracy of CAST(), so it was forbidden.
Before patch:
tarantool> box.execute("SELECT CAST('1.1' as INTEGER);")
---
- metadata:
- name: CAST('1.1' as INTEGER)
type: integer
rows:
- [1]
...
After patch:
tarantool> box.execute("SELECT CAST('1.1' as INTEGER);")
---
- null
- 'Type mismatch: can not convert 1.1 to integer'
...
Part of #4766
---
src/box/sql/vdbe.c | 11 +++++++--
src/box/sql/vdbeInt.h | 1 -
src/box/sql/vdbemem.c | 45 +++++++++++-------------------------
test/sql-tap/e_select1.test.lua | 2 +-
test/sql-tap/intpkey.test.lua | 2 +-
test/sql-tap/join.test.lua | 4 ++--
test/sql-tap/subquery.test.lua | 6 ++---
test/sql-tap/tkt-9a8b09f8e6.test.lua | 4 ++--
test/sql/types.result | 23 ++++++++----------
9 files changed, 41 insertions(+), 57 deletions(-)
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index e8a029a..6c0e5bd 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -319,6 +319,8 @@ mem_apply_type(struct Mem *record, enum field_type type)
switch (type) {
case FIELD_TYPE_INTEGER:
case FIELD_TYPE_UNSIGNED:
+ if ((record->flags & (MEM_Bool | MEM_Blob)) != 0)
+ return -1;
if ((record->flags & MEM_UInt) == MEM_UInt)
return 0;
if ((record->flags & MEM_Real) == MEM_Real) {
@@ -331,8 +333,13 @@ mem_apply_type(struct Mem *record, enum field_type type)
mem_set_u64(record, u);
return 0;
}
- if (sqlVdbeMemIntegerify(record) != 0)
- return -1;
+ if ((record->flags & MEM_Str) != 0) {
+ bool is_neg;
+ int64_t i;
+ if (sql_atoi64(record->z, &i, &is_neg, record->n) != 0)
+ return -1;
+ mem_set_int(record, i, is_neg);
+ }
if ((record->flags & MEM_Int) == MEM_Int) {
if (type == FIELD_TYPE_UNSIGNED)
return -1;
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index 38305ce..2c50b67 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -525,7 +525,6 @@ int sqlVdbeMemMakeWriteable(Mem *);
int sqlVdbeMemStringify(Mem *);
int sqlVdbeIntValue(Mem *, int64_t *, bool *is_neg);
-int sqlVdbeMemIntegerify(struct Mem *pMem);
int sqlVdbeRealValue(Mem *, double *);
int
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index aad030d..3bc7303 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -551,35 +551,6 @@ mem_apply_integer_type(Mem *pMem)
}
/*
- * Convert pMem to type integer. Invalidate any prior representations.
- */
-int
-sqlVdbeMemIntegerify(struct Mem *pMem)
-{
- assert(EIGHT_BYTE_ALIGNMENT(pMem));
-
- int64_t i;
- bool is_neg;
- if (sqlVdbeIntValue(pMem, &i, &is_neg) == 0) {
- mem_set_int(pMem, i, is_neg);
- return 0;
- }
-
- double d;
- if (sqlVdbeRealValue(pMem, &d) != 0)
- return -1;
- if (d < INT64_MAX && d >= INT64_MIN) {
- mem_set_int(pMem, d, d <= -1);
- return 0;
- }
- if (d >= INT64_MAX && d < UINT64_MAX) {
- mem_set_u64(pMem, d);
- return 0;
- }
- return -1;
-}
-
-/*
* Convert pMem so that it is of type MEM_Real.
* Invalidate any prior representations.
*/
@@ -696,7 +667,7 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
return -1;
case FIELD_TYPE_INTEGER:
case FIELD_TYPE_UNSIGNED:
- if ((pMem->flags & MEM_Blob) != 0) {
+ if ((pMem->flags & (MEM_Blob | MEM_Str)) != 0) {
bool is_neg;
int64_t val;
if (sql_atoi64(pMem->z, &val, &is_neg, pMem->n) != 0)
@@ -711,8 +682,20 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
MemSetTypeFlag(pMem, MEM_UInt);
return 0;
}
- if (sqlVdbeMemIntegerify(pMem) != 0)
+ if ((pMem->flags & MEM_Real) != 0) {
+ double d;
+ if (sqlVdbeRealValue(pMem, &d) != 0)
+ return -1;
+ if (d < INT64_MAX && d >= INT64_MIN) {
+ mem_set_int(pMem, d, d <= -1);
+ return 0;
+ }
+ if (d >= INT64_MAX && d < UINT64_MAX) {
+ mem_set_u64(pMem, d);
+ return 0;
+ }
return -1;
+ }
if (type == FIELD_TYPE_UNSIGNED &&
(pMem->flags & MEM_UInt) == 0)
return -1;
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index c1818dd..1d3b964 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -2195,7 +2195,7 @@ test:do_select_tests(
{"1", "SELECT b FROM f1 ORDER BY a LIMIT 0 ", {}},
{"2", "SELECT b FROM f1 ORDER BY a DESC LIMIT 4 ", {"z", "y", "x", "w"}},
{"3", "SELECT b FROM f1 ORDER BY a DESC LIMIT 8 ", {"z", "y", "x", "w", "v", "u", "t", "s"}},
- {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}},
+ {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT '12' ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}},
})
-- EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return
diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
index bec2670..b6b1866 100755
--- a/test/sql-tap/intpkey.test.lua
+++ b/test/sql-tap/intpkey.test.lua
@@ -788,7 +788,7 @@ test:do_execsql_test(
test:do_execsql_test(
"intpkey-13.2",
[[
- INSERT INTO t1 VALUES('1.0',2,3);
+ INSERT INTO t1 VALUES('1',2,3);
SELECT * FROM t1 WHERE a=1;
]], {
-- <intpkey-13.2>
diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua
index 4f014e0..840b780 100755
--- a/test/sql-tap/join.test.lua
+++ b/test/sql-tap/join.test.lua
@@ -1017,7 +1017,7 @@ test:do_test(
return test:execsql [[
CREATE TABLE t1(a TEXT primary key, b TEXT);
CREATE TABLE t2(b INTEGER primary key, a TEXT);
- INSERT INTO t1 VALUES('one', '1.0');
+ INSERT INTO t1 VALUES('one', '1');
INSERT INTO t1 VALUES('two', '2');
INSERT INTO t2 VALUES(1, 'one');
INSERT INTO t2 VALUES(2, 'two');
@@ -1034,7 +1034,7 @@ test:do_execsql_test(
SELECT * FROM t1 NATURAL JOIN t2
]], {
-- <join-11.9>
- "one", "1.0", "two", "2"
+ "one", "1", "two", "2"
-- </join-11.9>
})
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index 6bedf58..15c4c82 100755
--- a/test/sql-tap/subquery.test.lua
+++ b/test/sql-tap/subquery.test.lua
@@ -342,7 +342,7 @@ test:do_execsql_test(
INSERT INTO t3 VALUES(10);
CREATE TABLE t4(x TEXT PRIMARY KEY);
- INSERT INTO t4 VALUES('10.0');
+ INSERT INTO t4 VALUES('10');
]], {
-- <subquery-2.5.1>
@@ -363,7 +363,7 @@ test:do_test(
]]
end, {
-- <subquery-2.5.2>
- "10.0"
+ "10"
-- </subquery-2.5.2>
})
@@ -378,7 +378,7 @@ test:do_test(
]]
end, {
-- <subquery-2.5.3.1>
- "10.0"
+ "10"
-- </subquery-2.5.3.1>
})
diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua
index db0881c..cb5348a 100755
--- a/test/sql-tap/tkt-9a8b09f8e6.test.lua
+++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua
@@ -196,7 +196,7 @@ test:do_execsql_test(
test:do_execsql_test(
3.4,
[[
- SELECT x FROM t2 WHERE x IN ('1.0');
+ SELECT x FROM t2 WHERE x IN ('1');
]], {
-- <3.4>
1
@@ -236,7 +236,7 @@ test:do_execsql_test(
test:do_execsql_test(
3.8,
[[
- SELECT x FROM t2 WHERE '1.0' IN (x);
+ SELECT x FROM t2 WHERE '1' IN (x);
]], {
-- <3.8>
1
diff --git a/test/sql/types.result b/test/sql/types.result
index 38e4385..54aff46 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -269,11 +269,8 @@ box.space.T1:drop()
--
box.execute("SELECT CAST('1.123' AS INTEGER);")
---
-- metadata:
- - name: CAST('1.123' AS INTEGER)
- type: integer
- rows:
- - [1]
+- null
+- 'Type mismatch: can not convert 1.123 to integer'
...
box.execute("CREATE TABLE t1 (f TEXT PRIMARY KEY);")
---
@@ -285,13 +282,8 @@ box.execute("INSERT INTO t1 VALUES('0.0'), ('1.5'), ('3.9312453');")
...
box.execute("SELECT CAST(f AS INTEGER) FROM t1;")
---
-- metadata:
- - name: CAST(f AS INTEGER)
- type: integer
- rows:
- - [0]
- - [1]
- - [3]
+- null
+- 'Type mismatch: can not convert 0.0 to integer'
...
box.space.T1:drop()
---
@@ -1105,8 +1097,11 @@ box.execute("SELECT CAST(1.5 AS UNSIGNED);")
...
box.execute("SELECT CAST(-1.5 AS UNSIGNED);")
---
-- null
-- 'Type mismatch: can not convert -1 to unsigned'
+- metadata:
+ - name: CAST(-1.5 AS UNSIGNED)
+ type: unsigned
+ rows:
+ - [-1]
...
box.execute("SELECT CAST(true AS UNSIGNED);")
---
--
2.7.4
next prev parent reply other threads:[~2020-03-25 11:38 UTC|newest]
Thread overview: 7+ messages / expand[flat|nested] mbox.gz Atom feed top
2020-03-25 11:38 [Tarantool-patches] [PATCH v3 0/2] sql: fix CAST() from BLOB " imeevma
2020-03-25 11:38 ` imeevma [this message]
2020-03-25 18:10 ` [Tarantool-patches] [PATCH v3 1/2] sql: fix CAST() from STRING " Nikita Pettik
2020-03-25 11:38 ` [Tarantool-patches] [PATCH v3 2/2] sql: add '\0' to the BLOB when it is cast " imeevma
2020-03-25 18:17 ` Nikita Pettik
2020-03-25 17:46 ` [Tarantool-patches] [PATCH v3 0/2] sql: fix CAST() from BLOB " Nikita Pettik
2020-03-27 11:30 ` Mergen Imeev
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=8f687a7553ab76d61eb125631bba4655f10b919e.1585135623.git.imeevma@gmail.com \
--to=imeevma@tarantool.org \
--cc=korablev@tarantool.org \
--cc=tarantool-patches@dev.tarantool.org \
--cc=tsafin@tarantool.org \
--subject='Re: [Tarantool-patches] [PATCH v3 1/2] sql: fix CAST() from STRING to INTEGER' \
/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