From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (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 0D45942EF5C for ; Thu, 25 Jun 2020 18:17:45 +0300 (MSK) From: imeevma@tarantool.org Date: Thu, 25 Jun 2020 18:17:44 +0300 Message-Id: <010e6a896440f240f504daf8c72b71095a17869b.1593096639.git.imeevma@gmail.com> In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Subject: [Tarantool-patches] [PATCH v3 5/8] sql: remove mem_apply_type() from OP_MustBeInt 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 replaces mem_apply_type() with mem_convert_to_integer(), since this mem_convert_to_integer() contains the only functionality needed here from the mem_apply_type(). --- src/box/sql/vdbe.c | 18 +- test/sql-tap/e_select1.test.lua | 17 +- test/sql-tap/in4.test.lua | 97 +-- test/sql-tap/join.test.lua | 1 - test/sql-tap/limit.test.lua | 2 +- test/sql-tap/tkt-9a8b09f8e6.test.lua | 24 +- test/sql-tap/tkt-fc7bd6358f.test.lua | 111 ---- test/sql-tap/transitive1.test.lua | 4 +- test/sql-tap/whereB.test.lua | 900 --------------------------- test/sql-tap/whereC.test.lua | 8 +- 10 files changed, 25 insertions(+), 1157 deletions(-) delete mode 100755 test/sql-tap/tkt-fc7bd6358f.test.lua delete mode 100755 test/sql-tap/whereB.test.lua diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c index 276956170..a609fa985 100644 --- a/src/box/sql/vdbe.c +++ b/src/box/sql/vdbe.c @@ -2122,17 +2122,13 @@ 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); - if ((pIn1->flags & (MEM_Int | MEM_UInt)) == 0) { - if (pOp->p2==0) { - diag_set(ClientError, ER_SQL_TYPE_MISMATCH, - sql_value_to_diag_str(pIn1), "integer"); - goto abort_due_to_error; - } else { - goto jump_to_p2; - } - } + if ((pIn1->flags & (MEM_Int | MEM_UInt)) == 0 && + mem_convert_to_integer(pIn1, true) != 0) { + if (pOp->p2 != 0) + goto jump_to_p2; + diag_set(ClientError, ER_SQL_TYPE_MISMATCH, + sql_value_to_diag_str(pIn1), "integer"); + goto abort_due_to_error; } break; } 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) ]], { -- "one", "two" @@ -153,7 +153,6 @@ test:do_execsql_test( SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') ]], { -- - "two" -- }) @@ -585,98 +584,6 @@ test:do_execsql_test( -- }) -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; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.12", - [[ - SELECT c FROM t4b WHERE b=a; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.13", - [[ - SELECT c FROM t4b WHERE +a=b; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.14", - [[ - SELECT c FROM t4b WHERE a=+b; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.15", - [[ - SELECT c FROM t4b WHERE +b=a; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.16", - [[ - SELECT c FROM t4b WHERE b=+a; - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.17", - [[ - SELECT c FROM t4b WHERE a IN (b); - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.18", - [[ - SELECT c FROM t4b WHERE b IN (a); - ]], { - -- - 4 - -- - }) - -test:do_execsql_test( - "in4-4.19", - [[ - SELECT c FROM t4b WHERE +b IN (a); - ]], { - -- - 4 - -- - }) - -- 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 ]], { -- - "one", "1", "two", "2" -- }) 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 ]], { -- 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 -- }) -test:do_execsql_test( - 3.4, - [[ - SELECT x FROM t2 WHERE x IN ('1'); - ]], { - -- <3.4> - 1 - -- - }) - 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 -- }) -test:do_execsql_test( - 3.8, - [[ - SELECT x FROM t2 WHERE '1' IN (x); - ]], { - -- <3.8> - 1 - -- - }) - 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, { - -- - - -- - }) - --- ["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, { - -- - - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.4", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-1.102", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, false - -- - }) - -test:do_execsql_test( - "whereB-2.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.4", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-2.102", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - - -- - }) - --- 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; - ]], - { - -- - 1, 2, false - -- - }) - -test:do_execsql_test( - "whereB-3.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.4", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - - -- - }) - -test:do_execsql_test( - "whereB-3.102", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; - ]], - { - -- - - -- - }) - --- 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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-4.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - --- 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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-5.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - --- 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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-6.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - --- 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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-7.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - --- 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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-8.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - --- 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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.2", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.3", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.100", - [[ - DROP INDEX t2b ON t2; - SELECT x, a, y=b FROM t1, t2 WHERE y=b; - ]], - { - -- - 1, 2, true - -- - }) - -test:do_execsql_test( - "whereB-9.101", - [[ - SELECT x, a, y=b FROM t1, t2 WHERE b=y; - ]], - { - -- - 1, 2, true - -- - }) - -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; - ]], - { - -- - 1, 2, true - -- - }) - -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=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 -- 2.25.1