[tarantool-patches] [PATCH 5/6] sql: return result-set type via IProto

[Nikita Pettik <korablev@tarantool.org>]

From: Georgy Kirichenko <georgy@tarantool.org>

Lets evaluate an expression type during processing of expression's AST
and code generation. It allows to calculate resulting columns data types
and export them as IProto meta alongside with columns' names.

Part of #2620
---
 src/box/execute.c             | 11 +++++++---
 src/box/iproto_constants.h    |  1 +
 src/box/lua/net_box.c         |  8 ++++++--
 src/box/sql/expr.c            | 37 +++++++++++++++++++++++++++++++++-
 src/box/sql/select.c          | 22 ++++++++++++++++++++
 src/box/sql/sqliteInt.h       |  3 +++
 src/box/sql/vdbe.h            |  4 ----
 src/box/sql/vdbeapi.c         |  7 +++++++
 src/box/sql/vdbeaux.c         |  2 +-
 test/sql-tap/in4.test.lua     |  2 +-
 test/sql-tap/tkt3493.test.lua |  2 +-
 test/sql-tap/where2.test.lua  |  4 ++--
 test/sql-tap/whereB.test.lua  |  3 ++-
 test/sql/errinj.result        |  1 +
 test/sql/iproto.result        | 47 +++++++++++++++++++++++++++++++++++++++++++
 15 files changed, 138 insertions(+), 16 deletions(-)

diff --git a/src/box/execute.c b/src/box/execute.c
index 24459b4b9..a5326fc53 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -528,9 +528,11 @@ sql_get_description(struct sqlite3_stmt *stmt, struct obuf *out,
 		return -1;
 
 	for (int i = 0; i < column_count; ++i) {
-		size_t size = mp_sizeof_map(1) +
-			      mp_sizeof_uint(IPROTO_FIELD_NAME);
+		size_t size = mp_sizeof_map(2) +
+			      mp_sizeof_uint(IPROTO_FIELD_NAME) +
+			      mp_sizeof_uint(IPROTO_FIELD_TYPE);
 		const char *name = sqlite3_column_name(stmt, i);
+		const char *type = sqlite3_column_datatype(stmt, i);
 		/*
 		 * Can not fail, since all column names are
 		 * preallocated during prepare phase and the
@@ -538,14 +540,17 @@ sql_get_description(struct sqlite3_stmt *stmt, struct obuf *out,
 		 */
 		assert(name != NULL);
 		size += mp_sizeof_str(strlen(name));
+		size += mp_sizeof_str(strlen(type));
 		char *pos = (char *) obuf_alloc(out, size);
 		if (pos == NULL) {
 			diag_set(OutOfMemory, size, "obuf_alloc", "pos");
 			return -1;
 		}
-		pos = mp_encode_map(pos, 1);
+		pos = mp_encode_map(pos, 2);
 		pos = mp_encode_uint(pos, IPROTO_FIELD_NAME);
 		pos = mp_encode_str(pos, name, strlen(name));
+		pos = mp_encode_uint(pos, IPROTO_FIELD_TYPE);
+		pos = mp_encode_str(pos, type, strlen(type));
 	}
 	return 0;
 }
diff --git a/src/box/iproto_constants.h b/src/box/iproto_constants.h
index f571375ee..770784004 100644
--- a/src/box/iproto_constants.h
+++ b/src/box/iproto_constants.h
@@ -123,6 +123,7 @@ enum iproto_key {
  */
 enum iproto_metadata_key {
 	IPROTO_FIELD_NAME = 0,
+	IPROTO_FIELD_TYPE = 1,
 };
 
 enum iproto_ballot_key {
diff --git a/src/box/lua/net_box.c b/src/box/lua/net_box.c
index a928a4cf2..4d7d8c6b2 100644
--- a/src/box/lua/net_box.c
+++ b/src/box/lua/net_box.c
@@ -644,8 +644,7 @@ netbox_decode_metadata(struct lua_State *L, const char **data)
 	lua_createtable(L, count, 0);
 	for (uint32_t i = 0; i < count; ++i) {
 		uint32_t map_size = mp_decode_map(data);
-		/* Only IPROTO_FIELD_NAME is available. */
-		assert(map_size == 1);
+		assert(map_size == 2);
 		(void) map_size;
 		uint32_t key = mp_decode_uint(data);
 		assert(key == IPROTO_FIELD_NAME);
@@ -655,6 +654,11 @@ netbox_decode_metadata(struct lua_State *L, const char **data)
 		const char *str = mp_decode_str(data, &len);
 		lua_pushlstring(L, str, len);
 		lua_setfield(L, -2, "name");
+		key = mp_decode_uint(data);
+		assert(key == IPROTO_FIELD_TYPE);
+		const char *type = mp_decode_str(data, &len);
+		lua_pushlstring(L, type, len);
+		lua_setfield(L, -2, "type");
 		lua_rawseti(L, -2, i + 1);
 	}
 }
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 86029b74e..534d856eb 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -3708,6 +3708,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 	case TK_AGG_COLUMN:{
 			AggInfo *pAggInfo = pExpr->pAggInfo;
 			struct AggInfo_col *pCol = &pAggInfo->aCol[pExpr->iAgg];
+			pExpr->affinity = pCol->pExpr->affinity;
 			if (!pAggInfo->directMode) {
 				assert(pCol->iMem > 0);
 				return pCol->iMem;
@@ -3733,22 +3734,27 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 					iTab = pParse->iSelfTab;
 				}
 			}
+			pExpr->affinity =
+				pExpr->space_def->fields[pExpr->iColumn].affinity;
 			return sqlite3ExprCodeGetColumn(pParse, pExpr->space_def,
 							pExpr->iColumn, iTab,
 							target, pExpr->op2);
 		}
 	case TK_INTEGER:{
+			pExpr->affinity = AFFINITY_INTEGER;
 			expr_code_int(pParse, pExpr, false, target);
 			return target;
 		}
 #ifndef SQLITE_OMIT_FLOATING_POINT
 	case TK_FLOAT:{
+			pExpr->affinity = AFFINITY_REAL;
 			assert(!ExprHasProperty(pExpr, EP_IntValue));
 			codeReal(v, pExpr->u.zToken, 0, target);
 			return target;
 		}
 #endif
 	case TK_STRING:{
+			pExpr->affinity = AFFINITY_TEXT;
 			assert(!ExprHasProperty(pExpr, EP_IntValue));
 			sqlite3VdbeLoadString(v, target, pExpr->u.zToken);
 			return target;
@@ -3766,6 +3772,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 			assert(pExpr->u.zToken[0] == 'x'
 			       || pExpr->u.zToken[0] == 'X');
 			assert(pExpr->u.zToken[1] == '\'');
+			pExpr->affinity = AFFINITY_BLOB;
 			z = &pExpr->u.zToken[2];
 			n = sqlite3Strlen30(z) - 1;
 			assert(z[n] == '\'');
@@ -3847,6 +3854,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 				testcase(regFree1 == 0);
 				testcase(regFree2 == 0);
 			}
+			pExpr->affinity = AFFINITY_INTEGER;
 			break;
 		}
 	case TK_AND:
@@ -3890,10 +3898,15 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 			sqlite3VdbeAddOp3(v, op, r2, r1, target);
 			testcase(regFree1 == 0);
 			testcase(regFree2 == 0);
+			if (op != TK_CONCAT)
+				pExpr->affinity = AFFINITY_NUMERIC;
+			else
+				pExpr->affinity = AFFINITY_TEXT;
 			break;
 		}
 	case TK_UMINUS:{
 			Expr *pLeft = pExpr->pLeft;
+			pExpr->affinity = AFFINITY_NUMERIC;
 			assert(pLeft);
 			if (pLeft->op == TK_INTEGER) {
 				expr_code_int(pParse, pLeft, true, target);
@@ -3920,6 +3933,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 		}
 	case TK_BITNOT:
 	case TK_NOT:{
+			pExpr->affinity = AFFINITY_INTEGER;
 			assert(TK_BITNOT == OP_BitNot);
 			testcase(op == TK_BITNOT);
 			assert(TK_NOT == OP_Not);
@@ -3933,6 +3947,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 	case TK_ISNULL:
 	case TK_NOTNULL:{
 			int addr;
+			pExpr->affinity = AFFINITY_INTEGER;
 			assert(TK_ISNULL == OP_IsNull);
 			testcase(op == TK_ISNULL);
 			assert(TK_NOTNULL == OP_NotNull);
@@ -3956,6 +3971,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 						"misuse of aggregate: %s()",
 						pExpr->u.zToken);
 			} else {
+				pExpr->affinity = pInfo->aFunc->pFunc->ret_type;
 				return pInfo->aFunc[pExpr->iAgg].iMem;
 			}
 			break;
@@ -3992,6 +4008,18 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 				break;
 			}
 
+			if (pDef->ret_type != AFFINITY_UNDEFINED) {
+				pExpr->affinity = pDef->ret_type;
+			} else {
+				/*
+				 * Otherwise, use first arg as
+				 * expression affinity.
+				 */
+				if (pFarg && pFarg->nExpr > 0) {
+					pExpr->affinity =
+						pFarg->a[0].pExpr->affinity;
+				}
+			}
 			/* Attempt a direct implementation of the built-in COALESCE() and
 			 * IFNULL() functions.  This avoids unnecessary evaluation of
 			 * arguments past the first non-NULL argument.
@@ -4135,6 +4163,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 	case TK_IN:{
 			int destIfFalse = sqlite3VdbeMakeLabel(v);
 			int destIfNull = sqlite3VdbeMakeLabel(v);
+			pExpr->affinity = AFFINITY_INTEGER;
 			sqlite3VdbeAddOp2(v, OP_Null, 0, target);
 			sqlite3ExprCodeIN(pParse, pExpr, destIfFalse,
 					  destIfNull);
@@ -4158,12 +4187,18 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 		 * Z is stored in pExpr->pList->a[1].pExpr.
 		 */
 	case TK_BETWEEN:{
+			pExpr->affinity = AFFINITY_INTEGER;
 			exprCodeBetween(pParse, pExpr, target, 0, 0);
 			return target;
 		}
 	case TK_SPAN:
-	case TK_COLLATE:
+	case TK_COLLATE:{
+			pExpr->affinity = AFFINITY_TEXT;
+			return sqlite3ExprCodeTarget(pParse, pExpr->pLeft,
+						     target);
+		}
 	case TK_UPLUS:{
+			pExpr->affinity = AFFINITY_NUMERIC;
 			return sqlite3ExprCodeTarget(pParse, pExpr->pLeft,
 						     target);
 		}
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 849c0f871..d6e04525b 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1748,6 +1748,28 @@ generateColumnNames(Parse * pParse,	/* Parser context */
 		p = pEList->a[i].pExpr;
 		if (NEVER(p == 0))
 			continue;
+		switch (p->affinity) {
+		case AFFINITY_INTEGER:
+			sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, "INTEGER",
+					      SQLITE_TRANSIENT);
+			break;
+		case AFFINITY_REAL:
+		case AFFINITY_NUMERIC:
+			sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, "NUMERIC",
+					      SQLITE_TRANSIENT);
+			break;
+		case AFFINITY_TEXT:
+			sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, "TEXT",
+					      SQLITE_TRANSIENT);
+			break;
+		case AFFINITY_BLOB:
+			sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, "BLOB",
+					      SQLITE_TRANSIENT);
+			break;
+		default:
+			sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, "UNKNOWN",
+					      SQLITE_TRANSIENT);
+		}
 		if (pEList->a[i].zName) {
 			char *zName = pEList->a[i].zName;
 			sqlite3VdbeSetColName(v, i, COLNAME_NAME, zName,
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 7835be814..9e83028de 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -690,6 +690,9 @@ sqlite3_column_count(sqlite3_stmt * pStmt);
 const char *
 sqlite3_column_name(sqlite3_stmt *, int N);
 
+const char *
+sqlite3_column_datatype(sqlite3_stmt *, int N);
+
 const char *
 sqlite3_errmsg(sqlite3 *);
 
diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h
index 2987d7ab0..a7b150d64 100644
--- a/src/box/sql/vdbe.h
+++ b/src/box/sql/vdbe.h
@@ -152,12 +152,8 @@ struct SubProgram {
 #ifdef SQLITE_ENABLE_COLUMN_METADATA
 #define COLNAME_N        5	/* Number of COLNAME_xxx symbols */
 #else
-#ifdef SQLITE_OMIT_DECLTYPE
-#define COLNAME_N      1	/* Store only the name */
-#else
 #define COLNAME_N      2	/* Store the name and decltype */
 #endif
-#endif
 
 /*
  * The following macro converts a relative address in the p2 field
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index ead527c27..df1c19782 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -1111,6 +1111,13 @@ sqlite3_column_name(sqlite3_stmt * pStmt, int N)
 			  COLNAME_NAME);
 }
 
+const char *
+sqlite3_column_datatype(sqlite3_stmt *pStmt, int N)
+{
+	return columnName(pStmt, N, (const void *(*)(Mem *))sqlite3_value_text,
+			  COLNAME_DECLTYPE);
+}
+
 /*
  * Constraint:  If you have ENABLE_COLUMN_METADATA then you must
  * not define OMIT_DECLTYPE.
diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c
index 3b0c90ce3..8ac7c41ae 100644
--- a/src/box/sql/vdbeaux.c
+++ b/src/box/sql/vdbeaux.c
@@ -2174,7 +2174,7 @@ sqlite3VdbeSetColName(Vdbe * p,			/* Vdbe being configured */
 		return SQLITE_NOMEM_BKPT;
 	}
 	assert(p->aColName != 0);
-	assert(var == COLNAME_NAME);
+	assert(var == COLNAME_NAME || var == COLNAME_DECLTYPE);
 	pColName = &(p->aColName[idx + var * p->nResColumn]);
 	rc = sqlite3VdbeMemSetStr(pColName, zName, -1, 1, xDel);
 	assert(rc != 0 || !zName || (pColName->flags & MEM_Term) != 0);
diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua
index 70fb207fd..ef426b092 100755
--- a/test/sql-tap/in4.test.lua
+++ b/test/sql-tap/in4.test.lua
@@ -673,7 +673,7 @@ test:do_execsql_test(
         SELECT c FROM t4b WHERE +b IN (a);
     ]], {
         -- <in4-4.19>
-        
+        4
         -- </in4-4.19>
     })
 
diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
index 31d81d529..26ca2271b 100755
--- a/test/sql-tap/tkt3493.test.lua
+++ b/test/sql-tap/tkt3493.test.lua
@@ -169,7 +169,7 @@ test:do_execsql_test(
         SELECT count(*), +a=123 FROM t1 
     ]], {
         -- <tkt3493-2.2.5>
-        1, 0
+        1, 1
         -- </tkt3493-2.2.5>
     })
 
diff --git a/test/sql-tap/where2.test.lua b/test/sql-tap/where2.test.lua
index 8e30f11cb..a2b60e347 100755
--- a/test/sql-tap/where2.test.lua
+++ b/test/sql-tap/where2.test.lua
@@ -688,7 +688,7 @@ test:do_test(
   ]])
         end, {
             -- <where2-6.10>
-            "nosort", "T2249B", "*", "T2249A", "*"
+            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
             -- </where2-6.10>
         })
 
@@ -805,7 +805,7 @@ test:do_test(
     ]])
         end, {
             -- <where2-6.13>
-            "nosort", "T2249B", "*", "T2249A", "*"
+            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
             -- </where2-6.13>
         })
 
diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua
index c7eb10bf6..7e2aff078 100755
--- a/test/sql-tap/whereB.test.lua
+++ b/test/sql-tap/whereB.test.lua
@@ -74,6 +74,7 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-1.4>
+    1, 2, 1
     -- </whereB-1.4>
     })
 
@@ -107,7 +108,7 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-1.102>
-    
+    1, 2, 1
     -- </whereB-1.102>
     })
 
diff --git a/test/sql/errinj.result b/test/sql/errinj.result
index 2bcfdb7db..cb993f8ce 100644
--- a/test/sql/errinj.result
+++ b/test/sql/errinj.result
@@ -79,6 +79,7 @@ select_res
 ---
 - metadata:
   - name: '1'
+    type: INTEGER
   rows:
   - [1]
 ...
diff --git a/test/sql/iproto.result b/test/sql/iproto.result
index d46df2a26..16ffd0991 100644
--- a/test/sql/iproto.result
+++ b/test/sql/iproto.result
@@ -55,8 +55,11 @@ ret
 ---
 - metadata:
   - name: ID
+    type: INTEGER
   - name: A
+    type: NUMERIC
   - name: B
+    type: TEXT
   rows:
   - [1, 2, '3']
   - [4, 5, '6']
@@ -97,6 +100,7 @@ cn:execute('select id as identifier from test where a = 5;')
 ---
 - metadata:
   - name: IDENTIFIER
+    type: INTEGER
   rows: []
 ...
 -- netbox API errors.
@@ -124,8 +128,11 @@ cn:execute('select * from test where id = ?', {1})
 ---
 - metadata:
   - name: ID
+    type: INTEGER
   - name: A
+    type: NUMERIC
   - name: B
+    type: TEXT
   rows:
   - [1, 2, '3']
 ...
@@ -142,8 +149,11 @@ cn:execute('select * from test where id = :value', parameters)
 ---
 - metadata:
   - name: ID
+    type: INTEGER
   - name: A
+    type: NUMERIC
   - name: B
+    type: TEXT
   rows:
   - [1, 2, '3']
 ...
@@ -151,8 +161,11 @@ cn:execute('select ?, ?, ?', {1, 2, 3})
 ---
 - metadata:
   - name: '?'
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   rows:
   - [1, 2, 3]
 ...
@@ -178,8 +191,11 @@ cn:execute('select ?, :value1, @value2', parameters)
 ---
 - metadata:
   - name: '?'
+    type: UNKNOWN
   - name: :value1
+    type: UNKNOWN
   - name: '@value2'
+    type: UNKNOWN
   rows:
   - [10, 11, 12]
 ...
@@ -217,13 +233,21 @@ cn:execute('select :value3, ?, :value1, ?, ?, @value2, ?, :value3', parameters)
 ---
 - metadata:
   - name: :value3
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   - name: :value1
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   - name: '@value2'
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   - name: :value3
+    type: UNKNOWN
   rows:
   - [1, 2, 3, 4, 5, 6, null, 1]
 ...
@@ -235,10 +259,15 @@ cn:execute('select ?, ?, ?, ?, ?', {'abc', -123.456, msgpack.NULL, true, false})
 ---
 - metadata:
   - name: '?'
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   - name: '?'
+    type: UNKNOWN
   rows:
   - ['abc', -123.456, null, 1, 0]
 ...
@@ -247,7 +276,9 @@ cn:execute('select ? as kek, ? as kek2', {1, 2})
 ---
 - metadata:
   - name: KEK
+    type: UNKNOWN
   - name: KEK2
+    type: UNKNOWN
   rows:
   - [1, 2]
 ...
@@ -344,9 +375,13 @@ cn:execute('select * from test2')
 ---
 - metadata:
   - name: ID
+    type: INTEGER
   - name: A
+    type: INTEGER
   - name: B
+    type: INTEGER
   - name: C
+    type: INTEGER
   rows:
   - [1, 1, 1, 1]
 ...
@@ -494,8 +529,11 @@ cn:execute('select $2, $1, $3', parameters)
 ---
 - metadata:
   - name: $2
+    type: UNKNOWN
   - name: $1
+    type: UNKNOWN
   - name: $3
+    type: UNKNOWN
   rows:
   - [22, 11, 33]
 ...
@@ -503,8 +541,11 @@ cn:execute('select * from test where id = :1', {1})
 ---
 - metadata:
   - name: ID
+    type: INTEGER
   - name: A
+    type: NUMERIC
   - name: B
+    type: TEXT
   rows:
   - [1, 2, '3']
 ...
@@ -518,8 +559,11 @@ res = cn:execute('select * from test')
 res.metadata
 ---
 - - name: ID
+    type: INTEGER
   - name: A
+    type: NUMERIC
   - name: B
+    type: TEXT
 ...
 box.sql.execute('drop table test')
 ---
@@ -567,8 +611,11 @@ future4:wait_result()
 ---
 - metadata:
   - name: ID
+    type: INTEGER
   - name: A
+    type: INTEGER
   - name: B
+    type: INTEGER
   rows:
   - [1, 1, 1]
   - [2, 2, 2]
-- 
2.15.1