[tarantool-patches] [PATCH 6/6] Evaluate an affinity for all producing expressions

[Georgy Kirichenko <georgy@tarantool.org>]

Evaluate an expression affinity during ast-processing and code
generation. For selects resulting columns data types are
exported to meta as columns names.
---
 src/box/execute.c             | 11 +++++---
 src/box/iproto_constants.h    |  1 +
 src/box/lua/net_box.c         |  8 ++++--
 src/box/sql/expr.c            | 44 +++++++++++++++++++++++++++++---
 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/suite.ini        |  1 +
 test/sql-tap/tkt3493.test.lua |  2 +-
 test/sql-tap/where2.test.lua  |  4 +--
 test/sql/errinj.result        |  1 +
 test/sql/iproto.result        | 47 +++++++++++++++++++++++++++++++++++
 15 files changed, 142 insertions(+), 17 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 90b10869a..f555d3840 100644
--- a/src/box/iproto_constants.h
+++ b/src/box/iproto_constants.h
@@ -122,6 +122,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 308c9c719..6423253b4 100644
--- a/src/box/lua/net_box.c
+++ b/src/box/lua/net_box.c
@@ -643,8 +643,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);
@@ -654,6 +653,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 6ae426496..61749000e 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -3696,6 +3696,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;
@@ -3721,22 +3722,26 @@ 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;
@@ -3754,6 +3759,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] == '\'');
@@ -3835,6 +3841,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 				testcase(regFree1 == 0);
 				testcase(regFree2 == 0);
 			}
+			pExpr->affinity = AFFINITY_INTEGER;
 			break;
 		}
 	case TK_AND:
@@ -3847,8 +3854,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 	case TK_BITOR:
 	case TK_SLASH:
 	case TK_LSHIFT:
-	case TK_RSHIFT:
-	case TK_CONCAT:{
+	case TK_RSHIFT:{
 			assert(TK_AND == OP_And);
 			testcase(op == TK_AND);
 			assert(TK_OR == OP_Or);
@@ -3878,10 +3884,25 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 			sqlite3VdbeAddOp3(v, op, r2, r1, target);
 			testcase(regFree1 == 0);
 			testcase(regFree2 == 0);
+			pExpr->affinity = AFFINITY_NUMERIC;
+			break;
+		}
+	case TK_CONCAT:{
+			assert(TK_CONCAT == OP_Concat);
+			testcase(op == TK_CONCAT);
+			r1 = sqlite3ExprCodeTemp(pParse, pExpr->pLeft,
+						 &regFree1);
+			r2 = sqlite3ExprCodeTemp(pParse, pExpr->pRight,
+						 &regFree2);
+			sqlite3VdbeAddOp3(v, op, r2, r1, target);
+			testcase(regFree1 == 0);
+			testcase(regFree2 == 0);
+			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);
@@ -3908,6 +3929,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);
@@ -3921,6 +3943,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);
@@ -3944,6 +3967,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 						"misuse of aggregate: %s()",
 						pExpr->u.zToken);
 			} else {
+				pExpr->affinity = pInfo->aFunc->pFunc->affinity;
 				return pInfo->aFunc[pExpr->iAgg].iMem;
 			}
 			break;
@@ -3980,6 +4004,13 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 				break;
 			}
 
+			if (pDef->affinity)
+				pExpr->affinity = pDef->affinity;
+			else {
+				// 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.
@@ -4123,6 +4154,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);
@@ -4146,12 +4178,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 6738ba54f..dbf928650 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 45a8525e9..04a23910c 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -730,6 +730,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 242a6448e..206136e93 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/suite.ini b/test/sql-tap/suite.ini
index e3a8a2031..67e6d78be 100644
--- a/test/sql-tap/suite.ini
+++ b/test/sql-tap/suite.ini
@@ -16,6 +16,7 @@ disabled =
 	triggerE.test.lua
 	where3.test.lua
 	where7.test.lua
+	whereB.test.lua
 	e_expr.test.lua
 lua_libs = lua/sqltester.lua ../sql/lua/sql_tokenizer.lua ../box/lua/identifier.lua
 is_parallel = True
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/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.18.0