Tarantool development patches archive
 help / color / mirror / Atom feed
From: Mergen Imeev <imeevma@tarantool.org>
To: v.shpilevoy@tarantool.org, tsafin@tarantool.org,
	tarantool-patches@dev.tarantool.org
Subject: [Tarantool-patches] [PATCH 3/6] sql: replace ApplyType by CheckType for IN operator
Date: Thu, 28 May 2020 17:17:33 +0300	[thread overview]
Message-ID: <a88a83ec1eb1fdd9e946e0b97f270f206e85fb3c.1590671266.git.imeevma@gmail.com> (raw)
In-Reply-To: <cover.1590671266.git.imeevma@gmail.com>

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

  parent reply	other threads:[~2020-05-28 14:17 UTC|newest]

Thread overview: 21+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2020-05-28 14:17 [Tarantool-patches] [PATCH 0/6] Remove implicit cast Mergen Imeev
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 1/6] sql: remove implicit cast for assignment Mergen Imeev
2020-06-01 17:03   ` Vladislav Shpilevoy
2020-06-09 11:41     ` Mergen Imeev
2020-06-09 22:28       ` Vladislav Shpilevoy
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 2/6] sql: remove mem_apply_type() from OP_MakeRecord Mergen Imeev
2020-06-01 17:03   ` Vladislav Shpilevoy
2020-06-09 11:43     ` Mergen Imeev
2020-05-28 14:17 ` Mergen Imeev [this message]
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 4/6] sql: remove mem_apply_type() from OP_MustBeInt Mergen Imeev
2020-06-01 17:04   ` Vladislav Shpilevoy
2020-06-09 11:47     ` Mergen Imeev
2020-06-09 22:28       ` Vladislav Shpilevoy
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 5/6] sql: remove implicit cast from string for comparison Mergen Imeev
2020-06-01 17:04   ` Vladislav Shpilevoy
2020-06-09 11:51     ` Mergen Imeev
2020-06-09 22:29       ` Vladislav Shpilevoy
2020-05-28 14:17 ` [Tarantool-patches] [PATCH 6/6] sql: remove OP_ApplyType Mergen Imeev
2020-06-09 22:29   ` Vladislav Shpilevoy
2020-06-01 17:03 ` [Tarantool-patches] [PATCH 0/6] Remove implicit cast Vladislav Shpilevoy
2020-06-09 11:25   ` Mergen Imeev

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=a88a83ec1eb1fdd9e946e0b97f270f206e85fb3c.1590671266.git.imeevma@gmail.com \
    --to=imeevma@tarantool.org \
    --cc=tarantool-patches@dev.tarantool.org \
    --cc=tsafin@tarantool.org \
    --cc=v.shpilevoy@tarantool.org \
    --subject='Re: [Tarantool-patches] [PATCH 3/6] sql: replace ApplyType by CheckType for IN operator' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox