From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp39.i.mail.ru (smtp39.i.mail.ru [94.100.177.99]) (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 9B7CA469710 for ; Tue, 9 Jun 2020 14:47:32 +0300 (MSK) References: <40caa8fdc66d3d20aca72677e10014e749172f92.1590671266.git.imeevma@gmail.com> <1fdaea95-07d2-72ac-82bc-24e0c8fa4ed0@tarantool.org> From: Mergen Imeev Message-ID: Date: Tue, 9 Jun 2020 14:47:31 +0300 MIME-Version: 1.0 In-Reply-To: <1fdaea95-07d2-72ac-82bc-24e0c8fa4ed0@tarantool.org> Content-Type: text/plain; charset="utf-8"; format="flowed" Content-Transfer-Encoding: 8bit Content-Language: en-US Subject: Re: [Tarantool-patches] [PATCH 4/6] sql: remove mem_apply_type() from OP_MustBeInt List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: Vladislav Shpilevoy , tsafin@tarantool.org, tarantool-patches@dev.tarantool.org Thanks for the review! My answers and new patch below. I removed the tests tkt-fc7bd6358f.test.lua and whereB.test.lua (from comments 2. and 3.), because they do not make sense now. On 01.06.2020 20:04, Vladislav Shpilevoy wrote: > Thanks for the patch! > > See 3 comments below. > > On 28/05/2020 16:17, Mergen Imeev wrote: >> This patch replaces mem_apply_type() by mem_check_types() in >> OP_MustBeInt, which allows to remove implicit case in some places, > 1. 'case' -> 'cast'. Fixed. > >> for example in IN operator. >> >> Part of #4230 >> --- >> diff --git a/test/sql-tap/tkt-fc7bd6358f.test.lua b/test/sql-tap/tkt-fc7bd6358f.test.lua >> index fe5d6200f..f38ffa3d6 100755 >> --- a/test/sql-tap/tkt-fc7bd6358f.test.lua >> +++ b/test/sql-tap/tkt-fc7bd6358f.test.lua >> @@ -80,7 +80,6 @@ for a, from in ipairs(froms) do >> 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( >> @@ -88,7 +87,6 @@ for a, from in ipairs(froms) do >> function() >> return test:execsql(string.format("SELECT t1.textid, i.intid, t2.textid %s %s", from, where)) >> end, { >> - "12", 12, "12", "34", 34, "34" >> }) > 2. In the header of this file it is said, that the whole test's > purpose is to ensure, that these values are returned. Do we > need this test file now at all? Or can it be fixed in a way, that > the results are not changed? > >> >> end >> diff --git a/test/sql-tap/whereB.test.lua b/test/sql-tap/whereB.test.lua >> index fe5e28c70..970ff1dec 100755 >> --- a/test/sql-tap/whereB.test.lua >> +++ b/test/sql-tap/whereB.test.lua >> @@ -432,7 +432,6 @@ test:do_execsql_test( >> ]], >> { >> -- >> - 1, 2, true >> -- >> }) >> >> @@ -443,7 +442,6 @@ test:do_execsql_test( >> ]], >> { >> -- >> - 1, 2, true > 3. These tests also look useless now. Their comment > says: > > -- Because t2.b has a numeric affinity, type conversion should occur > -- and the two fields should be equal. > > And now they are not equal. Do we need these tests? New patch: From c5478fc613301127090c8439e78eff7d76ca9e1e Mon Sep 17 00:00:00 2001 From: Mergen Imeev 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)      ]], {          --          "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..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          --      }) @@ -199,7 +198,6 @@ test:do_execsql_test(          SELECT x FROM t2 WHERE x IN ('1');      ]], {          -- <3.4> -        1          --      }) @@ -229,7 +227,6 @@ test:do_execsql_test(          SELECT x FROM t2 WHERE '1' IN (x);      ]], {          -- <3.7> -        1          --      }) @@ -239,7 +236,6 @@ test:do_execsql_test(          SELECT x FROM t2 WHERE '1' IN (x);      ]], {          -- <3.8> -        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