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 4B6F6296AF for ; Wed, 28 Aug 2019 08:20:13 -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 FKEOom6VCKIH for ; Wed, 28 Aug 2019 08:17:44 -0400 (EDT) Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (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 15868292EE for ; Wed, 28 Aug 2019 08:17:43 -0400 (EDT) Content-Type: text/plain; charset=us-ascii 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: Wed, 28 Aug 2019 15:17:41 +0300 Content-Transfer-Encoding: quoted-printable Message-Id: <1B494CAB-704F-4A62-A00D-257388DDB2B8@tarantool.org> References: <20190802125252.54621-1-roman.habibov@tarantool.org> <6B834CCD-6A77-42D0-8AAD-B1BD2B8F51A6@tarantool.org> <8BD992A5-0A74-4E60-A239-5FDE85783467@tarantool.org> <326482ED-8190-4B72-B2B8-F20763A86F7E@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 20, 2019, at 22:41, n.pettik wrote: > You slightly misunderstood me. I proposed to allow such queries > gracefully handling WITH statement and avoiding references > counter incrementation for CTEs. commit 202677704c455208d6def08d373776cfae82fdbe Author: Roman Khabibov Date: Mon Jul 29 18:00:34 2019 +0300 sql: allow to create view as clause =20 Allow views to use CTEs in clauses, which can be in any (nested) select after . =20 Closes #4149 diff --git a/src/box/alter.cc b/src/box/alter.cc index 92f1d5b22..d3d6770cf 100644 --- a/src/box/alter.cc +++ b/src/box/alter.cc @@ -1721,6 +1721,8 @@ update_view_references(struct Select *select, int = update_value, const char *space_name =3D sql_src_list_entry_name(list, = i); if (space_name =3D=3D NULL) continue; + if (is_cte(select, space_name) =3D=3D true) + continue; struct space *space =3D space_by_name(space_name); if (space =3D=3D NULL) { if (! suppress_error) { diff --git a/src/box/sql.h b/src/box/sql.h index 9ccecf28c..c180e40e1 100644 --- a/src/box/sql.h +++ b/src/box/sql.h @@ -323,6 +323,17 @@ sql_select_delete(struct sql *db, struct Select = *select); struct SrcList * sql_select_expand_from_tables(struct Select *select); =20 +/** + * Check if @a name matches with at least one of CTE names typed + * in clauses within @a select. + * + * @param select Select to be checked. + * @retval true Has CTE with @a name. + * @retval false Hasn't CTE with @a name. +*/ +bool +is_cte(struct Select *select, const char *name); + /** * Temporary getter in order to avoid including sqlInt.h * in alter.cc. diff --git a/src/box/sql/select.c b/src/box/sql/select.c index c312f61f1..0ee840b89 100644 --- a/src/box/sql/select.c +++ b/src/box/sql/select.c @@ -332,6 +332,25 @@ sql_select_expand_from_tables(struct Select = *select) return walker.u.pSrcList; } =20 +bool +is_cte(struct Select *select, const char *name) +{ + assert(select !=3D NULL && name !=3D NULL); + struct With *with =3D select->pWith; + if (with !=3D NULL) { + if (memcmp(name, with->a->zName, strlen(name)) =3D=3D 0) + return true; + } + struct SrcList *list =3D select->pSrc; + int item_count =3D sql_src_list_entry_count(list); + for (int i =3D 0; i < item_count; ++i) { + if (list->a[i].pSelect !=3D NULL) + if (is_cte(list->a[i].pSelect, name) =3D=3D = true) + return true; + } + return false; +} + /* * Given 1 to 3 identifiers preceding the JOIN keyword, determine the * type of join. Return an integer constant that expresses that type diff --git a/test/sql-tap/view.test.lua b/test/sql-tap/view.test.lua index 101f4c3e7..4b695fa6a 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(80) =20 --!./tcltestrunner.lua -- 2002 February 26 @@ -1233,4 +1233,99 @@ test:do_catchsql_test( -- }) =20 +-- gh-4149: Check error message for view creation with (nested) +-- select with clause. +test:do_execsql_test( + "view-24.1", + [[ + CREATE TABLE ts (s1 INT PRIMARY KEY); + INSERT INTO ts VALUES (1); + ]], { + -- + -- + }) + +test:do_execsql_test( + "view-24.2", + [[ + CREATE VIEW v AS WITH w(id) AS ( + SELECT 1) + SELECT * FROM ts; + ]], { + -- + -- + }) + +test:do_execsql_test( + "view-24.3", + [[ + DROP VIEW v; + 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; + ]], { + -- + -- + }) + +test:do_execsql_test( + "view-24.4", + [[ + DROP VIEW v; + CREATE VIEW v AS SELECT * FROM ( + WITH RECURSIVE w AS ( + SELECT s1 FROM ts + UNION ALL + SELECT s1+1 FROM w WHERE s1 < 4) + SELECT * FROM w); + ]], { + -- + -- + }) + +test:do_execsql_test( + "view-24.5", + [[ + DROP VIEW v; + CREATE VIEW v AS SELECT * FROM ( + SELECT * FROM ( + WITH RECURSIVE w AS ( + SELECT s1 FROM ts + UNION ALL + SELECT s1+1 FROM w WHERE s1 < 4) + SELECT * FROM w)); + ]], { + -- + -- + }) + +test:do_execsql_test( + "view-24.6", + [[ + DROP VIEW v; + CREATE VIEW v AS SELECT * FROM + (SELECT 1), + (SELECT 2) JOIN + (WITH RECURSIVE w AS ( + SELECT s1 FROM ts + UNION ALL + SELECT s1+1 FROM w WHERE s1 < 4) + SELECT * FROM w); + ]], { + -- + -- + }) + +test:do_execsql_test( + "view-24.7", + [[ + DROP VIEW v; + DROP TABLE ts; + ]], { + -- + -- + }) + test:finish_test()