[tarantool-patches] [PATCH 5/5] sql: introduce VARBINARY column type
Nikita Pettik
korablev at tarantool.org
Wed Jul 24 14:42:47 MSK 2019
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
More information about the Tarantool-patches
mailing list