Tarantool development patches archive
 help / color / mirror / Atom feed
From: Mergen Imeev <imeevma@tarantool.org>
To: Vladislav Shpilevoy <v.shpilevoy@tarantool.org>,
	tsafin@tarantool.org, tarantool-patches@dev.tarantool.org
Subject: Re: [Tarantool-patches] [PATCH 5/6] sql: remove implicit cast from string for comparison
Date: Tue, 9 Jun 2020 14:51:26 +0300	[thread overview]
Message-ID: <c4b2d4ee-9052-1c9d-e8bd-e67071cd50ed@tarantool.org> (raw)
In-Reply-To: <379debee-67fc-f870-9e0b-032a5188d8a4@tarantool.org>

Thanks for the review! My answers and new patch below.


On 01.06.2020 20:04, Vladislav Shpilevoy wrote:
> Thanks for the patch!
>
> See 7 comments below.
>
> On 28/05/2020 16:17, Mergen Imeev wrote:
>> This patch removes implicit cast from strings to numbers for
>> comparison.
>>
>> Closes #4230
>>
>> @TarantoolBot document
>> Title: remove implicit cast between strings and numbers
>>
>> This patch-set removes implicit cast from string to number and
>> from number to string.
>>
>> Example:
>>
>> For comparison:
>>
>> tarantool> box.execute([[SELECT '1' > 0;]])
>> ---
>> - null
>> - 'Type mismatch: can not convert 1 to numeric'
>> ...
>>
>> tarantool> box.execute([[SELECT "id" FROM "_space" WHERE '1' > "id";]])
>> ---
>> - null
>> - 'Type mismatch: can not convert text to unsigned'
>> ...
>>
>> For assignment:
>>
>> tarantool> box.execute([[CREATE TABLE t1(i INT PRIMARY KEY);]])
>> tarantool> box.execute([[INSERT INTO t1 VALUES ('1');]])
>> ---
>> - null
>> - 'Type mismatch: can not convert text to integer'
>> ...
>>
>> tarantool> box.execute([[CREATE TABLE t2(t text PRIMARY KEY);]])
>> tarantool> box.execute([[INSERT INTO t2 VALUES (1);]])
>> ---
>> - null
>> - 'Type mismatch: can not convert unsigned to string'
>> ...
>> ---
>>   src/box/sql/vdbe.c                            | 105 ++++----
>>   src/box/sql/wherecode.c                       | 203 +--------------
>>   .../gh-4230-del-impl-cast-str-to-num.test.lua |  78 ++++++
>>   test/sql-tap/identifier_case.test.lua         |   6 +-
>>   test/sql-tap/in1.test.lua                     |   4 +-
>>   test/sql-tap/in4.test.lua                     |   3 +-
>>   test/sql-tap/insert3.test.lua                 |   2 +-
>>   test/sql-tap/intpkey.test.lua                 |  24 +-
>>   test/sql-tap/join.test.lua                    |   7 +-
>>   test/sql-tap/misc1.test.lua                   |  32 +--
>>   test/sql-tap/select1.test.lua                 |   4 +-
>>   test/sql-tap/select7.test.lua                 |   2 +-
>>   test/sql-tap/subquery.test.lua                |   4 +-
>>   test/sql-tap/tkt-9a8b09f8e6.test.lua          |  84 ++++---
>>   test/sql-tap/tkt-f973c7ac31.test.lua          |  32 +--
>>   test/sql-tap/tkt-fc7bd6358f.test.lua          |   6 +-
>>   test/sql-tap/tkt3493.test.lua                 |  40 ++-
>>   test/sql-tap/transitive1.test.lua             |  12 +-
>>   test/sql-tap/where2.test.lua                  | 183 +-------------
>>   test/sql-tap/where5.test.lua                  |  12 +-
>>   test/sql-tap/whereB.test.lua                  | 238 ++++++++++--------
>>   test/sql/types.result                         |  12 +-
>>   test/sql/types.test.lua                       |   1 -
>>   23 files changed, 418 insertions(+), 676 deletions(-)
>>   create mode 100755 test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
>>
>> diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
>> index 7add67ae7..021e09d1e 100644
>> --- a/src/box/sql/vdbe.c
>> +++ b/src/box/sql/vdbe.c
>> @@ -2345,22 +2338,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_Int | MEM_UInt | MEM_Real)) != 0) {
> 1. Maybe it would be better to check '(flags1 & MEM_Str) == 0'. Otherwise
> every time when we will add a new type, we will need to add it here. The
> same below.

Thanks, fixed.


>
>> +				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_Int | MEM_UInt | MEM_Real)) != 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);
>> diff --git a/src/box/sql/wherecode.c b/src/box/sql/wherecode.c
>> index 5bc27f134..1d7c76670 100644
>> --- a/src/box/sql/wherecode.c
>> +++ b/src/box/sql/wherecode.c
>> @@ -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);
> 2. This function is now unused and can be deleted.

Removed.


>
>> -	assert(type != NULL || pParse->db->mallocFailed);
>> -
>>   	if (nSkip) {
>>   		int iIdxCur = pLevel->iIdxCur;
>>   		sqlVdbeAddOp1(v, (bRev ? OP_Last : OP_Rewind), iIdxCur);> diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua
>> index 854ed774f..2a18b17be 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 1 to numeric"
> 3. Can tests in this file be fixed to return the same results
> as before?
I fixed this in most cases, but am not sure if this is correct.
I think most tests are out of date.

>
>>           -- </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 1 to numeric"
>>           -- </2.2>
>>       })
>>   
>> diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua
>> index 82bdb52f8..5239a7785 100755
>> --- a/test/sql-tap/tkt-f973c7ac31.test.lua
>> +++ b/test/sql-tap/tkt-f973c7ac31.test.lua
>> @@ -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/"
> 4. The same for this file.

Fixed, used CAST().


>
>>           })
>>   
>>       test:do_execsql_test(
>> diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
>> index de77e61e9..ec12a4492 100755
>> --- a/test/sql-tap/tkt3493.test.lua
>> +++ b/test/sql-tap/tkt3493.test.lua
>> @@ -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
> 5. Trailing whitespace.

Removed.


>
>>       ]], {
>>           -- <tkt3493-2.2.1>
>>           true
>>           -- </tkt3493-2.2.1>
>>       })
>> @@ -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
> 6. Trailing whitespace here and in the test above.

Removed.


>
>>       ]], {
>>           -- <tkt3493-2.2.5>
>>           1, true
>> diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua
>> index 970ff1dec..9e99ea41c 100755
>> --- a/test/sql-tap/whereB.test.lua
>> +++ b/test/sql-tap/whereB.test.lua
>> @@ -36,49 +35,54 @@ test:do_execsql_test(
>>           CREATE TABLE t2(a  INT primary key, b TEXT);  -- affinity of t2.b is TEXT
>>           CREATE INDEX t2b ON t2(b);
>>           INSERT INTO t2 VALUES(2,'99');
>> +    ]]
>> +)
>>   
>> +test:do_catchsql_test(
>> +    "whereB-1.1",
> 7. The whole whereB-1 now fails because of type conversions banned.
> Is this test still needed at all? I didn't check other whereB tests,
> but seems like most of them also fail now. So either almost the entire
> test file is not needed, or it should be fixed so as the test still
> would work.

Removed whereB.test.lua in the previous patch.


New patch:


 From 5c3feffdfd6837438de4b7ce3d728b356bb4be8a Mon Sep 17 00:00:00 2001
From: Mergen Imeev <imeevma@gmail.com>
Date: Thu, 16 Apr 2020 12:52:59 +0300
Subject: [PATCH] sql: remove implicit cast from string for comparison

This patch removes implicit cast from strings to numbers for
comparison.

Closes #4230

@TarantoolBot document
Title: remove implicit cast between strings and numbers

This patch-set removes implicit cast from string to number and
from number to string.

Example:

For comparison:

tarantool> box.execute([[SELECT '1' > 0;]])
---
- null
- 'Type mismatch: can not convert 1 to numeric'
...

tarantool> box.execute([[SELECT "id" FROM "_space" WHERE '1' > "id";]])
---
- null
- 'Type mismatch: can not convert text to unsigned'
...

For assignment:

tarantool> box.execute([[CREATE TABLE t1(i INT PRIMARY KEY);]])
tarantool> box.execute([[INSERT INTO t1 VALUES ('1');]])
---
- null
- 'Type mismatch: can not convert text to integer'
...

tarantool> box.execute([[CREATE TABLE t2(t text PRIMARY KEY);]])
tarantool> box.execute([[INSERT INTO t2 VALUES (1);]])
---
- null
- 'Type mismatch: can not convert unsigned to string'
...

review fix

diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 253304ba3..7b9cda504 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -41,20 +41,6 @@
  #include "box/box.h"
  #include "box/schema.h"

-enum field_type *
-sql_index_type_str(struct sql *db, const struct index_def *idx_def)
-{
-    uint32_t column_count = idx_def->key_def->part_count;
-    uint32_t sz = (column_count + 1) * sizeof(enum field_type);
-    enum field_type *types = (enum field_type *) sqlDbMallocRaw(db, sz);
-    if (types == NULL)
-        return NULL;
-    for (uint32_t i = 0; i < column_count; i++)
-        types[i] = idx_def->key_def->parts[i].type;
-    types[column_count] = field_type_MAX;
-    return types;
-}
-
  void
  sql_emit_table_types(struct Vdbe *v, struct space_def *def, int reg)
  {
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 37283e506..01c32d518 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -3866,10 +3866,6 @@ int sqlVarintLen(u64 v);
  #define getVarint    sqlGetVarint
  #define putVarint    sqlPutVarint

-/** Return string consisting of fields types of given index. */
-enum field_type *
-sql_index_type_str(struct sql *db, const struct index_def *idx_def);
-
  /**
   * Code an OP_ApplyType opcode that will force types
   * for given range of register starting from @reg.
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 77b758833..a92c9f60f 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -2260,8 +2260,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) {
@@ -2320,22 +2318,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()
@@ -2368,22 +2361,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);
@@ -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;
-
      if (pOp->p5 & SQL_STOREP2) {
          iCompare = res;
          res2 = res2!=0;  /* For this path res2 must be exactly 0 or 1 */
@@ -3496,8 +3476,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;
@@ -3590,6 +3568,26 @@ skip_truncate:
      assert(oc!=OP_SeekLT || r.default_rc==+1);

      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->flags & MEM_Str) != 0 && sql_type_is_numeric(type)) {
+            diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+                field_type_strs[type], mem_type_to_str(mem));
+            goto abort_due_to_error;
+        }
+        if (mem_check_types(mem, type) == 0)
+            continue;
+        if ((mem->flags & MEM_Real) != 0 &&
+            (type == FIELD_TYPE_INTEGER ||
+             type == FIELD_TYPE_UNSIGNED)) {
+            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
@@ -4717,6 +4715,27 @@ case OP_IdxGE:  {       /* jump */
          r.default_rc = 0;
      }
      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) ||
+            !(mp_type == MP_INT || mp_type == MP_UINT ||
+              mp_type == MP_DOUBLE || mp_type == MP_FLOAT)) {
+            diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+                 mem_type_to_str(mem),
+                 field_type_strs[field_type]);
+            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/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..ef4127e0e
--- /dev/null
+++ b/test/sql-tap/gh-4230-del-impl-cast-str-to-num.test.lua
@@ -0,0 +1,78 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(8)
+
+--
+-- Make sure that there is no implicit cast between string and
+-- number.
+--
+test:do_catchsql_test(
+    "gh-4230-1",
+    [[
+        SELECT '1' > 0;
+    ]], {
+        1, "Type mismatch: can not convert 1 to numeric"
+    })
+
+test:do_catchsql_test(
+    "gh-4230-2",
+    [[
+        SELECT 0 > '1';
+    ]], {
+        1, "Type mismatch: can not convert 1 to numeric"
+    })
+
+test:execsql([[
+        CREATE TABLE t (i INT PRIMARY KEY, d DOUBLE, n NUMBER, s STRING);
+        INSERT INTO t VALUES (1, 1.0, 1, '2'), (2, 2.0, 2.0, '2');
+    ]])
+
+test:do_catchsql_test(
+    "gh-4230-3",
+    [[
+        SELECT * from t where i > s;
+    ]], {
+        1, "Type mismatch: can not convert 2 to numeric"
+    })
+
+test:do_catchsql_test(
+    "gh-4230-4",
+    [[
+        SELECT * from t WHERE s > i;
+    ]], {
+        1, "Type mismatch: can not convert 2 to numeric"
+    })
+
+test:do_catchsql_test(
+    "gh-4230-5",
+    [[
+        SELECT * from t WHERE d > s;
+    ]], {
+        1, "Type mismatch: can not convert 2 to numeric"
+    })
+
+test:do_catchsql_test(
+    "gh-4230-6",
+    [[
+        SELECT * from t WHERE s > d;
+    ]], {
+        1, "Type mismatch: can not convert 2 to numeric"
+    })
+
+test:do_catchsql_test(
+    "gh-4230-7",
+    [[
+        SELECT * from t WHERE i = 1 and n > s;
+    ]], {
+        1, "Type mismatch: can not convert 2 to numeric"
+    })
+
+test:do_catchsql_test(
+    "gh-4230-8",
+    [[
+        SELECT * from t WHERE i = 2 and s > n;
+    ]], {
+        1, "Type mismatch: can not convert 2 to numeric"
+    })
+
+test:finish_test()
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/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
index 0db18ba91..bc3d701a7 100755
--- a/test/sql-tap/intpkey.test.lua
+++ b/test/sql-tap/intpkey.test.lua
@@ -1,6 +1,6 @@
  #!/usr/bin/env tarantool
  test = require("sqltester")
-test:plan(40)
+test:plan(39)

  --!./tcltestrunner.lua
  -- 2001 September 15
@@ -854,43 +854,33 @@ test:do_execsql_test(
          -- </intpkey-14.2>
      })

-test:do_execsql_test(
-    "intpkey-14.3",
-    [[
-        SELECT * FROM t3 WHERE a>'2';
-    ]], {
-        -- <intpkey-14.3>
-        3, 3, "3"
-        -- </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(
+test:do_catchsql_test(
      "intpkey-14.5",
      [[
          SELECT * FROM t3 WHERE a<c;
      ]], {
          -- <intpkey-14.5>
-        1, 1, "one"
+        1, "Type mismatch: can not convert one to numeric"
          -- </intpkey-14.5>
      })

-test:do_execsql_test(
+test:do_catchsql_test(
      "intpkey-14.6",
      [[
          SELECT * FROM t3 WHERE a=c;
      ]], {
          -- <intpkey-14.6>
-        2, 2, "2", 3, 3, "3"
+        1, "Type mismatch: can not convert one to numeric"
          -- </intpkey-14.6>
      })

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
index 854ed774f..083a87b45 100755
--- a/test/sql-tap/tkt-9a8b09f8e6.test.lua
+++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua
@@ -1,6 +1,6 @@
  #!/usr/bin/env tarantool
  test = require("sqltester")
-test:plan(49)
+test:plan(47)

  --!./tcltestrunner.lua
  -- 2014 June 26
@@ -86,7 +86,7 @@ test:do_execsql_test(
  test:do_execsql_test(
      2.1,
      [[
-        SELECT x FROM t1 WHERE x IN (1);
+        SELECT x FROM t1 WHERE CAST(x AS INTEGER) IN (1);
      ]], {
          -- <2.1>
          "1"
@@ -96,7 +96,7 @@ test:do_execsql_test(
  test:do_execsql_test(
      2.2,
      [[
-        SELECT x FROM t1 WHERE x IN (1.0);
+        SELECT x FROM t1 WHERE CAST(x AS DOUBLE) IN (1);
      ]], {
          -- <2.2>
          "1"
@@ -126,7 +126,7 @@ test:do_execsql_test(
  test:do_execsql_test(
      2.5,
      [[
-        SELECT x FROM t1 WHERE 1 IN (x);
+        SELECT x FROM t1 WHERE CAST(1 AS STRING) IN (x);
      ]], {
          -- <2.5>
          "1"
@@ -136,10 +136,9 @@ test:do_execsql_test(
  test:do_execsql_test(
      2.6,
      [[
-        SELECT x FROM t1 WHERE 1.0 IN (x);
+        SELECT x FROM t1 WHERE CAST(1.0 AS STRING) IN (x);
      ]], {
          -- <2.6>
-        "1"
          -- </2.6>
      })

@@ -186,21 +185,13 @@ test:do_execsql_test(
  test:do_execsql_test(
      3.3,
      [[
-        SELECT x FROM t2 WHERE x IN ('1');
+        SELECT x FROM t2 WHERE CAST(x AS STRING) IN ('1');
      ]], {
          -- <3.3>
+        1
          -- </3.3>
      })

-test:do_execsql_test(
-    3.4,
-    [[
-        SELECT x FROM t2 WHERE x IN ('1');
-    ]], {
-        -- <3.4>
-        -- </3.4>
-    })
-
  test:do_execsql_test(
      3.5,
      [[
@@ -224,21 +215,13 @@ test:do_execsql_test(
  test:do_execsql_test(
      3.7,
      [[
-        SELECT x FROM t2 WHERE '1' IN (x);
+        SELECT x FROM t2 WHERE CAST('1' AS INTEGER) IN (x);
      ]], {
          -- <3.7>
+        1
          -- </3.7>
      })

-test:do_execsql_test(
-    3.8,
-    [[
-        SELECT x FROM t2 WHERE '1' IN (x);
-    ]], {
-        -- <3.8>
-        -- </3.8>
-    })
-
  test:do_execsql_test(
      4.1,
      [[
@@ -262,17 +245,16 @@ test:do_execsql_test(
  test:do_execsql_test(
      4.3,
      [[
-        SELECT x FROM t3 WHERE x IN ('1');
+        SELECT x FROM t3 WHERE CAST(x AS STRING) IN ('1');
      ]], {
          -- <4.3>
-        1.0
          -- </4.3>
      })

  test:do_execsql_test(
      4.4,
      [[
-        SELECT x FROM t3 WHERE x IN ('1.0');
+        SELECT x FROM t3 WHERE CAST(x AS STRING) IN ('1.0');
      ]], {
          -- <4.4>
          1.0
@@ -302,20 +284,20 @@ test:do_execsql_test(
  test:do_execsql_test(
      4.7,
      [[
-        SELECT x FROM t3 WHERE '1' IN (x);
+        SELECT x FROM t3 WHERE CAST('1' AS DOUBLE) IN (x);
      ]], {
          -- <4.7>
-        1
+        1.0
          -- </4.7>
      })

  test:do_execsql_test(
      4.8,
      [[
-        SELECT x FROM t3 WHERE '1.0' IN (x);
+        SELECT x FROM t3 WHERE CAST('1.0' AS DOUBLE) IN (x);
      ]], {
          -- <4.8>
-        1
+        1.0
          -- </4.8>
      })

@@ -342,20 +324,20 @@ test:do_execsql_test(
  test:do_execsql_test(
      5.3,
      [[
-        SELECT x FROM t4 WHERE x IN ('1');
+        SELECT x FROM t4 WHERE CAST(x AS STRING) IN ('1');
      ]], {
          -- <5.3>
-
+
          -- </5.3>
      })

  test:do_execsql_test(
      5.4,
      [[
-        SELECT x FROM t4 WHERE x IN ('1.0');
+        SELECT x FROM t4 WHERE CAST(x AS STRING) IN ('1.0');
      ]], {
          -- <5.4>
-
+
          -- </5.4>
      })

@@ -372,7 +354,7 @@ test:do_execsql_test(
  test:do_execsql_test(
      5.6,
      [[
-        SELECT x FROM t4 WHERE x IN ('1.11');
+        SELECT x FROM t4 WHERE CAST(x AS STRING) IN ('1.11');
      ]], {
          -- <5.6>
          1.11
@@ -402,20 +384,20 @@ test:do_execsql_test(
  test:do_execsql_test(
      5.9,
      [[
-        SELECT x FROM t4 WHERE '1' IN (x);
+        SELECT x FROM t4 WHERE CAST('1' AS DOUBLE) IN (x);
      ]], {
          -- <5.9>
-
+
          -- </5.9>
      })

  test:do_execsql_test(
      5.10,
      [[
-        SELECT x FROM t4 WHERE '1.0' IN (x);
+        SELECT x FROM t4 WHERE CAST('1.0' AS DOUBLE) IN (x);
      ]], {
          -- <5.10>
-
+
          -- </5.10>
      })

@@ -432,7 +414,7 @@ test:do_execsql_test(
  test:do_execsql_test(
      5.12,
      [[
-        SELECT x FROM t4 WHERE '1.11' IN (x);
+        SELECT x FROM t4 WHERE CAST('1.11' AS DOUBLE) IN (x);
      ]], {
          -- <5.12>
          1.11
@@ -462,7 +444,7 @@ test:do_execsql_test(
  test:do_execsql_test(
      6.3,
      [[
-        SELECT x, y FROM t5 WHERE x IN ('1');
+        SELECT x, y FROM t5 WHERE CAST(x AS STRING) IN ('1');
      ]], {
          -- <6.3>
          1, "one", 1, "two", 1, "three", 1.0, "four"
@@ -472,10 +454,10 @@ test:do_execsql_test(
  test:do_execsql_test(
      6.4,
      [[
-        SELECT x, y FROM t5 WHERE x IN ('1.0');
+        SELECT x, y FROM t5 WHERE CAST(x AS STRING) IN ('1.0');
      ]], {
          -- <6.4>
-        1, "one", 1, "two", 1, "three", 1.0, "four"
+
          -- </6.4>
      })

@@ -502,7 +484,7 @@ test:do_execsql_test(
  test:do_execsql_test(
      6.7,
      [[
-        SELECT x, y FROM t5 WHERE '1' IN (x);
+        SELECT x, y FROM t5 WHERE CAST('1' AS INTEGER) IN (x);
      ]], {
          -- <6.7>
          1, "one", 1, "two", 1, "three", 1.0, "four"
@@ -512,7 +494,7 @@ test:do_execsql_test(
  test:do_execsql_test(
      6.8,
      [[
-        SELECT x, y FROM t5 WHERE '1.0' IN (x);
+        SELECT x, y FROM t5 WHERE CAST('1.0' AS DOUBLE) IN (x);
      ]], {
          -- <6.8>
          1, "one", 1, "two", 1, "three", 1, "four"
diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua 
b/test/sql-tap/tkt-f973c7ac31.test.lua
index 82bdb52f8..604a7e6bb 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 6b0f7a651..9099bcea4 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:
diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua
index bd14b342d..f9603c60d 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);")

  reply	other threads:[~2020-06-09 11:51 UTC|newest]

Thread overview: 21+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2020-05-28 14:17 [Tarantool-patches] [PATCH 0/6] Remove implicit cast Mergen Imeev
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 1/6] sql: remove implicit cast for assignment Mergen Imeev
2020-06-01 17:03   ` Vladislav Shpilevoy
2020-06-09 11:41     ` Mergen Imeev
2020-06-09 22:28       ` Vladislav Shpilevoy
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 2/6] sql: remove mem_apply_type() from OP_MakeRecord Mergen Imeev
2020-06-01 17:03   ` Vladislav Shpilevoy
2020-06-09 11:43     ` Mergen Imeev
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 3/6] sql: replace ApplyType by CheckType for IN operator Mergen Imeev
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 4/6] sql: remove mem_apply_type() from OP_MustBeInt Mergen Imeev
2020-06-01 17:04   ` Vladislav Shpilevoy
2020-06-09 11:47     ` Mergen Imeev
2020-06-09 22:28       ` Vladislav Shpilevoy
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 5/6] sql: remove implicit cast from string for comparison Mergen Imeev
2020-06-01 17:04   ` Vladislav Shpilevoy
2020-06-09 11:51     ` Mergen Imeev [this message]
2020-06-09 22:29       ` Vladislav Shpilevoy
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 6/6] sql: remove OP_ApplyType Mergen Imeev
2020-06-09 22:29   ` Vladislav Shpilevoy
2020-06-01 17:03 ` [Tarantool-patches] [PATCH 0/6] Remove implicit cast Vladislav Shpilevoy
2020-06-09 11:25   ` Mergen Imeev

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=c4b2d4ee-9052-1c9d-e8bd-e67071cd50ed@tarantool.org \
    --to=imeevma@tarantool.org \
    --cc=tarantool-patches@dev.tarantool.org \
    --cc=tsafin@tarantool.org \
    --cc=v.shpilevoy@tarantool.org \
    --subject='Re: [Tarantool-patches] [PATCH 5/6] sql: remove implicit cast from string 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