[tarantool-patches] [PATCH 5/5] sql: introduce VARBINARY column type

[Nikita Pettik <korablev@tarantool.org>]

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