[tarantool-patches] [PATCH 6/8] sql: replace affinity with field type in struct Expr

[Nikita Pettik <korablev@tarantool.org>]

Also this patch resolves issue connected with wrong query plans during
select on spaces created from Lua: instead of index search in most cases
table scan was used. It appeared due to the fact that index was checked
on affinity compatibility with space format. So, if space is created
without affinity in format, indexes won't be used.
However, now all checks are related to field types, and as a result
query optimizer is able to choose correct index.

Closes #3886
Part of #3698
---
 src/box/sql/build.c              |   6 +-
 src/box/sql/expr.c               | 270 +++++++++++++++++----------------------
 src/box/sql/fkey.c               |   5 +-
 src/box/sql/insert.c             |   2 +-
 src/box/sql/parse.y              |  12 +-
 src/box/sql/resolve.c            |   4 +-
 src/box/sql/select.c             |  23 ++--
 src/box/sql/sqliteInt.h          |  46 ++++---
 src/box/sql/vdbemem.c            |  18 +--
 src/box/sql/where.c              |  25 ++--
 src/box/sql/whereInt.h           |   3 +-
 src/box/sql/wherecode.c          |  38 +++---
 src/box/sql/whereexpr.c          |  31 ++---
 test/sql-tap/lua-tables.test.lua |  38 +++++-
 test/sql/iproto.result           |   2 +-
 15 files changed, 257 insertions(+), 266 deletions(-)

diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index b3f98c317..2885bb6d5 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -495,8 +495,12 @@ sql_affinity_to_field_type(enum affinity_type affinity)
 			return FIELD_TYPE_NUMBER;
 		case AFFINITY_TEXT:
 			return FIELD_TYPE_STRING;
-		default:
+		case AFFINITY_BLOB:
 			return FIELD_TYPE_SCALAR;
+		case AFFINITY_UNDEFINED:
+			return FIELD_TYPE_ANY;
+		default:
+			unreachable();
 	}
 }
 
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index c823c5a06..32606dac3 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -45,36 +45,12 @@ static void exprCodeBetween(Parse *, Expr *, int,
 			    void (*)(Parse *, Expr *, int, int), int);
 static int exprCodeVector(Parse * pParse, Expr * p, int *piToFree);
 
-char
-sqlite3TableColumnAffinity(struct space_def *def, int idx)
-{
-	assert(idx < (int)def->field_count);
-	return idx >= 0 ? def->fields[idx].affinity :
-	       AFFINITY_INTEGER;
-}
-
-/*
- * Return the 'affinity' of the expression pExpr if any.
- *
- * If pExpr is a column, a reference to a column via an 'AS' alias,
- * or a sub-select with a column as the return value, then the
- * affinity of that column is returned. Otherwise, 0x00 is returned,
- * indicating no affinity for the expression.
- *
- * i.e. the WHERE clause expressions in the following statements all
- * have an affinity:
- *
- * CREATE TABLE t1(a);
- * SELECT * FROM t1 WHERE a;
- * SELECT a AS b FROM t1 WHERE b;
- * SELECT * FROM t1 WHERE (select a from t1);
- */
-char
-sqlite3ExprAffinity(Expr * pExpr)
+enum field_type
+sql_expr_type(struct Expr *pExpr)
 {
 	pExpr = sqlite3ExprSkipCollate(pExpr);
 	if (pExpr->flags & EP_Generic)
-		return 0;
+		return FIELD_TYPE_ANY;
 	uint8_t op = pExpr->op;
 	struct ExprList *el;
 	if (op == TK_REGISTER)
@@ -83,27 +59,26 @@ sqlite3ExprAffinity(Expr * pExpr)
 	case TK_SELECT:
 		assert(pExpr->flags & EP_xIsSelect);
 		el = pExpr->x.pSelect->pEList;
-		return sqlite3ExprAffinity(el->a[0].pExpr);
+		return sql_expr_type(el->a[0].pExpr);
 	case TK_CAST:
 		assert(!ExprHasProperty(pExpr, EP_IntValue));
-		return pExpr->affinity;
+		return pExpr->type;
 	case TK_AGG_COLUMN:
 	case TK_COLUMN:
 		assert(pExpr->iColumn >= 0);
-		return sqlite3TableColumnAffinity(pExpr->space_def,
-						  pExpr->iColumn);
+		return pExpr->space_def->fields[pExpr->iColumn].type;
 	case TK_SELECT_COLUMN:
 		assert(pExpr->pLeft->flags & EP_xIsSelect);
 		el = pExpr->pLeft->x.pSelect->pEList;
-		return sqlite3ExprAffinity(el->a[pExpr->iColumn].pExpr);
+		return sql_expr_type(el->a[pExpr->iColumn].pExpr);
 	case TK_PLUS:
 	case TK_MINUS:
 	case TK_STAR:
 	case TK_SLASH:
 		assert(pExpr->pRight != NULL && pExpr->pLeft != NULL);
-		enum affinity_type lhs_aff = sqlite3ExprAffinity(pExpr->pLeft);
-		enum affinity_type rhs_aff = sqlite3ExprAffinity(pExpr->pRight);
-		return sql_affinity_result(rhs_aff, lhs_aff);
+		enum field_type lhs_type = sql_expr_type(pExpr->pLeft);
+		enum field_type rhs_type = sql_expr_type(pExpr->pRight);
+		return sql_type_result(rhs_type, lhs_type);
 	case TK_LT:
 	case TK_GT:
 	case TK_EQ:
@@ -113,22 +88,22 @@ sqlite3ExprAffinity(Expr * pExpr)
 	case TK_AND:
 	case TK_OR:
 		/*
-		 * FIXME: should be changed to BOOL type or
-		 * affinity when it is implemented. Now simply
+		 * FIXME: should be changed to BOOL type
+		 * when it is implemented. Now simply
 		 * return INTEGER.
 		 */
-		return AFFINITY_INTEGER;
+		return FIELD_TYPE_INTEGER;
 	}
 	/*
 	 * In case of unary plus we shouldn't discard
-	 * affinity of operand (since plus always features
-	 * NUMERIC affinity).
+	 * type of operand (since plus always features
+	 * NUMERIC type).
 	 */
 	if (op == TK_UPLUS) {
 		assert(pExpr->pRight == NULL);
-		return pExpr->pLeft->affinity;
+		return pExpr->pLeft->type;
 	}
-	return pExpr->affinity;
+	return pExpr->type;
 }
 
 /*
@@ -256,28 +231,29 @@ sql_expr_coll(Parse *parse, Expr *p, bool *is_explicit_coll, uint32_t *coll_id)
 	return coll;
 }
 
-enum affinity_type
-sql_affinity_result(enum affinity_type aff1, enum affinity_type aff2)
+enum field_type
+sql_type_result(enum field_type lhs, enum field_type rhs)
 {
-	if (aff1 && aff2) {
-		/* Both sides of the comparison are columns. If one has numeric
-		 * affinity, use that. Otherwise use no affinity.
+	if (lhs != FIELD_TYPE_ANY && rhs != FIELD_TYPE_ANY) {
+		/*
+		 * Both sides of the comparison are columns.
+		 * If one has numeric type, use that.
 		 */
-		if (sqlite3IsNumericAffinity(aff1)
-		    || sqlite3IsNumericAffinity(aff2)) {
-			return AFFINITY_REAL;
-		} else {
-			return AFFINITY_BLOB;
-		}
-	} else if (!aff1 && !aff2) {
-		/* Neither side of the comparison is a column.  Compare the
-		 * results directly.
+		if (sql_type_is_numeric(lhs) || sql_type_is_numeric(rhs))
+			return FIELD_TYPE_NUMBER;
+		else
+			return FIELD_TYPE_SCALAR;
+
+	} else if (lhs == FIELD_TYPE_ANY && rhs == FIELD_TYPE_ANY) {
+		/*
+		 * Neither side of the comparison is a column.
+		 * Compare the results directly.
 		 */
-		return AFFINITY_BLOB;
+		return FIELD_TYPE_SCALAR;
 	} else {
-		/* One side is a column, the other is not. Use the columns affinity. */
-		assert(aff1 == 0 || aff2 == 0);
-		return (aff1 + aff2);
+		if (lhs == FIELD_TYPE_ANY)
+			return rhs;
+		return lhs;
 	}
 }
 
@@ -285,45 +261,44 @@ sql_affinity_result(enum affinity_type aff1, enum affinity_type aff2)
  * pExpr is a comparison operator.  Return the type affinity that should
  * be applied to both operands prior to doing the comparison.
  */
-static char
+static enum field_type
 comparisonAffinity(Expr * pExpr)
 {
-	char aff;
 	assert(pExpr->op == TK_EQ || pExpr->op == TK_IN || pExpr->op == TK_LT ||
 	       pExpr->op == TK_GT || pExpr->op == TK_GE || pExpr->op == TK_LE ||
 	       pExpr->op == TK_NE);
 	assert(pExpr->pLeft);
-	aff = sqlite3ExprAffinity(pExpr->pLeft);
+	enum field_type type = sql_expr_type(pExpr->pLeft);
 	if (pExpr->pRight) {
-		enum affinity_type rhs_aff = sqlite3ExprAffinity(pExpr->pRight);
-		aff = sql_affinity_result(rhs_aff, aff);
+		enum field_type rhs_type = sql_expr_type(pExpr->pRight);
+		type = sql_type_result(rhs_type, type);
 	} else if (ExprHasProperty(pExpr, EP_xIsSelect)) {
-		enum affinity_type rhs_aff =
-			sqlite3ExprAffinity(pExpr->x.pSelect->pEList->a[0].pExpr);
-		aff = sql_affinity_result(rhs_aff, aff);
+		enum field_type rhs_type =
+			sql_expr_type(pExpr->x.pSelect->pEList->a[0].pExpr);
+		type = sql_type_result(rhs_type, type);
 	} else {
-		aff = AFFINITY_BLOB;
+		type = FIELD_TYPE_SCALAR;
 	}
-	return aff;
+	return type;
 }
 
-/*
- * pExpr is a comparison expression, eg. '=', '<', IN(...) etc.
- * idx_affinity is the affinity of an indexed column. Return true
- * if the index with affinity idx_affinity may be used to implement
- * the comparison in pExpr.
+/**
+ * @param expr is a comparison expression, eg. '=', '<', IN(...) etc.
+ * @param idx_affinity is the affinity of an indexed column.
+ * @retval Return true if the index with @idx_type may be used to
+ * implement the comparison in expr.
  */
-int
-sqlite3IndexAffinityOk(Expr * pExpr, char idx_affinity)
+enum field_type
+sql_index_type_is_ok(struct Expr *expr, enum field_type idx_type)
 {
-	char aff = comparisonAffinity(pExpr);
-	switch (aff) {
-	case AFFINITY_BLOB:
+	enum field_type type = comparisonAffinity(expr);
+	switch (type) {
+	case FIELD_TYPE_SCALAR:
 		return 1;
-	case AFFINITY_TEXT:
-		return idx_affinity == AFFINITY_TEXT;
+	case FIELD_TYPE_STRING:
+		return idx_type == FIELD_TYPE_STRING;
 	default:
-		return sqlite3IsNumericAffinity(idx_affinity);
+		return sql_type_is_numeric(idx_type);
 	}
 }
 
@@ -334,11 +309,11 @@ sqlite3IndexAffinityOk(Expr * pExpr, char idx_affinity)
 static u8
 binaryCompareP5(Expr * pExpr1, Expr * pExpr2, int jumpIfNull)
 {
-	enum affinity_type aff2 = sqlite3ExprAffinity(pExpr2);
-	enum affinity_type aff1 = sqlite3ExprAffinity(pExpr1);
-	enum affinity_type aff = sql_affinity_result(aff1, aff2) |
-				 (u8) jumpIfNull;
-	return aff;
+	enum field_type lhs = sql_expr_type(pExpr2);
+	enum field_type rhs = sql_expr_type(pExpr1);
+	u8 type_mask = sql_field_type_to_affinity(sql_type_result(rhs, lhs)) |
+		       (u8) jumpIfNull;
+	return type_mask;
 }
 
 int
@@ -2155,10 +2130,10 @@ sqlite3ExprCanBeNull(const Expr * p)
  * answer.
  */
 int
-sqlite3ExprNeedsNoAffinityChange(const Expr * p, char aff)
+sql_expr_needs_type_change(const Expr *p, enum field_type type)
 {
 	u8 op;
-	if (aff == AFFINITY_BLOB)
+	if (type == FIELD_TYPE_SCALAR)
 		return 1;
 	while (p->op == TK_UPLUS || p->op == TK_UMINUS) {
 		p = p->pLeft;
@@ -2167,27 +2142,21 @@ sqlite3ExprNeedsNoAffinityChange(const Expr * p, char aff)
 	if (op == TK_REGISTER)
 		op = p->op2;
 	switch (op) {
-	case TK_INTEGER:{
-			return aff == AFFINITY_INTEGER;
-		}
-	case TK_FLOAT:{
-			return aff == AFFINITY_REAL;
-		}
-	case TK_STRING:{
-			return aff == AFFINITY_TEXT;
-		}
-	case TK_BLOB:{
-			return 1;
-		}
+	case TK_INTEGER:
+		return type == FIELD_TYPE_INTEGER;
+	case TK_FLOAT:
+		return type == FIELD_TYPE_NUMBER;
+	case TK_STRING:
+		return type == FIELD_TYPE_STRING;
+	case TK_BLOB:
+		return 1;
 	case TK_COLUMN:{
-			assert(p->iTable >= 0);	/* p cannot be part of a CHECK constraint */
-			return p->iColumn < 0
-			    && (aff == AFFINITY_INTEGER
-				|| aff == AFFINITY_REAL);
-		}
-	default:{
-			return 0;
+			/* p cannot be part of a CHECK constraint. */
+			assert(p->iTable >= 0);
+			return p->iColumn < 0 && sql_type_is_numeric(type);
 		}
+	default:
+		return 0;
 	}
 }
 
@@ -2436,14 +2405,14 @@ sqlite3FindInIndex(Parse * pParse,	/* Parsing context */
 			Expr *pLhs = sqlite3VectorFieldSubexpr(pX->pLeft, i);
 			int iCol = pEList->a[i].pExpr->iColumn;
 			/* RHS table */
-			enum affinity_type idxaff =
-				sqlite3TableColumnAffinity(pTab->def, iCol);
-			enum affinity_type lhs_aff = sqlite3ExprAffinity(pLhs);
+			assert(iCol >= 0);
+			enum field_type idx_type = pTab->def->fields[iCol].type;
+			enum field_type lhs_type = sql_expr_type(pLhs);
 			/*
 			 * Index search is possible only if types
 			 * of columns match.
 			 */
-			if (lhs_aff != idxaff)
+			if (idx_type != lhs_type)
 				affinity_ok = 0;
 		}
 
@@ -2633,13 +2602,13 @@ exprINAffinity(Parse * pParse, Expr * pExpr)
 		int i;
 		for (i = 0; i < nVal; i++) {
 			Expr *pA = sqlite3VectorFieldSubexpr(pLeft, i);
-			char a = sqlite3ExprAffinity(pA);
+			enum field_type lhs = sql_expr_type(pA);
 			if (pSelect) {
 				struct Expr *e = pSelect->pEList->a[i].pExpr;
-				enum affinity_type aff = sqlite3ExprAffinity(e);
-				zRet[i] = sql_affinity_result(aff, a);
+				enum field_type rhs = sql_expr_type(e);
+				zRet[i] = sql_field_type_to_affinity(sql_type_result(rhs, lhs));
 			} else {
-				zRet[i] = a;
+				zRet[i] = sql_field_type_to_affinity(lhs);
 			}
 		}
 		zRet[nVal] = '\0';
@@ -2826,16 +2795,15 @@ sqlite3CodeSubselect(Parse * pParse,	/* Parsing context */
 				 * that columns affinity when building index keys. If <expr> is not
 				 * a column, use numeric affinity.
 				 */
-				char affinity;	/* Affinity of the LHS of the IN */
 				int i;
 				ExprList *pList = pExpr->x.pList;
 				struct ExprList_item *pItem;
 				int r1, r2, r3;
 
-				affinity = sqlite3ExprAffinity(pLeft);
-				if (!affinity) {
-					affinity = AFFINITY_BLOB;
-				}
+				enum field_type lhs_type =
+					sql_expr_type(pLeft);
+				if (lhs_type == FIELD_TYPE_ANY)
+					lhs_type = FIELD_TYPE_SCALAR;
 				bool unused;
 				sql_expr_coll(pParse, pExpr->pLeft,
 					      &unused, &key_info->parts[0].coll_id);
@@ -2858,10 +2826,9 @@ sqlite3CodeSubselect(Parse * pParse,	/* Parsing context */
 						jmpIfDynamic = -1;
 					}
 					r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
-					char type =
-						sql_affinity_to_field_type(affinity);
 	 				sqlite3VdbeAddOp4(v, OP_MakeRecord, r3,
-							  1, r2, &type,
+							  1, r2,
+							  (char *) &lhs_type,
 							  1);
 					sqlite3ExprCacheAffinityChange(pParse,
 								       r3, 1);
@@ -3694,7 +3661,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;
+			pExpr->type = pCol->pExpr->type;
 			if (!pAggInfo->directMode) {
 				assert(pCol->iMem > 0);
 				return pCol->iMem;
@@ -3725,28 +3692,27 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 					iTab = pParse->iSelfTab;
 				}
 			}
-			pExpr->affinity =
-				pExpr->space_def->fields[col].affinity;
+			pExpr->type = pExpr->space_def->fields[col].type;
 			return sqlite3ExprCodeGetColumn(pParse,
 							pExpr->space_def, col,
 							iTab, target,
 							pExpr->op2);
 		}
 	case TK_INTEGER:{
-			pExpr->affinity = AFFINITY_INTEGER;
+			pExpr->type = FIELD_TYPE_INTEGER;
 			expr_code_int(pParse, pExpr, false, target);
 			return target;
 		}
 #ifndef SQLITE_OMIT_FLOATING_POINT
 	case TK_FLOAT:{
-			pExpr->affinity = AFFINITY_REAL;
+			pExpr->type = FIELD_TYPE_INTEGER;
 			assert(!ExprHasProperty(pExpr, EP_IntValue));
 			codeReal(v, pExpr->u.zToken, 0, target);
 			return target;
 		}
 #endif
 	case TK_STRING:{
-			pExpr->affinity = AFFINITY_TEXT;
+			pExpr->type = FIELD_TYPE_STRING;
 			assert(!ExprHasProperty(pExpr, EP_IntValue));
 			sqlite3VdbeLoadString(v, target, pExpr->u.zToken);
 			return target;
@@ -3764,7 +3730,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;
+			pExpr->type = FIELD_TYPE_SCALAR;
 			z = &pExpr->u.zToken[2];
 			n = sqlite3Strlen30(z) - 1;
 			assert(z[n] == '\'');
@@ -3803,8 +3769,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 				sqlite3VdbeAddOp2(v, OP_SCopy, inReg, target);
 				inReg = target;
 			}
-			sqlite3VdbeAddOp2(v, OP_Cast, target,
-					  sql_affinity_to_field_type(pExpr->affinity));
+			sqlite3VdbeAddOp2(v, OP_Cast, target, pExpr->type);
 			testcase(usedAsColumnCache(pParse, inReg, inReg));
 			sqlite3ExprCacheAffinityChange(pParse, inReg, 1);
 			return inReg;
@@ -3847,7 +3812,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 				testcase(regFree1 == 0);
 				testcase(regFree2 == 0);
 			}
-			pExpr->affinity = AFFINITY_INTEGER;
+			pExpr->type = FIELD_TYPE_INTEGER;
 			break;
 		}
 	case TK_AND:
@@ -3892,14 +3857,14 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 			testcase(regFree1 == 0);
 			testcase(regFree2 == 0);
 			if (op != TK_CONCAT)
-				pExpr->affinity = AFFINITY_REAL;
+				pExpr->type = FIELD_TYPE_NUMBER;
 			else
-				pExpr->affinity = AFFINITY_TEXT;
+				pExpr->type = FIELD_TYPE_STRING;
 			break;
 		}
 	case TK_UMINUS:{
 			Expr *pLeft = pExpr->pLeft;
-			pExpr->affinity = AFFINITY_REAL;
+			pExpr->type = FIELD_TYPE_NUMBER;
 			assert(pLeft);
 			if (pLeft->op == TK_INTEGER) {
 				expr_code_int(pParse, pLeft, true, target);
@@ -3926,7 +3891,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 		}
 	case TK_BITNOT:
 	case TK_NOT:{
-			pExpr->affinity = AFFINITY_INTEGER;
+			pExpr->type = FIELD_TYPE_INTEGER;
 			assert(TK_BITNOT == OP_BitNot);
 			testcase(op == TK_BITNOT);
 			assert(TK_NOT == OP_Not);
@@ -3940,7 +3905,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 	case TK_ISNULL:
 	case TK_NOTNULL:{
 			int addr;
-			pExpr->affinity = AFFINITY_INTEGER;
+			pExpr->type = FIELD_TYPE_INTEGER;
 			assert(TK_ISNULL == OP_IsNull);
 			testcase(op == TK_ISNULL);
 			assert(TK_NOTNULL == OP_NotNull);
@@ -3964,9 +3929,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 						"misuse of aggregate: %s()",
 						pExpr->u.zToken);
 			} else {
-				enum field_type t =
-					pInfo->aFunc->pFunc->ret_type;
-				pExpr->affinity = sql_field_type_to_affinity(t);
+				pExpr->type = pInfo->aFunc->pFunc->ret_type;
 				return pInfo->aFunc[pExpr->iAgg].iMem;
 			}
 			break;
@@ -4003,18 +3966,15 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 				break;
 			}
 
-			if (pDef->ret_type != AFFINITY_UNDEFINED) {
-				pExpr->affinity =
-					sql_field_type_to_affinity(pDef->ret_type);
+			if (pDef->ret_type != FIELD_TYPE_ANY) {
+				pExpr->type = pDef->ret_type;
 			} else {
 				/*
 				 * Otherwise, use first arg as
 				 * expression affinity.
 				 */
-				if (pFarg && pFarg->nExpr > 0) {
-					pExpr->affinity =
-						pFarg->a[0].pExpr->affinity;
-				}
+				if (pFarg && pFarg->nExpr > 0)
+					pExpr->type = pFarg->a[0].pExpr->type;
 			}
 			/* Attempt a direct implementation of the built-in COALESCE() and
 			 * IFNULL() functions.  This avoids unnecessary evaluation of
@@ -4159,7 +4119,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 	case TK_IN:{
 			int destIfFalse = sqlite3VdbeMakeLabel(v);
 			int destIfNull = sqlite3VdbeMakeLabel(v);
-			pExpr->affinity = AFFINITY_INTEGER;
+			pExpr->type = FIELD_TYPE_INTEGER;
 			sqlite3VdbeAddOp2(v, OP_Null, 0, target);
 			sqlite3ExprCodeIN(pParse, pExpr, destIfFalse,
 					  destIfNull);
@@ -4183,18 +4143,18 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 		 * Z is stored in pExpr->pList->a[1].pExpr.
 		 */
 	case TK_BETWEEN:{
-			pExpr->affinity = AFFINITY_INTEGER;
+			pExpr->type = FIELD_TYPE_INTEGER;
 			exprCodeBetween(pParse, pExpr, target, 0, 0);
 			return target;
 		}
 	case TK_SPAN:
 	case TK_COLLATE:{
-			pExpr->affinity = AFFINITY_TEXT;
+			pExpr->type = FIELD_TYPE_STRING;
 			return sqlite3ExprCodeTarget(pParse, pExpr->pLeft,
 						     target);
 		}
 	case TK_UPLUS:{
-			pExpr->affinity = AFFINITY_REAL;
+			pExpr->type = FIELD_TYPE_NUMBER;
 			return sqlite3ExprCodeTarget(pParse, pExpr->pLeft,
 						     target);
 		}
@@ -4246,7 +4206,7 @@ sqlite3ExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
 			 * floating point when extracting it from the record.
 			 */
 			if (pExpr->iColumn >= 0 && def->fields[
-				pExpr->iColumn].affinity == AFFINITY_REAL) {
+				pExpr->iColumn].type == FIELD_TYPE_NUMBER) {
 				sqlite3VdbeAddOp1(v, OP_Realify, target);
 			}
 #endif
diff --git a/src/box/sql/fkey.c b/src/box/sql/fkey.c
index a6a8a24dd..a3bcca26a 100644
--- a/src/box/sql/fkey.c
+++ b/src/box/sql/fkey.c
@@ -310,11 +310,10 @@ exprTableRegister(Parse * pParse,	/* Parsing and code generating context */
 	if (pExpr) {
 		if (iCol >= 0) {
 			pExpr->iTable = regBase + iCol + 1;
-			char affinity = pTab->def->fields[iCol].affinity;
-			pExpr->affinity = affinity;
+			pExpr->type = pTab->def->fields[iCol].type;
 		} else {
 			pExpr->iTable = regBase;
-			pExpr->affinity = AFFINITY_INTEGER;
+			pExpr->type = FIELD_TYPE_INTEGER;
 		}
 	}
 	return pExpr;
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index fd36e2786..acc7712f6 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -989,7 +989,7 @@ vdbe_emit_constraint_checks(struct Parse *parse_context, struct Table *tab,
 	if (part_count == 1) {
 		uint32_t fieldno = pk->def->key_def->parts[0].fieldno;
 		int reg_pk = new_tuple_reg + fieldno;
-		if (def->fields[fieldno].affinity == AFFINITY_INTEGER) {
+		if (def->fields[fieldno].type == FIELD_TYPE_INTEGER) {
 			int skip_if_null = sqlite3VdbeMakeLabel(v);
 			if (autoinc_fieldno != UINT32_MAX) {
 				sqlite3VdbeAddOp2(v, OP_IsNull, reg_pk,
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 50bc25152..e71e0cdb5 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -818,16 +818,16 @@ idlist(A) ::= nm(Y).
       memset(p, 0, sizeof(Expr));
       switch (op) {
       case TK_STRING:
-        p->affinity = AFFINITY_TEXT;
+        p->type = FIELD_TYPE_STRING;
         break;
       case TK_BLOB:
-        p->affinity = AFFINITY_BLOB;
+        p->type = FIELD_TYPE_SCALAR;
         break;
       case TK_INTEGER:
-        p->affinity = AFFINITY_INTEGER;
+        p->type = FIELD_TYPE_INTEGER;
         break;
       case TK_FLOAT:
-        p->affinity = AFFINITY_REAL;
+        p->type = FIELD_TYPE_NUMBER;
         break;
       }
       p->op = (u8)op;
@@ -865,7 +865,7 @@ term(A) ::= FLOAT|BLOB(X). {spanExpr(&A,pParse,@X,X);/*A-overwrites-X*/}
 term(A) ::= STRING(X).     {spanExpr(&A,pParse,@X,X);/*A-overwrites-X*/}
 term(A) ::= INTEGER(X). {
   A.pExpr = sqlite3ExprAlloc(pParse->db, TK_INTEGER, &X, 1);
-  A.pExpr->affinity = AFFINITY_INTEGER;
+  A.pExpr->type = FIELD_TYPE_INTEGER;
   A.zStart = X.z;
   A.zEnd = X.z + X.n;
   if( A.pExpr ) A.pExpr->flags |= EP_Leaf;
@@ -898,7 +898,7 @@ expr(A) ::= expr(A) COLLATE id(C). {
 expr(A) ::= CAST(X) LP expr(E) AS typedef(T) RP(Y). {
   spanSet(&A,&X,&Y); /*A-overwrites-X*/
   A.pExpr = sqlite3ExprAlloc(pParse->db, TK_CAST, 0, 1);
-  A.pExpr->affinity = sql_field_type_to_affinity(T.type);
+  A.pExpr->type = T.type;
   sqlite3ExprAttachSubtrees(pParse->db, A.pExpr, E.pExpr, 0);
 }
 %endif  SQLITE_OMIT_CAST
diff --git a/src/box/sql/resolve.c b/src/box/sql/resolve.c
index c1253ab95..7f106457c 100644
--- a/src/box/sql/resolve.c
+++ b/src/box/sql/resolve.c
@@ -339,8 +339,8 @@ lookupName(Parse * pParse,	/* The parsing context */
 				if (iCol < (int)pTab->def->field_count) {
 					cnt++;
 					if (iCol < 0) {
-						pExpr->affinity =
-							AFFINITY_INTEGER;
+						pExpr->type =
+							FIELD_TYPE_INTEGER;
 					} else if (pExpr->iTable == 0) {
 						testcase(iCol == 31);
 						testcase(iCol == 32);
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 0ee40093f..cc3e2f2fd 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1430,8 +1430,7 @@ sql_expr_list_to_key_info(struct Parse *parse, struct ExprList *list, int start)
 		sql_expr_coll(parse, item->pExpr, &unused, &id);
 		part->coll_id = id;
 		part->sort_order = item->sort_order;
-		enum affinity_type aff = sqlite3ExprAffinity(item->pExpr);
-		part->type =sql_affinity_to_field_type(aff);
+		part->type = sql_expr_type(item->pExpr);
 	}
 	return key_info;
 }
@@ -1725,20 +1724,20 @@ generateColumnNames(Parse * pParse,	/* Parser context */
 		p = pEList->a[i].pExpr;
 		if (NEVER(p == 0))
 			continue;
-		switch (p->affinity) {
-		case AFFINITY_INTEGER:
+		switch (p->type) {
+		case FIELD_TYPE_INTEGER:
 			sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, "INTEGER",
 					      SQLITE_TRANSIENT);
 			break;
-		case AFFINITY_REAL:
+		case FIELD_TYPE_NUMBER:
 			sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, "NUMERIC",
 					      SQLITE_TRANSIENT);
 			break;
-		case AFFINITY_TEXT:
+		case FIELD_TYPE_STRING:
 			sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, "TEXT",
 					      SQLITE_TRANSIENT);
 			break;
-		case AFFINITY_BLOB:
+		case FIELD_TYPE_SCALAR:
 			sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, "BLOB",
 					      SQLITE_TRANSIENT);
 			break;
@@ -1972,11 +1971,11 @@ sqlite3SelectAddColumnTypeAndCollation(Parse * pParse,		/* Parsing contexts */
 	a = pSelect->pEList->a;
 	for (uint32_t i = 0; i < pTab->def->field_count; i++) {
 		p = a[i].pExpr;
-		char affinity = sqlite3ExprAffinity(p);
-		if (affinity == 0)
-			affinity = AFFINITY_BLOB;
-		pTab->def->fields[i].affinity = affinity;
-		pTab->def->fields[i].type = sql_affinity_to_field_type(affinity);
+		enum field_type type = sql_expr_type(p);
+		if (type == FIELD_TYPE_ANY)
+			type = FIELD_TYPE_SCALAR;
+		pTab->def->fields[i].affinity = sql_field_type_to_affinity(type);
+		pTab->def->fields[i].type = type;
 		bool is_found;
 		uint32_t coll_id;
 
diff --git a/src/box/sql/sqliteInt.h b/src/box/sql/sqliteInt.h
index 690fa6431..7d1159345 100644
--- a/src/box/sql/sqliteInt.h
+++ b/src/box/sql/sqliteInt.h
@@ -1788,7 +1788,9 @@ struct Savepoint {
 #define SAVEPOINT_RELEASE    1
 #define SAVEPOINT_ROLLBACK   2
 
-#define sqlite3IsNumericAffinity(X)  ((X)>=AFFINITY_INTEGER)
+#define sql_type_is_numeric(X)  ((X) == FIELD_TYPE_INTEGER || \
+				 (X) == FIELD_TYPE_NUMBER || \
+				 (X) == FIELD_TYPE_UNSIGNED)
 
 /*
  * The AFFINITY_MASK values masks off the significant bits of an
@@ -2092,7 +2094,7 @@ struct Expr {
 	u8 op;			/* Operation performed by this node */
 	union {
 		/** The affinity of the column. */
-		enum affinity_type affinity;
+		enum field_type type;
 		/** Conflict action for RAISE() function. */
 		enum on_conflict_action on_conflict_action;
 	};
@@ -3707,7 +3709,9 @@ int sqlite3ExprIsConstantOrFunction(Expr *, u8);
 int sqlite3ExprIsTableConstant(Expr *, int);
 int sqlite3ExprIsInteger(Expr *, int *);
 int sqlite3ExprCanBeNull(const Expr *);
-int sqlite3ExprNeedsNoAffinityChange(const Expr *, char);
+
+int
+sql_expr_needs_type_change(const Expr *epr, enum field_type type);
 
 /**
  * This routine generates VDBE code that causes a single row of a
@@ -4253,26 +4257,30 @@ sql_index_type_str(struct sqlite3 *db, const struct index_def *idx_def);
 void
 sql_emit_table_types(struct Vdbe *v, struct space_def *def, int reg);
 
-/**
- * Return superposition of two affinities.
- * This may be required for determining resulting
- * affinity of expressions like a + '2'.
- */
-enum affinity_type
-sql_affinity_result(enum affinity_type aff1, enum affinity_type aff2);
+enum field_type
+sql_type_result(enum field_type lhs, enum field_type rhs);
 
-int sqlite3IndexAffinityOk(Expr * pExpr, char idx_affinity);
+enum field_type
+sql_index_type_is_ok(struct Expr *expr, enum field_type idx_type);
 
 /**
- * Return the affinity character for a single column of a table.
- * @param def space definition.
- * @param idx column index.
- * @retval AFFINITY
+ * Return the type of the expression pExpr.
+ *
+ * If pExpr is a column, a reference to a column via an 'AS' alias,
+ * or a sub-select with a column as the return value, then the
+ * type of that column is returned. Otherwise, type ANY is returned,
+ * indicating that the expression can feature any type.
+ *
+ * The WHERE clause expressions in the following statements all
+ * have an type:
+ *
+ * CREATE TABLE t1(a);
+ * SELECT * FROM t1 WHERE a;
+ * SELECT a AS b FROM t1 WHERE b;
+ * SELECT * FROM t1 WHERE (select a from t1);
  */
-char
-sqlite3TableColumnAffinity(struct space_def *def, int idx);
-
-char sqlite3ExprAffinity(Expr * pExpr);
+enum field_type
+sql_expr_type(Expr *pExpr);
 
 
 /**
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index cd71641b0..d12a2a833 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -1279,11 +1279,10 @@ valueFromExpr(sqlite3 * db,	/* The database connection */
 	assert((pExpr->flags & EP_TokenOnly) == 0 || pCtx == 0);
 
 	if (op == TK_CAST) {
-		u8 aff = pExpr->affinity;
-		rc = valueFromExpr(db, pExpr->pLeft, aff, ppVal, pCtx);
+		rc = valueFromExpr(db, pExpr->pLeft, pExpr->type, ppVal, pCtx);
 		testcase(rc != SQLITE_OK);
 		if (*ppVal) {
-			sqlite3VdbeMemCast(*ppVal, aff);
+			sqlite3VdbeMemCast(*ppVal, pExpr->type);
 			sqlite3ValueApplyAffinity(*ppVal, affinity);
 		}
 		return rc;
@@ -1316,8 +1315,8 @@ valueFromExpr(sqlite3 * db,	/* The database connection */
 			sqlite3ValueSetStr(pVal, -1, zVal, SQLITE_DYNAMIC);
 		}
 		if ((op == TK_INTEGER || op == TK_FLOAT)
-		    && affinity == AFFINITY_BLOB) {
-			sqlite3ValueApplyAffinity(pVal, AFFINITY_REAL);
+		    && affinity == FIELD_TYPE_SCALAR) {
+			sqlite3ValueApplyAffinity(pVal, FIELD_TYPE_INTEGER);
 		} else {
 			sqlite3ValueApplyAffinity(pVal, affinity);
 		}
@@ -1571,17 +1570,14 @@ sqlite3Stat4ProbeSetValue(Parse * pParse,	/* Parse context */
 		alloc.pIdx = idx;
 		alloc.ppRec = ppRec;
 
-		struct space *space = space_by_id(idx->space_id);
-		assert(space != NULL);
 		for (i = 0; i < nElem; i++) {
 			sqlite3_value *pVal = 0;
 			Expr *pElem =
 			    (pExpr ? sqlite3VectorFieldSubexpr(pExpr, i) : 0);
-			u8 aff = sql_space_index_part_affinity(space->def, idx,
-							       iVal + i);
+			enum field_type type =
+				idx->key_def->parts[iVal + i].type;
 			alloc.iVal = iVal + i;
-			aff = sql_affinity_to_field_type(aff);
-			rc = stat4ValueFromExpr(pParse, pElem, aff, &alloc,
+			rc = stat4ValueFromExpr(pParse, pElem, type, &alloc,
 						&pVal);
 			if (!pVal)
 				break;
diff --git a/src/box/sql/where.c b/src/box/sql/where.c
index 539296079..ac5390f92 100644
--- a/src/box/sql/where.c
+++ b/src/box/sql/where.c
@@ -298,7 +298,7 @@ whereScanNext(WhereScan * pScan)
 						/* Verify the affinity and collating sequence match */
 						if ((pTerm->eOperator & WO_ISNULL) == 0) {
 							pX = pTerm->pExpr;
-							if (!sqlite3IndexAffinityOk(pX, pScan->idxaff))
+							if (!sql_index_type_is_ok(pX, pScan->idx_type))
 								continue;
 							if (pScan->is_column_seen) {
 								Parse *pParse =
@@ -367,7 +367,7 @@ whereScanInit(WhereScan * pScan,	/* The WhereScan object being initialized */
 	pScan->pOrigWC = pWC;
 	pScan->pWC = pWC;
 	pScan->pIdxExpr = 0;
-	pScan->idxaff = 0;
+	pScan->idx_type = FIELD_TYPE_ANY;
 	pScan->coll = NULL;
 	pScan->is_column_seen = false;
 	if (idx_def != NULL) {
@@ -381,9 +381,9 @@ whereScanInit(WhereScan * pScan,	/* The WhereScan object being initialized */
 			struct space *sp = space_by_id(idx_def->space_id);
 			assert(sp != NULL);
 			if (sp->def->field_count == 0)
-				pScan->idxaff = AFFINITY_BLOB;
+				pScan->idx_type = FIELD_TYPE_SCALAR;
 			else
-				pScan->idxaff = sp->def->fields[iColumn].affinity;
+				pScan->idx_type = sp->def->fields[iColumn].type;
 			pScan->coll = idx_def->key_def->parts[j].coll;
 			pScan->is_column_seen = true;
 		} else {
@@ -424,13 +424,13 @@ where_scan_init(struct WhereScan *scan, struct WhereClause *clause,
 	scan->pOrigWC = clause;
 	scan->pWC = clause;
 	scan->pIdxExpr = NULL;
-	scan->idxaff = 0;
+	scan->idx_type = FIELD_TYPE_ANY;
 	scan->coll = NULL;
 	scan->is_column_seen = false;
 	if (key_def != NULL) {
 		int j = column;
 		column = key_def->parts[j].fieldno;
-		scan->idxaff = space_def->fields[column].affinity;
+		scan->idx_type = space_def->fields[column].type;
 		uint32_t coll_id = space_def->fields[column].coll_id;
 		struct coll_id *coll = coll_by_id(coll_id);
 		scan->coll = coll != NULL ? coll->coll : NULL;
@@ -2261,8 +2261,6 @@ whereRangeVectorLen(Parse * pParse,	/* Parsing context */
 		/* Test if comparison i of pTerm is compatible with column (i+nEq)
 		 * of the index. If not, exit the loop.
 		 */
-		char aff;	/* Comparison affinity */
-		char idxaff = 0;	/* Indexed columns affinity */
 		struct coll *pColl;	/* Comparison collation sequence */
 		Expr *pLhs = pTerm->pExpr->pLeft->x.pList->a[i].pExpr;
 		Expr *pRhs = pTerm->pExpr->pRight;
@@ -2282,11 +2280,12 @@ whereRangeVectorLen(Parse * pParse,	/* Parsing context */
 		    pLhs->iColumn != (int)parts[i + nEq].fieldno ||
 		    parts[i + nEq].sort_order != parts[nEq].sort_order)
 			break;
-		enum affinity_type rhs_aff = sqlite3ExprAffinity(pRhs);
-		aff = sql_affinity_result(rhs_aff, sqlite3ExprAffinity(pLhs));
-		idxaff =
-		    sqlite3TableColumnAffinity(space->def, pLhs->iColumn);
-		if (aff != idxaff)
+		enum field_type rhs_type = sql_expr_type(pRhs);
+		enum field_type type =
+			sql_type_result(rhs_type, sql_expr_type(pLhs));
+		enum field_type idx_type = pLhs->iColumn >= 0 ?
+			space->def->fields[pLhs->iColumn].type : FIELD_TYPE_INTEGER;
+		if (type != idx_type)
 			break;
 		uint32_t unused;
 		pColl = sql_binary_compare_coll_seq(pParse, pLhs, pRhs, &unused);
diff --git a/src/box/sql/whereInt.h b/src/box/sql/whereInt.h
index 4657055ea..286881a55 100644
--- a/src/box/sql/whereInt.h
+++ b/src/box/sql/whereInt.h
@@ -298,7 +298,8 @@ struct WhereScan {
 	/** Flag is set if actual column was encountered. */
 	bool is_column_seen;
 	Expr *pIdxExpr;		/* Search for this index expression */
-	char idxaff;		/* Must match this affinity, if zCollName!=NULL */
+	/** Must match this type, if zCollName!=NULL */
+	enum field_type idx_type;
 	unsigned char nEquiv;	/* Number of entries in aEquiv[] */
 	unsigned char iEquiv;	/* Next unused slot in aEquiv[] */
 	u32 opMask;		/* Acceptable operators */
diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
index aae5d6617..06335bcf7 100644
--- a/src/box/sql/wherecode.c
+++ b/src/box/sql/wherecode.c
@@ -392,6 +392,7 @@ codeApplyAffinity(Parse * pParse, int base, int n, char *zAff)
 		base++;
 		zAff++;
 	}
+
 	while (n > 1 && zAff[n - 1] == AFFINITY_BLOB) {
 		n--;
 	}
@@ -420,10 +421,11 @@ updateRangeAffinityStr(Expr * pRight,	/* RHS of comparison */
 {
 	int i;
 	for (i = 0; i < n; i++) {
+		enum field_type type = sql_affinity_to_field_type(zAff[i]);
 		Expr *p = sqlite3VectorFieldSubexpr(pRight, i);
-		enum affinity_type aff = sqlite3ExprAffinity(p);
-		if (sql_affinity_result(aff, zAff[i]) == AFFINITY_BLOB
-		    || sqlite3ExprNeedsNoAffinityChange(p, zAff[i])) {
+		enum field_type expr_type = sql_expr_type(p);
+		if (sql_type_result(expr_type, type) == FIELD_TYPE_SCALAR ||
+		    sql_expr_needs_type_change(p, type)) {
 			zAff[i] = AFFINITY_BLOB;
 		}
 	}
@@ -775,16 +777,16 @@ codeAllEqualityTerms(Parse * pParse,	/* Parsing context */
 				VdbeCoverage(v);
 			}
 			if (zAff) {
-				enum affinity_type aff =
-					sqlite3ExprAffinity(pRight);
-				if (sql_affinity_result(aff, zAff[j]) ==
-				    AFFINITY_BLOB) {
+				enum field_type type =
+					sql_expr_type(pRight);
+				enum field_type idx_type =
+					sql_affinity_to_field_type(zAff[j]);
+				if (sql_type_result(type, idx_type) ==
+				    FIELD_TYPE_SCALAR) {
 					zAff[j] = AFFINITY_BLOB;
 				}
-				if (sqlite3ExprNeedsNoAffinityChange
-				    (pRight, zAff[j])) {
+				if (sql_expr_needs_type_change(pRight, idx_type))
 					zAff[j] = AFFINITY_BLOB;
-				}
 			}
 		}
 	}
@@ -1163,20 +1165,12 @@ sqlite3WhereCodeOneLoopStart(WhereInfo * pWInfo,	/* Complete information about t
 		}
 		struct index_def *idx_pk = space->index[0]->def;
 		int fieldno = idx_pk->key_def->parts[0].fieldno;
-		char affinity = is_format_set ?
-				space->def->fields[fieldno].affinity :
-				AFFINITY_BLOB;
-		if (affinity == AFFINITY_UNDEFINED) {
-			if (idx_pk->key_def->part_count == 1 &&
-			    space->def->fields[fieldno].type ==
-			    FIELD_TYPE_INTEGER)
-				affinity = AFFINITY_INTEGER;
-			else
-				affinity = AFFINITY_BLOB;
-		}
+		char fd_type = is_format_set ?
+				space->def->fields[fieldno].type :
+				FIELD_TYPE_ANY;
 
 		uint32_t pk_part_count = idx_pk->key_def->part_count;
-		if (pk_part_count == 1 && affinity == AFFINITY_INTEGER) {
+		if (pk_part_count == 1 && fd_type == FIELD_TYPE_INTEGER) {
 			/* Right now INTEGER PRIMARY KEY is the only option to
 			 * get Tarantool's INTEGER column type. Need special handling
 			 * here: try to loosely convert FLOAT to INT. If RHS type
diff --git a/src/box/sql/whereexpr.c b/src/box/sql/whereexpr.c
index 342064ec8..6ffd5ae84 100644
--- a/src/box/sql/whereexpr.c
+++ b/src/box/sql/whereexpr.c
@@ -269,7 +269,7 @@ like_optimization_is_valid(Parse *pParse, Expr *pExpr, Expr **ppPrefix,
 	pLeft = pList->a[1].pExpr;
 	/* Value might be numeric */
 	if (pLeft->op != TK_COLUMN ||
-	    sqlite3ExprAffinity(pLeft) != AFFINITY_TEXT) {
+	    sql_expr_type(pLeft) != FIELD_TYPE_STRING) {
 		/* IMP: R-02065-49465 The left-hand side of the
 		 * LIKE operator must be the name of an indexed
 		 * column with TEXT affinity.
@@ -748,19 +748,18 @@ exprAnalyzeOrTerm(SrcList * pSrc,	/* the FROM clause */
 				} else if (pOrTerm->u.leftColumn != iColumn) {
 					okToChngToIN = 0;
 				} else {
-					int affLeft, affRight;
 					/* If the right-hand side is also a column, then the affinities
 					 * of both right and left sides must be such that no type
 					 * conversions are required on the right.  (Ticket #2249)
 					 */
-					affRight =
-					    sqlite3ExprAffinity(pOrTerm->pExpr->
-								pRight);
-					affLeft =
-					    sqlite3ExprAffinity(pOrTerm->pExpr->
-								pLeft);
-					if (affRight != 0
-					    && affRight != affLeft) {
+					enum field_type rhs =
+						sql_expr_type(pOrTerm->pExpr->
+							pRight);
+					enum field_type lhs =
+						sql_expr_type(pOrTerm->pExpr->
+							pLeft);
+					if (rhs != FIELD_TYPE_ANY &&
+					    rhs != lhs) {
 						okToChngToIN = 0;
 					} else {
 						pOrTerm->wtFlags |= TERM_OR_OK;
@@ -836,21 +835,17 @@ exprAnalyzeOrTerm(SrcList * pSrc,	/* the FROM clause */
 static int
 termIsEquivalence(Parse * pParse, Expr * pExpr)
 {
-	char aff1, aff2;
 	if (!OptimizationEnabled(pParse->db, SQLITE_Transitive))
 		return 0;
 	if (pExpr->op != TK_EQ)
 		return 0;
 	if (ExprHasProperty(pExpr, EP_FromJoin))
 		return 0;
-	aff1 = sqlite3ExprAffinity(pExpr->pLeft);
-	aff2 = sqlite3ExprAffinity(pExpr->pRight);
-	if (aff1 != aff2
-	    && (!sqlite3IsNumericAffinity(aff1)
-		|| !sqlite3IsNumericAffinity(aff2))
-	    ) {
+	enum field_type lhs_type = sql_expr_type(pExpr->pLeft);
+	enum field_type rhs_type = sql_expr_type(pExpr->pRight);
+	if (lhs_type != rhs_type && (!sql_type_is_numeric(lhs_type) ||
+				     !sql_type_is_numeric(rhs_type)))
 		return 0;
-	}
 	uint32_t unused;
 	struct coll *coll1 =
 	    sql_binary_compare_coll_seq(pParse, pExpr->pLeft, pExpr->pRight,
diff --git a/test/sql-tap/lua-tables.test.lua b/test/sql-tap/lua-tables.test.lua
index 19d80b611..409976bdd 100755
--- a/test/sql-tap/lua-tables.test.lua
+++ b/test/sql-tap/lua-tables.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(9)
+test:plan(12)
 
 test:do_test(
     "lua-tables-prepare-1",
@@ -124,4 +124,40 @@ test:do_execsql_test(
     [[SELECT * FROM "t" INDEXED BY "i"]],
     {1, 4, 2, 2, 3, 3, 4, 3})
 
+-- gh-3886: indexes created from Lua are visible
+-- to query optimizer.
+--
+test:do_test(
+    "lua-tables-prepare-10",
+    function()
+        sp = box.schema.space.create("TEST", {
+            engine = 'memtx',
+            format = {
+                { name = 'ID', type = 'unsigned' },
+                { name = 'A', type = 'unsigned' }
+            }})
+        sp:create_index('primary', {parts = {1, 'unsigned' } })
+        sp:create_index('secondary', {parts = {2, 'unsigned' } })
+        sp:insert({1,1})
+        sp:insert({2,2})
+        sp:insert({3,3})
+    end,
+    {})
+
+test:do_eqp_test(
+    11,
+    [[
+        SELECT * FROM test WHERE id = 2;
+    ]], {
+        {0, 0, 0, 'SEARCH TABLE TEST USING PRIMARY KEY (ID=?)'}
+    })
+
+test:do_eqp_test(
+    12,
+    [[
+        SELECT * FROM test WHERE a = 5;
+    ]], {
+        {0, 0, 0, 'SEARCH TABLE TEST USING COVERING INDEX secondary (A=?)'}
+    })
+
 test:finish_test()
diff --git a/test/sql/iproto.result b/test/sql/iproto.result
index 9ace282d5..326eda586 100644
--- a/test/sql/iproto.result
+++ b/test/sql/iproto.result
@@ -792,7 +792,7 @@ cn:execute('select * from "test"')
 ---
 - metadata:
   - name: id
-    type: UNKNOWN
+    type: INTEGER
   - name: x
     type: UNKNOWN
   rows:
-- 
2.15.1