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 3878442F4AE for ; Thu, 25 Jun 2020 18:17:44 +0300 (MSK) From: imeevma@tarantool.org Date: Thu, 25 Jun 2020 18:17:43 +0300 Message-Id: In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v3 4/8] sql: replace ApplyType by CheckType for IN operator 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 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 +--------------------------------- 3 files changed, 3 insertions(+), 82 deletions(-) diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c index 019628a26..745011d35 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_ImplicitCast, 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( -- }) -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/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. -- 2.25.1