[Tarantool-patches] [PATCH v1 2/2] sql: introduce syntax for MAP values

imeevma at tarantool.org imeevma at tarantool.org
Thu Nov 18 17:08:45 MSK 2021


This patch introduces a new syntax that allows to create MAP values in
an SQL query.

Part of #4762

@TarantoolBot document
Title: Syntax for MAP in SQL

The syntax for creating document values is available in SQL. You can use
`{`, ':' and `}` to create a MAP value. Only INTEGER, STRING and UUID
values can be keys in MAP values.

Examples:
```
tarantool> box.execute("SELECT {1 : 'a', 'asd' : 1.5, uuid() : true};")
---
- metadata:
  - name: COLUMN_1
    type: map
  rows:
  - [{1: 'a', 91ca4dbb-c6d4-4468-b4a4-ab1e409dd87e: true, 'asd': 1.5}]
...
```

```
tarantool> box.execute("SELECT {'h' : ['abc', 321], 7 : {'b' : 1.5}};")
---
- metadata:
  - name: COLUMN_1
    type: map
  rows:
  - [{7: {'b': 1.5}, 'h': ['abc', 321]}]
...
```
---
 src/box/sql/expr.c        |  33 +++++++++
 src/box/sql/mem.c         |  39 +++++++++++
 src/box/sql/mem.h         |  16 +++++
 src/box/sql/parse.y       |  35 +++++++++-
 src/box/sql/tokenize.c    |  21 +++++-
 src/box/sql/vdbe.c        |  20 ++++++
 test/sql-tap/map.test.lua | 138 +++++++++++++++++++++++++++++++++++++-
 7 files changed, 296 insertions(+), 6 deletions(-)

diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 74a98c550..789d8906c 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -3432,6 +3432,35 @@ expr_code_array(struct Parse *parser, struct Expr *expr, int reg)
 	sqlVdbeAddOp3(vdbe, OP_Array, count, reg, values_reg);
 }
 
+static void
+expr_code_map(struct Parse *parser, struct Expr *expr, int reg)
+{
+	struct Vdbe *vdbe = parser->pVdbe;
+	struct ExprList *list = expr->x.pList;
+	if (list == NULL) {
+		sqlVdbeAddOp3(vdbe, OP_Map, 0, reg, 0);
+		return;
+	}
+	int count = list->nExpr;
+	assert(count % 2 == 0);
+	for (int i = 0; i < count / 2; ++i) {
+		struct Expr *expr = list->a[2 * i].pExpr;
+		enum field_type type = sql_expr_type(expr);
+		if (expr->op != TK_VARIABLE && type != FIELD_TYPE_INTEGER &&
+		    type != FIELD_TYPE_UNSIGNED && type != FIELD_TYPE_STRING &&
+		    type != FIELD_TYPE_UUID) {
+			diag_set(ClientError, ER_SQL_PARSER_GENERIC, "Only "
+				 "integer, string and uuid can be keys in map");
+			parser->is_aborted = true;
+			return;
+		}
+	}
+	int values_reg = parser->nMem + 1;
+	parser->nMem += count;
+	sqlExprCodeExprList(parser, list, values_reg, 0, SQL_ECEL_FACTOR);
+	sqlVdbeAddOp3(vdbe, OP_Map, count, reg, values_reg);
+}
+
 /*
  * Erase column-cache entry number i
  */
@@ -3887,6 +3916,10 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 		expr_code_array(pParse, pExpr, target);
 		break;
 
+	case TK_MAP:
+		expr_code_map(pParse, pExpr, target);
+		return target;
+
 	case TK_LT:
 	case TK_LE:
 	case TK_GT:
diff --git a/src/box/sql/mem.c b/src/box/sql/mem.c
index b598fe5c2..fe7029341 100644
--- a/src/box/sql/mem.c
+++ b/src/box/sql/mem.c
@@ -3043,6 +3043,45 @@ mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size,
 	return array;
 }
 
+char *
+mem_encode_map(const struct Mem *mems, uint32_t count, uint32_t *size,
+	       struct region *region)
+{
+	assert(count % 2 == 0);
+	size_t used = region_used(region);
+	bool is_error = false;
+	struct mpstream stream;
+	mpstream_init(&stream, region, region_reserve_cb, region_alloc_cb,
+		      set_encode_error, &is_error);
+	mpstream_encode_map(&stream, (count + 1) / 2);
+	for (uint32_t i = 0; i < count / 2; ++i) {
+		const struct Mem *key = &mems[2 * i];
+		const struct Mem *value = &mems[2 * i + 1];
+		if (mem_is_metatype(key) ||
+		    (key->type & (MEM_TYPE_UINT | MEM_TYPE_INT | MEM_TYPE_UUID |
+				  MEM_TYPE_STR)) == 0) {
+			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+				 mem_str(key), "integer, string or uuid");
+			return NULL;
+		}
+		mem_to_mpstream(key, &stream);
+		mem_to_mpstream(value, &stream);
+	}
+	mpstream_flush(&stream);
+	if (is_error) {
+		diag_set(OutOfMemory, stream.pos - stream.buf,
+			 "mpstream_flush", "stream");
+		return NULL;
+	}
+	*size = region_used(region) - used;
+	char *map = region_join(region, *size);
+	if (map == NULL) {
+		diag_set(OutOfMemory, *size, "region_join", "map");
+		return NULL;
+	}
+	return map;
+}
+
 /**
  * Allocate a sequence of initialized vdbe memory registers
  * on region.
diff --git a/src/box/sql/mem.h b/src/box/sql/mem.h
index b0128921f..83bb30ccf 100644
--- a/src/box/sql/mem.h
+++ b/src/box/sql/mem.h
@@ -861,3 +861,19 @@ mem_to_mpstream(const struct Mem *var, struct mpstream *stream);
 char *
 mem_encode_array(const struct Mem *mems, uint32_t count, uint32_t *size,
 		 struct region *region);
+
+/**
+ * Encode array of MEMs as msgpack map on region. Values in even position are
+ * treated as keys in MAP, values in odd position are treated as values in MAP.
+ * number of MEMs should be even.
+ *
+ * @param mems array of MEMs to encode.
+ * @param count number of elements in the array.
+ * @param[out] size Size of encoded msgpack map.
+ * @param region Region to use.
+ * @retval NULL on error, diag message is set.
+ * @retval Pointer to valid msgpack map on success.
+ */
+char *
+mem_encode_map(const struct Mem *mems, uint32_t count, uint32_t *size,
+	       struct region *region);
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 06e6244e3..db7fef71a 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1100,12 +1100,12 @@ expr(A) ::= VARNUM(X). {
     sqlExprAssignVarNumber(pParse, A.pExpr, X.n);
   }
 }
-expr(A) ::= VARIABLE(X) id(Y).     {
+expr(A) ::= COLON|VARIABLE(X) id(Y).     {
   A.pExpr = expr_variable(pParse, &X, &Y);
   A.zStart = X.z;
   A.zEnd = &Y.z[Y.n];
 }
-expr(A) ::= VARIABLE(X) INTEGER(Y).     {
+expr(A) ::= COLON|VARIABLE(X) INTEGER(Y).     {
   A.pExpr = expr_variable(pParse, &X, &Y);
   A.zStart = X.z;
   A.zEnd = &Y.z[Y.n];
@@ -1140,6 +1140,37 @@ expr(A) ::= LB(X) exprlist(Y) RB(E). {
   spanSet(&A, &X, &E);
 }
 
+expr(A) ::= LCB(X) maplist(Y) RCB(E). {
+  struct sql *db = pParse->db;
+  struct Expr *expr = sql_expr_new_dequoted(db, TK_MAP, NULL);
+  if (expr == NULL) {
+    sql_expr_list_delete(db, Y);
+    pParse->is_aborted = true;
+    return;
+  }
+  expr->x.pList = Y;
+  expr->type = FIELD_TYPE_MAP;
+  sqlExprSetHeightAndFlags(pParse, expr);
+  A.pExpr = expr;
+  spanSet(&A, &X, &E);
+}
+
+maplist(A) ::= nmaplist(A).
+maplist(A) ::= .                                  {A = 0;}
+nmaplist(A) ::= nmaplist(A) COMMA expr(X) COLON expr(Y). {
+  A = sql_expr_list_append(pParse->db, A, X.pExpr);
+  A = sql_expr_list_append(pParse->db, A, Y.pExpr);
+}
+nmaplist(A) ::= expr(X) COLON expr(Y). {
+  A = sql_expr_list_append(pParse->db, NULL, X.pExpr);
+  A = sql_expr_list_append(pParse->db, A, Y.pExpr);
+}
+
+%type maplist {ExprList*}
+%destructor maplist {sql_expr_list_delete(pParse->db, $$);}
+%type nmaplist {ExprList*}
+%destructor nmaplist {sql_expr_list_delete(pParse->db, $$);}
+
 expr(A) ::= TRIM(X) LP trim_operands(Y) RP(E). {
   A.pExpr = sqlExprFunction(pParse, Y, &X);
   spanSet(&A, &X, &E);
diff --git a/src/box/sql/tokenize.c b/src/box/sql/tokenize.c
index 8bc519b9d..9e85801a3 100644
--- a/src/box/sql/tokenize.c
+++ b/src/box/sql/tokenize.c
@@ -58,7 +58,9 @@
 #define CC_KYWD       1		/* Alphabetics or '_'.  Usable in a keyword */
 #define CC_ID         2		/* unicode characters usable in IDs */
 #define CC_DIGIT      3		/* Digits */
-/** SQL variables: '@', '#', ':', and '$'. */
+/** Character ':'. */
+#define CC_COLON      4
+/** SQL variable special characters: '@', '#', and '$'. */
 #define CC_VARALPHA   5
 #define CC_VARNUM     6		/* '?'.  Numeric SQL variables */
 #define CC_SPACE      7		/* Space characters */
@@ -85,17 +87,21 @@
 #define CC_LINEFEED  28		/* '\n' */
 #define CC_LB        29		/* '[' */
 #define CC_RB        30		/* ']' */
+/** Character '{'. */
+#define CC_LCB       31
+/** Character '}'. */
+#define CC_RCB       32
 
 static const char sql_ascii_class[] = {
 /*       x0  x1  x2  x3  x4  x5  x6  x7  x8 x9  xa xb  xc xd xe  xf */
 /* 0x */ 27, 27, 27, 27, 27, 27, 27, 27, 27, 7, 28, 7, 7, 7, 27, 27,
 /* 1x */ 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27,
 /* 2x */ 7, 15, 9, 5, 5, 22, 24, 8, 17, 18, 21, 20, 23, 11, 26, 16,
-/* 3x */ 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 5, 19, 12, 14, 13, 6,
+/* 3x */ 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 19, 12, 14, 13, 6,
 /* 4x */ 5, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
 /* 5x */ 1, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 29, 27, 30, 27, 1,
 /* 6x */ 27, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
-/* 7x */ 1, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 27, 10, 27, 25, 27,
+/* 7x */ 1, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 31, 10, 32, 25, 27,
 /* 8x */ 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
 /* 9x */ 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
 /* Ax */ 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
@@ -228,6 +234,12 @@ sql_token(const char *z, int *type, bool *is_reserved)
 	case CC_RB:
 		*type = TK_RB;
 		return 1;
+	case CC_LCB:
+		*type = TK_LCB;
+		return 1;
+	case CC_RCB:
+		*type = TK_RCB;
+		return 1;
 	case CC_SEMI:
 		*type = TK_SEMI;
 		return 1;
@@ -371,6 +383,9 @@ sql_token(const char *z, int *type, bool *is_reserved)
 	case CC_VARNUM:
 		*type = TK_VARNUM;
 		return 1;
+	case CC_COLON:
+		*type = TK_COLON;
+		return 1;
 	case CC_VARALPHA:
 		*type = TK_VARIABLE;
 		return 1;
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 55e494332..86de3f98a 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1438,6 +1438,26 @@ case OP_Array: {
 	break;
 }
 
+/**
+ * Opcode: Map P1 P2 P3 * *
+ * Synopsis: r[P2] = map(P3 at P1)
+ *
+ * Construct an MAP value from P1 registers starting at reg(P3).
+ */
+case OP_Map: {
+	pOut = &aMem[pOp->p2];
+
+	uint32_t size;
+	struct region *region = &fiber()->gc;
+	size_t svp = region_used(region);
+	char *val = mem_encode_map(&aMem[pOp->p3], pOp->p1, &size, region);
+	if (val == NULL || mem_copy_map(pOut, val, size) != 0) {
+		region_truncate(region, svp);
+		goto abort_due_to_error;
+	}
+	break;
+}
+
 /* Opcode: Eq P1 P2 P3 P4 P5
  * Synopsis: IF r[P3]==r[P1]
  *
diff --git a/test/sql-tap/map.test.lua b/test/sql-tap/map.test.lua
index 1afbb2b1d..de1e495f3 100755
--- a/test/sql-tap/map.test.lua
+++ b/test/sql-tap/map.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(110)
+test:plan(126)
 
 box.schema.func.create('M1', {
     language = 'Lua',
@@ -982,6 +982,142 @@ test:do_catchsql_test(
         1, "Failed to execute SQL statement: wrong arguments for function ZEROBLOB()"
     })
 
+-- Make sure syntax for MAP values works as intended.
+test:do_execsql_test(
+    "map-13.1",
+    [[
+        SELECT {'a': a, 'g': g, 't': t, 'n': n, 'f': f, 'i': i, 'b': b, 'v': v,
+                's': s, 'd': d, 'u': u} FROM t1 WHERE id = 1;
+    ]], {
+        {t = "1", f = 1, n = 1, v = "1", g = 1, b = true, s = 1,
+         d = require('decimal').new(1), a = {a = 1}, i = 1,
+         u = require('uuid').fromstr('11111111-1111-1111-1111-111111111111')}
+    })
+
+test:do_execsql_test(
+    "map-13.2",
+    [[
+        SELECT {'q': 1, 'w': true, 'e': 1.5e0, 'r': ['asd', x'32'], 't': 123.0};
+    ]], {
+        {w = true, e = 1.5, r = {'asd', '2'}, t = require('decimal').new(123),
+         q = 1}
+    })
+
+test:do_execsql_test(
+    "map-13.3",
+    [[
+        SELECT typeof({1: 1});
+    ]], {
+        "map"
+    })
+
+-- Make sure MAP() accepts only INTEGER, STRING and UUID as keys.
+test:do_execsql_test(
+    "map-13.4",
+    [[
+        SELECT {1: 1};
+    ]], {
+        {[1] = 1}
+    })
+
+test:do_execsql_test(
+    "map-13.5",
+    [[
+        SELECT {-1: 1};
+    ]], {
+        {[-1] = 1}
+    })
+
+test:do_execsql_test(
+    "map-13.6",
+    [[
+        SELECT {'a': 1};
+    ]], {
+        {a = 1}
+    })
+
+test:do_execsql_test(
+    "map-13.6",
+    [[
+        SELECT typeof({UUID(): 1});
+    ]], {
+        "map"
+    })
+
+test:do_catchsql_test(
+    "map-13.7",
+    [[
+        SELECT {1.5e0: 1};
+    ]], {
+        1, "Only integer, string and uuid can be keys in map"
+    })
+
+test:do_catchsql_test(
+    "map-13.8",
+    [[
+        SELECT {1.5: 1};
+    ]], {
+        1, "Only integer, string and uuid can be keys in map"
+    })
+
+test:do_catchsql_test(
+    "map-13.9",
+    [[
+        SELECT {x'33': 1};
+    ]], {
+        1, "Only integer, string and uuid can be keys in map"
+    })
+
+test:do_catchsql_test(
+    "map-13.10",
+    [[
+        SELECT {[1, 2, 3]: 1};
+    ]], {
+        1, "Only integer, string and uuid can be keys in map"
+    })
+
+test:do_catchsql_test(
+    "map-13.11",
+    [[
+        SELECT {{'a': 1}: 1};
+    ]], {
+        1,
+        'Only integer, string and uuid can be keys in map'
+    })
+
+test:do_catchsql_test(
+    "map-13.12",
+    [[
+        SELECT {CAST(1 AS NUMBER): 1};
+    ]], {
+        1, 'Only integer, string and uuid can be keys in map'
+    })
+
+test:do_catchsql_test(
+    "map-13.13",
+    [[
+        SELECT {CAST(1 AS SCALAR): 1};
+    ]], {
+        1, 'Only integer, string and uuid can be keys in map'
+    })
+
+test:do_catchsql_test(
+    "map-13.14",
+    [[
+        SELECT {CAST(1 AS ANY): 1};
+    ]], {
+        1, 'Only integer, string and uuid can be keys in map'
+    })
+
+test:do_test(
+    "map-13.15",
+    function()
+        local res = {pcall(box.execute, [[SELECT {?: 1};]], {1.5})}
+        return {tostring(res[3])}
+    end, {
+        "Type mismatch: can not convert double(1.5) to integer, string or uuid"
+    })
+
 box.execute([[DROP TABLE t1;]])
 box.execute([[DROP TABLE t;]])
 
-- 
2.25.1



More information about the Tarantool-patches mailing list