[Tarantool-patches] [PATCH 1/6] sql: remove implicit cast for assignment

Mergen Imeev imeevma at tarantool.org
Tue Jun 9 14:41:33 MSK 2020


Thank you for the review. My answers and new patch below. Also,
there may be some problems with tabs replaced by spaces due to
sending answers from Thunderbird. Should I re-send patches?

On 01.06.2020 20:03, Vladislav Shpilevoy wrote:
> Thanks for the patch!
>
> See 9 comments below.
>
> On 28/05/2020 16:17, Mergen Imeev 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 724bc188b..2a941025c 100644
>> --- a/src/box/sql/vdbe.c
>> +++ b/src/box/sql/vdbe.c
>> @@ -414,6 +414,94 @@ sql_value_apply_type(
>>   	mem_apply_type((Mem *) pVal, type);
>>   }
>>   
>> +/**
>> + * Check that mem_type of the mem compatible with given type. In
> 1. 'compatible' -> 'is compatible'.

Fixed.


>
>> + * case of numeric values this fuction tries to comvert mem to
> 2. 'fuction' -> 'function', 'comvert' -> 'convert'.

Fixed.


>
>> + * given type and returns -1 if it is impossible.
>> + *
>> + * @param mem The value to check.
>> + * @param type The type to check.
>> + */
>> +static int
>> +mem_check_types(struct Mem *mem, enum field_type type)
>> +{
>> +	if ((mem->flags & MEM_Null) != 0)
>> +		return 0;
>> +	assert(type < field_type_MAX);
>> +	uint32_t flags = mem->flags;
>> +	switch (type) {
>> +	case FIELD_TYPE_UNSIGNED:
>> +		if ((flags & MEM_Int) != 0)
>> +			return -1;
>> +		if ((flags & MEM_Real) != 0 && mem->u.r < 0)
>> +			return -1;
>> +		FALLTHROUGH;
>> +	case FIELD_TYPE_INTEGER:
>> +		if ((flags & (MEM_Int | MEM_UInt)) != 0)
>> +			return 0;
>> +		if ((flags & MEM_Real) != 0) {
>> +			double d = mem->u.r;
>> +			int64_t i = (int64_t) d;
>> +			uint64_t u = (uint64_t) d;
> 3. This is undefined behaviour to cast double to int,
> if the double value is out of range of the int.
> For example, if double is < 0 or > UINT64_MAX, it is
> UB to cast it to uint64_t. If double is < INT64_MIN or
>> INT64_MAX, it is UB to cast it to int64_t.
> However until clang sanitizer is turned on, I don't know
> how you could test it. So up to you whether try to fix it
> now, or wait until the sanitizer is on board. And then
> probably I will fix it.

Thanks, but i think it is fixed now.


>
>> +			if (i == d) {
>> +				mem_set_int(mem, i, i <= -1);
>> +				return 0;
>> +			}
>> +			if (u == d) {
>> +				mem_set_u64(mem, u);
>> +				return 0;
>> +			}
>> +		}
>> +		return -1;
>> +	case FIELD_TYPE_BOOLEAN:
>> +		if ((flags & MEM_Bool) != 0)
>> +			return 0;
>> +		return -1;
>> +	case FIELD_TYPE_NUMBER:
>> +		if ((flags & (MEM_Real | MEM_Int | MEM_UInt)) != 0)
>> +			return 0;
>> +		return -1;
>> +	case FIELD_TYPE_DOUBLE:
>> +		if ((flags & MEM_Real) != 0)
>> +			return 0;
>> +		if ((flags & (MEM_Int | MEM_UInt)) != 0)
>> +			return sqlVdbeMemRealify(mem);
>> +		return -1;
>> +	case FIELD_TYPE_STRING:
>> +		if ((flags & MEM_Str) != 0)
>> +			return 0;
>> +		return -1;
>> +	case FIELD_TYPE_VARBINARY:
>> +		if ((flags & MEM_Blob) != 0)
>> +			return 0;
>> +		return -1;
>> +	case FIELD_TYPE_SCALAR:
>> +		/* Can't cast MAP and ARRAY to scalar types. */
>> +		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;
>> +	case FIELD_TYPE_MAP:
>> +		if ((flags & MEM_Subtype) != 0 &&
>> +		    mem->subtype == SQL_SUBTYPE_MSGPACK &&
>> +		    mp_typeof(*mem->z) == MP_MAP)
>> +			return 0;
>> +		return -1;
>> +	case FIELD_TYPE_ARRAY:
>> +		if ((flags & MEM_Subtype) != 0 &&
>> +		    mem->subtype == SQL_SUBTYPE_MSGPACK &&
>> +		    mp_typeof(*mem->z) == MP_ARRAY)
>> +			return 0;
>> +		return -1;
>> +	case FIELD_TYPE_ANY:
>> +		return 0;
>> +	default:
>> +		return -1;
>> +	}
>> +}
>> +
>>   /*
>>    * 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
>> @@ -2772,6 +2860,31 @@ case OP_ApplyType: {
>>   	break;
>>   }
>>   
>> +/* Opcode: CheckType P1 P2 * P4 *
>> + * Synopsis: type(r[P1 at P2])
>> + *
>> + * Check that types of P2 registers starting from register
>> + * P1 are compatible with given field_types.
> 4. 'with given field types in P4'.

Fixed.


>
>> + */
>> +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;
>> +}
>> +
>>   /* Opcode: MakeRecord P1 P2 P3 P4 P5
>>    * Synopsis: r[P3]=mkrec(r[P1 at P2])
>>    *
>> 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..9df90bf72
>> --- /dev/null
>> +++ b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua
>> @@ -0,0 +1,633 @@
>> +#!/usr/bin/env tarantool
>> +test = require("sqltester")
>> +test:plan(75)
>> +
>> +--
>> +-- Make sure that duting assignment there is no impolicit casts
> 5. 'duting' -> 'during', 'impolicit' -> 'implicit'.

Fixed.


>
>> +-- with exception of 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 (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)
> 6. Do we really want to keep this type of implicit
> assignment? After all, it is still double. Even though with 0
> fraction.
>
> What if I insert a double value > INT64_MAX? Double can store
> integers bigger than int64_t/uint64_t can.
ANSI says that there should be implicit cast between numeric
types. Also, in the discussion I suggested to use C rules for
this. However, now I think that it is a bad idea, though it can
be implemented quite easly. For now i allowed to convert them only
in case they represent the same number. I will write a letter to
discussion about this.

>
>> +    ]], {
>> +        0
>> +    })
>> +
>> +test:do_catchsql_test(
>> +    "gh-3809-4",
>> +    [[
>> +        INSERT INTO ti(i) VALUES (true)
>> +    ]], {
>> +        1, "Type mismatch: can not convert boolean to integer"
>> +    })
>> +
>> +test:do_catchsql_test(
>> +    "gh-3809-5",
>> +    [[
>> +        INSERT INTO ti(i) VALUES ('33')
>> +    ]], {
>> +        1, "Type mismatch: can not convert text 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)
> 7. What if I insert something > 2^53 or < -2^53? It
> can't be stored in double as is. Is it an error?

Fixed, according to statement above.


>
>> +    ]], {
>> +        0
>> +    })
>> +
>> diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua
>> index eeac5353a..9bbae5ca4 100755
>> --- a/test/sql-tap/numcast.test.lua
>> +++ b/test/sql-tap/numcast.test.lua
>> @@ -135,7 +135,7 @@ 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 real to integer"
> 8. Is it correct, that we can remove all the checks from OP_CheckType
> except numeric conversions, and everything will work fine, because
> box makes the type checks anyway? It seems OP_CheckType duplicates
> box's work.
This is correct in most cases. We will lose the implicit cast
between numeric values, but this can be done, for example, during
MakeRecord. In addition, all type mismatch errors will look the
same as in Lua.

>
>> diff --git a/test/sql-tap/tkt-3998683a16.test.lua b/test/sql-tap/tkt-3998683a16.test.lua
>> index 885dcf5cd..c91960c2c 100755
>> --- a/test/sql-tap/tkt-3998683a16.test.lua
>> +++ b/test/sql-tap/tkt-3998683a16.test.lua
>> @@ -26,24 +26,24 @@ 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);
>> +            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);
> 9. There are lines where the second column is a duplicate. I guess
> they can be removed now. Previously they were different in spacing
> only.
Fixed, duplicate tests removed. Not sure we should keep those the
ones left there.



New patch:


 From ed81efdca71104a8fefa95afa45e956a6b2d2743 Mon Sep 17 00:00:00 2001
From: Mergen Imeev <imeevma at gmail.com>
Date: Wed, 27 May 2020 13:49:11 +0300
Subject: [PATCH] sql: remove implicit cast for assignment

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/insert.c b/src/box/sql/insert.c
index 588e142d2..253304ba3 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_CheckType, reg, field_count, 0,
                (char *)colls_type, P4_DYNAMIC);
  }

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)
+{
+    if ((mem->flags & MEM_Null) != 0)
+        return 0;
+    assert(type < field_type_MAX);
+    uint32_t flags = mem->flags;
+    switch (type) {
+    case FIELD_TYPE_UNSIGNED:
+        if ((flags & MEM_Int) != 0)
+            return -1;
+        if ((flags & MEM_Real) != 0 && mem->u.r < 0)
+            return -1;
+        FALLTHROUGH;
+    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_BOOLEAN:
+        if ((flags & MEM_Bool) != 0)
+            return 0;
+        return -1;
+    case FIELD_TYPE_NUMBER:
+        if ((flags & (MEM_Real | MEM_Int | MEM_UInt)) != 0)
+            return 0;
+        return -1;
+    case FIELD_TYPE_DOUBLE:
+        if ((flags & MEM_Real) != 0)
+            return 0;
+        if ((flags & (MEM_Int | MEM_UInt)) == 0)
+            return -1;
+        if ((flags & MEM_Int) != 0) {
+            double d = mem->u.i;
+            if (mem->u.i == (int64_t)d) {
+                mem_set_double(mem, d);
+                return 0;
+            }
+        } else {
+            double d = mem->u.u;
+            if (mem->u.u == (uint64_t)d) {
+                mem_set_double(mem, d);
+                return 0;
+            }
+        }
+        return -1;
+    case FIELD_TYPE_STRING:
+        if ((flags & MEM_Str) != 0)
+            return 0;
+        return -1;
+    case FIELD_TYPE_VARBINARY:
+        if ((flags & MEM_Blob) != 0)
+            return 0;
+        return -1;
+    case FIELD_TYPE_SCALAR:
+        /* Can't cast MAP and ARRAY to scalar types. */
+        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;
+    case FIELD_TYPE_MAP:
+        if ((flags & MEM_Subtype) != 0 &&
+            mem->subtype == SQL_SUBTYPE_MSGPACK &&
+            mp_typeof(*mem->z) == MP_MAP)
+            return 0;
+        return -1;
+    case FIELD_TYPE_ARRAY:
+        if ((flags & MEM_Subtype) != 0 &&
+            mem->subtype == SQL_SUBTYPE_MSGPACK &&
+            mp_typeof(*mem->z) == MP_ARRAY)
+            return 0;
+        return -1;
+    case FIELD_TYPE_ANY:
+        return 0;
+    default:
+        return -1;
+    }
+}
+
  /*
   * 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
@@ -2776,6 +2883,31 @@ case OP_ApplyType: {
      break;
  }

+/* Opcode: CheckType P1 P2 * P4 *
+ * Synopsis: type(r[P1 at 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;
+}
+
  /* Opcode: MakeRecord P1 P2 P3 P4 P5
   * Synopsis: r[P3]=mkrec(r[P1 at P2])
   *
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index 2c50b6768..edb216a8a 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -517,6 +517,11 @@ mem_set_u64(struct Mem *mem, uint64_t value);
  void
  mem_set_int(struct Mem *mem, int64_t value, bool is_neg);

+/** Set double value and MEM_Real flag. */
+void
+mem_set_double(struct Mem *mem, double value);
+
+
  void sqlVdbeMemSetDouble(Mem *, double);
  void sqlVdbeMemInit(Mem *, sql *, u32);
  void sqlVdbeMemSetNull(Mem *);
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)
+{
+    mem->u.r = value;
+    MemSetTypeFlag(mem, MEM_Real);
+}
+
  /*
   * Delete any previous value and set the value stored in *pMem to val,
   * manifest type 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"
          -- </autoinc-10.2>
      })

@@ -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..de72cf3a4
--- /dev/null
+++ b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua
@@ -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.
+--
+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)
+    ]], {
+        0
+    })
+
+test:do_catchsql_test(
+    "gh-3809-4",
+    [[
+        INSERT INTO ti(i) VALUES (true)
+    ]], {
+        1, "Type mismatch: can not convert boolean to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-5",
+    [[
+        INSERT INTO ti(i) VALUES ('33')
+    ]], {
+        1, "Type mismatch: can not convert text 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);
+    ]], {
+        1, "Type mismatch: can not convert unsigned to double"
+    })
+
+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 boolean to double"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-12",
+    [[
+        INSERT INTO td(d) VALUES ('33')
+    ]], {
+        1, "Type mismatch: can not convert text 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, 22.2
+    })
+
+test:do_catchsql_test(
+    "gh-3809-15",
+    [[
+        INSERT INTO tb(b) VALUES (11)
+    ]], {
+        1, "Type mismatch: can not convert unsigned to boolean"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-16",
+    [[
+        INSERT INTO tb(b) VALUES (22.2)
+    ]], {
+        1, "Type mismatch: can not convert real 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 text 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 unsigned to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-22",
+    [[
+        INSERT INTO tt(t) VALUES (22.2)
+    ]], {
+        1, "Type mismatch: can not convert real to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-23",
+    [[
+        INSERT INTO tt(t) VALUES (true)
+    ]], {
+        1, "Type mismatch: can not convert boolean 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 unsigned to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-28",
+    [[
+        INSERT INTO tv(v) VALUES (22.2)
+    ]], {
+        1, "Type mismatch: can not convert real to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-29",
+    [[
+        INSERT INTO tv(v) VALUES (true)
+    ]], {
+        1, "Type mismatch: can not convert boolean to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-30",
+    [[
+        INSERT INTO tv(v) VALUES ('33')
+    ]], {
+        1, "Type mismatch: can not convert text 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"
+    })
+
+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 = 22.2 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert real 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 boolean to integer"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-44",
+    [[
+        UPDATE ti SET i = '33' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert text 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;
+    ]], {
+        1, "Type mismatch: can not convert unsigned to double"
+    })
+
+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 boolean to double"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-51",
+    [[
+        UPDATE td SET d = '33' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert text 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 unsigned to boolean"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-55",
+    [[
+        UPDATE tb SET b = 22.2 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert real 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 text 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 unsigned to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-61",
+    [[
+        UPDATE tt SET t = 22.2 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert real to string"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-62",
+    [[
+        UPDATE tt SET t = true WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert boolean 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 unsigned to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-67",
+    [[
+        UPDATE tv SET v = 22.2 WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert real to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-68",
+    [[
+        UPDATE tv SET v = true WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert boolean to varbinary"
+    })
+
+test:do_catchsql_test(
+    "gh-3809-69",
+    [[
+        UPDATE tv SET v = '33' WHERE a = 1;
+    ]], {
+        1, "Type mismatch: can not convert text 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..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.
---
  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>
@@ -825,7 +820,7 @@ end
  test:do_catchsql_test(
      "intpkey-13.5",
      [[
-        INSERT INTO t1 VALUES('+1234567890',3,4);
+        INSERT INTO t1 VALUES(1234567890,'3','4');
      ]], {
          -- <intpkey-13.5>
          0
@@ -842,7 +837,7 @@ test:do_execsql_test(
          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);
+        INSERT INTO t3 VALUES(3, 3, '3');
      ]], {
          -- <intpkey-14.1>

@@ -905,7 +900,7 @@ test:do_execsql_test(
  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..9bbae5ca4 100755
--- a/test/sql-tap/numcast.test.lua
+++ b/test/sql-tap/numcast.test.lua
@@ -135,7 +135,7 @@ 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 real to integer"
      })

  test:do_catchsql_test(
@@ -144,7 +144,7 @@ test:do_catchsql_test(
          INSERT INTO t VALUES(2.1);
          SELECT * FROM t;
      ]], {
-        1,"Tuple field 1 type does not match one required by operation: 
expected integer"
+        1,"Type mismatch: can not convert real to integer"
      })

  --
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);

diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index 112e41a12..e88183854 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -161,7 +161,7 @@ SELECT s FROM ts WHERE s IN (true, 1, 'abcd');
  INSERT INTO ts VALUES (true, 12345);
   | ---
   | - null
- | - 'Type mismatch: can not convert TRUE to integer'
+ | - 'Type mismatch: can not convert boolean to integer'
   | ...

  -- Check that we can create index on field of type BOOLEAN.
diff --git a/test/sql/persistency.result b/test/sql/persistency.result
index 6d14d4c4e..6ec2f06fc 100644
--- a/test/sql/persistency.result
+++ b/test/sql/persistency.result
@@ -370,7 +370,7 @@ box.execute("SELECT \"name\", \"opts\" FROM 
\"_trigger\"");
  box.execute("INSERT INTO foobar VALUES ('foobar trigger test', 8888)")
  ---
  - null
-- 'Type mismatch: can not convert foobar trigger test to integer'
+- 'Type mismatch: can not convert text to integer'
  ...
  box.execute("SELECT * FROM barfoo WHERE foo = 9999");
  ---
diff --git a/test/sql/types.result b/test/sql/types.result
index 54aff460e..6b0f7a651 100644
--- a/test/sql/types.result
+++ b/test/sql/types.result
@@ -1062,7 +1062,7 @@ box.execute("INSERT INTO t1 VALUES (0), (1), (2);")
  box.execute("INSERT INTO t1 VALUES (-3);")
  ---
  - null
-- 'Type mismatch: can not convert -3 to unsigned'
+- 'Type mismatch: can not convert integer to unsigned'
  ...
  box.execute("SELECT id FROM t1;")
  ---
@@ -1220,22 +1220,22 @@ box.execute("CREATE TABLE t (id INT PRIMARY KEY, 
v VARBINARY);")
  box.execute("INSERT INTO t VALUES(1, 1);")
  ---
  - null
-- 'Type mismatch: can not convert 1 to varbinary'
+- 'Type mismatch: can not convert unsigned to varbinary'
  ...
  box.execute("INSERT INTO t VALUES(1, 1.123);")
  ---
  - null
-- 'Type mismatch: can not convert 1.123 to varbinary'
+- 'Type mismatch: can not convert real to varbinary'
  ...
  box.execute("INSERT INTO t VALUES(1, true);")
  ---
  - null
-- 'Type mismatch: can not convert TRUE to varbinary'
+- 'Type mismatch: can not convert boolean to varbinary'
  ...
  box.execute("INSERT INTO t VALUES(1, 'asd');")
  ---
  - null
-- 'Type mismatch: can not convert asd to varbinary'
+- 'Type mismatch: can not convert text to varbinary'
  ...
  box.execute("INSERT INTO t VALUES(1, x'616263');")
  ---
@@ -1617,7 +1617,7 @@ s:insert({1, {1,2,3}})
  box.execute('INSERT INTO t1(a) SELECT a FROM t2;')
  ---
  - null
-- 'Type mismatch: can not convert [1, 2, 3] to scalar'
+- 'Type mismatch: can not convert varbinary to scalar'
  ...
  s:replace({1, 
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30}})
  ---
@@ -1627,8 +1627,7 @@ s:replace({1, 
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25
  box.execute('INSERT INTO t1(a) SELECT a FROM t2;')
  ---
  - null
-- 'Type mismatch: can not convert [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 
12, 13, 14,
-  15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30] to 
scalar'
+- 'Type mismatch: can not convert varbinary to scalar'
  ...
  --
  -- Make sure that the error will be displayed correctly even if
@@ -1653,13 +1652,7 @@ s:replace({1, long_array})
  box.execute('INSERT INTO t1(a) SELECT a FROM t2;')
  ---
  - null
-- 'Type mismatch: can not convert [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 
12, 13, 14,
-  15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 
32, 33, 34,
-  35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 
52, 53, 54,
-  55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 
72, 73, 74,
-  75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 
92, 93, 94,
-  95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 
110, 111,
-  112, 113, 114, 115, 116, 117, 11'
+- 'Type mismatch: can not convert varbinary to scalar'
  ...
  s:drop()
  ---
@@ -1680,7 +1673,7 @@ s:insert({1, {b = 1}})
  box.execute('INSERT INTO t1(a) SELECT a FROM t2;')
  ---
  - null
-- 'Type mismatch: can not convert {"b": 1} to scalar'
+- 'Type mismatch: can not convert varbinary to scalar'
  ...
  s:drop()
  ---



More information about the Tarantool-patches mailing list