From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4D88627ED2 for ; Thu, 29 Mar 2018 06:59:00 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HH5buPkb5owS for ; Thu, 29 Mar 2018 06:59:00 -0400 (EDT) Received: from smtp47.i.mail.ru (smtp47.i.mail.ru [94.100.177.107]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id B0CAB27E1B for ; Thu, 29 Mar 2018 06:58:59 -0400 (EDT) Received: by smtp47.i.mail.ru with esmtpa (envelope-from ) id 1f1VGj-0002tK-22 for tarantool-patches@freelists.org; Thu, 29 Mar 2018 13:58:57 +0300 From: "Gleb" (Redacted sender "gleb-skiba" for DMARC) Subject: [tarantool-patches] [PATCH] sql: remove support of partial indexes Date: Thu, 29 Mar 2018 13:58:51 +0300 Message-Id: <1522321131-18495-1-git-send-email-gleb-skiba@mail.ru> Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: tarantool-patches@freelists.org 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> - -- - }) +--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> +-- -- +-- }) -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=?)' - -- - }) +--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=?)' +-- -- +-- }) -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=?)" - -- - }) +--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=?)" +-- -- +-- }) -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>?)" - -- - }) +--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>?)" +-- -- +-- }) -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>?)" - -- - }) +--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>?)" + -- +-- }) --------------------------------------------------------------------------- @@ -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; ]], { -- @@ -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( -- }) -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; - ]], { - -- - "Item1", "Item2" - -- - }) +--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; +-- ]], { +-- -- +-- "Item1", "Item2" +-- -- +-- }) 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"; - ]], { -- 1, 'abc' @@ -208,36 +208,36 @@ test:do_execsql_test( -- }) -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)); - ]], { - -- - -- - }) - -test:do_catchsql_test( - "fkey1-6.2", - [[ - INSERT INTO c1 VALUES(1); - ]], { - -- - 1, "foreign key mismatch - \"C1\" referencing \"P1\"" - -- - }) - -test:do_execsql_test( - "fkey1-6.3", - [[ - CREATE UNIQUE INDEX p1x2 ON p1(x); - INSERT INTO c1 VALUES(1); - ]], { - -- - -- - }) +--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)); +-- ]], { +-- -- +-- -- +-- }) + +--test:do_catchsql_test( +-- "fkey1-6.2", +-- [[ +-- INSERT INTO c1 VALUES(1); +-- ]], { +-- -- +-- 1, "foreign key mismatch - \"C1\" referencing \"P1\"" +-- -- +-- }) + +--test:do_execsql_test( +-- "fkey1-6.3", +-- [[ +-- CREATE UNIQUE INDEX p1x2 ON p1(x); +-- INSERT INTO c1 VALUES(1); +-- ]], { +-- -- +-- -- +-- }) 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( -- }) -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'; - ]], { - -- - 1, "xyz", "abc", "not xyz" - -- - }) +--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'; +-- ]], { +-- -- +-- 1, "xyz", "abc", "not xyz" +-- -- +-- }) -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' - ]], { - -- - "def", "xyz" - -- - }) +--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' +-- ]], { +-- -- +-- "def", "xyz" +-- -- +-- }) -test:do_eqp_test( - "index7-6.4", - [[ - SELECT * FROM v4 WHERE d='xyz' AND c='def' - ]], { - -- - {0, 0, 0, "SEARCH TABLE T4 USING COVERING INDEX I4 (C=?)"} - -- - }) +--test:do_eqp_test( +-- "index7-6.4", +-- [[ +-- SELECT * FROM v4 WHERE d='xyz' AND c='def' +-- ]], { +-- -- +-- {0, 0, 0, "SEARCH TABLE T4 USING COVERING INDEX I4 (C=?)"} +-- -- +-- }) -test:do_catchsql_test( - "index7-6.5", - [[ - CREATE INDEX t5a ON t5(a) WHERE a=#1; - ]], { - -- - 1, [[near "#1": syntax error]] - -- - }) +--test:do_catchsql_test( +-- "index7-6.5", +-- [[ +-- CREATE INDEX t5a ON t5(a) WHERE a=#1; +-- ]], { +-- -- +-- 1, [[near "#1": syntax error]] +-- -- +-- }) 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