From: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
Subject: [PATCH] Reject indirect reference to CTEs with SEARCH or CYCLE clause
Date: Tue, 7 Dec 2021 16:13:13 +0900
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(+)
inline patch 1: src/backend/parser/parse_relation.c, src/test/regress/expected/with.out, src/test/regress/sql/with.sql (+31/-0 lines) download inline-1.patchdiff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.cindex 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.outindex 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.sqlindex 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)----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: horikyota.ntt@gmail.com
Subject: Re: [PATCH] Reject indirect reference to CTEs with SEARCH or CYCLE clause
In-Reply-To: <no-message-id-31145@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox