[Tarantool-patches] [PATCH 3/6] sql: replace ApplyType by CheckType for IN operator

[Mergen Imeev <imeevma@tarantool.org>]

This patch removes implicit cast from STRING to numeric
and vice versa of left operand of IN operator.

Part of #4230
Part of #4692
---
 src/box/sql/expr.c                   |  2 +-
 test/sql-tap/in3.test.lua            | 14 +-----
 test/sql-tap/subquery.test.lua       | 69 +---------------------------
 test/sql-tap/tkt-80e031a00f.test.lua |  4 +-
 test/sql/boolean.result              | 12 ++---
 5 files changed, 11 insertions(+), 90 deletions(-)

diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 019628a26..8ec685cd9 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -3179,7 +3179,7 @@ sqlExprCodeIN(Parse * pParse,	/* Parsing and code generating context */
 	 * true.
 	 */
 	zAff[nVector] = field_type_MAX;
-	sqlVdbeAddOp4(v, OP_ApplyType, rLhs, nVector, 0, (char*)zAff,
+	sqlVdbeAddOp4(v, OP_CheckType, rLhs, nVector, 0, (char*)zAff,
 			  P4_DYNAMIC);
 	/*
 	 * zAff will be freed at the end of VDBE execution, since
diff --git a/test/sql-tap/in3.test.lua b/test/sql-tap/in3.test.lua
index f7681640e..a2147b0e8 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(
         -- </in3-3.2>
     })
 
-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, {
-        -- <in3-3.3>
-        1, true
-        -- </in3-3.3>
-    })
-
 test:do_test(
     "in3-3.4",
     function()
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(
         -- </subquery-2.4.3>
     })
 
-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');
-    ]], {
-        -- <subquery-2.5.1>
-        
-        -- </subquery-2.5.1>
-    })
-
-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, {
-        -- <subquery-2.5.2>
-        "10"
-        -- </subquery-2.5.2>
-    })
-
-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, {
-        -- <subquery-2.5.3.1>
-        "10"
-        -- </subquery-2.5.3.1>
-    })
-
--- 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;
-    ]], {
-        -- <subquery-2.5.4>
-        
-        -- </subquery-2.5.4>
-    })
-
 --------------------------------------------------------------------
 -- The following test cases - subquery-3.* - test tickets that
 -- were raised during development of correlated subqueries.
diff --git a/test/sql-tap/tkt-80e031a00f.test.lua b/test/sql-tap/tkt-80e031a00f.test.lua
index a0e6539e0..c883937ca 100755
--- a/test/sql-tap/tkt-80e031a00f.test.lua
+++ b/test/sql-tap/tkt-80e031a00f.test.lua
@@ -346,7 +346,7 @@ test:do_catchsql_test(
         SELECT 'hello' IN t1
     ]], {
         -- <tkt-80e031a00f.27>
-        1, 'Type mismatch: can not convert hello to integer'
+        1, 'Type mismatch: can not convert text to integer'
         -- </tkt-80e031a00f.27>
     })
 
@@ -356,7 +356,7 @@ test:do_catchsql_test(
         SELECT 'hello' NOT IN t1
     ]], {
         -- <tkt-80e031a00f.28>
-        1, 'Type mismatch: can not convert hello to integer'
+        1, 'Type mismatch: can not convert text to integer'
         -- </tkt-80e031a00f.28>
     })
 
diff --git a/test/sql/boolean.result b/test/sql/boolean.result
index e88183854..c74713c34 100644
--- a/test/sql/boolean.result
+++ b/test/sql/boolean.result
@@ -3877,12 +3877,12 @@ SELECT false IN (0, 1, 2, 3);
 SELECT true IN (SELECT b FROM t7);
  | ---
  | - null
- | - 'Type mismatch: can not convert TRUE to integer'
+ | - 'Type mismatch: can not convert boolean to integer'
  | ...
 SELECT false IN (SELECT b FROM t7);
  | ---
  | - null
- | - 'Type mismatch: can not convert FALSE to integer'
+ | - 'Type mismatch: can not convert boolean to integer'
  | ...
 SELECT a1, a1 IN (0, 1, 2, 3) FROM t6
  | ---
@@ -5038,22 +5038,22 @@ SELECT a2 IN (0.1, 1.2, 2.3, 3.4) FROM t6 LIMIT 1;
 SELECT true IN (SELECT c FROM t8);
  | ---
  | - null
- | - 'Type mismatch: can not convert TRUE to number'
+ | - 'Type mismatch: can not convert boolean to number'
  | ...
 SELECT false IN (SELECT c FROM t8);
  | ---
  | - null
- | - 'Type mismatch: can not convert FALSE to number'
+ | - 'Type mismatch: can not convert boolean to number'
  | ...
 SELECT a1 IN (SELECT c FROM t8) FROM t6 LIMIT 1;
  | ---
  | - null
- | - 'Type mismatch: can not convert FALSE to number'
+ | - 'Type mismatch: can not convert boolean to number'
  | ...
 SELECT a2 IN (SELECT c FROM t8) FROM t6 LIMIT 1;
  | ---
  | - null
- | - 'Type mismatch: can not convert TRUE to number'
+ | - 'Type mismatch: can not convert boolean to number'
  | ...
 
 SELECT true BETWEEN 0.1 and 9.9;
-- 
2.25.1