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 82A90228EE for ; Wed, 24 Jul 2019 07:42: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 zBifuI8iEeEk for ; Wed, 24 Jul 2019 07:42:53 -0400 (EDT) Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (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 21A03227A3 for ; Wed, 24 Jul 2019 07:42:53 -0400 (EDT) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 5/5] sql: introduce VARBINARY column type Date: Wed, 24 Jul 2019 14:42:47 +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 Current patch introduces new type available in SQL: - VARBINARY now is reserved keyword; - Allow to specify VARBINARY column and CAST type; - All literals which start from 'x' are assumed to be of this type; - There's no available implicit or explicit conversions between VARBINARY and other types; - Under the hood all values of VARBINARY type are stored as MP_BIN msgpack format type. Closes #4206 --- extra/mkkeywordhash.c | 1 + src/box/sql/expr.c | 2 +- src/box/sql/func.c | 4 +- src/box/sql/parse.y | 3 +- src/box/sql/vdbe.c | 26 ++- src/box/sql/vdbemem.c | 5 + test/sql-tap/keyword1.test.lua | 3 +- test/sql/gh-3888-values-blob-assert.result | 4 +- test/sql/iproto.result | 4 +- test/sql/misc.result | 2 +- test/sql/types.result | 256 ++++++++++++++++++++++++++++- test/sql/types.test.lua | 60 +++++++ 12 files changed, 345 insertions(+), 25 deletions(-) diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c index b83294cb3..49c9565c7 100644 --- a/extra/mkkeywordhash.c +++ b/extra/mkkeywordhash.c @@ -188,6 +188,7 @@ static Keyword aKeywordTable[] = { { "UPDATE", "TK_UPDATE", ALWAYS, true }, { "USING", "TK_USING", ALWAYS, true }, { "VALUES", "TK_VALUES", ALWAYS, true }, + { "VARBINARY", "TK_VARBINARY", ALWAYS, true }, { "VIEW", "TK_VIEW", VIEW, true }, { "WITH", "TK_WITH", CTE, true }, { "WHEN", "TK_WHEN", ALWAYS, true }, diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 97f5bd180..4448f01cb 100644 --- a/src/box/sql/expr.c +++ b/src/box/sql/expr.c @@ -2235,7 +2235,7 @@ sql_expr_needs_no_type_change(const struct Expr *p, enum field_type type) case TK_STRING: return type == FIELD_TYPE_STRING; case TK_BLOB: - return true; + return type == FIELD_TYPE_VARBINARY; case TK_COLUMN: /* p cannot be part of a CHECK constraint. */ assert(p->iTable >= 0); diff --git a/src/box/sql/func.c b/src/box/sql/func.c index 5fd1496fd..34e80cd32 100644 --- a/src/box/sql/func.c +++ b/src/box/sql/func.c @@ -1926,13 +1926,13 @@ sqlRegisterBuiltinFunctions(void) FUNCTION2(ifnull, 2, 0, 0, noopFunc, SQL_FUNC_COALESCE, FIELD_TYPE_INTEGER), VFUNCTION(random, 0, 0, 0, randomFunc, FIELD_TYPE_INTEGER), - VFUNCTION(randomblob, 1, 0, 0, randomBlob, FIELD_TYPE_SCALAR), + VFUNCTION(randomblob, 1, 0, 0, randomBlob, FIELD_TYPE_VARBINARY), FUNCTION(nullif, 2, 0, 1, nullifFunc, FIELD_TYPE_SCALAR), FUNCTION(version, 0, 0, 0, sql_func_version, FIELD_TYPE_STRING), FUNCTION(quote, 1, 0, 0, quoteFunc, FIELD_TYPE_STRING), VFUNCTION(row_count, 0, 0, 0, sql_row_count, FIELD_TYPE_INTEGER), FUNCTION_COLL(replace, 3, 0, 0, replaceFunc), - FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc, FIELD_TYPE_SCALAR), + FUNCTION(zeroblob, 1, 0, 0, zeroblobFunc, FIELD_TYPE_VARBINARY), FUNCTION_COLL(substr, 2, 0, 0, substrFunc), FUNCTION_COLL(substr, 3, 0, 0, substrFunc), AGGREGATE(sum, 1, 0, 0, sum_step, sumFinalize, diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index 010feffd4..1512b37d7 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -897,7 +897,7 @@ idlist(A) ::= nm(Y). { p->type = FIELD_TYPE_STRING; break; case TK_BLOB: - p->type = FIELD_TYPE_SCALAR; + p->type = FIELD_TYPE_VARBINARY; break; case TK_INTEGER: p->type = FIELD_TYPE_INTEGER; @@ -1724,6 +1724,7 @@ typedef(A) ::= SCALAR . { A.type = FIELD_TYPE_SCALAR; } /** BOOL | BOOLEAN is not used due to possible bug in Lemon. */ typedef(A) ::= BOOL . { A.type = FIELD_TYPE_BOOLEAN; } typedef(A) ::= BOOLEAN . { A.type = FIELD_TYPE_BOOLEAN; } +typedef(A) ::= VARBINARY . { A.type = FIELD_TYPE_VARBINARY; } /** * Time-like types are temporary disabled, until they are diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 826232f99..d0f0cb4f5 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -337,6 +337,10 @@ mem_apply_type(struct Mem *record, enum field_type type) } record->flags &= ~(MEM_Real | MEM_Int); return 0; + case FIELD_TYPE_VARBINARY: + if ((record->flags & MEM_Blob) == 0) + return -1; + return 0; case FIELD_TYPE_SCALAR: return 0; default: @@ -2094,20 +2098,24 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */ } break; } - } else if ((flags1 | flags3) & MEM_Bool) { + } else if (((flags1 | flags3) & MEM_Bool) != 0 || + ((flags1 | flags3) & MEM_Blob) != 0) { /* - * If one of values is of type BOOLEAN, then the - * second one must be BOOLEAN as well. Otherwise - * an error is raised. + * If one of values is of type BOOLEAN or VARBINARY, + * then the second one must be of the same type as + * well. Otherwise an error is raised. */ - bool is_bool_type_arg1 = flags1 & MEM_Bool; - bool is_bool_type_arg3 = flags3 & MEM_Bool; - if (! is_bool_type_arg1 || ! is_bool_type_arg3) { - char *inconsistent_type = ! is_bool_type_arg1 ? + int type_arg1 = flags1 & (MEM_Bool | MEM_Blob); + int type_arg3 = flags3 & (MEM_Bool | MEM_Blob); + if (type_arg1 != type_arg3) { + char *inconsistent_type = type_arg1 != 0 ? + mem_type_to_str(pIn3) : + mem_type_to_str(pIn1); + char *expected_type = type_arg1 != 0 ? mem_type_to_str(pIn1) : mem_type_to_str(pIn3); diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - inconsistent_type, "boolean"); + inconsistent_type, expected_type); goto abort_due_to_error; } res = sqlMemCompare(pIn3, pIn1, NULL); diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c index d85148bc3..3aaf3e04f 100644 --- a/src/box/sql/vdbemem.c +++ b/src/box/sql/vdbemem.c @@ -691,6 +691,11 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type) return sqlVdbeMemIntegerify(pMem, true); case FIELD_TYPE_NUMBER: return sqlVdbeMemRealify(pMem); + case FIELD_TYPE_VARBINARY: + /* VARIBNARY can't be converted to any other type. */ + if ((pMem->flags & MEM_Blob) != 0) + return 0; + return -1; default: assert(type == FIELD_TYPE_STRING); assert(MEM_Str == (MEM_Blob >> 3)); diff --git a/test/sql-tap/keyword1.test.lua b/test/sql-tap/keyword1.test.lua index 9c524d607..4266f93d7 100755 --- a/test/sql-tap/keyword1.test.lua +++ b/test/sql-tap/keyword1.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(176) +test:plan(177) --!./tcltestrunner.lua -- 2009 January 29 @@ -186,6 +186,7 @@ local bannedkws = { "sql", "user", "varchar", + "varbinary", "whenever", "while" } diff --git a/test/sql/gh-3888-values-blob-assert.result b/test/sql/gh-3888-values-blob-assert.result index 81b0f52fd..bb396a0df 100644 --- a/test/sql/gh-3888-values-blob-assert.result +++ b/test/sql/gh-3888-values-blob-assert.result @@ -55,7 +55,7 @@ box.execute('SELECT X\'507265766564\'') --- - metadata: - name: X'507265766564' - type: scalar + type: varbinary rows: - ['Preved'] ... @@ -72,7 +72,7 @@ box.execute('SELECT X\'4D6564766564\'') --- - metadata: - name: X'4D6564766564' - type: scalar + type: varbinary rows: - ['Medved'] ... diff --git a/test/sql/iproto.result b/test/sql/iproto.result index 9639ba7a6..7efc31355 100644 --- a/test/sql/iproto.result +++ b/test/sql/iproto.result @@ -771,7 +771,7 @@ cn:execute("SELECT zeroblob(1);") --- - metadata: - name: zeroblob(1) - type: scalar + type: varbinary rows: - ["\0"] ... @@ -784,7 +784,7 @@ res = cn:execute("SELECT randomblob(1);") res.metadata --- - - name: randomblob(1) - type: scalar + type: varbinary ... -- Type set during compilation stage, and since min/max are accept -- arguments of all scalar type, we can't say nothing more than diff --git a/test/sql/misc.result b/test/sql/misc.result index bc8b10e87..96cfb3e84 100644 --- a/test/sql/misc.result +++ b/test/sql/misc.result @@ -102,7 +102,7 @@ box.execute('SELECT X\'4D6564766564\'') --- - metadata: - name: X'4D6564766564' - type: scalar + type: varbinary rows: - ['Medved'] ... diff --git a/test/sql/types.result b/test/sql/types.result index 332ebd43b..5a7d42e5a 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -355,15 +355,15 @@ box.execute("SELECT unknown = true;") ... box.execute("SELECT 1 = true;") --- -- error: 'Type mismatch: can not convert INTEGER to boolean' +- error: 'Type mismatch: can not convert INTEGER to BOOLEAN' ... box.execute("SELECT 'abc' = true;") --- -- error: 'Type mismatch: can not convert TEXT to boolean' +- error: 'Type mismatch: can not convert TEXT to BOOLEAN' ... box.execute("SELECT 1.123 > true;") --- -- error: 'Type mismatch: can not convert REAL to boolean' +- error: 'Type mismatch: can not convert REAL to BOOLEAN' ... box.execute("SELECT true IN (1, 'abc', true)") --- @@ -626,7 +626,7 @@ box.execute("SELECT upper(b) FROM t;") ... box.execute("SELECT abs(b) FROM t;") --- -- error: 'Inconsistent types: expected number got boolean' +- error: 'Inconsistent types: expected number got BOOLEAN' ... box.execute("SELECT typeof(b) FROM t;") --- @@ -895,11 +895,11 @@ box.execute("INSERT INTO t1 VALUES (3, 'abc'), (4, 12.5);") ... box.execute("SELECT s FROM t1 WHERE s = true;") --- -- error: 'Type mismatch: can not convert TEXT to boolean' +- error: 'Type mismatch: can not convert TEXT to BOOLEAN' ... box.execute("SELECT s FROM t1 WHERE s < true;") --- -- error: 'Type mismatch: can not convert TEXT to boolean' +- error: 'Type mismatch: can not convert TEXT to BOOLEAN' ... box.execute("SELECT s FROM t1 WHERE s IN (true, 1, 'abcd')") --- @@ -1001,3 +1001,247 @@ box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 ELSE 'asd' END") rows: - [666] ... +-- Test basic capabilities of VARBINARY type. +-- +box.execute("CREATE TABLE t (id INT PRIMARY KEY, v VARBINARY);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t VALUES(1, 1);") +--- +- error: 'Type mismatch: can not convert 1 to varbinary' +... +box.execute("INSERT INTO t VALUES(1, 1.123);") +--- +- error: 'Type mismatch: can not convert 1.123 to varbinary' +... +box.execute("INSERT INTO t VALUES(1, true);") +--- +- error: 'Type mismatch: can not convert true to varbinary' +... +box.execute("INSERT INTO t VALUES(1, 'asd');") +--- +- error: 'Type mismatch: can not convert asd to varbinary' +... +box.execute("INSERT INTO t VALUES(1, x'616263');") +--- +- row_count: 1 +... +box.execute("SELECT * FROM t WHERE v = 1") +--- +- error: 'Type mismatch: can not convert INTEGER to VARBINARY' +... +box.execute("SELECT * FROM t WHERE v = 1.123") +--- +- error: 'Type mismatch: can not convert REAL to VARBINARY' +... +box.execute("SELECT * FROM t WHERE v = 'str'") +--- +- error: 'Type mismatch: can not convert TEXT to VARBINARY' +... +box.execute("SELECT * FROM t WHERE v = x'616263'") +--- +- metadata: + - name: ID + type: integer + - name: V + type: varbinary + rows: + - [1, 'abc'] +... +box.execute("SELECT sum(v) FROM t;") +--- +- error: 'Type mismatch: can not convert abc to number' +... +box.execute("SELECT avg(v) FROM t;") +--- +- error: 'Type mismatch: can not convert abc to number' +... +box.execute("SELECT total(v) FROM t;") +--- +- error: 'Type mismatch: can not convert abc to number' +... +box.execute("SELECT min(v) FROM t;") +--- +- metadata: + - name: min(v) + type: scalar + rows: + - ['abc'] +... +box.execute("SELECT max(v) FROM t;") +--- +- metadata: + - name: max(v) + type: scalar + rows: + - ['abc'] +... +box.execute("SELECT count(v) FROM t;") +--- +- metadata: + - name: count(v) + type: integer + rows: + - [1] +... +box.execute("SELECT group_concat(v) FROM t;") +--- +- metadata: + - name: group_concat(v) + type: string + rows: + - ['abc'] +... +box.execute("SELECT lower(v) FROM t;") +--- +- error: 'Inconsistent types: expected TEXT got VARBINARY' +... +box.execute("SELECT upper(v) FROM t;") +--- +- error: 'Inconsistent types: expected TEXT got VARBINARY' +... +box.execute("SELECT abs(v) FROM t;") +--- +- error: 'Inconsistent types: expected number got VARBINARY' +... +box.execute("SELECT typeof(v) FROM t;") +--- +- metadata: + - name: typeof(v) + type: string + rows: + - ['varbinary'] +... +box.execute("SELECT quote(v) FROM t;") +--- +- metadata: + - name: quote(v) + type: string + rows: + - ['X''616263'''] +... +box.execute("SELECT min(v, x'') FROM t;") +--- +- metadata: + - name: min(v, x'') + type: scalar + rows: + - [''] +... +box.execute("CREATE INDEX iv ON t(v);") +--- +- row_count: 1 +... +box.execute("SELECT v FROM t WHERE v = x'616263';") +--- +- metadata: + - name: V + type: varbinary + rows: + - ['abc'] +... +box.execute("SELECT v FROM t ORDER BY v;") +--- +- metadata: + - name: V + type: varbinary + rows: + - ['abc'] +... +box.execute("UPDATE t SET v = x'636261' WHERE v = x'616263';") +--- +- row_count: 1 +... +box.execute("SELECT v FROM t;") +--- +- metadata: + - name: V + type: varbinary + rows: + - ['cba'] +... +box.execute("CREATE TABLE parent (id INT PRIMARY KEY, a VARBINARY UNIQUE);") +--- +- row_count: 1 +... +box.space.T:truncate() +--- +... +box.execute("ALTER TABLE t ADD CONSTRAINT fk1 FOREIGN KEY (v) REFERENCES parent (a);") +--- +- row_count: 1 +... +box.execute("INSERT INTO t VALUES (1, x'616263');") +--- +- error: 'Failed to execute SQL statement: FOREIGN KEY constraint failed' +... +box.execute("INSERT INTO parent VALUES (1, x'616263');") +--- +- row_count: 1 +... +box.execute("INSERT INTO t VALUES (1, x'616263');") +--- +- row_count: 1 +... +box.execute("ALTER TABLE t DROP CONSTRAINT fk1;") +--- +- row_count: 1 +... +box.space.PARENT:drop() +--- +... +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a VARBINARY CHECK (a = x'616263'));") +--- +- row_count: 1 +... +box.execute("INSERT INTO t1 VALUES (1, x'006162');") +--- +- error: 'Check constraint failed ''CK_CONSTRAINT_1_T1'': a = x''616263''' +... +box.execute("INSERT INTO t1 VALUES (1, x'616263');") +--- +- row_count: 1 +... +box.space.T1:drop() +--- +... +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a VARBINARY DEFAULT x'616263');") +--- +- row_count: 1 +... +box.execute("INSERT INTO t1 (id) VALUES (1);") +--- +- row_count: 1 +... +box.space.T1:select() +--- +- - [1, 'abc'] +... +box.space.T1:drop() +--- +... +box.execute("SELECT CAST(1 AS VARBINARY);") +--- +- error: 'Type mismatch: can not convert 1 to varbinary' +... +box.execute("SELECT CAST(1.123 AS VARBINARY);") +--- +- error: 'Type mismatch: can not convert 1.123 to varbinary' +... +box.execute("SELECT CAST(true AS VARBINARY);") +--- +- error: 'Type mismatch: can not convert true to varbinary' +... +box.execute("SELECT CAST('asd' AS VARBINARY);") +--- +- error: 'Type mismatch: can not convert asd to varbinary' +... +box.execute("SELECT CAST(x'' AS VARBINARY);") +--- +- metadata: + - name: CAST(x'' AS VARBINARY) + type: varbinary + rows: + - [''] +... diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index b66a3e068..fb2e824d4 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -242,3 +242,63 @@ box.execute("SELECT CASE 1 WHEN 1 THEN x'0000000000' WHEN 2 THEN 'str' END") box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 END") box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 ELSE 321 END") box.execute("SELECT CASE 1 WHEN 1 THEN 666 WHEN 2 THEN 123 ELSE 'asd' END") + +-- Test basic capabilities of VARBINARY type. +-- +box.execute("CREATE TABLE t (id INT PRIMARY KEY, v VARBINARY);") +box.execute("INSERT INTO t VALUES(1, 1);") +box.execute("INSERT INTO t VALUES(1, 1.123);") +box.execute("INSERT INTO t VALUES(1, true);") +box.execute("INSERT INTO t VALUES(1, 'asd');") +box.execute("INSERT INTO t VALUES(1, x'616263');") +box.execute("SELECT * FROM t WHERE v = 1") +box.execute("SELECT * FROM t WHERE v = 1.123") +box.execute("SELECT * FROM t WHERE v = 'str'") +box.execute("SELECT * FROM t WHERE v = x'616263'") + +box.execute("SELECT sum(v) FROM t;") +box.execute("SELECT avg(v) FROM t;") +box.execute("SELECT total(v) FROM t;") +box.execute("SELECT min(v) FROM t;") +box.execute("SELECT max(v) FROM t;") +box.execute("SELECT count(v) FROM t;") +box.execute("SELECT group_concat(v) FROM t;") + +box.execute("SELECT lower(v) FROM t;") +box.execute("SELECT upper(v) FROM t;") +box.execute("SELECT abs(v) FROM t;") +box.execute("SELECT typeof(v) FROM t;") +box.execute("SELECT quote(v) FROM t;") +box.execute("SELECT min(v, x'') FROM t;") + +box.execute("CREATE INDEX iv ON t(v);") +box.execute("SELECT v FROM t WHERE v = x'616263';") +box.execute("SELECT v FROM t ORDER BY v;") + +box.execute("UPDATE t SET v = x'636261' WHERE v = x'616263';") +box.execute("SELECT v FROM t;") + +box.execute("CREATE TABLE parent (id INT PRIMARY KEY, a VARBINARY UNIQUE);") +box.space.T:truncate() +box.execute("ALTER TABLE t ADD CONSTRAINT fk1 FOREIGN KEY (v) REFERENCES parent (a);") +box.execute("INSERT INTO t VALUES (1, x'616263');") +box.execute("INSERT INTO parent VALUES (1, x'616263');") +box.execute("INSERT INTO t VALUES (1, x'616263');") +box.execute("ALTER TABLE t DROP CONSTRAINT fk1;") +box.space.PARENT:drop() + +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a VARBINARY CHECK (a = x'616263'));") +box.execute("INSERT INTO t1 VALUES (1, x'006162');") +box.execute("INSERT INTO t1 VALUES (1, x'616263');") +box.space.T1:drop() + +box.execute("CREATE TABLE t1 (id INT PRIMARY KEY, a VARBINARY DEFAULT x'616263');") +box.execute("INSERT INTO t1 (id) VALUES (1);") +box.space.T1:select() +box.space.T1:drop() + +box.execute("SELECT CAST(1 AS VARBINARY);") +box.execute("SELECT CAST(1.123 AS VARBINARY);") +box.execute("SELECT CAST(true AS VARBINARY);") +box.execute("SELECT CAST('asd' AS VARBINARY);") +box.execute("SELECT CAST(x'' AS VARBINARY);") -- 2.15.1