From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B8EB9310FB for ; Fri, 7 Jun 2019 11:37:53 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 908shjbLxxTn for ; Fri, 7 Jun 2019 11:37:53 -0400 (EDT) Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 55E4A31189 for ; Fri, 7 Jun 2019 11:37:53 -0400 (EDT) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 6/6] sql: allow to specify UNSIGNED column type Date: Fri, 7 Jun 2019 18:37:46 +0300 Message-Id: In-Reply-To: References: In-Reply-To: References: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, Nikita Pettik Since all preparations concerning internal handling of unsigned values have been done, now nothing prevents us from using UNSIGNED type in SQL. This patch allows to specify UNSIGNED as a column type and adds CAST rules, which are the same as for casual INTEGER, but with additional check - result must be positive. Otherwise, error is raised. Closes #4015 --- extra/mkkeywordhash.c | 1 + src/box/sql/parse.y | 1 + src/box/sql/vdbemem.c | 10 ++++++- test/sql/types.result | 72 +++++++++++++++++++++++++++++++++++++++++++++++++ test/sql/types.test.lua | 17 ++++++++++++ 5 files changed, 100 insertions(+), 1 deletion(-) diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c index 2ad74eddc..ff59d60cf 100644 --- a/extra/mkkeywordhash.c +++ b/extra/mkkeywordhash.c @@ -209,6 +209,7 @@ static Keyword aKeywordTable[] = { { "UNION", "TK_UNION", COMPOUND, true }, { "UNIQUE", "TK_UNIQUE", ALWAYS, true }, { "UNKNOWN", "TK_NULL", ALWAYS, true }, + { "UNSIGNED", "TK_UNSIGNED", ALWAYS, true }, { "UPDATE", "TK_UPDATE", ALWAYS, true }, { "USING", "TK_USING", ALWAYS, true }, { "VALUES", "TK_VALUES", ALWAYS, true }, diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index f241b8d52..ee0d6ce79 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -1766,6 +1766,7 @@ typedef(A) ::= VARCHAR char_len(B) . { typedef(A) ::= number_typedef(A) . number_typedef(A) ::= FLOAT_KW|REAL|DOUBLE . { A.type = FIELD_TYPE_NUMBER; } number_typedef(A) ::= INT|INTEGER_KW . { A.type = FIELD_TYPE_INTEGER; } +number_typedef(A) ::= UNSIGNED . { A.type = FIELD_TYPE_UNSIGNED; } /** * NUMERIC type is temporary disabled. To be enabled when diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c index 25119ff16..cef5196dd 100644 --- a/src/box/sql/vdbemem.c +++ b/src/box/sql/vdbemem.c @@ -699,12 +699,15 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type) return 0; return -1; case FIELD_TYPE_INTEGER: + case FIELD_TYPE_UNSIGNED: if ((pMem->flags & MEM_Blob) != 0) { bool is_neg; int64_t val; if (sql_atoi64(pMem->z, (int64_t *) &val, &is_neg, pMem->n) != 0) return -1; + if (type == FIELD_TYPE_UNSIGNED && is_neg) + return -1; mem_set_int(pMem, val, is_neg); return 0; } @@ -713,7 +716,12 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type) MemSetTypeFlag(pMem, MEM_UInt); return 0; } - return sqlVdbeMemIntegerify(pMem, true); + if (sqlVdbeMemIntegerify(pMem, true) != 0) + return -1; + if (type == FIELD_TYPE_UNSIGNED && + (pMem->flags & MEM_UInt) == 0) + return -1; + return 0; case FIELD_TYPE_NUMBER: return sqlVdbeMemRealify(pMem); default: diff --git a/test/sql/types.result b/test/sql/types.result index e12515345..6a09bac8a 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -1466,3 +1466,75 @@ box.execute("SELECT CAST('18446744073709551615' AS INTEGER);") rows: - [18446744073709551615] ... +-- gh-4015: introduce unsigned type in SQL. +-- +box.execute("CREATE TABLE t1 (id UNSIGNED PRIMARY KEY);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t1 VALUES (0), (1), (2);") +--- +- row_count: 3 +... +box.execute("INSERT INTO t1 VALUES (-3);") +--- +- error: 'Failed to execute SQL statement: Tuple field 1 type does not match one required + by operation: expected unsigned' +... +box.execute("SELECT id FROM t1;") +--- +- metadata: + - name: ID + type: unsigned + rows: + - [0] + - [1] + - [2] +... +box.execute("SELECT CAST(123 AS UNSIGNED);") +--- +- metadata: + - name: CAST(123 AS UNSIGNED) + type: unsigned + rows: + - [123] +... +box.execute("SELECT CAST(-123 AS UNSIGNED);") +--- +- error: 'Type mismatch: can not convert -123 to unsigned' +... +box.execute("SELECT CAST(1.5 AS UNSIGNED);") +--- +- metadata: + - name: CAST(1.5 AS UNSIGNED) + type: unsigned + rows: + - [1] +... +box.execute("SELECT CAST(-1.5 AS UNSIGNED);") +--- +- error: 'Type mismatch: can not convert -1 to unsigned' +... +box.execute("SELECT CAST(true AS UNSIGNED);") +--- +- metadata: + - name: CAST(true AS UNSIGNED) + type: unsigned + rows: + - [1] +... +box.execute("SELECT CAST('123' AS UNSIGNED);") +--- +- metadata: + - name: CAST('123' AS UNSIGNED) + type: unsigned + rows: + - [123] +... +box.execute("SELECT CAST('-123' AS UNSIGNED);") +--- +- error: 'Type mismatch: can not convert -123 to unsigned' +... +box.space.T1:drop() +--- +... diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index ccdd1f3d4..ddd19cde2 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -332,3 +332,20 @@ box.execute("SELECT CAST(18446744073709551615 AS TEXT);") box.execute("SELECT CAST(18446744073709551615 AS SCALAR);") box.execute("SELECT CAST(18446744073709551615 AS BOOLEAN);") box.execute("SELECT CAST('18446744073709551615' AS INTEGER);") + +-- gh-4015: introduce unsigned type in SQL. +-- +box.execute("CREATE TABLE t1 (id UNSIGNED PRIMARY KEY);") +box.execute("INSERT INTO t1 VALUES (0), (1), (2);") +box.execute("INSERT INTO t1 VALUES (-3);") +box.execute("SELECT id FROM t1;") + +box.execute("SELECT CAST(123 AS UNSIGNED);") +box.execute("SELECT CAST(-123 AS UNSIGNED);") +box.execute("SELECT CAST(1.5 AS UNSIGNED);") +box.execute("SELECT CAST(-1.5 AS UNSIGNED);") +box.execute("SELECT CAST(true AS UNSIGNED);") +box.execute("SELECT CAST('123' AS UNSIGNED);") +box.execute("SELECT CAST('-123' AS UNSIGNED);") + +box.space.T1:drop() -- 2.15.1