[Tarantool-patches] [PATCH v1 3/3] sql: introduce syntax for ARRAY values

imeevma at tarantool.org imeevma at tarantool.org
Tue Nov 30 11:43:27 MSK 2021


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

Part of #4762

@TarantoolBot document
Title: Syntax for ARRAY in SQL

The syntax for creating ARRAY values is available in SQL. You can use
`[` and `]` to create an ARRAY value - all values in those brackets will
be part of ARRAY. The position of the values will be translated to the
same positions in ARRAY.

Examples:
```
tarantool> box.execute("SELECT [1, 'a', 1.5];")
---
- metadata:
  - name: COLUMN_1
    type: array
  rows:
  - [[1, 'a', 1.5]]
...
```

```
tarantool> box.execute("SELECT [1, 'a', ['abc', 321], 1.5];")
---
- metadata:
  - name: COLUMN_1
    type: array
  rows:
  - [[1, 'a', ['abc', 321], 1.5]]
...
```
---
 .../gh-4762-introduce-array-to-sql.md         |  3 +-
 src/box/sql/expr.c                            | 20 ++++++++
 src/box/sql/parse.y                           | 14 ++++++
 src/box/sql/tokenize.c                        | 10 +++-
 src/box/sql/vdbe.c                            | 20 ++++++++
 test/sql-tap/array.test.lua                   | 47 ++++++++++++++++++-
 test/sql-tap/colname.test.lua                 |  4 +-
 7 files changed, 112 insertions(+), 6 deletions(-)

diff --git a/changelogs/unreleased/gh-4762-introduce-array-to-sql.md b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md
index 1446ab1cb..fcfa53c73 100644
--- a/changelogs/unreleased/gh-4762-introduce-array-to-sql.md
+++ b/changelogs/unreleased/gh-4762-introduce-array-to-sql.md
@@ -1,3 +1,4 @@
 ## feature/core
 
- * Field type ARRAY is now available in SQL (gh-4762).
+ * Field type ARRAY is now available in SQL. The syntax has also been
+   implemented to allow the creation of ARRAY values (gh-4762).
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 2c8021060..eb169aeb8 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -3370,6 +3370,22 @@ int_overflow:
 			  is_neg ? P4_INT64 : P4_UINT64);
 }
 
+static void
+expr_code_array(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_Array, 0, reg, 0);
+		return;
+	}
+	int count = list->nExpr;
+	int values_reg = parser->nMem + 1;
+	parser->nMem += count;
+	sqlExprCodeExprList(parser, list, values_reg, 0, SQL_ECEL_FACTOR);
+	sqlVdbeAddOp3(vdbe, OP_Array, count, reg, values_reg);
+}
+
 /*
  * Erase column-cache entry number i
  */
@@ -3821,6 +3837,10 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 			return inReg;
 		}
 
+	case TK_ARRAY:
+		expr_code_array(pParse, pExpr, target);
+		break;
+
 	case TK_LT:
 	case TK_LE:
 	case TK_GT:
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 548004252..ee319d5ad 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1128,6 +1128,20 @@ expr(A) ::= CAST(X) LP expr(E) AS typedef(T) RP(Y). {
   sqlExprAttachSubtrees(pParse->db, A.pExpr, E.pExpr, 0);
 }
 
+expr(A) ::= LB(X) exprlist(Y) RB(E). {
+  struct Expr *expr = sql_expr_new_anon(pParse->db, TK_ARRAY);
+  if (expr == NULL) {
+    sql_expr_list_delete(pParse->db, Y);
+    pParse->is_aborted = true;
+    return;
+  }
+  expr->x.pList = Y;
+  expr->type = FIELD_TYPE_ARRAY;
+  sqlExprSetHeightAndFlags(pParse, expr);
+  A.pExpr = expr;
+  spanSet(&A, &X, &E);
+}
+
 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 b3cf8f6e6..f2d5a2df5 100644
--- a/src/box/sql/tokenize.c
+++ b/src/box/sql/tokenize.c
@@ -83,6 +83,8 @@
 #define CC_DOT       26		/* '.' */
 #define CC_ILLEGAL   27		/* Illegal character */
 #define CC_LINEFEED  28		/* '\n' */
+#define CC_LB        29		/* '[' */
+#define CC_RB        30		/* ']' */
 
 static const char sql_ascii_class[] = {
 /*       x0  x1  x2  x3  x4  x5  x6  x7  x8 x9  xa xb  xc xd xe  xf */
@@ -91,7 +93,7 @@ static const char sql_ascii_class[] = {
 /* 2x */ 7, 15, 9, 5, 4, 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,
 /* 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, 27, 27, 27, 27, 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,
 /* 8x */ 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
@@ -220,6 +222,12 @@ sql_token(const char *z, int *type, bool *is_reserved)
 	case CC_RP:
 		*type = TK_RP;
 		return 1;
+	case CC_LB:
+		*type = TK_LB;
+		return 1;
+	case CC_RB:
+		*type = TK_RB;
+		return 1;
 	case CC_SEMI:
 		*type = TK_SEMI;
 		return 1;
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 2e6893f1a..55e494332 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -1418,6 +1418,26 @@ case OP_Cast: {                  /* in1 */
 	goto abort_due_to_error;
 }
 
+/* Opcode: Array P1 P2 P3 * *
+ * Synopsis: r[P2]=array(P3 at P1)
+ *
+ * Construct an ARRAY value from P1 registers starting at reg(P3).
+ */
+case OP_Array: {
+	pOut = &aMem[pOp->p2];
+
+	uint32_t size;
+	struct region *region = &fiber()->gc;
+	size_t svp = region_used(region);
+	char *val = mem_encode_array(&aMem[pOp->p3], pOp->p1, &size, region);
+	if (val == NULL || mem_copy_array(pOut, val, size) != 0) {
+		region_truncate(region, svp);
+		goto abort_due_to_error;
+	}
+	region_truncate(region, svp);
+	break;
+}
+
 /* Opcode: Eq P1 P2 P3 P4 P5
  * Synopsis: IF r[P3]==r[P1]
  *
diff --git a/test/sql-tap/array.test.lua b/test/sql-tap/array.test.lua
index 752cb24f2..79a1c831d 100755
--- a/test/sql-tap/array.test.lua
+++ b/test/sql-tap/array.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(110)
+test:plan(115)
 
 box.schema.func.create('A1', {
     language = 'Lua',
@@ -979,6 +979,51 @@ test:do_catchsql_test(
         1, "Failed to execute SQL statement: wrong arguments for function ZEROBLOB()"
     })
 
+-- Make sure syntax for ARRAY values works as intended.
+test:do_execsql_test(
+    "array-13.1",
+    [[
+        SELECT [a, g, t, n, f, i, b, v, s, d, u] FROM t1 WHERE id = 1;
+    ]], {
+        {{1}, 1, '1', 1, 1, 1, true, '1', 1, require('decimal').new(1),
+         require('uuid').fromstr('11111111-1111-1111-1111-111111111111')}
+    })
+
+test:do_execsql_test(
+    "array-13.2",
+    [[
+        SELECT [1, true, 1.5e0, ['asd', x'32'], 1234.0];
+    ]], {
+        {1, true, 1.5, {'asd', '2'}, require('decimal').new(1234)}
+    })
+
+test:do_execsql_test(
+    "array-13.3",
+    [[
+        SELECT [];
+    ]], {
+        {}
+    })
+
+local arr = {0}
+local arr_str = '0'
+for i = 1, 1000 do table.insert(arr, i) arr_str = arr_str .. ', ' .. i end
+test:do_execsql_test(
+    "array-13.4",
+    [[
+        SELECT []] .. arr_str .. [[];
+    ]], {
+        arr
+    })
+
+test:do_execsql_test(
+    "array-13.5",
+    [[
+        SELECT typeof([1]);
+    ]], {
+        "array"
+    })
+
 box.execute([[DROP TABLE t1;]])
 box.execute([[DROP TABLE t;]])
 
diff --git a/test/sql-tap/colname.test.lua b/test/sql-tap/colname.test.lua
index ff7585c7a..698a446e1 100755
--- a/test/sql-tap/colname.test.lua
+++ b/test/sql-tap/colname.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 local test = require("sqltester")
-test:plan(79)
+test:plan(76)
 
 --!./tcltestrunner.lua
 -- 2008 July 15
@@ -546,7 +546,6 @@ test:do_test(
 
 local data = {
     [[`a`]],
-    "[a]",
 }
 for i, val in ipairs(data) do
     test:do_catchsql_test(
@@ -559,7 +558,6 @@ end
 local data2 = {
     {[['a']],{1, "/Syntax error/"}}, -- because ' is delimiter for strings
     {[[`a`]],{1, "/unrecognized token/"}}, -- because ` is undefined symbol
-    {"[a]",{1, "/unrecognized token/"}} -- because [ is undefined symbol
 }
 for i, val in ipairs(data2) do
     test:do_catchsql_test(
-- 
2.25.1



More information about the Tarantool-patches mailing list