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