[tarantool-patches] [PATCH 6/6] sql: allow to specify UNSIGNED column type

[Nikita Pettik]

Since all preparations concerning internal handling of unsigned values
have been done, now nothing prevents us from using UNSIGNED type in SQL.
This patch allows to specify UNSIGNED as a column type and adds CAST
rules, which are the same as for casual INTEGER, but with additional
check - result must be positive. Otherwise, error is raised.

Closes #4015
---
 extra/mkkeywordhash.c   |  1 +
 src/box/sql/parse.y     |  1 +
 src/box/sql/vdbemem.c   | 10 ++++++-
 test/sql/types.result   | 72 +++++++++++++++++++++++++++++++++++++++++++++++++
 test/sql/types.test.lua | 17 ++++++++++++
 5 files changed, 100 insertions(+), 1 deletion(-)

diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 2ad74eddc..ff59d60cf 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -209,6 +209,7 @@ static Keyword aKeywordTable[] = {
   { "UNION",                  "TK_UNION",       COMPOUND,         true  },
   { "UNIQUE",                 "TK_UNIQUE",      ALWAYS,           true  },
   { "UNKNOWN",                "TK_NULL",        ALWAYS,           true  },
+  { "UNSIGNED",               "TK_UNSIGNED",    ALWAYS,           true  },
   { "UPDATE",                 "TK_UPDATE",      ALWAYS,           true  },
   { "USING",                  "TK_USING",       ALWAYS,           true  },
   { "VALUES",                 "TK_VALUES",      ALWAYS,           true  },
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index f241b8d52..ee0d6ce79 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1766,6 +1766,7 @@ typedef(A) ::= VARCHAR char_len(B) . {
 typedef(A) ::= number_typedef(A) .
 number_typedef(A) ::= FLOAT_KW|REAL|DOUBLE . { A.type = FIELD_TYPE_NUMBER; }
 number_typedef(A) ::= INT|INTEGER_KW . { A.type = FIELD_TYPE_INTEGER; }
+number_typedef(A) ::= UNSIGNED . { A.type = FIELD_TYPE_UNSIGNED; }
 
 /**
  * NUMERIC type is temporary disabled. To be enabled when
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index 25119ff16..cef5196dd 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -699,12 +699,15 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
 			return 0;
 		return -1;
 	case FIELD_TYPE_INTEGER:
+	case FIELD_TYPE_UNSIGNED:
 		if ((pMem->flags & MEM_Blob) != 0) {
 			bool is_neg;
 			int64_t val;
 			if (sql_atoi64(pMem->z, (int64_t *) &val, &is_neg,
 				       pMem->n) != 0)
 				return -1;
+			if (type == FIELD_TYPE_UNSIGNED && is_neg)
+				return -1;
 			mem_set_int(pMem, val, is_neg);
 			return 0;
 		}
@@ -713,7 +716,12 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
 			MemSetTypeFlag(pMem, MEM_UInt);
 			return 0;
 		}
-		return sqlVdbeMemIntegerify(pMem, true);
+		if (sqlVdbeMemIntegerify(pMem, true) != 0)
+			return -1;
+		if (type == FIELD_TYPE_UNSIGNED &&
+		    (pMem->flags & MEM_UInt) == 0)
+			return -1;
+		return 0;
 	case FIELD_TYPE_NUMBER:
 		return sqlVdbeMemRealify(pMem);
 	default:
diff --git a/test/sql/types.result b/test/sql/types.result
index e12515345..6a09bac8a 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -1466,3 +1466,75 @@ box.execute("SELECT CAST('18446744073709551615' AS INTEGER);")
   rows:
   - [18446744073709551615]
 ...
+-- gh-4015: introduce unsigned type in SQL.
+--
+box.execute("CREATE TABLE t1 (id UNSIGNED PRIMARY KEY);")
+---
+- row_count: 1
+...
+box.execute("INSERT INTO t1 VALUES (0), (1), (2);")
+---
+- row_count: 3
+...
+box.execute("INSERT INTO t1 VALUES (-3);")
+---
+- error: 'Failed to execute SQL statement: Tuple field 1 type does not match one required
+    by operation: expected unsigned'
+...
+box.execute("SELECT id FROM t1;")
+---
+- metadata:
+  - name: ID
+    type: unsigned
+  rows:
+  - [0]
+  - [1]
+  - [2]
+...
+box.execute("SELECT CAST(123 AS UNSIGNED);")
+---
+- metadata:
+  - name: CAST(123 AS UNSIGNED)
+    type: unsigned
+  rows:
+  - [123]
+...
+box.execute("SELECT CAST(-123 AS UNSIGNED);")
+---
+- error: 'Type mismatch: can not convert -123 to unsigned'
+...
+box.execute("SELECT CAST(1.5 AS UNSIGNED);")
+---
+- metadata:
+  - name: CAST(1.5 AS UNSIGNED)
+    type: unsigned
+  rows:
+  - [1]
+...
+box.execute("SELECT CAST(-1.5 AS UNSIGNED);")
+---
+- error: 'Type mismatch: can not convert -1 to unsigned'
+...
+box.execute("SELECT CAST(true AS UNSIGNED);")
+---
+- metadata:
+  - name: CAST(true AS UNSIGNED)
+    type: unsigned
+  rows:
+  - [1]
+...
+box.execute("SELECT CAST('123' AS UNSIGNED);")
+---
+- metadata:
+  - name: CAST('123' AS UNSIGNED)
+    type: unsigned
+  rows:
+  - [123]
+...
+box.execute("SELECT CAST('-123' AS UNSIGNED);")
+---
+- error: 'Type mismatch: can not convert -123 to unsigned'
+...
+box.space.T1:drop()
+---
+...
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index ccdd1f3d4..ddd19cde2 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -332,3 +332,20 @@ box.execute("SELECT CAST(18446744073709551615 AS TEXT);")
 box.execute("SELECT CAST(18446744073709551615 AS SCALAR);")
 box.execute("SELECT CAST(18446744073709551615 AS BOOLEAN);")
 box.execute("SELECT CAST('18446744073709551615' AS INTEGER);")
+
+-- gh-4015: introduce unsigned type in SQL.
+--
+box.execute("CREATE TABLE t1 (id UNSIGNED PRIMARY KEY);")
+box.execute("INSERT INTO t1 VALUES (0), (1), (2);")
+box.execute("INSERT INTO t1 VALUES (-3);")
+box.execute("SELECT id FROM t1;")
+
+box.execute("SELECT CAST(123 AS UNSIGNED);")
+box.execute("SELECT CAST(-123 AS UNSIGNED);")
+box.execute("SELECT CAST(1.5 AS UNSIGNED);")
+box.execute("SELECT CAST(-1.5 AS UNSIGNED);")
+box.execute("SELECT CAST(true AS UNSIGNED);")
+box.execute("SELECT CAST('123' AS UNSIGNED);")
+box.execute("SELECT CAST('-123' AS UNSIGNED);")
+
+box.space.T1:drop()
-- 
2.15.1