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 4758B445323 for ; Mon, 13 Jul 2020 08:33:12 +0300 (MSK) From: imeevma@tarantool.org Date: Mon, 13 Jul 2020 08:33:10 +0300 Message-Id: <965068a9fb4a38da3c64e15ab531df57f879ec14.1594618005.git.imeevma@gmail.com> In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v4 4/5] 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 This patch changes implicit cast for assignment. Closes #3809 Needed for #4159 Part of #4230 @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' ... ``` --- src/box/sql/vdbe.c | 156 ++++- src/box/sql/vdbeInt.h | 4 + test/sql-tap/autoinc.test.lua | 2 +- test/sql-tap/default.test.lua | 6 +- test/sql-tap/e_select1.test.lua | 27 +- test/sql-tap/in3.test.lua | 14 +- test/sql-tap/in4.test.lua | 96 +-- test/sql-tap/index1.test.lua | 24 +- test/sql-tap/insert3.test.lua | 10 +- test/sql-tap/intpkey.test.lua | 133 +--- test/sql-tap/limit.test.lua | 2 +- test/sql-tap/minmax2.test.lua | 6 +- test/sql-tap/misc1.test.lua | 24 +- test/sql-tap/numcast.test.lua | 6 +- test/sql-tap/select1.test.lua | 8 +- test/sql-tap/select4.test.lua | 12 +- test/sql-tap/select7.test.lua | 2 +- test/sql-tap/sort.test.lua | 8 +- test/sql-tap/subquery.test.lua | 69 +- test/sql-tap/tkt-3998683a16.test.lua | 26 +- test/sql-tap/tkt-54844eea3f.test.lua | 8 +- test/sql-tap/tkt-7bbfb7d442.test.lua | 4 +- test/sql-tap/tkt-9a8b09f8e6.test.lua | 62 +- test/sql-tap/tkt-f973c7ac31.test.lua | 18 +- test/sql-tap/tkt-fc7bd6358f.test.lua | 111 ---- test/sql-tap/tkt1444.test.lua | 4 +- test/sql-tap/tkt3493.test.lua | 6 +- test/sql-tap/tkt3841.test.lua | 12 +- test/sql-tap/transitive1.test.lua | 4 +- test/sql-tap/triggerA.test.lua | 4 +- test/sql-tap/unique.test.lua | 8 +- test/sql-tap/view.test.lua | 2 +- test/sql-tap/where5.test.lua | 10 +- test/sql-tap/whereB.test.lua | 908 --------------------------- test/sql-tap/whereC.test.lua | 8 +- test/sql/types.result | 620 ++++++++++++++++++ test/sql/types.test.lua | 129 ++++ 37 files changed, 1042 insertions(+), 1511 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 950f72ddd..863f38f5d 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -417,6 +417,144 @@ 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 TRUE if the MEM_type of the value and the given type + * are compatible, FALSE otherwise. + */ +static bool +mem_is_type_compatible(struct Mem *mem, enum field_type type) +{ + enum mp_type mp_type = mem_mp_type(mem); + assert(mp_type < MP_EXT); + return field_mp_plain_type_is_compatible(type, mp_type, true); +} + +/** + * 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_Real) != 0) + return 0; + 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; + } + 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_UInt) != 0) + return 0; + if ((mem->flags & MEM_Int) != 0) + return -1; + 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; + 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_UInt | MEM_Int)) != 0) + return 0; + 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_int(mem, (int64_t) d, d < 0); + else + return -1; + } else { + uint64_t u = (uint64_t)d; + if (!is_precise || d == (double)u) + mem_set_int(mem, (uint64_t) d, false); + else + return -1; + } + 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_to_numeric(struct Mem *mem, enum field_type type, bool is_precise) +{ + assert(mp_type_is_numeric(mem_mp_type(mem)) && + sql_type_is_numeric(type)); + assert(type != FIELD_TYPE_NUMBER); + 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 @@ -2747,11 +2885,11 @@ case OP_Fetch: { /* Opcode: ApplyType P1 P2 * P4 * * Synopsis: type(r[P1@P2]) * - * Apply types to a range of P2 registers starting with P1. - * - * P4 is a string that is P2 characters long. The nth character of the - * string indicates the column type that should be used for the nth - * memory cell in the range. + * Check that types of P2 registers starting from register P1 are + * compatible with given field types in P4. If the MEM_type of the + * value and the given type are incompatible according to + * field_mp_plain_type_is_compatible(), but both are numeric, + * this opcode attempts to convert the value to the type. */ case OP_ApplyType: { enum field_type *types = pOp->p4.types; @@ -2762,7 +2900,13 @@ case OP_ApplyType: { while((type = *(types++)) != field_type_MAX) { assert(pIn1 <= &p->aMem[(p->nMem+1 - p->nCursor)]); assert(memIsValid(pIn1)); - if (mem_apply_type(pIn1, type) != 0) { + if (mem_is_type_compatible(pIn1, type)) { + pIn1++; + continue; + } + if (!mp_type_is_numeric(mem_mp_type(pIn1)) || + !sql_type_is_numeric(type) || + mem_convert_to_numeric(pIn1, type, false) != 0) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, sql_value_to_diag_str(pIn1), field_type_strs[type]); diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h index 44c27bdb7..ad46ab129 100644 --- a/src/box/sql/vdbeInt.h +++ b/src/box/sql/vdbeInt.h @@ -566,6 +566,10 @@ mem_mp_type(struct Mem *mem); */ #define mp_type_is_bloblike(X) ((X) == MP_BIN || (X) == MP_ARRAY || (X) == MP_MAP) +/** Return TRUE if MP_type of X is numeric, FALSE otherwise. */ +#define mp_type_is_numeric(X) ((X) == MP_INT || (X) == MP_UINT ||\ + (X) == MP_DOUBLE) + /** * Memory cell mem contains the context of an aggregate function. * This routine calls the finalize method for that function. The 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..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) --!./tcltestrunner.lua -- 2010 July 16 @@ -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'); ]], { -- @@ -2150,7 +2150,6 @@ test:do_select_tests( {"2", "SELECT b FROM f1 ORDER BY a LIMIT 2+3 ", {"a", "b", "c", "d", "e"}}, {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') ", {"a", "b", "c", "d", "e"}}, {"4", "SELECT b FROM f1 ORDER BY a LIMIT 5.0 ", {"a", "b", "c", "d", "e"}}, - {"5", "SELECT b FROM f1 ORDER BY a LIMIT '5' ", {"a", "b", "c", "d", "e"}}, }) -- EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value @@ -2195,7 +2194,7 @@ test:do_select_tests( {"1", "SELECT b FROM f1 ORDER BY a LIMIT 0 ", {}}, {"2", "SELECT b FROM f1 ORDER BY a DESC LIMIT 4 ", {"z", "y", "x", "w"}}, {"3", "SELECT b FROM f1 ORDER BY a DESC LIMIT 8 ", {"z", "y", "x", "w", "v", "u", "t", "s"}}, - {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT '12' ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}}, + {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT 12 ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}}, }) -- EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return @@ -2240,10 +2239,10 @@ test:do_select_tests( {"1", "SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5", {"f", "g", "h", "i", "j", "k", "l", "m", "n", "o"}}, {"2", "SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10", {"k", "l", "m", "n", "o"}}, {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b='j') OFFSET (SELECT a FROM f1 WHERE b='b') ", {"c", "d", "e", "f", "g", "h", "i", "j", "k", "l"}}, - {"4", "SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 ", {"d", "e", "f", "g", "h"}}, - {"5", "SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 ", {"a", "b", "c", "d", "e"}}, + {"4", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 3.0 ", {"d", "e", "f", "g", "h"}}, + {"5", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 ", {"a", "b", "c", "d", "e"}}, {"6", "SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 ", {}}, - {"7", "SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' ", {"p", "q", "r"}}, + {"7", "SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET CAST('1'||'5' AS INTEGER) ", {"p", "q", "r"}}, }) -- EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than @@ -2279,10 +2278,10 @@ test:do_select_tests( {"1", "SELECT b FROM f1 ORDER BY a LIMIT 5, 10 ", {"f", "g", "h", "i", "j", "k", "l", "m", "n", "o"}}, {"2", "SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 ", {"k", "l", "m", "n", "o"}}, {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j')", {"c", "d", "e", "f", "g", "h", "i", "j", "k", "l"}}, - {"4", "SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' ", {"d", "e", "f", "g", "h"}}, - {"5", "SELECT b FROM f1 ORDER BY a LIMIT 0, '5' ", {"a", "b", "c", "d", "e"}}, + {"4", "SELECT b FROM f1 ORDER BY a LIMIT 3.0, 5 ", {"d", "e", "f", "g", "h"}}, + {"5", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}}, {"6", "SELECT b FROM f1 ORDER BY a LIMIT 10, 0 ", {}}, - {"7", "SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 ", {"p", "q", "r"}}, + {"7", "SELECT b FROM f1 ORDER BY a LIMIT CAST('1'||'5' AS INTEGER), 3 ", {"p", "q", "r"}}, {"8", "SELECT b FROM f1 ORDER BY a LIMIT 20, 10 ", {"u", "v", "w", "x", "y", "z"}}, {"9", "SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 ", {4, 3, 2, 1}}, {"10", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}}, diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua index e29db9d93..a6d842962 100755 --- a/test/sql-tap/in3.test.lua +++ b/test/sql-tap/in3.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(29) +test:plan(28) --!./tcltestrunner.lua -- 2007 November 29 @@ -322,18 +322,6 @@ test:do_test( -- }) -test:do_test( - "in3-3.3", - function() - -- Logically, numeric affinity is applied to both sides before - -- the comparison, but index can't be used. - return exec_neph(" SELECT x IN (SELECT b FROM t1) FROM t2 ") - end, { - -- - 1, true - -- - }) - test:do_test( "in3-3.4", function() diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua index 8c6917379..5c01ccdab 100755 --- a/test/sql-tap/in4.test.lua +++ b/test/sql-tap/in4.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(61) +test:plan(52) --!./tcltestrunner.lua -- 2008 September 1 @@ -140,7 +140,7 @@ test:do_execsql_test( test:do_execsql_test( "in4-2.7", [[ - SELECT b FROM t2 WHERE a IN ('1', '2') + SELECT b FROM t2 WHERE a IN (1, 2) ]], { -- "one", "two" @@ -585,98 +585,6 @@ test:do_execsql_test( -- }) -test:do_execsql_test( - "in4-4.11", - [[ - CREATE TABLE t4b(a TEXT, b NUMBER, c INT PRIMARY KEY); - INSERT INTO t4b VALUES('1.0',1,4); - SELECT c FROM t4b WHERE a=b; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.12", - [[ - SELECT c FROM t4b WHERE b=a; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.13", - [[ - SELECT c FROM t4b WHERE +a=b; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.14", - [[ - SELECT c FROM t4b WHERE a=+b; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.15", - [[ - SELECT c FROM t4b WHERE +b=a; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.16", - [[ - SELECT c FROM t4b WHERE b=+a; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.17", - [[ - SELECT c FROM t4b WHERE a IN (b); - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.18", - [[ - SELECT c FROM t4b WHERE b IN (a); - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.19", - [[ - SELECT c FROM t4b WHERE +b IN (a); - ]], { - -- - 4 - -- - }) - -- MUST_WORK_TEST -- Tarantool: TBI: Need to support collations. Depends on #2121 -- test:do_execsql_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/limit.test.lua b/test/sql-tap/limit.test.lua index 870233942..a7d1451f7 100755 --- a/test/sql-tap/limit.test.lua +++ b/test/sql-tap/limit.test.lua @@ -441,7 +441,7 @@ test:do_catchsql_test( test:do_execsql_test( "limit-6.5.2", [[ - SELECT * FROM t6 LIMIT '12' + SELECT * FROM t6 LIMIT 12 ]], { -- 1, 2, 3, 4 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..c0136d04c 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) @@ -176,7 +176,7 @@ test:do_test( "misc1-2.1", function() test:execsql([[ - CREATE TABLE agger(one text primary key, two text, three text, four text); + CREATE TABLE agger(one int 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'); @@ -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 6248abb65..0570dc49f 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/subquery.test.lua b/test/sql-tap/subquery.test.lua index 15c4c8276..e0771825e 100755 --- a/test/sql-tap/subquery.test.lua +++ b/test/sql-tap/subquery.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(73) +test:plan(69) --!./tcltestrunner.lua -- 2005 January 19 @@ -335,73 +335,6 @@ test:do_execsql_test( -- }) -test:do_execsql_test( - "subquery-2.5.1", - [[ - CREATE TABLE t3(a INTEGER PRIMARY KEY); - INSERT INTO t3 VALUES(10); - - CREATE TABLE t4(x TEXT PRIMARY KEY); - INSERT INTO t4 VALUES('10'); - ]], { - -- - - -- - }) - -test:do_test( - "subquery-2.5.2", - function() - -- In the expr "x IN (SELECT a FROM t3)" the RHS of the IN operator - -- has text affinity and the LHS has integer affinity. The rule is - -- that we try to convert both sides to an integer before doing the - -- comparision. Hence, the integer value 10 in t3 will compare equal - -- to the string value '10.0' in t4 because the t4 value will be - -- converted into an integer. - return test:execsql [[ - SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); - ]] - end, { - -- - "10" - -- - }) - -test:do_test( - "subquery-2.5.3.1", - function() - -- The t4i index cannot be used to resolve the "x IN (...)" constraint - -- because the constraint has integer affinity but t4i has text affinity. - return test:execsql [[ - CREATE INDEX t4i ON t4(x); - SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); - ]] - end, { - -- - "10" - -- - }) - --- Tarantool: no-rowid is implied for the table, so query plan contains --- scan over t4i. Verified w/ vanilla sql. Comment this case ---do_test subquery-2.5.3.2 { --- Verify that the t4i index was not used in the previous query --- execsql { --- EXPLAIN QUERY PLAN --- SELECT * FROM t4 WHERE x IN (SELECT a FROM t3); --- } ---} {~/t4i/} -test:do_execsql_test( - "subquery-2.5.4", - [[ - DROP TABLE t3; - DROP TABLE t4; - ]], { - -- - - -- - }) - -------------------------------------------------------------------- -- The following test cases - subquery-3.* - test tickets that -- were raised during development of correlated subqueries. 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/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua index cb5348ab4..ca3a5427a 100755 --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua +++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(49) +test:plan(47) --!./tcltestrunner.lua -- 2014 June 26 @@ -193,16 +193,6 @@ test:do_execsql_test( -- }) -test:do_execsql_test( - 3.4, - [[ - SELECT x FROM t2 WHERE x IN ('1'); - ]], { - -- <3.4> - 1 - -- - }) - test:do_execsql_test( 3.5, [[ @@ -233,16 +223,6 @@ test:do_execsql_test( -- }) -test:do_execsql_test( - 3.8, - [[ - SELECT x FROM t2 WHERE '1' IN (x); - ]], { - -- <3.8> - 1 - -- - }) - test:do_execsql_test( 4.1, [[ @@ -263,23 +243,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.3, [[ SELECT x FROM t3 WHERE x IN ('1'); ]], { -- <4.3> - 1.0 + 1, "Type mismatch: can not convert 1 to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.4, [[ SELECT x FROM t3 WHERE x IN ('1.0'); ]], { -- <4.4> - 1.0 + 1, "Type mismatch: can not convert 1.0 to number" -- }) @@ -303,23 +283,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.7, [[ SELECT x FROM t3 WHERE '1' IN (x); ]], { -- <4.7> - 1 + 1, "Type mismatch: can not convert 1 to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( 4.8, [[ SELECT x FROM t3 WHERE '1.0' IN (x); ]], { -- <4.8> - 1 + 1, "Type mismatch: can not convert 1.0 to number" -- }) @@ -343,23 +323,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.3, [[ SELECT x FROM t4 WHERE x IN ('1'); ]], { -- <5.3> - + 1, "Type mismatch: can not convert 1 to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.4, [[ SELECT x FROM t4 WHERE x IN ('1.0'); ]], { -- <5.4> - + 1, "Type mismatch: can not convert 1.0 to number" -- }) @@ -373,13 +353,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.6, [[ SELECT x FROM t4 WHERE x IN ('1.11'); ]], { -- <5.6> - 1.11 + 1, "Type mismatch: can not convert 1.11 to number" -- }) @@ -403,23 +383,23 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.9, [[ SELECT x FROM t4 WHERE '1' IN (x); ]], { -- <5.9> - + 1, "Type mismatch: can not convert 1 to number" -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.10, [[ SELECT x FROM t4 WHERE '1.0' IN (x); ]], { -- <5.10> - + 1, "Type mismatch: can not convert 1.0 to number" -- }) @@ -433,13 +413,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( 5.12, [[ SELECT x FROM t4 WHERE '1.11' IN (x); ]], { -- <5.12> - 1.11 + 1, "Type mismatch: can not convert 1.11 to number" -- }) diff --git a/test/sql-tap/tkt-f973c7ac31.test.lua b/test/sql-tap/tkt-f973c7ac31.test.lua index 82bdb52f8..381f29c65 100755 --- a/test/sql-tap/tkt-f973c7ac31.test.lua +++ b/test/sql-tap/tkt-f973c7ac31.test.lua @@ -39,9 +39,8 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".1", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 DESC ]], { - }) test:do_execsql_test( @@ -55,7 +54,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".3", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC ]], { 5, 5, 5, 4 }) @@ -63,7 +62,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".4", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 DESC ]], { 5, 5, 5, 4 }) @@ -71,7 +70,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".5", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 DESC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 DESC ]], { 5, 5, 5, 4 }) @@ -79,9 +78,8 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".6", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='2' ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='2' ORDER BY c2 ASC ]], { - }) test:do_execsql_test( @@ -95,7 +93,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".8", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND c2<='5' ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>0 AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC ]], { 5, 4, 5, 5 }) @@ -103,7 +101,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".9", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<=5 ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND c2<=5 ORDER BY c2 ASC ]], { 5, 4, 5, 5 }) @@ -111,7 +109,7 @@ for tn, sql in ipairs(sqls) do test:do_execsql_test( "tkt-f973c7ac3-1."..tn..".10", [[ - SELECT c1,c2 FROM t WHERE c1 = 5 AND c2>'0' AND c2<='5' ORDER BY c2 ASC + SELECT c1,c2 FROM t WHERE c1 = 5 AND CAST(c2 AS STRING)>'0' AND CAST(c2 AS STRING)<='5' ORDER BY c2 ASC ]], { 5, 4, 5, 5 }) diff --git a/test/sql-tap/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua deleted file mode 100755 index fe5d6200f..000000000 --- a/test/sql-tap/tkt-fc7bd6358f.test.lua +++ /dev/null @@ -1,111 +0,0 @@ -#!/usr/bin/env tarantool -test = require("sqltester") -test:plan(50) - ---!./tcltestrunner.lua --- 2013 March 05 --- --- The author disclaims copyright to this source code. In place of --- a legal notice, here is a blessing: --- --- May you do good and not evil. --- May you find forgiveness for yourself and forgive others. --- May you share freely, never taking more than you give. --- -------------------------------------------------------------------------- --- This file implements regression tests for sql library. Specifically, --- it tests that ticket [fc7bd6358f]: --- --- The following SQL yields an incorrect result (zero rows) in all --- versions of sql between 3.6.14 and 3.7.15.2: --- --- CREATE TABLE t(textid TEXT); --- INSERT INTO t VALUES('12'); --- INSERT INTO t VALUES('34'); --- CREATE TABLE i(intid INTEGER PRIMARY KEY); --- INSERT INTO i VALUES(12); --- INSERT INTO i VALUES(34); --- --- SELECT t1.textid AS a, i.intid AS b, t2.textid AS c --- FROM t t1, i, t t2 --- WHERE t1.textid = i.intid --- AND t1.textid = t2.textid; --- --- The correct result should be two rows, one with 12|12|12 and the other --- with 34|34|34. With this bug, no rows are returned. Bisecting shows that --- this bug was introduced with check-in [dd4d67a67454] on 2009-04-23. --- --- ["set","testdir",[["file","dirname",["argv0"]]]] --- ["source",[["testdir"],"\/tester.tcl"]] -test:do_test( - "tkt-fc7bd6358f.100", - function() - return test:execsql [[ - CREATE TABLE t(textid TEXT PRIMARY KEY); - INSERT INTO t VALUES('12'); - INSERT INTO t VALUES('34'); - CREATE TABLE i(intid INTEGER PRIMARY KEY); - INSERT INTO i VALUES(12); - INSERT INTO i VALUES(34); - ]] - end, { - -- - - -- - }) - --- ["unset","-nocomplain","from"] --- ["unset","-nocomplain","where"] --- ["unset","-nocomplain","a"] --- ["unset","-nocomplain","b"] -local froms = { - "FROM t t1, i, t t2", - "FROM i, t t1, t t2", - "FROM t t1, t t2, i", -} -local wheres = { - "WHERE t1.textid=i.intid AND t1.textid=t2.textid", - "WHERE i.intid=t1.textid AND t1.textid=t2.textid", - "WHERE t1.textid=i.intid AND i.intid=t2.textid", - "WHERE t1.textid=i.intid AND t2.textid=i.intid", - "WHERE i.intid=t1.textid AND i.intid=t2.textid", - "WHERE i.intid=t1.textid AND t2.textid=i.intid", - "WHERE t1.textid=t2.textid AND i.intid=t2.textid", - "WHERE t1.textid=t2.textid AND t2.textid=i.intid", -} -for a, from in ipairs(froms) do - for b, where in ipairs(wheres) do - test:do_test( - string.format("tkt-fc7bd6358f.110.%s.%s.1", a, b), - function() - return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) - end, { - "12", 12, "12", "34", 34, "34" - }) - - test:do_test( - string.format("tkt-fc7bd6358f.110.%s.%s.2", a, b), - function() - return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) - end, { - "12", 12, "12", "34", 34, "34" - }) - - end -end - -test:do_test( - "tkt-fc7bd6358f.200", - function() - return test:execsql [[ - DROP TABLE t; - DROP TABLE i; - ]] - end, { - -- - - -- - }) - -test:finish_test() - 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/transitive1.test.lua b/test/sql-tap/transitive1.test.lua index e96056580..96895b4a7 100755 --- a/test/sql-tap/transitive1.test.lua +++ b/test/sql-tap/transitive1.test.lua @@ -338,7 +338,7 @@ test:do_execsql_test( ON tvshow.idshow = episode.idshow LEFT JOIN seasons ON seasons.idshow = episode.idshow - AND seasons.season = episode.c12 + AND seasons.season = CAST(episode.c12 AS INTEGER) JOIN path ON files.idpath = path.idpath LEFT JOIN bookmark @@ -378,7 +378,7 @@ test:do_execsql_test( FROM episodeview JOIN tvshowview ON tvshowview.idShow = episodeview.idShow JOIN seasons ON (seasons.idShow = tvshowview.idShow - AND seasons.season = episodeview.c12) + AND seasons.season = CAST(episodeview.c12 AS INTEGER)) JOIN files ON files.idFile = episodeview.idFile JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow JOIN path ON path.idPath = tvshowlinkpath.idPath 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 deleted file mode 100755 index d98645fdc..000000000 --- a/test/sql-tap/whereB.test.lua +++ /dev/null @@ -1,908 +0,0 @@ -#!/usr/bin/env tarantool -test = require("sqltester") -test:plan(63) - ---!./tcltestrunner.lua --- 2009 August 13 --- --- The author disclaims copyright to this source code. In place of --- a legal notice, here is a blessing: --- --- May you do good and not evil. --- May you find forgiveness for yourself and forgive others. --- May you share freely, never taking more than you give. --- -------------------------------------------------------------------------- --- This file implements regression tests for sql library. The --- focus of this file is testing WHERE clause conditions with --- subtle affinity issues. --- --- ["set","testdir",[["file","dirname",["argv0"]]]] --- ["source",[["testdir"],"\/tester.tcl"]] --- For this set of tests: --- --- * t1.y holds an integer value with affinity NONE --- * t2.b holds a text value with affinity TEXT --- --- These values are not equal and because neither affinity is NUMERIC --- no type conversion occurs. --- -test:do_execsql_test( - "whereB-1.1", - [[ - CREATE TABLE t1(x INT primary key,y INT ); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,99); - - CREATE TABLE t2(a INT primary key, b TEXT); -- affinity of t2.b is TEXT - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.4", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.102", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - --- 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 t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2, 99); - - SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a; - ]], - { - -- - 1, 2, false - -- - }) - -test:do_execsql_test( - "whereB-2.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.4", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.102", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - - -- - }) - --- For this set of tests: --- --- * t1.y holds a text value with affinity NONE --- * 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-3.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,99); - - CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- - 1, 2, false - -- - }) - -test:do_execsql_test( - "whereB-3.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.4", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.102", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - - -- - }) - --- For this set of tests: --- --- * t1.y holds a text value with affinity NONE --- * t2.b holds an integer value with affinity NUMERIC --- --- Because t2.b has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-4.1", - [[ - DROP TABLE IF EXISTS t1; - DROP TABLE IF EXISTS t2; - - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,'99'); - - CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is NUMERIC - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,99); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - --- For this set of tests: --- --- * t1.y holds a text value with affinity NONE --- * t2.b holds an integer value with affinity INTEGER --- --- Because t2.b has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-5.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,'99'); - - CREATE TABLE t2(a INT primary key, b INT); -- affinity of t2.b is INTEGER - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,99); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - --- For this set of tests: --- --- * t1.y holds a text value with affinity NONE --- * t2.b holds an integer value with affinity REAL --- --- Because t2.b has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-6.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,'99'); - - CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is REAL - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,99.0); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - --- For this set of tests: --- --- * t1.y holds an integer value with affinity NUMERIC --- * t2.b holds a text value with affinity NONE --- --- Because t1.y has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-7.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y NUMBER); -- affinity of t1.y is NUMERIC - INSERT INTO t1 VALUES(1,99); - - CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - --- For this set of tests: --- --- * t1.y holds an integer value with affinity INTEGER --- * t2.b holds a text value with affinity NONE --- --- Because t1.y has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-8.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y INT); -- affinity of t1.y is INTEGER - INSERT INTO t1 VALUES(1,99); - - CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - --- For this set of tests: --- --- * t1.y holds an integer value with affinity REAL --- * t2.b holds a text value with affinity NONE --- --- Because t1.y has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-9.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y NUMBER); -- affinity of t1.y is REAL - INSERT INTO t1 VALUES(1,99.0); - - CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -test:finish_test() - diff --git a/test/sql-tap/whereC.test.lua b/test/sql-tap/whereC.test.lua index 89459dee3..58c049553 100755 --- a/test/sql-tap/whereC.test.lua +++ b/test/sql-tap/whereC.test.lua @@ -55,9 +55,9 @@ test:do_execsql_test( test:test("main", function() local data = {{"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3", {4, 5}}, -- {"SELECT i FROM t1 WHERE rowid='12'", {12}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2'", {3, 4, 5}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i>'3'", {4, 5}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<5", {3, 4}}, + {"SELECT i FROM t1 WHERE a=1 AND b=2", {3, 4, 5}}, + {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3", {4, 5}}, + {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i<5", {3, 4}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<12", {10, 11}}, {"SELECT i FROM t1 WHERE a IN(1, 2) AND b=2 AND i<11", {3, 4, 5, 10}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12", {10, 11, 12}}, @@ -66,7 +66,7 @@ test:test("main", function() {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10", {}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i=NULL", {}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5", {3, 4}}} + {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i<4.5", {3, 4}}} -- {"SELECT i FROM t1 WHERE rowid IS '12'", {12}}} for tn, t in ipairs(data) do diff --git a/test/sql/types.result b/test/sql/types.result index 54aff460e..70fbbc5a2 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -2155,3 +2155,623 @@ box.execute([[SELECT * FROM "s" WHERE "id" = ?;]]) s:drop() --- ... +-- +-- gh-3809: Make sure there are no implicit casts during +-- assignment, except for the implicit cast between numeric +-- values. +-- +-- Check INSERT. +box.execute([[CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER);]]) +--- +- row_count: 1 +... +box.execute([[CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);]]) +--- +- row_count: 1 +... +box.execute([[CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);]]) +--- +- row_count: 1 +... +box.execute([[CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT);]]) +--- +- row_count: 1 +... +box.execute([[CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);]]) +--- +- row_count: 1 +... +box.execute([[CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR);]]) +--- +- row_count: 1 +... +box.execute([[INSERT INTO ti(i) VALUES (NULL);]]) +--- +- autoincrement_ids: + - 1 + row_count: 1 +... +box.execute([[INSERT INTO ti(i) VALUES (11);]]) +--- +- autoincrement_ids: + - 2 + row_count: 1 +... +box.execute([[INSERT INTO ti(i) VALUES (100000000000000000000000000000000.1);]]) +--- +- null +- 'Type mismatch: can not convert 1.0e+32 to integer' +... +box.execute([[INSERT INTO ti(i) VALUES (33.0);]]) +--- +- autoincrement_ids: + - 3 + row_count: 1 +... +box.execute([[INSERT INTO ti(i) VALUES (true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to integer' +... +box.execute([[INSERT INTO ti(i) VALUES ('33');]]) +--- +- null +- 'Type mismatch: can not convert 33 to integer' +... +box.execute([[INSERT INTO ti(i) VALUES (X'3434');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to integer' +... +box.execute([[SELECT * FROM ti;]]) +--- +- metadata: + - name: A + type: integer + - name: I + type: integer + rows: + - [1, null] + - [2, 11] + - [3, 33] +... +box.execute([[INSERT INTO td(d) VALUES (NULL);]]) +--- +- autoincrement_ids: + - 1 + row_count: 1 +... +box.execute([[INSERT INTO td(d) VALUES (11);]]) +--- +- autoincrement_ids: + - 2 + row_count: 1 +... +box.execute([[INSERT INTO td(d) VALUES (100000000000000001);;]]) +--- +- null +- Syntax error at line 1 near ';' +... +box.execute([[INSERT INTO td(d) VALUES (22.2);]]) +--- +- autoincrement_ids: + - 3 + row_count: 1 +... +box.execute([[INSERT INTO td(d) VALUES (true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to double' +... +box.execute([[INSERT INTO td(d) VALUES ('33');]]) +--- +- null +- 'Type mismatch: can not convert 33 to double' +... +box.execute([[INSERT INTO td(d) VALUES (X'3434');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to double' +... +box.execute([[SELECT * FROM td;]]) +--- +- metadata: + - name: A + type: integer + - name: D + type: double + rows: + - [1, null] + - [2, 11] + - [3, 22.2] +... +box.execute([[INSERT INTO tb(b) VALUES (NULL);]]) +--- +- autoincrement_ids: + - 1 + row_count: 1 +... +box.execute([[INSERT INTO tb(b) VALUES (11);]]) +--- +- null +- 'Type mismatch: can not convert 11 to boolean' +... +box.execute([[INSERT INTO tb(b) VALUES (22.2);]]) +--- +- null +- 'Type mismatch: can not convert 22.2 to boolean' +... +box.execute([[INSERT INTO tb(b) VALUES (true);]]) +--- +- autoincrement_ids: + - 2 + row_count: 1 +... +box.execute([[INSERT INTO tb(b) VALUES ('33');]]) +--- +- null +- 'Type mismatch: can not convert 33 to boolean' +... +box.execute([[INSERT INTO tb(b) VALUES (X'3434');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to boolean' +... +box.execute([[SELECT * FROM tb;]]) +--- +- metadata: + - name: A + type: integer + - name: B + type: boolean + rows: + - [1, null] + - [2, true] +... +box.execute([[INSERT INTO tt(t) VALUES (NULL);]]) +--- +- autoincrement_ids: + - 1 + row_count: 1 +... +box.execute([[INSERT INTO tt(t) VALUES (11);]]) +--- +- null +- 'Type mismatch: can not convert 11 to string' +... +box.execute([[INSERT INTO tt(t) VALUES (22.2);]]) +--- +- null +- 'Type mismatch: can not convert 22.2 to string' +... +box.execute([[INSERT INTO tt(t) VALUES (true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string' +... +box.execute([[INSERT INTO tt(t) VALUES ('33');]]) +--- +- autoincrement_ids: + - 2 + row_count: 1 +... +box.execute([[INSERT INTO tt(t) VALUES (X'3434');]]) +--- +- null +- 'Type mismatch: can not convert varbinary to string' +... +box.execute([[SELECT * FROM tt;]]) +--- +- metadata: + - name: A + type: integer + - name: T + type: string + rows: + - [1, null] + - [2, '33'] +... +box.execute([[INSERT INTO tv(v) VALUES (NULL);]]) +--- +- autoincrement_ids: + - 1 + row_count: 1 +... +box.execute([[INSERT INTO tv(v) VALUES (11);]]) +--- +- null +- 'Type mismatch: can not convert 11 to varbinary' +... +box.execute([[INSERT INTO tv(v) VALUES (22.2);]]) +--- +- null +- 'Type mismatch: can not convert 22.2 to varbinary' +... +box.execute([[INSERT INTO tv(v) VALUES (true);]]) +--- +- null +- 'Type mismatch: can not convert TRUE to varbinary' +... +box.execute([[INSERT INTO tv(v) VALUES ('33');]]) +--- +- null +- 'Type mismatch: can not convert 33 to varbinary' +... +box.execute([[INSERT INTO tv(v) VALUES (X'3434');]]) +--- +- autoincrement_ids: + - 2 + row_count: 1 +... +box.execute([[SELECT * FROM tv;]]) +--- +- metadata: + - name: A + type: integer + - name: V + type: varbinary + rows: + - [1, null] + - [2, '44'] +... +box.execute([[INSERT INTO ts(s) VALUES (NULL);]]) +--- +- autoincrement_ids: + - 1 + row_count: 1 +... +box.execute([[INSERT INTO ts(s) VALUES (11);]]) +--- +- autoincrement_ids: + - 2 + row_count: 1 +... +box.execute([[INSERT INTO ts(s) VALUES (22.2);]]) +--- +- autoincrement_ids: + - 3 + row_count: 1 +... +box.execute([[INSERT INTO ts(s) VALUES (true);]]) +--- +- autoincrement_ids: + - 4 + row_count: 1 +... +box.execute([[INSERT INTO ts(s) VALUES ('33');]]) +--- +- autoincrement_ids: + - 5 + row_count: 1 +... +box.execute([[INSERT INTO ts(s) VALUES (X'3434');]]) +--- +- autoincrement_ids: + - 6 + row_count: 1 +... +box.execute([[SELECT * FROM ts;]]) +--- +- metadata: + - name: A + type: integer + - name: S + type: scalar + rows: + - [1, null] + - [2, 11] + - [3, 22.2] + - [4, true] + - [5, '33'] + - [6, '44'] +... +-- Check for UPDATE. +box.execute([[DELETE FROM ti;]]) +--- +- row_count: 3 +... +box.execute([[DELETE FROM td;]]) +--- +- row_count: 3 +... +box.execute([[DELETE FROM tb;]]) +--- +- row_count: 2 +... +box.execute([[DELETE FROM tt;]]) +--- +- row_count: 2 +... +box.execute([[DELETE FROM tv;]]) +--- +- row_count: 2 +... +box.execute([[DELETE FROM ts;]]) +--- +- row_count: 6 +... +box.execute([[INSERT INTO ti VALUES(1, NULL);]]) +--- +- row_count: 1 +... +box.execute([[INSERT INTO td VALUES(1, NULL);]]) +--- +- row_count: 1 +... +box.execute([[INSERT INTO tb VALUES(1, NULL);]]) +--- +- row_count: 1 +... +box.execute([[INSERT INTO tt VALUES(1, NULL);]]) +--- +- row_count: 1 +... +box.execute([[INSERT INTO tv VALUES(1, NULL);]]) +--- +- row_count: 1 +... +box.execute([[INSERT INTO ts VALUES(1, NULL);]]) +--- +- row_count: 1 +... +box.execute([[SELECT * FROM ti, td, tb, tt, tv, ts;]]) +--- +- metadata: + - name: A + type: integer + - name: I + type: integer + - name: A + type: integer + - name: D + type: double + - name: A + type: integer + - name: B + type: boolean + - name: A + type: integer + - name: T + type: string + - name: A + type: integer + - name: V + type: varbinary + - name: A + type: integer + - name: S + type: scalar + rows: + - [1, null, 1, null, 1, null, 1, null, 1, null, 1, null] +... +box.execute([[UPDATE ti SET i = NULL WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE ti SET i = 11 WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE ti SET i = 100000000000000000000000000000000.1 WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 1.0e+32 to integer' +... +box.execute([[UPDATE ti SET i = 33.0 WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE ti SET i = true WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to integer' +... +box.execute([[UPDATE ti SET i = '33' WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 33 to integer' +... +box.execute([[UPDATE ti SET i = X'3434' WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert varbinary to integer' +... +box.execute([[SELECT * FROM ti;]]) +--- +- metadata: + - name: A + type: integer + - name: I + type: integer + rows: + - [1, 33] +... +box.execute([[UPDATE td SET d = NULL WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE td SET d = 11 WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE td SET d = 100000000000000001 WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE td SET d = 22.2 WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE td SET d = true WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to double' +... +box.execute([[UPDATE td SET d = '33' WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 33 to double' +... +box.execute([[UPDATE td SET d = X'3434' WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert varbinary to double' +... +box.execute([[SELECT * FROM td;]]) +--- +- metadata: + - name: A + type: integer + - name: D + type: double + rows: + - [1, 22.2] +... +box.execute([[UPDATE tb SET b = NULL WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE tb SET b = 11 WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 11 to boolean' +... +box.execute([[UPDATE tb SET b = 22.2 WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 22.2 to boolean' +... +box.execute([[UPDATE tb SET b = true WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE tb SET b = '33' WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 33 to boolean' +... +box.execute([[UPDATE tb SET b = X'3434' WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert varbinary to boolean' +... +box.execute([[SELECT * FROM tb;]]) +--- +- metadata: + - name: A + type: integer + - name: B + type: boolean + rows: + - [1, true] +... +box.execute([[UPDATE tt SET t = NULL WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE tt SET t = 11 WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 11 to string' +... +box.execute([[UPDATE tt SET t = 22.2 WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 22.2 to string' +... +box.execute([[UPDATE tt SET t = true WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to string' +... +box.execute([[UPDATE tt SET t = '33' WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE tt SET t = X'3434' WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert varbinary to string' +... +box.execute([[SELECT * FROM tt;]]) +--- +- metadata: + - name: A + type: integer + - name: T + type: string + rows: + - [1, '33'] +... +box.execute([[UPDATE tv SET v = NULL WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE tv SET v = 11 WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 11 to varbinary' +... +box.execute([[UPDATE tv SET v = 22.2 WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 22.2 to varbinary' +... +box.execute([[UPDATE tv SET v = true WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert TRUE to varbinary' +... +box.execute([[UPDATE tv SET v = '33' WHERE a = 1;]]) +--- +- null +- 'Type mismatch: can not convert 33 to varbinary' +... +box.execute([[UPDATE tv SET v = X'3434' WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[SELECT * FROM tv;]]) +--- +- metadata: + - name: A + type: integer + - name: V + type: varbinary + rows: + - [1, '44'] +... +box.execute([[UPDATE ts SET s = NULL WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE ts SET s = 11 WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE ts SET s = 22.2 WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE ts SET s = true WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE ts SET s = '33' WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[UPDATE ts SET s = X'3434' WHERE a = 1;]]) +--- +- row_count: 1 +... +box.execute([[SELECT * FROM ts;]]) +--- +- metadata: + - name: A + type: integer + - name: S + type: scalar + rows: + - [1, '44'] +... diff --git a/test/sql/types.test.lua b/test/sql/types.test.lua index bd14b342d..2dc70f3c5 100644 --- a/test/sql/types.test.lua +++ b/test/sql/types.test.lua @@ -487,3 +487,132 @@ s:format({ \ box.execute([[SELECT * FROM "s" WHERE "id" = ?;]]) s:drop() + +-- +-- gh-3809: Make sure there are no implicit casts during +-- assignment, except for the implicit cast between numeric +-- values. +-- + +-- Check INSERT. +box.execute([[CREATE TABLE ti (a INT PRIMARY KEY AUTOINCREMENT, i INTEGER);]]) +box.execute([[CREATE TABLE td (a INT PRIMARY KEY AUTOINCREMENT, d DOUBLE);]]) +box.execute([[CREATE TABLE tb (a INT PRIMARY KEY AUTOINCREMENT, b BOOLEAN);]]) +box.execute([[CREATE TABLE tt (a INT PRIMARY KEY AUTOINCREMENT, t TEXT);]]) +box.execute([[CREATE TABLE tv (a INT PRIMARY KEY AUTOINCREMENT, v VARBINARY);]]) +box.execute([[CREATE TABLE ts (a INT PRIMARY KEY AUTOINCREMENT, s SCALAR);]]) + +box.execute([[INSERT INTO ti(i) VALUES (NULL);]]) +box.execute([[INSERT INTO ti(i) VALUES (11);]]) +box.execute([[INSERT INTO ti(i) VALUES (100000000000000000000000000000000.1);]]) +box.execute([[INSERT INTO ti(i) VALUES (33.0);]]) +box.execute([[INSERT INTO ti(i) VALUES (true);]]) +box.execute([[INSERT INTO ti(i) VALUES ('33');]]) +box.execute([[INSERT INTO ti(i) VALUES (X'3434');]]) +box.execute([[SELECT * FROM ti;]]) + +box.execute([[INSERT INTO td(d) VALUES (NULL);]]) +box.execute([[INSERT INTO td(d) VALUES (11);]]) +box.execute([[INSERT INTO td(d) VALUES (100000000000000001);;]]) +box.execute([[INSERT INTO td(d) VALUES (22.2);]]) +box.execute([[INSERT INTO td(d) VALUES (true);]]) +box.execute([[INSERT INTO td(d) VALUES ('33');]]) +box.execute([[INSERT INTO td(d) VALUES (X'3434');]]) +box.execute([[SELECT * FROM td;]]) + +box.execute([[INSERT INTO tb(b) VALUES (NULL);]]) +box.execute([[INSERT INTO tb(b) VALUES (11);]]) +box.execute([[INSERT INTO tb(b) VALUES (22.2);]]) +box.execute([[INSERT INTO tb(b) VALUES (true);]]) +box.execute([[INSERT INTO tb(b) VALUES ('33');]]) +box.execute([[INSERT INTO tb(b) VALUES (X'3434');]]) +box.execute([[SELECT * FROM tb;]]) + +box.execute([[INSERT INTO tt(t) VALUES (NULL);]]) +box.execute([[INSERT INTO tt(t) VALUES (11);]]) +box.execute([[INSERT INTO tt(t) VALUES (22.2);]]) +box.execute([[INSERT INTO tt(t) VALUES (true);]]) +box.execute([[INSERT INTO tt(t) VALUES ('33');]]) +box.execute([[INSERT INTO tt(t) VALUES (X'3434');]]) +box.execute([[SELECT * FROM tt;]]) + +box.execute([[INSERT INTO tv(v) VALUES (NULL);]]) +box.execute([[INSERT INTO tv(v) VALUES (11);]]) +box.execute([[INSERT INTO tv(v) VALUES (22.2);]]) +box.execute([[INSERT INTO tv(v) VALUES (true);]]) +box.execute([[INSERT INTO tv(v) VALUES ('33');]]) +box.execute([[INSERT INTO tv(v) VALUES (X'3434');]]) +box.execute([[SELECT * FROM tv;]]) + +box.execute([[INSERT INTO ts(s) VALUES (NULL);]]) +box.execute([[INSERT INTO ts(s) VALUES (11);]]) +box.execute([[INSERT INTO ts(s) VALUES (22.2);]]) +box.execute([[INSERT INTO ts(s) VALUES (true);]]) +box.execute([[INSERT INTO ts(s) VALUES ('33');]]) +box.execute([[INSERT INTO ts(s) VALUES (X'3434');]]) +box.execute([[SELECT * FROM ts;]]) + +-- Check for UPDATE. +box.execute([[DELETE FROM ti;]]) +box.execute([[DELETE FROM td;]]) +box.execute([[DELETE FROM tb;]]) +box.execute([[DELETE FROM tt;]]) +box.execute([[DELETE FROM tv;]]) +box.execute([[DELETE FROM ts;]]) +box.execute([[INSERT INTO ti VALUES(1, NULL);]]) +box.execute([[INSERT INTO td VALUES(1, NULL);]]) +box.execute([[INSERT INTO tb VALUES(1, NULL);]]) +box.execute([[INSERT INTO tt VALUES(1, NULL);]]) +box.execute([[INSERT INTO tv VALUES(1, NULL);]]) +box.execute([[INSERT INTO ts VALUES(1, NULL);]]) +box.execute([[SELECT * FROM ti, td, tb, tt, tv, ts;]]) + +box.execute([[UPDATE ti SET i = NULL WHERE a = 1;]]) +box.execute([[UPDATE ti SET i = 11 WHERE a = 1;]]) +box.execute([[UPDATE ti SET i = 100000000000000000000000000000000.1 WHERE a = 1;]]) +box.execute([[UPDATE ti SET i = 33.0 WHERE a = 1;]]) +box.execute([[UPDATE ti SET i = true WHERE a = 1;]]) +box.execute([[UPDATE ti SET i = '33' WHERE a = 1;]]) +box.execute([[UPDATE ti SET i = X'3434' WHERE a = 1;]]) +box.execute([[SELECT * FROM ti;]]) + +box.execute([[UPDATE td SET d = NULL WHERE a = 1;]]) +box.execute([[UPDATE td SET d = 11 WHERE a = 1;]]) +box.execute([[UPDATE td SET d = 100000000000000001 WHERE a = 1;]]) +box.execute([[UPDATE td SET d = 22.2 WHERE a = 1;]]) +box.execute([[UPDATE td SET d = true WHERE a = 1;]]) +box.execute([[UPDATE td SET d = '33' WHERE a = 1;]]) +box.execute([[UPDATE td SET d = X'3434' WHERE a = 1;]]) +box.execute([[SELECT * FROM td;]]) + +box.execute([[UPDATE tb SET b = NULL WHERE a = 1;]]) +box.execute([[UPDATE tb SET b = 11 WHERE a = 1;]]) +box.execute([[UPDATE tb SET b = 22.2 WHERE a = 1;]]) +box.execute([[UPDATE tb SET b = true WHERE a = 1;]]) +box.execute([[UPDATE tb SET b = '33' WHERE a = 1;]]) +box.execute([[UPDATE tb SET b = X'3434' WHERE a = 1;]]) +box.execute([[SELECT * FROM tb;]]) + +box.execute([[UPDATE tt SET t = NULL WHERE a = 1;]]) +box.execute([[UPDATE tt SET t = 11 WHERE a = 1;]]) +box.execute([[UPDATE tt SET t = 22.2 WHERE a = 1;]]) +box.execute([[UPDATE tt SET t = true WHERE a = 1;]]) +box.execute([[UPDATE tt SET t = '33' WHERE a = 1;]]) +box.execute([[UPDATE tt SET t = X'3434' WHERE a = 1;]]) +box.execute([[SELECT * FROM tt;]]) + +box.execute([[UPDATE tv SET v = NULL WHERE a = 1;]]) +box.execute([[UPDATE tv SET v = 11 WHERE a = 1;]]) +box.execute([[UPDATE tv SET v = 22.2 WHERE a = 1;]]) +box.execute([[UPDATE tv SET v = true WHERE a = 1;]]) +box.execute([[UPDATE tv SET v = '33' WHERE a = 1;]]) +box.execute([[UPDATE tv SET v = X'3434' WHERE a = 1;]]) +box.execute([[SELECT * FROM tv;]]) + +box.execute([[UPDATE ts SET s = NULL WHERE a = 1;]]) +box.execute([[UPDATE ts SET s = 11 WHERE a = 1;]]) +box.execute([[UPDATE ts SET s = 22.2 WHERE a = 1;]]) +box.execute([[UPDATE ts SET s = true WHERE a = 1;]]) +box.execute([[UPDATE ts SET s = '33' WHERE a = 1;]]) +box.execute([[UPDATE ts SET s = X'3434' WHERE a = 1;]]) +box.execute([[SELECT * FROM ts;]]) -- 2.25.1