[Tarantool-patches] [PATCH v2 1/7] sql: remove implicit cast for assignment
imeevma at tarantool.org
imeevma at tarantool.org
Thu Jun 11 15:54:07 MSK 2020
Hi! Thanks for the review! My answer and patch are below. The
patch is the same.
On 10.06.2020 01:28, Vladislav Shpilevoy wrote:
> Hi! Thanks for the patch!
>
>>>> + ]], {
>>>> + 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.
>
> I didn't meant to remove number casts. Only non-numeric checks.
> For example, if mem_check_type is called on a string, and target
> is number, we can ignore that, and let box raise an error.
>
> This should become even simpler, when MEM_<type> flags are
> replaced with enum field_type.
>
> Please, do it here, if it is easy, or file a new ticket (I mean
> error fallthrough down to box, not MEM_<type> removal).
It is not very easy to do since this function used in some other
places. I filled a new ticked (#5074).
Patch:
>From be6254fb656d0b688ff0c85a092d30703903a20f 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