[Tarantool-patches] [PATCH v5 4/6] sql: remove implicit cast from comparison opcodes

imeevma at tarantool.org imeevma at tarantool.org
Fri Aug 21 12:19:54 MSK 2020


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, {
         -- <in-11.2>
-        1, 2
+        1, "Type mismatch: can not convert 2 to numeric"
         -- </in-11.2>
     })
 
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(
         -- </join-11.9>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "join-11.10",
     [[
         SELECT * FROM t2 NATURAL JOIN t1 
     ]], {
         -- <join-11.10>
-        1, "one", 2, "two"
+        1, "Type mismatch: can not convert 1 to numeric"
         -- </join-11.10>
     })
 
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'
     ]], {
         -- <misc1-1.4>
         "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'
     ]], {
         -- <misc1-1.5>
         "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, {
         -- <misc1-1.6>
         "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, {
         -- <misc1-1.8>
@@ -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, {
         -- <misc1-1.9>
@@ -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, {
         -- <misc1-1.10>
@@ -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, {
         -- <misc1-1.11>
@@ -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, {
         -- <misc1-10.3>
@@ -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'
     ]], {
         -- <misc1-10.6>
         "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, {
         -- <misc1-10.7>
@@ -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'
     ]], {
         -- <misc1-10.8>
         "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'
     ]], {
         -- <misc1-10.10>
         "103"
@@ -619,13 +619,13 @@ test:do_execsql_test(
         -- </misc1-12.1>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "misc1-12.2",
     [[
         SELECT '0'==0.0
     ]], {
         -- <misc1-12.2>
-        true
+        1, "Type mismatch: can not convert 0 to numeric"
         -- </misc1-12.2>
     })
 
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');
         ]], {
             -- <select1-12.7>
             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');
     ]], {
         -- <select1-12.8>
 
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<b;
+        SELECT typeof(a), a FROM t5 GROUP BY a HAVING CAST(a AS INTEGER)<b;
     ]], {
         -- <select7-7.7>
         "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(
         -- </subquery-2.3.1>
     })
 
-test:do_execsql_test(
+test:do_catchsql_test(
     "subquery-2.3.2",
     [[
         SELECT a IN (10.0, 20) FROM t3;
     ]], {
         -- <subquery-2.3.2>
-        false
+        1, "Type mismatch: can not convert text to real"
         -- </subquery-2.3.2>
     })
 
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>
-        
-        -- </1.1>
-    })
-
-test:do_execsql_test(
-    1.2,
-    [[
-        CREATE TABLE t2(x INTEGER primary key);
-        INSERT INTO t2 VALUES(1);
-    ]], {
-        -- <1.2>
-        
-        -- </1.2>
-    })
-
-test:do_execsql_test(
-    1.3,
-    [[
-        CREATE TABLE t3(x NUMBER primary key);
-        INSERT INTO t3 VALUES(1.0);
-    ]], {
-        -- <1.3>
-        
-        -- </1.3>
-    })
-
-test:do_execsql_test(
-    1.4,
-    [[
-        CREATE TABLE t4(x NUMBER primary key);
-        INSERT INTO t4 VALUES(1.11);
-    ]], {
-        -- <1.4>
-        
-        -- </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>
-        
-        -- </1.5>
-    })
-
-test:do_execsql_test(
-    2.1,
-    [[
-        SELECT x FROM t1 WHERE x IN (1);
-    ]], {
-        -- <2.1>
-        "1"
-        -- </2.1>
-    })
-
-test:do_execsql_test(
-    2.2,
-    [[
-        SELECT x FROM t1 WHERE x IN (1.0);
-    ]], {
-        -- <2.2>
-        "1"
-        -- </2.2>
-    })
-
-test:do_execsql_test(
-    2.3,
-    [[
-        SELECT x FROM t1 WHERE x IN ('1');
-    ]], {
-        -- <2.3>
-        "1"
-        -- </2.3>
-    })
-
-test:do_execsql_test(
-    2.4,
-    [[
-        SELECT x FROM t1 WHERE x IN ('1.0');
-    ]], {
-        -- <2.4>
-        
-        -- </2.4>
-    })
-
-test:do_execsql_test(
-    2.5,
-    [[
-        SELECT x FROM t1 WHERE 1 IN (x);
-    ]], {
-        -- <2.5>
-        "1"
-        -- </2.5>
-    })
-
-test:do_execsql_test(
-    2.6,
-    [[
-        SELECT x FROM t1 WHERE 1.0 IN (x);
-    ]], {
-        -- <2.6>
-        "1"
-        -- </2.6>
-    })
-
-test:do_execsql_test(
-    2.7,
-    [[
-        SELECT x FROM t1 WHERE '1' IN (x);
-    ]], {
-        -- <2.7>
-        "1"
-        -- </2.7>
-    })
-
-test:do_execsql_test(
-    2.8,
-    [[
-        SELECT x FROM t1 WHERE '1.0' IN (x);
-    ]], {
-        -- <2.8>
-        
-        -- </2.8>
-    })
-
-test:do_execsql_test(
-    3.1,
-    [[
-        SELECT x FROM t2 WHERE x IN (1);
-    ]], {
-        -- <3.1>
-        1
-        -- </3.1>
-    })
-
-test:do_execsql_test(
-    3.2,
-    [[
-        SELECT x FROM t2 WHERE x IN (1.0);
-    ]], {
-        -- <3.2>
-        1
-        -- </3.2>
-    })
-
-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"
-        -- </3.3>
-    })
-
-test:do_execsql_test(
-    3.5,
-    [[
-        SELECT x FROM t2 WHERE 1 IN (x);
-    ]], {
-        -- <3.5>
-        1
-        -- </3.5>
-    })
-
-test:do_execsql_test(
-    3.6,
-    [[
-        SELECT x FROM t2 WHERE 1.0 IN (x);
-    ]], {
-        -- <3.6>
-        1
-        -- </3.6>
-    })
-
-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"
-        -- </3.7>
-    })
-
-test:do_execsql_test(
-    4.1,
-    [[
-        SELECT x FROM t3 WHERE x IN (1);
-    ]], {
-        -- <4.1>
-        1.0
-        -- </4.1>
-    })
-
-test:do_execsql_test(
-    4.2,
-    [[
-        SELECT x FROM t3 WHERE x IN (1.0);
-    ]], {
-        -- <4.2>
-        1.0
-        -- </4.2>
-    })
-
-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"
-        -- </4.3>
-    })
-
-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"
-        -- </4.4>
-    })
-
-test:do_execsql_test(
-    4.5,
-    [[
-        SELECT x FROM t3 WHERE 1 IN (x);
-    ]], {
-        -- <4.5>
-        1.0
-        -- </4.5>
-    })
-
-test:do_execsql_test(
-    4.6,
-    [[
-        SELECT x FROM t3 WHERE 1.0 IN (x);
-    ]], {
-        -- <4.6>
-        1.0
-        -- </4.6>
-    })
-
-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"
-        -- </4.7>
-    })
-
-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"
-        -- </4.8>
-    })
-
-test:do_execsql_test(
-    5.1,
-    [[
-        SELECT x FROM t4 WHERE x IN (1);
-    ]], {
-        -- <5.1>
-        
-        -- </5.1>
-    })
-
-test:do_execsql_test(
-    5.2,
-    [[
-        SELECT x FROM t4 WHERE x IN (1.0);
-    ]], {
-        -- <5.2>
-        
-        -- </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"
-        -- </5.3>
-    })
-
-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"
-        -- </5.4>
-    })
-
-test:do_execsql_test(
-    5.5,
-    [[
-        SELECT x FROM t4 WHERE x IN (1.11);
-    ]], {
-        -- <5.5>
-        1.11
-        -- </5.5>
-    })
-
-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"
-        -- </5.6>
-    })
-
-test:do_execsql_test(
-    5.7,
-    [[
-        SELECT x FROM t4 WHERE 1 IN (x);
-    ]], {
-        -- <5.7>
-        
-        -- </5.7>
-    })
-
-test:do_execsql_test(
-    5.8,
-    [[
-        SELECT x FROM t4 WHERE 1.0 IN (x);
-    ]], {
-        -- <5.8>
-        
-        -- </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"
-        -- </5.9>
-    })
-
-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"
-        -- </5.10>
-    })
-
-test:do_execsql_test(
-    5.11,
-    [[
-        SELECT x FROM t4 WHERE 1.11 IN (x);
-    ]], {
-        -- <5.11>
-        1.11
-        -- </5.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"
-        -- </5.12>
-    })
-
-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"
-        -- </6.1>
-    })
-
-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"
-        -- </6.2>
-    })
-
-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"
-        -- </6.3>
-    })
-
-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"
-        -- </6.4>
-    })
-
-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"
-        -- </6.5>
-    })
-
-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"
-        -- </6.6>
-    })
-
-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"
-        -- </6.7>
-    })
-
-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"
-        -- </6.8>
-    })
-
-
-
-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;
     ]], {
         -- <tkt3493-1.4>
         "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;
     ]], {
         -- <tkt3493-1.5>
@@ -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
     ]], {
         -- <tkt3493-2.2.1>
         true
         -- </tkt3493-2.2.1>
     })
 
-test:do_execsql_test(
-    "tkt3493-2.2.2",
-    [[
-        SELECT a=123 FROM t1 
-    ]], {
-        -- <tkt3493-2.2.2>
-        true
-        -- </tkt3493-2.2.2>
-    })
-
 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
     ]], {
         -- <tkt3493-2.2.4>
         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
     ]], {
         -- <tkt3493-2.2.5>
         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
     ]], {
         -- <tkt3493-2.3.3>
         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
     ]], {
         -- <tkt3493-2.3.1>
         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
     ]], {
         -- <tkt3493-2.3.2>
         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'
     ]], {
         -- <tkt3493-2.4.1>
         "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'
     ]], {
         -- <tkt3493-2.4.2>
         "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
     ]], {
         -- <tkt3493-2.5.1>
         "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
     ]], {
         -- <tkt3493-2.5.2>
         "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';
     ]], {
         -- <transitive1-200>
         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;
     ]], {
         -- <transitive1-210>
         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;
     ]], {
         -- <transitive1-220>
         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;
     ]], {
         -- <transitive1-500>
         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 ';
     ]], {
         -- <transitive1-520>
 
@@ -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';
     ]], {
         -- <transitive1-530>
         "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(
         -- </where2-6.6>
     })
 
--- 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, {
-            -- <where2-6.7>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.7>
-        })
-
-    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, {
-            -- <where2-6.9>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.9>
-        })
-
-    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, {
-            -- <where2-6.9.2>
-            123, "0123","nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.9.2>
-        })
-
-    test:do_test(
-        "where2-6.10",
-        function()
-            return queryplan([[
-    SELECT b,a FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
-  ]])
-        end, {
-            -- <where2-6.10>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.10>
-        })
-
-    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, {
-            -- <where2-6.11>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.11>
-        })
-
-    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, {
-            -- <where2-6.11.2>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.11.2>
-        })
-
-    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, {
-            -- <where2-6.11.3>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.11.3>
-        })
-
-    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, {
-            -- <where2-6.11.4>
-            123, '0123', "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.11.4>
-        })
-
-    -- 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, {
-            -- <where2-6.12>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.12>
-        })
-
-    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, {
-            -- <where2-6.12.2>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.12.2>
-        })
-
-    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, {
-            -- <where2-6.12.3>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.12.3>
-        })
-
-    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, {
-            -- <where2-6.13>
-            123, "0123", "nosort", "T2249B", "*", "T2249A", "*"
-            -- </where2-6.13>
-        })
-
-
+    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, {
     -- <where5-1.0>
@@ -43,7 +43,7 @@ end, {
 })
 
 test:do_execsql_test("where5-1.1", [[
-    SELECT * FROM t1 WHERE x<=0
+    SELECT * FROM t1 WHERE x<='0'
 ]], {
     -- <where5-1.1>
     '-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'
 ]], {
     -- <where5-1.2>
     '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'
 ]], {
     -- <where5-1.3>
     '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'
 ]], {
     -- <where5-1.4>
     '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'
 ]], {
     -- <where5-1.5>
     '-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



More information about the Tarantool-patches mailing list