From f819020d400f1dbd96ee1a9fd44a1f6f44932b4d Mon Sep 17 00:00:00 2001
From: Tom Lane
Date: Sat, 23 Apr 2022 12:16:12 -0400
Subject: Fix incautious CTE matching in rewriteSearchAndCycle().

This function looks for a reference to the recursive WITH CTE,
but it checked only the CTE name not ctelevelsup, so that it could
seize on a lower CTE that happened to have the same name.  This
would result in planner failures later, either weird errors such as
"could not find attribute 2 in subquery targetlist", or crashes
or assertion failures.  The code also merely Assert'ed that it found
a matching entry, which is not guaranteed at all by the parser.

Per bugs #17320 and #17318 from Zhiyong Wu.
Thanks to Kyotaro Horiguchi for investigation.

Discussion: https://postgr.es/m/17320-70e37868182512ab@postgresql.org
Discussion: https://postgr.es/m/17318-2eb65a3a611d2368@postgresql.org
---
 src/backend/rewrite/rewriteSearchCycle.c | 19 ++++++++++++++++---
 1 file changed, 16 insertions(+), 3 deletions(-)

(limited to 'src/backend/rewrite')

diff --git a/src/backend/rewrite/rewriteSearchCycle.c b/src/backend/rewrite/rewriteSearchCycle.c
index dc408404ebe..58f684cd52e 100644
--- a/src/backend/rewrite/rewriteSearchCycle.c
+++ b/src/backend/rewrite/rewriteSearchCycle.c
@@ -383,19 +383,32 @@ rewriteSearchAndCycle(CommonTableExpr *cte)
 	newrte->eref = newrte->alias;
 
 	/*
-	 * Find the reference to our CTE in the range table
+	 * Find the reference to the recursive CTE in the right UNION subquery's
+	 * range table.  We expect it to be two levels up from the UNION subquery
+	 * (and must check that to avoid being fooled by sub-WITHs with the same
+	 * CTE name).  There will not be more than one such reference, because the
+	 * parser would have rejected that (see checkWellFormedRecursion() in
+	 * parse_cte.c).  However, the parser doesn't insist that the reference
+	 * appear in the UNION subquery's topmost range table, so we might fail to
+	 * find it at all.  That's an unimplemented case for the moment.
 	 */
 	for (int rti = 1; rti <= list_length(rte2->subquery->rtable); rti++)
 	{
 		RangeTblEntry *e = rt_fetch(rti, rte2->subquery->rtable);
 
-		if (e->rtekind == RTE_CTE && strcmp(cte->ctename, e->ctename) == 0)
+		if (e->rtekind == RTE_CTE &&
+			strcmp(cte->ctename, e->ctename) == 0 &&
+			e->ctelevelsup == 2)
 		{
 			cte_rtindex = rti;
 			break;
 		}
 	}
-	Assert(cte_rtindex > 0);
+	if (cte_rtindex <= 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+				 errmsg("with a SEARCH or CYCLE clause, the recursive reference to WITH query \"%s\" must be at the top level of its right-hand SELECT",
+						cte->ctename)));
 
 	newsubquery = copyObject(rte2->subquery);
 	IncrementVarSublevelsUp((Node *) newsubquery, 1, 1);
-- 
cgit v1.2.3