[Tarantool-patches] [PATCH 3/6] sql: replace ApplyType by CheckType for IN operator
Mergen Imeev
imeevma at tarantool.org
Thu May 28 17:17:33 MSK 2020
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
More information about the Tarantool-patches
mailing list