Tarantool development patches archive
 help / color / mirror / Atom feed
From: Roman Khabibov <roman.habibov@tarantool.org>
To: tarantool-patches@freelists.org
Cc: "n. pettik" <korablev@tarantool.org>
Subject: [tarantool-patches] Re: [PATCH] sql: add check for <WITH> absence in <CREATE VIEW>
Date: Tue, 13 Aug 2019 15:42:14 +0300	[thread overview]
Message-ID: <6B834CCD-6A77-42D0-8AAD-B1BD2B8F51A6@tarantool.org> (raw)
In-Reply-To: <DAA03B36-2520-4E40-AF7A-47ECAAB6FB7B@tarantool.org>


> On Aug 9, 2019, at 18:15, n.pettik <korablev@tarantool.org> wrote:
> 
> 
> 
>> On 2 Aug 2019, at 15:52, Roman Khabibov <roman.habibov@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@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()

  reply	other threads:[~2019-08-13 12:42 UTC|newest]

Thread overview: 12+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2019-08-02 12:52 [tarantool-patches] " Roman Khabibov
2019-08-09 15:15 ` [tarantool-patches] " n.pettik
2019-08-13 12:42   ` Roman Khabibov [this message]
2019-08-13 22:10     ` n.pettik
2019-08-16 19:09       ` Roman Khabibov
2019-08-19 15:39         ` Roman Khabibov
2019-08-20 19:41         ` n.pettik
2019-08-28 12:17           ` Roman Khabibov
2019-08-29 17:59             ` Nikita Pettik
2019-09-04 14:14               ` Roman Khabibov
2019-09-11 13:32                 ` Nikita Pettik
2019-09-13 14:57                   ` Roman Khabibov

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=6B834CCD-6A77-42D0-8AAD-B1BD2B8F51A6@tarantool.org \
    --to=roman.habibov@tarantool.org \
    --cc=korablev@tarantool.org \
    --cc=tarantool-patches@freelists.org \
    --subject='[tarantool-patches] Re: [PATCH] sql: add check for <WITH> absence in <CREATE VIEW>' \
    /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