Tarantool development patches archive
 help / color / mirror / Atom feed
From: imeevma@tarantool.org
To: v.shpilevoy@tarantool.org, tsafin@tarantool.org,
	tarantool-patches@dev.tarantool.org
Subject: [Tarantool-patches] [PATCH v1 1/1] sql: remove implicit cast for COMPARISON
Date: Fri, 20 Mar 2020 15:34:58 +0300	[thread overview]
Message-ID: <9a402676dbab6bdc4b1cbbdfc1f5069214fdfa06.1584707598.git.imeevma@gmail.com> (raw)

This patch removes implicit cast for comparison. Also, it make
search using an index work the same way as in case of fullscan in
most cases.

Closes #4230
Closes #4783
---
https://github.com/tarantool/tarantool/issues/4230
https://github.com/tarantool/tarantool/issues/4783
https://github.com/tarantool/tarantool/tree/imeevma/gh-4230-remove-implicit-cast-for-index-search-second

@ChangeLog Remove implicit cast for comparison gh-4230

 src/box/sql/sqlInt.h                  |   3 +
 src/box/sql/vdbe.c                    | 638 +++++++++++++++++++---------------
 src/box/sql/vdbemem.c                 |   1 +
 src/box/sql/where.c                   |   4 +-
 src/box/sql/wherecode.c               | 204 +----------
 src/box/tuple_compare.cc              |   6 +
 src/box/tuple_compare.h               |   9 +
 test/sql-tap/identifier_case.test.lua |   6 +-
 test/sql-tap/in1.test.lua             |  14 +-
 test/sql-tap/in4.test.lua             |  40 +--
 test/sql-tap/index1.test.lua          |   4 +-
 test/sql-tap/insert3.test.lua         |   2 +-
 test/sql-tap/intpkey.test.lua         |  12 +-
 test/sql-tap/join.test.lua            |   8 +-
 test/sql-tap/misc1.test.lua           |  32 +-
 test/sql-tap/select1.test.lua         |  10 +-
 test/sql-tap/select7.test.lua         |   2 +-
 test/sql-tap/sql-errors.test.lua      |   4 +-
 test/sql-tap/subquery.test.lua        |   4 +-
 test/sql-tap/tkt-9a8b09f8e6.test.lua  |  88 ++---
 test/sql-tap/tkt-f973c7ac31.test.lua  |  34 +-
 test/sql-tap/tkt-fc7bd6358f.test.lua  |   8 +-
 test/sql-tap/tkt3493.test.lua         |  30 +-
 test/sql-tap/transitive1.test.lua     |  16 +-
 test/sql-tap/where2.test.lua          |  24 +-
 test/sql-tap/where5.test.lua          |  12 +-
 test/sql-tap/whereB.test.lua          |  80 ++---
 test/sql-tap/whereC.test.lua          |  10 +-
 test/sql/boolean.result               | 230 ++++++------
 test/sql/types.result                 |   7 +-
 test/sql/types.test.lua               |   2 +-
 31 files changed, 702 insertions(+), 842 deletions(-)

diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 1579cc9..cd626bd 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -1304,6 +1304,9 @@ enum trim_side_mask {
 				 (X) == FIELD_TYPE_UNSIGNED || \
 				 (X) == FIELD_TYPE_DOUBLE)
 
+#define sql_mp_type_is_numeric(X)  ((X) == MP_INT || (X) == MP_UINT || \
+				    (X) == MP_FLOAT || (X) == MP_DOUBLE)
+
 /*
  * Additional bit values that can be ORed with an type without
  * changing the type.
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index e8a029a..8c95fd4 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -660,6 +660,216 @@ vdbe_field_ref_fetch_field(struct vdbe_field_ref *field_ref, uint32_t fieldno)
 }
 
 /**
+ * Prepare mem to use for comparison with value with type UNSIGNED
+ * from index.
+ *
+ * @param[out] mem Mem to prepare for comparison.
+ * @param[in][out] op Operation that was before preperation and
+ *                 operation after.
+ * @param is_eq True if operation was EQ.
+ * @param[out] is_none True if nothing will be found.
+ * @retval 0 on success.
+ * @retval -1 on error.
+ */
+static int
+sql_mem_to_unsigned_for_comp(struct Mem *mem, uint32_t *op, int is_eq,
+			     bool *is_none)
+{
+	assert(*is_none == false);
+	uint32_t orig_op = *op;
+	enum mp_type mp_type = sql_value_type(mem);
+	assert(mp_type == MP_DOUBLE || mp_type == MP_INT);
+	if (mp_type == MP_INT || mem->u.r < 0) {
+		if (orig_op == OP_SeekGE || orig_op == OP_SeekGT) {
+			mem_set_u64(mem, 0);
+			*op = OP_SeekGE;
+			return 0;
+		}
+		*is_none = true;
+		return 0;
+	}
+	double d = mem->u.r;
+	if (d >= UINT64_MAX) {
+		if (orig_op == OP_SeekLE || orig_op == OP_SeekLT) {
+			mem_set_u64(mem, UINT64_MAX);
+			*op = OP_SeekLE;
+			return 0;
+		}
+		*is_none = true;
+		return 0;
+	}
+	uint64_t u = (uint64_t)d;
+	if (d != u && is_eq) {
+		*is_none = true;
+		return 0;
+	}
+	mem_set_u64(mem, u);
+	if (d == u)
+		return 0;
+	if (orig_op == OP_SeekGE)
+		*op = OP_SeekGT;
+	else if (orig_op == OP_SeekLT)
+		*op = OP_SeekLE;
+	return 0;
+}
+
+/**
+ * Prepare mem to use for comparison with value with type INTEGER
+ * from index.
+ *
+ * @param[out] mem Mem to prepare for comparison.
+ * @param[in][out] op Operation that was before preperation and
+ *                 operation after.
+ * @param is_eq True if operation was EQ.
+ * @param[out] is_none True if nothing will be found.
+ * @retval 0 on success.
+ * @retval -1 on error.
+ */
+static int
+sql_mem_to_integer_for_comp(struct Mem *mem, uint32_t *op, int is_eq,
+			    bool *is_none)
+{
+	assert(*is_none == false);
+	uint32_t orig_op = *op;
+	assert(sql_value_type(mem) == MP_DOUBLE);
+	double d = mem->u.r;
+	if (d < INT64_MIN) {
+		if (orig_op == OP_SeekGE || orig_op == OP_SeekGT) {
+			mem_set_i64(mem, INT64_MIN);
+			*op = OP_SeekGE;
+			return 0;
+		}
+		*is_none = true;
+		return 0;
+	}
+	if (d >= UINT64_MAX) {
+		if (orig_op == OP_SeekLE || orig_op == OP_SeekLT) {
+			mem_set_u64(mem, UINT64_MAX);
+			*op = OP_SeekLE;
+			return 0;
+		}
+		*is_none = true;
+		return 0;
+	}
+	int64_t i = (int64_t)d;
+	uint64_t u = (uint64_t)d;
+	if (d != i && d != u && is_eq) {
+		*is_none = true;
+		return 0;
+	}
+	if (d > 0)
+		mem_set_u64(mem, u);
+	else
+		mem_set_i64(mem, i);
+	if (d == u || d == i)
+		return 0;
+	if (d >= 0) {
+		if (orig_op == OP_SeekGE)
+			*op = OP_SeekGT;
+		else if (orig_op == OP_SeekLT)
+			*op = OP_SeekLE;
+		return 0;
+	}
+	if (orig_op == OP_SeekLE)
+		*op = OP_SeekLT;
+	else if (orig_op == OP_SeekGT)
+		*op = OP_SeekGE;
+	return 0;
+}
+
+/**
+ * Prepare mem to use for comparison with value with type DOUBLE
+ * from index.
+ *
+ * @param[out] mem Mem to prepare for comparison.
+ * @param[in][out] op Operation that was before preperation and
+ *                 operation after.
+ * @param is_eq True if operation was EQ.
+ * @param[out] is_none True if nothing will be found.
+ * @retval 0 on success.
+ * @retval -1 on error.
+ */
+static int
+sql_mem_to_double_for_comp(struct Mem *mem, uint32_t *op, int is_eq,
+			   bool *is_none)
+{
+	assert(*is_none == false);
+	uint32_t orig_op = *op;
+	enum mp_type mp_type = sql_value_type(mem);
+	assert(mp_type == MP_INT || mp_type == MP_UINT);
+	int64_t i = mem->u.i;
+	uint64_t u = mem->u.u;
+	/* 2^53 == 9007199254740992 */
+	if (mp_type == MP_INT && i > -9007199254740992) {
+		sqlVdbeMemSetDouble(mem, i);
+		return 0;
+	} else if (mp_type == MP_UINT && u < 9007199254740992) {
+		sqlVdbeMemSetDouble(mem, u);
+		return 0;
+	}
+
+	double d = mp_type == MP_INT ? (double)i : (double)u;
+	if (is_eq) {
+		if (i != d && u != d)
+			*is_none = true;
+		else
+			sqlVdbeMemSetDouble(mem, d);
+		return 0;
+	}
+
+	sqlVdbeMemSetDouble(mem, d);
+	if (mp_type == MP_INT) {
+		if (orig_op == OP_SeekGT && d > i)
+			*op = OP_SeekGE;
+		else if (orig_op == OP_SeekGE && d < i)
+			*op = OP_SeekGT;
+		else if (orig_op == OP_SeekLT && d < i)
+			*op = OP_SeekLE;
+		else if (orig_op == OP_SeekLE && d > i)
+			*op = OP_SeekLT;
+		return 0;
+	}
+	if (orig_op == OP_SeekGT && d > u)
+		*op = OP_SeekGE;
+	else if (orig_op == OP_SeekGE && d < u)
+		*op = OP_SeekGT;
+	else if (orig_op == OP_SeekLT && d < u)
+		*op = OP_SeekLE;
+	else if (orig_op == OP_SeekLE && d > u)
+		*op = OP_SeekLT;
+	return 0;
+}
+
+/**
+ * Prepare mem to use for comparison with value with numeric type
+ * from index.
+ *
+ * @param[in][out] r Unpacked tuple that should be prepared for
+ *                 comparison.
+ * @param i field of unpacked tuple that should be prepared.
+ * @param is_eq True if operation was EQ.
+ * @param[out] is_none True if nothing will be found.
+ * @retval 0 on success.
+ * @retval -1 on error.
+ */
+static int
+sql_prepare_rec_for_comp(struct UnpackedRecord *r, int i, int is_eq,
+			 bool *is_none)
+{
+	struct Mem *mem = &r->aMem[i];
+	enum field_type field_type = r->key_def->parts[i].type;
+	uint32_t opcode = r->opcode;
+	if (field_type == FIELD_TYPE_UNSIGNED)
+		sql_mem_to_unsigned_for_comp(mem, &opcode, is_eq, is_none);
+	else if (field_type == FIELD_TYPE_INTEGER)
+		sql_mem_to_integer_for_comp(mem, &opcode, is_eq, is_none);
+	else
+		sql_mem_to_double_for_comp(mem, &opcode, is_eq, is_none);
+	r->opcode = opcode;
+	return 0;
+}
+
+/**
  * Find the left closest field for a given fieldno in field_ref's
  * slot_bitmask. The fieldno is expected to be greater than 0.
  * @param field_ref The vdbe_field_ref instance to use.
@@ -2050,15 +2260,15 @@ case OP_Cast: {                  /* in1 */
  * jump to address P2.  Or if the SQL_STOREP2 flag is set in P5, then
  * store the result of comparison in register P2.
  *
- * Once any conversions have taken place, and neither value is NULL,
- * the values are compared. If both values are blobs then memcmp() is
- * used to determine the results of the comparison.  If both values
- * are text, then the appropriate collating function specified in
- * P4 is used to do the comparison.  If P4 is not specified then
- * memcmp() is used to compare text string.  If both values are
- * numeric, then a numeric comparison is used. If the two values
- * are of different types, then numbers are considered less than
- * strings and strings are considered less than blobs.
+ * If neither value is NULL, the values are compared. If both
+ * values are blobs then memcmp() is used to determine the results
+ * of the comparison. If both values are booleans, TRUE if more
+ * than FALSE. If both values are text, then the appropriate
+ * collating function specified in P4 is used to do the
+ * comparison. If P4 is not specified then memcmp() is used to
+ * compare text string. If both values are numeric, then a numeric
+ * comparison is used. If both values are not NULL and the above
+ * rules cannot be applied to them, an error is generated.
  *
  * If SQL_NULLEQ is set in P5 then the result of comparison is always either
  * true or false and is never NULL.  If both operands are NULL then the result
@@ -2096,15 +2306,8 @@ case OP_Cast: {                  /* in1 */
  * reg(P3) is NULL then the take the jump.  If the SQL_JUMPIFNULL
  * bit is clear then fall through if either operand is NULL.
  *
- * Once any conversions have taken place, and neither value is NULL,
- * the values are compared. If both values are blobs then memcmp() is
- * used to determine the results of the comparison.  If both values
- * are text, then the appropriate collating function specified in
- * P4 is  used to do the comparison.  If P4 is not specified then
- * memcmp() is used to compare text string.  If both values are
- * numeric, then a numeric comparison is used. If the two values
- * are of different types, then numbers are considered less than
- * strings and strings are considered less than blobs.
+ * See the Eq opcode for additional information about comparison
+ * rules.
  */
 /* Opcode: Le P1 P2 P3 P4 P5
  * Synopsis: IF r[P3]<=r[P1]
@@ -2133,189 +2336,109 @@ case OP_Lt:               /* same as TK_LT, jump, in1, in3 */
 case OP_Le:               /* same as TK_LE, jump, in1, in3 */
 case OP_Gt:               /* same as TK_GT, jump, in1, in3 */
 case OP_Ge: {             /* same as TK_GE, jump, in1, in3 */
-	int res, res2;      /* Result of the comparison of pIn1 against pIn3 */
-	u32 flags1;         /* Copy of initial value of pIn1->flags */
-	u32 flags3;         /* Copy of initial value of pIn3->flags */
-
+	/*
+	 * Result of comparison: more than 0 if l > r,
+	 * 0 if l == r, less than 0 if l < r.
+	 */
+	int cmp_res;
+	/* Result of the operation. */
+	bool result;
 	pIn1 = &aMem[pOp->p1];
 	pIn3 = &aMem[pOp->p3];
-	flags1 = pIn1->flags;
-	flags3 = pIn3->flags;
-	enum field_type ft_p1 = pIn1->field_type;
-	enum field_type ft_p3 = pIn3->field_type;
-	if ((flags1 | flags3)&MEM_Null) {
-		/* One or both operands are NULL */
-		if (pOp->p5 & SQL_NULLEQ) {
-			/* If SQL_NULLEQ is set (which will only happen if the operator is
-			 * OP_Eq or OP_Ne) then take the jump or not depending on whether
-			 * or not both operands are null.
-			 */
-			assert(pOp->opcode==OP_Eq || pOp->opcode==OP_Ne);
-			assert((flags1 & MEM_Cleared)==0);
-			assert((pOp->p5 & SQL_JUMPIFNULL)==0);
-			if ((flags1&flags3&MEM_Null)!=0
-			    && (flags3&MEM_Cleared)==0
-				) {
-				res = 0;  /* Operands are equal */
-			} else {
-				res = 1;  /* Operands are not equal */
-			}
-		} else {
-			/* SQL_NULLEQ is clear and at least one operand is NULL,
-			 * then the result is always NULL.
-			 * The jump is taken if the SQL_JUMPIFNULL bit is set.
+
+	bool has_scalar = (pIn1->field_type == FIELD_TYPE_SCALAR) ||
+			  (pIn3->field_type == FIELD_TYPE_SCALAR);
+	enum mp_type ltype = sql_value_type(pIn1);
+	enum mp_type rtype = sql_value_type(pIn3);
+	assert(ltype != MP_MAP && rtype != MP_MAP);
+	assert(ltype != MP_ARRAY && rtype != MP_ARRAY);
+	int mp_classes_comp = mp_type_classes_comp(rtype, ltype);
+
+	if (ltype == MP_NIL || rtype == MP_NIL) {
+		if ((pOp->p5 & SQL_NULLEQ) == 0) {
+			/*
+			 * SQL_NULLEQ is clear and at least one
+			 * operand is NULL, then the result is
+			 * always NULL. The jump is taken if the
+			 * SQL_JUMPIFNULL bit is set.
 			 */
 			if (pOp->p5 & SQL_STOREP2) {
 				pOut = vdbe_prepare_null_out(p, pOp->p2);
-				iCompare = 1;    /* Operands are not equal */
+				iCompare = 1;
 				REGISTER_TRACE(p, pOp->p2, pOut);
 			} else {
 				VdbeBranchTaken(2,3);
-				if (pOp->p5 & SQL_JUMPIFNULL) {
+				if (pOp->p5 & SQL_JUMPIFNULL)
 					goto jump_to_p2;
-				}
 			}
 			break;
 		}
-	} else if (((flags1 | flags3) & MEM_Bool) != 0 ||
-		   ((flags1 | flags3) & MEM_Blob) != 0) {
 		/*
-		 * If one of values is of type BOOLEAN or VARBINARY,
-		 * then the second one must be of the same type as
-		 * well. Otherwise an error is raised.
+		 * If SQL_NULLEQ is set (which will only happen if
+		 * the operator is OP_Eq or OP_Ne) then take the
+		 * jump or not depending on whether or not both
+		 * operands are null.
 		 */
-		int type_arg1 = flags1 & (MEM_Bool | MEM_Blob);
-		int type_arg3 = flags3 & (MEM_Bool | MEM_Blob);
-		if (type_arg1 != type_arg3) {
-			char *inconsistent_type = type_arg1 != 0 ?
-						  mem_type_to_str(pIn3) :
-						  mem_type_to_str(pIn1);
-			char *expected_type     = type_arg1 != 0 ?
-						  mem_type_to_str(pIn1) :
-						  mem_type_to_str(pIn3);
-			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
-				 inconsistent_type, expected_type);
-			goto abort_due_to_error;
-		}
-		res = sqlMemCompare(pIn3, pIn1, NULL);
+		assert(pOp->opcode == OP_Eq || pOp->opcode == OP_Ne);
+		assert((pOp->p5 & SQL_JUMPIFNULL) == 0);
+		cmp_res = !(ltype == MP_NIL && rtype == MP_NIL);
+	} else if (mp_classes_comp == 0) {
+		cmp_res = sqlMemCompare(pIn3, pIn1, pOp->p4.pColl);
+	} else if (has_scalar) {
+		cmp_res = mp_classes_comp;
 	} else {
-		enum field_type type = pOp->p5 & FIELD_TYPE_MASK;
-		if (sql_type_is_numeric(type)) {
-			if ((flags1 | flags3)&MEM_Str) {
-				if ((flags1 & MEM_Str) == MEM_Str) {
-					mem_apply_numeric_type(pIn1);
-					testcase( flags3!=pIn3->flags); /* Possible if pIn1==pIn3 */
-					flags3 = pIn3->flags;
-				}
-				if ((flags3 & MEM_Str) == MEM_Str) {
-					if (mem_apply_numeric_type(pIn3) != 0) {
-						diag_set(ClientError,
-							 ER_SQL_TYPE_MISMATCH,
-							 sql_value_to_diag_str(pIn3),
-							 "numeric");
-						goto abort_due_to_error;
-					}
+		diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+			 mem_type_to_str(pIn1), mem_type_to_str(pIn3));
+		goto abort_due_to_error;
+	}
 
-				}
-			}
-			/* Handle the common case of integer comparison here, as an
-			 * optimization, to avoid a call to sqlMemCompare()
-			 */
-			if ((pIn1->flags & pIn3->flags & (MEM_Int | MEM_UInt)) != 0) {
-				if ((pIn1->flags & pIn3->flags & MEM_Int) != 0) {
-					if (pIn3->u.i > pIn1->u.i)
-						res = +1;
-					else if (pIn3->u.i < pIn1->u.i)
-						res = -1;
-					else
-						res = 0;
-					goto compare_op;
-				}
-				if ((pIn1->flags & pIn3->flags & MEM_UInt) != 0) {
-					if (pIn3->u.u > pIn1->u.u)
-						res = +1;
-					else if (pIn3->u.u < pIn1->u.u)
-						res = -1;
-					else
-						res = 0;
-					goto compare_op;
-				}
-				if ((pIn1->flags & MEM_UInt) != 0 &&
-				    (pIn3->flags & MEM_Int) != 0) {
-					res = -1;
-					goto compare_op;
-				}
-				res = 1;
-				goto compare_op;
-			}
-		} else if (type == FIELD_TYPE_STRING) {
-			if ((flags1 & MEM_Str) == 0 &&
-			    (flags1 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
-				testcase( pIn1->flags & MEM_Int);
-				testcase( pIn1->flags & MEM_Real);
-				sqlVdbeMemStringify(pIn1);
-				testcase( (flags1&MEM_Dyn) != (pIn1->flags&MEM_Dyn));
-				flags1 = (pIn1->flags & ~MEM_TypeMask) | (flags1 & MEM_TypeMask);
-				assert(pIn1!=pIn3);
-			}
-			if ((flags3 & MEM_Str) == 0 &&
-			    (flags3 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
-				testcase( pIn3->flags & MEM_Int);
-				testcase( pIn3->flags & MEM_Real);
-				sqlVdbeMemStringify(pIn3);
-				testcase( (flags3&MEM_Dyn) != (pIn3->flags&MEM_Dyn));
-				flags3 = (pIn3->flags & ~MEM_TypeMask) | (flags3 & MEM_TypeMask);
-			}
-		}
-		assert(pOp->p4type==P4_COLLSEQ || pOp->p4.pColl==0);
-		res = sqlMemCompare(pIn3, pIn1, pOp->p4.pColl);
-	}
-			compare_op:
-	switch( pOp->opcode) {
-	case OP_Eq:    res2 = res==0;     break;
-	case OP_Ne:    res2 = res;        break;
-	case OP_Lt:    res2 = res<0;      break;
-	case OP_Le:    res2 = res<=0;     break;
-	case OP_Gt:    res2 = res>0;      break;
-	default:       res2 = res>=0;     break;
-	}
-
-	/* Undo any changes made by mem_apply_type() to the input registers. */
-	assert((pIn1->flags & MEM_Dyn) == (flags1 & MEM_Dyn));
-	pIn1->flags = flags1;
-	pIn1->field_type = ft_p1;
-	assert((pIn3->flags & MEM_Dyn) == (flags3 & MEM_Dyn));
-	pIn3->flags = flags3;
-	pIn3->field_type = ft_p3;
+	switch (pOp->opcode) {
+	case OP_Eq:
+		result = cmp_res == 0;
+		break;
+	case OP_Ne:
+		result = cmp_res != 0;
+		break;
+	case OP_Lt:
+		result = cmp_res < 0;
+		break;
+	case OP_Le:
+		result = cmp_res <= 0;
+		break;
+	case OP_Gt:
+		result = cmp_res > 0;
+		break;
+	case OP_Ge:
+		result = cmp_res >= 0;
+		break;
+	default:
+		unreachable();
+	}
 
 	if (pOp->p5 & SQL_STOREP2) {
-		iCompare = res;
-		res2 = res2!=0;  /* For this path res2 must be exactly 0 or 1 */
-		if ((pOp->p5 & SQL_KEEPNULL)!=0) {
-			/* The KEEPNULL flag prevents OP_Eq from overwriting a NULL with true
-			 * and prevents OP_Ne from overwriting NULL with false.  This flag
-			 * is only used in contexts where either:
-			 *   (1) op==OP_Eq && (r[P2]==NULL || r[P2]==0)
-			 *   (2) op==OP_Ne && (r[P2]==NULL || r[P2]==1)
-			 * Therefore it is not necessary to check the content of r[P2] for
-			 * NULL.
-			 */
-			assert(pOp->opcode==OP_Ne || pOp->opcode==OP_Eq);
-			assert(res2==0 || res2==1);
-			testcase( res2==0 && pOp->opcode==OP_Eq);
-			testcase( res2==1 && pOp->opcode==OP_Eq);
-			testcase( res2==0 && pOp->opcode==OP_Ne);
-			testcase( res2==1 && pOp->opcode==OP_Ne);
-			if ((pOp->opcode==OP_Eq)==res2) break;
+		iCompare = cmp_res;
+		/*
+		 * The KEEPNULL flag prevents OP_Eq from
+		 * overwriting a NULL with true and prevents OP_Ne
+		 * from overwriting NULL with false. This flag
+		 * is only used in contexts where either:
+		 *   (1) op==OP_Eq && (r[P2]==NULL || r[P2]==0)
+		 *   (2) op==OP_Ne && (r[P2]==NULL || r[P2]==1)
+		 * Therefore it is not necessary to check the
+		 * content of r[P2] for NULL.
+		 */
+		if ((pOp->p5 & SQL_KEEPNULL) != 0) {
+			assert(pOp->opcode == OP_Ne || pOp->opcode == OP_Eq);
+			if ((pOp->opcode == OP_Eq) == result)
+				break;
 		}
 		pOut = vdbe_prepare_null_out(p, pOp->p2);
-		mem_set_bool(pOut, res2);
+		mem_set_bool(pOut, result);
 		REGISTER_TRACE(p, pOp->p2, pOut);
 	} else {
-		VdbeBranchTaken(res!=0, (pOp->p5 & SQL_NULLEQ)?2:3);
-		if (res2) {
+		VdbeBranchTaken(cmp_res != 0, (pOp->p5 & SQL_NULLEQ) ? 2 : 3);
+		if (result)
 			goto jump_to_p2;
-		}
 	}
 	break;
 }
@@ -3346,7 +3469,6 @@ case OP_SeekGT: {       /* jump, in3 */
 	VdbeCursor *pC;    /* The cursor to seek */
 	UnpackedRecord r;  /* The key to seek for */
 	int nField;        /* Number of columns or fields in the key */
-	i64 iKey;          /* The id we are to seek to */
 	int eqOnly;        /* Only interested in == results */
 
 	assert(pOp->p1>=0 && pOp->p1<p->nCursor);
@@ -3364,86 +3486,6 @@ case OP_SeekGT: {       /* jump, in3 */
 #ifdef SQL_DEBUG
 	pC->seekOp = pOp->opcode;
 #endif
-	iKey = 0;
-	/*
-	 * In case floating value is intended to be passed to
-	 * iterator over integer field, we must truncate it to
-	 * integer value and change type of iterator:
-	 * a > 1.5 -> a >= 2
-	 */
-	int int_field = pOp->p5;
-	bool is_neg = false;
-
-	if (int_field > 0) {
-		/* The input value in P3 might be of any type: integer, real, string,
-		 * blob, or NULL.  But it needs to be an integer before we can do
-		 * the seek, so convert it.
-		 */
-		pIn3 = &aMem[int_field];
-		if ((pIn3->flags & MEM_Null) != 0)
-			goto skip_truncate;
-		if ((pIn3->flags & MEM_Str) != 0)
-			mem_apply_numeric_type(pIn3);
-		int64_t i;
-		if ((pIn3->flags & MEM_Int) == MEM_Int) {
-			i = pIn3->u.i;
-			is_neg = true;
-		} else if ((pIn3->flags & MEM_UInt) == MEM_UInt) {
-			i = pIn3->u.u;
-			is_neg = false;
-		} else if ((pIn3->flags & MEM_Real) == MEM_Real) {
-			if (pIn3->u.r > INT64_MAX)
-				i = INT64_MAX;
-			else if (pIn3->u.r < INT64_MIN)
-				i = INT64_MIN;
-			else
-				i = pIn3->u.r;
-			is_neg = i < 0;
-		} else {
-			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
-				 sql_value_to_diag_str(pIn3), "integer");
-			goto abort_due_to_error;
-		}
-		iKey = i;
-
-		/* If the P3 value could not be converted into an integer without
-		 * loss of information, then special processing is required...
-		 */
-		if ((pIn3->flags & (MEM_Int | MEM_UInt)) == 0) {
-			if ((pIn3->flags & MEM_Real)==0) {
-				/* If the P3 value cannot be converted into any kind of a number,
-				 * then the seek is not possible, so jump to P2
-				 */
-				VdbeBranchTaken(1,2); goto jump_to_p2;
-				break;
-			}
-
-			/* If the approximation iKey is larger than the actual real search
-			 * term, substitute >= for > and < for <=. e.g. if the search term
-			 * is 4.9 and the integer approximation 5:
-			 *
-			 *        (x >  4.9)    ->     (x >= 5)
-			 *        (x <= 4.9)    ->     (x <  5)
-			 */
-			if (pIn3->u.r<(double)iKey) {
-				assert(OP_SeekGE==(OP_SeekGT-1));
-				assert(OP_SeekLT==(OP_SeekLE-1));
-				assert((OP_SeekLE & 0x0001)==(OP_SeekGT & 0x0001));
-				if ((oc & 0x0001)==(OP_SeekGT & 0x0001)) oc--;
-			}
-
-			/* If the approximation iKey is smaller than the actual real search
-			 * term, substitute <= for < and > for >=.
-			 */
-			else if (pIn3->u.r>(double)iKey) {
-				assert(OP_SeekLE==(OP_SeekLT+1));
-				assert(OP_SeekGT==(OP_SeekGE+1));
-				assert((OP_SeekLT & 0x0001)==(OP_SeekGE & 0x0001));
-				if ((oc & 0x0001)==(OP_SeekLT & 0x0001)) oc++;
-			}
-		}
-	}
-skip_truncate:
 	/*
 	 * For a cursor with the OPFLAG_SEEKEQ hint, only the
 	 * OP_SeekGE and OP_SeekLE opcodes are allowed, and these
@@ -3465,22 +3507,38 @@ skip_truncate:
 	assert(nField>0);
 	r.key_def = pC->key_def;
 	r.nField = (u16)nField;
-
-	if (int_field > 0)
-		mem_set_int(&aMem[int_field], iKey, is_neg);
-
-	r.default_rc = ((1 & (oc - OP_SeekLT)) ? -1 : +1);
-	assert(oc!=OP_SeekGT || r.default_rc==-1);
-	assert(oc!=OP_SeekLE || r.default_rc==-1);
-	assert(oc!=OP_SeekGE || r.default_rc==+1);
-	assert(oc!=OP_SeekLT || r.default_rc==+1);
-
 	r.aMem = &aMem[pOp->p3];
 #ifdef SQL_DEBUG
 	{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
 #endif
 	r.eqSeen = 0;
 	r.opcode = oc;
+	for (int i = 0; i < nField; ++i) {
+		struct Mem *mem = &r.aMem[i];
+		enum mp_type mp_type = sql_value_type(mem);
+		enum field_type field_type = r.key_def->parts[i].type;
+		bool is_nullable = r.key_def->parts[i].nullable_action ==
+				   ON_CONFLICT_ACTION_NONE;
+		if (field_mp_plain_type_is_compatible(field_type, mp_type,
+						      is_nullable))
+			continue;
+		if (!sql_type_is_numeric(field_type) ||
+		    !sql_mp_type_is_numeric(mp_type)) {
+			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+				 mem_type_to_str(mem),
+				 field_type_strs[field_type]);
+			goto abort_due_to_error;
+		}
+		bool is_none = false;
+		if (sql_prepare_rec_for_comp(&r, i, eqOnly, &is_none) != 0)
+			goto abort_due_to_error;
+		oc = r.opcode;
+		if (is_none) {
+			res = 1;
+			goto seek_not_found;
+		}
+	}
+
 	if (sqlCursorMovetoUnpacked(pC->uc.pCursor, &r, &res) != 0)
 		goto abort_due_to_error;
 	if (eqOnly && r.eqSeen==0) {
@@ -4595,26 +4653,36 @@ case OP_IdxGE:  {       /* jump */
 	assert(pOp->p4type==P4_INT32);
 	r.key_def = pC->key_def;
 	r.nField = (u16)pOp->p4.i;
-	if (pOp->opcode<OP_IdxLT) {
-		assert(pOp->opcode==OP_IdxLE || pOp->opcode==OP_IdxGT);
+	r.aMem = &aMem[pOp->p3];
+	for (int i = 0; i < r.nField; ++i) {
+		struct Mem *mem = &r.aMem[i];
+		enum mp_type mp_type = sql_value_type(mem);
+		enum field_type field_type = r.key_def->parts[i].type;
+		if (field_type == FIELD_TYPE_SCALAR ||
+		    mem->field_type == FIELD_TYPE_SCALAR)
+			continue;
+		bool is_nullable = r.key_def->parts[i].nullable_action ==
+				   ON_CONFLICT_ACTION_NONE;
+		if (field_mp_plain_type_is_compatible(field_type, mp_type,
+						      is_nullable))
+			continue;
+		if (!sql_type_is_numeric(field_type) ||
+		    !sql_mp_type_is_numeric(mp_type)) {
+			diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+				 mem_type_to_str(mem),
+				 field_type_strs[field_type]);
+			goto abort_due_to_error;
+		}
+	}
+	if (pOp->opcode == OP_IdxLE || pOp->opcode == OP_IdxGT)
 		r.default_rc = -1;
-	} else {
-		assert(pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxLT);
+	else
 		r.default_rc = 0;
-	}
-	r.aMem = &aMem[pOp->p3];
-#ifdef SQL_DEBUG
-	{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
-#endif
-	int res =  tarantoolsqlIdxKeyCompare(pC->uc.pCursor, &r);
-	assert((OP_IdxLE&1)==(OP_IdxLT&1) && (OP_IdxGE&1)==(OP_IdxGT&1));
-	if ((pOp->opcode&1)==(OP_IdxLT&1)) {
-		assert(pOp->opcode==OP_IdxLE || pOp->opcode==OP_IdxLT);
+	int res = tarantoolsqlIdxKeyCompare(pC->uc.pCursor, &r);
+	if (pOp->opcode == OP_IdxLE || pOp->opcode == OP_IdxLT)
 		res = -res;
-	} else {
-		assert(pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxGT);
+	else
 		res++;
-	}
 	VdbeBranchTaken(res>0,2);
 	if (res>0) goto jump_to_p2;
 	break;
diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
index aad030d..2d10c21 100644
--- a/src/box/sql/vdbemem.c
+++ b/src/box/sql/vdbemem.c
@@ -670,6 +670,7 @@ sqlVdbeMemCast(Mem * pMem, enum field_type type)
 		return 0;
 	switch (type) {
 	case FIELD_TYPE_SCALAR:
+		pMem->field_type = FIELD_TYPE_SCALAR;
 		return 0;
 	case FIELD_TYPE_BOOLEAN:
 		if ((pMem->flags & MEM_Int) != 0) {
diff --git a/src/box/sql/where.c b/src/box/sql/where.c
index 7ec43e1..993b078 100644
--- a/src/box/sql/where.c
+++ b/src/box/sql/where.c
@@ -299,7 +299,9 @@ whereScanNext(WhereScan * pScan)
 							pX = pTerm->pExpr;
 							enum field_type expr_type =
 								expr_cmp_mutual_type(pX);
-							if (!field_type1_contains_type2(expr_type, pScan->idx_type))
+							if (!field_type1_contains_type2(expr_type, pScan->idx_type) &&
+							    !(sql_type_is_numeric(expr_type) &&
+							      sql_type_is_numeric(pScan->idx_type)))
 								continue;
 							if (pScan->is_column_seen) {
 								Parse *pParse =
diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
index 5bc27f1..725e2af 100644
--- a/src/box/sql/wherecode.c
+++ b/src/box/sql/wherecode.c
@@ -335,72 +335,6 @@ disableTerm(WhereLevel * pLevel, WhereTerm * pTerm)
 	}
 }
 
-/**
- * Code an OP_ApplyType opcode to apply the column type string
- * @types to the n registers starting at @base.
- *
- * As an optimization, SCALAR entries (which are no-ops) at the
- * beginning and end of @types are ignored.  If all entries in
- * @types are SCALAR, then no code gets generated.
- *
- * This routine makes its own copy of @types so that the caller is
- * free to modify @types after this routine returns.
- */
-static void
-emit_apply_type(Parse *pParse, int base, int n, enum field_type *types)
-{
-	Vdbe *v = pParse->pVdbe;
-	if (types == NULL) {
-		assert(pParse->db->mallocFailed);
-		return;
-	}
-	assert(v != 0);
-
-	/*
-	 * Adjust base and n to skip over SCALAR entries at the
-	 * beginning and end of the type sequence.
-	 */
-	while (n > 0 && types[0] == FIELD_TYPE_SCALAR) {
-		n--;
-		base++;
-		types++;
-	}
-	while (n > 1 && types[n - 1] == FIELD_TYPE_SCALAR) {
-		n--;
-	}
-
-	if (n > 0) {
-		enum field_type *types_dup = field_type_sequence_dup(pParse,
-								     types, n);
-		sqlVdbeAddOp4(v, OP_ApplyType, base, n, 0,
-				  (char *) types_dup, P4_DYNAMIC);
-		sql_expr_type_cache_change(pParse, base, n);
-	}
-}
-
-/**
- * Expression @rhs, which is the RHS of a comparison operation, is
- * either a vector of n elements or, if n==1, a scalar expression.
- * Before the comparison operation, types @types are to be applied
- * to the @rhs values. This function modifies entries within the
- * field sequence to SCALAR if either:
- *
- *   * the comparison will be performed with no type, or
- *   * the type change in @types is guaranteed not to change the value.
- */
-static void
-expr_cmp_update_rhs_type(struct Expr *rhs, int n, enum field_type *types)
-{
-	for (int i = 0; i < n; i++) {
-		Expr *p = sqlVectorFieldSubexpr(rhs, i);
-		enum field_type expr_type = sql_expr_type(p);
-		if (sql_type_result(expr_type, types[i]) == FIELD_TYPE_SCALAR ||
-		    sql_expr_needs_no_type_change(p, types[i])) {
-			types[i] = FIELD_TYPE_SCALAR;
-		}
-	}
-}
-
 /*
  * Generate code for a single equality term of the WHERE clause.  An equality
  * term can be either X=expr or X IN (...).   pTerm is the term to be
@@ -644,8 +578,7 @@ static int
 codeAllEqualityTerms(Parse * pParse,	/* Parsing context */
 		     WhereLevel * pLevel,	/* Which nested loop of the FROM we are coding */
 		     int bRev,		/* Reverse the order of IN operators */
-		     int nExtraReg,	/* Number of extra registers to allocate */
-		     enum field_type **res_type)
+		     int nExtraReg)	/* Number of extra registers to allocate */
 {
 	u16 nEq;		/* The number of == or IN constraints to code */
 	u16 nSkip;		/* Number of left-most columns to skip */
@@ -669,9 +602,6 @@ codeAllEqualityTerms(Parse * pParse,	/* Parsing context */
 	nReg = pLoop->nEq + nExtraReg;
 	pParse->nMem += nReg;
 
-	enum field_type *type = sql_index_type_str(pParse->db, idx_def);
-	assert(type != NULL || pParse->db->mallocFailed);
-
 	if (nSkip) {
 		int iIdxCur = pLevel->iIdxCur;
 		sqlVdbeAddOp1(v, (bRev ? OP_Last : OP_Rewind), iIdxCur);
@@ -714,17 +644,8 @@ codeAllEqualityTerms(Parse * pParse,	/* Parsing context */
 				sqlVdbeAddOp2(v, OP_SCopy, r1, regBase + j);
 			}
 		}
-		if (pTerm->eOperator & WO_IN) {
-			if (pTerm->pExpr->flags & EP_xIsSelect) {
-				/* No type ever needs to be (or should be) applied to a value
-				 * from the RHS of an "? IN (SELECT ...)" expression. The
-				 * sqlFindInIndex() routine has already ensured that the
-				 * type of the comparison has been applied to the value.
-				 */
-				if (type != NULL)
-					type[j] = FIELD_TYPE_SCALAR;
-			}
-		} else if ((pTerm->eOperator & WO_ISNULL) == 0) {
+		if (!(pTerm->eOperator & WO_IN) &&
+		    (pTerm->eOperator & WO_ISNULL) == 0) {
 			Expr *pRight = pTerm->pExpr->pRight;
 			if (sqlExprCanBeNull(pRight)) {
 				sqlVdbeAddOp2(v, OP_IsNull, regBase + j,
@@ -733,7 +654,6 @@ codeAllEqualityTerms(Parse * pParse,	/* Parsing context */
 			}
 		}
 	}
-	*res_type = type;
 	return regBase;
 }
 
@@ -904,10 +824,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo,	/* Complete information about the W
 		int iIdxCur;	/* The VDBE cursor for the index */
 		int nExtraReg = 0;	/* Number of extra registers needed */
 		int op;		/* Instruction opcode */
-		/* Types for start of range constraint. */
-		enum field_type *start_types;
-		/* Types for end of range constraint */
-		enum field_type *end_types = NULL;
 		u8 bSeekPastNull = 0;	/* True to seek past initial nulls */
 		u8 bStopAtNull = 0;	/* Add condition to terminate at NULLs */
 		int force_integer_reg = -1;  /* If non-negative: number of
@@ -994,17 +910,7 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo,	/* Complete information about the W
 		 * and store the values of those terms in an array of registers
 		 * starting at regBase.
 		 */
-		regBase =
-		    codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg,
-					 &start_types);
-		if (start_types != NULL && nTop) {
-			uint32_t len = 0;
-			for (enum field_type *tmp = &start_types[nEq];
-			     *tmp != field_type_MAX; tmp++, len++);
-			uint32_t sz = len * sizeof(enum field_type);
-			end_types = sqlDbMallocRaw(db, sz);
-			memcpy(end_types, &start_types[nEq], sz);
-		}
+		regBase = codeAllEqualityTerms(pParse, pLevel, bRev, nExtraReg);
 		addrNxt = pLevel->addrNxt;
 
 		testcase(pRangeStart && (pRangeStart->eOperator & WO_LE) != 0);
@@ -1029,10 +935,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo,	/* Complete information about the W
 				VdbeCoverage(v);
 			}
 
-			if (start_types) {
-				expr_cmp_update_rhs_type(pRight, nBtm,
-							 &start_types[nEq]);
-			}
 			nConstraint += nBtm;
 			testcase(pRangeStart->wtFlags & TERM_VIRTUAL);
 			if (sqlExprIsVector(pRight) == 0) {
@@ -1049,91 +951,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo,	/* Complete information about the W
 		}
 		struct index_def *idx_pk = space->index[0]->def;
 		uint32_t pk_part_count = idx_pk->key_def->part_count;
-		/*
-		 * Tarantool's iterator over integer fields doesn't
-		 * tolerate floating point values. Hence, if term
-		 * is equality comparison and value of operand is
-		 * not integer, we can skip it since it always
-		 * results in false: INT a == 0.5 -> false;
-		 * It is done using OP_MustBeInt facilities.
-		 * In case term is greater comparison (a > ?), we
-		 * should notify OP_SeekGT to process truncation of
-		 * floating point value: a > 0.5 -> a >= 1;
-		 * It is done by setting P5 flag for OP_Seek*.
-		 * It is worth mentioning that we do not need
-		 * this step when it comes for less (<) comparison
-		 * of nullable field. Key is NULL in this case:
-		 * values are ordered as  NULL, ... NULL, min_value,
-		 * so to fetch min value we pass NULL to GT iterator.
-		 * The only exception is less comparison in
-		 * conjunction with ORDER BY DESC clause:
-		 * in such situation we use LE iterator and
-		 * truncated value to compare. But then
-		 * pRangeStart == NULL.
-		 * This procedure is correct for compound index:
-		 * only one comparison of less/greater type can be
-		 * used at the same time. For instance,
-		 * a < 1.5 AND b > 0.5 is handled by SeekGT using
-		 * column a and fetching column b from tuple and
-		 * OP_Le comparison.
-		 *
-		 * Note that OP_ApplyType, which is emitted before
-		 * OP_Seek** doesn't truncate floating point to
-		 * integer. That's why we need this routine.
-		 * Also, note that terms are separated by OR
-		 * predicates, so we consider term as sequence
-		 * of AND'ed predicates.
-		 */
-		size_t addrs_sz = sizeof(int) * nEq;
-		int *seek_addrs = region_alloc(&pParse->region, addrs_sz);
-		if (seek_addrs == NULL) {
-			diag_set(OutOfMemory, addrs_sz, "region", "seek_addrs");
-			pParse->is_aborted = true;
-			return 0;
-		}
-		memset(seek_addrs, 0, addrs_sz);
-		for (int i = 0; i < nEq; i++) {
-			enum field_type type = idx_def->key_def->parts[i].type;
-			if (type == FIELD_TYPE_INTEGER ||
-			    type == FIELD_TYPE_UNSIGNED) {
-				/*
-				 * OP_MustBeInt consider NULLs as
-				 * non-integer values, so firstly
-				 * check whether value is NULL or not.
-				 */
-				seek_addrs[i] = sqlVdbeAddOp1(v, OP_IsNull,
-							      regBase);
-				sqlVdbeAddOp2(v, OP_MustBeInt, regBase + i,
-					      addrNxt);
-				start_types[i] = FIELD_TYPE_SCALAR;
-				/*
-				 * We need to notify column cache
-				 * that type of value may change
-				 * so we should fetch value from
-				 * tuple again rather then copy
-				 * from register.
-				 */
-				sql_expr_type_cache_change(pParse, regBase + i,
-							   1);
-			}
-		}
-		/* Inequality constraint comes always at the end of list. */
-		part_count = idx_def->key_def->part_count;
-		if (pRangeStart != NULL) {
-			/*
-			 * nEq == 0 means that filter condition
-			 * contains only inequality.
-			 */
-			uint32_t ineq_idx = nEq == 0 ? 0 : nEq - 1;
-			assert(ineq_idx < part_count);
-			enum field_type ineq_type =
-				idx_def->key_def->parts[ineq_idx].type;
-			if (ineq_type == FIELD_TYPE_INTEGER ||
-			    ineq_type == FIELD_TYPE_UNSIGNED)
-				force_integer_reg = regBase + nEq;
-		}
-		emit_apply_type(pParse, regBase, nConstraint - bSeekPastNull,
-				start_types);
 		if (pLoop->nSkip > 0 && nConstraint == pLoop->nSkip) {
 			/* The skip-scan logic inside the call to codeAllEqualityConstraints()
 			 * above has already left the cursor sitting on the correct row,
@@ -1143,10 +960,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo,	/* Complete information about the W
 			op = aStartOp[(start_constraints << 2) +
 				      (startEq << 1) + bRev];
 			assert(op != 0);
-			for (uint32_t i = 0; i < nEq; ++i) {
-				if (seek_addrs[i] != 0)
-					sqlVdbeJumpHere(v, seek_addrs[i]);
-			}
 			sqlVdbeAddOp4Int(v, op, iIdxCur, addrNxt, regBase,
 					     nConstraint);
 			/* If this is Seek* opcode, and IPK is detected in the
@@ -1186,13 +999,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo,	/* Complete information about the W
 						  addrNxt);
 				VdbeCoverage(v);
 			}
-			if (end_types) {
-				expr_cmp_update_rhs_type(pRight, nTop, end_types);
-				emit_apply_type(pParse, regBase + nEq, nTop,
-						end_types);
-			} else {
-				assert(pParse->db->mallocFailed);
-			}
 			nConstraint += nTop;
 			testcase(pRangeEnd->wtFlags & TERM_VIRTUAL);
 
@@ -1206,8 +1012,6 @@ sqlWhereCodeOneLoopStart(WhereInfo * pWInfo,	/* Complete information about the W
 			endEq = 0;
 			nConstraint++;
 		}
-		sqlDbFree(db, start_types);
-		sqlDbFree(db, end_types);
 
 		/* Top of the loop body */
 		pLevel->p2 = sqlVdbeCurrentAddr(v);
diff --git a/src/box/tuple_compare.cc b/src/box/tuple_compare.cc
index 3f8a0ce..6cbf555 100644
--- a/src/box/tuple_compare.cc
+++ b/src/box/tuple_compare.cc
@@ -104,6 +104,12 @@ mp_classof(enum mp_type type)
 	return mp_classes[type];
 }
 
+int
+mp_type_classes_comp(enum mp_type ltype, enum mp_type rtype)
+{
+	return mp_classof(ltype) - mp_classof(rtype);
+}
+
 static enum mp_class
 mp_extension_class(const char *data)
 {
diff --git a/src/box/tuple_compare.h b/src/box/tuple_compare.h
index 8614f23..0c1e4c6 100644
--- a/src/box/tuple_compare.h
+++ b/src/box/tuple_compare.h
@@ -31,6 +31,7 @@
  * SUCH DAMAGE.
  */
 #include <stdint.h>
+#include <msgpuck.h>
 
 #if defined(__cplusplus)
 extern "C" {
@@ -76,6 +77,14 @@ typedef uint64_t hint_t;
 void
 key_def_set_compare_func(struct key_def *def);
 
+/**
+ * Return an integer less than, equal to, or greater than zero if
+ * mp_class of ltype is found, respectively, to be less than, to
+ * match, or be greater than mp_class of rtype.
+ */
+int
+mp_type_classes_comp(enum mp_type ltype, enum mp_type rtype);
+
 #if defined(__cplusplus)
 } /* extern "C" */
 #endif /* defined(__cplusplus) */
diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua
index 2a00626..242645f 100755
--- a/test/sql-tap/identifier_case.test.lua
+++ b/test/sql-tap/identifier_case.test.lua
@@ -242,11 +242,11 @@ data = {
     { 2,  [[ 'a' < 'b' collate "binary" ]], {0, {true}}},
     { 3,  [[ 'a' < 'b' collate 'binary' ]], {1, [[Syntax error at line 1 near ''binary'']]}},
     { 4,  [[ 'a' < 'b' collate "unicode" ]], {0, {true}}},
-    { 5,  [[ 5 < 'b' collate "unicode" ]], {0, {true}}},
+    { 5,  [[ 5 < 'b' collate "unicode" ]], {1, "Type mismatch: can not convert text to unsigned"}},
     { 6,  [[ 5 < 'b' collate unicode ]], {1,"Collation 'UNICODE' does not exist"}},
-    { 7,  [[ 5 < 'b' collate "unicode_ci" ]], {0, {true}}},
+    { 7,  [[ 5 < 'b' collate "unicode_ci" ]], {1, "Type mismatch: can not convert text to unsigned"}},
     { 8,  [[ 5 < 'b' collate NONE ]], {1, "Collation 'NONE' does not exist"}},
-    { 9,  [[ 5 < 'b' collate "none" ]], {0, {true}}},
+    { 9,  [[ 5 < 'b' collate "none" ]], {1, "Type mismatch: can not convert text to unsigned"}},
 }
 
 for _, row in ipairs(data) do
diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua
index 570cc17..710dd80 100755
--- a/test/sql-tap/in1.test.lua
+++ b/test/sql-tap/in1.test.lua
@@ -633,16 +633,12 @@ test:do_execsql_test(
         -- </in-11.1>
     })
 
-test:do_test(
-    "in-11.2",
-    function()
-        -- The '2' should be coerced into 2 because t6.b is NUMERIC
-        return test:execsql [[
-            SELECT * FROM t6 WHERE b IN ('2');
-        ]]
-    end, {
+test:do_catchsql_test(
+    "in-11.2",[[
+        SELECT * FROM t6 WHERE b IN ('2');
+    ]], {
         -- <in-11.2>
-        1, 2
+        1, "Type mismatch: can not convert text to unsigned"
         -- </in-11.2>
     })
 
diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua
index 8c69173..4d400ce 100755
--- a/test/sql-tap/in4.test.lua
+++ b/test/sql-tap/in4.test.lua
@@ -147,13 +147,13 @@ test:do_execsql_test(
         -- </in4-2.7>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-2.8",
     [[
         SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') 
     ]], {
         -- <in4-2.8>
-        "two"
+        1, "Type mismatch: can not convert text to integer"
         -- </in4-2.8>
     })
 
@@ -585,7 +585,7 @@ test:do_execsql_test(
         -- </in4-4.6>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-4.11",
     [[
         CREATE TABLE t4b(a TEXT, b NUMBER, c  INT PRIMARY KEY);
@@ -593,87 +593,87 @@ test:do_execsql_test(
         SELECT c FROM t4b WHERE a=b;
     ]], {
         -- <in4-4.11>
-        4
+        1, "Type mismatch: can not convert unsigned to text"
         -- </in4-4.11>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-4.12",
     [[
         SELECT c FROM t4b WHERE b=a;
     ]], {
         -- <in4-4.12>
-        4
+        1, "Type mismatch: can not convert text to unsigned"
         -- </in4-4.12>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-4.13",
     [[
         SELECT c FROM t4b WHERE +a=b;
     ]], {
         -- <in4-4.13>
-        4
+        1, "Type mismatch: can not convert text to unsigned"
         -- </in4-4.13>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-4.14",
     [[
         SELECT c FROM t4b WHERE a=+b;
     ]], {
         -- <in4-4.14>
-        4
+        1, "Type mismatch: can not convert unsigned to text"
         -- </in4-4.14>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-4.15",
     [[
         SELECT c FROM t4b WHERE +b=a;
     ]], {
         -- <in4-4.15>
-        4
+        1, "Type mismatch: can not convert unsigned to text"
         -- </in4-4.15>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-4.16",
     [[
         SELECT c FROM t4b WHERE b=+a;
     ]], {
         -- <in4-4.16>
-        4
+        1, "Type mismatch: can not convert text to unsigned"
         -- </in4-4.16>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-4.17",
     [[
         SELECT c FROM t4b WHERE a IN (b);
     ]], {
         -- <in4-4.17>
-        4
+        1, "Type mismatch: can not convert unsigned to text"
         -- </in4-4.17>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-4.18",
     [[
         SELECT c FROM t4b WHERE b IN (a);
     ]], {
         -- <in4-4.18>
-        4
+        1, "Type mismatch: can not convert text to unsigned"
         -- </in4-4.18>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "in4-4.19",
     [[
         SELECT c FROM t4b WHERE +b IN (a);
     ]], {
         -- <in4-4.19>
-        4
+        1, "Type mismatch: can not convert unsigned to text"
         -- </in4-4.19>
     })
 
diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua
index e173e68..85988c9 100755
--- a/test/sql-tap/index1.test.lua
+++ b/test/sql-tap/index1.test.lua
@@ -785,7 +785,7 @@ test:do_catchsql_test(
         SELECT c FROM t6 WHERE a>123;
     ]], {
         -- <index-14.6>
-        1, "Type mismatch: can not convert  to numeric"
+        1, "Type mismatch: can not convert unsigned to text"
         -- </index-14.6>
     })
 
@@ -795,7 +795,7 @@ test:do_catchsql_test(
         SELECT c FROM t6 WHERE a>=123;
     ]], {
         -- <index-14.7>
-        1, "Type mismatch: can not convert  to numeric"
+        1, "Type mismatch: can not convert unsigned to text"
         -- </index-14.7>
     })
 
diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua
index 43bb066..ff8f6de 100755
--- a/test/sql-tap/insert3.test.lua
+++ b/test/sql-tap/insert3.test.lua
@@ -59,7 +59,7 @@ test:do_execsql_test(
     [[
             CREATE TABLE log2(rowid INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE,y INT );
             CREATE TRIGGER r2 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
-              UPDATE log2 SET y=y+1 WHERE x=new.b;
+              UPDATE log2 SET y=y+1 WHERE x=CAST(new.b AS STRING);
               INSERT OR IGNORE INTO log2(x, y) VALUES(new.b,1);
             END;
             INSERT INTO t1(a, b) VALUES('hi', 453);
diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
index bec2670..71739f7 100755
--- a/test/sql-tap/intpkey.test.lua
+++ b/test/sql-tap/intpkey.test.lua
@@ -859,30 +859,30 @@ test:do_execsql_test(
         -- </intpkey-14.2>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "intpkey-14.3",
     [[
         SELECT * FROM t3 WHERE a>'2';
     ]], {
         -- <intpkey-14.3>
-        3, 3, "3"
+        1, "Type mismatch: can not convert text to integer"
         -- </intpkey-14.3>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "intpkey-14.4",
     [[
         SELECT * FROM t3 WHERE a<'2';
     ]], {
         -- <intpkey-14.4>
-        1, 1, "one"
+        1, "Type mismatch: can not convert text to integer"
         -- </intpkey-14.4>
     })
 
 test:do_execsql_test(
     "intpkey-14.5",
     [[
-        SELECT * FROM t3 WHERE a<c;
+        SELECT * FROM t3 WHERE CAST(a AS TEXT)<c;
     ]], {
         -- <intpkey-14.5>
         1, 1, "one"
@@ -892,7 +892,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "intpkey-14.6",
     [[
-        SELECT * FROM t3 WHERE a=c;
+        SELECT * FROM t3 WHERE CAST(a AS TEXT)=c;
     ]], {
         -- <intpkey-14.6>
         2, 2, "2", 3, 3, "3"
diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua
index 4f014e0..577db7a 100755
--- a/test/sql-tap/join.test.lua
+++ b/test/sql-tap/join.test.lua
@@ -1028,23 +1028,23 @@ test:do_test(
         -- </join-11.8>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "join-11.9",
     [[
         SELECT * FROM t1 NATURAL JOIN t2 
     ]], {
         -- <join-11.9>
-        "one", "1.0", "two", "2"
+        1, "Type mismatch: can not convert text to integer"
         -- </join-11.9>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "join-11.10",
     [[
         SELECT * FROM t2 NATURAL JOIN t1 
     ]], {
         -- <join-11.10>
-        1, "one", 2, "two"
+        1, "Type mismatch: can not convert text to unsigned"
         -- </join-11.10>
     })
 
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index 32f38cc..4f83bc1 100755
--- a/test/sql-tap/misc1.test.lua
+++ b/test/sql-tap/misc1.test.lua
@@ -88,7 +88,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "misc1-1.4",
     [[
-        SELECT x75 FROM manycol WHERE x50=350
+        SELECT x75 FROM manycol WHERE x50='350'
     ]], {
         -- <misc1-1.4>
         "375"
@@ -98,7 +98,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "misc1-1.5",
     [[
-        SELECT x50 FROM manycol WHERE x99=599
+        SELECT x50 FROM manycol WHERE x99='599'
     ]], {
         -- <misc1-1.5>
         "550"
@@ -109,7 +109,7 @@ test:do_test(
     "misc1-1.6",
     function()
         test:execsql("CREATE INDEX manycol_idx1 ON manycol(x99)")
-        return test:execsql("SELECT x50 FROM manycol WHERE x99=899")
+        return test:execsql("SELECT x50 FROM manycol WHERE x99='899'")
     end, {
         -- <misc1-1.6>
         "850"
@@ -129,7 +129,7 @@ test:do_execsql_test(
 test:do_test(
     "misc1-1.8",
     function()
-        test:execsql("DELETE FROM manycol WHERE x98=1234")
+        test:execsql("DELETE FROM manycol WHERE x98='1234'")
         return test:execsql("SELECT count(*) FROM manycol")
     end, {
         -- <misc1-1.8>
@@ -140,7 +140,7 @@ test:do_test(
 test:do_test(
     "misc1-1.9",
     function()
-        test:execsql("DELETE FROM manycol WHERE x98=998")
+        test:execsql("DELETE FROM manycol WHERE x98='998'")
         return test:execsql("SELECT count(*) FROM manycol")
     end, {
         -- <misc1-1.9>
@@ -151,7 +151,7 @@ test:do_test(
 test:do_test(
     "misc1-1.10",
     function()
-        test:execsql("DELETE FROM manycol WHERE x99=500")
+        test:execsql("DELETE FROM manycol WHERE x99='500'")
         return test:execsql("SELECT count(*) FROM manycol")
     end, {
         -- <misc1-1.10>
@@ -162,7 +162,7 @@ test:do_test(
 test:do_test(
     "misc1-1.11",
     function()
-        test:execsql("DELETE FROM manycol WHERE x99=599")
+        test:execsql("DELETE FROM manycol WHERE x99='599'")
         return test:execsql("SELECT count(*) FROM manycol")
     end, {
         -- <misc1-1.11>
@@ -479,9 +479,9 @@ local where = ""
 test:do_test(
     "misc1-10.1",
     function()
-        where = "WHERE x0>=0"
+        where = "WHERE x0>='0'"
         for i = 1, 99, 1 do
-            where = where .. " AND x"..i.."<>0"
+            where = where .. " AND x"..i.."<>'0'"
         end
         return test:catchsql("SELECT count(*) FROM manycol "..where.."")
     end, {
@@ -496,7 +496,7 @@ test:do_test(
 test:do_test(
     "misc1-10.3",
     function()
-        where = string.gsub(where,"x0>=0", "x0=0")
+        where = string.gsub(where,"x0>='0'", "x0='0'")
         return test:catchsql("DELETE FROM manycol "..where.."")
     end, {
         -- <misc1-10.3>
@@ -520,7 +520,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "misc1-10.6",
     [[
-        SELECT x1 FROM manycol WHERE x0=100
+        SELECT x1 FROM manycol WHERE x0='100'
     ]], {
         -- <misc1-10.6>
         "101"
@@ -530,7 +530,7 @@ test:do_execsql_test(
 test:do_test(
     "misc1-10.7",
     function()
-        where = string.gsub(where, "x0=0", "x0=100")
+        where = string.gsub(where, "x0='0'", "x0='100'")
         return test:catchsql("UPDATE manycol SET x1=x1+1 "..where.."")
     end, {
         -- <misc1-10.7>
@@ -541,7 +541,7 @@ test:do_test(
 test:do_execsql_test(
     "misc1-10.8",
     [[
-        SELECT x1 FROM manycol WHERE x0=100
+        SELECT x1 FROM manycol WHERE x0='100'
     ]], {
         -- <misc1-10.8>
         "102"
@@ -563,7 +563,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "misc1-10.10",
     [[
-        SELECT x1 FROM manycol WHERE x0=100
+        SELECT x1 FROM manycol WHERE x0='100'
     ]], {
         -- <misc1-10.10>
         "103"
@@ -619,13 +619,13 @@ test:do_execsql_test(
         -- </misc1-12.1>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "misc1-12.2",
     [[
         SELECT '0'==0.0
     ]], {
         -- <misc1-12.2>
-        true
+        1, "Type mismatch: can not convert real to text"
         -- </misc1-12.2>
     })
 
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index fbebfab..ed01c26 100755
--- a/test/sql-tap/select1.test.lua
+++ b/test/sql-tap/select1.test.lua
@@ -320,7 +320,7 @@ test:do_catchsql_test(
         SELECT count(*),count(a),count(b) FROM t4 WHERE b=5
     ]], {
         -- <select1-2.5.3>
-        1, "Type mismatch: can not convert This is a string that is too big to fit inside a NBFS buffer to numeric"
+        1, "Type mismatch: can not convert unsigned to text"
         -- </select1-2.5.3>
     })
 
@@ -1909,23 +1909,23 @@ test:do_execsql_test(
 
 
 -- ifcapable compound
-test:do_execsql_test(
+test:do_catchsql_test(
         "select1-12.7",
         [[
             SELECT * FROM t3 WHERE a=(SELECT 1);
         ]], {
             -- <select1-12.7>
-            0, "1", "2"
+            1, "Type mismatch: can not convert unsigned to text"
             -- </select1-12.7>
         })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "select1-12.8",
     [[
         SELECT * FROM t3 WHERE a=(SELECT 2);
     ]], {
         -- <select1-12.8>
-
+        1, "Type mismatch: can not convert unsigned to text"
         -- </select1-12.8>
     })
 
diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
index fec5d7a..167ce05 100755
--- a/test/sql-tap/select7.test.lua
+++ b/test/sql-tap/select7.test.lua
@@ -256,7 +256,7 @@ test:do_execsql_test(
         DROP TABLE IF EXISTS t5;
         CREATE TABLE t5(a TEXT primary key, b INT);
         INSERT INTO t5 VALUES(123, 456);
-        SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;
+        SELECT typeof(a), a FROM t5 GROUP BY a HAVING a < CAST(b AS STRING);
     ]], {
         -- <select7-7.7>
         "string", "123"
diff --git a/test/sql-tap/sql-errors.test.lua b/test/sql-tap/sql-errors.test.lua
index 5ec94bf..8ea6f8b 100755
--- a/test/sql-tap/sql-errors.test.lua
+++ b/test/sql-tap/sql-errors.test.lua
@@ -765,7 +765,7 @@ test:do_catchsql_test(
 		SELECT X'ff' >= false;
 	]], {
 		-- <sql-errors-2.8>
-		1, "Type mismatch: can not convert varbinary to boolean"
+		1, "Type mismatch: can not convert boolean to varbinary"
 		-- </sql-errors-2.8>
 	})
 
@@ -775,7 +775,7 @@ test:do_catchsql_test(
 		SELECT X'ff' <= false;
 	]], {
 		-- <sql-errors-2.9>
-		1, "Type mismatch: can not convert varbinary to boolean"
+		1, "Type mismatch: can not convert boolean to varbinary"
 		-- </sql-errors-2.9>
 	})
 
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index 6bedf58..4a59b01 100755
--- a/test/sql-tap/subquery.test.lua
+++ b/test/sql-tap/subquery.test.lua
@@ -284,13 +284,13 @@ test:do_execsql_test(
         -- </subquery-2.3.1>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "subquery-2.3.2",
     [[
         SELECT a IN (10.0, 20) FROM t3;
     ]], {
         -- <subquery-2.3.2>
-        false
+        1, "Type mismatch: can not convert text to real"
         -- </subquery-2.3.2>
     })
 
diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua
index db0881c..e80e7c6 100755
--- a/test/sql-tap/tkt-9a8b09f8e6.test.lua
+++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua
@@ -83,23 +83,23 @@ test:do_execsql_test(
         -- </1.5>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     2.1,
     [[
         SELECT x FROM t1 WHERE x IN (1);
     ]], {
         -- <2.1>
-        "1"
+        1, "Type mismatch: can not convert unsigned to text"
         -- </2.1>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     2.2,
     [[
         SELECT x FROM t1 WHERE x IN (1.0);
     ]], {
         -- <2.2>
-        "1"
+        1, "Type mismatch: can not convert real to text"
         -- </2.2>
     })
 
@@ -123,23 +123,23 @@ test:do_execsql_test(
         -- </2.4>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     2.5,
     [[
         SELECT x FROM t1 WHERE 1 IN (x);
     ]], {
         -- <2.5>
-        "1"
+        1, "Type mismatch: can not convert unsigned to text"
         -- </2.5>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     2.6,
     [[
         SELECT x FROM t1 WHERE 1.0 IN (x);
     ]], {
         -- <2.6>
-        "1"
+        1, "Type mismatch: can not convert real to text"
         -- </2.6>
     })
 
@@ -183,23 +183,23 @@ test:do_execsql_test(
         -- </3.2>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     3.3,
     [[
         SELECT x FROM t2 WHERE x IN ('1');
     ]], {
         -- <3.3>
-        1
+        1, "Type mismatch: can not convert text to integer"
         -- </3.3>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     3.4,
     [[
         SELECT x FROM t2 WHERE x IN ('1.0');
     ]], {
         -- <3.4>
-        1
+        1, "Type mismatch: can not convert text to integer"
         -- </3.4>
     })
 
@@ -223,23 +223,23 @@ test:do_execsql_test(
         -- </3.6>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     3.7,
     [[
         SELECT x FROM t2 WHERE '1' IN (x);
     ]], {
         -- <3.7>
-        1
+        1, "Type mismatch: can not convert text to integer"
         -- </3.7>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     3.8,
     [[
         SELECT x FROM t2 WHERE '1.0' IN (x);
     ]], {
         -- <3.8>
-        1
+        1, "Type mismatch: can not convert text to integer"
         -- </3.8>
     })
 
@@ -263,23 +263,23 @@ test:do_execsql_test(
         -- </4.2>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     4.3,
     [[
         SELECT x FROM t3 WHERE x IN ('1');
     ]], {
         -- <4.3>
-        1.0
+        1, "Type mismatch: can not convert text to number"
         -- </4.3>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     4.4,
     [[
         SELECT x FROM t3 WHERE x IN ('1.0');
     ]], {
         -- <4.4>
-        1.0
+        1, "Type mismatch: can not convert text to number"
         -- </4.4>
     })
 
@@ -303,23 +303,23 @@ test:do_execsql_test(
         -- </4.6>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     4.7,
     [[
         SELECT x FROM t3 WHERE '1' IN (x);
     ]], {
         -- <4.7>
-        1
+        1, "Type mismatch: can not convert text to number"
         -- </4.7>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     4.8,
     [[
         SELECT x FROM t3 WHERE '1.0' IN (x);
     ]], {
         -- <4.8>
-        1
+        1, "Type mismatch: can not convert text to number"
         -- </4.8>
     })
 
@@ -343,23 +343,23 @@ test:do_execsql_test(
         -- </5.2>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     5.3,
     [[
         SELECT x FROM t4 WHERE x IN ('1');
     ]], {
         -- <5.3>
-        
+        1, "Type mismatch: can not convert text to number"
         -- </5.3>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     5.4,
     [[
         SELECT x FROM t4 WHERE x IN ('1.0');
     ]], {
         -- <5.4>
-        
+        1, "Type mismatch: can not convert text to number"
         -- </5.4>
     })
 
@@ -373,13 +373,13 @@ test:do_execsql_test(
         -- </5.5>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     5.6,
     [[
         SELECT x FROM t4 WHERE x IN ('1.11');
     ]], {
         -- <5.6>
-        1.11
+        1, "Type mismatch: can not convert text to number"
         -- </5.6>
     })
 
@@ -403,23 +403,23 @@ test:do_execsql_test(
         -- </5.8>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     5.9,
     [[
         SELECT x FROM t4 WHERE '1' IN (x);
     ]], {
         -- <5.9>
-        
+        1, "Type mismatch: can not convert text to number"
         -- </5.9>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     5.10,
     [[
         SELECT x FROM t4 WHERE '1.0' IN (x);
     ]], {
         -- <5.10>
-        
+        1, "Type mismatch: can not convert text to number"
         -- </5.10>
     })
 
@@ -433,13 +433,13 @@ test:do_execsql_test(
         -- </5.11>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     5.12,
     [[
         SELECT x FROM t4 WHERE '1.11' IN (x);
     ]], {
         -- <5.12>
-        1.11
+        1, "Type mismatch: can not convert text to number"
         -- </5.12>
     })
 
@@ -463,23 +463,23 @@ test:do_execsql_test(
         -- </6.2>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     6.3,
     [[
         SELECT x, y FROM t5 WHERE x IN ('1');
     ]], {
         -- <6.3>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
+        1, "Type mismatch: can not convert text to unsigned"
         -- </6.3>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     6.4,
     [[
         SELECT x, y FROM t5 WHERE x IN ('1.0');
     ]], {
         -- <6.4>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
+        1, "Type mismatch: can not convert text to unsigned"
         -- </6.4>
     })
 
@@ -503,23 +503,23 @@ test:do_execsql_test(
         -- </6.6>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     6.7,
     [[
         SELECT x, y FROM t5 WHERE '1' IN (x);
     ]], {
         -- <6.7>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
+        1, "Type mismatch: can not convert text to unsigned"
         -- </6.7>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     6.8,
     [[
         SELECT x, y FROM t5 WHERE '1.0' IN (x);
     ]], {
         -- <6.8>
-        1, "one", 1, "two", 1, "three", 1, "four"
+        1, "Type mismatch: can not convert text to unsigned"
         -- </6.8>
     })
 
diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua
index 82bdb52..6bf7884 100755
--- a/test/sql-tap/tkt-f973c7ac31.test.lua
+++ b/test/sql-tap/tkt-f973c7ac31.test.lua
@@ -20,7 +20,7 @@ test:plan(21)
 test:do_execsql_test(
     "tkt-f973c7ac3-1.0",
     [[
-        CREATE TABLE t(id INT primary key, c1 INTEGER, c2 INTEGER);
+        CREATE TABLE t(id INT primary key, c1 UNSIGNED, c2 UNSIGNED);
         INSERT INTO t VALUES(1, 5, 5);
         INSERT INTO t VALUES(2, 5, 4);
     ]], {
@@ -36,12 +36,12 @@ local sqls = {
 
 for tn, sql in ipairs(sqls) do
     test:execsql(sql)
-    test:do_execsql_test(
+    test:do_catchsql_test(
         "tkt-f973c7ac3-1."..tn..".1",
         [[
             SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 DESC 
         ]], {
-            
+            1,"Type mismatch: can not convert text to unsigned"
         })
 
     test:do_execsql_test(
@@ -52,36 +52,36 @@ for tn, sql in ipairs(sqls) do
             5, 5, 5, 4
         })
 
-    test:do_execsql_test(
+    test:do_catchsql_test(
         "tkt-f973c7ac3-1."..tn..".3",
         [[
             SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 DESC 
         ]], {
-            5, 5, 5, 4
+            1,"Type mismatch: can not convert text to unsigned"
         })
 
-    test:do_execsql_test(
+    test:do_catchsql_test(
         "tkt-f973c7ac3-1."..tn..".4",
         [[
             SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 DESC 
         ]], {
-            5, 5, 5, 4
+            1,"Type mismatch: can not convert text to unsigned"
         })
 
-    test:do_execsql_test(
+    test:do_catchsql_test(
         "tkt-f973c7ac3-1."..tn..".5",
         [[
             SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 DESC 
         ]], {
-            5, 5, 5, 4
+            1,"Type mismatch: can not convert text to unsigned"
         })
 
-    test:do_execsql_test(
+    test:do_catchsql_test(
         "tkt-f973c7ac3-1."..tn..".6",
         [[
             SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 ASC 
         ]], {
-            
+            1,"Type mismatch: can not convert text to unsigned"
         })
 
     test:do_execsql_test(
@@ -92,28 +92,28 @@ for tn, sql in ipairs(sqls) do
             5, 4, 5, 5
         })
 
-    test:do_execsql_test(
+    test:do_catchsql_test(
         "tkt-f973c7ac3-1."..tn..".8",
         [[
             SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 ASC 
         ]], {
-            5, 4, 5, 5
+            1,"Type mismatch: can not convert text to unsigned"
         })
 
-    test:do_execsql_test(
+    test:do_catchsql_test(
         "tkt-f973c7ac3-1."..tn..".9",
         [[
             SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 ASC 
         ]], {
-            5, 4, 5, 5
+            1,"Type mismatch: can not convert text to unsigned"
         })
 
-    test:do_execsql_test(
+    test:do_catchsql_test(
         "tkt-f973c7ac3-1."..tn..".10",
         [[
             SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC 
         ]], {
-            5, 4, 5, 5
+            1,"Type mismatch: can not convert text to unsigned"
         })
 
 end
diff --git a/test/sql-tap/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua
index fe5d620..8b99f8e 100755
--- a/test/sql-tap/tkt-fc7bd6358f.test.lua
+++ b/test/sql-tap/tkt-fc7bd6358f.test.lua
@@ -78,17 +78,17 @@ for a, from in ipairs(froms) do
         test:do_test(
             string.format("tkt-fc7bd6358f.110.%s.%s.1", a, b),
             function()
-                return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
+                return test:catchsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
             end, {
-                "12", 12, "12", "34", 34, "34"
+                1, "Type mismatch: can not convert text to integer"
             })
 
         test:do_test(
             string.format("tkt-fc7bd6358f.110.%s.%s.2", a, b),
             function()
-                return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
+                return test:catchsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where))
             end, {
-                "12", 12, "12", "34", 34, "34"
+                1, "Type mismatch: can not convert text to integer"
             })
 
     end
diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
index 7ceec47..e5d7b2f 100755
--- a/test/sql-tap/tkt3493.test.lua
+++ b/test/sql-tap/tkt3493.test.lua
@@ -45,7 +45,7 @@ test:do_execsql_test(
     [[
         SELECT 
           CASE 
-             WHEN B.val = 1 THEN 'XYZ' 
+             WHEN B.val = '1' THEN 'XYZ' 
              ELSE A.val 
           END AS Col1
         FROM B  
@@ -63,7 +63,7 @@ test:do_execsql_test(
     [[
         SELECT DISTINCT
           CASE 
-             WHEN B.val = 1 THEN 'XYZ' 
+             WHEN B.val = '1' THEN 'XYZ'
              ELSE A.val 
           END AS Col1
         FROM B  
@@ -79,7 +79,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-1.4",
     [[
-        SELECT b.val, CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 FROM b;
+        SELECT b.val, CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1 FROM b;
     ]], {
         -- <tkt3493-1.4>
         "1", "xyz", "2", "2"
@@ -91,7 +91,7 @@ test:do_execsql_test(
     [[
         SELECT DISTINCT 
           b.val, 
-          CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 
+          CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1
         FROM b;
     ]], {
         -- <tkt3493-1.5>
@@ -126,7 +126,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.2.1",
     [[
-        SELECT a=123 FROM t1 GROUP BY a 
+        SELECT a = '123' FROM t1 GROUP BY a;
     ]], {
         -- <tkt3493-2.2.1>
         true
@@ -136,7 +136,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.2.2",
     [[
-        SELECT a=123 FROM t1 
+        SELECT a = '123' FROM t1;
     ]], {
         -- <tkt3493-2.2.2>
         true
@@ -156,7 +156,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.2.4",
     [[
-        SELECT count(*), a=123 FROM t1 
+        SELECT count(*), a = '123' FROM t1;
     ]], {
         -- <tkt3493-2.2.4>
         1, true
@@ -166,7 +166,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.2.5",
     [[
-        SELECT count(*), +a=123 FROM t1 
+        SELECT count(*), +a = '123' FROM t1;
     ]], {
         -- <tkt3493-2.2.5>
         1, true
@@ -176,7 +176,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.3.3",
     [[
-        SELECT b='456' FROM t1 GROUP BY a 
+        SELECT b = 456 FROM t1 GROUP BY a;
     ]], {
         -- <tkt3493-2.3.3>
         true
@@ -186,7 +186,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.3.1",
     [[
-        SELECT b='456' FROM t1 GROUP BY b 
+        SELECT b = 456 FROM t1 GROUP BY b;
     ]], {
         -- <tkt3493-2.3.1>
         true
@@ -196,7 +196,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.3.2",
     [[
-        SELECT b='456' FROM t1 
+        SELECT b = 456 FROM t1;
     ]], {
         -- <tkt3493-2.3.2>
         true
@@ -206,7 +206,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.4.1",
     [[
-        SELECT typeof(a), a FROM t1 GROUP BY a HAVING a=123 
+        SELECT typeof(a), a FROM t1 GROUP BY a HAVING a = '123';
     ]], {
         -- <tkt3493-2.4.1>
         "string", "123"
@@ -216,7 +216,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.4.2",
     [[
-        SELECT typeof(a), a FROM t1 GROUP BY b HAVING a=123 
+        SELECT typeof(a), a FROM t1 GROUP BY b HAVING a = '123';
     ]], {
         -- <tkt3493-2.4.2>
         "string", "123"
@@ -226,7 +226,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.5.1",
     [[
-        SELECT typeof(b), b FROM t1 GROUP BY a HAVING b='456' 
+        SELECT typeof(b), b FROM t1 GROUP BY a HAVING b = 456;
     ]], {
         -- <tkt3493-2.5.1>
         "integer", 456
@@ -236,7 +236,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "tkt3493-2.5.2",
     [[
-        SELECT typeof(b), b FROM t1 GROUP BY b HAVING b='456' 
+        SELECT typeof(b), b FROM t1 GROUP BY b HAVING b = 456;
     ]], {
         -- <tkt3493-2.5.2>
         "integer", 456
diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua
index e960565..cc7e066 100755
--- a/test/sql-tap/transitive1.test.lua
+++ b/test/sql-tap/transitive1.test.lua
@@ -63,7 +63,7 @@ test:do_execsql_test(
         INSERT INTO t2 VALUES(2, 20,20,'20');
         INSERT INTO t2 VALUES(3, 3,3,'3');
 
-        SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c=20;
+        SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c='20';
     ]], {
         -- <transitive1-200>
         20, 20, "20"
@@ -73,7 +73,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "transitive1-210",
     [[
-        SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c>='20' ORDER BY +a;
+        SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c>='20' ORDER BY +a;
     ]], {
         -- <transitive1-210>
         3, 3, "3", 20, 20, "20"
@@ -83,7 +83,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "transitive1-220",
     [[
-        SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c<='20' ORDER BY +a;
+        SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c<='20' ORDER BY +a;
     ]], {
         -- <transitive1-220>
         20, 20, "20", 100, 100, "100"
@@ -338,7 +338,7 @@ test:do_execsql_test(
                    ON tvshow.idshow = episode.idshow
                  LEFT JOIN seasons
                         ON seasons.idshow = episode.idshow
-                           AND seasons.season = episode.c12
+                           AND seasons.season = CAST(episode.c12 AS INTEGER)
                  JOIN path
                    ON files.idpath = path.idpath
                  LEFT JOIN bookmark
@@ -378,7 +378,7 @@ test:do_execsql_test(
         FROM episodeview
             JOIN tvshowview ON tvshowview.idShow = episodeview.idShow
             JOIN seasons ON (seasons.idShow = tvshowview.idShow
-                             AND seasons.season = episodeview.c12)
+                             AND seasons.season = CAST(episodeview.c12 AS INTEGER))
             JOIN files ON files.idFile = episodeview.idFile
             JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow
             JOIN path ON path.idPath = tvshowlinkpath.idPath
@@ -402,7 +402,7 @@ test:do_execsql_test(
     [[
         CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT);
         INSERT INTO x VALUES(10, '10');
-        SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y;
+        SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND CAST(x.i AS STRING)=x.y;
     ]], {
         -- <transitive1-500>
         10, "10"
@@ -430,7 +430,7 @@ test:do_execsql_test(
     [[
         CREATE TABLE t3(i INTEGER PRIMARY KEY, t TEXT);
         INSERT INTO t3 VALUES(10, '10');
-        SELECT * FROM t3 WHERE i=t AND t = '10 ';
+        SELECT * FROM t3 WHERE CAST(i AS STRING)=t AND t = '10 ';
     ]], {
         -- <transitive1-520>
 
@@ -443,7 +443,7 @@ test:do_execsql_test(
         CREATE TABLE u1(x TEXT PRIMARY KEY, y INTEGER, z TEXT);
         CREATE INDEX i1 ON u1(x);
         INSERT INTO u1 VALUES('00013', 13, '013');
-        SELECT * FROM u1 WHERE x=y AND y=z AND z='013';
+        SELECT * FROM u1 WHERE CAST(x AS INTEGER)=y AND y=CAST(z AS INTEGER) AND z='013';
     ]], {
         -- <transitive1-530>
         "00013",13,"013"
diff --git a/test/sql-tap/where2.test.lua b/test/sql-tap/where2.test.lua
index f267be8..ca6b480 100755
--- a/test/sql-tap/where2.test.lua
+++ b/test/sql-tap/where2.test.lua
@@ -641,7 +641,7 @@ test:do_test(
     -- will attempt to convert to NUMERIC before the comparison.
     -- They will thus compare equal.
     --
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b;
+    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=b;
   ]])
         end, {
             -- <where2-6.7>
@@ -655,7 +655,7 @@ test:do_test(
             return queryplan([[
     -- The + operator doesn't affect RHS.
     --
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b;
+    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=+b;
   ]])
         end, {
             -- <where2-6.9>
@@ -668,7 +668,7 @@ test:do_test(
         function()
             -- The same thing but with the expression flipped around.
             return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a
+    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=CAST(a AS INTEGER)
   ]])
         end, {
             -- <where2-6.9.2>
@@ -680,7 +680,7 @@ test:do_test(
         "where2-6.10",
         function()
             return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
+    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +CAST(a AS INTEGER)=+b;
   ]])
         end, {
             -- <where2-6.10>
@@ -694,7 +694,7 @@ test:do_test(
             -- This will not attempt the OR optimization because of the a=b
             -- comparison.
             return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
+    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=b OR a='hello';
   ]])
         end, {
             -- <where2-6.11>
@@ -707,7 +707,7 @@ test:do_test(
         function()
             -- Permutations of the expression terms.
             return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
+    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=CAST(a AS INTEGER) OR a='hello';
   ]])
         end, {
             -- <where2-6.11.2>
@@ -720,7 +720,7 @@ test:do_test(
         function()
             -- Permutations of the expression terms.
             return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
+    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=CAST(a AS INTEGER);
   ]])
         end, {
             -- <where2-6.11.3>
@@ -733,7 +733,7 @@ test:do_test(
         function()
             -- Permutations of the expression terms.
             return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
+    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=CAST(a AS INTEGER);
   ]])
         end, {
             -- <where2-6.11.4>
@@ -750,7 +750,7 @@ test:do_test(
         "where2-6.12",
         function()
             return queryplan([[
-      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
+      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=+b OR a='hello';
     ]])
         end, {
             -- <where2-6.12>
@@ -762,7 +762,7 @@ test:do_test(
         "where2-6.12.2",
         function()
             return queryplan([[
-      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
+      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=CAST(a AS INTEGER);
     ]])
         end, {
             -- <where2-6.12.2>
@@ -774,7 +774,7 @@ test:do_test(
         "where2-6.12.3",
         function()
             return queryplan([[
-      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
+      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=CAST(a AS INTEGER) OR a='hello';
     ]])
         end, {
             -- <where2-6.12.3>
@@ -788,7 +788,7 @@ test:do_test(
             -- The addition of +a on the second term disabled the OR optimization.
             -- But we should still get the same empty-set result as in where2-6.9.
             return queryplan([[
-      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
+      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE CAST(a AS INTEGER)=+b OR +a='hello';
     ]])
         end, {
             -- <where2-6.13>
diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua
index 7492015..fc532c5 100755
--- a/test/sql-tap/where5.test.lua
+++ b/test/sql-tap/where5.test.lua
@@ -34,7 +34,7 @@ test:do_test("where5-1.0", function()
         INSERT INTO t3 SELECT * FROM t1;
     ]]
     return test:execsql [[
-        SELECT * FROM t1 WHERE x<0
+        SELECT * FROM t1 WHERE CAST(x AS INTEGER)<0
     ]]
 end, {
     -- <where5-1.0>
@@ -43,7 +43,7 @@ end, {
 })
 
 test:do_execsql_test("where5-1.1", [[
-    SELECT * FROM t1 WHERE x<=0
+    SELECT * FROM t1 WHERE CAST(x AS INTEGER)<=0
 ]], {
     -- <where5-1.1>
     '-1', '0'
@@ -51,7 +51,7 @@ test:do_execsql_test("where5-1.1", [[
 })
 
 test:do_execsql_test("where5-1.2", [[
-    SELECT * FROM t1 WHERE x=0
+    SELECT * FROM t1 WHERE CAST(x AS INTEGER)=0
 ]], {
     -- <where5-1.2>
     '0'
@@ -59,7 +59,7 @@ test:do_execsql_test("where5-1.2", [[
 })
 
 test:do_execsql_test("where5-1.3", [[
-    SELECT * FROM t1 WHERE x>=0
+    SELECT * FROM t1 WHERE CAST(x AS INTEGER)>=0
 ]], {
     -- <where5-1.3>
     '0', '1'
@@ -67,7 +67,7 @@ test:do_execsql_test("where5-1.3", [[
 })
 
 test:do_execsql_test("where5-1.4", [[
-    SELECT * FROM t1 WHERE x>0
+    SELECT * FROM t1 WHERE CAST(x AS INTEGER)>0
 ]], {
     -- <where5-1.4>
     '1'
@@ -75,7 +75,7 @@ test:do_execsql_test("where5-1.4", [[
 })
 
 test:do_execsql_test("where5-1.5", [[
-    SELECT * FROM t1 WHERE x<>0
+    SELECT * FROM t1 WHERE CAST(x AS INTEGER)<>0
 ]], {
     -- <where5-1.5>
     '-1', '1'
diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua
index d98645f..eb11ba3 100755
--- a/test/sql-tap/whereB.test.lua
+++ b/test/sql-tap/whereB.test.lua
@@ -37,7 +37,7 @@ test:do_execsql_test(
         CREATE INDEX t2b ON t2(b);
         INSERT INTO t2 VALUES(2,'99');
 
-        SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a;
+        SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 ORDER BY +x, +a;
     ]],
     {
     -- <whereB-1.1>
@@ -48,7 +48,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "whereB-1.2",
     [[
-        SELECT x, a, y=b FROM t1, t2 WHERE y=b;
+        SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE y=CAST(b AS INTEGER);
     ]],
     {
     -- <whereB-1.2>
@@ -59,7 +59,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "whereB-1.3",
     [[
-        SELECT x, a, y=b FROM t1, t2 WHERE b=y;
+        SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE CAST(b AS INTEGER)=y;
     ]],
     {
     -- <whereB-1.3>
@@ -70,7 +70,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "whereB-1.4",
     [[
-        SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
+        SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE +y=+CAST(b AS INTEGER);
     ]],
     {
     -- <whereB-1.4>
@@ -82,7 +82,7 @@ test:do_execsql_test(
     "whereB-1.100",
     [[
         DROP INDEX t2b ON t2;
-        SELECT x, a, y=b FROM t1, t2 WHERE y=b;
+        SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE y=CAST(b AS INTEGER);
     ]],
     {
     -- <whereB-1.100>
@@ -93,7 +93,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "whereB-1.101",
     [[
-        SELECT x, a, y=b FROM t1, t2 WHERE b=y;
+        SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE CAST(b AS INTEGER)=y;
     ]],
     {
     -- <whereB-1.101>
@@ -104,7 +104,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "whereB-1.102",
     [[
-        SELECT x, a, y=b FROM t1, t2 WHERE +y=+b;
+        SELECT x, a, y=CAST(b AS INTEGER) FROM t1, t2 WHERE +y=+CAST(b AS INTEGER);
     ]],
     {
     -- <whereB-1.102>
@@ -329,29 +329,29 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-4.1>
-    1, 2, true
+    1, 2, false
     -- </whereB-4.1>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "whereB-4.2",
     [[
         SELECT x, a, y=b FROM t1, t2 WHERE y=b;
     ]],
     {
     -- <whereB-4.2>
-    1, 2, true
+    1, "Type mismatch: can not convert text to number"
     -- </whereB-4.2>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "whereB-4.3",
     [[
         SELECT x, a, y=b FROM t1, t2 WHERE b=y;
     ]],
     {
     -- <whereB-4.3>
-    1, 2, true
+    1, "Type mismatch: can not convert text to number"
     -- </whereB-4.3>
     })
 
@@ -364,7 +364,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-4.4>
-    1, 2, true
     -- </whereB-4.4>
     })
 
@@ -376,7 +375,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-4.100>
-    1, 2, true
     -- </whereB-4.100>
     })
 
@@ -387,7 +385,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-4.101>
-    1, 2, true
     -- </whereB-4.101>
     })
 
@@ -400,7 +397,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-4.102>
-    1, 2, true
     -- </whereB-4.102>
     })
 
@@ -429,29 +425,29 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-5.1>
-    1, 2, true
+    1, 2, false
     -- </whereB-5.1>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "whereB-5.2",
     [[
         SELECT x, a, y=b FROM t1, t2 WHERE y=b;
     ]],
     {
     -- <whereB-5.2>
-    1, 2, true
+    1, "Type mismatch: can not convert text to integer"
     -- </whereB-5.2>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "whereB-5.3",
     [[
         SELECT x, a, y=b FROM t1, t2 WHERE b=y;
     ]],
     {
     -- <whereB-5.3>
-    1, 2, true
+    1, "Type mismatch: can not convert text to integer"
     -- </whereB-5.3>
     })
 
@@ -464,7 +460,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-5.4>
-    1, 2, true
     -- </whereB-5.4>
     })
 
@@ -476,7 +471,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-5.100>
-    1, 2, true
     -- </whereB-5.100>
     })
 
@@ -487,7 +481,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-5.101>
-    1, 2, true
     -- </whereB-5.101>
     })
 
@@ -500,7 +493,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-5.102>
-    1, 2, true
     -- </whereB-5.102>
     })
 
@@ -529,29 +521,29 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-6.1>
-    1, 2, true
+    1, 2, false
     -- </whereB-6.1>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "whereB-6.2",
     [[
         SELECT x, a, y=b FROM t1, t2 WHERE y=b;
     ]],
     {
     -- <whereB-6.2>
-    1, 2, true
+    1, "Type mismatch: can not convert text to number"
     -- </whereB-6.2>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "whereB-6.3",
     [[
         SELECT x, a, y=b FROM t1, t2 WHERE b=y;
     ]],
     {
     -- <whereB-6.3>
-    1, 2, true
+    1, "Type mismatch: can not convert text to number"
     -- </whereB-6.3>
     })
 
@@ -564,7 +556,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-6.4>
-    1, 2, true
     -- </whereB-6.4>
     })
 
@@ -576,7 +567,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-6.100>
-    1, 2, true
     -- </whereB-6.100>
     })
 
@@ -587,7 +577,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-6.101>
-    1, 2, true
     -- </whereB-6.101>
     })
 
@@ -600,7 +589,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-6.102>
-    1, 2, true
     -- </whereB-6.102>
     })
 
@@ -629,7 +617,7 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-7.1>
-    1, 2, true
+    1, 2, false
     -- </whereB-7.1>
     })
 
@@ -640,7 +628,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-7.2>
-    1, 2, true
     -- </whereB-7.2>
     })
 
@@ -651,7 +638,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-7.3>
-    1, 2, true
     -- </whereB-7.3>
     })
 
@@ -664,7 +650,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-7.4>
-    1, 2, true
     -- </whereB-7.4>
     })
 
@@ -676,7 +661,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-7.100>
-    1, 2, true
     -- </whereB-7.100>
     })
 
@@ -687,7 +671,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-7.101>
-    1, 2, true
     -- </whereB-7.101>
     })
 
@@ -700,7 +683,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-7.102>
-    1, 2, true
     -- </whereB-7.102>
     })
 
@@ -729,7 +711,7 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-8.1>
-    1, 2, true
+    1, 2, false
     -- </whereB-8.1>
     })
 
@@ -740,7 +722,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-8.2>
-    1, 2, true
     -- </whereB-8.2>
     })
 
@@ -751,7 +732,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-8.3>
-    1, 2, true
     -- </whereB-8.3>
     })
 
@@ -764,7 +744,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-8.4>
-    1, 2, true
     -- </whereB-8.4>
     })
 
@@ -776,7 +755,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-8.100>
-    1, 2, true
     -- </whereB-8.100>
     })
 
@@ -787,7 +765,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-8.101>
-    1, 2, true
     -- </whereB-8.101>
     })
 
@@ -800,7 +777,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-8.102>
-    1, 2, true
     -- </whereB-8.102>
     })
 
@@ -829,7 +805,7 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-9.1>
-    1, 2, true
+    1, 2, false
     -- </whereB-9.1>
     })
 
@@ -840,7 +816,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-9.2>
-    1, 2, true
     -- </whereB-9.2>
     })
 
@@ -851,7 +826,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-9.3>
-    1, 2, true
     -- </whereB-9.3>
     })
 
@@ -864,7 +838,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-9.4>
-    1, 2, true
     -- </whereB-9.4>
     })
 
@@ -876,7 +849,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-9.100>
-    1, 2, true
     -- </whereB-9.100>
     })
 
@@ -887,7 +859,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-9.101>
-    1, 2, true
     -- </whereB-9.101>
     })
 
@@ -900,7 +871,6 @@ test:do_execsql_test(
     ]],
     {
     -- <whereB-9.102>
-    1, 2, true
     -- </whereB-9.102>
     })
 
diff --git a/test/sql-tap/whereC.test.lua b/test/sql-tap/whereC.test.lua
index 89459de..2f214ef 100755
--- a/test/sql-tap/whereC.test.lua
+++ b/test/sql-tap/whereC.test.lua
@@ -1,7 +1,7 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
 local yaml = require('yaml')
-test:plan(41)
+test:plan(29)
 
 --!./tcltestrunner.lua
 -- 2011 November 16
@@ -54,10 +54,6 @@ test:do_execsql_test(
 --local function main()
 test:test("main", function()
     local data = {{"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3",         {4, 5}},
-                  -- {"SELECT i FROM t1 WHERE rowid='12'",                  {12}},
-                  {"SELECT i FROM t1 WHERE a=1 AND b='2'",               {3, 4, 5}},
-                  {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i>'3'",     {4, 5}},
-                  {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<5",       {3, 4}},
                   {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<12",        {10, 11}},
                   {"SELECT i FROM t1 WHERE a IN(1, 2) AND b=2 AND i<11", {3, 4, 5, 10}},
                   {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12", {10, 11, 12}},
@@ -65,9 +61,7 @@ test:test("main", function()
                   {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 11", {10, 11}},
                   {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10", {}},
                   {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<NULL",      {}},
-                  {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL",     {}},
-                  {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5",     {3, 4}}}
-                  -- {"SELECT i FROM t1 WHERE rowid IS '12'",               {12}}}
+                  {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL",     {}}}
 
     for tn, t in ipairs(data) do
         test:do_execsql_test(
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index 112e41a..483873b 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -137,13 +137,19 @@ INSERT INTO ts(s) VALUES ('abc'), (12.5);
  | ...
 SELECT s FROM ts WHERE s = true;
  | ---
- | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - metadata:
+ |   - name: S
+ |     type: scalar
+ |   rows:
+ |   - [true]
  | ...
 SELECT s FROM ts WHERE s < true;
  | ---
- | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - metadata:
+ |   - name: S
+ |     type: scalar
+ |   rows:
+ |   - [false]
  | ...
 SELECT s FROM ts WHERE s IN (true, 1, 'abcd');
  | ---
@@ -3389,22 +3395,22 @@ SELECT false < 2;
 SELECT 2 > true;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT 2 > false;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT 2 < true;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT 2 < false;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT a1, a1 > 2 FROM t6
@@ -3420,12 +3426,12 @@ SELECT a1, a1 < 2 FROM t6
 SELECT a1, 2 > a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a1, 2 < a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, a2 > 2 FROM t6
  | ---
@@ -3440,12 +3446,12 @@ SELECT a2, a2 < 2 FROM t6
 SELECT a2, 2 > a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, 2 < a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT b, true > b FROM t7;
@@ -3471,22 +3477,22 @@ SELECT b, false < b FROM t7;
 SELECT b, b > true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT b, b > false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT b, b < true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT b, b < false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT a1, b, a1 > b FROM t6, t7;
@@ -3502,12 +3508,12 @@ SELECT a1, b, a1 < b FROM t6, t7;
 SELECT a1, b, b > a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a1, b, b < a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, b, a2 > b FROM t6, t7;
  | ---
@@ -3522,12 +3528,12 @@ SELECT a2, b, a2 < b FROM t6, t7;
 SELECT a2, b, b > a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, b, b < a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT true >= 2;
@@ -3553,22 +3559,22 @@ SELECT false <= 2;
 SELECT 2 >= true;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT 2 >= false;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT 2 <= true;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT 2 <= false;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT a1, a1 >= 2 FROM t6
@@ -3584,12 +3590,12 @@ SELECT a1, a1 <= 2 FROM t6
 SELECT a1, 2 >= a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a1, 2 <= a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, a2 >= 2 FROM t6
  | ---
@@ -3604,12 +3610,12 @@ SELECT a2, a2 <= 2 FROM t6
 SELECT a2, 2 >= a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, 2 <= a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT b, true >= b FROM t7;
@@ -3635,22 +3641,22 @@ SELECT b, false <= b FROM t7;
 SELECT b, b >= true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT b, b >= false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT b, b <= true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT b, b <= false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT a1, b, a1 >= b FROM t6, t7;
@@ -3666,12 +3672,12 @@ SELECT a1, b, a1 <= b FROM t6, t7;
 SELECT a1, b, b >= a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a1, b, b <= a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, b, a2 >= b FROM t6, t7;
  | ---
@@ -3686,12 +3692,12 @@ SELECT a2, b, a2 <= b FROM t6, t7;
 SELECT a2, b, b >= a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, b, b <= a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT true == 2;
@@ -3717,22 +3723,22 @@ SELECT false != 2;
 SELECT 2 == true;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT 2 == false;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT 2 != true;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT 2 != false;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT a1, a1 == 2 FROM t6
@@ -3748,12 +3754,12 @@ SELECT a1, a1 != 2 FROM t6
 SELECT a1, 2 == a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a1, 2 != a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, a2 == 2 FROM t6
  | ---
@@ -3768,12 +3774,12 @@ SELECT a2, a2 != 2 FROM t6
 SELECT a2, 2 == a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, 2 != a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT b, true == b FROM t7;
@@ -3799,22 +3805,22 @@ SELECT b, false != b FROM t7;
 SELECT b, b == true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT b, b == false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT b, b != true FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT b, b != false FROM t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT a1, b, a1 == b FROM t6, t7;
@@ -3830,12 +3836,12 @@ SELECT a1, b, a1 != b FROM t6, t7;
 SELECT a1, b, b == a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a1, b, b != a1 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, b, a2 == b FROM t6, t7;
  | ---
@@ -3850,12 +3856,12 @@ SELECT a2, b, a2 != b FROM t6, t7;
 SELECT a2, b, b == a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 SELECT a2, b, b != a2 FROM t6, t7;
  | ---
  | - null
- | - 'Type mismatch: can not convert unsigned to boolean'
+ | - 'Type mismatch: can not convert boolean to unsigned'
  | ...
 
 SELECT true IN (0, 1, 2, 3);
@@ -4534,22 +4540,22 @@ SELECT false < 2.3;
 SELECT 2.3 > true;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT 2.3 > false;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT 2.3 < true;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT 2.3 < false;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT a1, a1 > 2.3 FROM t6
@@ -4565,12 +4571,12 @@ SELECT a1, a1 < 2.3 FROM t6
 SELECT a1, 2.3 > a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a1, 2.3 < a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, a2 > 2.3 FROM t6
  | ---
@@ -4585,12 +4591,12 @@ SELECT a2, a2 < 2.3 FROM t6
 SELECT a2, 2.3 > a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, 2.3 < a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT c, true > c FROM t8;
@@ -4616,22 +4622,22 @@ SELECT c, false < c FROM t8;
 SELECT c, c > true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT c, c > false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT c, c < true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT c, c < false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT a1, c, a1 > c FROM t6, t8;
@@ -4647,12 +4653,12 @@ SELECT a1, c, a1 < c FROM t6, t8;
 SELECT a1, c, c > a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a1, c, c < a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, c, a2 > c FROM t6, t8;
  | ---
@@ -4667,12 +4673,12 @@ SELECT a2, c, a2 < c FROM t6, t8;
 SELECT a2, c, c > a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, c, c < a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT true >= 2.3;
@@ -4698,22 +4704,22 @@ SELECT false <= 2.3;
 SELECT 2.3 >= true;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT 2.3 >= false;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT 2.3 <= true;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT 2.3 <= false;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT a1, a1 >= 2.3 FROM t6
@@ -4729,12 +4735,12 @@ SELECT a1, a1 <= 2.3 FROM t6
 SELECT a1, 2.3 >= a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a1, 2.3 <= a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, a2 >= 2.3 FROM t6
  | ---
@@ -4749,12 +4755,12 @@ SELECT a2, a2 <= 2.3 FROM t6
 SELECT a2, 2.3 >= a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, 2.3 <= a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT c, true >= c FROM t8;
@@ -4780,22 +4786,22 @@ SELECT c, false <= c FROM t8;
 SELECT c, c >= true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT c, c >= false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT c, c <= true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT c, c <= false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT a1, c, a1 >= c FROM t6, t8;
@@ -4811,12 +4817,12 @@ SELECT a1, c, a1 <= c FROM t6, t8;
 SELECT a1, c, c >= a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a1, c, c <= a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, c, a2 >= c FROM t6, t8;
  | ---
@@ -4831,12 +4837,12 @@ SELECT a2, c, a2 <= c FROM t6, t8;
 SELECT a2, c, c >= a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, c, c <= a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT true == 2.3;
@@ -4862,22 +4868,22 @@ SELECT false != 2.3;
 SELECT 2.3 == true;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT 2.3 == false;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT 2.3 != true;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT 2.3 != false;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT a1, a1 == 2.3 FROM t6
@@ -4893,12 +4899,12 @@ SELECT a1, a1 != 2.3 FROM t6
 SELECT a1, 2.3 == a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a1, 2.3 != a1 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, a2 == 2.3 FROM t6
  | ---
@@ -4913,12 +4919,12 @@ SELECT a2, a2 != 2.3 FROM t6
 SELECT a2, 2.3 == a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, 2.3 != a2 FROM t6
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT c, true == c FROM t8;
@@ -4944,22 +4950,22 @@ SELECT c, false != c FROM t8;
 SELECT c, c == true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT c, c == false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT c, c != true FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT c, c != false FROM t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT a1, c, a1 == c FROM t6, t8;
@@ -4975,12 +4981,12 @@ SELECT a1, c, a1 != c FROM t6, t8;
 SELECT a1, c, c == a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a1, c, c != a1 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, c, a2 == c FROM t6, t8;
  | ---
@@ -4995,12 +5001,12 @@ SELECT a2, c, a2 != c FROM t6, t8;
 SELECT a2, c, c == a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 SELECT a2, c, c != a2 FROM t6, t8;
  | ---
  | - null
- | - 'Type mismatch: can not convert real to boolean'
+ | - 'Type mismatch: can not convert boolean to real'
  | ...
 
 SELECT true IN (0.1, 1.2, 2.3, 3.4);
@@ -5290,22 +5296,22 @@ SELECT false < 'abc';
 SELECT 'abc' > true;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 SELECT 'abc' > false;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 SELECT 'abc' < true;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 SELECT 'abc' < false;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 
 SELECT d, true > d FROM t9;
@@ -5331,22 +5337,22 @@ SELECT d, false < d FROM t9;
 SELECT d, d > true FROM t9;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 SELECT d, d > false FROM t9;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 SELECT d, d < true FROM t9;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 SELECT d, d < false FROM t9;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 
 SELECT a1, d, a1 > d FROM t6, t9;
@@ -5362,12 +5368,12 @@ SELECT a1, d, a1 < d FROM t6, t9;
 SELECT a1, d, d > a1 FROM t6, t9;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 SELECT a1, d, d < a1 FROM t6, t9;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 SELECT a2, d, a2 > d FROM t6, t9;
  | ---
@@ -5382,12 +5388,12 @@ SELECT a2, d, a2 < d FROM t6, t9;
 SELECT a2, d, d > a2 FROM t6, t9;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 SELECT a2, d, d < a2 FROM t6, t9;
  | ---
  | - null
- | - 'Type mismatch: can not convert text to boolean'
+ | - 'Type mismatch: can not convert boolean to text'
  | ...
 
 SELECT true || 'abc';
diff --git a/test/sql/types.result b/test/sql/types.result
index 38e4385..97b57a8 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -352,7 +352,7 @@ box.execute("SELECT * FROM tboolean WHERE s1 = x'44';")
 box.execute("SELECT * FROM tboolean WHERE s1 = 'abc';")
 ---
 - null
-- 'Type mismatch: can not convert abc to boolean'
+- 'Type mismatch: can not convert text to boolean'
 ...
 box.execute("SELECT * FROM tboolean WHERE s1 = 1;")
 ---
@@ -614,10 +614,11 @@ box.execute("SELECT 18446744073709551615.0 > 18446744073709551615")
   rows:
   - [true]
 ...
-box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)")
+box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0, 1)")
 ---
 - metadata:
-  - name: 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)
+  - name: 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0,
+      1)
     type: boolean
   rows:
   - [true]
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index bd14b34..f9e2531 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -151,7 +151,7 @@ box.execute("SELECT 18446744073709551610 - 18446744073709551615;")
 box.execute("SELECT 18446744073709551615 = null;")
 box.execute("SELECT 18446744073709551615 = 18446744073709551615.0;")
 box.execute("SELECT 18446744073709551615.0 > 18446744073709551615")
-box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)")
+box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0, 1)")
 box.execute("SELECT 1 LIMIT 18446744073709551615;")
 box.execute("SELECT 1 LIMIT 1 OFFSET 18446744073709551614;")
 box.execute("SELECT CAST('18446744073' || '709551616' AS INTEGER);")
-- 
2.7.4

             reply	other threads:[~2020-03-20 12:35 UTC|newest]

Thread overview: 8+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2020-03-20 12:34 imeevma [this message]
2020-03-29 22:29 ` Vladislav Shpilevoy
2020-03-30 11:38   ` Nikita Pettik
2020-03-30 16:53     ` Mergen Imeev
2020-04-02 23:47       ` Vladislav Shpilevoy
2020-04-21 12:42   ` Mergen Imeev
2020-04-26 18:22     ` Vladislav Shpilevoy
  -- strict thread matches above, loose matches on Subject: below --
2020-02-20  8:00 imeevma

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=9a402676dbab6bdc4b1cbbdfc1f5069214fdfa06.1584707598.git.imeevma@gmail.com \
    --to=imeevma@tarantool.org \
    --cc=tarantool-patches@dev.tarantool.org \
    --cc=tsafin@tarantool.org \
    --cc=v.shpilevoy@tarantool.org \
    --subject='Re: [Tarantool-patches] [PATCH v1 1/1] sql: remove implicit cast for COMPARISON' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox