[tarantool-patches] [PATCH] sql: remove support of partial indexes from parser

[tarantool-patches] [PATCH] sql: remove support of partial indexes from parser

[Nikita Pettik]

From: Gleb <gleb-skiba@mail.ru>

Currently, Tarantool doesn't support partial indexes, so lets temporary
remove their processing from SQL parser to avoid confusing situations.
It can be returned after #1260 (implement functional indexes in
Tarantool) is done.

Closes #2165
---
Branch: https://github.com/tarantool/tarantool/tree/gh-2165-remove-support-partial-indexes
Issue: https://github.com/tarantool/tarantool/issues/2165

 src/box/sql/parse.y              |  4 ++--
 test/sql-tap/analyze9.test.lua   |  2 +-
 test/sql-tap/autoindex4.test.lua |  2 +-
 test/sql-tap/fkey1.test.lua      |  2 +-
 test/sql-tap/index6.test.lua     | 12 ++++++------
 test/sql-tap/index7.test.lua     | 14 ++++++++------
 6 files changed, 19 insertions(+), 17 deletions(-)

diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 909891984..b2940b7c4 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1193,9 +1193,9 @@ 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. {
   sql_create_index(pParse, &X, sqlite3SrcListAppend(pParse->db,0,&Y), Z, U, &S,
-                   W, SORT_ORDER_ASC, NE, SQLITE_IDXTYPE_APPDEF);
+                   NULL, SORT_ORDER_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 3b3d52f67..c6ab65a73 100755
--- a/test/sql-tap/analyze9.test.lua
+++ b/test/sql-tap/analyze9.test.lua
@@ -1111,7 +1111,7 @@ test:do_test(
         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;
+            CREATE INDEX i1 ON t1(a, b);
             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;
diff --git a/test/sql-tap/autoindex4.test.lua b/test/sql-tap/autoindex4.test.lua
index 45bae4866..a567c5e7d 100755
--- a/test/sql-tap/autoindex4.test.lua
+++ b/test/sql-tap/autoindex4.test.lua
@@ -111,7 +111,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "autoindex4-3.1",
     [[
-        CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
+        CREATE INDEX Items_x1 ON Items(ItemName,Name);
 
         SELECT Items.ItemName
           FROM Items
diff --git a/test/sql-tap/fkey1.test.lua b/test/sql-tap/fkey1.test.lua
index 8749e1f32..bca82d93d 100755
--- a/test/sql-tap/fkey1.test.lua
+++ b/test/sql-tap/fkey1.test.lua
@@ -212,7 +212,7 @@ 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;
+        CREATE INDEX p1x ON p1(x);
         INSERT INTO p1 VALUES(1, 1, 1);
         CREATE TABLE c1(a PRIMARY KEY REFERENCES p1(x));
     ]], {
diff --git a/test/sql-tap/index6.test.lua b/test/sql-tap/index6.test.lua
index 2aa97e848..069623f66 100755
--- a/test/sql-tap/index6.test.lua
+++ b/test/sql-tap/index6.test.lua
@@ -225,7 +225,7 @@ test:do_execsql_test(
     "index6-6.0",
     [[
         CREATE TABLE t6(a,b, PRIMARY KEY (a,b));
-        CREATE INDEX t6b ON t6(b) WHERE b=1;
+        CREATE INDEX t6b ON t6(b);
         INSERT INTO t6(a,b) VALUES(123,456);
         SELECT * FROM t6;
     ]], {
@@ -262,7 +262,7 @@ test:do_execsql_test(
         CREATE TABLE t7a(id primary key, x);
         CREATE TABLE t7b(id primary key, y);
         INSERT INTO t7a VALUES(1, 1);
-        CREATE INDEX t7ax ON t7a(x) WHERE x=99;
+        CREATE INDEX t7ax ON t7a(x);
         SELECT x,y FROM t7a LEFT JOIN t7b ON (x=99) ORDER BY x;
     ]], {
         -- <index6-7.0>
@@ -318,7 +318,7 @@ test:do_execsql_test(
     [[
         CREATE TABLE t8a(id primary key, a,b);
         CREATE TABLE t8b(id primary key, x,y);
-        CREATE INDEX i8c ON t8b(y) WHERE x = 'value';
+        CREATE INDEX i8c ON t8b(y);
 
         INSERT INTO t8a VALUES(1, 1, 'one');
         INSERT INTO t8a VALUES(2, 2, 'two');
@@ -364,7 +364,7 @@ if (0 > 0)
         "index6-9.1",
         [[
             CREATE TABLE t9(a int, b int, c int);
-            CREATE INDEX t9ca ON t9(c,a) WHERE a in (10,12,20);
+            CREATE INDEX t9ca ON t9(c,a);
             INSERT INTO t9 VALUES(1,1,9),(10,2,35),(11,15,82),(20,19,5),(NULL,7,3);
             UPDATE t9 SET b=c WHERE a in (10,12,20);
             SELECT a,b,c,'|' FROM t9 ORDER BY a;
@@ -379,7 +379,7 @@ test:do_execsql_test(
     [[
         --DROP TABLE t9;
         CREATE TABLE t9(a int, b int, c int, PRIMARY KEY(a));
-        CREATE INDEX t9ca ON t9(c,a) WHERE a in (10,12,20);
+        CREATE INDEX t9ca ON t9(c,a);
         INSERT INTO t9 VALUES(1,1,9),(10,2,35),(11,15,82),(20,19,5);
         UPDATE t9 SET b=c WHERE a in (10,12,20);
         SELECT a,b,c,'|' FROM t9 ORDER BY a;
@@ -400,7 +400,7 @@ test:do_execsql_test(
           (2,3,4,5,6),
           (3,4,5,6,7),
           (1,2,3,8,9);
-        CREATE INDEX t10x ON t10(d) WHERE a=1 AND b=2 AND c=3;
+        CREATE INDEX t10x ON t10(d);
         SELECT e FROM t10 WHERE a=1 AND b=2 AND c=3 ORDER BY d;
     ]], {
         -- <index6-10.1>
diff --git a/test/sql-tap/index7.test.lua b/test/sql-tap/index7.test.lua
index c8f56eb29..11728d39e 100755
--- a/test/sql-tap/index7.test.lua
+++ b/test/sql-tap/index7.test.lua
@@ -261,7 +261,7 @@ test:do_execsql_test(
 test:do_execsql_test(
     "index7-6.2",
     [[
-        CREATE INDEX i4 ON t4(c) WHERE d='xyz';
+        CREATE INDEX i4 ON t4(c);
         SELECT a,b,c,d FROM (SELECT a,b FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';
     ]], {
         -- <index7-6.2>
@@ -291,14 +291,16 @@ test:do_eqp_test(
         -- </index7-6.4>
     })
 
+-- gh-2165 Currently, Tarantool lacks support of partial indexes,
+-- so temporary we removed processing of their syntax from parser.
+--
 test:do_catchsql_test(
-    "index7-6.5",
+    "index7-7.1",
     [[
-        CREATE INDEX t5a ON t5(a) WHERE a=#1;
+        CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER)
+        CREATE UNIQUE INDEX i ON t1 (a) WHERE a = 3;
     ]], {
-        -- <index7-6.5>
-        1, [[near "#1": syntax error]]
-        -- </index7-6.5>
+        1, "keyword \"CREATE\" is reserved"
     })
 
 test:finish_test()
-- 
2.15.1

[tarantool-patches] Re: [PATCH] sql: remove support of partial indexes from parser

[Vladislav Shpilevoy]

Hello. Thanks for the patch! See 1 comment below.

On 02/07/2018 14:08, Nikita Pettik wrote:
> From: Gleb <gleb-skiba@mail.ru>
> 
> Currently, Tarantool doesn't support partial indexes, so lets temporary
> remove their processing from SQL parser to avoid confusing situations.
> It can be returned after #1260 (implement functional indexes in
> Tarantool) is done.
> 
> Closes #2165
> ---
> Branch: https://github.com/tarantool/tarantool/tree/gh-2165-remove-support-partial-indexes
> Issue: https://github.com/tarantool/tarantool/issues/2165
> 
>   src/box/sql/parse.y              |  4 ++--
>   test/sql-tap/analyze9.test.lua   |  2 +-
>   test/sql-tap/autoindex4.test.lua |  2 +-
>   test/sql-tap/fkey1.test.lua      |  2 +-
>   test/sql-tap/index6.test.lua     | 12 ++++++------
>   test/sql-tap/index7.test.lua     | 14 ++++++++------
>   6 files changed, 19 insertions(+), 17 deletions(-)
> 
> diff --git a/test/sql-tap/index7.test.lua b/test/sql-tap/index7.test.lua
> index c8f56eb29..11728d39e 100755
> --- a/test/sql-tap/index7.test.lua
> +++ b/test/sql-tap/index7.test.lua
> @@ -291,14 +291,16 @@ test:do_eqp_test(
>           -- </index7-6.4>
>       })
>   
> +-- gh-2165 Currently, Tarantool lacks support of partial indexes,
> +-- so temporary we removed processing of their syntax from parser.
> +--
>   test:do_catchsql_test(
> -    "index7-6.5",
> +    "index7-7.1",
>       [[
> -        CREATE INDEX t5a ON t5(a) WHERE a=#1;
> +        CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER)
> +        CREATE UNIQUE INDEX i ON t1 (a) WHERE a = 3;
>       ]], {
> -        -- <index7-6.5>
> -        1, [[near "#1": syntax error]]
> -        -- </index7-6.5>
> +        1, "keyword \"CREATE\" is reserved"

1. The error is caused by typo in the request, not by lack of
partial indexes.

>       })
>   
>   test:finish_test()
> 

The rest is ok if the single purpose of the patch is to remove partial
indexes from frontend.

[tarantool-patches] Re: [PATCH] sql: remove support of partial indexes from parser

[n.pettik]

> 1. The error is caused by typo in the request, not by lack of
> partial indexes.

Forgot to place ‘;’ at the end of statement:

@@ -291,14 +291,16 @@ test:do_eqp_test(
         -- </index7-6.4>
     })
 
+-- gh-2165 Currently, Tarantool lacks support of partial indexes,
+-- so temporary we removed processing of their syntax from parser.
+--
 test:do_catchsql_test(
-    "index7-6.5",
+    "index7-7.1",
     [[
-        CREATE INDEX t5a ON t5(a) WHERE a=#1;
+        CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER);
+        CREATE UNIQUE INDEX i ON t1 (a) WHERE a = 3;
     ]], {
-        -- <index7-6.5>
-        1, [[near "#1": syntax error]]
-        -- </index7-6.5>
+        1, "keyword \"WHERE\" is reserved"

[tarantool-patches] Re: [PATCH] sql: remove support of partial indexes from parser

[Vladislav Shpilevoy]

LGTM.

On 02/07/2018 18:51, n.pettik wrote:
> 
>> 1. The error is caused by typo in the request, not by lack of
>> partial indexes.
> 
> Forgot to place ‘;’ at the end of statement:
> 
> @@ -291,14 +291,16 @@ test:do_eqp_test(
>           -- </index7-6.4>
>       })
>   
> +-- gh-2165 Currently, Tarantool lacks support of partial indexes,
> +-- so temporary we removed processing of their syntax from parser.
> +--
>   test:do_catchsql_test(
> -    "index7-6.5",
> +    "index7-7.1",
>       [[
> -        CREATE INDEX t5a ON t5(a) WHERE a=#1;
> +        CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER);
> +        CREATE UNIQUE INDEX i ON t1 (a) WHERE a = 3;
>       ]], {
> -        -- <index7-6.5>
> -        1, [[near "#1": syntax error]]
> -        -- </index7-6.5>
> +        1, "keyword \"WHERE\" is reserved"
> 

[tarantool-patches] Re: [PATCH] sql: remove support of partial indexes from parser

[Kirill Yukhin]

Hello,
On 02 июл 14:08, Nikita Pettik wrote:
> From: Gleb <gleb-skiba@mail.ru>
> 
> Currently, Tarantool doesn't support partial indexes, so lets temporary
> remove their processing from SQL parser to avoid confusing situations.
> It can be returned after #1260 (implement functional indexes in
> Tarantool) is done.
> 
> Closes #2165
> ---
> Branch: https://github.com/tarantool/tarantool/tree/gh-2165-remove-support-partial-indexes
> Issue: https://github.com/tarantool/tarantool/issues/2165
I've checked the patch into 2.0 branch.

--
Regards, Kirill Yukhin