[Tarantool-patches] [PATCH v4 1/3] sql: fix CAST() from STRING to INTEGER

[imeevma at tarantool.org]

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:
box.execute("SELECT CAST('111.1' as INTEGER);")
Result: 111

After patch:
box.execute("SELECT CAST('1.1' as INTEGER);")
Result: 'Type mismatch: can not convert 1.1 to integer'

box.execute("SELECT CAST('1.0' as INTEGER);")
Result: 'Type mismatch: can not convert 1.0 to integer'

box.execute("SELECT CAST('1.' as INTEGER);")
Result: 'Type mismatch: can not convert 1. to integer'

Part of #4766
---
 src/box/sql/vdbemem.c                              | 16 +++++-
 .../gh-4766-wrong-cast-from-blob-to-int.test.lua   | 57 ++++++++++++++++++++++
 test/sql/types.result                              | 23 ++++-----
 3 files changed, 80 insertions(+), 16 deletions(-)
 create mode 100755 test/sql-tap/gh-4766-wrong-cast-from-blob-to-int.test.lua

diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index aad030d..de1d9c3 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -696,7 +696,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 +711,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/gh-4766-wrong-cast-from-blob-to-int.test.lua b/test/sql-tap/gh-4766-wrong-cast-from-blob-to-int.test.lua
new file mode 100755
index 0000000..0865c4e
--- /dev/null
+++ b/test/sql-tap/gh-4766-wrong-cast-from-blob-to-int.test.lua
@@ -0,0 +1,57 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(6)
+
+--
+-- Make sure that STRING or BLOB that contains DOUBLE value cannot
+-- be cast to INTEGER.
+--
+test:do_catchsql_test(
+    "gh-4766-1",
+    [[
+        SELECT CAST('1.1' AS INTEGER)
+    ]], {
+        1, "Type mismatch: can not convert 1.1 to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-4766-2",
+    [[
+        SELECT CAST(x'312e31' AS INTEGER)
+    ]], {
+        1, "Type mismatch: can not convert varbinary to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-4766-3",
+    [[
+        SELECT CAST('2.0' AS INTEGER)
+    ]], {
+        1, "Type mismatch: can not convert 2.0 to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-4766-4",
+    [[
+        SELECT CAST(x'322e30' AS INTEGER)
+    ]], {
+        1, "Type mismatch: can not convert varbinary to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-4766-5",
+    [[
+        SELECT CAST('2.' AS INTEGER)
+    ]], {
+        1, "Type mismatch: can not convert 2. to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-4766-6",
+    [[
+        SELECT CAST(x'322e' AS INTEGER)
+    ]], {
+        1, "Type mismatch: can not convert varbinary to integer"
+    })
+
+test:finish_test()
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