[Tarantool-patches] [PATCH v3 2/8] sql: change implicit cast for assignment

[imeevma@tarantool.org]

Hi! Thank you for review. My answers and new patch below. I
haven't included diffs since there were quite a few changes.
I have included answers to review questions in the next three
patches here.

On 22.06.2020 11:23, Nikita Pettik wrote:
> On 17 Jun 15:36, imeevma@tarantool.org wrote:
>> This patch removes implicit cast for assignment, however,
>> it is allowed to implicitly cast DOUBLE to INTEGER and
>> vice versa.
>>
>> Closes #3809
>> ---
>>
>> diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
>> index 6b769805c..ae2622c9e 100644
>> --- a/src/box/sql/vdbe.c
>> +++ b/src/box/sql/vdbe.c
>> @@ -418,6 +418,113 @@ sql_value_apply_type(
>>    mem_apply_type((Mem *) pVal, type);
>>  }
>>  
>> +/**
>> + * Check that mem_type of the mem is compatible with given type.
>> + * In the case of numeric values, this function tries to convert
>> + * the mem to the specified type and returns -1 if this is not
>> + * possible.
>> + *
>> + * @param mem The value to check.
>> + * @param type The type to check.
>> + */
>> +static int
>> +mem_check_types(struct Mem *mem, enum field_type type)
>
> I'd rename it to mem_icast_to_type() or mem_impl_cast_to_type()
> or smth like that.
>
I divided into 2 functions: mem_check_type (), which checks, and
mem_convert_numeric(), which converts.

>> +{
>> +  if ((mem->flags & MEM_Null) != 0)
>> +    return 0;
>> +  assert(type < field_type_MAX);
>> +  uint32_t flags = mem->flags;
>> +  switch (type) {
>
> Instead of such long switch-cases could we organize it in one table
> containing valid conversions? I mean sort of field_mp_type_is_compatible()
> To provide not only check but execution mechanism you can fill
> table with pointers to functions implementing particular casts.
>
Fixed, I think.

>> +  case FIELD_TYPE_INTEGER:
>> +    if ((flags & (MEM_Int | MEM_UInt)) != 0)
>> +      return 0;
>> +    if ((flags & MEM_Real) == 0)
>> +      return -1;
>> +    double d = mem->u.r;
>> +    if (d >= (double)UINT64_MAX || d < (double)INT64_MIN)
>> +      return -1;
>> +    if (d < (double)INT64_MAX) {
>> +      int64_t i = (int64_t) d;
>> +      if (i == d) {
>> +        mem_set_int(mem, i, i <= -1);
>> +        return 0;
>> +      }
>> +      return -1;
>> +    }
>> +    uint64_t u = (uint64_t) d;
>> +    if (u == d) {
>> +      mem_set_u64(mem, u);
>> +      return 0;
>> +    }
>> +    return -1;
>> +  case FIELD_TYPE_SCALAR:
>> +    /* Can't cast MAP and ARRAY to scalar types. */
>
> Except for map and arrays we alread have decimal. IS this function
> able to handle it?
>
We do not have a MEM type corresponding to MP_EXT, so we cannot
get MP_EXT in this function.

>> +    if ((flags & MEM_Subtype) == 0 ||
>> +        mem->subtype != SQL_SUBTYPE_MSGPACK)
>> +      return 0;
>> +    assert(mp_typeof(*mem->z) == MP_MAP ||
>> +           mp_typeof(*mem->z) == MP_ARRAY);
>> +    return -1;
>> @@ -2776,6 +2883,31 @@ case OP_ApplyType: {
>>    break;
>>  }
>>  
>> +/* Opcode: CheckType P1 P2 * P4 *
>
> ApplyType was quite suitable name, meanwhile CheckType is a bit confusing
> since in fact it doesn't only check but cast (apply, coerce or whatever)
> mem to given type.
>
I replaced by ImplicitCast for now.

>> + * Synopsis: type(r[P1@P2])
>> + *
>> + * Check that types of P2 registers starting from register
>> + * P1 are compatible with given with given field types in P4.
>> + */
>> +case OP_CheckType: {
>> +  enum field_type *types = pOp->p4.types;
>> +  assert(types != NULL);
>> +  assert(types[pOp->p2] == field_type_MAX);
>> +  pIn1 = &aMem[pOp->p1];
>> +  enum field_type type;
>> +  while((type = *(types++)) != field_type_MAX) {
>> +    assert(pIn1 <= &p->aMem[(p->nMem+1 - p->nCursor)]);
>> +    assert(memIsValid(pIn1));
>> +    if (mem_check_types(pIn1, type) != 0) {
>> +      diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
>> +         mem_type_to_str(pIn1), field_type_strs[type]);
>> +      goto abort_due_to_error;
>> +    }
>> +    pIn1++;
>> +  }
>> +  break;
>> +}
>> +
>> diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c
>> index 8dad2db9a..9e8586ffc 100644
>> --- a/src/box/sql/vdbemem.c
>> +++ b/src/box/sql/vdbemem.c
>> @@ -839,6 +839,13 @@ mem_set_int(struct Mem *mem, int64_t value, bool is_neg)
>>    }
>>  }
>>  
>> +void
>> +mem_set_double(struct Mem *mem, double value)
>> +{
>
> I see inconsistency with other setters: they provide auxiliary
> clean-up in case mem has one of Agg/Dyn/Frame flags. Please
> investigate whether it is really required and if it is so add
> it to current one (or remove from other setters).
>
Fixed in previous commit. In fact, I found that such a function
already exists, so I renamed it and refactored it a bit.

>> +  mem->u.r = value;
>> +  MemSetTypeFlag(mem, MEM_Real);
>> +}
>> +
>> diff --git a/test/sql-tap/autoinc.test.lua b/test/sql-tap/autoinc.test.lua
>> index 37e65e541..ec8dfeab1 100755
>> --- a/test/sql-tap/autoinc.test.lua
>> +++ b/test/sql-tap/autoinc.test.lua
>> @@ -618,7 +618,7 @@ test:do_catchsql_test(
>>              INSERT INTO t2 VALUES('asd'); 
>>      ]], {
>>          -- <autoinc-10.2>
>> -        1, "Type mismatch: can not convert asd to integer"
>> +        1, "Type mismatch: can not convert text to integer"
>
> Having an opportunity to see which particular value can't be
> converted is quite helpful. Let's leave it.
>
Fixed. Fully fixed in the last commit of the set.

>> @@ -694,7 +694,7 @@ test:do_test(
>>                   INSERT INTO t3928(b) VALUES('after-int-' || CAST(new.b AS TEXT));
>>              END;
>>              DELETE FROM t3928 WHERE a!=1;
>> -            UPDATE t3928 SET b=456 WHERE a=1;
>> +            UPDATE t3928 SET b='456' WHERE a=1;
>>              SELECT * FROM t3928 ORDER BY a;
>>          ]])
>>      end, {
>> diff --git a/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua
>> new file mode 100755
>> index 000000000..de72cf3a4
>> --- /dev/null
>> +++ b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua
>
> It's not regression issue, so you can a) remove gh- prefix;
> b) amalgamate it with another test (e.g. sql/types.test.lua).
> It's up to you tho.
>
>> @@ -0,0 +1,649 @@
>> +#!/usr/bin/env tarantool
>> +test = require("sqltester")
>> +test:plan(77)
>> +
>> +--
>> +-- Make sure there are no implicit casts during assignment,
>> +-- except for the implicit cast between numeric values.
>> +--
>
> Why do you still avoid .sql test format?
>
As far as I know, the TAP format is preferable.

>> +test:execsql([[
>> +    CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER);
>> +    CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);
>> +    CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);
>> +    CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT);
>> +    CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);
>> +    CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR);
>> +]])
>> +
>> +test:do_catchsql_test(
>> +    "gh-3809-1",
>> +    [[
>> +        INSERT INTO ti(i) VALUES (11)
>> +    ]], {
>> +        0
>> +    })
>> +
>> +test:do_catchsql_test(
>> +    "gh-3809-2",
>> +    [[
>> +        INSERT INTO ti(i) VALUES (22.2)
>> +    ]], {
>> +        1, "Type mismatch: can not convert real to integer"
>> +    })
>> +
>> +test:do_catchsql_test(
>> +    "gh-3809-3",
>> +    [[
>> +        INSERT INTO ti(i) VALUES (33.0)
>
> Should this be valid? Could you please ask Peter to clarify this question?
>
It should, new rules I described in doc request.

>> +test:execsql([[
>> +    DELETE FROM ti;
>> +    DELETE FROM td;
>> +    DELETE FROM tb;
>> +    DELETE FROM tt;
>> +    DELETE FROM tv;
>> +    DELETE FROM ts;
>> +    INSERT INTO ti(a) VALUES(1);
>> +    INSERT INTO td(a) VALUES(1);
>> +    INSERT INTO tb(a) VALUES(1);
>> +    INSERT INTO tt(a) VALUES(1);
>> +    INSERT INTO tv(a) VALUES(1);
>> +    INSERT INTO ts(a) VALUES(1);
>> +]])
>
> Strange turn..Could you please supply each batch of tests with
> brief description?
>
Added a comment here.

>> +test:do_execsql_test(
>> +    "gh-3809-39",
>> +    [[
>> +        SELECT * FROM ti, td, tb, tt, tv, ts;
>> +    ]], {
>> +        1, "", 1, "", 1, "", 1, "", 1, "", 1, ""
>> +    })
>> diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua
>> index e173e685c..ce66b7c1e 100755
>> --- a/test/sql-tap/index1.test.lua
>> +++ b/test/sql-tap/index1.test.lua
>> @@ -593,25 +593,17 @@ test:do_test(
>>          -- </index-11.1>
>>      })
>>  end
>> --- integrity_check index-11.2
>> --- Numeric strings should compare as if they were numbers.  So even if the
>> --- strings are not character-by-character the same, if they represent the
>> --- same number they should compare equal to one another.  Verify that this
>> --- is true in indices.
>> ---
>> --- Updated for sql v3: sql will now store these values as numbers
>> --- (because the affinity of column a is NUMERIC) so the quirky
>> --- representations are not retained. i.e. '+1.0' becomes '1'.
>> +
>>  test:do_execsql_test(
>>      "index-12.1",
>>      [[
>>          CREATE TABLE t4(id  INT primary key, a NUMBER,b INT );
>> -        INSERT INTO t4 VALUES(1, '0.0',1);
>> -        INSERT INTO t4 VALUES(2, '0.00',2);
>> -        INSERT INTO t4 VALUES(4, '-1.0',4);
>> -        INSERT INTO t4 VALUES(5, '+1.0',5);
>> -        INSERT INTO t4 VALUES(6, '0',6);
>> -        INSERT INTO t4 VALUES(7, '00000',7);
>> +        INSERT INTO t4 VALUES(1, 0.0, 1);
>> +        INSERT INTO t4 VALUES(2, 0.00, 2);
>> +        INSERT INTO t4 VALUES(4, -1.0, 4);
>> +        INSERT INTO t4 VALUES(5, +1.0, 5);
>> +        INSERT INTO t4 VALUES(6, 0, 6);
>> +        INSERT INTO t4 VALUES(7, 00000, 7);
>>          SELECT a FROM t4 ORDER BY b;
>
> Does this test make any sense now?
>
Removed these tests.

>>      ]], {
>>          -- <index-12.1>
>> @@ -692,7 +684,7 @@ test:do_execsql_test(
>>             c  TEXT,
>>             UNIQUE(a,c)
>>          );
>> -        INSERT INTO t5 VALUES(1,2,3);
>> +        INSERT INTO t5 VALUES(1,2,'3');
>>          SELECT * FROM t5;
>>      ]], {
>>          -- <index-13.1>
>> diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
>> index b6b186632..0db18ba91 100755
>> --- a/test/sql-tap/intpkey.test.lua
>> +++ b/test/sql-tap/intpkey.test.lua
>> @@ -770,11 +770,6 @@ test:do_execsql_test(
>>          -- </intpkey-11.1>
>>      })
>>  
>> --- integrity_check intpkey-12.1
>> --- Try to use a string that looks like a floating point number as
>> --- an integer primary key.  This should actually work when the floating
>> --- point value can be rounded to an integer without loss of data.
>> ---
>
> Ditto
>
Removed these tests.

>>  test:do_execsql_test(
>>      "intpkey-13.1",
>>      [[
>> @@ -788,7 +783,7 @@ test:do_execsql_test(
>>  test:do_execsql_test(
>>      "intpkey-13.2",
>>      [[
>> -        INSERT INTO t1 VALUES('1',2,3);
>> +        INSERT INTO t1 VALUES(1,'2','3');
>>          SELECT * FROM t1 WHERE a=1;
>>      ]], {
>>          -- <intpkey-13.2>
>> --- a/test/sql-tap/whereB.test.lua
>> +++ b/test/sql-tap/whereB.test.lua
>> @@ -112,24 +112,16 @@ test:do_execsql_test(
>>      -- </whereB-1.102>
>>      })
>>  
>> --- For this set of tests:
>> ---
>> ---  *   t1.y holds a text value with affinity TEXT
>> ---  *   t2.b holds an integer value with affinity NONE
>> ---
>> --- These values are not equal and because neither affinity is NUMERIC
>> --- no type conversion occurs.
>> ---
>
> Ditto
>
Removed these tests.

>>  test:do_execsql_test(
>>      "whereB-2.1",
>>      [[
>>          DROP TABLE t1;
>>          DROP TABLE t2;
>>  
>> -        CREATE TABLE t1(x  INT primary key, y TEXT);    -- affinity of t1.y is TEXT
>> -        INSERT INTO t1 VALUES(1,99);
>> +        CREATE TABLE t1(x  INT primary key, y TEXT);
>> +        INSERT INTO t1 VALUES(1,'99');
>>  
>> -        CREATE TABLE t2(a  INT primary key, b SCALAR);  -- affinity of t2.b is NONE
>> +        CREATE TABLE t2(a  INT primary key, b SCALAR);
>>          CREATE INDEX t2b ON t2(b);
>>          INSERT INTO t2 VALUES(2, 99);
>>  



On 22.06.2020 11:48, Nikita Pettik wrote:
> On 17 Jun 15:36, imeevma@tarantool.org wrote:
>> This patch removes type changing from OP_MakeRecord.
>
> Please reflect user-visible changes in commit message.
>  
Fixed.

>> Part of #4230
>> ---
>>  src/box/sql/analyze.c       |  7 +------
>>  src/box/sql/delete.c        |  8 ++------
>>  src/box/sql/expr.c          |  8 +-------
>>  src/box/sql/fk_constraint.c |  9 ++-------
>>  src/box/sql/select.c        | 25 ++++++-------------------
>>  src/box/sql/update.c        | 14 +++-----------
>>  src/box/sql/vdbe.c          | 19 +------------------
>>  test/sql-tap/in3.test.lua   |  4 ++--
>>  8 files changed, 18 insertions(+), 76 deletions(-)
>>
>> diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua
>> index e29db9d93..f7681640e 100755
>> --- a/test/sql-tap/in3.test.lua
>> +++ b/test/sql-tap/in3.test.lua
>> @@ -354,7 +354,7 @@ test:do_test(
>>          return exec_neph(" SELECT y IN (SELECT a FROM t1) FROM t2 ")
>>      end, {
>>          -- <in3-3.5>
>> -        1, true
>> +        1, false
>>          -- </in3-3.5>
>>      })
>>  
>> @@ -378,7 +378,7 @@ test:do_test(
>>          return exec_neph(" SELECT y IN (SELECT c FROM t1) FROM t2 ")
>>      end, {
>>          -- <in3-3.7>
>> -        1, true
>> +        1, false
>>          -- </in3-3.7>
>>      })
>>  
>> -- 
>> 2.25.1
>>



On 22.06.2020 12:32, Nikita Pettik wrote:
> On 17 Jun 15:36, imeevma@tarantool.org wrote:
>> This patch removes implicit cast from STRING to numeric
>> and vice versa of left operand of IN operator.
>>
>> Part of #4230
>> Part of #4692
>> ---
>>  src/box/sql/expr.c                   |  2 +-
>>  test/sql-tap/in3.test.lua            | 14 +-----
>>  test/sql-tap/subquery.test.lua       | 69 +---------------------------
>>  test/sql-tap/tkt-80e031a00f.test.lua |  4 +-
>>  test/sql/boolean.result              | 12 ++---
>>  5 files changed, 11 insertions(+), 90 deletions(-)
>>
>> diff --git a/test/sql-tap/tkt-80e031a00f.test.lua b/test/sql-tap/tkt-80e031a00f.test.lua
>> index a0e6539e0..c883937ca 100755
>> --- a/test/sql-tap/tkt-80e031a00f.test.lua
>> +++ b/test/sql-tap/tkt-80e031a00f.test.lua
>> @@ -346,7 +346,7 @@ test:do_catchsql_test(
>>          SELECT 'hello' IN t1
>>      ]], {
>>          -- <tkt-80e031a00f.27>
>> -        1, 'Type mismatch: can not convert hello to integer'
>> +        1, 'Type mismatch: can not convert text to integer'
>>          -- </tkt-80e031a00f.27>
>>      })
>>  
>> @@ -356,7 +356,7 @@ test:do_catchsql_test(
>>          SELECT 'hello' NOT IN t1
>>      ]], {
>>          -- <tkt-80e031a00f.28>
>> -        1, 'Type mismatch: can not convert hello to integer'
>> +        1, 'Type mismatch: can not convert text to integer'
>>          -- </tkt-80e031a00f.28>
>>      })
>
> Again, old format of error message containing value which
> can't be converted seems more suitable to me. Mb it is worth
> combining them putting in error message both type and value:
>
> ... convert 'hello' (type : text) to integer
>  
Fixed in the last patch of the set.

> Otherwise LGTM
>
>> diff --git a/test/sql/boolean.result b/test/sql/boolean.result
>> index e88183854..c74713c34 100644
>> --- a/test/sql/boolean.result
>> +++ b/test/sql/boolean.result
>> @@ -3877,12 +3877,12 @@ SELECT false IN (0, 1, 2, 3);
>>  SELECT true IN (SELECT b FROM t7);
>>   | ---
>>   | - null
>> - | - 'Type mismatch: can not convert TRUE to integer'
>> + | - 'Type mismatch: can not convert boolean to integer'
>>   | ...




On 22.06.2020 13:07, Nikita Pettik wrote:
> On 17 Jun 15:36, imeevma@tarantool.org wrote:
>> This patch replaces mem_apply_type() with mem_check_types() in
>> OP_MustBeInt, which allows to remove implicit cast in some places,
>> for example, in the IN statement.
>>
>> Part of #4230
>> ---
>>  src/box/sql/vdbe.c                   |   2 +-
>>  test/sql-tap/e_select1.test.lua      |  17 +-
>>  test/sql-tap/in4.test.lua            |  97 +--
>>  test/sql-tap/join.test.lua           |   1 -
>>  test/sql-tap/limit.test.lua          |   2 +-
>>  test/sql-tap/tkt-9a8b09f8e6.test.lua |  24 +-
>>  test/sql-tap/tkt-fc7bd6358f.test.lua | 111 ----
>>  test/sql-tap/transitive1.test.lua    |   4 +-
>>  test/sql-tap/whereB.test.lua         | 900 ---------------------------
>>  test/sql-tap/whereC.test.lua         |   8 +-
>>  10 files changed, 19 insertions(+), 1147 deletions(-)
>>  delete mode 100755 test/sql-tap/tkt-fc7bd6358f.test.lua
>>  delete mode 100755 test/sql-tap/whereB.test.lua
>>
>> diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
>> index eb5c89e9d..77b758833 100644
>> --- a/src/box/sql/vdbe.c
>> +++ b/src/box/sql/vdbe.c
>> @@ -2096,7 +2096,7 @@ case OP_AddImm: {            /* in1 */
>>  case OP_MustBeInt: {            /* jump, in1 */
>>    pIn1 = &aMem[pOp->p1];
>>    if ((pIn1->flags & (MEM_Int | MEM_UInt)) == 0) {
>> -    mem_apply_type(pIn1, FIELD_TYPE_INTEGER);
>> +    mem_check_types(pIn1, FIELD_TYPE_INTEGER);
>>      if ((pIn1->flags & (MEM_Int | MEM_UInt)) == 0) {
>>        if (pOp->p2==0) {
>>          diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
>
> Have the code coverage changed after these patches?
> I'm wondering since a lot of tests are removed now (I believe
> due to redundancy, so in perfect case coverage may even increase).
>
The coverage of src/box/sql/* has not changed according to
coverall.io:
https://coveralls.io/builds/31683427

>> index 7673426f4..578620fca 100755
>> --- a/test/sql-tap/e_select1.test.lua
>> +++ b/test/sql-tap/e_select1.test.lua
>> @@ -1,6 +1,6 @@
>>  #!/usr/bin/env tarantool
>>  test = require("sqltester")
>> -test:plan(510)
>> +test:plan(509)
>>  




New patch:

commit 21d7145c1929bc4606c56e9a566477f248637ed1
Author: Mergen Imeev <imeevma@gmail.com>
Date:   Wed May 27 13:49:11 2020 +0300

    sql: change implicit cast for assignment
    
    This patch changes implicit cast for assignment.
    
    Closes #3809
    
    @TarantoolBot document
    Title: change implicit cast for comparison
    
    After this patch, these rules will apply during the implicit cast:
    
    | | STRING | BINARY | BOOLEAN | INTEGER | UNSIGNED | DOUBLE |
    | :--- | :---: | :---: | :---: | :---: | :---: | :---: |
    | STRING | A | N | N | N | N | N |
    | BINARY | N | A | N | N | N | N |
    | BOOLEAN | N | N | A | N | N | N |
    | INTEGER | N | N | N | A | S | Aa |
    | UNSIGNED | N | N | N | A | A | Aa |
    | DOUBLE | N | N | N | Sa | Sa | A |
    
    In this table, the types of the assigned value are on the left,
    and the types that should be after the assignment are at the top.
    
    'A' - the assignment will be completed.
    'N' - the assignment won't be completed.
    'S' - the appointment may be unsuccessful.
    'a' - after assignment, the resulting value may be approximated.
    
    Rules for numeric values are these:
    1) Loss of significant digits (overflow) is an error.
    2) Loss of insignificant digits is not an error.
    
    Example:
    ```
    tarantool> box.execute([[CREATE TABLE t1(i INT PRIMARY KEY);]])
    tarantool> box.execute([[INSERT INTO t1 VALUES ('1');]])
    ---
    - null
    - 'Type mismatch: can not convert 1 to integer'
    ...
    
    tarantool> box.execute('INSERT INTO t1 VALUES (1.2345);')
    tarantool> box.execute('SELECT * FROM t1;')
    ---
    - metadata:
      - name: I
        type: integer
      rows:
      - [1]
    ...
    
    tarantool> box.execute([[CREATE TABLE t2(t text PRIMARY KEY);]])
    tarantool> box.execute([[INSERT INTO t2 VALUES (1);]])
    ---
    - null
    - 'Type mismatch: can not convert 1 to string'
    ...
    ```

diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index 588e142d2..8a89f9904 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -69,7 +69,7 @@ sql_emit_table_types(struct Vdbe *v, struct space_def *def, int reg)
  for (uint32_t i = 0; i < field_count; ++i)
    colls_type[i] = def->fields[i].type;
  colls_type[field_count] = field_type_MAX;
- sqlVdbeAddOp4(v, OP_ApplyType, reg, field_count, 0,
+ sqlVdbeAddOp4(v, OP_ImplicitCast, reg, field_count, 0,
        (char *)colls_type, P4_DYNAMIC);
 }
 
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 37283e506..f1d0345f9 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -397,6 +397,15 @@ sql_value_to_diag_str(sql_value *value);
 enum mp_type
 sql_value_type(sql_value *);
 
+/*
+ * Return the MP_type of the value of the MEM.
+ *
+ * @param mem MEM with the correct MEM_type.
+ * @retval MP_type of the value.
+ */
+enum mp_type
+sql_value_mp_type(struct Mem *mem);
+
 static inline bool
 sql_value_is_null(sql_value *value)
 {
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index 950f72ddd..009d577d2 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -417,6 +417,143 @@ sql_value_apply_type(
  mem_apply_type((Mem *) pVal, type);
 }
 
+/**
+ * Check that MEM_type of the mem is compatible with given type.
+ *
+ * @param mem The MEM that contains the value to check.
+ * @param type The type to check.
+ * @retval 0 if the MEM_type of the value ​​and the given type are
+ *         compatible, -1 otherwise.
+ */
+static int
+mem_check_type(struct Mem *mem, enum field_type type)
+{
+ enum mp_type mp_type = sql_value_mp_type(mem);
+ assert(mp_type < MP_EXT);
+ if (field_mp_plain_type_is_compatible(type, mp_type, true))
+   return 0;
+ return -1;
+}
+
+/**
+ * Convert the numeric value contained in MEM to double. If the
+ * is_precise flag is set, the conversion will succeed only if it
+ * is lossless.
+ *
+ * @param mem The MEM that contains the numeric value.
+ * @param is_precise Flag.
+ * @retval 0 if the conversion was successful, -1 otherwise.
+ */
+static int
+mem_convert_to_double(struct Mem *mem, bool is_precise)
+{
+ if ((mem->flags & (MEM_Int | MEM_UInt)) == 0)
+   return -1;
+ if ((mem->flags & MEM_Int) != 0) {
+   int64_t i = mem->u.i;
+   double d = (double)i;
+   if (!is_precise || i == (int64_t)d)
+     mem_set_double(mem, d);
+   else
+     return -1;
+ } else {
+   uint64_t u = mem->u.u;
+   double d = (double)u;
+   if (!is_precise || u == (uint64_t)d)
+     mem_set_double(mem, d);
+   else
+     return -1;
+ }
+ mem->field_type = FIELD_TYPE_DOUBLE;
+ return 0;
+}
+
+/**
+ * Convert the numeric value contained in MEM to unsigned. If the
+ * is_precise flag is set, the conversion will succeed only if it
+ * is lossless.
+ *
+ * @param mem The MEM that contains the numeric value.
+ * @param is_precise Flag.
+ * @retval 0 if the conversion was successful, -1 otherwise.
+ */
+static int
+mem_convert_to_unsigned(struct Mem *mem, bool is_precise)
+{
+ if ((mem->flags & MEM_Real) == 0)
+   return -1;
+ double d = mem->u.r;
+ if (d < 0.0 || d >= (double)UINT64_MAX)
+   return -1;
+ uint64_t u = (uint64_t)d;
+ if (!is_precise || d == (double)u)
+   mem_set_u64(mem, (uint64_t) d);
+ else
+   return -1;
+ mem->field_type = FIELD_TYPE_UNSIGNED;
+ return 0;
+}
+
+/**
+ * Convert the numeric value contained in MEM to integer. If the
+ * is_precise flag is set, the conversion will succeed only if it
+ * is lossless.
+ *
+ * @param mem The MEM that contains the numeric value.
+ * @param is_precise Flag.
+ * @retval 0 if the conversion was successful, -1 otherwise.
+ */
+static int
+mem_convert_to_integer(struct Mem *mem, bool is_precise)
+{
+ if ((mem->flags & MEM_Real) == 0)
+   return -1;
+ double d = mem->u.r;
+ if (d >= (double)UINT64_MAX || d < (double)INT64_MIN)
+   return -1;
+ if (d < (double)INT64_MAX) {
+   int64_t i = (int64_t)d;
+   if (!is_precise || d == (double)i)
+     mem_set_i64(mem, (int64_t) d);
+   else
+     return -1;
+ } else {
+   uint64_t u = (uint64_t)d;
+   if (!is_precise || d == (double)u)
+     mem_set_u64(mem, (uint64_t) d);
+   else
+     return -1;
+ }
+ mem->field_type = FIELD_TYPE_INTEGER;
+ return 0;
+}
+
+/**
+ * Convert the numeric value contained in MEM to another numeric
+ * type. If the is_precise flag is set, the conversion will
+ * succeed only if it is lossless.
+ *
+ * @param mem The MEM that contains the numeric value.
+ * @param type The type to convert to.
+ * @param is_precise Flag.
+ * @retval 0 if the conversion was successful, -1 otherwise.
+ */
+static int
+mem_convert_numeric(struct Mem *mem, enum field_type type, bool is_precise)
+{
+ if (!sql_type_is_numeric(type) ||
+     (mem->flags & (MEM_Real | MEM_Int | MEM_UInt)) == 0)
+   return -1;
+ if (type == FIELD_TYPE_NUMBER)
+   return 0;
+ if (type == FIELD_TYPE_DOUBLE)
+   return mem_convert_to_double(mem, is_precise);
+ if (type == FIELD_TYPE_UNSIGNED)
+   return mem_convert_to_unsigned(mem, is_precise);
+ assert(type == FIELD_TYPE_INTEGER);
+ return mem_convert_to_integer(mem, is_precise);
+}
+
 /*
  * 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
@@ -2773,6 +2910,36 @@ case OP_ApplyType: {
  break;
 }
 
+/* Opcode: CheckType P1 P2 * P4 *
+ * Synopsis: type(r[P1@P2])
+ *
+ * Check that types of P2 registers starting from register
+ * P1 are compatible with given with given field types in P4.
+ * If the MEM_type of the value and the given type are
+ * incompatible, but both are numeric, this opcode attempts to
+ * convert the value to the type.
+ */
+case OP_ImplicitCast: {
+ enum field_type *types = pOp->p4.types;
+ assert(types != NULL);
+ assert(types[pOp->p2] == field_type_MAX);
+ pIn1 = &aMem[pOp->p1];
+ enum field_type type;
+ while((type = *(types++)) != field_type_MAX) {
+   assert(pIn1 <= &p->aMem[(p->nMem+1 - p->nCursor)]);
+   assert(memIsValid(pIn1));
+   if (mem_check_type(pIn1, type) != 0 &&
+       mem_convert_numeric(pIn1, type, false) != 0) {
+     diag_set(ClientError, ER_SQL_TYPE_MISMATCH,
+        sql_value_to_diag_str(pIn1),
+        field_type_strs[type]);
+     goto abort_due_to_error;
+   }
+   pIn1++;
+ }
+ break;
+}
+
 /* Opcode: MakeRecord P1 P2 P3 P4 P5
  * Synopsis: r[P3]=mkrec(r[P1@P2])
  *
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index 4e103a653..087c661ec 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -225,6 +225,33 @@ sql_value_type(sql_value *pVal)
  }
 }
 
+enum mp_type
+sql_value_mp_type(struct Mem *mem)
+{
+ switch (mem->flags & MEM_PURE_TYPE_MASK) {
+ case MEM_Int:
+   return MP_INT;
+ case MEM_UInt:
+   return MP_UINT;
+ case MEM_Real:
+   return MP_DOUBLE;
+ case MEM_Str:
+   return MP_STR;
+ case MEM_Blob:
+   if ((mem->flags & MEM_Subtype) == 0 ||
+       mem->subtype != SQL_SUBTYPE_MSGPACK)
+     return MP_BIN;
+   assert(mp_typeof(*mem->z) == MP_MAP ||
+          mp_typeof(*mem->z) == MP_ARRAY);
+   return mp_typeof(*mem->z);
+ case MEM_Bool:
+   return MP_BOOL;
+ case MEM_Null:
+   return MP_NIL;
+ default: unreachable();
+ }
+}
+
 /* Make a copy of an sql_value object
  */
 sql_value *
diff --git a/test/sql-tap/autoinc.test.lua b/test/sql-tap/autoinc.test.lua
index 37e65e541..07442b60a 100755
--- a/test/sql-tap/autoinc.test.lua
+++ b/test/sql-tap/autoinc.test.lua
@@ -694,7 +694,7 @@ test:do_test(
                  INSERT INTO t3928(b) VALUES('after-int-' || CAST(new.b AS TEXT));
             END;
             DELETE FROM t3928 WHERE a!=1;
-            UPDATE t3928 SET b=456 WHERE a=1;
+            UPDATE t3928 SET b='456' WHERE a=1;
             SELECT * FROM t3928 ORDER BY a;
         ]])
     end, {
diff --git a/test/sql-tap/default.test.lua b/test/sql-tap/default.test.lua
index d3e35c71c..f1def2b10 100755
--- a/test/sql-tap/default.test.lua
+++ b/test/sql-tap/default.test.lua
@@ -109,16 +109,12 @@ test:do_execsql_test(
  f VARCHAR(15), --COLLATE RTRIM,
  g INTEGER DEFAULT( 3600*12 )
  );
- INSERT INTO t3 VALUES(null, 5, 'row1', 5.25, 8.67, 321, 432);
+ INSERT INTO t3 VALUES(null, 5, 'row1', 5.25, 8.67, '321', 432);
  SELECT a, typeof(a), b, typeof(b), c, typeof(c), 
  d, typeof(d), e, typeof(e), f, typeof(f),
  g, typeof(g) FROM t3;
  ]], {
  -- <default-3.1>
- -- TODO: In original test "321" is not a string, its a value.
- -- In current situation I don't know what to do, need Kirill's
- -- advice.
- -- Bulat
  1, "integer", 5, "integer", "row1", "string", 5.25, "number", 8.67, "number", "321", "string", 432, "integer"
  -- </default-3.1>
 })
diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua
index 1d3b964b9..7673426f4 100755
--- a/test/sql-tap/e_select1.test.lua
+++ b/test/sql-tap/e_select1.test.lua
@@ -753,11 +753,11 @@ test:do_execsql_test(
         CREATE TABLE z3(id  INT primary key, a NUMBER, b NUMBER);
 
         INSERT INTO z1 VALUES(1, 51.65, -59.58, 'belfries');
-        INSERT INTO z1 VALUES(2, -5, NULL, 75);
+        INSERT INTO z1 VALUES(2, -5, NULL, '75');
         INSERT INTO z1 VALUES(3, -2.2, -23.18, 'suiters');
         INSERT INTO z1 VALUES(4, NULL, 67, 'quartets');
         INSERT INTO z1 VALUES(5, -1.04, -32.3, 'aspen');
-        INSERT INTO z1 VALUES(6, 63, '0', -26);
+        INSERT INTO z1 VALUES(6, 63, 0, '-26');
 
         INSERT INTO z2 VALUES(1, NULL, 21);
         INSERT INTO z2 VALUES(2, 36.0, 6.0);
@@ -1457,13 +1457,13 @@ test:do_execsql_test(
         CREATE TABLE q2(id  INT primary key, d TEXT, e NUMBER);
         CREATE TABLE q3(id  INT primary key, f TEXT, g INT);
 
-        INSERT INTO q1 VALUES(1, 16, -87.66, NULL);
+        INSERT INTO q1 VALUES(1, '16', -87.66, NULL);
         INSERT INTO q1 VALUES(2, 'legible', 94, -42.47);
         INSERT INTO q1 VALUES(3, 'beauty', 36, NULL);
 
         INSERT INTO q2 VALUES(1, 'legible', 1);
         INSERT INTO q2 VALUES(2, 'beauty', 2);
-        INSERT INTO q2 VALUES(3, -65, 4);
+        INSERT INTO q2 VALUES(3, '-65', 4);
         INSERT INTO q2 VALUES(4, 'emanating', -16.56);
 
         INSERT INTO q3 VALUES(1, 'beauty', 2);
@@ -1603,7 +1603,7 @@ test:do_execsql_test(
         CREATE TABLE w2(a  INT PRIMARY KEY, b TEXT);
 
         INSERT INTO w1 VALUES('1', 4.1);
-        INSERT INTO w2 VALUES(1, 4.1);
+        INSERT INTO w2 VALUES(1, '4.1');
     ]], {
         -- <e_select-7.10.0>
 
diff --git a/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua
new file mode 100755
index 000000000..a1809b3cb
--- /dev/null
+++ b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua
@@ -0,0 +1,653 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+test:plan(77)
+
+--
+-- Make sure there are no implicit casts during assignment,
+-- except for the implicit cast between numeric values.
+--
+test:execsql([[
+    CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER);
+    CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);
+    CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);
+    CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT);
+    CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);
+    CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR);
+]])
+
+test:do_catchsql_test(
+    "gh-3809-1",
+    [[
+        INSERT INTO ti(i) VALUES (11)
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-2",
+    [[
+        INSERT INTO ti(i) VALUES (100000000000000000000000000000000.1)
+    ]], {
+        1, "Type mismatch: can not convert 1.0e+32 to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-3",
+    [[
+        INSERT INTO ti(i) VALUES (33.0)
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-4",
+    [[
+        INSERT INTO ti(i) VALUES (true)
+    ]], {
+        1, "Type mismatch: can not convert TRUE to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-5",
+    [[
+        INSERT INTO ti(i) VALUES ('33')
+    ]], {
+        1, "Type mismatch: can not convert 33 to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-6",
+    [[
+        INSERT INTO ti(i) VALUES (X'3434')
+    ]], {
+        1, "Type mismatch: can not convert varbinary to integer"
+    })
+
+test:do_execsql_test(
+    "gh-3809-7",
+    [[
+        SELECT * FROM ti;
+    ]], {
+        1, 11, 2, 33
+    })
+
+test:do_catchsql_test(
+    "gh-3809-8",
+    [[
+        INSERT INTO td(d) VALUES (11)
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-9",
+    [[
+        INSERT INTO td(d) VALUES (100000000000000001);
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-10",
+    [[
+        INSERT INTO td(d) VALUES (22.2)
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-11",
+    [[
+        INSERT INTO td(d) VALUES (true)
+    ]], {
+        1, "Type mismatch: can not convert TRUE to double"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-12",
+    [[
+        INSERT INTO td(d) VALUES ('33')
+    ]], {
+        1, "Type mismatch: can not convert 33 to double"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-13",
+    [[
+        INSERT INTO td(d) VALUES (X'3434')
+    ]], {
+        1, "Type mismatch: can not convert varbinary to double"
+    })
+
+test:do_execsql_test(
+    "gh-3809-14",
+    [[
+        SELECT * FROM td;
+    ]], {
+        1, 11, 2, 100000000000000000, 3, 22.2
+    })
+
+test:do_catchsql_test(
+    "gh-3809-15",
+    [[
+        INSERT INTO tb(b) VALUES (11)
+    ]], {
+        1, "Type mismatch: can not convert 11 to boolean"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-16",
+    [[
+        INSERT INTO tb(b) VALUES (22.2)
+    ]], {
+        1, "Type mismatch: can not convert 22.2 to boolean"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-17",
+    [[
+        INSERT INTO tb(b) VALUES (true)
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-18",
+    [[
+        INSERT INTO tb(b) VALUES ('33')
+    ]], {
+        1, "Type mismatch: can not convert 33 to boolean"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-19",
+    [[
+        INSERT INTO tb(b) VALUES (X'3434')
+    ]], {
+        1, "Type mismatch: can not convert varbinary to boolean"
+    })
+
+test:do_execsql_test(
+    "gh-3809-20",
+    [[
+        SELECT * FROM tb;
+    ]], {
+        1, true
+    })
+
+test:do_catchsql_test(
+    "gh-3809-21",
+    [[
+        INSERT INTO tt(t) VALUES (11)
+    ]], {
+        1, "Type mismatch: can not convert 11 to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-22",
+    [[
+        INSERT INTO tt(t) VALUES (22.2)
+    ]], {
+        1, "Type mismatch: can not convert 22.2 to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-23",
+    [[
+        INSERT INTO tt(t) VALUES (true)
+    ]], {
+        1, "Type mismatch: can not convert TRUE to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-24",
+    [[
+        INSERT INTO tt(t) VALUES ('33')
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-25",
+    [[
+        INSERT INTO tt(t) VALUES (X'3434')
+    ]], {
+        1, "Type mismatch: can not convert varbinary to string"
+    })
+
+test:do_execsql_test(
+    "gh-3809-26",
+    [[
+        SELECT * FROM tt;
+    ]], {
+        1, "33"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-27",
+    [[
+        INSERT INTO tv(v) VALUES (11)
+    ]], {
+        1, "Type mismatch: can not convert 11 to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-28",
+    [[
+        INSERT INTO tv(v) VALUES (22.2)
+    ]], {
+        1, "Type mismatch: can not convert 22.2 to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-29",
+    [[
+        INSERT INTO tv(v) VALUES (true)
+    ]], {
+        1, "Type mismatch: can not convert TRUE to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-30",
+    [[
+        INSERT INTO tv(v) VALUES ('33')
+    ]], {
+        1, "Type mismatch: can not convert 33 to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-31",
+    [[
+        INSERT INTO tv(v) VALUES (X'3434')
+    ]], {
+        0
+    })
+
+test:do_execsql_test(
+    "gh-3809-32",
+    [[
+        SELECT * FROM tv;
+    ]], {
+        1, "44"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-33",
+    [[
+        INSERT INTO ts(s) VALUES (11)
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-34",
+    [[
+        INSERT INTO ts(s) VALUES (22.2)
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-35",
+    [[
+        INSERT INTO ts(s) VALUES (true)
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-36",
+    [[
+        INSERT INTO ts(s) VALUES ('33')
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-37",
+    [[
+        INSERT INTO ts(s) VALUES (X'3434')
+    ]], {
+        0
+    })
+
+test:do_execsql_test(
+    "gh-3809-38",
+    [[
+        SELECT * FROM ts;
+    ]], {
+        1, 11, 2, 22.2, 3, true, 4, "33", 5, "44"
+    })
+
+--
+-- This test suite verifies that ASSIGNMENT is working correctly
+-- during an UPDATE.
+--
+test:execsql([[
+    DELETE FROM ti;
+    DELETE FROM td;
+    DELETE FROM tb;
+    DELETE FROM tt;
+    DELETE FROM tv;
+    DELETE FROM ts;
+    INSERT INTO ti(a) VALUES(1);
+    INSERT INTO td(a) VALUES(1);
+    INSERT INTO tb(a) VALUES(1);
+    INSERT INTO tt(a) VALUES(1);
+    INSERT INTO tv(a) VALUES(1);
+    INSERT INTO ts(a) VALUES(1);
+]])
+
+test:do_execsql_test(
+    "gh-3809-39",
+    [[
+        SELECT * FROM ti, td, tb, tt, tv, ts;
+    ]], {
+        1, "", 1, "", 1, "", 1, "", 1, "", 1, ""
+    })
+
+test:do_catchsql_test(
+    "gh-3809-40",
+    [[
+        UPDATE ti SET i = 11 WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-41",
+    [[
+        UPDATE ti SET i = 100000000000000000000000000000000.1 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 1.0e+32 to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-42",
+    [[
+        UPDATE ti SET i = 33.0 WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-43",
+    [[
+        UPDATE ti SET i = true WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert TRUE to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-44",
+    [[
+        UPDATE ti SET i = '33' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 33 to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-45",
+    [[
+        UPDATE ti SET i = X'3434' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert varbinary to integer"
+    })
+
+test:do_execsql_test(
+    "gh-3809-46",
+    [[
+        SELECT * FROM ti;
+    ]], {
+        1, 33
+    })
+
+test:do_catchsql_test(
+    "gh-3809-47",
+    [[
+        UPDATE td SET d = 11 WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-48",
+    [[
+        UPDATE td SET d = 100000000000000001 WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-49",
+    [[
+        UPDATE td SET d = 22.2 WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-50",
+    [[
+        UPDATE td SET d = true WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert TRUE to double"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-51",
+    [[
+        UPDATE td SET d = '33' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 33 to double"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-52",
+    [[
+        UPDATE td SET d = X'3434' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert varbinary to double"
+    })
+
+test:do_execsql_test(
+    "gh-3809-53",
+    [[
+        SELECT * FROM td;
+    ]], {
+        1, 22.2
+    })
+
+test:do_catchsql_test(
+    "gh-3809-54",
+    [[
+        UPDATE tb SET b = 11 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 11 to boolean"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-55",
+    [[
+        UPDATE tb SET b = 22.2 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 22.2 to boolean"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-56",
+    [[
+        UPDATE tb SET b = true WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-57",
+    [[
+        UPDATE tb SET b = '33' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 33 to boolean"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-58",
+    [[
+        UPDATE tb SET b = X'3434' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert varbinary to boolean"
+    })
+
+test:do_execsql_test(
+    "gh-3809-59",
+    [[
+        SELECT * FROM tb;
+    ]], {
+        1, true
+    })
+
+test:do_catchsql_test(
+    "gh-3809-60",
+    [[
+        UPDATE tt SET t = 11 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 11 to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-61",
+    [[
+        UPDATE tt SET t = 22.2 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 22.2 to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-62",
+    [[
+        UPDATE tt SET t = true WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert TRUE to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-63",
+    [[
+        UPDATE tt SET t = '33' WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-64",
+    [[
+        UPDATE tt SET t = X'3434' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert varbinary to string"
+    })
+
+test:do_execsql_test(
+    "gh-3809-65",
+    [[
+        SELECT * FROM tt;
+    ]], {
+        1, "33"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-66",
+    [[
+        UPDATE tv SET v = 11 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 11 to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-67",
+    [[
+        UPDATE tv SET v = 22.2 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 22.2 to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-68",
+    [[
+        UPDATE tv SET v = true WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert TRUE to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-69",
+    [[
+        UPDATE tv SET v = '33' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert 33 to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-70",
+    [[
+        UPDATE tv SET v = X'3434' WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_execsql_test(
+    "gh-3809-71",
+    [[
+        SELECT * FROM tv;
+    ]], {
+        1, "44"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-72",
+    [[
+        UPDATE ts SET s = 11 WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-73",
+    [[
+        UPDATE ts SET s = 22.2 WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-74",
+    [[
+        UPDATE ts SET s = true WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-75",
+    [[
+        UPDATE ts SET s = '33' WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-76",
+    [[
+        UPDATE ts SET s = X'3434' WHERE a = 1;
+    ]], {
+        0
+    })
+
+test:do_execsql_test(
+    "gh-3809-77",
+    [[
+        SELECT * FROM ts;
+    ]], {
+        1, "44"
+    })
+
+test:finish_test()
diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua
index e173e685c..ce66b7c1e 100755
--- a/test/sql-tap/index1.test.lua
+++ b/test/sql-tap/index1.test.lua
@@ -593,25 +593,17 @@ test:do_test(
         -- </index-11.1>
     })
 end
--- integrity_check index-11.2
--- Numeric strings should compare as if they were numbers.  So even if the
--- strings are not character-by-character the same, if they represent the
--- same number they should compare equal to one another.  Verify that this
--- is true in indices.
---
--- Updated for sql v3: sql will now store these values as numbers
--- (because the affinity of column a is NUMERIC) so the quirky
--- representations are not retained. i.e. '+1.0' becomes '1'.
+
 test:do_execsql_test(
     "index-12.1",
     [[
         CREATE TABLE t4(id  INT primary key, a NUMBER,b INT );
-        INSERT INTO t4 VALUES(1, '0.0',1);
-        INSERT INTO t4 VALUES(2, '0.00',2);
-        INSERT INTO t4 VALUES(4, '-1.0',4);
-        INSERT INTO t4 VALUES(5, '+1.0',5);
-        INSERT INTO t4 VALUES(6, '0',6);
-        INSERT INTO t4 VALUES(7, '00000',7);
+        INSERT INTO t4 VALUES(1, 0.0, 1);
+        INSERT INTO t4 VALUES(2, 0.00, 2);
+        INSERT INTO t4 VALUES(4, -1.0, 4);
+        INSERT INTO t4 VALUES(5, +1.0, 5);
+        INSERT INTO t4 VALUES(6, 0, 6);
+        INSERT INTO t4 VALUES(7, 00000, 7);
         SELECT a FROM t4 ORDER BY b;
     ]], {
         -- <index-12.1>
@@ -692,7 +684,7 @@ test:do_execsql_test(
            c  TEXT,
            UNIQUE(a,c)
         );
-        INSERT INTO t5 VALUES(1,2,3);
+        INSERT INTO t5 VALUES(1,2,'3');
         SELECT * FROM t5;
     ]], {
         -- <index-13.1>
diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua
index 43bb06630..b92bc508e 100755
--- a/test/sql-tap/insert3.test.lua
+++ b/test/sql-tap/insert3.test.lua
@@ -60,7 +60,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;
-              INSERT OR IGNORE INTO log2(x, y) VALUES(new.b,1);
+              INSERT OR IGNORE INTO log2(x, y) VALUES(CAST(new.b AS STRING),1);
             END;
             INSERT INTO t1(a, b) VALUES('hi', 453);
             SELECT x,y FROM log ORDER BY x;
@@ -129,8 +129,8 @@ test:do_execsql_test(
               INSERT INTO t2dup(a,b,c) VALUES(new.a,new.b,new.c);
             END;
             INSERT INTO t2(a) VALUES(123);
-            INSERT INTO t2(b) VALUES(234);
-            INSERT INTO t2(c) VALUES(345);
+            INSERT INTO t2(b) VALUES('234');
+            INSERT INTO t2(c) VALUES('345');
             SELECT * FROM t2dup;
     ]], {
         -- <insert3-2.1>
@@ -143,8 +143,8 @@ test:do_execsql_test(
     [[
             DELETE FROM t2dup;
             INSERT INTO t2(a) SELECT 1 FROM t1 LIMIT 1;
-            INSERT INTO t2(b) SELECT 987 FROM t1 LIMIT 1;
-            INSERT INTO t2(c) SELECT 876 FROM t1 LIMIT 1;
+            INSERT INTO t2(b) SELECT '987' FROM t1 LIMIT 1;
+            INSERT INTO t2(c) SELECT '876' FROM t1 LIMIT 1;
             SELECT * FROM t2dup;
     ]], {
         -- <insert3-2.2>
diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua
index b6b186632..684a24114 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(31)
 
 --!./tcltestrunner.lua
 -- 2001 September 15
@@ -770,142 +770,13 @@ test:do_execsql_test(
         -- </intpkey-11.1>
     })
 
--- integrity_check intpkey-12.1
--- Try to use a string that looks like a floating point number as
--- an integer primary key.  This should actually work when the floating
--- point value can be rounded to an integer without loss of data.
---
-test:do_execsql_test(
-    "intpkey-13.1",
-    [[
-        SELECT * FROM t1 WHERE a=1;
-    ]], {
-        -- <intpkey-13.1>
-        
-        -- </intpkey-13.1>
-    })
-
-test:do_execsql_test(
-    "intpkey-13.2",
-    [[
-        INSERT INTO t1 VALUES('1',2,3);
-        SELECT * FROM t1 WHERE a=1;
-    ]], {
-        -- <intpkey-13.2>
-        1, "2", "3"
-        -- </intpkey-13.2>
-    })
-
--- MUST_WORK_TEST
-if (0 > 0) then
-    -- Tarantool: issue submitted #2315
-    test:do_catchsql_test(
-        "intpkey-13.3",
-        [[
-            INSERT INTO t1 VALUES('1.5',3,4);
-        ]], {
-            -- <intpkey-13.3>
-            1, "datatype mismatch"
-            -- </intpkey-13.3>
-        })
-
-    test:do_catchsql_test(
-        "intpkey-13.4",
-        [[
-            INSERT INTO t1 VALUES(x'123456',3,4);
-        ]], {
-            -- <intpkey-13.4>
-            1, "datatype mismatch"
-            -- </intpkey-13.4>
-        })
-
-
-
-end
-test:do_catchsql_test(
-    "intpkey-13.5",
-    [[
-        INSERT INTO t1 VALUES('+1234567890',3,4);
-    ]], {
-        -- <intpkey-13.5>
-        0
-        -- </intpkey-13.5>
-    })
-
--- Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER
--- affinity should be applied to the text value before the comparison
--- takes place.
---
-test:do_execsql_test(
-    "intpkey-14.1",
-    [[
-        CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT);
-        INSERT INTO t3 VALUES(1, 1, 'one');
-        INSERT INTO t3 VALUES(2, 2, '2');
-        INSERT INTO t3 VALUES(3, 3, 3);
-    ]], {
-        -- <intpkey-14.1>
-        
-        -- </intpkey-14.1>
-    })
-
-test:do_execsql_test(
-    "intpkey-14.2",
-    [[
-        SELECT * FROM t3 WHERE a>2;
-    ]], {
-        -- <intpkey-14.2>
-        3, 3, "3"
-        -- </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(
-    "intpkey-14.4",
-    [[
-        SELECT * FROM t3 WHERE a<'2';
-    ]], {
-        -- <intpkey-14.4>
-        1, 1, "one"
-        -- </intpkey-14.4>
-    })
-
-test:do_execsql_test(
-    "intpkey-14.5",
-    [[
-        SELECT * FROM t3 WHERE a<c;
-    ]], {
-        -- <intpkey-14.5>
-        1, 1, "one"
-        -- </intpkey-14.5>
-    })
-
-test:do_execsql_test(
-    "intpkey-14.6",
-    [[
-        SELECT * FROM t3 WHERE a=c;
-    ]], {
-        -- <intpkey-14.6>
-        2, 2, "2", 3, 3, "3"
-        -- </intpkey-14.6>
-    })
-
 -- Check for proper handling of primary keys greater than 2^31.
 -- Ticket #1188
 --
 test:do_execsql_test(
     "intpkey-15.1",
     [[
-        INSERT INTO t1 VALUES(2147483647, 'big-1', 123);
+        INSERT INTO t1 VALUES(2147483647, 'big-1', '123');
         SELECT * FROM t1 WHERE a>2147483648;
     ]], {
         -- <intpkey-15.1>
diff --git a/test/sql-tap/minmax2.test.lua b/test/sql-tap/minmax2.test.lua
index 0e0f0d08e..707d1c4da 100755
--- a/test/sql-tap/minmax2.test.lua
+++ b/test/sql-tap/minmax2.test.lua
@@ -441,9 +441,9 @@ test:do_execsql_test(
     "minmax2-8.2",
     [[
         CREATE TABLE t5(a INTEGER PRIMARY KEY);
-        INSERT INTO t5 VALUES('1234');
-        INSERT INTO t5 VALUES('234');
-        INSERT INTO t5 VALUES('34');
+        INSERT INTO t5 VALUES(1234);
+        INSERT INTO t5 VALUES(234);
+        INSERT INTO t5 VALUES(34);
         SELECT min(a), max(a) FROM t5;
     ]], {
         -- <minmax2-8.2>
diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua
index 32f38cc97..e0fe50bbe 100755
--- a/test/sql-tap/misc1.test.lua
+++ b/test/sql-tap/misc1.test.lua
@@ -34,9 +34,9 @@ test:do_test(
         end
         cmd = cmd .. ")"
         test:execsql(cmd)
-        cmd = "INSERT INTO manycol VALUES(1, 0"
+        cmd = "INSERT INTO manycol VALUES(1, '0'"
         for i = 1, 99, 1 do
-            cmd = cmd .. ","..i..""
+            cmd = cmd .. ",'"..i.."'"
         end
         cmd = cmd .. ")"
         test:execsql(cmd)
@@ -61,9 +61,9 @@ test:do_test(
     "misc1-1.3.1",
     function()
         for j = 100, 1000, 100 do
-            local cmd = string.format("INSERT INTO manycol VALUES(%s, %s", j, j)
+            local cmd = string.format("INSERT INTO manycol VALUES(%s, '%s'", j, j)
             for i = 1, 99, 1 do
-                cmd = cmd .. ","..(i + j)..""
+                cmd = cmd .. ",'"..(i + j).."'"
             end
             cmd = cmd .. ")"
             test:execsql(cmd)
@@ -178,12 +178,12 @@ test:do_test(
         test:execsql([[
             CREATE TABLE agger(one text primary key, two text, three text, four text);
             START TRANSACTION;
-            INSERT INTO agger VALUES(1, 'one', 'hello', 'yes');
-            INSERT INTO agger VALUES(2, 'two', 'howdy', 'no');
-            INSERT INTO agger VALUES(3, 'thr', 'howareya', 'yes');
-            INSERT INTO agger VALUES(4, 'two', 'lothere', 'yes');
-            INSERT INTO agger VALUES(5, 'one', 'atcha', 'yes');
-            INSERT INTO agger VALUES(6, 'two', 'hello', 'no');
+            INSERT INTO agger VALUES('1', 'one', 'hello', 'yes');
+            INSERT INTO agger VALUES('2', 'two', 'howdy', 'no');
+            INSERT INTO agger VALUES('3', 'thr', 'howareya', 'yes');
+            INSERT INTO agger VALUES('4', 'two', 'lothere', 'yes');
+            INSERT INTO agger VALUES('5', 'one', 'atcha', 'yes');
+            INSERT INTO agger VALUES('6', 'two', 'hello', 'no');
             COMMIT
         ]])
         return test:execsql("SELECT count(*) FROM agger")
@@ -531,7 +531,7 @@ test:do_test(
     "misc1-10.7",
     function()
         where = string.gsub(where, "x0=0", "x0=100")
-        return test:catchsql("UPDATE manycol SET x1=x1+1 "..where.."")
+        return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."")
     end, {
         -- <misc1-10.7>
         0
@@ -553,7 +553,7 @@ test:do_execsql_test(
 -- } {0 {}}
 test:do_execsql_test(
     "misc1-10.9",
-    "UPDATE manycol SET x1=x1+1 "..where
+    "UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where
         --"UPDATE manycol SET x1=x1+1 $::where AND rowid>0"
     , {})
 
@@ -665,7 +665,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "misc1-12.6",
     [[
-        INSERT OR IGNORE INTO t6 VALUES('y',0);
+        INSERT OR IGNORE INTO t6 VALUES('y','0');
         SELECT * FROM t6;
     ]], {
         -- <misc1-12.6>
@@ -679,10 +679,10 @@ test:do_execsql_test(
     "misc1-12.7",
     [[
         CREATE TABLE t7(x INTEGER, y TEXT, z  INT primary key);
-        INSERT INTO t7 VALUES(0,0,1);
-        INSERT INTO t7 VALUES(0.0,0,2);
-        INSERT INTO t7 VALUES(0,0.0,3);
-        INSERT INTO t7 VALUES(0.0,0.0,4);
+        INSERT INTO t7 VALUES(0,'0',1);
+        INSERT INTO t7 VALUES(0.0,'0',2);
+        INSERT INTO t7 VALUES(0,'0.0',3);
+        INSERT INTO t7 VALUES(0.0,'0.0',4);
         SELECT DISTINCT x, y FROM t7 ORDER BY z;
     ]], {
         -- <misc1-12.7>
diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
index eeac5353a..3161e48fa 100755
--- a/test/sql-tap/numcast.test.lua
+++ b/test/sql-tap/numcast.test.lua
@@ -135,16 +135,16 @@ test:do_catchsql_test(
         INSERT INTO t VALUES(20000000000000000000.01);
         SELECT * FROM t;
     ]], {
-        1,"Tuple field 1 type does not match one required by operation: expected integer"
+        1,"Type mismatch: can not convert 2.0e+19 to integer"
     })
 
-test:do_catchsql_test(
+test:do_execsql_test(
     "cast-2.9",
     [[
         INSERT INTO t VALUES(2.1);
         SELECT * FROM t;
     ]], {
-        1,"Tuple field 1 type does not match one required by operation: expected integer"
+        2, 9223372036854775808ULL, 18000000000000000000ULL
     })
 
 --
diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua
index fbebfab37..9a969bf3c 100755
--- a/test/sql-tap/select1.test.lua
+++ b/test/sql-tap/select1.test.lua
@@ -231,7 +231,7 @@ string.format([[
         CREATE TABLE t3(id INT, a TEXT, b TEXT, PRIMARY KEY(id));
         INSERT INTO t3 VALUES(1, 'abc',NULL);
         INSERT INTO t3 VALUES(2, NULL,'xyz');
-        INSERT INTO t3 SELECT f1, * FROM test1;
+        INSERT INTO t3 SELECT f1, CAST(f1 AS STRING), CAST(f2 AS STRING) FROM test1;
         DROP TABLE IF EXISTS t4;
         CREATE TABLE t4(id INT, a INT , b TEXT , PRIMARY KEY(id));
         INSERT INTO t4 VALUES(1, NULL,'%s');
@@ -1671,8 +1671,8 @@ test:do_execsql_test(
     [[
         DELETE FROM t3;
         DELETE FROM t4;
-        INSERT INTO t3 VALUES(0,1,2);
-        INSERT INTO t4 VALUES(0,3,4);
+        INSERT INTO t3 VALUES(0,'1','2');
+        INSERT INTO t4 VALUES(0,3,'4');
         SELECT * FROM t3, t4;
     ]], {
         -- <select1-11.1>
@@ -1878,7 +1878,7 @@ test:do_execsql_test(
     "select1-12.4",
     [[
         DELETE FROM t3;
-        INSERT INTO t3 VALUES(0,1,2);
+        INSERT INTO t3 VALUES(0,'1','2');
     ]], {
         -- <select1-12.4>
         
diff --git a/test/sql-tap/select4.test.lua b/test/sql-tap/select4.test.lua
index 23cf1bf1b..f7a320438 100755
--- a/test/sql-tap/select4.test.lua
+++ b/test/sql-tap/select4.test.lua
@@ -761,12 +761,12 @@ test:do_test(
         test:execsql [[
             CREATE TABLE t3(a text primary key, b NUMBER, c text);
             START TRANSACTION;
-            INSERT INTO t3 VALUES(1, 1.1, '1.1');
-            INSERT INTO t3 VALUES(2, 1.10, '1.10');
-            INSERT INTO t3 VALUES(3, 1.10, '1.1');
-            INSERT INTO t3 VALUES(4, 1.1, '1.10');
-            INSERT INTO t3 VALUES(5, 1.2, '1.2');
-            INSERT INTO t3 VALUES(6, 1.3, '1.3');
+            INSERT INTO t3 VALUES('1', 1.1, '1.1');
+            INSERT INTO t3 VALUES('2', 1.10, '1.10');
+            INSERT INTO t3 VALUES('3', 1.10, '1.1');
+            INSERT INTO t3 VALUES('4', 1.1, '1.10');
+            INSERT INTO t3 VALUES('5', 1.2, '1.2');
+            INSERT INTO t3 VALUES('6', 1.3, '1.3');
             COMMIT;
         ]]
         return test:execsql [[
diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua
index fec5d7a41..e1e43c557 100755
--- a/test/sql-tap/select7.test.lua
+++ b/test/sql-tap/select7.test.lua
@@ -255,7 +255,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);
+        INSERT INTO t5 VALUES('123', 456);
         SELECT typeof(a), a FROM t5 GROUP BY a HAVING a<b;
     ]], {
         -- <select7-7.7>
diff --git a/test/sql-tap/sort.test.lua b/test/sql-tap/sort.test.lua
index 36074d6ef..18bfd443d 100755
--- a/test/sql-tap/sort.test.lua
+++ b/test/sql-tap/sort.test.lua
@@ -505,10 +505,10 @@ test:do_execsql_test(
           a INTEGER PRIMARY KEY,
           b VARCHAR(30)
         );
-        INSERT INTO t4 VALUES(1,1);
-        INSERT INTO t4 VALUES(2,2);
-        INSERT INTO t4 VALUES(11,11);
-        INSERT INTO t4 VALUES(12,12);
+        INSERT INTO t4 VALUES(1,'1');
+        INSERT INTO t4 VALUES(2,'2');
+        INSERT INTO t4 VALUES(11,'11');
+        INSERT INTO t4 VALUES(12,'12');
         SELECT a FROM t4 ORDER BY 1;
     ]], {
         -- <sort-7.1>
diff --git a/test/sql-tap/tkt-3998683a16.test.lua b/test/sql-tap/tkt-3998683a16.test.lua
index 885dcf5cd..9bc310358 100755
--- a/test/sql-tap/tkt-3998683a16.test.lua
+++ b/test/sql-tap/tkt-3998683a16.test.lua
@@ -26,29 +26,17 @@ test:do_test(
     function()
         return test:execsql [[
             CREATE TABLE t1(x  INT primary key, y NUMBER);
-            INSERT INTO t1 VALUES(1, '1.0');
-            INSERT INTO t1 VALUES(2, '.125');
-            INSERT INTO t1 VALUES(3, '123.');
-            INSERT INTO t1 VALUES(4, '123.e+2');
-            INSERT INTO t1 VALUES(5, '.125e+3');
-            INSERT INTO t1 VALUES(6, '123e4');
-            INSERT INTO t1 VALUES(11, '  1.0');
-            INSERT INTO t1 VALUES(12, '  .125');
-            INSERT INTO t1 VALUES(13, '  123.');
-            INSERT INTO t1 VALUES(14, '  123.e+2');
-            INSERT INTO t1 VALUES(15, '  .125e+3');
-            INSERT INTO t1 VALUES(16, '  123e4');
-            INSERT INTO t1 VALUES(21, '1.0  ');
-            INSERT INTO t1 VALUES(22, '.125  ');
-            INSERT INTO t1 VALUES(23, '123.  ');
-            INSERT INTO t1 VALUES(24, '123.e+2  ');
-            INSERT INTO t1 VALUES(25, '.125e+3  ');
-            INSERT INTO t1 VALUES(26, '123e4  ');
+            INSERT INTO t1 VALUES(1, 1.0);
+            INSERT INTO t1 VALUES(2, .125);
+            INSERT INTO t1 VALUES(3, 123.);
+            INSERT INTO t1 VALUES(4, 123.e+2);
+            INSERT INTO t1 VALUES(5, .125e+3);
+            INSERT INTO t1 VALUES(6, 123e4);
             SELECT x FROM t1 WHERE typeof(y)=='number' ORDER BY x;
         ]]
     end, {
         -- <tkt-3998683a16.1>
-        1, 2, 3, 4, 5, 6, 11, 12, 13, 14, 15, 16, 21, 22, 23, 24, 25, 26
+        1, 2, 3, 4, 5, 6
         -- </tkt-3998683a16.1>
     })
 
diff --git a/test/sql-tap/tkt-54844eea3f.test.lua b/test/sql-tap/tkt-54844eea3f.test.lua
index d6cd56e52..89d0d1218 100755
--- a/test/sql-tap/tkt-54844eea3f.test.lua
+++ b/test/sql-tap/tkt-54844eea3f.test.lua
@@ -62,10 +62,10 @@ test:do_execsql_test(
     "1.2",
     [[
         CREATE TABLE t4(id INT primary key, a TEXT, b TEXT, c TEXT);
-        INSERT INTO t4 VALUES(1, 'a', 1, 'one');
-        INSERT INTO t4 VALUES(2, 'a', 2, 'two');
-        INSERT INTO t4 VALUES(3, 'b', 1, 'three');
-        INSERT INTO t4 VALUES(4, 'b', 2, 'four');
+        INSERT INTO t4 VALUES(1, 'a', '1', 'one');
+        INSERT INTO t4 VALUES(2, 'a', '2', 'two');
+        INSERT INTO t4 VALUES(3, 'b', '1', 'three');
+        INSERT INTO t4 VALUES(4, 'b', '2', 'four');
         SELECT ( 
           SELECT c FROM (
             SELECT a,b,c FROM t4 WHERE a=output.a ORDER BY b LIMIT 10 OFFSET 1
diff --git a/test/sql-tap/tkt-7bbfb7d442.test.lua b/test/sql-tap/tkt-7bbfb7d442.test.lua
index 535303771..bfddcd920 100755
--- a/test/sql-tap/tkt-7bbfb7d442.test.lua
+++ b/test/sql-tap/tkt-7bbfb7d442.test.lua
@@ -109,13 +109,13 @@ if (1 > 0)
                     T1.Variant AS Variant,
                     T1.ControlDate AS ControlDate,
                     1 AS ControlState,
-                    COALESCE(T2.DeliveredQty,0) AS DeliveredQty
+                    CAST(COALESCE(T2.DeliveredQty,0) AS STRING) AS DeliveredQty
                 FROM (
                     SELECT
                         NEW.InventoryControlId AS InventoryControlId,
                         II.SKU AS SKU,
                         II.Variant AS Variant,
-                        COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate
+                        CAST(COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS STRING) AS ControlDate
                     FROM
                         InventoryItem II
                     LEFT JOIN
diff --git a/test/sql-tap/tkt1444.test.lua b/test/sql-tap/tkt1444.test.lua
index 82a5ded25..fb148bc5f 100755
--- a/test/sql-tap/tkt1444.test.lua
+++ b/test/sql-tap/tkt1444.test.lua
@@ -30,8 +30,8 @@ test:do_execsql_test(
     [[
         CREATE TABLE DemoTable (id  INT primary key, x INTEGER, TextKey TEXT, DKey NUMBER);
         CREATE INDEX DemoTableIdx ON DemoTable (TextKey);
-        INSERT INTO DemoTable VALUES(1, 9,8,7);
-        INSERT INTO DemoTable VALUES(2, 1,2,3);
+        INSERT INTO DemoTable VALUES(1, 9,'8',7);
+        INSERT INTO DemoTable VALUES(2, 1,'2',3);
         CREATE VIEW DemoView AS SELECT x, TextKey, DKey FROM DemoTable ORDER BY TextKey;
         SELECT x,TextKey,DKey FROM DemoTable UNION ALL SELECT * FROM DemoView ORDER BY 1;
     ]], {
diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua
index 7ceec4702..de77e61e9 100755
--- a/test/sql-tap/tkt3493.test.lua
+++ b/test/sql-tap/tkt3493.test.lua
@@ -29,8 +29,8 @@ test:do_execsql_test(
         START TRANSACTION;
         INSERT INTO A VALUES(1,'123');
         INSERT INTO A VALUES(2,'456');
-        INSERT INTO B VALUES(1,1);
-        INSERT INTO B VALUES(2,2);
+        INSERT INTO B VALUES(1,'1');
+        INSERT INTO B VALUES(2,'2');
         INSERT INTO A_B VALUES(1,1);
         INSERT INTO A_B VALUES(2,2);
         COMMIT;
@@ -116,7 +116,7 @@ test:do_execsql_test(
     "tkt3493-2.1",
     [[
         CREATE TABLE t1(a TEXT PRIMARY KEY, b INT);
-        INSERT INTO t1 VALUES(123, 456);
+        INSERT INTO t1 VALUES('123', 456);
     ]], {
         -- <tkt3493-2.1>
         
diff --git a/test/sql-tap/tkt3841.test.lua b/test/sql-tap/tkt3841.test.lua
index 5203d0cd4..56668f6a3 100755
--- a/test/sql-tap/tkt3841.test.lua
+++ b/test/sql-tap/tkt3841.test.lua
@@ -31,12 +31,12 @@ test:do_execsql_test(
 
         INSERT INTO table2 VALUES ('a', 'alist');
         INSERT INTO table2 VALUES ('b', 'blist');
-        INSERT INTO list VALUES ('a', 1);
-        INSERT INTO list VALUES ('a', 2);
-        INSERT INTO list VALUES ('a', 3);
-        INSERT INTO list VALUES ('b', 4);
-        INSERT INTO list VALUES ('b', 5);
-        INSERT INTO list VALUES ('b', 6);
+        INSERT INTO list VALUES ('a', '1');
+        INSERT INTO list VALUES ('a', '2');
+        INSERT INTO list VALUES ('a', '3');
+        INSERT INTO list VALUES ('b', '4');
+        INSERT INTO list VALUES ('b', '5');
+        INSERT INTO list VALUES ('b', '6');
 
         SELECT
           table2.x,
diff --git a/test/sql-tap/triggerA.test.lua b/test/sql-tap/triggerA.test.lua
index fac51ca14..fc8ecfe17 100755
--- a/test/sql-tap/triggerA.test.lua
+++ b/test/sql-tap/triggerA.test.lua
@@ -283,7 +283,7 @@ test:do_test(
             CREATE TABLE result2(id INTEGER PRIMARY KEY, a TEXT,b INT);
             CREATE TRIGGER r5d INSTEAD OF DELETE ON v5 FOR EACH ROW BEGIN
               INSERT INTO result2(id, a,b) VALUES((SELECT coalesce(max(id),0) + 1 FROM result2),
-                                                  old.x, old.b);
+                                                  CAST(old.x AS STRING), old.b);
             END;
             DELETE FROM v5 WHERE x=5;
             SELECT a, b FROM result2;
@@ -301,7 +301,7 @@ test:do_test(
             DELETE FROM result4;
             CREATE TRIGGER r5u INSTEAD OF UPDATE ON v5 FOR EACH ROW BEGIN
               INSERT INTO result4(id, a,b,c,d) VALUES((SELECT coalesce(max(id),0) + 1 FROM result4),
-                                                      old.x, old.b, new.x, new.b);
+                                                      CAST(old.x AS STRING), old.b, CAST(new.x AS STRING), new.b);
             END;
             UPDATE v5 SET b = b+9900000 WHERE x BETWEEN 3 AND 5;
             SELECT a,b,c,d FROM result4 ORDER BY a;
diff --git a/test/sql-tap/unique.test.lua b/test/sql-tap/unique.test.lua
index 9818f90a8..6b0a7e20d 100755
--- a/test/sql-tap/unique.test.lua
+++ b/test/sql-tap/unique.test.lua
@@ -52,7 +52,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "unique-1.2",
     [[
-        INSERT INTO t1(a,b,c) VALUES(1,2,3)
+        INSERT INTO t1(a,b,c) VALUES(1,2,'3')
     ]], {
         -- <unique-1.2>
         0
@@ -62,7 +62,7 @@ test:do_catchsql_test(
 test:do_catchsql_test(
     "unique-1.3",
     [[
-        INSERT INTO t1(a,b,c) VALUES(1,3,4)
+        INSERT INTO t1(a,b,c) VALUES(1,3,'4')
     ]], {
         -- <unique-1.3>
         1, "Duplicate key exists in unique index 'pk_unnamed_T1_1' in space 'T1'"
@@ -83,7 +83,7 @@ test:do_execsql_test(
 test:do_catchsql_test(
     "unique-1.5",
     [[
-        INSERT INTO t1(a,b,c) VALUES(3,2,4)
+        INSERT INTO t1(a,b,c) VALUES(3,2,'4')
     ]], {
         -- <unique-1.5>
         1, "Duplicate key exists in unique index 'unique_unnamed_T1_2' in space 'T1'"
@@ -104,7 +104,7 @@ test:do_execsql_test(
 test:do_catchsql_test(
     "unique-1.7",
     [[
-        INSERT INTO t1(a,b,c) VALUES(3,4,5)
+        INSERT INTO t1(a,b,c) VALUES(3,4,'5')
     ]], {
         -- <unique-1.7>
         0
diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua
index e553b91c7..ab14c5edb 100755
--- a/test/sql-tap/view.test.lua
+++ b/test/sql-tap/view.test.lua
@@ -757,7 +757,7 @@ test:do_execsql_test(
     "view-10.1",
     [=[
         CREATE TABLE t3("9" integer primary key, "4" text);
-        INSERT INTO t3 VALUES(1,2);
+        INSERT INTO t3 VALUES(1,'2');
         CREATE VIEW v_t3_a AS SELECT a."9" FROM t3 AS a;
         CREATE VIEW v_t3_b AS SELECT "4" FROM t3;
         SELECT * FROM v_t3_a;
diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua
index 749201564..3aefcaca5 100755
--- a/test/sql-tap/where5.test.lua
+++ b/test/sql-tap/where5.test.lua
@@ -27,11 +27,11 @@ test:do_test("where5-1.0", function()
         CREATE TABLE t1(x TEXT primary key);
         CREATE TABLE t2(x integer primary key);
         CREATE TABLE t3(x integer PRIMARY KEY);
-        INSERT INTO t1 VALUES(-1);
-        INSERT INTO t1 VALUES(0);
-        INSERT INTO t1 VALUES(1);
-        INSERT INTO t2 SELECT * FROM t1;
-        INSERT INTO t3 SELECT * FROM t1;
+        INSERT INTO t1 VALUES('-1');
+        INSERT INTO t1 VALUES('0');
+        INSERT INTO t1 VALUES('1');
+        INSERT INTO t2 SELECT CAST(x AS INTEGER) FROM t1;
+        INSERT INTO t3 SELECT CAST(x AS INTEGER) FROM t1;
     ]]
     return test:execsql [[
         SELECT * FROM t1 WHERE x<0
diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua
index d98645fdc..fe5e28c70 100755
--- a/test/sql-tap/whereB.test.lua
+++ b/test/sql-tap/whereB.test.lua
@@ -112,24 +112,16 @@ test:do_execsql_test(
     -- </whereB-1.102>
     })
 
--- For this set of tests:
---
---  *   t1.y holds a text value with affinity TEXT
---  *   t2.b holds an integer value with affinity NONE
---
--- These values are not equal and because neither affinity is NUMERIC
--- no type conversion occurs.
---
 test:do_execsql_test(
     "whereB-2.1",
     [[
         DROP TABLE t1;
         DROP TABLE t2;
 
-        CREATE TABLE t1(x  INT primary key, y TEXT);    -- affinity of t1.y is TEXT
-        INSERT INTO t1 VALUES(1,99);
+        CREATE TABLE t1(x  INT primary key, y TEXT);
+        INSERT INTO t1 VALUES(1,'99');
 
-        CREATE TABLE t2(a  INT primary key, b SCALAR);  -- affinity of t2.b is NONE
+        CREATE TABLE t2(a  INT primary key, b SCALAR);
         CREATE INDEX t2b ON t2(b);
         INSERT INTO t2 VALUES(2, 99);