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: Wed, 28 Aug 2019 15:17:41 +0300 [thread overview] Message-ID: <1B494CAB-704F-4A62-A00D-257388DDB2B8@tarantool.org> (raw) In-Reply-To: <F2CF11ED-02DB-43EA-BA21-A8F38B47CA6F@tarantool.org> > On Aug 20, 2019, at 22:41, n.pettik <korablev@tarantool.org> 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 <roman.habibov@tarantool.org> Date: Mon Jul 29 18:00:34 2019 +0300 sql: allow to create view as <WITH> clause Allow views to use CTEs in <WITH> clauses, which can be in any (nested) select after <AS>. 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 = sql_src_list_entry_name(list, i); if (space_name == NULL) continue; + if (is_cte(select, space_name) == true) + continue; struct space *space = space_by_name(space_name); if (space == 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); +/** + * Check if @a name matches with at least one of CTE names typed + * in <WITH> 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; } +bool +is_cte(struct Select *select, const char *name) +{ + assert(select != NULL && name != NULL); + struct With *with = select->pWith; + if (with != NULL) { + if (memcmp(name, with->a->zName, strlen(name)) == 0) + return true; + } + struct SrcList *list = select->pSrc; + int item_count = sql_src_list_entry_count(list); + for (int i = 0; i < item_count; ++i) { + if (list->a[i].pSelect != NULL) + if (is_cte(list->a[i].pSelect, name) == 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 = require("sqltester") -test:plan(73) +test:plan(80) --!./tcltestrunner.lua -- 2002 February 26 @@ -1233,4 +1233,99 @@ test:do_catchsql_test( -- </view-23.8> }) +-- gh-4149: Check error message for view creation with (nested) +-- select with <WITH> clause. +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_execsql_test( + "view-24.2", + [[ + CREATE VIEW v AS WITH w(id) AS ( + SELECT 1) + SELECT * FROM ts; + ]], { + -- <view-24.2> + -- </view-24.2> + }) + +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; + ]], { + -- <view-24.3> + -- </view-24.3> + }) + +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); + ]], { + -- <view-24.4> + -- </view-24.4> + }) + +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)); + ]], { + -- <view-24.5> + -- </view-24.5> + }) + +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); + ]], { + -- <view-24.6> + -- </view-24.6> + }) + +test:do_execsql_test( + "view-24.7", + [[ + DROP VIEW v; + DROP TABLE ts; + ]], { + -- <view-24.7> + -- </view-24.7> + }) + test:finish_test()
next prev parent reply other threads:[~2019-08-28 12:20 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 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 [this message] 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=1B494CAB-704F-4A62-A00D-257388DDB2B8@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