From aa5528cfdd54def0a23c6fd8223d283fc834a1d6 Mon Sep 17 00:00:00 2001 Message-Id: From: Mergen Imeev Date: Wed, 16 May 2018 16:39:26 +0300 Subject: [PATCH 1/1] sql: IS is only applicable when dealing with NULL According to ANSI Standard IS/IS NOT can be used to determine if values is null. At the same time in SQLite3 IS/IS NOT have an additional function - it can be used to check equality of two values. This patch removes that additional function. Closes #2136 --- Branch: https://github.com/tarantool/tarantool/tree/gh-2136-behaviour-of-IS-corrected Issue: https://github.com/tarantool/tarantool/issues/2136  src/box/sql/parse.y               |  30 +---  test/sql-tap/e_expr.test.lua      | 366 ++++++++++++++------------------------  test/sql-tap/lua_sql.test.lua     |  12 +-  test/sql-tap/null.test.lua        |  89 ++++++++-  test/sql-tap/subselect.test.lua   |   2 +-  test/sql-tap/transitive1.test.lua |  45 +----  test/sql-tap/types.test.lua       |   2 +-  test/sql-tap/types2.test.lua      |   2 +-  8 files changed, 229 insertions(+), 319 deletions(-) diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index 872647d..4ad5195 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -1032,36 +1032,10 @@ expr(A) ::= expr(A) likeop(OP) expr(Y) ESCAPE expr(E).  [LIKE_KW]  {    }                             }   -expr(A) ::= expr(A) ISNULL|NOTNULL(E).   {spanUnaryPostfix(pParse,@E,&A,&E);} +expr(A) ::= expr(A) IS NULL(E).   {spanUnaryPostfix(pParse,TK_ISNULL,&A,&E);} +expr(A) ::= expr(A) IS NOT NULL(E).   {spanUnaryPostfix(pParse,TK_NOTNULL,&A,&E);}  expr(A) ::= expr(A) NOT NULL(E). {spanUnaryPostfix(pParse,TK_NOTNULL,&A,&E);}   -%include { -  /* A routine to convert a binary TK_IS or TK_ISNOT expression into a -  ** unary TK_ISNULL or TK_NOTNULL expression. */ -  static void binaryToUnaryIfNull(Parse *pParse, Expr *pY, Expr *pA, int op){ -    sqlite3 *db = pParse->db; -    if( pA && pY && pY->op==TK_NULL ){ -      pA->op = (u8)op; -      sql_expr_free(db, pA->pRight, false); -      pA->pRight = 0; -    } -  } -} - -//    expr1 IS expr2 -//    expr1 IS NOT expr2 -// -// If expr2 is NULL then code as TK_ISNULL or TK_NOTNULL.  If expr2 -// is any other expression, code as TK_IS or TK_ISNOT. -// -expr(A) ::= expr(A) IS expr(Y).     { -  spanBinaryExpr(pParse,TK_IS,&A,&Y); -  binaryToUnaryIfNull(pParse, Y.pExpr, A.pExpr, TK_ISNULL); -} -expr(A) ::= expr(A) IS NOT expr(Y). { -  spanBinaryExpr(pParse,TK_ISNOT,&A,&Y); -  binaryToUnaryIfNull(pParse, Y.pExpr, A.pExpr, TK_NOTNULL); -}    %include {    /* Construct an expression node for a unary prefix operator diff --git a/test/sql-tap/e_expr.test.lua b/test/sql-tap/e_expr.test.lua index d0f6895..d378222 100755 --- a/test/sql-tap/e_expr.test.lua +++ b/test/sql-tap/e_expr.test.lua @@ -1,6 +1,6 @@  #!/usr/bin/env tarantool  test = require("sqltester") -test:plan(14750) +test:plan(12436)    --!./tcltestrunner.lua  -- 2010 July 16 @@ -96,7 +96,7 @@ operations = {      {"+", "-"},      {"<<", ">>", "&", "|"},      {"<", "<=", ">", ">="}, -    {"=", "==", "!=", "<>", "IS", "IS NOT", "LIKE", "GLOB"}, --"MATCH", "REGEXP"}, +    {"=", "==", "!=", "<>", "LIKE", "GLOB"}, --"MATCH", "REGEXP"},      {"AND"},      {"OR"},  } @@ -512,25 +512,27 @@ test:do_execsql_test(          --      })   -test:do_execsql_test( -    "e_expr-8.1.3", -    [[ -        SELECT NULL IS     'ab' -    ]], { -        -- -        0 -        -- -    }) - -test:do_execsql_test( -    "e_expr-8.1.4", -    [[ -        SELECT 'ab' IS     'ab' -    ]], { -        -- -        1 -        -- -    }) +-- gh-2136: According to ANSI SQL IS can be used only in IS NULL or IS NOT NULL +-- test:do_execsql_test( +--     "e_expr-8.1.3", +--     [[ +--         SELECT NULL IS     'ab' +--     ]], { +--         -- +--         0 +--         -- +--     }) + +-- gh-2136: According to ANSI SQL IS can be used only in IS NULL or IS NOT NULL +-- test:do_execsql_test( +--     "e_expr-8.1.4", +--     [[ +--         SELECT 'ab' IS     'ab' +--     ]], { +--         -- +--         1 +--         -- +--     })    test:do_execsql_test(      "e_expr-8.1.5", @@ -592,25 +594,27 @@ test:do_execsql_test(          --      })   -test:do_execsql_test( -    "e_expr-8.1.11", -    [[ -        SELECT NULL IS NOT 'ab' -    ]], { -        -- -        1 -        -- -    }) - -test:do_execsql_test( -    "e_expr-8.1.12", -    [[ -        SELECT 'ab' IS NOT 'ab' -    ]], { -        -- -        0 -        -- -    }) +-- gh-2136: According to ANSI SQL IS can be used only in IS NULL or IS NOT NULL +-- test:do_execsql_test( +--     "e_expr-8.1.11", +--     [[ +--         SELECT NULL IS NOT 'ab' +--     ]], { +--         -- +--         1 +--         -- +--     }) + +-- gh-2136: According to ANSI SQL IS can be used only in IS NULL or IS NOT NULL +-- test:do_execsql_test( +--     "e_expr-8.1.12", +--     [[ +--         SELECT 'ab' IS NOT 'ab' +--     ]], { +--         -- +--         0 +--         -- +--     })    test:do_execsql_test(      "e_expr-8.1.13", @@ -652,31 +656,6 @@ test:do_execsql_test(          --      })   -for n1, rhs in ipairs(literals) do -    for n2, lhs in ipairs(literals) do -        local eq = "" -        if ((rhs ~= "NULL") and (lhs ~= "NULL")) then -            eq = test:execsql(string.format("SELECT %s = %s, %s != %s", lhs, rhs, lhs, rhs)) -        else -            eq = { ((lhs == "NULL") and (rhs == "NULL")) and 1 or 0, ((lhs ~= "NULL") or (rhs ~= "NULL")) and 1 or 0} -        end -        local label = string.format("e_expr-8.2.%s.%s", n1, n2) -        test:do_execsql_test( -            label..".1", -            string.format([[ -                SELECT %s IS %s, %s IS NOT %s -            ]], lhs, rhs, lhs, rhs), eq) - -        test:do_execsql_test( -            label..".2", -            string.format([[ -                SELECT (%s IS %s) IS NULL, (%s IS NOT %s) IS NULL -            ]], lhs, rhs, lhs, rhs), { -                0, 0 -            }) - -    end -end  ---------------------------------------------------------------------------  -- Run some tests on the COLLATE "unary postfix operator".  -- @@ -829,28 +808,9 @@ test:do_execsql_test(          --      })   -test:do_execsql_test( -    "e_expr-9.14", -    [[ -        SELECT  'abcd' IS 'ABCD'  COLLATE "unicode_ci" -    ]], { -        -- -        1 -        -- -    }) - -test:do_execsql_test( -    "e_expr-9.15", -    [[ -        SELECT ('abcd' IS 'ABCD') COLLATE "unicode_ci" -    ]], { -        -- -        0 -        -- -    })    test:do_execsql_test( -    "e_expr-9.16", +    "e_expr-9.14",      [[          SELECT  'abcd' != 'ABCD'      COLLATE "unicode_ci"      ]], { @@ -860,7 +820,7 @@ test:do_execsql_test(      })    test:do_execsql_test( -    "e_expr-9.17", +    "e_expr-9.15",      [[          SELECT ('abcd' != 'ABCD')     COLLATE "unicode_ci"      ]], { @@ -870,7 +830,7 @@ test:do_execsql_test(      })    test:do_execsql_test( -    "e_expr-9.18", +    "e_expr-9.16",      [[          SELECT  'abcd' <> 'ABCD'      COLLATE "unicode_ci"      ]], { @@ -880,7 +840,7 @@ test:do_execsql_test(      })    test:do_execsql_test( -    "e_expr-9.19", +    "e_expr-9.17",      [[          SELECT ('abcd' <> 'ABCD')     COLLATE "unicode_ci"      ]], { @@ -890,27 +850,7 @@ test:do_execsql_test(      })    test:do_execsql_test( -    "e_expr-9.20", -    [[ -        SELECT  'abcd' IS NOT 'ABCD'  COLLATE "unicode_ci" -    ]], { -        -- -        0 -        -- -    }) - -test:do_execsql_test( -    "e_expr-9.21", -    [[ -        SELECT ('abcd' IS NOT 'ABCD') COLLATE "unicode_ci" -    ]], { -        -- -        1 -        -- -    }) - -test:do_execsql_test( -    "e_expr-9.22", +    "e_expr-9.18",      [[          SELECT 'bbb' BETWEEN 'AAA' AND 'CCC' COLLATE "unicode_ci"      ]], { @@ -920,7 +860,7 @@ test:do_execsql_test(      })    test:do_execsql_test( -    "e_expr-9.23", +    "e_expr-9.19",      [[          SELECT ('bbb' BETWEEN 'AAA' AND 'CCC') COLLATE "unicode_ci"      ]], { @@ -1512,128 +1452,90 @@ local test_cases12 ={      {15, "cname"},      {16, "tblname.cname"},   -    {18, "+ EXPR"}, -    {19, "- EXPR"}, -    {20, "NOT EXPR"}, -    {21, "~ EXPR"}, - -    {22, "EXPR1 || EXPR2"}, -    {23, "EXPR1 * EXPR2"}, -    {24, "EXPR1 / EXPR2"}, -    {25, "EXPR1 % EXPR2"}, -    {26, "EXPR1 + EXPR2"}, -    {27, "EXPR1 - EXPR2"}, -    {28, "EXPR1 << EXPR2"}, -    {29, "EXPR1 >> EXPR2"}, -    {30, "EXPR1 & EXPR2"}, - -    {31, "EXPR1 | EXPR2"}, -    {32, "EXPR1 < EXPR2"}, -    {33, "EXPR1 <= EXPR2"}, -    {34, "EXPR1 > EXPR2"}, -    {35, "EXPR1 >= EXPR2"}, -    {36, "EXPR1 = EXPR2"}, -    {37, "EXPR1 == EXPR2"}, -    {38, "EXPR1 != EXPR2"}, -    {39, "EXPR1 <> EXPR2"}, -    {40, "EXPR1 IS EXPR2"}, -    {41, "EXPR1 IS NOT EXPR2"}, -    {42, "EXPR1 AND EXPR2"}, -    {43, "EXPR1 OR EXPR2"}, - -    {44, "count(*)"}, -    {45, "count(DISTINCT EXPR)"}, -    {46, "substr(EXPR, 10, 20)"}, -    {47, "changes()"}, - -    {48, "( EXPR )"}, - -    {49, "CAST ( EXPR AS integer )"}, -    {50, "CAST ( EXPR AS 'abcd' )"}, -    {51, "CAST ( EXPR AS 'ab$ $cd' )"}, - -    {52, "EXPR COLLATE \"unicode_ci\""}, -    {53, "EXPR COLLATE binary"}, - -    {54, "EXPR1 LIKE EXPR2"}, -    {55, "EXPR1 LIKE EXPR2 ESCAPE EXPR"}, -    {56, "EXPR1 GLOB EXPR2"}, -    {57, "EXPR1 GLOB EXPR2 ESCAPE EXPR"}, -    {58, "EXPR1 REGEXP EXPR2"}, -    {59, "EXPR1 REGEXP EXPR2 ESCAPE EXPR"}, -    {60, "EXPR1 MATCH EXPR2"}, -    {61, "EXPR1 MATCH EXPR2 ESCAPE EXPR"}, -    {62, "EXPR1 NOT LIKE EXPR2"}, -    {63, "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"}, -    {64, "EXPR1 NOT GLOB EXPR2"}, -    {65, "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"}, -    {66, "EXPR1 NOT REGEXP EXPR2"}, -    {67, "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"}, -    {68, "EXPR1 NOT MATCH EXPR2"}, -    {69, "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"}, - -    {70, "EXPR ISNULL"}, -    {71, "EXPR NOTNULL"}, -    {72, "EXPR NOT NULL"}, - -    {73, "EXPR1 IS EXPR2"}, -    {74, "EXPR1 IS NOT EXPR2"}, - -    {75, "EXPR NOT BETWEEN EXPR1 AND EXPR2"}, -    {76, "EXPR BETWEEN EXPR1 AND EXPR2"}, - -    {77, "EXPR NOT IN (SELECT cname FROM tblname)"}, -    {78, "EXPR NOT IN (1)"}, -    {79, "EXPR NOT IN (1, 2, 3)"}, -    {80, "EXPR NOT IN tblname"}, -    {82, "EXPR IN (SELECT cname FROM tblname)"}, -    {83, "EXPR IN (1)"}, -    {84, "EXPR IN (1, 2, 3)"}, -    {85, "EXPR IN tblname"}, -    {57, "EXPR1 GLOB EXPR2 ESCAPE EXPR"}, -    {58, "EXPR1 REGEXP EXPR2"}, -    {59, "EXPR1 REGEXP EXPR2 ESCAPE EXPR"}, -    {60, "EXPR1 MATCH EXPR2"}, -    {61, "EXPR1 MATCH EXPR2 ESCAPE EXPR"}, -    {62, "EXPR1 NOT LIKE EXPR2"}, -    {63, "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"}, -    {64, "EXPR1 NOT GLOB EXPR2"}, -    {65, "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"}, -    {66, "EXPR1 NOT REGEXP EXPR2"}, -    {67, "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"}, -    {68, "EXPR1 NOT MATCH EXPR2"}, -    {69, "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"}, - -    {70, "EXPR ISNULL"}, -    {71, "EXPR NOTNULL"}, -    {72, "EXPR NOT NULL"}, - -    {73, "EXPR1 IS EXPR2"}, -    {74, "EXPR1 IS NOT EXPR2"}, - -    {75, "EXPR NOT BETWEEN EXPR1 AND EXPR2"}, -    {76, "EXPR BETWEEN EXPR1 AND EXPR2"}, - -    {77, "EXPR NOT IN (SELECT cname FROM tblname)"}, -    {78, "EXPR NOT IN (1)"}, -    {79, "EXPR NOT IN (1, 2, 3)"}, -    {80, "EXPR NOT IN tblname"}, -    {82, "EXPR IN (SELECT cname FROM tblname)"}, -    {83, "EXPR IN (1)"}, -    {84, "EXPR IN (1, 2, 3)"}, -    {85, "EXPR IN tblname"}, - -    {87, "EXISTS (SELECT cname FROM tblname)"}, -    {88, "NOT EXISTS (SELECT cname FROM tblname)"}, - -    {89, "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"}, -    {90, "CASE EXPR WHEN EXPR1 THEN EXPR2 END"}, -    {91, "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"}, -    {92, "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"}, -    {93, "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"}, -    {94, "CASE WHEN EXPR1 THEN EXPR2 END"}, -    {95, "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"}, -    {96, "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"}, +    {17, "+ EXPR"}, +    {18, "- EXPR"}, +    {19, "NOT EXPR"}, +    {20, "~ EXPR"}, + +    {21, "EXPR1 || EXPR2"}, +    {22, "EXPR1 * EXPR2"}, +    {23, "EXPR1 / EXPR2"}, +    {24, "EXPR1 % EXPR2"}, +    {25, "EXPR1 + EXPR2"}, +    {26, "EXPR1 - EXPR2"}, +    {27, "EXPR1 << EXPR2"}, +    {28, "EXPR1 >> EXPR2"}, +    {29, "EXPR1 & EXPR2"}, + +    {30, "EXPR1 | EXPR2"}, +    {31, "EXPR1 < EXPR2"}, +    {32, "EXPR1 <= EXPR2"}, +    {33, "EXPR1 > EXPR2"}, +    {34, "EXPR1 >= EXPR2"}, +    {35, "EXPR1 = EXPR2"}, +    {36, "EXPR1 == EXPR2"}, +    {37, "EXPR1 != EXPR2"}, +    {38, "EXPR1 <> EXPR2"}, +    {39, "EXPR1 AND EXPR2"}, +    {40, "EXPR1 OR EXPR2"}, + +    {41, "count(*)"}, +    {42, "count(DISTINCT EXPR)"}, +    {43, "substr(EXPR, 10, 20)"}, +    {44, "changes()"}, + +    {45, "( EXPR )"}, + +    {46, "CAST ( EXPR AS integer )"}, +    {47, "CAST ( EXPR AS 'abcd' )"}, +    {48, "CAST ( EXPR AS 'ab$ $cd' )"}, + +    {49, "EXPR COLLATE \"unicode_ci\""}, +    {50, "EXPR COLLATE binary"}, + +    {51, "EXPR1 LIKE EXPR2"}, +    {52, "EXPR1 LIKE EXPR2 ESCAPE EXPR"}, +    {53, "EXPR1 GLOB EXPR2"}, +    {54, "EXPR1 GLOB EXPR2 ESCAPE EXPR"}, +    {55, "EXPR1 REGEXP EXPR2"}, +    {56, "EXPR1 REGEXP EXPR2 ESCAPE EXPR"}, +    {57, "EXPR1 MATCH EXPR2"}, +    {58, "EXPR1 MATCH EXPR2 ESCAPE EXPR"}, +    {59, "EXPR1 NOT LIKE EXPR2"}, +    {60, "EXPR1 NOT LIKE EXPR2 ESCAPE EXPR"}, +    {61, "EXPR1 NOT GLOB EXPR2"}, +    {62, "EXPR1 NOT GLOB EXPR2 ESCAPE EXPR"}, +    {63, "EXPR1 NOT REGEXP EXPR2"}, +    {64, "EXPR1 NOT REGEXP EXPR2 ESCAPE EXPR"}, +    {65, "EXPR1 NOT MATCH EXPR2"}, +    {66, "EXPR1 NOT MATCH EXPR2 ESCAPE EXPR"}, + +    {67, "EXPR IS NULL"}, +    {68, "EXPR IS NOT NULL"}, + +    {69, "EXPR NOT BETWEEN EXPR1 AND EXPR2"}, +    {70, "EXPR BETWEEN EXPR1 AND EXPR2"}, + +    {71, "EXPR NOT IN (SELECT cname FROM tblname)"}, +    {72, "EXPR NOT IN (1)"}, +    {73, "EXPR NOT IN (1, 2, 3)"}, +    {74, "EXPR NOT IN tblname"}, +    {75, "EXPR IN (SELECT cname FROM tblname)"}, +    {76, "EXPR IN (1)"}, +    {77, "EXPR IN (1, 2, 3)"}, +    {78, "EXPR IN tblname"}, + +    {79, "EXISTS (SELECT cname FROM tblname)"}, +    {80, "NOT EXISTS (SELECT cname FROM tblname)"}, + +    {81, "CASE EXPR WHEN EXPR1 THEN EXPR2 ELSE EXPR END"}, +    {82, "CASE EXPR WHEN EXPR1 THEN EXPR2 END"}, +    {83, "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"}, +    {84, "CASE EXPR WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"}, +    {85, "CASE WHEN EXPR1 THEN EXPR2 ELSE EXPR END"}, +    {86, "CASE WHEN EXPR1 THEN EXPR2 END"}, +    {87, "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 ELSE EXPR2 END"}, +    {88, "CASE WHEN EXPR1 THEN EXPR2 WHEN EXPR THEN EXPR1 END"},  }    for _, val in ipairs(test_cases12) do diff --git a/test/sql-tap/lua_sql.test.lua b/test/sql-tap/lua_sql.test.lua index 1444d21..394922a 100755 --- a/test/sql-tap/lua_sql.test.lua +++ b/test/sql-tap/lua_sql.test.lua @@ -1,7 +1,7 @@  #!/usr/bin/env tarantool  test = require("sqltester")  NULL = require('msgpack').NULL -test:plan(25) +test:plan(24)    local function func1(a)      return a @@ -118,16 +118,6 @@ for i = 1, #from_lua_to_sql, 1 do          {1})  end   -local function allways_nil() -    return nil -end -box.internal.sql_create_function("allways_nil", allways_nil) - -test:do_execsql_test( -    "lua_sql-2.4", -    "select NULL is allways_nil()", -    {1}) -  local from_lua_to_sql_bad = {      [1] = NULL,      [2] = 12LL, -- it is possible to support this type diff --git a/test/sql-tap/null.test.lua b/test/sql-tap/null.test.lua index ed9a1aa..ee30961 100755 --- a/test/sql-tap/null.test.lua +++ b/test/sql-tap/null.test.lua @@ -1,6 +1,6 @@  #!/usr/bin/env tarantool  test = require("sqltester") -test:plan(37) +test:plan(45)    --!./tcltestrunner.lua  -- 2001 September 15 @@ -470,4 +470,91 @@ test:do_execsql_test(  -- } {{} x two {} x ii}     +-- gh-2136: "IS" is only applicable when dealing with NULL + +test:do_execsql_test( +    "null-10.1", +    [[ +        SELECT 1 WHERE 1 IS NULL; +    ]], { +        -- + +        -- +    }) + +test:do_execsql_test( +    "null-10.2", +    [[ +        SELECT 1 WHERE 1 IS NOT NULL; +    ]], { +        -- +        1 +        -- +    }) + +test:do_execsql_test( +    "null-10.3", +    [[ +        SELECT 1 WHERE NULL IS NULL; +    ]], { +        -- +        1 +        -- +    }) + +test:do_execsql_test( +    "null-10.4", +    [[ +        SELECT 1 WHERE NULL IS NOT NULL; +    ]], { +        -- + +        -- +    }) + +test:do_catchsql_test( +    "null-10.5", +    [[ +        SELECT 1 WHERE 1 IS 1; +    ]], +    { +    -- +    1, "near \"1\": syntax error" +    -- +    }) + +test:do_catchsql_test( +    "null-10.6", +    [[ +        SELECT 1 WHERE 1 IS NOT 1; +    ]], +    { +    -- +    1, "near \"1\": syntax error" +    -- +    }) + +test:do_catchsql_test( +    "null-10.7", +    [[ +        SELECT 1 WHERE NULL IS 1; +    ]], +    { +    -- +    1, "near \"1\": syntax error" +    -- +    }) + +test:do_catchsql_test( +    "null-10.8", +    [[ +        SELECT 1 WHERE NULL IS NOT 1; +    ]], +    { +    -- +    1, "near \"1\": syntax error" +    -- +    }) + +  test:finish_test() diff --git a/test/sql-tap/subselect.test.lua b/test/sql-tap/subselect.test.lua index 36df9a1..809ec12 100755 --- a/test/sql-tap/subselect.test.lua +++ b/test/sql-tap/subselect.test.lua @@ -135,7 +135,7 @@ test:do_test(          ]]          return test:execsql [[              SELECT y from t2 -            WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1) +            WHERE x = (SELECT sum(b) FROM t1 where a is not null) - (SELECT sum(a) FROM t1)          ]]      end, {          -- diff --git a/test/sql-tap/transitive1.test.lua b/test/sql-tap/transitive1.test.lua index bdb9e97..ed3238f 100755 --- a/test/sql-tap/transitive1.test.lua +++ b/test/sql-tap/transitive1.test.lua @@ -1,6 +1,6 @@  #!/usr/bin/env tarantool  test = require("sqltester") -test:plan(30) +test:plan(26)    --!./tcltestrunner.lua  -- 2013 April 17 @@ -124,19 +124,6 @@ test:do_execsql_test(      })    test:do_execsql_test( -    "transitive1-302", -    [[ -        SELECT * -          FROM t301 CROSS JOIN t302 -         WHERE w IS y AND y IS NOT NULL -         ORDER BY w; -    ]], { -        -- -        1, 2, 1, 3, 3, 4, 3, 6, 5, 6, 5, 7 -        -- -    }) - -test:do_execsql_test(      "transitive1-310",      [[          SELECT * @@ -234,36 +221,6 @@ test:do_execsql_test(          --      })   -test:do_execsql_test( -    "transitive1-401", -    [[ -        SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c=a; -    ]], { -        -- -        "1-row" -        -- -    }) - -test:do_execsql_test( -    "transitive1-402", -    [[ -        SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c IS a; -    ]], { -        -- -        "1-row" -        -- -    }) - -test:do_execsql_test( -    "transitive1-403", -    [[ -        SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c IS a; -    ]], { -        -- -        "1-row" -        -- -    }) -  -- The following is a script distilled from the XBMC project where the  -- bug was originally encountered.  The correct answer is a single row  -- of output.  Before the bug was fixed, zero rows were generated. diff --git a/test/sql-tap/types.test.lua b/test/sql-tap/types.test.lua index c102efd..1da2512 100755 --- a/test/sql-tap/types.test.lua +++ b/test/sql-tap/types.test.lua @@ -306,7 +306,7 @@ test:do_execsql_test(  test:do_execsql_test(      "types-2.3.2",      [[ -        SELECT a ISNULL FROM t3; +        SELECT a IS NULL FROM t3;      ]], {          --          1 diff --git a/test/sql-tap/types2.test.lua b/test/sql-tap/types2.test.lua index 6103593..06817aa 100755 --- a/test/sql-tap/types2.test.lua +++ b/test/sql-tap/types2.test.lua @@ -201,7 +201,7 @@ test_bool("types2-4.28", "o1='500'", "'500.0' > o1", 1)  -- types2-5.* - The 'IN (x, y....)' operator with no index.  --  -- Compare literals against literals (no affinity applied) -test_bool("types2-5.1", "", "(NULL IN ('10.0', 20)) ISNULL", 1) +test_bool("types2-5.1", "", "(NULL IN ('10.0', 20)) IS NULL", 1)  test_bool("types2-5.2", "", "10 IN ('10.0', 20)", 0)  test_bool("types2-5.3", "", "'10' IN ('10.0', 20)", 0)  test_bool("types2-5.4", "", "10 IN (10.0, 20)", 1) -- 2.7.4