From 1e14aa6e30b3bee7ae71ebb15452334de9954a22 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 31 Jul 2012 17:56:42 -0400 Subject: [PATCH] Fix WITH attached to a nested set operation (UNION/INTERSECT/EXCEPT). Parse analysis neglected to cover the case of a WITH clause attached to an intermediate-level set operation; it only handled WITH at the top level or WITH attached to a leaf-level SELECT. Per report from Adam Mackler. In HEAD, I rearranged the order of SelectStmt's fields to put withClause with the other fields that can appear on non-leaf SelectStmts. In back branches, leave it alone to avoid a possible ABI break for third-party code. Back-patch to 8.4 where WITH support was added. --- src/backend/parser/analyze.c | 21 ++++++------ src/backend/parser/parse_cte.c | 15 ++++++++- src/test/regress/expected/with.out | 51 ++++++++++++++++++++++++++++++ src/test/regress/sql/with.sql | 35 ++++++++++++++++++++ 4 files changed, 112 insertions(+), 10 deletions(-) diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 1fb115578bc..66ac4a36288 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -1151,6 +1151,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt) Node *limitOffset; Node *limitCount; List *lockingClause; + WithClause *withClause; Node *node; ListCell *left_tlist, *lct, @@ -1193,11 +1194,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) @@ -1205,11 +1208,11 @@ 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 */ - if (stmt->withClause) + /* Process the WITH clause independently of all else */ + if (withClause) { - qry->hasRecursive = stmt->withClause->recursive; - qry->cteList = transformWithClause(pstate, stmt->withClause); + qry->hasRecursive = withClause->recursive; + qry->cteList = transformWithClause(pstate, withClause); } /* @@ -1400,10 +1403,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, UPDATE, or LIMIT - * 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) { @@ -1414,7 +1417,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 41c913c869a..10ec1fe18b1 100644 --- a/src/backend/parser/parse_cte.c +++ b/src/backend/parser/parse_cte.c @@ -627,6 +627,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 @@ -882,7 +894,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) @@ -901,6 +913,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 a3e94e93d49..7db4d7cac75 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -1026,3 +1026,54 @@ SELECT * FROM t; 10 (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) + ^ diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index 2cbaa42492f..e74ebd76888 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -500,3 +500,38 @@ WITH RECURSIVE t(j) AS ( SELECT j+1 FROM t WHERE j < 10 ) 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; -- 2.39.5