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 3CF64265E0 for ; Tue, 13 Aug 2019 08:42:19 -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 Bf51BdNdE2Id for ; Tue, 13 Aug 2019 08:42:19 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (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 8EE4923166 for ; Tue, 13 Aug 2019 08:42:18 -0400 (EDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 12.2 \(3445.102.3\)) Subject: [tarantool-patches] Re: [PATCH] sql: add check for absence in From: Roman Khabibov In-Reply-To: Date: Tue, 13 Aug 2019 15:42:14 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: <6B834CCD-6A77-42D0-8AAD-B1BD2B8F51A6@tarantool.org> References: <20190802125252.54621-1-roman.habibov@tarantool.org> 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 Cc: "n. pettik" > On Aug 9, 2019, at 18:15, n.pettik wrote: >=20 >=20 >=20 >> On 2 Aug 2019, at 15:52, Roman Khabibov = wrote: >>=20 >> Check that hasn't after : "CREATE VIEW v >> AS WITH ... SELECT ...". Throw error, if it has. >=20 > What is the reason for that? I run example from ticket: >=20 > tarantool> \set language sql > tarantool> \set delimiter ; >=20 > 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 > ... >=20 > So, it fails at the stage of view creation. Please, ask Peter > to provide valid example. Otherwise there=E2=80=99s no problem and > issue can be closed. >=20 = 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=E2=80=99s 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 Date: Mon Jul 29 18:00:34 2019 +0300 sql: disallow in select =20 We don't support queries with the syntax: "CREATE VIEW v AS WITH ... SELECT ..." Throw the syntax error in this case. =20 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) ::=3D . {A =3D 0;} ///////////////////// The CREATE VIEW statement = ///////////////////////////// // cmd ::=3D 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 =3D 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 =3D require("sqltester") -test:plan(73) +test:plan(76) =20 --!./tcltestrunner.lua -- 2002 February 26 @@ -1233,4 +1233,39 @@ test:do_catchsql_test( -- }) =20 +-- 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); + ]], { + -- + -- + }) + +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; + ]], { + -- + 1,"Keyword 'WITH' is reserved. Please use double quotes if = 'WITH' is an identifier." + -- + }) + +test:do_execsql_test( + "view-24.3", + [[ + DROP TABLE ts + ]], { + -- + -- + }) + test:finish_test()