From: "Gleb" <dmarc-noreply@freelists.org> (Redacted sender "gleb-skiba" for DMARC) To: tarantool-patches@freelists.org Subject: [tarantool-patches] [PATCH] sql: remove support of partial indexes Date: Thu, 29 Mar 2018 13:58:51 +0300 [thread overview] Message-ID: <1522321131-18495-1-git-send-email-gleb-skiba@mail.ru> (raw) 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
next reply other threads:[~2018-03-29 10:59 UTC|newest] Thread overview: 4+ messages / expand[flat|nested] mbox.gz Atom feed top 2018-03-29 10:58 Gleb [this message] 2018-04-02 12:31 ` [tarantool-patches] " Alexander Turenko 2018-04-03 11:42 [tarantool-patches] " Gleb 2018-04-04 8:37 Gleb
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=1522321131-18495-1-git-send-email-gleb-skiba@mail.ru \ --to=dmarc-noreply@freelists.org \ --cc=tarantool-patches@freelists.org \ --subject='Re: [tarantool-patches] [PATCH] sql: remove support of partial indexes' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox