aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/parser/analyze.c29
-rw-r--r--src/backend/parser/parse_cte.c15
-rw-r--r--src/test/regress/expected/with.out51
-rw-r--r--src/test/regress/sql/with.sql35
4 files changed, 116 insertions, 14 deletions
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index e4a4e3a5e48..114454951d1 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1273,6 +1273,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
Node *limitOffset;
Node *limitCount;
List *lockingClause;
+ WithClause *withClause;
Node *node;
ListCell *left_tlist,
*lct,
@@ -1289,14 +1290,6 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
qry->commandType = CMD_SELECT;
- /* process the WITH clause independently of all else */
- if (stmt->withClause)
- {
- qry->hasRecursive = stmt->withClause->recursive;
- qry->cteList = transformWithClause(pstate, stmt->withClause);
- qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
- }
-
/*
* Find leftmost leaf SelectStmt; extract the one-time-only items from it
* and from the top-level node.
@@ -1324,11 +1317,13 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
limitOffset = stmt->limitOffset;
limitCount = stmt->limitCount;
lockingClause = stmt->lockingClause;
+ withClause = stmt->withClause;
stmt->sortClause = NIL;
stmt->limitOffset = NULL;
stmt->limitCount = NULL;
stmt->lockingClause = NIL;
+ stmt->withClause = NULL;
/* We don't support FOR UPDATE/SHARE with set ops at the moment. */
if (lockingClause)
@@ -1336,6 +1331,14 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("SELECT FOR UPDATE/SHARE is not allowed with UNION/INTERSECT/EXCEPT")));
+ /* Process the WITH clause independently of all else */
+ if (withClause)
+ {
+ qry->hasRecursive = withClause->recursive;
+ qry->cteList = transformWithClause(pstate, withClause);
+ qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
+ }
+
/*
* Recursively transform the components of the tree.
*/
@@ -1534,10 +1537,10 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
errmsg("SELECT FOR UPDATE/SHARE is not allowed with UNION/INTERSECT/EXCEPT")));
/*
- * If an internal node of a set-op tree has ORDER BY, LIMIT, or FOR UPDATE
- * clauses attached, we need to treat it like a leaf node to generate an
- * independent sub-Query tree. Otherwise, it can be represented by a
- * SetOperationStmt node underneath the parent Query.
+ * If an internal node of a set-op tree has ORDER BY, LIMIT, FOR UPDATE,
+ * or WITH clauses attached, we need to treat it like a leaf node to
+ * generate an independent sub-Query tree. Otherwise, it can be
+ * represented by a SetOperationStmt node underneath the parent Query.
*/
if (stmt->op == SETOP_NONE)
{
@@ -1548,7 +1551,7 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
{
Assert(stmt->larg != NULL && stmt->rarg != NULL);
if (stmt->sortClause || stmt->limitOffset || stmt->limitCount ||
- stmt->lockingClause)
+ stmt->lockingClause || stmt->withClause)
isLeaf = true;
else
isLeaf = false;
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c
index ec6afd83b6c..ef96df603c1 100644
--- a/src/backend/parser/parse_cte.c
+++ b/src/backend/parser/parse_cte.c
@@ -685,6 +685,18 @@ checkWellFormedRecursion(CteState *cstate)
if (cstate->selfrefcount != 1) /* shouldn't happen */
elog(ERROR, "missing recursive reference");
+ /* WITH mustn't contain self-reference, either */
+ if (stmt->withClause)
+ {
+ cstate->curitem = i;
+ cstate->innerwiths = NIL;
+ cstate->selfrefcount = 0;
+ cstate->context = RECURSION_SUBLINK;
+ checkWellFormedRecursionWalker((Node *) stmt->withClause->ctes,
+ cstate);
+ Assert(cstate->innerwiths == NIL);
+ }
+
/*
* Disallow ORDER BY and similar decoration atop the UNION. These
* don't make sense because it's impossible to figure out what they
@@ -940,7 +952,7 @@ checkWellFormedSelectStmt(SelectStmt *stmt, CteState *cstate)
cstate);
checkWellFormedRecursionWalker((Node *) stmt->lockingClause,
cstate);
- break;
+ /* stmt->withClause is intentionally ignored here */
break;
case SETOP_EXCEPT:
if (stmt->all)
@@ -959,6 +971,7 @@ checkWellFormedSelectStmt(SelectStmt *stmt, CteState *cstate)
cstate);
checkWellFormedRecursionWalker((Node *) stmt->lockingClause,
cstate);
+ /* stmt->withClause is intentionally ignored here */
break;
default:
elog(ERROR, "unrecognized set op: %d",
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index d8c42896cd0..a01f658c163 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -1160,6 +1160,57 @@ SELECT * FROM t;
(55 rows)
--
+-- test WITH attached to intermediate-level set operation
+--
+WITH outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost as (SELECT 2)
+ SELECT * FROM innermost
+ UNION SELECT 3)
+)
+SELECT * FROM outermost;
+ x
+---
+ 1
+ 2
+ 3
+(3 rows)
+
+WITH outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost as (SELECT 2)
+ SELECT * FROM outermost -- fail
+ UNION SELECT * FROM innermost)
+)
+SELECT * FROM outermost;
+ERROR: relation "outermost" does not exist
+LINE 4: SELECT * FROM outermost
+ ^
+DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
+HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
+WITH RECURSIVE outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost as (SELECT 2)
+ SELECT * FROM outermost
+ UNION SELECT * FROM innermost)
+)
+SELECT * FROM outermost;
+ x
+---
+ 1
+ 2
+(2 rows)
+
+WITH RECURSIVE outermost(x) AS (
+ WITH innermost as (SELECT 2 FROM outermost) -- fail
+ SELECT * FROM innermost
+ UNION SELECT * from outermost
+)
+SELECT * FROM outermost;
+ERROR: recursive reference to query "outermost" must not appear within a subquery
+LINE 2: WITH innermost as (SELECT 2 FROM outermost)
+ ^
+--
-- Data-modifying statements in WITH
--
-- INSERT ... RETURNING
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 9c6732b9611..22fdddc4eea 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -540,6 +540,41 @@ WITH RECURSIVE t(j) AS (
SELECT * FROM t;
--
+-- test WITH attached to intermediate-level set operation
+--
+
+WITH outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost as (SELECT 2)
+ SELECT * FROM innermost
+ UNION SELECT 3)
+)
+SELECT * FROM outermost;
+
+WITH outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost as (SELECT 2)
+ SELECT * FROM outermost -- fail
+ UNION SELECT * FROM innermost)
+)
+SELECT * FROM outermost;
+
+WITH RECURSIVE outermost(x) AS (
+ SELECT 1
+ UNION (WITH innermost as (SELECT 2)
+ SELECT * FROM outermost
+ UNION SELECT * FROM innermost)
+)
+SELECT * FROM outermost;
+
+WITH RECURSIVE outermost(x) AS (
+ WITH innermost as (SELECT 2 FROM outermost) -- fail
+ SELECT * FROM innermost
+ UNION SELECT * from outermost
+)
+SELECT * FROM outermost;
+
+--
-- Data-modifying statements in WITH
--
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 07:44:10 +0000