* [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] [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