From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 5FBA441C5DC for ; Thu, 25 Jun 2020 18:17:40 +0300 (MSK) From: imeevma@tarantool.org Date: Thu, 25 Jun 2020 18:17:38 +0300 Message-Id: <21d7145c1929bc4606c56e9a566477f248637ed1.1593096639.git.imeevma@gmail.com> In-Reply-To: References: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v3 2/8] sql: change implicit cast for assignment List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: korablev@tarantool.org, tsafin@tarantool.org, tarantool-patches@dev.tarantool.org Hi! Thank you for review. My answers and new patch below. I haven't included diffs since there were quite a few changes. I have included answers to review questions in the next three patches here. On 22.06.2020 11:23, Nikita Pettik wrote: > On 17 Jun 15:36, imeevma@tarantool.org wrote: >> This patch removes implicit cast for assignment, however, >> it is allowed to implicitly cast DOUBLE to INTEGER and >> vice versa. >> >> Closes #3809 >> --- >> >> diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c >> index 6b769805c..ae2622c9e 100644 >> --- a/src/box/sql/vdbe.c >> +++ b/src/box/sql/vdbe.c >> @@ -418,6 +418,113 @@ sql_value_apply_type( >> mem_apply_type((Mem *) pVal, type); >> } >> >> +/** >> + * Check that mem_type of the mem is compatible with given type. >> + * In the case of numeric values, this function tries to convert >> + * the mem to the specified type and returns -1 if this is not >> + * possible. >> + * >> + * @param mem The value to check. >> + * @param type The type to check. >> + */ >> +static int >> +mem_check_types(struct Mem *mem, enum field_type type) > > I'd rename it to mem_icast_to_type() or mem_impl_cast_to_type() > or smth like that. > I divided into 2 functions: mem_check_type (), which checks, and mem_convert_numeric(), which converts. >> +{ >> + if ((mem->flags & MEM_Null) != 0) >> + return 0; >> + assert(type < field_type_MAX); >> + uint32_t flags = mem->flags; >> + switch (type) { > > Instead of such long switch-cases could we organize it in one table > containing valid conversions? I mean sort of field_mp_type_is_compatible() > To provide not only check but execution mechanism you can fill > table with pointers to functions implementing particular casts. > Fixed, I think. >> + case FIELD_TYPE_INTEGER: >> + if ((flags & (MEM_Int | MEM_UInt)) != 0) >> + return 0; >> + if ((flags & MEM_Real) == 0) >> + return -1; >> + double d = mem->u.r; >> + if (d >= (double)UINT64_MAX || d < (double)INT64_MIN) >> + return -1; >> + if (d < (double)INT64_MAX) { >> + int64_t i = (int64_t) d; >> + if (i == d) { >> + mem_set_int(mem, i, i <= -1); >> + return 0; >> + } >> + return -1; >> + } >> + uint64_t u = (uint64_t) d; >> + if (u == d) { >> + mem_set_u64(mem, u); >> + return 0; >> + } >> + return -1; >> + case FIELD_TYPE_SCALAR: >> + /* Can't cast MAP and ARRAY to scalar types. */ > > Except for map and arrays we alread have decimal. IS this function > able to handle it? > We do not have a MEM type corresponding to MP_EXT, so we cannot get MP_EXT in this function. >> + if ((flags & MEM_Subtype) == 0 || >> + mem->subtype != SQL_SUBTYPE_MSGPACK) >> + return 0; >> + assert(mp_typeof(*mem->z) == MP_MAP || >> + mp_typeof(*mem->z) == MP_ARRAY); >> + return -1; >> @@ -2776,6 +2883,31 @@ case OP_ApplyType: { >> break; >> } >> >> +/* Opcode: CheckType P1 P2 * P4 * > > ApplyType was quite suitable name, meanwhile CheckType is a bit confusing > since in fact it doesn't only check but cast (apply, coerce or whatever) > mem to given type. > I replaced by ImplicitCast for now. >> + * Synopsis: type(r[P1@P2]) >> + * >> + * Check that types of P2 registers starting from register >> + * P1 are compatible with given with given field types in P4. >> + */ >> +case OP_CheckType: { >> + enum field_type *types = pOp->p4.types; >> + assert(types != NULL); >> + assert(types[pOp->p2] == field_type_MAX); >> + pIn1 = &aMem[pOp->p1]; >> + enum field_type type; >> + while((type = *(types++)) != field_type_MAX) { >> + assert(pIn1 <= &p->aMem[(p->nMem+1 - p->nCursor)]); >> + assert(memIsValid(pIn1)); >> + if (mem_check_types(pIn1, type) != 0) { >> + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, >> + mem_type_to_str(pIn1), field_type_strs[type]); >> + goto abort_due_to_error; >> + } >> + pIn1++; >> + } >> + break; >> +} >> + >> diff --git a/src/box/sql/vdbemem.c b/src/box/sql/vdbemem.c >> index 8dad2db9a..9e8586ffc 100644 >> --- a/src/box/sql/vdbemem.c >> +++ b/src/box/sql/vdbemem.c >> @@ -839,6 +839,13 @@ mem_set_int(struct Mem *mem, int64_t value, bool is_neg) >> } >> } >> >> +void >> +mem_set_double(struct Mem *mem, double value) >> +{ > > I see inconsistency with other setters: they provide auxiliary > clean-up in case mem has one of Agg/Dyn/Frame flags. Please > investigate whether it is really required and if it is so add > it to current one (or remove from other setters). > Fixed in previous commit. In fact, I found that such a function already exists, so I renamed it and refactored it a bit. >> + mem->u.r = value; >> + MemSetTypeFlag(mem, MEM_Real); >> +} >> + >> diff --git a/test/sql-tap/autoinc.test.lua b/test/sql-tap/autoinc.test.lua >> index 37e65e541..ec8dfeab1 100755 >> --- a/test/sql-tap/autoinc.test.lua >> +++ b/test/sql-tap/autoinc.test.lua >> @@ -618,7 +618,7 @@ test:do_catchsql_test( >> INSERT INTO t2 VALUES('asd'); >> ]], { >> -- >> - 1, "Type mismatch: can not convert asd to integer" >> + 1, "Type mismatch: can not convert text to integer" > > Having an opportunity to see which particular value can't be > converted is quite helpful. Let's leave it. > Fixed. Fully fixed in the last commit of the set. >> @@ -694,7 +694,7 @@ test:do_test( >> INSERT INTO t3928(b) VALUES('after-int-' || CAST(new.b AS TEXT)); >> END; >> DELETE FROM t3928 WHERE a!=1; >> - UPDATE t3928 SET b=456 WHERE a=1; >> + UPDATE t3928 SET b='456' WHERE a=1; >> SELECT * FROM t3928 ORDER BY a; >> ]]) >> end, { >> diff --git a/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua >> new file mode 100755 >> index 000000000..de72cf3a4 >> --- /dev/null >> +++ b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua > > It's not regression issue, so you can a) remove gh- prefix; > b) amalgamate it with another test (e.g. sql/types.test.lua). > It's up to you tho. > >> @@ -0,0 +1,649 @@ >> +#!/usr/bin/env tarantool >> +test = require("sqltester") >> +test:plan(77) >> + >> +-- >> +-- Make sure there are no implicit casts during assignment, >> +-- except for the implicit cast between numeric values. >> +-- > > Why do you still avoid .sql test format? > As far as I know, the TAP format is preferable. >> +test:execsql([[ >> + CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER); >> + CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE); >> + CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN); >> + CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT); >> + CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY); >> + CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR); >> +]]) >> + >> +test:do_catchsql_test( >> + "gh-3809-1", >> + [[ >> + INSERT INTO ti(i) VALUES (11) >> + ]], { >> + 0 >> + }) >> + >> +test:do_catchsql_test( >> + "gh-3809-2", >> + [[ >> + INSERT INTO ti(i) VALUES (22.2) >> + ]], { >> + 1, "Type mismatch: can not convert real to integer" >> + }) >> + >> +test:do_catchsql_test( >> + "gh-3809-3", >> + [[ >> + INSERT INTO ti(i) VALUES (33.0) > > Should this be valid? Could you please ask Peter to clarify this question? > It should, new rules I described in doc request. >> +test:execsql([[ >> + DELETE FROM ti; >> + DELETE FROM td; >> + DELETE FROM tb; >> + DELETE FROM tt; >> + DELETE FROM tv; >> + DELETE FROM ts; >> + INSERT INTO ti(a) VALUES(1); >> + INSERT INTO td(a) VALUES(1); >> + INSERT INTO tb(a) VALUES(1); >> + INSERT INTO tt(a) VALUES(1); >> + INSERT INTO tv(a) VALUES(1); >> + INSERT INTO ts(a) VALUES(1); >> +]]) > > Strange turn..Could you please supply each batch of tests with > brief description? > Added a comment here. >> +test:do_execsql_test( >> + "gh-3809-39", >> + [[ >> + SELECT * FROM ti, td, tb, tt, tv, ts; >> + ]], { >> + 1, "", 1, "", 1, "", 1, "", 1, "", 1, "" >> + }) >> diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua >> index e173e685c..ce66b7c1e 100755 >> --- a/test/sql-tap/index1.test.lua >> +++ b/test/sql-tap/index1.test.lua >> @@ -593,25 +593,17 @@ test:do_test( >> -- >> }) >> end >> --- integrity_check index-11.2 >> --- Numeric strings should compare as if they were numbers. So even if the >> --- strings are not character-by-character the same, if they represent the >> --- same number they should compare equal to one another. Verify that this >> --- is true in indices. >> --- >> --- Updated for sql v3: sql will now store these values as numbers >> --- (because the affinity of column a is NUMERIC) so the quirky >> --- representations are not retained. i.e. '+1.0' becomes '1'. >> + >> test:do_execsql_test( >> "index-12.1", >> [[ >> CREATE TABLE t4(id INT primary key, a NUMBER,b INT ); >> - INSERT INTO t4 VALUES(1, '0.0',1); >> - INSERT INTO t4 VALUES(2, '0.00',2); >> - INSERT INTO t4 VALUES(4, '-1.0',4); >> - INSERT INTO t4 VALUES(5, '+1.0',5); >> - INSERT INTO t4 VALUES(6, '0',6); >> - INSERT INTO t4 VALUES(7, '00000',7); >> + INSERT INTO t4 VALUES(1, 0.0, 1); >> + INSERT INTO t4 VALUES(2, 0.00, 2); >> + INSERT INTO t4 VALUES(4, -1.0, 4); >> + INSERT INTO t4 VALUES(5, +1.0, 5); >> + INSERT INTO t4 VALUES(6, 0, 6); >> + INSERT INTO t4 VALUES(7, 00000, 7); >> SELECT a FROM t4 ORDER BY b; > > Does this test make any sense now? > Removed these tests. >> ]], { >> -- >> @@ -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; >> ]], { >> -- >> 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( >> -- >> }) >> >> --- integrity_check intpkey-12.1 >> --- Try to use a string that looks like a floating point number as >> --- an integer primary key. This should actually work when the floating >> --- point value can be rounded to an integer without loss of data. >> --- > > Ditto > Removed these tests. >> test:do_execsql_test( >> "intpkey-13.1", >> [[ >> @@ -788,7 +783,7 @@ test:do_execsql_test( >> test:do_execsql_test( >> "intpkey-13.2", >> [[ >> - INSERT INTO t1 VALUES('1',2,3); >> + INSERT INTO t1 VALUES(1,'2','3'); >> SELECT * FROM t1 WHERE a=1; >> ]], { >> -- >> --- a/test/sql-tap/whereB.test.lua >> +++ b/test/sql-tap/whereB.test.lua >> @@ -112,24 +112,16 @@ test:do_execsql_test( >> -- >> }) >> >> --- For this set of tests: >> --- >> --- * t1.y holds a text value with affinity TEXT >> --- * t2.b holds an integer value with affinity NONE >> --- >> --- These values are not equal and because neither affinity is NUMERIC >> --- no type conversion occurs. >> --- > > Ditto > Removed these tests. >> test:do_execsql_test( >> "whereB-2.1", >> [[ >> DROP TABLE t1; >> DROP TABLE t2; >> >> - CREATE TABLE t1(x INT primary key, y TEXT); -- affinity of t1.y is TEXT >> - INSERT INTO t1 VALUES(1,99); >> + CREATE TABLE t1(x INT primary key, y TEXT); >> + INSERT INTO t1 VALUES(1,'99'); >> >> - CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE >> + CREATE TABLE t2(a INT primary key, b SCALAR); >> CREATE INDEX t2b ON t2(b); >> INSERT INTO t2 VALUES(2, 99); >> On 22.06.2020 11:48, Nikita Pettik wrote: > On 17 Jun 15:36, imeevma@tarantool.org wrote: >> This patch removes type changing from OP_MakeRecord. > > Please reflect user-visible changes in commit message. > Fixed. >> Part of #4230 >> --- >> src/box/sql/analyze.c | 7 +------ >> src/box/sql/delete.c | 8 ++------ >> src/box/sql/expr.c | 8 +------- >> src/box/sql/fk_constraint.c | 9 ++------- >> src/box/sql/select.c | 25 ++++++------------------- >> src/box/sql/update.c | 14 +++----------- >> src/box/sql/vdbe.c | 19 +------------------ >> test/sql-tap/in3.test.lua | 4 ++-- >> 8 files changed, 18 insertions(+), 76 deletions(-) >> >> diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua >> index e29db9d93..f7681640e 100755 >> --- a/test/sql-tap/in3.test.lua >> +++ b/test/sql-tap/in3.test.lua >> @@ -354,7 +354,7 @@ test:do_test( >> return exec_neph(" SELECT y IN (SELECT a FROM t1) FROM t2 ") >> end, { >> -- >> - 1, true >> + 1, false >> -- >> }) >> >> @@ -378,7 +378,7 @@ test:do_test( >> return exec_neph(" SELECT y IN (SELECT c FROM t1) FROM t2 ") >> end, { >> -- >> - 1, true >> + 1, false >> -- >> }) >> >> -- >> 2.25.1 >> On 22.06.2020 12:32, Nikita Pettik wrote: > On 17 Jun 15:36, imeevma@tarantool.org wrote: >> This patch removes implicit cast from STRING to numeric >> and vice versa of left operand of IN operator. >> >> Part of #4230 >> Part of #4692 >> --- >> src/box/sql/expr.c | 2 +- >> test/sql-tap/in3.test.lua | 14 +----- >> test/sql-tap/subquery.test.lua | 69 +--------------------------- >> test/sql-tap/tkt-80e031a00f.test.lua | 4 +- >> test/sql/boolean.result | 12 ++--- >> 5 files changed, 11 insertions(+), 90 deletions(-) >> >> diff --git a/test/sql-tap/tkt-80e031a00f.test.lua b/test/sql-tap/tkt-80e031a00f.test.lua >> index a0e6539e0..c883937ca 100755 >> --- a/test/sql-tap/tkt-80e031a00f.test.lua >> +++ b/test/sql-tap/tkt-80e031a00f.test.lua >> @@ -346,7 +346,7 @@ test:do_catchsql_test( >> SELECT 'hello' IN t1 >> ]], { >> -- >> - 1, 'Type mismatch: can not convert hello to integer' >> + 1, 'Type mismatch: can not convert text to integer' >> -- >> }) >> >> @@ -356,7 +356,7 @@ test:do_catchsql_test( >> SELECT 'hello' NOT IN t1 >> ]], { >> -- >> - 1, 'Type mismatch: can not convert hello to integer' >> + 1, 'Type mismatch: can not convert text to integer' >> -- >> }) > > Again, old format of error message containing value which > can't be converted seems more suitable to me. Mb it is worth > combining them putting in error message both type and value: > > ... convert 'hello' (type : text) to integer > Fixed in the last patch of the set. > Otherwise LGTM > >> diff --git a/test/sql/boolean.result b/test/sql/boolean.result >> index e88183854..c74713c34 100644 >> --- a/test/sql/boolean.result >> +++ b/test/sql/boolean.result >> @@ -3877,12 +3877,12 @@ SELECT false IN (0, 1, 2, 3); >> SELECT true IN (SELECT b FROM t7); >> | --- >> | - null >> - | - 'Type mismatch: can not convert TRUE to integer' >> + | - 'Type mismatch: can not convert boolean to integer' >> | ... On 22.06.2020 13:07, Nikita Pettik wrote: > On 17 Jun 15:36, imeevma@tarantool.org wrote: >> This patch replaces mem_apply_type() with mem_check_types() in >> OP_MustBeInt, which allows to remove implicit cast in some places, >> for example, in the IN statement. >> >> Part of #4230 >> --- >> src/box/sql/vdbe.c | 2 +- >> test/sql-tap/e_select1.test.lua | 17 +- >> test/sql-tap/in4.test.lua | 97 +-- >> test/sql-tap/join.test.lua | 1 - >> test/sql-tap/limit.test.lua | 2 +- >> test/sql-tap/tkt-9a8b09f8e6.test.lua | 24 +- >> test/sql-tap/tkt-fc7bd6358f.test.lua | 111 ---- >> test/sql-tap/transitive1.test.lua | 4 +- >> test/sql-tap/whereB.test.lua | 900 --------------------------- >> test/sql-tap/whereC.test.lua | 8 +- >> 10 files changed, 19 insertions(+), 1147 deletions(-) >> delete mode 100755 test/sql-tap/tkt-fc7bd6358f.test.lua >> delete mode 100755 test/sql-tap/whereB.test.lua >> >> diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c >> index eb5c89e9d..77b758833 100644 >> --- a/src/box/sql/vdbe.c >> +++ b/src/box/sql/vdbe.c >> @@ -2096,7 +2096,7 @@ case OP_AddImm: { /* in1 */ >> case OP_MustBeInt: { /* jump, in1 */ >> pIn1 = &aMem[pOp->p1]; >> if ((pIn1->flags & (MEM_Int | MEM_UInt)) == 0) { >> - mem_apply_type(pIn1, FIELD_TYPE_INTEGER); >> + mem_check_types(pIn1, FIELD_TYPE_INTEGER); >> if ((pIn1->flags & (MEM_Int | MEM_UInt)) == 0) { >> if (pOp->p2==0) { >> diag_set(ClientError, ER_SQL_TYPE_MISMATCH, > > Have the code coverage changed after these patches? > I'm wondering since a lot of tests are removed now (I believe > due to redundancy, so in perfect case coverage may even increase). > The coverage of src/box/sql/* has not changed according to coverall.io: https://coveralls.io/builds/31683427 >> index 7673426f4..578620fca 100755 >> --- a/test/sql-tap/e_select1.test.lua >> +++ b/test/sql-tap/e_select1.test.lua >> @@ -1,6 +1,6 @@ >> #!/usr/bin/env tarantool >> test = require("sqltester") >> -test:plan(510) >> +test:plan(509) >> New patch: commit 21d7145c1929bc4606c56e9a566477f248637ed1 Author: Mergen Imeev Date: Wed May 27 13:49:11 2020 +0300 sql: change implicit cast for assignment This patch changes implicit cast for assignment. Closes #3809 @TarantoolBot document Title: change implicit cast for comparison After this patch, these rules will apply during the implicit cast: | | STRING | BINARY | BOOLEAN | INTEGER | UNSIGNED | DOUBLE | | :--- | :---: | :---: | :---: | :---: | :---: | :---: | | STRING | A | N | N | N | N | N | | BINARY | N | A | N | N | N | N | | BOOLEAN | N | N | A | N | N | N | | INTEGER | N | N | N | A | S | Aa | | UNSIGNED | N | N | N | A | A | Aa | | DOUBLE | N | N | N | Sa | Sa | A | In this table, the types of the assigned value are on the left, and the types that should be after the assignment are at the top. 'A' - the assignment will be completed. 'N' - the assignment won't be completed. 'S' - the appointment may be unsuccessful. 'a' - after assignment, the resulting value may be approximated. Rules for numeric values are these: 1) Loss of significant digits (overflow) is an error. 2) Loss of insignificant digits is not an error. Example: ``` tarantool> box.execute([[CREATE TABLE t1(i INT PRIMARY KEY);]]) tarantool> box.execute([[INSERT INTO t1 VALUES ('1');]]) --- - null - 'Type mismatch: can not convert 1 to integer' ... tarantool> box.execute('INSERT INTO t1 VALUES (1.2345);') tarantool> box.execute('SELECT * FROM t1;') --- - metadata: - name: I type: integer rows: - [1] ... tarantool> box.execute([[CREATE TABLE t2(t text PRIMARY KEY);]]) tarantool> box.execute([[INSERT INTO t2 VALUES (1);]]) --- - null - 'Type mismatch: can not convert 1 to string' ... ``` diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c index 588e142d2..8a89f9904 100644 --- a/src/box/sql/insert.c +++ b/src/box/sql/insert.c @@ -69,7 +69,7 @@ sql_emit_table_types(struct Vdbe *v, struct space_def *def, int reg) for (uint32_t i = 0; i < field_count; ++i) colls_type[i] = def->fields[i].type; colls_type[field_count] = field_type_MAX; - sqlVdbeAddOp4(v, OP_ApplyType, reg, field_count, 0, + sqlVdbeAddOp4(v, OP_ImplicitCast, reg, field_count, 0, (char *)colls_type, P4_DYNAMIC); } diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h index 37283e506..f1d0345f9 100644 --- a/src/box/sql/sqlInt.h +++ b/src/box/sql/sqlInt.h @@ -397,6 +397,15 @@ sql_value_to_diag_str(sql_value *value); enum mp_type sql_value_type(sql_value *); +/* + * Return the MP_type of the value of the MEM. + * + * @param mem MEM with the correct MEM_type. + * @retval MP_type of the value. + */ +enum mp_type +sql_value_mp_type(struct Mem *mem); + static inline bool sql_value_is_null(sql_value *value) { diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 950f72ddd..009d577d2 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -417,6 +417,143 @@ sql_value_apply_type( mem_apply_type((Mem *) pVal, type); } +/** + * Check that MEM_type of the mem is compatible with given type. + * + * @param mem The MEM that contains the value to check. + * @param type The type to check. + * @retval 0 if the MEM_type of the value ​​and the given type are + * compatible, -1 otherwise. + */ +static int +mem_check_type(struct Mem *mem, enum field_type type) +{ + enum mp_type mp_type = sql_value_mp_type(mem); + assert(mp_type < MP_EXT); + if (field_mp_plain_type_is_compatible(type, mp_type, true)) + return 0; + return -1; +} + +/** + * Convert the numeric value contained in MEM to double. If the + * is_precise flag is set, the conversion will succeed only if it + * is lossless. + * + * @param mem The MEM that contains the numeric value. + * @param is_precise Flag. + * @retval 0 if the conversion was successful, -1 otherwise. + */ +static int +mem_convert_to_double(struct Mem *mem, bool is_precise) +{ + if ((mem->flags & (MEM_Int | MEM_UInt)) == 0) + return -1; + if ((mem->flags & MEM_Int) != 0) { + int64_t i = mem->u.i; + double d = (double)i; + if (!is_precise || i == (int64_t)d) + mem_set_double(mem, d); + else + return -1; + } else { + uint64_t u = mem->u.u; + double d = (double)u; + if (!is_precise || u == (uint64_t)d) + mem_set_double(mem, d); + else + return -1; + } + mem->field_type = FIELD_TYPE_DOUBLE; + return 0; +} + +/** + * Convert the numeric value contained in MEM to unsigned. If the + * is_precise flag is set, the conversion will succeed only if it + * is lossless. + * + * @param mem The MEM that contains the numeric value. + * @param is_precise Flag. + * @retval 0 if the conversion was successful, -1 otherwise. + */ +static int +mem_convert_to_unsigned(struct Mem *mem, bool is_precise) +{ + if ((mem->flags & MEM_Real) == 0) + return -1; + double d = mem->u.r; + if (d < 0.0 || d >= (double)UINT64_MAX) + return -1; + uint64_t u = (uint64_t)d; + if (!is_precise || d == (double)u) + mem_set_u64(mem, (uint64_t) d); + else + return -1; + mem->field_type = FIELD_TYPE_UNSIGNED; + return 0; +} + +/** + * Convert the numeric value contained in MEM to integer. If the + * is_precise flag is set, the conversion will succeed only if it + * is lossless. + * + * @param mem The MEM that contains the numeric value. + * @param is_precise Flag. + * @retval 0 if the conversion was successful, -1 otherwise. + */ +static int +mem_convert_to_integer(struct Mem *mem, bool is_precise) +{ + if ((mem->flags & MEM_Real) == 0) + return -1; + double d = mem->u.r; + if (d >= (double)UINT64_MAX || d < (double)INT64_MIN) + return -1; + if (d < (double)INT64_MAX) { + int64_t i = (int64_t)d; + if (!is_precise || d == (double)i) + mem_set_i64(mem, (int64_t) d); + else + return -1; + } else { + uint64_t u = (uint64_t)d; + if (!is_precise || d == (double)u) + mem_set_u64(mem, (uint64_t) d); + else + return -1; + } + mem->field_type = FIELD_TYPE_INTEGER; + return 0; +} + +/** + * Convert the numeric value contained in MEM to another numeric + * type. If the is_precise flag is set, the conversion will + * succeed only if it is lossless. + * + * @param mem The MEM that contains the numeric value. + * @param type The type to convert to. + * @param is_precise Flag. + * @retval 0 if the conversion was successful, -1 otherwise. + */ +static int +mem_convert_numeric(struct Mem *mem, enum field_type type, bool is_precise) +{ + if (!sql_type_is_numeric(type) || + (mem->flags & (MEM_Real | MEM_Int | MEM_UInt)) == 0) + return -1; + if (type == FIELD_TYPE_NUMBER) + return 0; + if (type == FIELD_TYPE_DOUBLE) + return mem_convert_to_double(mem, is_precise); + if (type == FIELD_TYPE_UNSIGNED) + return mem_convert_to_unsigned(mem, is_precise); + assert(type == FIELD_TYPE_INTEGER); + return mem_convert_to_integer(mem, is_precise); +} + /* * pMem currently only holds a string type (or maybe a BLOB that we can * interpret as a string if we want to). Compute its corresponding @@ -2773,6 +2910,36 @@ case OP_ApplyType: { break; } +/* Opcode: CheckType P1 P2 * P4 * + * Synopsis: type(r[P1@P2]) + * + * Check that types of P2 registers starting from register + * P1 are compatible with given with given field types in P4. + * If the MEM_type of the value and the given type are + * incompatible, but both are numeric, this opcode attempts to + * convert the value to the type. + */ +case OP_ImplicitCast: { + enum field_type *types = pOp->p4.types; + assert(types != NULL); + assert(types[pOp->p2] == field_type_MAX); + pIn1 = &aMem[pOp->p1]; + enum field_type type; + while((type = *(types++)) != field_type_MAX) { + assert(pIn1 <= &p->aMem[(p->nMem+1 - p->nCursor)]); + assert(memIsValid(pIn1)); + if (mem_check_type(pIn1, type) != 0 && + mem_convert_numeric(pIn1, type, false) != 0) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(pIn1), + field_type_strs[type]); + goto abort_due_to_error; + } + pIn1++; + } + break; +} + /* Opcode: MakeRecord P1 P2 P3 P4 P5 * Synopsis: r[P3]=mkrec(r[P1@P2]) * diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c index 4e103a653..087c661ec 100644 --- a/src/box/sql/vdbeapi.c +++ b/src/box/sql/vdbeapi.c @@ -225,6 +225,33 @@ sql_value_type(sql_value *pVal) } } +enum mp_type +sql_value_mp_type(struct Mem *mem) +{ + switch (mem->flags & MEM_PURE_TYPE_MASK) { + case MEM_Int: + return MP_INT; + case MEM_UInt: + return MP_UINT; + case MEM_Real: + return MP_DOUBLE; + case MEM_Str: + return MP_STR; + case MEM_Blob: + if ((mem->flags & MEM_Subtype) == 0 || + mem->subtype != SQL_SUBTYPE_MSGPACK) + return MP_BIN; + assert(mp_typeof(*mem->z) == MP_MAP || + mp_typeof(*mem->z) == MP_ARRAY); + return mp_typeof(*mem->z); + case MEM_Bool: + return MP_BOOL; + case MEM_Null: + return MP_NIL; + default: unreachable(); + } +} + /* Make a copy of an sql_value object */ sql_value * diff --git a/test/sql-tap/autoinc.test.lua b/test/sql-tap/autoinc.test.lua index 37e65e541..07442b60a 100755 --- a/test/sql-tap/autoinc.test.lua +++ b/test/sql-tap/autoinc.test.lua @@ -694,7 +694,7 @@ test:do_test( INSERT INTO t3928(b) VALUES('after-int-' || CAST(new.b AS TEXT)); END; DELETE FROM t3928 WHERE a!=1; - UPDATE t3928 SET b=456 WHERE a=1; + UPDATE t3928 SET b='456' WHERE a=1; SELECT * FROM t3928 ORDER BY a; ]]) end, { diff --git a/test/sql-tap/default.test.lua b/test/sql-tap/default.test.lua index d3e35c71c..f1def2b10 100755 --- a/test/sql-tap/default.test.lua +++ b/test/sql-tap/default.test.lua @@ -109,16 +109,12 @@ test:do_execsql_test( f VARCHAR(15), --COLLATE RTRIM, g INTEGER DEFAULT( 3600*12 ) ); - INSERT INTO t3 VALUES(null, 5, 'row1', 5.25, 8.67, 321, 432); + INSERT INTO t3 VALUES(null, 5, 'row1', 5.25, 8.67, '321', 432); SELECT a, typeof(a), b, typeof(b), c, typeof(c), d, typeof(d), e, typeof(e), f, typeof(f), g, typeof(g) FROM t3; ]], { -- - -- 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" -- }) 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'); ]], { -- diff --git a/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua new file mode 100755 index 000000000..a1809b3cb --- /dev/null +++ b/test/sql-tap/gh-3809-implicit-cast-assignment.test.lua @@ -0,0 +1,653 @@ +#!/usr/bin/env tarantool +test = require("sqltester") +test:plan(77) + +-- +-- Make sure there are no implicit casts during assignment, +-- except for the implicit cast between numeric values. +-- +test:execsql([[ + CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER); + CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE); + CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN); + CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT); + CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY); + CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR); +]]) + +test:do_catchsql_test( + "gh-3809-1", + [[ + INSERT INTO ti(i) VALUES (11) + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-2", + [[ + INSERT INTO ti(i) VALUES (100000000000000000000000000000000.1) + ]], { + 1, "Type mismatch: can not convert 1.0e+32 to integer" + }) + +test:do_catchsql_test( + "gh-3809-3", + [[ + INSERT INTO ti(i) VALUES (33.0) + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-4", + [[ + INSERT INTO ti(i) VALUES (true) + ]], { + 1, "Type mismatch: can not convert TRUE to integer" + }) + +test:do_catchsql_test( + "gh-3809-5", + [[ + INSERT INTO ti(i) VALUES ('33') + ]], { + 1, "Type mismatch: can not convert 33 to integer" + }) + +test:do_catchsql_test( + "gh-3809-6", + [[ + INSERT INTO ti(i) VALUES (X'3434') + ]], { + 1, "Type mismatch: can not convert varbinary to integer" + }) + +test:do_execsql_test( + "gh-3809-7", + [[ + SELECT * FROM ti; + ]], { + 1, 11, 2, 33 + }) + +test:do_catchsql_test( + "gh-3809-8", + [[ + INSERT INTO td(d) VALUES (11) + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-9", + [[ + INSERT INTO td(d) VALUES (100000000000000001); + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-10", + [[ + INSERT INTO td(d) VALUES (22.2) + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-11", + [[ + INSERT INTO td(d) VALUES (true) + ]], { + 1, "Type mismatch: can not convert TRUE to double" + }) + +test:do_catchsql_test( + "gh-3809-12", + [[ + INSERT INTO td(d) VALUES ('33') + ]], { + 1, "Type mismatch: can not convert 33 to double" + }) + +test:do_catchsql_test( + "gh-3809-13", + [[ + INSERT INTO td(d) VALUES (X'3434') + ]], { + 1, "Type mismatch: can not convert varbinary to double" + }) + +test:do_execsql_test( + "gh-3809-14", + [[ + SELECT * FROM td; + ]], { + 1, 11, 2, 100000000000000000, 3, 22.2 + }) + +test:do_catchsql_test( + "gh-3809-15", + [[ + INSERT INTO tb(b) VALUES (11) + ]], { + 1, "Type mismatch: can not convert 11 to boolean" + }) + +test:do_catchsql_test( + "gh-3809-16", + [[ + INSERT INTO tb(b) VALUES (22.2) + ]], { + 1, "Type mismatch: can not convert 22.2 to boolean" + }) + +test:do_catchsql_test( + "gh-3809-17", + [[ + INSERT INTO tb(b) VALUES (true) + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-18", + [[ + INSERT INTO tb(b) VALUES ('33') + ]], { + 1, "Type mismatch: can not convert 33 to boolean" + }) + +test:do_catchsql_test( + "gh-3809-19", + [[ + INSERT INTO tb(b) VALUES (X'3434') + ]], { + 1, "Type mismatch: can not convert varbinary to boolean" + }) + +test:do_execsql_test( + "gh-3809-20", + [[ + SELECT * FROM tb; + ]], { + 1, true + }) + +test:do_catchsql_test( + "gh-3809-21", + [[ + INSERT INTO tt(t) VALUES (11) + ]], { + 1, "Type mismatch: can not convert 11 to string" + }) + +test:do_catchsql_test( + "gh-3809-22", + [[ + INSERT INTO tt(t) VALUES (22.2) + ]], { + 1, "Type mismatch: can not convert 22.2 to string" + }) + +test:do_catchsql_test( + "gh-3809-23", + [[ + INSERT INTO tt(t) VALUES (true) + ]], { + 1, "Type mismatch: can not convert TRUE to string" + }) + +test:do_catchsql_test( + "gh-3809-24", + [[ + INSERT INTO tt(t) VALUES ('33') + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-25", + [[ + INSERT INTO tt(t) VALUES (X'3434') + ]], { + 1, "Type mismatch: can not convert varbinary to string" + }) + +test:do_execsql_test( + "gh-3809-26", + [[ + SELECT * FROM tt; + ]], { + 1, "33" + }) + +test:do_catchsql_test( + "gh-3809-27", + [[ + INSERT INTO tv(v) VALUES (11) + ]], { + 1, "Type mismatch: can not convert 11 to varbinary" + }) + +test:do_catchsql_test( + "gh-3809-28", + [[ + INSERT INTO tv(v) VALUES (22.2) + ]], { + 1, "Type mismatch: can not convert 22.2 to varbinary" + }) + +test:do_catchsql_test( + "gh-3809-29", + [[ + INSERT INTO tv(v) VALUES (true) + ]], { + 1, "Type mismatch: can not convert TRUE to varbinary" + }) + +test:do_catchsql_test( + "gh-3809-30", + [[ + INSERT INTO tv(v) VALUES ('33') + ]], { + 1, "Type mismatch: can not convert 33 to varbinary" + }) + +test:do_catchsql_test( + "gh-3809-31", + [[ + INSERT INTO tv(v) VALUES (X'3434') + ]], { + 0 + }) + +test:do_execsql_test( + "gh-3809-32", + [[ + SELECT * FROM tv; + ]], { + 1, "44" + }) + +test:do_catchsql_test( + "gh-3809-33", + [[ + INSERT INTO ts(s) VALUES (11) + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-34", + [[ + INSERT INTO ts(s) VALUES (22.2) + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-35", + [[ + INSERT INTO ts(s) VALUES (true) + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-36", + [[ + INSERT INTO ts(s) VALUES ('33') + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-37", + [[ + INSERT INTO ts(s) VALUES (X'3434') + ]], { + 0 + }) + +test:do_execsql_test( + "gh-3809-38", + [[ + SELECT * FROM ts; + ]], { + 1, 11, 2, 22.2, 3, true, 4, "33", 5, "44" + }) + +-- +-- This test suite verifies that ASSIGNMENT is working correctly +-- during an UPDATE. +-- +test:execsql([[ + DELETE FROM ti; + DELETE FROM td; + DELETE FROM tb; + DELETE FROM tt; + DELETE FROM tv; + DELETE FROM ts; + INSERT INTO ti(a) VALUES(1); + INSERT INTO td(a) VALUES(1); + INSERT INTO tb(a) VALUES(1); + INSERT INTO tt(a) VALUES(1); + INSERT INTO tv(a) VALUES(1); + INSERT INTO ts(a) VALUES(1); +]]) + +test:do_execsql_test( + "gh-3809-39", + [[ + SELECT * FROM ti, td, tb, tt, tv, ts; + ]], { + 1, "", 1, "", 1, "", 1, "", 1, "", 1, "" + }) + +test:do_catchsql_test( + "gh-3809-40", + [[ + UPDATE ti SET i = 11 WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-41", + [[ + UPDATE ti SET i = 100000000000000000000000000000000.1 WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 1.0e+32 to integer" + }) + +test:do_catchsql_test( + "gh-3809-42", + [[ + UPDATE ti SET i = 33.0 WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-43", + [[ + UPDATE ti SET i = true WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert TRUE to integer" + }) + +test:do_catchsql_test( + "gh-3809-44", + [[ + UPDATE ti SET i = '33' WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 33 to integer" + }) + +test:do_catchsql_test( + "gh-3809-45", + [[ + UPDATE ti SET i = X'3434' WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert varbinary to integer" + }) + +test:do_execsql_test( + "gh-3809-46", + [[ + SELECT * FROM ti; + ]], { + 1, 33 + }) + +test:do_catchsql_test( + "gh-3809-47", + [[ + UPDATE td SET d = 11 WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-48", + [[ + UPDATE td SET d = 100000000000000001 WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-49", + [[ + UPDATE td SET d = 22.2 WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-50", + [[ + UPDATE td SET d = true WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert TRUE to double" + }) + +test:do_catchsql_test( + "gh-3809-51", + [[ + UPDATE td SET d = '33' WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 33 to double" + }) + +test:do_catchsql_test( + "gh-3809-52", + [[ + UPDATE td SET d = X'3434' WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert varbinary to double" + }) + +test:do_execsql_test( + "gh-3809-53", + [[ + SELECT * FROM td; + ]], { + 1, 22.2 + }) + +test:do_catchsql_test( + "gh-3809-54", + [[ + UPDATE tb SET b = 11 WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 11 to boolean" + }) + +test:do_catchsql_test( + "gh-3809-55", + [[ + UPDATE tb SET b = 22.2 WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 22.2 to boolean" + }) + +test:do_catchsql_test( + "gh-3809-56", + [[ + UPDATE tb SET b = true WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-57", + [[ + UPDATE tb SET b = '33' WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 33 to boolean" + }) + +test:do_catchsql_test( + "gh-3809-58", + [[ + UPDATE tb SET b = X'3434' WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert varbinary to boolean" + }) + +test:do_execsql_test( + "gh-3809-59", + [[ + SELECT * FROM tb; + ]], { + 1, true + }) + +test:do_catchsql_test( + "gh-3809-60", + [[ + UPDATE tt SET t = 11 WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 11 to string" + }) + +test:do_catchsql_test( + "gh-3809-61", + [[ + UPDATE tt SET t = 22.2 WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 22.2 to string" + }) + +test:do_catchsql_test( + "gh-3809-62", + [[ + UPDATE tt SET t = true WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert TRUE to string" + }) + +test:do_catchsql_test( + "gh-3809-63", + [[ + UPDATE tt SET t = '33' WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-64", + [[ + UPDATE tt SET t = X'3434' WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert varbinary to string" + }) + +test:do_execsql_test( + "gh-3809-65", + [[ + SELECT * FROM tt; + ]], { + 1, "33" + }) + +test:do_catchsql_test( + "gh-3809-66", + [[ + UPDATE tv SET v = 11 WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 11 to varbinary" + }) + +test:do_catchsql_test( + "gh-3809-67", + [[ + UPDATE tv SET v = 22.2 WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 22.2 to varbinary" + }) + +test:do_catchsql_test( + "gh-3809-68", + [[ + UPDATE tv SET v = true WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert TRUE to varbinary" + }) + +test:do_catchsql_test( + "gh-3809-69", + [[ + UPDATE tv SET v = '33' WHERE a = 1; + ]], { + 1, "Type mismatch: can not convert 33 to varbinary" + }) + +test:do_catchsql_test( + "gh-3809-70", + [[ + UPDATE tv SET v = X'3434' WHERE a = 1; + ]], { + 0 + }) + +test:do_execsql_test( + "gh-3809-71", + [[ + SELECT * FROM tv; + ]], { + 1, "44" + }) + +test:do_catchsql_test( + "gh-3809-72", + [[ + UPDATE ts SET s = 11 WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-73", + [[ + UPDATE ts SET s = 22.2 WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-74", + [[ + UPDATE ts SET s = true WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-75", + [[ + UPDATE ts SET s = '33' WHERE a = 1; + ]], { + 0 + }) + +test:do_catchsql_test( + "gh-3809-76", + [[ + UPDATE ts SET s = X'3434' WHERE a = 1; + ]], { + 0 + }) + +test:do_execsql_test( + "gh-3809-77", + [[ + SELECT * FROM ts; + ]], { + 1, "44" + }) + +test:finish_test() diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua index e173e685c..ce66b7c1e 100755 --- a/test/sql-tap/index1.test.lua +++ b/test/sql-tap/index1.test.lua @@ -593,25 +593,17 @@ test:do_test( -- }) 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; ]], { -- @@ -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; ]], { -- 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; ]], { -- @@ -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; ]], { -- diff --git a/test/sql-tap/intpkey.test.lua b/test/sql-tap/intpkey.test.lua index b6b186632..684a24114 100755 --- a/test/sql-tap/intpkey.test.lua +++ b/test/sql-tap/intpkey.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(40) +test:plan(31) --!./tcltestrunner.lua -- 2001 September 15 @@ -770,142 +770,13 @@ test:do_execsql_test( -- }) --- integrity_check intpkey-12.1 --- Try to use a string that looks like a floating point number as --- an integer primary key. This should actually work when the floating --- point value can be rounded to an integer without loss of data. --- -test:do_execsql_test( - "intpkey-13.1", - [[ - SELECT * FROM t1 WHERE a=1; - ]], { - -- - - -- - }) - -test:do_execsql_test( - "intpkey-13.2", - [[ - INSERT INTO t1 VALUES('1',2,3); - SELECT * FROM t1 WHERE a=1; - ]], { - -- - 1, "2", "3" - -- - }) - --- MUST_WORK_TEST -if (0 > 0) then - -- Tarantool: issue submitted #2315 - test:do_catchsql_test( - "intpkey-13.3", - [[ - INSERT INTO t1 VALUES('1.5',3,4); - ]], { - -- - 1, "datatype mismatch" - -- - }) - - test:do_catchsql_test( - "intpkey-13.4", - [[ - INSERT INTO t1 VALUES(x'123456',3,4); - ]], { - -- - 1, "datatype mismatch" - -- - }) - - - -end -test:do_catchsql_test( - "intpkey-13.5", - [[ - INSERT INTO t1 VALUES('+1234567890',3,4); - ]], { - -- - 0 - -- - }) - --- Compare an INTEGER PRIMARY KEY against a TEXT expression. The INTEGER --- affinity should be applied to the text value before the comparison --- takes place. --- -test:do_execsql_test( - "intpkey-14.1", - [[ - CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT); - INSERT INTO t3 VALUES(1, 1, 'one'); - INSERT INTO t3 VALUES(2, 2, '2'); - INSERT INTO t3 VALUES(3, 3, 3); - ]], { - -- - - -- - }) - -test:do_execsql_test( - "intpkey-14.2", - [[ - SELECT * FROM t3 WHERE a>2; - ]], { - -- - 3, 3, "3" - -- - }) - -test:do_execsql_test( - "intpkey-14.3", - [[ - SELECT * FROM t3 WHERE a>'2'; - ]], { - -- - 3, 3, "3" - -- - }) - -test:do_execsql_test( - "intpkey-14.4", - [[ - SELECT * FROM t3 WHERE a<'2'; - ]], { - -- - 1, 1, "one" - -- - }) - -test:do_execsql_test( - "intpkey-14.5", - [[ - SELECT * FROM t3 WHERE a - 1, 1, "one" - -- - }) - -test:do_execsql_test( - "intpkey-14.6", - [[ - SELECT * FROM t3 WHERE a=c; - ]], { - -- - 2, 2, "2", 3, 3, "3" - -- - }) - -- Check for proper handling of primary keys greater than 2^31. -- Ticket #1188 -- test:do_execsql_test( "intpkey-15.1", [[ - INSERT INTO t1 VALUES(2147483647, 'big-1', 123); + INSERT INTO t1 VALUES(2147483647, 'big-1', '123'); SELECT * FROM t1 WHERE a>2147483648; ]], { -- 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; ]], { -- 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, { -- 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; ]], { -- @@ -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; ]], { -- diff --git a/test/sql-tap/numcast.test.lua b/test/sql-tap/numcast.test.lua index eeac5353a..3161e48fa 100755 --- a/test/sql-tap/numcast.test.lua +++ b/test/sql-tap/numcast.test.lua @@ -135,16 +135,16 @@ test:do_catchsql_test( INSERT INTO t VALUES(20000000000000000000.01); SELECT * FROM t; ]], { - 1,"Tuple field 1 type does not match one required by operation: expected integer" + 1,"Type mismatch: can not convert 2.0e+19 to integer" }) -test:do_catchsql_test( +test:do_execsql_test( "cast-2.9", [[ INSERT INTO t VALUES(2.1); SELECT * FROM t; ]], { - 1,"Tuple field 1 type does not match one required by operation: expected integer" + 2, 9223372036854775808ULL, 18000000000000000000ULL }) -- diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua index fbebfab37..9a969bf3c 100755 --- a/test/sql-tap/select1.test.lua +++ b/test/sql-tap/select1.test.lua @@ -231,7 +231,7 @@ string.format([[ CREATE TABLE t3(id INT, a TEXT, b TEXT, PRIMARY KEY(id)); INSERT INTO t3 VALUES(1, 'abc',NULL); INSERT INTO t3 VALUES(2, NULL,'xyz'); - INSERT INTO t3 SELECT f1, * FROM test1; + INSERT INTO t3 SELECT f1, CAST(f1 AS STRING), CAST(f2 AS STRING) FROM test1; DROP TABLE IF EXISTS t4; CREATE TABLE t4(id INT, a INT , b TEXT , PRIMARY KEY(id)); INSERT INTO t4 VALUES(1, NULL,'%s'); @@ -1671,8 +1671,8 @@ test:do_execsql_test( [[ DELETE FROM t3; DELETE FROM t4; - INSERT INTO t3 VALUES(0,1,2); - INSERT INTO t4 VALUES(0,3,4); + INSERT INTO t3 VALUES(0,'1','2'); + INSERT INTO t4 VALUES(0,3,'4'); SELECT * FROM t3, t4; ]], { -- @@ -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'); ]], { -- 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 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; ]], { -- 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, { -- - 1, 2, 3, 4, 5, 6, 11, 12, 13, 14, 15, 16, 21, 22, 23, 24, 25, 26 + 1, 2, 3, 4, 5, 6 -- }) 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); ]], { -- 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') ]], { -- 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') ]], { -- 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') ]], { -- 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') ]], { -- 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( -- }) --- 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);