From: imeevma@tarantool.org To: v.shpilevoy@tarantool.org, tsafin@tarantool.org, tarantool-patches@dev.tarantool.org Subject: [Tarantool-patches] [PATCH v2 4/7] sql: remove mem_apply_type() from OP_MustBeInt Date: Thu, 11 Jun 2020 15:54:13 +0300 [thread overview] Message-ID: <58ce0e0e58759fcd66786c7010cae687431771c4.1591878044.git.imeevma@gmail.com> (raw) In-Reply-To: <cover.1591878044.git.imeevma@gmail.com> Thank you for the review! My answers, diff and new patch below. On 10.06.2020 01:28, Vladislav Shpilevoy wrote: > Thanks for the patch! > > See 2 comments below. > >> diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua >> index 840b780a3..51e0ecb79 100755 >> --- a/test/sql-tap/join.test.lua >> +++ b/test/sql-tap/join.test.lua >> @@ -1034,7 +1034,6 @@ test:do_execsql_test( >> SELECT * FROM t1 NATURAL JOIN t2 >> ]], { >> -- <join-11.9> >> - "one", "1", "two", "2" >> -- </join-11.9> >> }) > > 1. Why does "join-11.10" still work? I see it started failing > in the next commits, but why not here? Because in the MustBeInt opcode, the result of executing the sql_check_types() function is not checked. The main functionality of the function there is to convert mem if necessary and possible. > >> diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua >> index cb5348ab4..854ed774f 100755 >> --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua >> +++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua >> @@ -189,7 +189,6 @@ test:do_execsql_test( >> SELECT x FROM t2 WHERE x IN ('1'); >> ]], { >> -- <3.3> >> - 1 >> -- </3.3> >> }) >> >> @@ -199,7 +198,6 @@ test:do_execsql_test( >> SELECT x FROM t2 WHERE x IN ('1'); >> ]], { >> -- <3.4> >> - 1 >> -- </3.4> >> }) >> >> @@ -229,7 +227,6 @@ test:do_execsql_test( >> SELECT x FROM t2 WHERE '1' IN (x); >> ]], { >> -- <3.7> >> - 1 >> -- </3.7> >> }) >> >> @@ -239,7 +236,6 @@ test:do_execsql_test( >> SELECT x FROM t2 WHERE '1' IN (x); >> ]], { >> -- <3.8> >> - 1 >> -- </3.8> > > 2. 3.7 and 3.8 are absolutely the same. Maybe better to drop > one of them. The same about 3.4 <-> 3.3. Removed two of them. However, in the next patch, I deleted the entire test file after I transferred several tests to another file. Diff: diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua index 854ed774f..ac89c7df2 100755 --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua +++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(49) +test:plan(47) --!./tcltestrunner.lua -- 2014 June 26 @@ -192,15 +192,6 @@ test:do_execsql_test( -- </3.3> }) -test:do_execsql_test( - 3.4, - [[ - SELECT x FROM t2 WHERE x IN ('1'); - ]], { - -- <3.4> - -- </3.4> - }) - test:do_execsql_test( 3.5, [[ @@ -230,15 +221,6 @@ test:do_execsql_test( -- </3.7> }) -test:do_execsql_test( - 3.8, - [[ - SELECT x FROM t2 WHERE '1' IN (x); - ]], { - -- <3.8> - -- </3.8> - }) - test:do_execsql_test( 4.1, [[ New patch: From 58ce0e0e58759fcd66786c7010cae687431771c4 Mon Sep 17 00:00:00 2001 From: Mergen Imeev <imeevma@gmail.com> Date: Thu, 28 May 2020 13:10:35 +0300 Subject: [PATCH] sql: remove mem_apply_type() from OP_MustBeInt This patch replaces mem_apply_type() with mem_check_types() in OP_MustBeInt, which allows to remove implicit cast in some places, for example, in the IN statement. Part of #4230 diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index eb5c89e9d..77b758833 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2096,7 +2096,7 @@ case OP_AddImm: { /* in1 */ case OP_MustBeInt: { /* jump, in1 */ pIn1 = &aMem[pOp->p1]; if ((pIn1->flags & (MEM_Int | MEM_UInt)) == 0) { - mem_apply_type(pIn1, FIELD_TYPE_INTEGER); + mem_check_types(pIn1, FIELD_TYPE_INTEGER); if ((pIn1->flags & (MEM_Int | MEM_UInt)) == 0) { if (pOp->p2==0) { diag_set(ClientError, ER_SQL_TYPE_MISMATCH, diff --git a/test/sql-tap/e_select1.test.lua b/test/sql-tap/e_select1.test.lua index 7673426f4..578620fca 100755 --- a/test/sql-tap/e_select1.test.lua +++ b/test/sql-tap/e_select1.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(510) +test:plan(509) --!./tcltestrunner.lua -- 2010 July 16 @@ -2150,7 +2150,6 @@ test:do_select_tests( {"2", "SELECT b FROM f1 ORDER BY a LIMIT 2+3 ", {"a", "b", "c", "d", "e"}}, {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') ", {"a", "b", "c", "d", "e"}}, {"4", "SELECT b FROM f1 ORDER BY a LIMIT 5.0 ", {"a", "b", "c", "d", "e"}}, - {"5", "SELECT b FROM f1 ORDER BY a LIMIT '5' ", {"a", "b", "c", "d", "e"}}, }) -- EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value @@ -2195,7 +2194,7 @@ test:do_select_tests( {"1", "SELECT b FROM f1 ORDER BY a LIMIT 0 ", {}}, {"2", "SELECT b FROM f1 ORDER BY a DESC LIMIT 4 ", {"z", "y", "x", "w"}}, {"3", "SELECT b FROM f1 ORDER BY a DESC LIMIT 8 ", {"z", "y", "x", "w", "v", "u", "t", "s"}}, - {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT '12' ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}}, + {"4", "SELECT b FROM f1 ORDER BY a DESC LIMIT 12 ", {"z", y, "x", "w", "v", "u", "t", "s", "r", "q", "p", "o"}}, }) -- EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return @@ -2240,10 +2239,10 @@ test:do_select_tests( {"1", "SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5", {"f", "g", "h", "i", "j", "k", "l", "m", "n", "o"}}, {"2", "SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10", {"k", "l", "m", "n", "o"}}, {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b='j') OFFSET (SELECT a FROM f1 WHERE b='b') ", {"c", "d", "e", "f", "g", "h", "i", "j", "k", "l"}}, - {"4", "SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 ", {"d", "e", "f", "g", "h"}}, - {"5", "SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 ", {"a", "b", "c", "d", "e"}}, + {"4", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 3.0 ", {"d", "e", "f", "g", "h"}}, + {"5", "SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 ", {"a", "b", "c", "d", "e"}}, {"6", "SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 ", {}}, - {"7", "SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' ", {"p", "q", "r"}}, + {"7", "SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET CAST('1'||'5' AS INTEGER) ", {"p", "q", "r"}}, }) -- EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than @@ -2279,10 +2278,10 @@ test:do_select_tests( {"1", "SELECT b FROM f1 ORDER BY a LIMIT 5, 10 ", {"f", "g", "h", "i", "j", "k", "l", "m", "n", "o"}}, {"2", "SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 ", {"k", "l", "m", "n", "o"}}, {"3", "SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j')", {"c", "d", "e", "f", "g", "h", "i", "j", "k", "l"}}, - {"4", "SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' ", {"d", "e", "f", "g", "h"}}, - {"5", "SELECT b FROM f1 ORDER BY a LIMIT 0, '5' ", {"a", "b", "c", "d", "e"}}, + {"4", "SELECT b FROM f1 ORDER BY a LIMIT 3.0, 5 ", {"d", "e", "f", "g", "h"}}, + {"5", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}}, {"6", "SELECT b FROM f1 ORDER BY a LIMIT 10, 0 ", {}}, - {"7", "SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 ", {"p", "q", "r"}}, + {"7", "SELECT b FROM f1 ORDER BY a LIMIT CAST('1'||'5' AS INTEGER), 3 ", {"p", "q", "r"}}, {"8", "SELECT b FROM f1 ORDER BY a LIMIT 20, 10 ", {"u", "v", "w", "x", "y", "z"}}, {"9", "SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 ", {4, 3, 2, 1}}, {"10", "SELECT b FROM f1 ORDER BY a LIMIT 0, 5 ", {"a", "b", "c", "d", "e"}}, diff --git a/test/sql-tap/in4.test.lua b/test/sql-tap/in4.test.lua index 8c6917379..33947d0ab 100755 --- a/test/sql-tap/in4.test.lua +++ b/test/sql-tap/in4.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(61) +test:plan(52) --!./tcltestrunner.lua -- 2008 September 1 @@ -140,7 +140,7 @@ test:do_execsql_test( test:do_execsql_test( "in4-2.7", [[ - SELECT b FROM t2 WHERE a IN ('1', '2') + SELECT b FROM t2 WHERE a IN (1, 2) ]], { -- <in4-2.7> "one", "two" @@ -153,7 +153,6 @@ test:do_execsql_test( SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') ]], { -- <in4-2.8> - "two" -- </in4-2.8> }) @@ -585,98 +584,6 @@ test:do_execsql_test( -- </in4-4.6> }) -test:do_execsql_test( - "in4-4.11", - [[ - CREATE TABLE t4b(a TEXT, b NUMBER, c INT PRIMARY KEY); - INSERT INTO t4b VALUES('1.0',1,4); - SELECT c FROM t4b WHERE a=b; - ]], { - -- <in4-4.11> - 4 - -- </in4-4.11> - }) - -test:do_execsql_test( - "in4-4.12", - [[ - SELECT c FROM t4b WHERE b=a; - ]], { - -- <in4-4.12> - 4 - -- </in4-4.12> - }) - -test:do_execsql_test( - "in4-4.13", - [[ - SELECT c FROM t4b WHERE +a=b; - ]], { - -- <in4-4.13> - 4 - -- </in4-4.13> - }) - -test:do_execsql_test( - "in4-4.14", - [[ - SELECT c FROM t4b WHERE a=+b; - ]], { - -- <in4-4.14> - 4 - -- </in4-4.14> - }) - -test:do_execsql_test( - "in4-4.15", - [[ - SELECT c FROM t4b WHERE +b=a; - ]], { - -- <in4-4.15> - 4 - -- </in4-4.15> - }) - -test:do_execsql_test( - "in4-4.16", - [[ - SELECT c FROM t4b WHERE b=+a; - ]], { - -- <in4-4.16> - 4 - -- </in4-4.16> - }) - -test:do_execsql_test( - "in4-4.17", - [[ - SELECT c FROM t4b WHERE a IN (b); - ]], { - -- <in4-4.17> - 4 - -- </in4-4.17> - }) - -test:do_execsql_test( - "in4-4.18", - [[ - SELECT c FROM t4b WHERE b IN (a); - ]], { - -- <in4-4.18> - 4 - -- </in4-4.18> - }) - -test:do_execsql_test( - "in4-4.19", - [[ - SELECT c FROM t4b WHERE +b IN (a); - ]], { - -- <in4-4.19> - 4 - -- </in4-4.19> - }) - -- MUST_WORK_TEST -- Tarantool: TBI: Need to support collations. Depends on #2121 -- test:do_execsql_test( diff --git a/test/sql-tap/join.test.lua b/test/sql-tap/join.test.lua index 840b780a3..51e0ecb79 100755 --- a/test/sql-tap/join.test.lua +++ b/test/sql-tap/join.test.lua @@ -1034,7 +1034,6 @@ test:do_execsql_test( SELECT * FROM t1 NATURAL JOIN t2 ]], { -- <join-11.9> - "one", "1", "two", "2" -- </join-11.9> }) diff --git a/test/sql-tap/limit.test.lua b/test/sql-tap/limit.test.lua index 870233942..a7d1451f7 100755 --- a/test/sql-tap/limit.test.lua +++ b/test/sql-tap/limit.test.lua @@ -441,7 +441,7 @@ test:do_catchsql_test( test:do_execsql_test( "limit-6.5.2", [[ - SELECT * FROM t6 LIMIT '12' + SELECT * FROM t6 LIMIT 12 ]], { -- <limit-6.5> 1, 2, 3, 4 diff --git a/test/sql-tap/tkt-9a8b09f8e6.test.lua b/test/sql-tap/tkt-9a8b09f8e6.test.lua index cb5348ab4..ac89c7df2 100755 --- a/test/sql-tap/tkt-9a8b09f8e6.test.lua +++ b/test/sql-tap/tkt-9a8b09f8e6.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(49) +test:plan(47) --!./tcltestrunner.lua -- 2014 June 26 @@ -189,20 +189,9 @@ test:do_execsql_test( SELECT x FROM t2 WHERE x IN ('1'); ]], { -- <3.3> - 1 -- </3.3> }) -test:do_execsql_test( - 3.4, - [[ - SELECT x FROM t2 WHERE x IN ('1'); - ]], { - -- <3.4> - 1 - -- </3.4> - }) - test:do_execsql_test( 3.5, [[ @@ -229,20 +218,9 @@ test:do_execsql_test( SELECT x FROM t2 WHERE '1' IN (x); ]], { -- <3.7> - 1 -- </3.7> }) -test:do_execsql_test( - 3.8, - [[ - SELECT x FROM t2 WHERE '1' IN (x); - ]], { - -- <3.8> - 1 - -- </3.8> - }) - test:do_execsql_test( 4.1, [[ diff --git a/test/sql-tap/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua deleted file mode 100755 index fe5d6200f..000000000 --- a/test/sql-tap/tkt-fc7bd6358f.test.lua +++ /dev/null @@ -1,111 +0,0 @@ -#!/usr/bin/env tarantool -test = require("sqltester") -test:plan(50) - ---!./tcltestrunner.lua --- 2013 March 05 --- --- The author disclaims copyright to this source code. In place of --- a legal notice, here is a blessing: --- --- May you do good and not evil. --- May you find forgiveness for yourself and forgive others. --- May you share freely, never taking more than you give. --- -------------------------------------------------------------------------- --- This file implements regression tests for sql library. Specifically, --- it tests that ticket [fc7bd6358f]: --- --- The following SQL yields an incorrect result (zero rows) in all --- versions of sql between 3.6.14 and 3.7.15.2: --- --- CREATE TABLE t(textid TEXT); --- INSERT INTO t VALUES('12'); --- INSERT INTO t VALUES('34'); --- CREATE TABLE i(intid INTEGER PRIMARY KEY); --- INSERT INTO i VALUES(12); --- INSERT INTO i VALUES(34); --- --- SELECT t1.textid AS a, i.intid AS b, t2.textid AS c --- FROM t t1, i, t t2 --- WHERE t1.textid = i.intid --- AND t1.textid = t2.textid; --- --- The correct result should be two rows, one with 12|12|12 and the other --- with 34|34|34. With this bug, no rows are returned. Bisecting shows that --- this bug was introduced with check-in [dd4d67a67454] on 2009-04-23. --- --- ["set","testdir",[["file","dirname",["argv0"]]]] --- ["source",[["testdir"],"\/tester.tcl"]] -test:do_test( - "tkt-fc7bd6358f.100", - function() - return test:execsql [[ - CREATE TABLE t(textid TEXT PRIMARY KEY); - INSERT INTO t VALUES('12'); - INSERT INTO t VALUES('34'); - CREATE TABLE i(intid INTEGER PRIMARY KEY); - INSERT INTO i VALUES(12); - INSERT INTO i VALUES(34); - ]] - end, { - -- <tkt-fc7bd6358f.100> - - -- </tkt-fc7bd6358f.100> - }) - --- ["unset","-nocomplain","from"] --- ["unset","-nocomplain","where"] --- ["unset","-nocomplain","a"] --- ["unset","-nocomplain","b"] -local froms = { - "FROM t t1, i, t t2", - "FROM i, t t1, t t2", - "FROM t t1, t t2, i", -} -local wheres = { - "WHERE t1.textid=i.intid AND t1.textid=t2.textid", - "WHERE i.intid=t1.textid AND t1.textid=t2.textid", - "WHERE t1.textid=i.intid AND i.intid=t2.textid", - "WHERE t1.textid=i.intid AND t2.textid=i.intid", - "WHERE i.intid=t1.textid AND i.intid=t2.textid", - "WHERE i.intid=t1.textid AND t2.textid=i.intid", - "WHERE t1.textid=t2.textid AND i.intid=t2.textid", - "WHERE t1.textid=t2.textid AND t2.textid=i.intid", -} -for a, from in ipairs(froms) do - for b, where in ipairs(wheres) do - test:do_test( - string.format("tkt-fc7bd6358f.110.%s.%s.1", a, b), - function() - return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) - end, { - "12", 12, "12", "34", 34, "34" - }) - - test:do_test( - string.format("tkt-fc7bd6358f.110.%s.%s.2", a, b), - function() - return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) - end, { - "12", 12, "12", "34", 34, "34" - }) - - end -end - -test:do_test( - "tkt-fc7bd6358f.200", - function() - return test:execsql [[ - DROP TABLE t; - DROP TABLE i; - ]] - end, { - -- <tkt-fc7bd6358f.100> - - -- </tkt-fc7bd6358f.100> - }) - -test:finish_test() - diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua index e96056580..96895b4a7 100755 --- a/test/sql-tap/transitive1.test.lua +++ b/test/sql-tap/transitive1.test.lua @@ -338,7 +338,7 @@ test:do_execsql_test( ON tvshow.idshow = episode.idshow LEFT JOIN seasons ON seasons.idshow = episode.idshow - AND seasons.season = episode.c12 + AND seasons.season = CAST(episode.c12 AS INTEGER) JOIN path ON files.idpath = path.idpath LEFT JOIN bookmark @@ -378,7 +378,7 @@ test:do_execsql_test( FROM episodeview JOIN tvshowview ON tvshowview.idShow = episodeview.idShow JOIN seasons ON (seasons.idShow = tvshowview.idShow - AND seasons.season = episodeview.c12) + AND seasons.season = CAST(episodeview.c12 AS INTEGER)) JOIN files ON files.idFile = episodeview.idFile JOIN tvshowlinkpath ON tvshowlinkpath.idShow = tvshowview.idShow JOIN path ON path.idPath = tvshowlinkpath.idPath diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua deleted file mode 100755 index fe5e28c70..000000000 --- a/test/sql-tap/whereB.test.lua +++ /dev/null @@ -1,900 +0,0 @@ -#!/usr/bin/env tarantool -test = require("sqltester") -test:plan(63) - ---!./tcltestrunner.lua --- 2009 August 13 --- --- The author disclaims copyright to this source code. In place of --- a legal notice, here is a blessing: --- --- May you do good and not evil. --- May you find forgiveness for yourself and forgive others. --- May you share freely, never taking more than you give. --- -------------------------------------------------------------------------- --- This file implements regression tests for sql library. The --- focus of this file is testing WHERE clause conditions with --- subtle affinity issues. --- --- ["set","testdir",[["file","dirname",["argv0"]]]] --- ["source",[["testdir"],"\/tester.tcl"]] --- For this set of tests: --- --- * t1.y holds an integer value with affinity NONE --- * t2.b holds a text value with affinity TEXT --- --- These values are not equal and because neither affinity is NUMERIC --- no type conversion occurs. --- -test:do_execsql_test( - "whereB-1.1", - [[ - CREATE TABLE t1(x INT primary key,y INT ); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,99); - - CREATE TABLE t2(a INT primary key, b TEXT); -- affinity of t2.b is TEXT - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a; - ]], - { - -- <whereB-1.1> - 1, 2, true - -- </whereB-1.1> - }) - -test:do_execsql_test( - "whereB-1.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-1.2> - 1, 2, true - -- </whereB-1.2> - }) - -test:do_execsql_test( - "whereB-1.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-1.3> - 1, 2, true - -- </whereB-1.3> - }) - -test:do_execsql_test( - "whereB-1.4", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-1.4> - 1, 2, true - -- </whereB-1.4> - }) - -test:do_execsql_test( - "whereB-1.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-1.100> - 1, 2, true - -- </whereB-1.100> - }) - -test:do_execsql_test( - "whereB-1.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-1.101> - 1, 2, true - -- </whereB-1.101> - }) - -test:do_execsql_test( - "whereB-1.102", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-1.102> - 1, 2, true - -- </whereB-1.102> - }) - -test:do_execsql_test( - "whereB-2.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y TEXT); - INSERT INTO t1 VALUES(1,'99'); - - CREATE TABLE t2(a INT primary key, b SCALAR); - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2, 99); - - SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a; - ]], - { - -- <whereB-2.1> - 1, 2, false - -- </whereB-2.1> - }) - -test:do_execsql_test( - "whereB-2.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-2.2> - - -- </whereB-2.2> - }) - -test:do_execsql_test( - "whereB-2.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-2.3> - - -- </whereB-2.3> - }) - -test:do_execsql_test( - "whereB-2.4", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-2.4> - - -- </whereB-2.4> - }) - -test:do_execsql_test( - "whereB-2.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-2.100> - - -- </whereB-2.100> - }) - -test:do_execsql_test( - "whereB-2.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-2.101> - - -- </whereB-2.101> - }) - -test:do_execsql_test( - "whereB-2.102", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-2.102> - - -- </whereB-2.102> - }) - --- For this set of tests: --- --- * t1.y holds a text value with affinity NONE --- * t2.b holds an integer value with affinity NONE --- --- These values are not equal and because neither affinity is NUMERIC --- no type conversion occurs. --- -test:do_execsql_test( - "whereB-3.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,99); - - CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- <whereB-3.1> - 1, 2, false - -- </whereB-3.1> - }) - -test:do_execsql_test( - "whereB-3.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-3.2> - - -- </whereB-3.2> - }) - -test:do_execsql_test( - "whereB-3.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-3.3> - - -- </whereB-3.3> - }) - -test:do_execsql_test( - "whereB-3.4", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-3.4> - - -- </whereB-3.4> - }) - -test:do_execsql_test( - "whereB-3.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-3.100> - - -- </whereB-3.100> - }) - -test:do_execsql_test( - "whereB-3.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-3.101> - - -- </whereB-3.101> - }) - -test:do_execsql_test( - "whereB-3.102", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-3.102> - - -- </whereB-3.102> - }) - --- For this set of tests: --- --- * t1.y holds a text value with affinity NONE --- * t2.b holds an integer value with affinity NUMERIC --- --- Because t2.b has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-4.1", - [[ - DROP TABLE IF EXISTS t1; - DROP TABLE IF EXISTS t2; - - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,'99'); - - CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is NUMERIC - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,99); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- <whereB-4.1> - 1, 2, true - -- </whereB-4.1> - }) - -test:do_execsql_test( - "whereB-4.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-4.2> - 1, 2, true - -- </whereB-4.2> - }) - -test:do_execsql_test( - "whereB-4.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-4.3> - 1, 2, true - -- </whereB-4.3> - }) - -test:do_execsql_test( - "whereB-4.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-4.4> - 1, 2, true - -- </whereB-4.4> - }) - -test:do_execsql_test( - "whereB-4.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-4.100> - 1, 2, true - -- </whereB-4.100> - }) - -test:do_execsql_test( - "whereB-4.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-4.101> - 1, 2, true - -- </whereB-4.101> - }) - -test:do_execsql_test( - "whereB-4.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-4.102> - 1, 2, true - -- </whereB-4.102> - }) - --- For this set of tests: --- --- * t1.y holds a text value with affinity NONE --- * t2.b holds an integer value with affinity INTEGER --- --- Because t2.b has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-5.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,'99'); - - CREATE TABLE t2(a INT primary key, b INT); -- affinity of t2.b is INTEGER - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,99); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- <whereB-5.1> - 1, 2, true - -- </whereB-5.1> - }) - -test:do_execsql_test( - "whereB-5.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-5.2> - 1, 2, true - -- </whereB-5.2> - }) - -test:do_execsql_test( - "whereB-5.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-5.3> - 1, 2, true - -- </whereB-5.3> - }) - -test:do_execsql_test( - "whereB-5.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-5.4> - 1, 2, true - -- </whereB-5.4> - }) - -test:do_execsql_test( - "whereB-5.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-5.100> - 1, 2, true - -- </whereB-5.100> - }) - -test:do_execsql_test( - "whereB-5.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-5.101> - 1, 2, true - -- </whereB-5.101> - }) - -test:do_execsql_test( - "whereB-5.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-5.102> - 1, 2, true - -- </whereB-5.102> - }) - --- For this set of tests: --- --- * t1.y holds a text value with affinity NONE --- * t2.b holds an integer value with affinity REAL --- --- Because t2.b has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-6.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y SCALAR); -- affinity of t1.y is NONE - INSERT INTO t1 VALUES(1,'99'); - - CREATE TABLE t2(a INT primary key, b NUMBER); -- affinity of t2.b is REAL - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,99.0); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- <whereB-6.1> - 1, 2, true - -- </whereB-6.1> - }) - -test:do_execsql_test( - "whereB-6.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-6.2> - 1, 2, true - -- </whereB-6.2> - }) - -test:do_execsql_test( - "whereB-6.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-6.3> - 1, 2, true - -- </whereB-6.3> - }) - -test:do_execsql_test( - "whereB-6.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-6.4> - 1, 2, true - -- </whereB-6.4> - }) - -test:do_execsql_test( - "whereB-6.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-6.100> - 1, 2, true - -- </whereB-6.100> - }) - -test:do_execsql_test( - "whereB-6.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-6.101> - 1, 2, true - -- </whereB-6.101> - }) - -test:do_execsql_test( - "whereB-6.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-6.102> - 1, 2, true - -- </whereB-6.102> - }) - --- For this set of tests: --- --- * t1.y holds an integer value with affinity NUMERIC --- * t2.b holds a text value with affinity NONE --- --- Because t1.y has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-7.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y NUMBER); -- affinity of t1.y is NUMERIC - INSERT INTO t1 VALUES(1,99); - - CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- <whereB-7.1> - 1, 2, true - -- </whereB-7.1> - }) - -test:do_execsql_test( - "whereB-7.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-7.2> - 1, 2, true - -- </whereB-7.2> - }) - -test:do_execsql_test( - "whereB-7.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-7.3> - 1, 2, true - -- </whereB-7.3> - }) - -test:do_execsql_test( - "whereB-7.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-7.4> - 1, 2, true - -- </whereB-7.4> - }) - -test:do_execsql_test( - "whereB-7.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-7.100> - 1, 2, true - -- </whereB-7.100> - }) - -test:do_execsql_test( - "whereB-7.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-7.101> - 1, 2, true - -- </whereB-7.101> - }) - -test:do_execsql_test( - "whereB-7.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-7.102> - 1, 2, true - -- </whereB-7.102> - }) - --- For this set of tests: --- --- * t1.y holds an integer value with affinity INTEGER --- * t2.b holds a text value with affinity NONE --- --- Because t1.y has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-8.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y INT); -- affinity of t1.y is INTEGER - INSERT INTO t1 VALUES(1,99); - - CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- <whereB-8.1> - 1, 2, true - -- </whereB-8.1> - }) - -test:do_execsql_test( - "whereB-8.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-8.2> - 1, 2, true - -- </whereB-8.2> - }) - -test:do_execsql_test( - "whereB-8.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-8.3> - 1, 2, true - -- </whereB-8.3> - }) - -test:do_execsql_test( - "whereB-8.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-8.4> - 1, 2, true - -- </whereB-8.4> - }) - -test:do_execsql_test( - "whereB-8.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-8.100> - 1, 2, true - -- </whereB-8.100> - }) - -test:do_execsql_test( - "whereB-8.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-8.101> - 1, 2, true - -- </whereB-8.101> - }) - -test:do_execsql_test( - "whereB-8.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-8.102> - 1, 2, true - -- </whereB-8.102> - }) - --- For this set of tests: --- --- * t1.y holds an integer value with affinity REAL --- * t2.b holds a text value with affinity NONE --- --- Because t1.y has a numeric affinity, type conversion should occur --- and the two fields should be equal. --- -test:do_execsql_test( - "whereB-9.1", - [[ - DROP TABLE t1; - DROP TABLE t2; - - CREATE TABLE t1(x INT primary key, y NUMBER); -- affinity of t1.y is REAL - INSERT INTO t1 VALUES(1,99.0); - - CREATE TABLE t2(a INT primary key, b SCALAR); -- affinity of t2.b is NONE - CREATE INDEX t2b ON t2(b); - INSERT INTO t2 VALUES(2,'99'); - - SELECT x, a, y=b FROM t1, t2; - ]], - { - -- <whereB-9.1> - 1, 2, true - -- </whereB-9.1> - }) - -test:do_execsql_test( - "whereB-9.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-9.2> - 1, 2, true - -- </whereB-9.2> - }) - -test:do_execsql_test( - "whereB-9.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-9.3> - 1, 2, true - -- </whereB-9.3> - }) - -test:do_execsql_test( - "whereB-9.4", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-9.4> - 1, 2, true - -- </whereB-9.4> - }) - -test:do_execsql_test( - "whereB-9.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- <whereB-9.100> - 1, 2, true - -- </whereB-9.100> - }) - -test:do_execsql_test( - "whereB-9.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- <whereB-9.101> - 1, 2, true - -- </whereB-9.101> - }) - -test:do_execsql_test( - "whereB-9.102", - -- In this case the unary "+" operator shouldn't - -- affect result set of query. - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- <whereB-9.102> - 1, 2, true - -- </whereB-9.102> - }) - -test:finish_test() - diff --git a/test/sql-tap/whereC.test.lua b/test/sql-tap/whereC.test.lua index 89459dee3..58c049553 100755 --- a/test/sql-tap/whereC.test.lua +++ b/test/sql-tap/whereC.test.lua @@ -55,9 +55,9 @@ test:do_execsql_test( test:test("main", function() local data = {{"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3", {4, 5}}, -- {"SELECT i FROM t1 WHERE rowid='12'", {12}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2'", {3, 4, 5}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i>'3'", {4, 5}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<5", {3, 4}}, + {"SELECT i FROM t1 WHERE a=1 AND b=2", {3, 4, 5}}, + {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i>3", {4, 5}}, + {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i<5", {3, 4}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<12", {10, 11}}, {"SELECT i FROM t1 WHERE a IN(1, 2) AND b=2 AND i<11", {3, 4, 5, 10}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 10 AND 12", {10, 11, 12}}, @@ -66,7 +66,7 @@ test:test("main", function() {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i BETWEEN 12 AND 10", {}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i<NULL", {}}, {"SELECT i FROM t1 WHERE a=2 AND b=2 AND i>=NULL", {}}, - {"SELECT i FROM t1 WHERE a=1 AND b='2' AND i<4.5", {3, 4}}} + {"SELECT i FROM t1 WHERE a=1 AND b=2 AND i<4.5", {3, 4}}} -- {"SELECT i FROM t1 WHERE rowid IS '12'", {12}}} for tn, t in ipairs(data) do
next prev parent reply other threads:[~2020-06-11 12:54 UTC|newest] Thread overview: 11+ messages / expand[flat|nested] mbox.gz Atom feed top 2020-06-11 12:54 [Tarantool-patches] [PATCH v2 0/7] Remove implicit cast imeevma 2020-06-11 12:54 ` [Tarantool-patches] [PATCH v2 1/7] sql: remove implicit cast for assignment imeevma 2020-06-11 12:54 ` [Tarantool-patches] [PATCH v2 2/7] sql: remove mem_apply_type() from OP_MakeRecord imeevma 2020-06-11 12:54 ` [Tarantool-patches] [PATCH v2 3/7] sql: replace ApplyType by CheckType for IN operator imeevma 2020-06-11 12:54 ` imeevma [this message] 2020-06-11 12:54 ` [Tarantool-patches] [PATCH v2 5/7] sql: remove implicit cast from string for comparison imeevma 2020-06-11 12:54 ` [Tarantool-patches] [PATCH v2 6/7] sql: remove OP_ApplyType imeevma 2020-06-11 12:54 ` [Tarantool-patches] [PATCH v2 7/7] sql: use type instead of value in type mismatch error imeevma 2020-06-14 17:03 ` Vladislav Shpilevoy 2020-06-17 12:36 [Tarantool-patches] [PATCH v2 0/7] Remove implicit cast imeevma 2020-06-17 12:36 ` [Tarantool-patches] [PATCH v2 4/7] sql: remove mem_apply_type() from OP_MustBeInt imeevma 2020-06-22 10:07 ` Nikita Pettik
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=58ce0e0e58759fcd66786c7010cae687431771c4.1591878044.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 v2 4/7] sql: remove mem_apply_type() from OP_MustBeInt' \ /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