Tarantool development patches archive
 help / color / mirror / Atom feed
From: Mergen Imeev <imeevma@tarantool.org>
To: Nikita Pettik <korablev@tarantool.org>
Cc: tarantool-patches@dev.tarantool.org
Subject: Re: [Tarantool-patches] [PATCH v3 6/8] sql: remove implicit cast for comparison
Date: Sun, 5 Jul 2020 17:47:20 +0300	[thread overview]
Message-ID: <20200705144720.GD135859@tarantool.org> (raw)
In-Reply-To: <20200629235129.GC27240@tarantool.org>

My answers and new patch below.

On Mon, Jun 29, 2020 at 11:51:29PM +0000, Nikita Pettik wrote:
> On 25 Jun 18:17, imeevma@tarantool.org wrote:
> > Thank you for review! My answers and new patch below.
> > 
> > On 22.06.2020 15:25, Nikita Pettik wrote:
> > > On 17 Jun 15:36, imeevma@tarantool.org wrote:
> > >> @@ -2399,14 +2387,6 @@ case OP_Ge: {             /* same as TK_GE, jump, in1, in3 */
> > >>  	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;
> > >> -
> > >
> > > Replace these assertions with relevant ones.
> > >
> > Not sure if this is necessary, since now we have much less flag
> > changes after removal of mem_apply_type().
> 
> Не понял как это связано...Твои изменения не затрагивают
> инвариант MEM_Dyn.
>  
True, however original asserts checked that
mem_apply_type() does not changed it. Now we can say that
it definitely won't be changed. I can return flags*
variables for the sake of these asserts. Should I do this?

Also, after #4906 these asserts become outdated since flags
of the mems won't change at all. Well, it will be so if we
close #4906.

> > >> @@ -0,0 +1,281 @@
> > >> +#!/usr/bin/env tarantool
> > >> +test = require("sqltester")
> > >> +test:plan(32)
> > >> +
> > >> +--
> > >> +
> > >> +test:execsql([[
> > >> +    CREATE TABLE t1(x TEXT primary key);
> > >> +    INSERT INTO t1 VALUES('1');
> > >> +    CREATE TABLE t2(x INTEGER primary key);
> > >> +    INSERT INTO t2 VALUES(1);
> > >> +    CREATE TABLE t3(x DOUBLE primary key);
> > >> +    INSERT INTO t3 VALUES(1.0);
> > >
> > > What about table with scalar/any fields?
> > >
> > I think this is too big a problem to solve it here.
> 
> Гораздо лучше потом клепать фоллоу-апы, точечно затыкая креши..
> Я бы добавил хотя бы самых базовых примеров.
> 
Added.

> >  			i = pIn3->u.i;
> > @@ -3617,7 +3595,38 @@ skip_truncate:
> >  	assert(oc!=OP_SeekGE || r.default_rc==+1);
> >  	assert(oc!=OP_SeekLT || r.default_rc==+1);
> >  
> > +	/*
> > +	 * Make sure that the types of all the fields in the tuple
> > +	 * that will be used in the iterator match the field types
> > +	 * of the space.
> > +	 */
> >  	r.aMem = &aMem[pOp->p3];
> > +	for (int i = 0; i < r.nField; ++i) {
> > +		enum field_type type = r.key_def->parts[i].type;
> > +		struct Mem *mem = &r.aMem[i];
> > +		if (mem_check_type(mem, type) == 0 ||
> > +		    mem_convert_numeric(mem, type, true) == 0)
> > +			continue;
> 
> Я бы все-таки разбил бы эту проверку на два условия:
> 
> if (...)
> 	continue
> if (...)
> 	continue
> 
> А еще лучше вынести этот чанк в хелпер.
> 
Moved to a new function.

> > +		/*
> > +		 * If the number was not converted without loss,
> > +		 * we will not find tuples using the EQ iterator.
> > +		 */
> 
> Пожайлуста, не используй двойное отрицание - комментарий очень
> сложно воспринимать. Давай это переформулируем, можно пример
> добавить. Это довольно скользкое место.
> 
Removed. Added comment in description of a new function.

> > +		if ((mem->flags & MEM_Real) != 0 &&
> > +		    (type == FIELD_TYPE_INTEGER ||
> > +		     type == FIELD_TYPE_UNSIGNED)) {
> > +			assert(eqOnly == 1);
> > +			res = 1;
> > +			goto seek_not_found;
> > +		}
> > +		if ((mem->flags & (MEM_Int | MEM_UInt)) != 0 &&
> > +		    type == FIELD_TYPE_DOUBLE && eqOnly == 1) {
> > +			res = 1;
> > +			goto seek_not_found;
> > +		}
> > +		diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
> > +			field_type_strs[type], mem_type_to_str(mem));
> > +		goto abort_due_to_error;
> > +	}
> >  #ifdef SQL_DEBUG
> >  	{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
> >  #endif
> > @@ -4744,7 +4753,25 @@ case OP_IdxGE:  {       /* jump */
> >  		assert(pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxLT);
> >  		r.default_rc = 0;
> >  	}
> > +
> > +	/*
> > +	 * Make sure that the types of all the fields in the tuple
> > +	 * that will be used in the iterator comparable with the
> > +	 * fields of the space.
> > +	 */
> >  	r.aMem = &aMem[pOp->p3];
> > +	for (int i = 0; i < r.nField; ++i) {
> > +		enum field_type type = r.key_def->parts[i].type;
> > +		struct Mem *mem = &r.aMem[i];
> 
> Как-то мне эти два места (выше в SeekGE) вообще не нравятся.
> Какие-то они запутанные. Давай подумаем как можно зарефакторить их.
> 
Moved to a new function. However, previously cast
here didn't always worked since in some cases field_type
and MEM_type of the mem not always were compatible. I think
this was fixed in previous commits. All tests passes.

However, there is another possiblity: it may be that I
already removed tests that fail on convertion but worked
on check. I do not think that it is the case though.

This also can be fully fixed in #4906 issue.

> > +		if (mem_check_type(mem, type) == 0)
> > +			continue;
> > +		if (sql_type_is_numeric(type) &&
> > +		    (mem->flags & (MEM_Real | MEM_Int | MEM_UInt)) != 0)
> > +			continue;
> > +		diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
> > +			field_type_strs[type], mem_type_to_str(mem));
> > +		goto abort_due_to_error;
> > +	}
> >  #ifdef SQL_DEBUG
> >  	{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
> >  #endif
> > diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
> > index 6d8768865..1d7c76670 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)
> 
> Ты в следующем коммите как раз делаешь клин-ап. Давай эту функцию
> там и удалим (как и остальные).
> 
It is a bit troublesome since the functions I remove here
are 'static'. I think, that instead of removing 'static'
keyword here and removing the function in the next commit
it is better to just remove these runctions here.

I can change it if you think that removing in two commits
is better.

> >  			nConstraint++;
> >  		}
> > -		sqlDbFree(db, start_types);
> > -		sqlDbFree(db, end_types);
> >  
> >  		/* Top of the loop body */
> >  		pLevel->p2 = sqlVdbeCurrentAddr(v);
> > diff --git a/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
> > new file mode 100755
> > index 000000000..b405a11b6
> > --- /dev/null
> > +++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
> 
> Когда кто-то ищет где посмотреть тесты связанные с типами,
> первое что приходит в голову - грепнуть *types*. Этот тест
> по сути ничего и не тестирует (уверен, эти пути уже покрыты
> в других тестах), зато хорошо отображает работу системы типов.
> Я бы просто подумал о других разработчиках, и все же замержил
> его в какой-нибудь тест типа sql/types.test.lua.
Moved to sql/types.test.lua


New patch:


commit f52e6669146e7a06b78d167abf369d67d9327789
Author: Mergen Imeev <imeevma@gmail.com>
Date:   Thu Jun 25 12:39:19 2020 +0300

    sql: remove implicit cast for comparison
    
    This patch removes implicit cast for comparison.
    
    Closes #4230
    
    @TarantoolBot document
    Title: remove implicit cast for comparison
    
    After this patch-set, there will be no implicit casts for
    comparison. This means that the values ​​of the field types STRING,
    BOOLEAN and VARBINARY can be compared with the values ​​of the same
    field type. Any numerical value can be compared with any other
    numerical value.
    
    Example:
    
    For comparison:
    ```
    tarantool> box.execute([[SELECT '1' > 0;]])
    ---
    - null
    - "Type mismatch: can not convert '1' (type: text) to numeric"
    ...
    
    tarantool> box.execute([[SELECT true > X'33';]])
    ---
    - null
    - 'Type mismatch: can not convert ''TRUE'' (type: boolean) to varbinary'
    ...
    
    tarantool> box.execute([[SELECT 1.23 > 123;]])
    ---
    - metadata:
      - name: 1.23 > 123
        type: boolean
      rows:
      - [false]
    ...
    ```

diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index ce9edaad9..0d3da49d3 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -561,6 +561,50 @@ mem_convert_to_numeric(struct Mem *mem, enum field_type type, bool is_precise)
 	return mem_convert_to_integer(mem, is_precise);
 }
 
+/**
+ * Check that MEM types of mems are compatible with the
+ * corresponding types from key_def.
+ *
+ * If mem and type are comparable but incompatible according to
+ * field_mp_plain_type_is_compatible() and the comparison
+ * operation is EQ, we can say that the result of the comparison
+ * will be FALSE. This allows us to compare DOUBLE and INTEGER
+ * values.
+ *
+ * @param mems The first mem of the array.
+ * @param def key_def that contains types to check.
+ * @param size Size of the array.
+ * @param is_op_eq TRUE is comparison operator is EQ.
+ * @param[out] is_not_converted TRUE if operation is EQ and mem
+                                and type are comparable but not
+                                compatible. FALSE otherwise.
+ * @retval TRUE if the MEM types and types from key_def are
+ *         comparable, FALSE otherwise.
+ */
+static bool
+mem_are_types_comparable(struct Mem *mems, struct key_def *def, uint32_t size,
+			 bool is_op_eq, bool *is_not_converted) {
+	assert(!is_op_eq || !*is_not_converted);
+	for (uint32_t i = 0; i < size; ++i) {
+		enum field_type type = def->parts[i].type;
+		struct Mem *mem = &mems[i];
+		if (mem_is_type_compatible(mem, type))
+			continue;
+		if (mem_convert_to_numeric(mem, type, true) == 0)
+			continue;
+		if (sql_type_is_numeric(type) &&
+		    (mem->flags & (MEM_Int | MEM_UInt | MEM_Real)) != 0) {
+			if (is_op_eq)
+				*is_not_converted = true;
+			continue;
+		}
+		diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+			field_type_strs[type], mem_type_to_str(mem));
+		return false;
+	}
+	return true;
+}
+
 /*
  * pMem currently only holds a string type (or maybe a BLOB that we can
  * interpret as a string if we want to).  Compute its corresponding
@@ -2289,8 +2333,6 @@ case OP_Ge: {             /* same as TK_GE, jump, in1, in3 */
 	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) {
@@ -2349,22 +2391,17 @@ case OP_Ge: {             /* same as TK_GE, jump, in1, in3 */
 	} 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;
-					}
-
-				}
+			if ((flags1 & MEM_Str) == MEM_Str) {
+				diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+					 sql_value_to_diag_str(pIn1),
+					 "numeric");
+				goto abort_due_to_error;
+			}
+			if ((flags3 & MEM_Str) == MEM_Str) {
+				diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+					 sql_value_to_diag_str(pIn3),
+					 "numeric");
+				goto abort_due_to_error;
 			}
 			/* Handle the common case of integer comparison here, as an
 			 * optimization, to avoid a call to sqlMemCompare()
@@ -2397,22 +2434,17 @@ case OP_Ge: {             /* same as TK_GE, jump, in1, in3 */
 				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 ((flags1 & MEM_Str) == 0) {
+				diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+					 mem_type_to_str(pIn3),
+					 mem_type_to_str(pIn1));
+				goto abort_due_to_error;
 			}
-			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);
+			if ((flags3 & MEM_Str) == 0) {
+				diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+					 mem_type_to_str(pIn1),
+					 mem_type_to_str(pIn3));
+				goto abort_due_to_error;
 			}
 		}
 		assert(pOp->p4type==P4_COLLSEQ || pOp->p4.pColl==0);
@@ -2428,14 +2460,6 @@ case OP_Ge: {             /* same as TK_GE, jump, in1, in3 */
 	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;
-
 	if (pOp->p5 & SQL_STOREP2) {
 		iCompare = res;
 		res2 = res2!=0;  /* For this path res2 must be exactly 0 or 1 */
@@ -3530,8 +3554,6 @@ case OP_SeekGT: {       /* jump, in3 */
 		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;
@@ -3623,7 +3645,20 @@ skip_truncate:
 	assert(oc!=OP_SeekGE || r.default_rc==+1);
 	assert(oc!=OP_SeekLT || r.default_rc==+1);
 
+	/*
+	 * Make sure that the types of all the fields in the tuple
+	 * that will be used in the iterator match the field types
+	 * of the space.
+	 */
 	r.aMem = &aMem[pOp->p3];
+	bool is_not_found = false;
+	if (!mem_are_types_comparable(r.aMem, r.key_def, r.nField, eqOnly == 1,
+				      &is_not_found))
+		goto abort_due_to_error;
+	if (is_not_found) {
+		res = 1;
+		goto seek_not_found;
+	}
 #ifdef SQL_DEBUG
 	{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
 #endif
@@ -4750,7 +4785,16 @@ case OP_IdxGE:  {       /* jump */
 		assert(pOp->opcode==OP_IdxGE || pOp->opcode==OP_IdxLT);
 		r.default_rc = 0;
 	}
+
+	/*
+	 * Make sure that the types of mems are comparable with
+	 * the field types of the space.
+	 */
 	r.aMem = &aMem[pOp->p3];
+	bool unused = false;
+	if (!mem_are_types_comparable(r.aMem, r.key_def, r.nField, false,
+				      &unused))
+		goto abort_due_to_error;
 #ifdef SQL_DEBUG
 	{ int i; for(i=0; i<r.nField; i++) assert(memIsValid(&r.aMem[i])); }
 #endif
diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
index 6d8768865..1d7c76670 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,7 @@ 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 | WO_ISNULL)) == 0) {
 			Expr *pRight = pTerm->pExpr->pRight;
 			if (sqlExprCanBeNull(pRight)) {
 				sqlVdbeAddOp2(v, OP_IsNull, regBase + j,
@@ -733,7 +653,6 @@ codeAllEqualityTerms(Parse * pParse,	/* Parsing context */
 			}
 		}
 	}
-	*res_type = type;
 	return regBase;
 }
 
@@ -904,10 +823,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 +909,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 +934,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,94 +950,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;
-		int *seek_addrs = region_alloc_array(&pParse->region,
-						     typeof(seek_addrs[0]), nEq,
-						     &addrs_sz);
-		if (seek_addrs == NULL) {
-			diag_set(OutOfMemory, addrs_sz, "region_alloc_array",
-				 "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,
@@ -1146,10 +959,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
@@ -1189,13 +998,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);
 
@@ -1209,8 +1011,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/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua
index 2a00626fc..1d56ffb44 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 b to numeric"}},
     { 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 b to numeric"}},
     { 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 b to numeric"}},
 }
 
 for _, row in ipairs(data) do
diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua
index 570cc1779..e2f498889 100755
--- a/test/sql-tap/in1.test.lua
+++ b/test/sql-tap/in1.test.lua
@@ -637,12 +637,12 @@ test:do_test(
     "in-11.2",
     function()
         -- The '2' should be coerced into 2 because t6.b is NUMERIC
-        return test:execsql [[
+        return test:catchsql [[
             SELECT * FROM t6 WHERE b IN ('2');
         ]]
     end, {
         -- <in-11.2>
-        1, 2
+        1, "Type mismatch: can not convert 2 to numeric"
         -- </in-11.2>
     })
 
diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua
index 33947d0ab..a494e846f 100755
--- a/test/sql-tap/in4.test.lua
+++ b/test/sql-tap/in4.test.lua
@@ -147,12 +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>
+        1, "Type mismatch: can not convert integer to text"
         -- </in4-2.8>
     })
 
diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua
index b92bc508e..3276f0db2 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(CAST(new.b AS STRING),1);
             END;
             INSERT INTO t1(a, b) VALUES('hi', 453);
diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua
index 51e0ecb79..792302ab5 100755
--- a/test/sql-tap/join.test.lua
+++ b/test/sql-tap/join.test.lua
@@ -1028,22 +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>
+        1, "Type mismatch: can not convert integer to text"
         -- </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 1 to numeric"
         -- </join-11.10>
     })
 
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index e0fe50bbe..3cef617f4 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=CAST(x1+1 AS STRING) "..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 0 to numeric"
         -- </misc1-12.2>
     })
 
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index 9a969bf3c..f5a9b63fe 100755
--- a/test/sql-tap/select1.test.lua
+++ b/test/sql-tap/select1.test.lua
@@ -1912,7 +1912,7 @@ test:do_execsql_test(
 test:do_execsql_test(
         "select1-12.7",
         [[
-            SELECT * FROM t3 WHERE a=(SELECT 1);
+            SELECT * FROM t3 WHERE a=(SELECT '1');
         ]], {
             -- <select1-12.7>
             0, "1", "2"
@@ -1922,7 +1922,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "select1-12.8",
     [[
-        SELECT * FROM t3 WHERE a=(SELECT 2);
+        SELECT * FROM t3 WHERE a=(SELECT '2');
     ]], {
         -- <select1-12.8>
 
diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
index e1e43c557..0d1390fd6 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 CAST(a AS INTEGER)<b;
     ]], {
         -- <select7-7.7>
         "string", "123"
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index e0771825e..bad702de9 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
deleted file mode 100755
index ac89c7df2..000000000
--- a/test/sql-tap/tkt-9a8b09f8e6.test.lua
+++ /dev/null
@@ -1,506 +0,0 @@
-#!/usr/bin/env tarantool
-test = require("sqltester")
-test:plan(47)
-
---!./tcltestrunner.lua
--- 2014 June 26
---
--- The author disclaims copyright to this source code.  In place of
--- a legal notice, here is a blessing:
---
---    May you do good and not evil.
---    May you find forgiveness for yourself and forgive others.
---    May you share freely, never taking more than you give.
---
--------------------------------------------------------------------------
--- This file implements regression tests for sql library.
---
--- This file implements tests to verify that ticket [9a8b09f8e6] has been
--- fixed.
---
--- ["set","testdir",[["file","dirname",["argv0"]]]]
--- ["source",[["testdir"],"\/tester.tcl"]]
-testprefix = "tkt-9a8b09f8e6"
--- MUST_WORK_TEST
-if (0 > 0)
- then
-end
-test:do_execsql_test(
-    1.1,
-    [[
-        CREATE TABLE t1(x TEXT primary key);
-        INSERT INTO t1 VALUES('1');
-    ]], {
-        -- <1.1>
-        
-        -- </1.1>
-    })
-
-test:do_execsql_test(
-    1.2,
-    [[
-        CREATE TABLE t2(x INTEGER primary key);
-        INSERT INTO t2 VALUES(1);
-    ]], {
-        -- <1.2>
-        
-        -- </1.2>
-    })
-
-test:do_execsql_test(
-    1.3,
-    [[
-        CREATE TABLE t3(x NUMBER primary key);
-        INSERT INTO t3 VALUES(1.0);
-    ]], {
-        -- <1.3>
-        
-        -- </1.3>
-    })
-
-test:do_execsql_test(
-    1.4,
-    [[
-        CREATE TABLE t4(x NUMBER primary key);
-        INSERT INTO t4 VALUES(1.11);
-    ]], {
-        -- <1.4>
-        
-        -- </1.4>
-    })
-
-test:do_execsql_test(
-    1.5,
-    [[
-        CREATE TABLE t5(id  INT primary key, x INT , y TEXT);
-        INSERT INTO t5 VALUES(1, 1, 'one');
-        INSERT INTO t5 VALUES(2, 1, 'two');
-        INSERT INTO t5 VALUES(3, 1.0, 'three');
-        INSERT INTO t5 VALUES(4, 1.0, 'four');
-    ]], {
-        -- <1.5>
-        
-        -- </1.5>
-    })
-
-test:do_execsql_test(
-    2.1,
-    [[
-        SELECT x FROM t1 WHERE x IN (1);
-    ]], {
-        -- <2.1>
-        "1"
-        -- </2.1>
-    })
-
-test:do_execsql_test(
-    2.2,
-    [[
-        SELECT x FROM t1 WHERE x IN (1.0);
-    ]], {
-        -- <2.2>
-        "1"
-        -- </2.2>
-    })
-
-test:do_execsql_test(
-    2.3,
-    [[
-        SELECT x FROM t1 WHERE x IN ('1');
-    ]], {
-        -- <2.3>
-        "1"
-        -- </2.3>
-    })
-
-test:do_execsql_test(
-    2.4,
-    [[
-        SELECT x FROM t1 WHERE x IN ('1.0');
-    ]], {
-        -- <2.4>
-        
-        -- </2.4>
-    })
-
-test:do_execsql_test(
-    2.5,
-    [[
-        SELECT x FROM t1 WHERE 1 IN (x);
-    ]], {
-        -- <2.5>
-        "1"
-        -- </2.5>
-    })
-
-test:do_execsql_test(
-    2.6,
-    [[
-        SELECT x FROM t1 WHERE 1.0 IN (x);
-    ]], {
-        -- <2.6>
-        "1"
-        -- </2.6>
-    })
-
-test:do_execsql_test(
-    2.7,
-    [[
-        SELECT x FROM t1 WHERE '1' IN (x);
-    ]], {
-        -- <2.7>
-        "1"
-        -- </2.7>
-    })
-
-test:do_execsql_test(
-    2.8,
-    [[
-        SELECT x FROM t1 WHERE '1.0' IN (x);
-    ]], {
-        -- <2.8>
-        
-        -- </2.8>
-    })
-
-test:do_execsql_test(
-    3.1,
-    [[
-        SELECT x FROM t2 WHERE x IN (1);
-    ]], {
-        -- <3.1>
-        1
-        -- </3.1>
-    })
-
-test:do_execsql_test(
-    3.2,
-    [[
-        SELECT x FROM t2 WHERE x IN (1.0);
-    ]], {
-        -- <3.2>
-        1
-        -- </3.2>
-    })
-
-test:do_execsql_test(
-    3.3,
-    [[
-        SELECT x FROM t2 WHERE x IN ('1');
-    ]], {
-        -- <3.3>
-        -- </3.3>
-    })
-
-test:do_execsql_test(
-    3.5,
-    [[
-        SELECT x FROM t2 WHERE 1 IN (x);
-    ]], {
-        -- <3.5>
-        1
-        -- </3.5>
-    })
-
-test:do_execsql_test(
-    3.6,
-    [[
-        SELECT x FROM t2 WHERE 1.0 IN (x);
-    ]], {
-        -- <3.6>
-        1
-        -- </3.6>
-    })
-
-test:do_execsql_test(
-    3.7,
-    [[
-        SELECT x FROM t2 WHERE '1' IN (x);
-    ]], {
-        -- <3.7>
-        -- </3.7>
-    })
-
-test:do_execsql_test(
-    4.1,
-    [[
-        SELECT x FROM t3 WHERE x IN (1);
-    ]], {
-        -- <4.1>
-        1.0
-        -- </4.1>
-    })
-
-test:do_execsql_test(
-    4.2,
-    [[
-        SELECT x FROM t3 WHERE x IN (1.0);
-    ]], {
-        -- <4.2>
-        1.0
-        -- </4.2>
-    })
-
-test:do_execsql_test(
-    4.3,
-    [[
-        SELECT x FROM t3 WHERE x IN ('1');
-    ]], {
-        -- <4.3>
-        1.0
-        -- </4.3>
-    })
-
-test:do_execsql_test(
-    4.4,
-    [[
-        SELECT x FROM t3 WHERE x IN ('1.0');
-    ]], {
-        -- <4.4>
-        1.0
-        -- </4.4>
-    })
-
-test:do_execsql_test(
-    4.5,
-    [[
-        SELECT x FROM t3 WHERE 1 IN (x);
-    ]], {
-        -- <4.5>
-        1.0
-        -- </4.5>
-    })
-
-test:do_execsql_test(
-    4.6,
-    [[
-        SELECT x FROM t3 WHERE 1.0 IN (x);
-    ]], {
-        -- <4.6>
-        1.0
-        -- </4.6>
-    })
-
-test:do_execsql_test(
-    4.7,
-    [[
-        SELECT x FROM t3 WHERE '1' IN (x);
-    ]], {
-        -- <4.7>
-        1
-        -- </4.7>
-    })
-
-test:do_execsql_test(
-    4.8,
-    [[
-        SELECT x FROM t3 WHERE '1.0' IN (x);
-    ]], {
-        -- <4.8>
-        1
-        -- </4.8>
-    })
-
-test:do_execsql_test(
-    5.1,
-    [[
-        SELECT x FROM t4 WHERE x IN (1);
-    ]], {
-        -- <5.1>
-        
-        -- </5.1>
-    })
-
-test:do_execsql_test(
-    5.2,
-    [[
-        SELECT x FROM t4 WHERE x IN (1.0);
-    ]], {
-        -- <5.2>
-        
-        -- </5.2>
-    })
-
-test:do_execsql_test(
-    5.3,
-    [[
-        SELECT x FROM t4 WHERE x IN ('1');
-    ]], {
-        -- <5.3>
-        
-        -- </5.3>
-    })
-
-test:do_execsql_test(
-    5.4,
-    [[
-        SELECT x FROM t4 WHERE x IN ('1.0');
-    ]], {
-        -- <5.4>
-        
-        -- </5.4>
-    })
-
-test:do_execsql_test(
-    5.5,
-    [[
-        SELECT x FROM t4 WHERE x IN (1.11);
-    ]], {
-        -- <5.5>
-        1.11
-        -- </5.5>
-    })
-
-test:do_execsql_test(
-    5.6,
-    [[
-        SELECT x FROM t4 WHERE x IN ('1.11');
-    ]], {
-        -- <5.6>
-        1.11
-        -- </5.6>
-    })
-
-test:do_execsql_test(
-    5.7,
-    [[
-        SELECT x FROM t4 WHERE 1 IN (x);
-    ]], {
-        -- <5.7>
-        
-        -- </5.7>
-    })
-
-test:do_execsql_test(
-    5.8,
-    [[
-        SELECT x FROM t4 WHERE 1.0 IN (x);
-    ]], {
-        -- <5.8>
-        
-        -- </5.8>
-    })
-
-test:do_execsql_test(
-    5.9,
-    [[
-        SELECT x FROM t4 WHERE '1' IN (x);
-    ]], {
-        -- <5.9>
-        
-        -- </5.9>
-    })
-
-test:do_execsql_test(
-    5.10,
-    [[
-        SELECT x FROM t4 WHERE '1.0' IN (x);
-    ]], {
-        -- <5.10>
-        
-        -- </5.10>
-    })
-
-test:do_execsql_test(
-    5.11,
-    [[
-        SELECT x FROM t4 WHERE 1.11 IN (x);
-    ]], {
-        -- <5.11>
-        1.11
-        -- </5.11>
-    })
-
-test:do_execsql_test(
-    5.12,
-    [[
-        SELECT x FROM t4 WHERE '1.11' IN (x);
-    ]], {
-        -- <5.12>
-        1.11
-        -- </5.12>
-    })
-
-test:do_execsql_test(
-    6.1,
-    [[
-        SELECT x, y FROM t5 WHERE x IN (1);
-    ]], {
-        -- <6.1>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
-        -- </6.1>
-    })
-
-test:do_execsql_test(
-    6.2,
-    [[
-        SELECT x, y FROM t5 WHERE x IN (1.0);
-    ]], {
-        -- <6.2>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
-        -- </6.2>
-    })
-
-test:do_execsql_test(
-    6.3,
-    [[
-        SELECT x, y FROM t5 WHERE x IN ('1');
-    ]], {
-        -- <6.3>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
-        -- </6.3>
-    })
-
-test:do_execsql_test(
-    6.4,
-    [[
-        SELECT x, y FROM t5 WHERE x IN ('1.0');
-    ]], {
-        -- <6.4>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
-        -- </6.4>
-    })
-
-test:do_execsql_test(
-    6.5,
-    [[
-        SELECT x, y FROM t5 WHERE 1 IN (x);
-    ]], {
-        -- <6.5>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
-        -- </6.5>
-    })
-
-test:do_execsql_test(
-    6.6,
-    [[
-        SELECT x, y FROM t5 WHERE 1.0 IN (x);
-    ]], {
-        -- <6.6>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
-        -- </6.6>
-    })
-
-test:do_execsql_test(
-    6.7,
-    [[
-        SELECT x, y FROM t5 WHERE '1' IN (x);
-    ]], {
-        -- <6.7>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
-        -- </6.7>
-    })
-
-test:do_execsql_test(
-    6.8,
-    [[
-        SELECT x, y FROM t5 WHERE '1.0' IN (x);
-    ]], {
-        -- <6.8>
-        1, "one", 1, "two", 1, "three", 1, "four"
-        -- </6.8>
-    })
-
-
-
-test:finish_test()
diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua
index 82bdb52f8..381f29c65 100755
--- a/test/sql-tap/tkt-f973c7ac31.test.lua
+++ b/test/sql-tap/tkt-f973c7ac31.test.lua
@@ -39,9 +39,8 @@ for tn, sql in ipairs(sqls) do
     test:do_execsql_test(
         "tkt-f973c7ac3-1."..tn..".1",
         [[
-            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 DESC 
+            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 DESC
         ]], {
-            
         })
 
     test:do_execsql_test(
@@ -55,7 +54,7 @@ for tn, sql in ipairs(sqls) do
     test:do_execsql_test(
         "tkt-f973c7ac3-1."..tn..".3",
         [[
-            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 DESC 
+            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC
         ]], {
             5, 5, 5, 4
         })
@@ -63,7 +62,7 @@ for tn, sql in ipairs(sqls) do
     test:do_execsql_test(
         "tkt-f973c7ac3-1."..tn..".4",
         [[
-            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 DESC 
+            SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 DESC
         ]], {
             5, 5, 5, 4
         })
@@ -71,7 +70,7 @@ for tn, sql in ipairs(sqls) do
     test:do_execsql_test(
         "tkt-f973c7ac3-1."..tn..".5",
         [[
-            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 DESC 
+            SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC
         ]], {
             5, 5, 5, 4
         })
@@ -79,9 +78,8 @@ for tn, sql in ipairs(sqls) do
     test:do_execsql_test(
         "tkt-f973c7ac3-1."..tn..".6",
         [[
-            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 ASC 
+            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 ASC
         ]], {
-            
         })
 
     test:do_execsql_test(
@@ -95,7 +93,7 @@ for tn, sql in ipairs(sqls) do
     test:do_execsql_test(
         "tkt-f973c7ac3-1."..tn..".8",
         [[
-            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 ASC 
+            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC
         ]], {
             5, 4, 5, 5
         })
@@ -103,7 +101,7 @@ for tn, sql in ipairs(sqls) do
     test:do_execsql_test(
         "tkt-f973c7ac3-1."..tn..".9",
         [[
-            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 ASC 
+            SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 ASC
         ]], {
             5, 4, 5, 5
         })
@@ -111,7 +109,7 @@ for tn, sql in ipairs(sqls) do
     test:do_execsql_test(
         "tkt-f973c7ac3-1."..tn..".10",
         [[
-            SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC 
+            SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC
         ]], {
             5, 4, 5, 5
         })
diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
index de77e61e9..82ba828d0 100755
--- a/test/sql-tap/tkt3493.test.lua
+++ b/test/sql-tap/tkt3493.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(26)
+test:plan(25)
 
 --!./tcltestrunner.lua
 -- 2008 October 13
@@ -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,23 +126,13 @@ 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
         -- </tkt3493-2.2.1>
     })
 
-test:do_execsql_test(
-    "tkt3493-2.2.2",
-    [[
-        SELECT a=123 FROM t1 
-    ]], {
-        -- <tkt3493-2.2.2>
-        true
-        -- </tkt3493-2.2.2>
-    })
-
 test:do_execsql_test(
     "tkt3493-2.2.3",
     [[
@@ -156,7 +146,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 +156,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 +166,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 +176,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 +186,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 +196,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 +206,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 +216,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 +226,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 96895b4a7..cc7e066bf 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"
@@ -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 f267be8e6..7348a855a 100755
--- a/test/sql-tap/where2.test.lua
+++ b/test/sql-tap/where2.test.lua
@@ -4,7 +4,7 @@ yaml = require("yaml")
 fio = require("fio")
 
 ffi = require("ffi")
-test:plan(74)
+test:plan(62)
 
 ffi.cdef[[
        int dup(int oldfd);
@@ -622,181 +622,12 @@ test:do_test(
         -- </where2-6.6>
     })
 
--- if X(356, "X!cmd", [=[["expr","[permutation] != \"no_optimization\""]]=])
--- then
-    -- Ticket #2249.  Make sure the OR optimization is not attempted if
-    -- comparisons between columns of different affinities are needed.
-    --
-    test:do_test(
-        "where2-6.7",
-        function()
-            test:execsql [[
-                CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100));
-                CREATE TABLE t2249b(b INTEGER PRIMARY KEY);
-                INSERT INTO t2249a(a) VALUES('0123');
-                INSERT INTO t2249b VALUES(123);
-            ]]
-            return queryplan([[
-    -- Because a is type TEXT and b is type INTEGER, both a and b
-    -- 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;
-  ]])
-        end, {
-            -- <where2-6.7>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.7>
-        })
-
-    test:do_test(
-        "where2-6.9",
-        function()
-            return queryplan([[
-    -- The + operator doesn't affect RHS.
-    --
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b;
-  ]])
-        end, {
-            -- <where2-6.9>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.9>
-        })
-
-    test:do_test(
-        "where2-6.9.2",
-        function()
-            -- The same thing but with the expression flipped around.
-            return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a
-  ]])
-        end, {
-            -- <where2-6.9.2>
-            123, "0123","nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.9.2>
-        })
-
-    test:do_test(
-        "where2-6.10",
-        function()
-            return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
-  ]])
-        end, {
-            -- <where2-6.10>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.10>
-        })
-
-    test:do_test(
-        "where2-6.11",
-        function()
-            -- 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';
-  ]])
-        end, {
-            -- <where2-6.11>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.11>
-        })
-
-    test:do_test(
-        "where2-6.11.2",
-        function()
-            -- Permutations of the expression terms.
-            return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
-  ]])
-        end, {
-            -- <where2-6.11.2>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.11.2>
-        })
-
-    test:do_test(
-        "where2-6.11.3",
-        function()
-            -- Permutations of the expression terms.
-            return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
-  ]])
-        end, {
-            -- <where2-6.11.3>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.11.3>
-        })
-
-    test:do_test(
-        "where2-6.11.4",
-        function()
-            -- Permutations of the expression terms.
-            return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
-  ]])
-        end, {
-            -- <where2-6.11.4>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.11.4>
-        })
-
-    -- These tests are not run if subquery support is not included in the
-    -- build. This is because these tests test the "a = 1 OR a = 2" to
-    -- "a IN (1, 2)" optimisation transformation, which is not enabled if
-    -- subqueries and the IN operator is not available.
-    --
-    test:do_test(
-        "where2-6.12",
-        function()
-            return queryplan([[
-      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
-    ]])
-        end, {
-            -- <where2-6.12>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.12>
-        })
-
-    test:do_test(
-        "where2-6.12.2",
-        function()
-            return queryplan([[
-      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
-    ]])
-        end, {
-            -- <where2-6.12.2>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.12.2>
-        })
-
-    test:do_test(
-        "where2-6.12.3",
-        function()
-            return queryplan([[
-      SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
-    ]])
-        end, {
-            -- <where2-6.12.3>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.12.3>
-        })
-
-    test:do_test(
-        "where2-6.13",
-        function()
-            -- 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';
-    ]])
-        end, {
-            -- <where2-6.13>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.13>
-        })
-
-
+    test:execsql [[
+        CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100));
+        CREATE TABLE t2249b(b INTEGER PRIMARY KEY);
+        INSERT INTO t2249a(a) VALUES('0123');
+        INSERT INTO t2249b VALUES(123);
+    ]]
 
     -- Variations on the order of terms in a WHERE clause in order
     -- to make sure the OR optimizer can recognize them all.
diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua
index 3aefcaca5..a93ba7854 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 CAST(x AS INTEGER) 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/types.result b/test/sql/types.result
index 54aff460e..1c8357bce 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -339,12 +339,12 @@ box.execute("INSERT INTO tboolean VALUES (TRUE);")
 box.execute("SELECT * FROM tboolean WHERE s1 = x'44';")
 ---
 - null
-- 'Type mismatch: can not convert varbinary to boolean'
+- 'Type mismatch: can not convert boolean to varbinary'
 ...
 box.execute("SELECT * FROM tboolean WHERE s1 = 'abc';")
 ---
 - null
-- 'Type mismatch: can not convert abc to boolean'
+- 'Type mismatch: can not convert boolean to text'
 ...
 box.execute("SELECT * FROM tboolean WHERE s1 = 1;")
 ---
@@ -606,14 +606,6 @@ box.execute("SELECT 18446744073709551615.0 > 18446744073709551615")
   rows:
   - [true]
 ...
-box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)")
----
-- metadata:
-  - name: 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)
-    type: boolean
-  rows:
-  - [true]
-...
 box.execute("SELECT 1 LIMIT 18446744073709551615;")
 ---
 - metadata:
@@ -2152,6 +2144,231 @@ box.execute([[SELECT * FROM "s" WHERE "id" = ?;]])
     type: string
   rows: []
 ...
+--
+-- gh-4230: Make sure that there is no implicit cast between
+-- string and number.
+--
+box.execute([[SELECT '1' > 0;]]);
+---
+- null
+- 'Type mismatch: can not convert 1 to numeric'
+...
+box.execute([[SELECT 1 > '0';]]);
+---
+- null
+- 'Type mismatch: can not convert 0 to numeric'
+...
+box.execute([[CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');]])
+---
+- row_count: 2
+...
+box.execute([[SELECT * from t WHERE i > s;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE s > i;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE d > s;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE s > d;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE i = 1 and n > s;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT * from t WHERE i = 2 and s > n;]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE i in (1);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE d in (1);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE n in (1);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE s in (1);]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE i in (1.0);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE d in (1.0);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE n in (1.0);]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows:
+  - [1]
+...
+box.execute([[SELECT i FROM t WHERE s in (1.0);]])
+---
+- null
+- 'Type mismatch: can not convert 2 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE i in ('1');]])
+---
+- null
+- 'Type mismatch: can not convert integer to text'
+...
+box.execute([[SELECT i FROM t WHERE d in ('1');]])
+---
+- null
+- 'Type mismatch: can not convert 1 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE n in ('1');]])
+---
+- null
+- 'Type mismatch: can not convert 1 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE s in ('1');]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows: []
+...
+box.execute([[SELECT i FROM t WHERE i in ('1.0');]])
+---
+- null
+- 'Type mismatch: can not convert integer to text'
+...
+box.execute([[SELECT i FROM t WHERE d in ('1.0');]])
+---
+- null
+- 'Type mismatch: can not convert 1.0 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE n in ('1.0');]])
+---
+- null
+- 'Type mismatch: can not convert 1.0 to numeric'
+...
+box.execute([[SELECT i FROM t WHERE s in ('1.0');]])
+---
+- metadata:
+  - name: I
+    type: integer
+  rows: []
+...
+box.execute([[DROP TABLE t;]])
+---
+- row_count: 1
+...
+-- Comparison with SCALAR.
+box.execute([[CREATE TABLE t(a SCALAR PRIMARY KEY);]])
+---
+- row_count: 1
+...
+box.execute([[INSERT INTO t VALUES (1), (2.2), ('3');]]);
+---
+- row_count: 3
+...
+box.execute([[SELECT a FROM t WHERE a > 1]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a > 1.0]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a > '1']]);
+---
+- metadata:
+  - name: A
+    type: scalar
+  rows:
+  - ['3']
+...
+box.execute([[SELECT a FROM t WHERE a < 1]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a < 1.0]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a < '1']]);
+---
+- metadata:
+  - name: A
+    type: scalar
+  rows:
+  - [1]
+  - [2.2]
+...
+box.execute([[SELECT a FROM t WHERE a = 1]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a = 1.0]]);
+---
+- null
+- 'Type mismatch: can not convert 3 to numeric'
+...
+box.execute([[SELECT a FROM t WHERE a = '1']]);
+---
+- metadata:
+  - name: A
+    type: scalar
+  rows: []
+...
+box.execute([[DROP TABLE t;]])
+---
+- row_count: 1
+...
 s:drop()
 ---
 ...
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index bd14b342d..cf0db5ae0 100644
--- a/test/sql/types.test.lua
+++ b/test/sql/types.test.lua
@@ -151,7 +151,6 @@ 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 1 LIMIT 18446744073709551615;")
 box.execute("SELECT 1 LIMIT 1 OFFSET 18446744073709551614;")
 box.execute("SELECT CAST('18446744073' || '709551616' AS INTEGER);")
@@ -486,4 +485,56 @@ s:format({ \
 })
 
 box.execute([[SELECT * FROM "s" WHERE "id" = ?;]])
+
+--
+-- gh-4230: Make sure that there is no implicit cast between
+-- string and number.
+--
+box.execute([[SELECT '1' > 0;]]);
+box.execute([[SELECT 1 > '0';]]);
+box.execute([[CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);]])
+box.execute([[INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');]])
+box.execute([[SELECT * from t WHERE i > s;]])
+box.execute([[SELECT * from t WHERE s > i;]])
+box.execute([[SELECT * from t WHERE d > s;]])
+box.execute([[SELECT * from t WHERE s > d;]])
+box.execute([[SELECT * from t WHERE i = 1 and n > s;]])
+box.execute([[SELECT * from t WHERE i = 2 and s > n;]])
+
+box.execute([[SELECT i FROM t WHERE i in (1);]])
+box.execute([[SELECT i FROM t WHERE d in (1);]])
+box.execute([[SELECT i FROM t WHERE n in (1);]])
+box.execute([[SELECT i FROM t WHERE s in (1);]])
+
+box.execute([[SELECT i FROM t WHERE i in (1.0);]])
+box.execute([[SELECT i FROM t WHERE d in (1.0);]])
+box.execute([[SELECT i FROM t WHERE n in (1.0);]])
+box.execute([[SELECT i FROM t WHERE s in (1.0);]])
+
+box.execute([[SELECT i FROM t WHERE i in ('1');]])
+box.execute([[SELECT i FROM t WHERE d in ('1');]])
+box.execute([[SELECT i FROM t WHERE n in ('1');]])
+box.execute([[SELECT i FROM t WHERE s in ('1');]])
+
+box.execute([[SELECT i FROM t WHERE i in ('1.0');]])
+box.execute([[SELECT i FROM t WHERE d in ('1.0');]])
+box.execute([[SELECT i FROM t WHERE n in ('1.0');]])
+box.execute([[SELECT i FROM t WHERE s in ('1.0');]])
+
+box.execute([[DROP TABLE t;]])
+
+-- Comparison with SCALAR.
+box.execute([[CREATE TABLE t(a SCALAR PRIMARY KEY);]])
+box.execute([[INSERT INTO t VALUES (1), (2.2), ('3');]]);
+box.execute([[SELECT a FROM t WHERE a > 1]]);
+box.execute([[SELECT a FROM t WHERE a > 1.0]]);
+box.execute([[SELECT a FROM t WHERE a > '1']]);
+box.execute([[SELECT a FROM t WHERE a < 1]]);
+box.execute([[SELECT a FROM t WHERE a < 1.0]]);
+box.execute([[SELECT a FROM t WHERE a < '1']]);
+box.execute([[SELECT a FROM t WHERE a = 1]]);
+box.execute([[SELECT a FROM t WHERE a = 1.0]]);
+box.execute([[SELECT a FROM t WHERE a = '1']]);
+box.execute([[DROP TABLE t;]])
+
 s:drop()

  reply	other threads:[~2020-07-05 14:47 UTC|newest]

Thread overview: 32+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2020-06-25 15:17 [Tarantool-patches] [PATCH v3 0/8] Remove implicit cast imeevma
2020-06-25 15:17 ` [Tarantool-patches] [PATCH v3 1/8] sql: introduce mem_set_double() imeevma
2020-06-28 13:31   ` Nikita Pettik
2020-07-06 14:02     ` Nikita Pettik
2020-06-25 15:17 ` [Tarantool-patches] [PATCH v3 2/8] sql: change implicit cast for assignment imeevma
2020-06-30 11:50   ` Nikita Pettik
2020-07-05 14:26     ` Mergen Imeev
2020-07-06 21:27       ` Nikita Pettik
2020-07-07  9:29         ` Mergen Imeev
2020-07-07 15:35           ` Nikita Pettik
2020-07-10 10:49           ` Nikita Pettik
2020-06-25 15:17 ` [Tarantool-patches] [PATCH v3 3/8] sql: remove mem_apply_type() from OP_MakeRecord imeevma
2020-06-25 15:17 ` [Tarantool-patches] [PATCH v3 4/8] sql: replace ApplyType by CheckType for IN operator imeevma
2020-06-29 12:56   ` Nikita Pettik
2020-07-05 14:28     ` Mergen Imeev
2020-07-06 22:06       ` Nikita Pettik
2020-07-07 11:26         ` Mergen Imeev
2020-07-07 16:29           ` Nikita Pettik
2020-06-25 15:17 ` [Tarantool-patches] [PATCH v3 5/8] sql: remove mem_apply_type() from OP_MustBeInt imeevma
2020-06-29 13:29   ` Nikita Pettik
2020-07-05 14:29     ` Mergen Imeev
2020-06-25 15:17 ` [Tarantool-patches] [PATCH v3 6/8] sql: remove implicit cast for comparison imeevma
2020-06-29 23:51   ` Nikita Pettik
2020-07-05 14:47     ` Mergen Imeev [this message]
2020-07-06 23:11       ` Nikita Pettik
2020-06-25 15:17 ` [Tarantool-patches] [PATCH v3 7/8] sql: remove unused functions imeevma
2020-06-29 23:52   ` Nikita Pettik
2020-07-05 14:50     ` Mergen Imeev
2020-06-25 15:17 ` [Tarantool-patches] [PATCH v3 8/8] sql: show value and its type in type mismatch error imeevma
2020-06-30  0:22   ` Nikita Pettik
2020-07-05 15:03     ` Mergen Imeev
2020-07-06 21:44       ` Nikita Pettik

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=20200705144720.GD135859@tarantool.org \
    --to=imeevma@tarantool.org \
    --cc=korablev@tarantool.org \
    --cc=tarantool-patches@dev.tarantool.org \
    --subject='Re: [Tarantool-patches] [PATCH v3 6/8] 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