[tarantool-patches] Re: [PATCH] sql: add check for <WITH> absence in <CREATE VIEW>

[Roman Khabibov]

> On Aug 9, 2019, at 18:15, n.pettik <korablev at tarantool.org> wrote:
> 
> 
> 
>> On 2 Aug 2019, at 15:52, Roman Khabibov <roman.habibov at tarantool.org> wrote:
>> 
>> Check that <CREATE VIEW> hasn't <WITH> after <AS>: "CREATE VIEW v
>> AS WITH ... SELECT ...". Throw error, if it has.
> 
> What is the reason for that? I run example from ticket:
> 
> tarantool> \set language sql
> tarantool> \set delimiter ;
> 
> tarantool> CREATE TABLE ts (s1 INT PRIMARY KEY);
> tarantool> INSERT INTO ts VALUES (1);
> tarantool> WITH RECURSIVE w AS (
>>            SELECT s1 FROM ts
>>            UNION ALL
>>            SELECT s1+1 FROM w WHERE s1 < 4)
>>          SELECT * FROM w;
> tarantool> CREATE VIEW v AS WITH RECURSIVE w AS (
>>            SELECT s1 FROM ts
>>            UNION ALL
>>            SELECT s1+1 FROM w WHERE s1 < 4)
>>          SELECT * FROM w;
> - null
> - Space 'W' does not exist
> ...
> 
> So, it fails at the stage of view creation. Please, ask Peter
> to provide valid example. Otherwise there’s no problem and
> issue can be closed.
> 
https://github.com/tarantool/tarantool/issues/4149#issuecomment-520390204

> This looks like a crutch. Please, either find out if it is
> possible to fix this at parser level (changing grammar),
> or simply close issue.
Yes, now it’s one-line fix, but error isn't so detailed.
But now it's a syntax error, as was required in the ticket.

commit e311d35bd64422bf6468b28a28057d9045817eca
Author: Roman Khabibov <roman.habibov at tarantool.org>
Date:   Mon Jul 29 18:00:34 2019 +0300

    sql: disallow <WITH> in <CREATE VIEW> select
    
    We don't support queries with the syntax:
    "CREATE VIEW v AS WITH ... SELECT ..."
    Throw the syntax error in this case.
    
    Closes #4149

diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 4c9d95f9c..d9ce239ca 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -399,7 +399,7 @@ ifexists(A) ::= .            {A = 0;}
 ///////////////////// The CREATE VIEW statement /////////////////////////////
 //
 cmd ::= CREATE(X) VIEW ifnotexists(E) nm(Y) eidlist_opt(C)
-          AS select(S). {
+          AS selectnowith(S). {
   if (!pParse->parse_only) {
     create_view_def_init(&pParse->create_view_def, &Y, &X, C, S, E);
     pParse->initiateTTrans = true;
diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua
index 101f4c3e7..cdba27e04 100755
--- a/test/sql-tap/view.test.lua
+++ b/test/sql-tap/view.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(73)
+test:plan(76)
 
 --!./tcltestrunner.lua
 -- 2002 February 26
@@ -1233,4 +1233,39 @@ test:do_catchsql_test(
         -- </view-23.8>
     })
 
+-- gh-4149: Check error message for
+-- "CREATE VIEW v AS WITH ... SELECT ...".
+test:do_execsql_test(
+    "view-24.1",
+    [[
+        CREATE TABLE ts (s1 INT PRIMARY KEY);
+        INSERT INTO ts VALUES (1);
+    ]], {
+        -- <view-24.1>
+        -- </view-24.1>
+    })
+
+test:do_catchsql_test(
+    "view-24.2",
+    [[
+        CREATE VIEW v AS WITH RECURSIVE w AS (
+            SELECT s1 FROM ts
+            UNION ALL
+            SELECT s1+1 FROM w WHERE s1 < 4)
+          SELECT * FROM w;
+    ]], {
+        -- <view-24.2>
+        1,"Keyword 'WITH' is reserved. Please use double quotes if 'WITH' is an identifier."
+        -- </view-24.2>
+    })
+
+test:do_execsql_test(
+    "view-24.3",
+    [[
+        DROP TABLE ts
+    ]], {
+        -- <view-24.3>
+        -- </view-24.3>
+    })
+
 test:finish_test()