From: Kyotaro Horiguchi Date: Tue, 7 Dec 2021 16:13:13 +0900 Subject: [PATCH] Reject indirect reference to CTEs with SEARCH or CYCLE clause SEARCH and CYCLE clauses adds an implicit column to the recursively referencing quireies then expects the column in the result from the immediate recursive query and we don't expect another level of CTE is inserted in-between. Reject indirect recursive references to CTEs that have SEARCH or CYCLE clause. --- src/backend/parser/parse_relation.c | 13 +++++++++++++ src/test/regress/expected/with.out | 9 +++++++++ src/test/regress/sql/with.sql | 9 +++++++++ 3 files changed, 31 insertions(+) diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index c5c3f26ecf..2cc497e0e7 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -263,6 +263,7 @@ scanNameSpaceForCTE(ParseState *pstate, const char *refname, Index *ctelevelsup) { Index levelsup; + CommonTableExpr *nearest_cte = NULL; for (levelsup = 0; pstate != NULL; @@ -270,12 +271,24 @@ scanNameSpaceForCTE(ParseState *pstate, const char *refname, { ListCell *lc; + if (!nearest_cte && pstate->p_parent_cte) + nearest_cte = pstate->p_parent_cte; + foreach(lc, pstate->p_ctenamespace) { CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc); if (strcmp(cte->ctename, refname) == 0) { + /* + * SEARCH and CYCLE clauses adds a hidden column which is not + * revealed to the upper levels. + */ + if (nearest_cte && nearest_cte != cte && + (cte->search_clause || cte->cycle_clause)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("indirectly referenced recursive CTE \"%s\" cannot have SEARCH or CYCLE clause", refname))); *ctelevelsup = levelsup; return cte; } diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index 75e61460d9..a06a0a37ae 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -846,6 +846,15 @@ with recursive search_graph(f, t, label) as ( ) search depth first by f, t set seq select * from search_graph order by seq; ERROR: with a SEARCH or CYCLE clause, the right side of the UNION must be a SELECT +with recursive search_graph(f, t, label) as ( + select * from graph0 g + union all + (with x as + (select * from search_graph g) + select * from x) +) search depth first by f, t set seq +select * from search_graph order by seq; +ERROR: indirectly referenced recursive CTE "search_graph" cannot have SEARCH or CYCLE clause -- test ruleutils and view expansion create temp view v_search as with recursive search_graph(f, t, label) as ( diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index 46668a903e..987ed4d11b 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -464,6 +464,15 @@ with recursive search_graph(f, t, label) as ( ) search depth first by f, t set seq select * from search_graph order by seq; +with recursive search_graph(f, t, label) as ( + select * from graph0 g + union all + (with x as + (select * from search_graph g) + select * from x) +) search depth first by f, t set seq +select * from search_graph order by seq; + -- test ruleutils and view expansion create temp view v_search as with recursive search_graph(f, t, label) as ( -- 2.27.0 ----Next_Part(Tue_Dec__7_17_25_46_2021_449)----