From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (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 0553D430410 for ; Fri, 21 Aug 2020 12:19:55 +0300 (MSK) From: imeevma@tarantool.org Date: Fri, 21 Aug 2020 12:19:54 +0300 Message-Id: In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v5 4/6] sql: remove implicit cast from comparison opcodes List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: korablev@tarantool.org, tsafin@tarantool.org Cc: tarantool-patches@dev.tarantool.org This patch removes implicit casting from STRING to number and vice versa from comparison opcodes. Part of #4230 --- src/box/sql/vdbe.c | 52 ++- test/sql-tap/identifier_case.test.lua | 6 +- test/sql-tap/in1.test.lua | 4 +- test/sql-tap/insert3.test.lua | 2 +- test/sql-tap/join.test.lua | 4 +- test/sql-tap/misc1.test.lua | 32 +- test/sql-tap/select1.test.lua | 4 +- test/sql-tap/select7.test.lua | 2 +- test/sql-tap/subquery.test.lua | 4 +- test/sql-tap/tkt-9a8b09f8e6.test.lua | 508 -------------------------- test/sql-tap/tkt3493.test.lua | 40 +- test/sql-tap/transitive1.test.lua | 12 +- test/sql-tap/where2.test.lua | 183 +--------- test/sql-tap/where5.test.lua | 12 +- test/sql/types.result | 7 +- 15 files changed, 86 insertions(+), 786 deletions(-) delete mode 100755 test/sql-tap/tkt-9a8b09f8e6.test.lua diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index a377ceae7..b326c4ba4 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2577,22 +2577,17 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */ } else { enum field_type type = pOp->p5 & FIELD_TYPE_MASK; if (sql_type_is_numeric(type)) { - if ((flags1 | flags3)&MEM_Str) { - if ((flags1 & MEM_Str) == MEM_Str) { - mem_apply_numeric_type(pIn1); - testcase( flags3!=pIn3->flags); /* Possible if pIn1==pIn3 */ - flags3 = pIn3->flags; - } - if ((flags3 & MEM_Str) == MEM_Str) { - if (mem_apply_numeric_type(pIn3) != 0) { - diag_set(ClientError, - ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(pIn3), - "numeric"); - goto abort_due_to_error; - } - - } + if ((flags1 & MEM_Str) == MEM_Str) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(pIn1), + "numeric"); + goto abort_due_to_error; + } + if ((flags3 & MEM_Str) == MEM_Str) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(pIn3), + "numeric"); + goto abort_due_to_error; } /* Handle the common case of integer comparison here, as an * optimization, to avoid a call to sqlMemCompare() @@ -2625,22 +2620,17 @@ case OP_Ge: { /* same as TK_GE, jump, in1, in3 */ goto compare_op; } } else if (type == FIELD_TYPE_STRING) { - if ((flags1 & MEM_Str) == 0 && - (flags1 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) { - testcase( pIn1->flags & MEM_Int); - testcase( pIn1->flags & MEM_Real); - sqlVdbeMemStringify(pIn1); - testcase( (flags1&MEM_Dyn) != (pIn1->flags&MEM_Dyn)); - flags1 = (pIn1->flags & ~MEM_TypeMask) | (flags1 & MEM_TypeMask); - assert(pIn1!=pIn3); + if ((flags1 & MEM_Str) == 0) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + mem_type_to_str(pIn3), + mem_type_to_str(pIn1)); + goto abort_due_to_error; } - if ((flags3 & MEM_Str) == 0 && - (flags3 & (MEM_Int | MEM_UInt | MEM_Real)) != 0) { - testcase( pIn3->flags & MEM_Int); - testcase( pIn3->flags & MEM_Real); - sqlVdbeMemStringify(pIn3); - testcase( (flags3&MEM_Dyn) != (pIn3->flags&MEM_Dyn)); - flags3 = (pIn3->flags & ~MEM_TypeMask) | (flags3 & MEM_TypeMask); + if ((flags3 & MEM_Str) == 0) { + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + mem_type_to_str(pIn1), + mem_type_to_str(pIn3)); + goto abort_due_to_error; } } assert(pOp->p4type==P4_COLLSEQ || pOp->p4.pColl==0); diff --git a/test/sql-tap/identifier_case.test.lua b/test/sql-tap/identifier_case.test.lua index 2a00626fc..1d56ffb44 100755 --- a/test/sql-tap/identifier_case.test.lua +++ b/test/sql-tap/identifier_case.test.lua @@ -242,11 +242,11 @@ data = { { 2, [[ 'a' < 'b' collate "binary" ]], {0, {true}}}, { 3, [[ 'a' < 'b' collate 'binary' ]], {1, [[Syntax error at line 1 near ''binary'']]}}, { 4, [[ 'a' < 'b' collate "unicode" ]], {0, {true}}}, - { 5, [[ 5 < 'b' collate "unicode" ]], {0, {true}}}, + { 5, [[ 5 < 'b' collate "unicode" ]], {1, "Type mismatch: can not convert b to numeric"}}, { 6, [[ 5 < 'b' collate unicode ]], {1,"Collation 'UNICODE' does not exist"}}, - { 7, [[ 5 < 'b' collate "unicode_ci" ]], {0, {true}}}, + { 7, [[ 5 < 'b' collate "unicode_ci" ]], {1, "Type mismatch: can not convert b to numeric"}}, { 8, [[ 5 < 'b' collate NONE ]], {1, "Collation 'NONE' does not exist"}}, - { 9, [[ 5 < 'b' collate "none" ]], {0, {true}}}, + { 9, [[ 5 < 'b' collate "none" ]], {1, "Type mismatch: can not convert b to numeric"}}, } for _, row in ipairs(data) do diff --git a/test/sql-tap/in1.test.lua b/test/sql-tap/in1.test.lua index 570cc1779..e2f498889 100755 --- a/test/sql-tap/in1.test.lua +++ b/test/sql-tap/in1.test.lua @@ -637,12 +637,12 @@ test:do_test( "in-11.2", function() -- The '2' should be coerced into 2 because t6.b is NUMERIC - return test:execsql [[ + return test:catchsql [[ SELECT * FROM t6 WHERE b IN ('2'); ]] end, { -- - 1, 2 + 1, "Type mismatch: can not convert 2 to numeric" -- }) diff --git a/test/sql-tap/insert3.test.lua b/test/sql-tap/insert3.test.lua index b92bc508e..3276f0db2 100755 --- a/test/sql-tap/insert3.test.lua +++ b/test/sql-tap/insert3.test.lua @@ -59,7 +59,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; + UPDATE log2 SET y=y+1 WHERE x=CAST(new.b AS STRING); INSERT OR IGNORE INTO log2(x, y) VALUES(CAST(new.b AS STRING),1); END; INSERT INTO t1(a, b) VALUES('hi', 453); diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua index 7a1346094..a78913836 100755 --- a/test/sql-tap/join.test.lua +++ b/test/sql-tap/join.test.lua @@ -1038,13 +1038,13 @@ test:do_catchsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "join-11.10", [[ SELECT * FROM t2 NATURAL JOIN t1 ]], { -- - 1, "one", 2, "two" + 1, "Type mismatch: can not convert 1 to numeric" -- }) diff --git a/test/sql-tap/misc1.test.lua b/test/sql-tap/misc1.test.lua index c0136d04c..66666878e 100755 --- a/test/sql-tap/misc1.test.lua +++ b/test/sql-tap/misc1.test.lua @@ -88,7 +88,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-1.4", [[ - SELECT x75 FROM manycol WHERE x50=350 + SELECT x75 FROM manycol WHERE x50='350' ]], { -- "375" @@ -98,7 +98,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-1.5", [[ - SELECT x50 FROM manycol WHERE x99=599 + SELECT x50 FROM manycol WHERE x99='599' ]], { -- "550" @@ -109,7 +109,7 @@ test:do_test( "misc1-1.6", function() test:execsql("CREATE INDEX manycol_idx1 ON manycol(x99)") - return test:execsql("SELECT x50 FROM manycol WHERE x99=899") + return test:execsql("SELECT x50 FROM manycol WHERE x99='899'") end, { -- "850" @@ -129,7 +129,7 @@ test:do_execsql_test( test:do_test( "misc1-1.8", function() - test:execsql("DELETE FROM manycol WHERE x98=1234") + test:execsql("DELETE FROM manycol WHERE x98='1234'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -140,7 +140,7 @@ test:do_test( test:do_test( "misc1-1.9", function() - test:execsql("DELETE FROM manycol WHERE x98=998") + test:execsql("DELETE FROM manycol WHERE x98='998'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -151,7 +151,7 @@ test:do_test( test:do_test( "misc1-1.10", function() - test:execsql("DELETE FROM manycol WHERE x99=500") + test:execsql("DELETE FROM manycol WHERE x99='500'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -162,7 +162,7 @@ test:do_test( test:do_test( "misc1-1.11", function() - test:execsql("DELETE FROM manycol WHERE x99=599") + test:execsql("DELETE FROM manycol WHERE x99='599'") return test:execsql("SELECT count(*) FROM manycol") end, { -- @@ -479,9 +479,9 @@ local where = "" test:do_test( "misc1-10.1", function() - where = "WHERE x0>=0" + where = "WHERE x0>='0'" for i = 1, 99, 1 do - where = where .. " AND x"..i.."<>0" + where = where .. " AND x"..i.."<>'0'" end return test:catchsql("SELECT count(*) FROM manycol "..where.."") end, { @@ -496,7 +496,7 @@ test:do_test( test:do_test( "misc1-10.3", function() - where = string.gsub(where,"x0>=0", "x0=0") + where = string.gsub(where,"x0>='0'", "x0='0'") return test:catchsql("DELETE FROM manycol "..where.."") end, { -- @@ -520,7 +520,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-10.6", [[ - SELECT x1 FROM manycol WHERE x0=100 + SELECT x1 FROM manycol WHERE x0='100' ]], { -- "101" @@ -530,7 +530,7 @@ test:do_execsql_test( test:do_test( "misc1-10.7", function() - where = string.gsub(where, "x0=0", "x0=100") + where = string.gsub(where, "x0='0'", "x0='100'") return test:catchsql("UPDATE manycol SET x1=CAST(x1+1 AS STRING) "..where.."") end, { -- @@ -541,7 +541,7 @@ test:do_test( test:do_execsql_test( "misc1-10.8", [[ - SELECT x1 FROM manycol WHERE x0=100 + SELECT x1 FROM manycol WHERE x0='100' ]], { -- "102" @@ -563,7 +563,7 @@ test:do_execsql_test( test:do_execsql_test( "misc1-10.10", [[ - SELECT x1 FROM manycol WHERE x0=100 + SELECT x1 FROM manycol WHERE x0='100' ]], { -- "103" @@ -619,13 +619,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "misc1-12.2", [[ SELECT '0'==0.0 ]], { -- - true + 1, "Type mismatch: can not convert 0 to numeric" -- }) diff --git a/test/sql-tap/select1.test.lua b/test/sql-tap/select1.test.lua index d8be38da6..8c91b4d31 100755 --- a/test/sql-tap/select1.test.lua +++ b/test/sql-tap/select1.test.lua @@ -1913,7 +1913,7 @@ test:do_execsql_test( test:do_execsql_test( "select1-12.7", [[ - SELECT * FROM t3 WHERE a=(SELECT 1); + SELECT * FROM t3 WHERE a=(SELECT '1'); ]], { -- 0, "1", "2" @@ -1923,7 +1923,7 @@ test:do_execsql_test( test:do_execsql_test( "select1-12.8", [[ - SELECT * FROM t3 WHERE a=(SELECT 2); + SELECT * FROM t3 WHERE a=(SELECT '2'); ]], { -- diff --git a/test/sql-tap/select7.test.lua b/test/sql-tap/select7.test.lua index e1e43c557..0d1390fd6 100755 --- a/test/sql-tap/select7.test.lua +++ b/test/sql-tap/select7.test.lua @@ -256,7 +256,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); - SELECT typeof(a), a FROM t5 GROUP BY a HAVING a "string", "123" diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua index e0771825e..bad702de9 100755 --- a/test/sql-tap/subquery.test.lua +++ b/test/sql-tap/subquery.test.lua @@ -284,13 +284,13 @@ test:do_execsql_test( -- }) -test:do_execsql_test( +test:do_catchsql_test( "subquery-2.3.2", [[ SELECT a IN (10.0, 20) FROM t3; ]], { -- - false + 1, "Type mismatch: can not convert text to real" -- }) diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua deleted file mode 100755 index 1314d0aad..000000000 --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua +++ /dev/null @@ -1,508 +0,0 @@ -#!/usr/bin/env tarantool -test = require("sqltester") -test:plan(47) - ---!./tcltestrunner.lua --- 2014 June 26 --- --- 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. --- --- This file implements tests to verify that ticket [9a8b09f8e6] has been --- fixed. --- --- ["set","testdir",[["file","dirname",["argv0"]]]] --- ["source",[["testdir"],"\/tester.tcl"]] -testprefix = "tkt-9a8b09f8e6" --- MUST_WORK_TEST -if (0 > 0) - then -end -test:do_execsql_test( - 1.1, - [[ - CREATE TABLE t1(x TEXT primary key); - INSERT INTO t1 VALUES('1'); - ]], { - -- <1.1> - - -- - }) - -test:do_execsql_test( - 1.2, - [[ - CREATE TABLE t2(x INTEGER primary key); - INSERT INTO t2 VALUES(1); - ]], { - -- <1.2> - - -- - }) - -test:do_execsql_test( - 1.3, - [[ - CREATE TABLE t3(x NUMBER primary key); - INSERT INTO t3 VALUES(1.0); - ]], { - -- <1.3> - - -- - }) - -test:do_execsql_test( - 1.4, - [[ - CREATE TABLE t4(x NUMBER primary key); - INSERT INTO t4 VALUES(1.11); - ]], { - -- <1.4> - - -- - }) - -test:do_execsql_test( - 1.5, - [[ - CREATE TABLE t5(id INT primary key, x INT , y TEXT); - INSERT INTO t5 VALUES(1, 1, 'one'); - INSERT INTO t5 VALUES(2, 1, 'two'); - INSERT INTO t5 VALUES(3, 1.0, 'three'); - INSERT INTO t5 VALUES(4, 1.0, 'four'); - ]], { - -- <1.5> - - -- - }) - -test:do_execsql_test( - 2.1, - [[ - SELECT x FROM t1 WHERE x IN (1); - ]], { - -- <2.1> - "1" - -- - }) - -test:do_execsql_test( - 2.2, - [[ - SELECT x FROM t1 WHERE x IN (1.0); - ]], { - -- <2.2> - "1" - -- - }) - -test:do_execsql_test( - 2.3, - [[ - SELECT x FROM t1 WHERE x IN ('1'); - ]], { - -- <2.3> - "1" - -- - }) - -test:do_execsql_test( - 2.4, - [[ - SELECT x FROM t1 WHERE x IN ('1.0'); - ]], { - -- <2.4> - - -- - }) - -test:do_execsql_test( - 2.5, - [[ - SELECT x FROM t1 WHERE 1 IN (x); - ]], { - -- <2.5> - "1" - -- - }) - -test:do_execsql_test( - 2.6, - [[ - SELECT x FROM t1 WHERE 1.0 IN (x); - ]], { - -- <2.6> - "1" - -- - }) - -test:do_execsql_test( - 2.7, - [[ - SELECT x FROM t1 WHERE '1' IN (x); - ]], { - -- <2.7> - "1" - -- - }) - -test:do_execsql_test( - 2.8, - [[ - SELECT x FROM t1 WHERE '1.0' IN (x); - ]], { - -- <2.8> - - -- - }) - -test:do_execsql_test( - 3.1, - [[ - SELECT x FROM t2 WHERE x IN (1); - ]], { - -- <3.1> - 1 - -- - }) - -test:do_execsql_test( - 3.2, - [[ - SELECT x FROM t2 WHERE x IN (1.0); - ]], { - -- <3.2> - 1 - -- - }) - -test:do_catchsql_test( - 3.3, - [[ - SELECT x FROM t2 WHERE x IN ('1'); - ]], { - -- <3.3> - 1, "Type mismatch: can not convert 1 to integer" - -- - }) - -test:do_execsql_test( - 3.5, - [[ - SELECT x FROM t2 WHERE 1 IN (x); - ]], { - -- <3.5> - 1 - -- - }) - -test:do_execsql_test( - 3.6, - [[ - SELECT x FROM t2 WHERE 1.0 IN (x); - ]], { - -- <3.6> - 1 - -- - }) - -test:do_catchsql_test( - 3.7, - [[ - SELECT x FROM t2 WHERE '1' IN (x); - ]], { - -- <3.7> - 1, "Type mismatch: can not convert 1 to integer" - -- - }) - -test:do_execsql_test( - 4.1, - [[ - SELECT x FROM t3 WHERE x IN (1); - ]], { - -- <4.1> - 1.0 - -- - }) - -test:do_execsql_test( - 4.2, - [[ - SELECT x FROM t3 WHERE x IN (1.0); - ]], { - -- <4.2> - 1.0 - -- - }) - -test:do_catchsql_test( - 4.3, - [[ - SELECT x FROM t3 WHERE x IN ('1'); - ]], { - -- <4.3> - 1, "Type mismatch: can not convert 1 to number" - -- - }) - -test:do_catchsql_test( - 4.4, - [[ - SELECT x FROM t3 WHERE x IN ('1.0'); - ]], { - -- <4.4> - 1, "Type mismatch: can not convert 1.0 to number" - -- - }) - -test:do_execsql_test( - 4.5, - [[ - SELECT x FROM t3 WHERE 1 IN (x); - ]], { - -- <4.5> - 1.0 - -- - }) - -test:do_execsql_test( - 4.6, - [[ - SELECT x FROM t3 WHERE 1.0 IN (x); - ]], { - -- <4.6> - 1.0 - -- - }) - -test:do_catchsql_test( - 4.7, - [[ - SELECT x FROM t3 WHERE '1' IN (x); - ]], { - -- <4.7> - 1, "Type mismatch: can not convert 1 to number" - -- - }) - -test:do_catchsql_test( - 4.8, - [[ - SELECT x FROM t3 WHERE '1.0' IN (x); - ]], { - -- <4.8> - 1, "Type mismatch: can not convert 1.0 to number" - -- - }) - -test:do_execsql_test( - 5.1, - [[ - SELECT x FROM t4 WHERE x IN (1); - ]], { - -- <5.1> - - -- - }) - -test:do_execsql_test( - 5.2, - [[ - SELECT x FROM t4 WHERE x IN (1.0); - ]], { - -- <5.2> - - -- - }) - -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_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" - -- - }) - -test:do_execsql_test( - 5.5, - [[ - SELECT x FROM t4 WHERE x IN (1.11); - ]], { - -- <5.5> - 1.11 - -- - }) - -test:do_catchsql_test( - 5.6, - [[ - SELECT x FROM t4 WHERE x IN ('1.11'); - ]], { - -- <5.6> - 1, "Type mismatch: can not convert 1.11 to number" - -- - }) - -test:do_execsql_test( - 5.7, - [[ - SELECT x FROM t4 WHERE 1 IN (x); - ]], { - -- <5.7> - - -- - }) - -test:do_execsql_test( - 5.8, - [[ - SELECT x FROM t4 WHERE 1.0 IN (x); - ]], { - -- <5.8> - - -- - }) - -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_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" - -- - }) - -test:do_execsql_test( - 5.11, - [[ - SELECT x FROM t4 WHERE 1.11 IN (x); - ]], { - -- <5.11> - 1.11 - -- - }) - -test:do_catchsql_test( - 5.12, - [[ - SELECT x FROM t4 WHERE '1.11' IN (x); - ]], { - -- <5.12> - 1, "Type mismatch: can not convert 1.11 to number" - -- - }) - -test:do_execsql_test( - 6.1, - [[ - SELECT x, y FROM t5 WHERE x IN (1); - ]], { - -- <6.1> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.2, - [[ - SELECT x, y FROM t5 WHERE x IN (1.0); - ]], { - -- <6.2> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.3, - [[ - SELECT x, y FROM t5 WHERE x IN ('1'); - ]], { - -- <6.3> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.4, - [[ - SELECT x, y FROM t5 WHERE x IN ('1.0'); - ]], { - -- <6.4> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.5, - [[ - SELECT x, y FROM t5 WHERE 1 IN (x); - ]], { - -- <6.5> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.6, - [[ - SELECT x, y FROM t5 WHERE 1.0 IN (x); - ]], { - -- <6.6> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.7, - [[ - SELECT x, y FROM t5 WHERE '1' IN (x); - ]], { - -- <6.7> - 1, "one", 1, "two", 1, "three", 1.0, "four" - -- - }) - -test:do_execsql_test( - 6.8, - [[ - SELECT x, y FROM t5 WHERE '1.0' IN (x); - ]], { - -- <6.8> - 1, "one", 1, "two", 1, "three", 1, "four" - -- - }) - - - -test:finish_test() diff --git a/test/sql-tap/tkt3493.test.lua b/test/sql-tap/tkt3493.test.lua index de77e61e9..82ba828d0 100755 --- a/test/sql-tap/tkt3493.test.lua +++ b/test/sql-tap/tkt3493.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(26) +test:plan(25) --!./tcltestrunner.lua -- 2008 October 13 @@ -45,7 +45,7 @@ test:do_execsql_test( [[ SELECT CASE - WHEN B.val = 1 THEN 'XYZ' + WHEN B.val = '1' THEN 'XYZ' ELSE A.val END AS Col1 FROM B @@ -63,7 +63,7 @@ test:do_execsql_test( [[ SELECT DISTINCT CASE - WHEN B.val = 1 THEN 'XYZ' + WHEN B.val = '1' THEN 'XYZ' ELSE A.val END AS Col1 FROM B @@ -79,7 +79,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-1.4", [[ - SELECT b.val, CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 FROM b; + SELECT b.val, CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1 FROM b; ]], { -- "1", "xyz", "2", "2" @@ -91,7 +91,7 @@ test:do_execsql_test( [[ SELECT DISTINCT b.val, - CASE WHEN b.val = 1 THEN 'xyz' ELSE b.val END AS col1 + CASE WHEN b.val = '1' THEN 'xyz' ELSE b.val END AS col1 FROM b; ]], { -- @@ -126,23 +126,13 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.2.1", [[ - SELECT a=123 FROM t1 GROUP BY a + SELECT a='123' FROM t1 GROUP BY a ]], { -- true -- }) -test:do_execsql_test( - "tkt3493-2.2.2", - [[ - SELECT a=123 FROM t1 - ]], { - -- - true - -- - }) - test:do_execsql_test( "tkt3493-2.2.3", [[ @@ -156,7 +146,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.2.4", [[ - SELECT count(*), a=123 FROM t1 + SELECT count(*), a='123' FROM t1 ]], { -- 1, true @@ -166,7 +156,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.2.5", [[ - SELECT count(*), +a=123 FROM t1 + SELECT count(*), +a='123' FROM t1 ]], { -- 1, true @@ -176,7 +166,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.3.3", [[ - SELECT b='456' FROM t1 GROUP BY a + SELECT b = 456 FROM t1 GROUP BY a ]], { -- true @@ -186,7 +176,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.3.1", [[ - SELECT b='456' FROM t1 GROUP BY b + SELECT b = 456 FROM t1 GROUP BY b ]], { -- true @@ -196,7 +186,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.3.2", [[ - SELECT b='456' FROM t1 + SELECT b = 456 FROM t1 ]], { -- true @@ -206,7 +196,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.4.1", [[ - SELECT typeof(a), a FROM t1 GROUP BY a HAVING a=123 + SELECT typeof(a), a FROM t1 GROUP BY a HAVING a='123' ]], { -- "string", "123" @@ -216,7 +206,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.4.2", [[ - SELECT typeof(a), a FROM t1 GROUP BY b HAVING a=123 + SELECT typeof(a), a FROM t1 GROUP BY b HAVING a='123' ]], { -- "string", "123" @@ -226,7 +216,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.5.1", [[ - SELECT typeof(b), b FROM t1 GROUP BY a HAVING b='456' + SELECT typeof(b), b FROM t1 GROUP BY a HAVING b=456 ]], { -- "integer", 456 @@ -236,7 +226,7 @@ test:do_execsql_test( test:do_execsql_test( "tkt3493-2.5.2", [[ - SELECT typeof(b), b FROM t1 GROUP BY b HAVING b='456' + SELECT typeof(b), b FROM t1 GROUP BY b HAVING b=456 ]], { -- "integer", 456 diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua index 96895b4a7..cc7e066bf 100755 --- a/test/sql-tap/transitive1.test.lua +++ b/test/sql-tap/transitive1.test.lua @@ -63,7 +63,7 @@ test:do_execsql_test( INSERT INTO t2 VALUES(2, 20,20,'20'); INSERT INTO t2 VALUES(3, 3,3,'3'); - SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c=20; + SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c='20'; ]], { -- 20, 20, "20" @@ -73,7 +73,7 @@ test:do_execsql_test( test:do_execsql_test( "transitive1-210", [[ - SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c>='20' ORDER BY +a; + SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c>='20' ORDER BY +a; ]], { -- 3, 3, "3", 20, 20, "20" @@ -83,7 +83,7 @@ test:do_execsql_test( test:do_execsql_test( "transitive1-220", [[ - SELECT a,b,c FROM t2 WHERE a=b AND c=b AND c<='20' ORDER BY +a; + SELECT a,b,c FROM t2 WHERE a=b AND c=CAST(b AS STRING) AND c<='20' ORDER BY +a; ]], { -- 20, 20, "20", 100, 100, "100" @@ -402,7 +402,7 @@ test:do_execsql_test( [[ CREATE TABLE x(i INTEGER PRIMARY KEY, y TEXT); INSERT INTO x VALUES(10, '10'); - SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND x.i=x.y; + SELECT * FROM x WHERE x.y>='1' AND x.y<'2' AND CAST(x.i AS STRING)=x.y; ]], { -- 10, "10" @@ -430,7 +430,7 @@ test:do_execsql_test( [[ CREATE TABLE t3(i INTEGER PRIMARY KEY, t TEXT); INSERT INTO t3 VALUES(10, '10'); - SELECT * FROM t3 WHERE i=t AND t = '10 '; + SELECT * FROM t3 WHERE CAST(i AS STRING)=t AND t = '10 '; ]], { -- @@ -443,7 +443,7 @@ test:do_execsql_test( CREATE TABLE u1(x TEXT PRIMARY KEY, y INTEGER, z TEXT); CREATE INDEX i1 ON u1(x); INSERT INTO u1 VALUES('00013', 13, '013'); - SELECT * FROM u1 WHERE x=y AND y=z AND z='013'; + SELECT * FROM u1 WHERE CAST(x AS INTEGER)=y AND y=CAST(z AS INTEGER) AND z='013'; ]], { -- "00013",13,"013" diff --git a/test/sql-tap/where2.test.lua b/test/sql-tap/where2.test.lua index f267be8e6..7348a855a 100755 --- a/test/sql-tap/where2.test.lua +++ b/test/sql-tap/where2.test.lua @@ -4,7 +4,7 @@ yaml = require("yaml") fio = require("fio") ffi = require("ffi") -test:plan(74) +test:plan(62) ffi.cdef[[ int dup(int oldfd); @@ -622,181 +622,12 @@ test:do_test( -- }) --- if X(356, "X!cmd", [=[["expr","[permutation] != \"no_optimization\""]]=]) --- then - -- Ticket #2249. Make sure the OR optimization is not attempted if - -- comparisons between columns of different affinities are needed. - -- - test:do_test( - "where2-6.7", - function() - test:execsql [[ - CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100)); - CREATE TABLE t2249b(b INTEGER PRIMARY KEY); - INSERT INTO t2249a(a) VALUES('0123'); - INSERT INTO t2249b VALUES(123); - ]] - return queryplan([[ - -- Because a is type TEXT and b is type INTEGER, both a and b - -- will attempt to convert to NUMERIC before the comparison. - -- They will thus compare equal. - -- - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.9", - function() - return queryplan([[ - -- The + operator doesn't affect RHS. - -- - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.9.2", - function() - -- The same thing but with the expression flipped around. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a - ]]) - end, { - -- - 123, "0123","nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.10", - function() - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.11", - function() - -- This will not attempt the OR optimization because of the a=b - -- comparison. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello'; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.11.2", - function() - -- Permutations of the expression terms. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello'; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.11.3", - function() - -- Permutations of the expression terms. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.11.4", - function() - -- Permutations of the expression terms. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a; - ]]) - end, { - -- - 123, '0123', "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - -- These tests are not run if subquery support is not included in the - -- build. This is because these tests test the "a = 1 OR a = 2" to - -- "a IN (1, 2)" optimisation transformation, which is not enabled if - -- subqueries and the IN operator is not available. - -- - test:do_test( - "where2-6.12", - function() - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello'; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.12.2", - function() - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.12.3", - function() - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello'; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - test:do_test( - "where2-6.13", - function() - -- The addition of +a on the second term disabled the OR optimization. - -- But we should still get the same empty-set result as in where2-6.9. - return queryplan([[ - SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello'; - ]]) - end, { - -- - 123, "0123", "nosort", "T2249B", "*", "T2249A", "*" - -- - }) - - + test:execsql [[ + CREATE TABLE t2249a(a TEXT PRIMARY KEY, x VARCHAR(100)); + CREATE TABLE t2249b(b INTEGER PRIMARY KEY); + INSERT INTO t2249a(a) VALUES('0123'); + INSERT INTO t2249b VALUES(123); + ]] -- Variations on the order of terms in a WHERE clause in order -- to make sure the OR optimizer can recognize them all. diff --git a/test/sql-tap/where5.test.lua b/test/sql-tap/where5.test.lua index 3aefcaca5..4a197aac2 100755 --- a/test/sql-tap/where5.test.lua +++ b/test/sql-tap/where5.test.lua @@ -34,7 +34,7 @@ test:do_test("where5-1.0", function() INSERT INTO t3 SELECT CAST(x AS INTEGER) FROM t1; ]] return test:execsql [[ - SELECT * FROM t1 WHERE x<0 + SELECT * FROM t1 WHERE x<'0' ]] end, { -- @@ -43,7 +43,7 @@ end, { }) test:do_execsql_test("where5-1.1", [[ - SELECT * FROM t1 WHERE x<=0 + SELECT * FROM t1 WHERE x<='0' ]], { -- '-1', '0' @@ -51,7 +51,7 @@ test:do_execsql_test("where5-1.1", [[ }) test:do_execsql_test("where5-1.2", [[ - SELECT * FROM t1 WHERE x=0 + SELECT * FROM t1 WHERE x='0' ]], { -- '0' @@ -59,7 +59,7 @@ test:do_execsql_test("where5-1.2", [[ }) test:do_execsql_test("where5-1.3", [[ - SELECT * FROM t1 WHERE x>=0 + SELECT * FROM t1 WHERE x>='0' ]], { -- '0', '1' @@ -67,7 +67,7 @@ test:do_execsql_test("where5-1.3", [[ }) test:do_execsql_test("where5-1.4", [[ - SELECT * FROM t1 WHERE x>0 + SELECT * FROM t1 WHERE x>'0' ]], { -- '1' @@ -75,7 +75,7 @@ test:do_execsql_test("where5-1.4", [[ }) test:do_execsql_test("where5-1.5", [[ - SELECT * FROM t1 WHERE x<>0 + SELECT * FROM t1 WHERE x<>'0' ]], { -- '-1', '1' diff --git a/test/sql/types.result b/test/sql/types.result index 8810a9f82..b40f45029 100644 --- a/test/sql/types.result +++ b/test/sql/types.result @@ -608,11 +608,8 @@ box.execute("SELECT 18446744073709551615.0 > 18446744073709551615") ... box.execute("SELECT 18446744073709551615 IN ('18446744073709551615', 18446744073709551615.0)") --- -- metadata: - - name: COLUMN_1 - type: boolean - rows: - - [true] +- null +- 'Type mismatch: can not convert 18446744073709551615 to numeric' ... box.execute("SELECT 1 LIMIT 18446744073709551615;") --- -- 2.25.1