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

[Roman Khabibov]

> On Aug 20, 2019, at 22:41, n.pettik <korablev at 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 at 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()