From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp32.i.mail.ru (smtp32.i.mail.ru [94.100.177.92]) (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 09D584696C0 for ; Thu, 28 May 2020 17:17:33 +0300 (MSK) From: Mergen Imeev Date: Thu, 28 May 2020 17:17:33 +0300 Message-Id: In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH 3/6] sql: replace ApplyType by CheckType for IN operator List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: v.shpilevoy@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 +--------------------------- 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( -- }) -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. 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 ]], { -- - 1, 'Type mismatch: can not convert hello to integer' + 1, 'Type mismatch: can not convert text to integer' -- }) @@ -356,7 +356,7 @@ test:do_catchsql_test( SELECT 'hello' NOT IN t1 ]], { -- - 1, 'Type mismatch: can not convert hello to integer' + 1, 'Type mismatch: can not convert text to integer' -- }) 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