From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp37.i.mail.ru (smtp37.i.mail.ru [94.100.177.97]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id AE01A43E89B for ; Fri, 27 Mar 2020 14:33:42 +0300 (MSK) From: imeevma@tarantool.org Date: Fri, 27 Mar 2020 14:33:40 +0300 Message-Id: <01024a2279b5c030f272d2a97ce8bd83f8ababc3.1585308644.git.imeevma@gmail.com> In-Reply-To: References: Subject: [Tarantool-patches] [PATCH v4 1/3] sql: fix CAST() from STRING to INTEGER List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: korablev@tarantool.org, tsafin@tarantool.org, tarantool-patches@dev.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