Tarantool development patches archive
 help / color / mirror / Atom feed
* [tarantool-patches] [PATCH 1/1] sql: IS is only applicable when dealing with NULL
@ 2018-05-16 13:50 Мерген Имеев
  2018-05-16 18:52 ` [tarantool-patches] " Vladislav Shpilevoy
  0 siblings, 1 reply; 3+ messages in thread
From: Мерген Имеев @ 2018-05-16 13:50 UTC (permalink / raw)
  To: tarantool-patches

[-- Attachment #1: Type: text/plain, Size: 23100 bytes --]

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
---
 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(
         -- </e_expr-8.1.2>
     })
 
-test:do_execsql_test(
-    "e_expr-8.1.3",
-    [[
-        SELECT NULL IS     'ab'
-    ]], {
-        -- <e_expr-8.1.3>
-        0
-        -- </e_expr-8.1.3>
-    })
-
-test:do_execsql_test(
-    "e_expr-8.1.4",
-    [[
-        SELECT 'ab' IS     'ab'
-    ]], {
-        -- <e_expr-8.1.4>
-        1
-        -- </e_expr-8.1.4>
-    })
+-- 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'
+--     ]], {
+--         -- <e_expr-8.1.3>
+--         0
+--         -- </e_expr-8.1.3>
+--     })
+
+-- 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'
+--     ]], {
+--         -- <e_expr-8.1.4>
+--         1
+--         -- </e_expr-8.1.4>
+--     })
 
 test:do_execsql_test(
     "e_expr-8.1.5",
@@ -592,25 +594,27 @@ test:do_execsql_test(
         -- </e_expr-8.1.10>
     })
 
-test:do_execsql_test(
-    "e_expr-8.1.11",
-    [[
-        SELECT NULL IS NOT 'ab'
-    ]], {
-        -- <e_expr-8.1.11>
-        1
-        -- </e_expr-8.1.11>
-    })
-
-test:do_execsql_test(
-    "e_expr-8.1.12",
-    [[
-        SELECT 'ab' IS NOT 'ab'
-    ]], {
-        -- <e_expr-8.1.12>
-        0
-        -- </e_expr-8.1.12>
-    })
+-- 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'
+--     ]], {
+--         -- <e_expr-8.1.11>
+--         1
+--         -- </e_expr-8.1.11>
+--     })
+
+-- 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'
+--     ]], {
+--         -- <e_expr-8.1.12>
+--         0
+--         -- </e_expr-8.1.12>
+--     })
 
 test:do_execsql_test(
     "e_expr-8.1.13",
@@ -652,31 +656,6 @@ test:do_execsql_test(
         -- </e_expr-8.1.16>
     })
 
-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(
         -- </e_expr-9.13>
     })
 
-test:do_execsql_test(
-    "e_expr-9.14",
-    [[
-        SELECT  'abcd' IS 'ABCD'  COLLATE "unicode_ci"
-    ]], {
-        -- <e_expr-9.14>
-        1
-        -- </e_expr-9.14>
-    })
-
-test:do_execsql_test(
-    "e_expr-9.15",
-    [[
-        SELECT ('abcd' IS 'ABCD') COLLATE "unicode_ci"
-    ]], {
-        -- <e_expr-9.15>
-        0
-        -- </e_expr-9.15>
-    })
 
 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"
-    ]], {
-        -- <e_expr-9.20>
-        0
-        -- </e_expr-9.20>
-    })
-
-test:do_execsql_test(
-    "e_expr-9.21",
-    [[
-        SELECT ('abcd' IS NOT 'ABCD') COLLATE "unicode_ci"
-    ]], {
-        -- <e_expr-9.21>
-        1
-        -- </e_expr-9.21>
-    })
-
-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;
+    ]], {
+        -- <null-8.15>
+
+        -- </null-8.15>
+    })
+
+test:do_execsql_test(
+    "null-10.2",
+    [[
+        SELECT 1 WHERE 1 IS NOT NULL;
+    ]], {
+        -- <null-8.15>
+        1
+        -- </null-8.15>
+    })
+
+test:do_execsql_test(
+    "null-10.3",
+    [[
+        SELECT 1 WHERE NULL IS NULL;
+    ]], {
+        -- <null-8.15>
+        1
+        -- </null-8.15>
+    })
+
+test:do_execsql_test(
+    "null-10.4",
+    [[
+        SELECT 1 WHERE NULL IS NOT NULL;
+    ]], {
+        -- <null-8.15>
+
+        -- </null-8.15>
+    })
+
+test:do_catchsql_test(
+    "null-10.5",
+    [[
+        SELECT 1 WHERE 1 IS 1;
+    ]],
+    {
+    -- <index-1.3>
+    1, "near \"1\": syntax error"
+    -- <index-1.3>
+    })
+
+test:do_catchsql_test(
+    "null-10.6",
+    [[
+        SELECT 1 WHERE 1 IS NOT 1;
+    ]],
+    {
+    -- <index-1.3>
+    1, "near \"1\": syntax error"
+    -- <index-1.3>
+    })
+
+test:do_catchsql_test(
+    "null-10.7",
+    [[
+        SELECT 1 WHERE NULL IS 1;
+    ]],
+    {
+    -- <index-1.3>
+    1, "near \"1\": syntax error"
+    -- <index-1.3>
+    })
+
+test:do_catchsql_test(
+    "null-10.8",
+    [[
+        SELECT 1 WHERE NULL IS NOT 1;
+    ]],
+    {
+    -- <index-1.3>
+    1, "near \"1\": syntax error"
+    -- <index-1.3>
+    })
+
+
 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, {
         -- <subselect-1.5>
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;
-    ]], {
-        -- <transitive1-302>
-        1, 2, 1, 3, 3, 4, 3, 6, 5, 6, 5, 7
-        -- </transitive1-302>
-    })
-
-test:do_execsql_test(
     "transitive1-310",
     [[
         SELECT *
@@ -234,36 +221,6 @@ test:do_execsql_test(
         -- </transitive1-400>
     })
 
-test:do_execsql_test(
-    "transitive1-401",
-    [[
-        SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c=a;
-    ]], {
-        -- <transitive1-401>
-        "1-row"
-        -- </transitive1-401>
-    })
-
-test:do_execsql_test(
-    "transitive1-402",
-    [[
-        SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c IS a;
-    ]], {
-        -- <transitive1-402>
-        "1-row"
-        -- </transitive1-402>
-    })
-
-test:do_execsql_test(
-    "transitive1-403",
-    [[
-        SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c IS a;
-    ]], {
-        -- <transitive1-403>
-        "1-row"
-        -- </transitive1-403>
-    })
-
 -- 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;
     ]], {
         -- <types-2.3.2>
         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



[-- Attachment #2: Type: text/html, Size: 35439 bytes --]

^ permalink raw reply	[flat|nested] 3+ messages in thread

* [tarantool-patches] Re: [PATCH 1/1] sql: IS is only applicable when dealing with NULL
  2018-05-16 13:50 [tarantool-patches] [PATCH 1/1] sql: IS is only applicable when dealing with NULL Мерген Имеев
@ 2018-05-16 18:52 ` Vladislav Shpilevoy
  0 siblings, 0 replies; 3+ messages in thread
From: Vladislav Shpilevoy @ 2018-05-16 18:52 UTC (permalink / raw)
  To: tarantool-patches,
	Мерген
	Имеев

Hello. Thanks for the patch! See my 2 comments below.

On 16/05/2018 16:50, Мерген Имеев wrote:
> 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
> ---

1. Please, post here link to the branch and issue like this:
https://www.freelists.org/post/tarantool-patches/PATCH-11-vinyl-fix-crash-in-vinyl-iterator-secondary-next

2. You forgot to push you patch. I do not see a branch on github.

^ permalink raw reply	[flat|nested] 3+ messages in thread

* [tarantool-patches] [PATCH 1/1] sql: IS is only applicable when dealing with NULL
@ 2018-05-18 11:26 Мерген Имеев
  0 siblings, 0 replies; 3+ messages in thread
From: Мерген Имеев @ 2018-05-18 11:26 UTC (permalink / raw)
  To: tarantool-patches

[-- Attachment #1: Type: text/plain, Size: 23561 bytes --]

From aa5528cfdd54def0a23c6fd8223d283fc834a1d6 Mon Sep 17 00:00:00 2001
Message-Id: <aa5528cfdd54def0a23c6fd8223d283fc834a1d6.1526478195.git.imeevma@tarantool.org>
From: Mergen Imeev <imeevma@tarantool.org>
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(
         -- </e_expr-8.1.2>
     })
 
-test:do_execsql_test(
-    "e_expr-8.1.3",
-    [[
-        SELECT NULL IS     'ab'
-    ]], {
-        -- <e_expr-8.1.3>
-        0
-        -- </e_expr-8.1.3>
-    })
-
-test:do_execsql_test(
-    "e_expr-8.1.4",
-    [[
-        SELECT 'ab' IS     'ab'
-    ]], {
-        -- <e_expr-8.1.4>
-        1
-        -- </e_expr-8.1.4>
-    })
+-- 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'
+--     ]], {
+--         -- <e_expr-8.1.3>
+--         0
+--         -- </e_expr-8.1.3>
+--     })
+
+-- 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'
+--     ]], {
+--         -- <e_expr-8.1.4>
+--         1
+--         -- </e_expr-8.1.4>
+--     })
 
 test:do_execsql_test(
     "e_expr-8.1.5",
@@ -592,25 +594,27 @@ test:do_execsql_test(
         -- </e_expr-8.1.10>
     })
 
-test:do_execsql_test(
-    "e_expr-8.1.11",
-    [[
-        SELECT NULL IS NOT 'ab'
-    ]], {
-        -- <e_expr-8.1.11>
-        1
-        -- </e_expr-8.1.11>
-    })
-
-test:do_execsql_test(
-    "e_expr-8.1.12",
-    [[
-        SELECT 'ab' IS NOT 'ab'
-    ]], {
-        -- <e_expr-8.1.12>
-        0
-        -- </e_expr-8.1.12>
-    })
+-- 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'
+--     ]], {
+--         -- <e_expr-8.1.11>
+--         1
+--         -- </e_expr-8.1.11>
+--     })
+
+-- 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'
+--     ]], {
+--         -- <e_expr-8.1.12>
+--         0
+--         -- </e_expr-8.1.12>
+--     })
 
 test:do_execsql_test(
     "e_expr-8.1.13",
@@ -652,31 +656,6 @@ test:do_execsql_test(
         -- </e_expr-8.1.16>
     })
 
-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(
         -- </e_expr-9.13>
     })
 
-test:do_execsql_test(
-    "e_expr-9.14",
-    [[
-        SELECT  'abcd' IS 'ABCD'  COLLATE "unicode_ci"
-    ]], {
-        -- <e_expr-9.14>
-        1
-        -- </e_expr-9.14>
-    })
-
-test:do_execsql_test(
-    "e_expr-9.15",
-    [[
-        SELECT ('abcd' IS 'ABCD') COLLATE "unicode_ci"
-    ]], {
-        -- <e_expr-9.15>
-        0
-        -- </e_expr-9.15>
-    })
 
 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"
-    ]], {
-        -- <e_expr-9.20>
-        0
-        -- </e_expr-9.20>
-    })
-
-test:do_execsql_test(
-    "e_expr-9.21",
-    [[
-        SELECT ('abcd' IS NOT 'ABCD') COLLATE "unicode_ci"
-    ]], {
-        -- <e_expr-9.21>
-        1
-        -- </e_expr-9.21>
-    })
-
-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;
+    ]], {
+        -- <null-8.15>
+
+        -- </null-8.15>
+    })
+
+test:do_execsql_test(
+    "null-10.2",
+    [[
+        SELECT 1 WHERE 1 IS NOT NULL;
+    ]], {
+        -- <null-8.15>
+        1
+        -- </null-8.15>
+    })
+
+test:do_execsql_test(
+    "null-10.3",
+    [[
+        SELECT 1 WHERE NULL IS NULL;
+    ]], {
+        -- <null-8.15>
+        1
+        -- </null-8.15>
+    })
+
+test:do_execsql_test(
+    "null-10.4",
+    [[
+        SELECT 1 WHERE NULL IS NOT NULL;
+    ]], {
+        -- <null-8.15>
+
+        -- </null-8.15>
+    })
+
+test:do_catchsql_test(
+    "null-10.5",
+    [[
+        SELECT 1 WHERE 1 IS 1;
+    ]],
+    {
+    -- <index-1.3>
+    1, "near \"1\": syntax error"
+    -- <index-1.3>
+    })
+
+test:do_catchsql_test(
+    "null-10.6",
+    [[
+        SELECT 1 WHERE 1 IS NOT 1;
+    ]],
+    {
+    -- <index-1.3>
+    1, "near \"1\": syntax error"
+    -- <index-1.3>
+    })
+
+test:do_catchsql_test(
+    "null-10.7",
+    [[
+        SELECT 1 WHERE NULL IS 1;
+    ]],
+    {
+    -- <index-1.3>
+    1, "near \"1\": syntax error"
+    -- <index-1.3>
+    })
+
+test:do_catchsql_test(
+    "null-10.8",
+    [[
+        SELECT 1 WHERE NULL IS NOT 1;
+    ]],
+    {
+    -- <index-1.3>
+    1, "near \"1\": syntax error"
+    -- <index-1.3>
+    })
+
+
 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, {
         -- <subselect-1.5>
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;
-    ]], {
-        -- <transitive1-302>
-        1, 2, 1, 3, 3, 4, 3, 6, 5, 6, 5, 7
-        -- </transitive1-302>
-    })
-
-test:do_execsql_test(
     "transitive1-310",
     [[
         SELECT *
@@ -234,36 +221,6 @@ test:do_execsql_test(
         -- </transitive1-400>
     })
 
-test:do_execsql_test(
-    "transitive1-401",
-    [[
-        SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c=a;
-    ]], {
-        -- <transitive1-401>
-        "1-row"
-        -- </transitive1-401>
-    })
-
-test:do_execsql_test(
-    "transitive1-402",
-    [[
-        SELECT '1-row' FROM t401 LEFT JOIN t402 ON b=a JOIN t403 ON c IS a;
-    ]], {
-        -- <transitive1-402>
-        "1-row"
-        -- </transitive1-402>
-    })
-
-test:do_execsql_test(
-    "transitive1-403",
-    [[
-        SELECT '1-row' FROM t401 LEFT JOIN t402 ON b IS a JOIN t403 ON c IS a;
-    ]], {
-        -- <transitive1-403>
-        "1-row"
-        -- </transitive1-403>
-    })
-
 -- 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;
     ]], {
         -- <types-2.3.2>
         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



[-- Attachment #2: Type: text/html, Size: 35939 bytes --]

^ permalink raw reply	[flat|nested] 3+ messages in thread

end of thread, other threads:[~2018-05-18 11:26 UTC | newest]

Thread overview: 3+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2018-05-16 13:50 [tarantool-patches] [PATCH 1/1] sql: IS is only applicable when dealing with NULL Мерген Имеев
2018-05-16 18:52 ` [tarantool-patches] " Vladislav Shpilevoy
2018-05-18 11:26 [tarantool-patches] " Мерген Имеев

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox