[tarantool-patches] [PATCH] sql: remove support of partial indexes
Gleb
dmarc-noreply at freelists.org
Thu Mar 29 13:58:51 MSK 2018
Remove support partial indexes.
Add test which check inaccessibility of partial index syntax.
Comment tests which use partial index.
They can be enabled after #2626.
Fixes #2165
---
Issue from https://github.com/tarantool/tarantool/issues/2165.
Source from https://github.com/tarantool/tarantool/tree/gh-2165-remove-support-partial-indexes.
src/box/sql/parse.y | 4 +-
test/sql-tap/analyze9.test.lua | 158 ++++++++++-----------
test/sql-tap/autoindex4.test.lua | 37 +++--
test/sql-tap/fkey1.test.lua | 66 ++++-----
...gh-2165-remove-support-partial-indexes.test.lua | 16 +++
test/sql-tap/index7.test.lua | 81 +++++------
test/sql-tap/suite.ini | 1 +
7 files changed, 183 insertions(+), 180 deletions(-)
create mode 100755 test/sql-tap/gh-2165-remove-support-partial-indexes.test.lua
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 914fc53..5fb2c89 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1236,10 +1236,10 @@ paren_exprlist(A) ::= LP exprlist(X) RP. {A = X;}
///////////////////////////// The CREATE INDEX command ///////////////////////
//
cmd ::= createkw(S) uniqueflag(U) INDEX ifnotexists(NE) nm(X)
- ON nm(Y) LP sortlist(Z) RP where_opt(W). {
+ ON nm(Y) LP sortlist(Z) RP. {
sqlite3CreateIndex(pParse, &X,
sqlite3SrcListAppend(pParse->db,0,&Y), Z, U,
- &S, W, SQLITE_SO_ASC, NE, SQLITE_IDXTYPE_APPDEF);
+ &S, 0, SQLITE_SO_ASC, NE, SQLITE_IDXTYPE_APPDEF);
}
%type uniqueflag {int}
diff --git a/test/sql-tap/analyze9.test.lua b/test/sql-tap/analyze9.test.lua
index 4ce575e..ddf7a7c 100755
--- a/test/sql-tap/analyze9.test.lua
+++ b/test/sql-tap/analyze9.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(124)
+test:plan(118)
testprefix = "analyze9"
@@ -78,7 +78,6 @@ test:do_execsql_test(
1.3,
[[
SELECT "tbl","idx","neq","nlt","ndlt",msgpack_decode_sample("sample") FROM "_sql_stat4" where "idx" = 'T1';
-
]], {
-- <1.3>
'T1', 'T1', '1', '0', '0', '(0)', 'T1', 'T1', '1', '1', '1', '(1)',
@@ -209,7 +208,7 @@ test:do_execsql_test(
INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
- INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
+ INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
CREATE INDEX t1b ON t1(b);
ANALYZE;
SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60;
@@ -252,16 +251,12 @@ test:do_test(
INSERT INTO t1(id, c, b, a) VALUES(null, 200, 1, 'a');
INSERT INTO t1(id, c, b, a) VALUES(null, 200, 1, 'b');
INSERT INTO t1(id, c, b, a) VALUES(null, 200, 1, 'c');
-
INSERT INTO t1(id, c, b, a) VALUES(null, 200, 2, 'e');
INSERT INTO t1(id, c, b, a) VALUES(null, 200, 2, 'f');
-
INSERT INTO t1(id, c, b, a) VALUES(null, 201, 3, 'g');
INSERT INTO t1(id, c, b, a) VALUES(null, 201, 4, 'h');
-
ANALYZE;
SELECT count(*) FROM "_sql_stat4";
-
]])
end, {
-- <4.1>
@@ -1105,82 +1100,82 @@ test:do_execsql_test(
---------------------------------------------------------------------------
-- Test that stat4 data may be used with partial indexes.
--
-test:do_test(
- 17.1,
- function()
- test:execsql([[
- DROP TABLE IF EXISTS t1;
- CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c, d);
- CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL;
- INSERT INTO t1 VALUES(null, -1, -1, -1, NULL);
- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
- ]])
- local b = 0
- for i = 0, 31 do
- if (i < 8) then
- b = 0
- else
- b = i
- end
- test:execsql(string.format(" INSERT INTO t1 VALUES(null, %s%%2, %s, %s/2, 'abc') ", i, b, i))
- end
- return test:execsql("ANALYZE")
- end, {
- -- <17.1>
- -- </17.1>
- })
+--test:do_test(
+-- 17.1,
+-- function()
+-- test:execsql([[
+-- DROP TABLE IF EXISTS t1;
+-- CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, a, b, c, d);
+-- CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL;
+-- INSERT INTO t1 VALUES(null, -1, -1, -1, NULL);
+-- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
+-- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
+-- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
+-- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
+-- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
+-- INSERT INTO t1 SELECT null, 2*a,2*b,2*c,d FROM t1;
+-- ]])
+-- local b = 0
+-- for i = 0, 31 do
+-- if (i < 8) then
+-- b = 0
+-- else
+-- b = i
+-- end
+-- test:execsql(string.format(" INSERT INTO t1 VALUES(null, %s%%2, %s, %s/2, 'abc') ", i, b, i))
+-- end
+-- return test:execsql("ANALYZE")
+-- end, {
+-- -- <17.1>
+-- -- </17.1>
+-- })
-test:do_execsql_test(
- 17.2,
- [[
- EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
- ]], {
- -- <17.2>
- 0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX I1 (A=? AND B=?)'
- -- </17.2>
- })
+--test:do_execsql_test(
+-- 17.2,
+-- [[
+-- EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
+-- ]], {
+-- -- <17.2>
+-- 0, 0, 0, 'SEARCH TABLE T1 USING COVERING INDEX I1 (A=? AND B=?)'
+-- -- </17.2>
+-- })
-test:do_execsql_test(
- 17.3,
- [[
- EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
- ]], {
- -- <17.3>
- 0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX I1 (A=? AND B=?)"
- -- </17.3>
- })
+--test:do_execsql_test(
+-- 17.3,
+-- [[
+-- EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
+-- ]], {
+-- -- <17.3>
+-- 0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX I1 (A=? AND B=?)"
+-- -- </17.3>
+-- })
-test:do_execsql_test(
- 17.4,
- [[
- CREATE INDEX i2 ON t1(c, d);
- ANALYZE;
- ]])
+--test:do_execsql_test(
+-- 17.4,
+-- [[
+-- CREATE INDEX i2 ON t1(c, d);
+-- ANALYZE;
+-- ]])
-test:do_execsql_test(
- 17.5,
- [[
- EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
- ]], {
- -- <17.5>
- 0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX I2 (C=? AND D>?)"
- -- </17.5>
- })
+--test:do_execsql_test(
+-- 17.5,
+-- [[
+-- EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
+-- ]], {
+-- -- <17.5>
+-- 0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX I2 (C=? AND D>?)"
+-- -- </17.5>
+-- })
-test:do_execsql_test(
- 17.6,
- [[
- EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
- ]], {
- -- <17.6>
- 0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX I2 (C=? AND D>?)"
- -- </17.6>
- })
+--test:do_execsql_test(
+-- 17.6,
+-- [[
+-- EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
+-- ]], {
+ -- <17.6>
+-- 0, 0, 0, "SEARCH TABLE T1 USING COVERING INDEX I2 (C=? AND D>?)"
+ -- </17.6>
+-- })
---------------------------------------------------------------------------
@@ -1308,7 +1303,6 @@ test:do_execsql_test(
WITH r(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<=100)
INSERT INTO t3 SELECT CASE WHEN (x>45 AND x<96) THEN 'B' ELSE 'A' END,
x, CASE WHEN (x<51) THEN 'one' ELSE 'two' END, x FROM r;
-
CREATE INDEX i3 ON t3(c);
CREATE INDEX i4 ON t3(d);
ANALYZE;
@@ -1354,7 +1348,6 @@ test:do_execsql_test(
CREATE TABLE t4(a COLLATE "unicode_ci", b, c, d, e, f, PRIMARY KEY(c, b, a));
CREATE INDEX i41 ON t4(e);
CREATE INDEX i42 ON t4(f);
-
WITH data(a, b, c, d, e, f) AS (SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0 UNION ALL
SELECT int_to_char(f+1), b, c, d, (e+1) % 2, f+1 FROM data WHERE f<1024)
INSERT INTO t4 SELECT a, b, c, d, e, f FROM data;
@@ -1601,18 +1594,13 @@ test:do_execsql_test(
CREATE TABLE t1(id INTEGER PRIMARY KEY AUTOINCREMENT, x, y, z);
CREATE INDEX i1 ON t1(x, y);
CREATE INDEX i2 ON t1(z);
-
-
WITH cnt(y) AS (SELECT 0 UNION ALL SELECT y+1 FROM cnt WHERE y<99),
letters(x) AS (SELECT 'A' UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D')
INSERT INTO t1(id, x, y) SELECT null, x, y FROM letters, cnt;
-
WITH letters(x) AS (SELECT 'A' UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D')
INSERT INTO t1(id, x, y) SELECT null, x, 70 FROM letters;
-
WITH cnt(i) AS (SELECT 407 UNION ALL SELECT i+1 FROM cnt WHERE i<9999)
INSERT INTO t1(id, x, y) SELECT i, i, i FROM cnt;
-
UPDATE t1 SET z = (id / 95);
ANALYZE;
]])
diff --git a/test/sql-tap/autoindex4.test.lua b/test/sql-tap/autoindex4.test.lua
index 45bae48..bdb0b98 100755
--- a/test/sql-tap/autoindex4.test.lua
+++ b/test/sql-tap/autoindex4.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(7)
+test:plan(6)
--!./tcltestrunner.lua
-- 2014-10-24
@@ -27,7 +27,6 @@ test:do_execsql_test(
INSERT INTO t1 VALUES(123,'abc'),(234,'def'),(234,'ghi'),(345,'jkl');
CREATE TABLE t2(x,y, primary key(x,y));
INSERT INTO t2 VALUES(987,'zyx'),(654,'wvu'),(987,'rqp');
-
SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=987 ORDER BY +b;
]], {
-- <autoindex4-1.0>
@@ -95,7 +94,6 @@ test:do_execsql_test(
INSERT INTO Items VALUES('Item1','Parent');
INSERT INTO Items VALUES('Item2','Parent');
CREATE TABLE B(Name text primary key);
-
SELECT Items.ItemName
FROM Items
LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
@@ -108,22 +106,21 @@ test:do_execsql_test(
-- </autoindex4-3.0>
})
-test:do_execsql_test(
- "autoindex4-3.1",
- [[
- CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
-
- SELECT Items.ItemName
- FROM Items
- LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
- LEFT JOIN B ON (B.Name = Items.ItemName)
- WHERE Items.Name = 'Parent'
- ORDER BY Items.ItemName;
- ]], {
- -- <autoindex4-3.1>
- "Item1", "Item2"
- -- </autoindex4-3.1>
- })
+--test:do_execsql_test(
+-- "autoindex4-3.1",
+-- [[
+-- CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
+--
+-- SELECT Items.ItemName
+-- FROM Items
+-- LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
+-- LEFT JOIN B ON (B.Name = Items.ItemName)
+-- WHERE Items.Name = 'Parent'
+-- ORDER BY Items.ItemName;
+-- ]], {
+-- -- <autoindex4-3.1>
+-- "Item1", "Item2"
+-- -- </autoindex4-3.1>
+-- })
test:finish_test()
-
diff --git a/test/sql-tap/fkey1.test.lua b/test/sql-tap/fkey1.test.lua
index 8749e1f..ed40b81 100755
--- a/test/sql-tap/fkey1.test.lua
+++ b/test/sql-tap/fkey1.test.lua
@@ -1,6 +1,7 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(19)
+test:plan(16)
+
-- This file implements regression tests for foreign keys.
@@ -123,7 +124,6 @@ test:do_execsql_test(
INSERT INTO "xx4"("xx5") VALUES('abc');
INSERT INTO "xx1"("xx2","xx3") VALUES('uvw','xyz');
SELECT 1, "xx5" FROM "xx4";
-
]], {
-- <fkey1-4.1>
1, 'abc'
@@ -208,36 +208,36 @@ test:do_execsql_test(
-- </fkey1-5.6>
})
-test:do_execsql_test(
- "fkey1-6.1",
- [[
- CREATE TABLE p1(id PRIMARY KEY, x, y);
- CREATE UNIQUE INDEX p1x ON p1(x) WHERE y<2;
- INSERT INTO p1 VALUES(1, 1, 1);
- CREATE TABLE c1(a PRIMARY KEY REFERENCES p1(x));
- ]], {
- -- <fkey1-6.1>
- -- </fkey1-6.1>
- })
-
-test:do_catchsql_test(
- "fkey1-6.2",
- [[
- INSERT INTO c1 VALUES(1);
- ]], {
- -- <fkey1-6.2>
- 1, "foreign key mismatch - \"C1\" referencing \"P1\""
- -- </fkey1-6.2>
- })
-
-test:do_execsql_test(
- "fkey1-6.3",
- [[
- CREATE UNIQUE INDEX p1x2 ON p1(x);
- INSERT INTO c1 VALUES(1);
- ]], {
- -- <fkey1-6.3>
- -- </fkey1-6.3>
- })
+--test:do_execsql_test(
+-- "fkey1-6.1",
+-- [[
+-- CREATE TABLE p1(id PRIMARY KEY, x, y);
+-- CREATE UNIQUE INDEX p1x ON p1(x) WHERE y<2;
+-- INSERT INTO p1 VALUES(1, 1, 1);
+-- CREATE TABLE c1(a PRIMARY KEY REFERENCES p1(x));
+-- ]], {
+-- -- <fkey1-6.1>
+-- -- </fkey1-6.1>
+-- })
+
+--test:do_catchsql_test(
+-- "fkey1-6.2",
+-- [[
+-- INSERT INTO c1 VALUES(1);
+-- ]], {
+-- -- <fkey1-6.2>
+-- 1, "foreign key mismatch - \"C1\" referencing \"P1\""
+-- -- </fkey1-6.2>
+-- })
+
+--test:do_execsql_test(
+-- "fkey1-6.3",
+-- [[
+-- CREATE UNIQUE INDEX p1x2 ON p1(x);
+-- INSERT INTO c1 VALUES(1);
+-- ]], {
+-- -- <fkey1-6.3>
+-- -- </fkey1-6.3>
+-- })
test:finish_test()
diff --git a/test/sql-tap/gh-2165-remove-support-partial-indexes.test.lua b/test/sql-tap/gh-2165-remove-support-partial-indexes.test.lua
new file mode 100755
index 0000000..a4e274b
--- /dev/null
+++ b/test/sql-tap/gh-2165-remove-support-partial-indexes.test.lua
@@ -0,0 +1,16 @@
+#!/usr/bin/env tarantool
+test = require("sqltester")
+
+test:plan(1)
+
+
+test:do_catchsql_test(
+ "partial-index-1",
+ [[
+ CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER)
+ CREATE UNIQUE INDEX i ON t1 (a) WHERE a = 3;
+ ]], {
+ 1,"keyword \"CREATE\" is reserved"
+ })
+
+test:finish_test()
diff --git a/test/sql-tap/index7.test.lua b/test/sql-tap/index7.test.lua
index c8f56eb..97ac194 100755
--- a/test/sql-tap/index7.test.lua
+++ b/test/sql-tap/index7.test.lua
@@ -1,6 +1,7 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(5)
+test:plan(1)
+
--!./tcltestrunner.lua
-- 2013-11-04
@@ -258,47 +259,47 @@ test:do_execsql_test(
-- </index7-6.1>
})
-test:do_execsql_test(
- "index7-6.2",
- [[
- CREATE INDEX i4 ON t4(c) WHERE d='xyz';
- SELECT a,b,c,d FROM (SELECT a,b FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';
- ]], {
- -- <index7-6.2>
- 1, "xyz", "abc", "not xyz"
- -- </index7-6.2>
- })
+--test:do_execsql_test(
+-- "index7-6.2",
+-- [[
+-- CREATE INDEX i4 ON t4(c) WHERE d='xyz';
+-- SELECT a,b,c,d FROM (SELECT a,b FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';
+-- ]], {
+-- -- <index7-6.2>
+-- 1, "xyz", "abc", "not xyz"
+-- -- </index7-6.2>
+-- })
-test:do_execsql_test(
- "index7-6.3",
- [[
- CREATE VIEW v4 AS SELECT c,d FROM t4;
- INSERT INTO t4 VALUES(2, 'def', 'xyz');
- SELECT * FROM v4 WHERE d='xyz' AND c='def'
- ]], {
- -- <index7-6.3>
- "def", "xyz"
- -- </index7-6.3>
- })
+--test:do_execsql_test(
+-- "index7-6.3",
+-- [[
+-- CREATE VIEW v4 AS SELECT c,d FROM t4;
+-- INSERT INTO t4 VALUES(2, 'def', 'xyz');
+-- SELECT * FROM v4 WHERE d='xyz' AND c='def'
+-- ]], {
+-- -- <index7-6.3>
+-- "def", "xyz"
+-- -- </index7-6.3>
+-- })
-test:do_eqp_test(
- "index7-6.4",
- [[
- SELECT * FROM v4 WHERE d='xyz' AND c='def'
- ]], {
- -- <index7-6.4>
- {0, 0, 0, "SEARCH TABLE T4 USING COVERING INDEX I4 (C=?)"}
- -- </index7-6.4>
- })
+--test:do_eqp_test(
+-- "index7-6.4",
+-- [[
+-- SELECT * FROM v4 WHERE d='xyz' AND c='def'
+-- ]], {
+-- -- <index7-6.4>
+-- {0, 0, 0, "SEARCH TABLE T4 USING COVERING INDEX I4 (C=?)"}
+-- -- </index7-6.4>
+-- })
-test:do_catchsql_test(
- "index7-6.5",
- [[
- CREATE INDEX t5a ON t5(a) WHERE a=#1;
- ]], {
- -- <index7-6.5>
- 1, [[near "#1": syntax error]]
- -- </index7-6.5>
- })
+--test:do_catchsql_test(
+-- "index7-6.5",
+-- [[
+-- CREATE INDEX t5a ON t5(a) WHERE a=#1;
+-- ]], {
+-- -- <index7-6.5>
+-- 1, [[near "#1": syntax error]]
+-- -- </index7-6.5>
+-- })
test:finish_test()
diff --git a/test/sql-tap/suite.ini b/test/sql-tap/suite.ini
index 0bc9e83..d29c1b0 100644
--- a/test/sql-tap/suite.ini
+++ b/test/sql-tap/suite.ini
@@ -3,3 +3,4 @@ core = app
description = Database tests with #! using TAP
lua_libs = lua/sqltester.lua ../sql/lua/sql_tokenizer.lua ../box/lua/identifier.lua
is_parallel = True
+disabled = index6.test.lua ; to be enabled after #2626
--
2.7.4
More information about the Tarantool-patches
mailing list