Skip to content

Commit b1c891e

Browse files
tglsfdctglsfdc
committed
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.
1 parent 118b941 commit b1c891e

File tree

4 files changed

+116
-14
lines changed

4 files changed

+116
-14
lines changed

‎src/backend/parser/analyze.c

Lines changed: 16 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1273,6 +1273,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
12731273
Node *limitOffset;
12741274
Node *limitCount;
12751275
List *lockingClause;
1276+
WithClause *withClause;
12761277
Node *node;
12771278
ListCell *left_tlist,
12781279
*lct,
@@ -1289,14 +1290,6 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
12891290

12901291
qry->commandType = CMD_SELECT;
12911292

1292-
/* process the WITH clause independently of all else */
1293-
if (stmt->withClause)
1294-
{
1295-
qry->hasRecursive = stmt->withClause->recursive;
1296-
qry->cteList = transformWithClause(pstate, stmt->withClause);
1297-
qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
1298-
}
1299-
13001293
/*
13011294
* Find leftmost leaf SelectStmt; extract the one-time-only items from it
13021295
* and from the top-level node.
@@ -1324,18 +1317,28 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
13241317
limitOffset = stmt->limitOffset;
13251318
limitCount = stmt->limitCount;
13261319
lockingClause = stmt->lockingClause;
1320+
withClause = stmt->withClause;
13271321

13281322
stmt->sortClause = NIL;
13291323
stmt->limitOffset = NULL;
13301324
stmt->limitCount = NULL;
13311325
stmt->lockingClause = NIL;
1326+
stmt->withClause = NULL;
13321327

13331328
/* We don't support FOR UPDATE/SHARE with set ops at the moment. */
13341329
if (lockingClause)
13351330
ereport(ERROR,
13361331
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13371332
errmsg("SELECT FOR UPDATE/SHARE is not allowed with UNION/INTERSECT/EXCEPT")));
13381333

1334+
/* Process the WITH clause independently of all else */
1335+
if (withClause)
1336+
{
1337+
qry->hasRecursive = withClause->recursive;
1338+
qry->cteList = transformWithClause(pstate, withClause);
1339+
qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
1340+
}
1341+
13391342
/*
13401343
* Recursively transform the components of the tree.
13411344
*/
@@ -1534,10 +1537,10 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
15341537
errmsg("SELECT FOR UPDATE/SHARE is not allowed with UNION/INTERSECT/EXCEPT")));
15351538

15361539
/*
1537-
* If an internal node of a set-op tree has ORDER BY, LIMIT, or FOR UPDATE
1538-
* clauses attached, we need to treat it like a leaf node to generate an
1539-
* independent sub-Query tree. Otherwise, it can be represented by a
1540-
* SetOperationStmt node underneath the parent Query.
1540+
* If an internal node of a set-op tree has ORDER BY, LIMIT, FOR UPDATE,
1541+
* or WITH clauses attached, we need to treat it like a leaf node to
1542+
* generate an independent sub-Query tree. Otherwise, it can be
1543+
* represented by a SetOperationStmt node underneath the parent Query.
15411544
*/
15421545
if (stmt->op == SETOP_NONE)
15431546
{
@@ -1548,7 +1551,7 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
15481551
{
15491552
Assert(stmt->larg != NULL && stmt->rarg != NULL);
15501553
if (stmt->sortClause || stmt->limitOffset || stmt->limitCount ||
1551-
stmt->lockingClause)
1554+
stmt->lockingClause || stmt->withClause)
15521555
isLeaf = true;
15531556
else
15541557
isLeaf = false;

‎src/backend/parser/parse_cte.c

Lines changed: 14 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -685,6 +685,18 @@ checkWellFormedRecursion(CteState *cstate)
685685
if (cstate->selfrefcount != 1) /* shouldn't happen */
686686
elog(ERROR, "missing recursive reference");
687687

688+
/* WITH mustn't contain self-reference, either */
689+
if (stmt->withClause)
690+
{
691+
cstate->curitem = i;
692+
cstate->innerwiths = NIL;
693+
cstate->selfrefcount = 0;
694+
cstate->context = RECURSION_SUBLINK;
695+
checkWellFormedRecursionWalker((Node *) stmt->withClause->ctes,
696+
cstate);
697+
Assert(cstate->innerwiths == NIL);
698+
}
699+
688700
/*
689701
* Disallow ORDER BY and similar decoration atop the UNION. These
690702
* don't make sense because it's impossible to figure out what they
@@ -940,7 +952,7 @@ checkWellFormedSelectStmt(SelectStmt *stmt, CteState *cstate)
940952
cstate);
941953
checkWellFormedRecursionWalker((Node *) stmt->lockingClause,
942954
cstate);
943-
break;
955+
/* stmt->withClause is intentionally ignored here */
944956
break;
945957
case SETOP_EXCEPT:
946958
if (stmt->all)
@@ -959,6 +971,7 @@ checkWellFormedSelectStmt(SelectStmt *stmt, CteState *cstate)
959971
cstate);
960972
checkWellFormedRecursionWalker((Node *) stmt->lockingClause,
961973
cstate);
974+
/* stmt->withClause is intentionally ignored here */
962975
break;
963976
default:
964977
elog(ERROR, "unrecognized set op: %d",

‎src/test/regress/expected/with.out

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1159,6 +1159,57 @@ SELECT * FROM t;
11591159
10
11601160
(55 rows)
11611161

1162+
--
1163+
-- test WITH attached to intermediate-level set operation
1164+
--
1165+
WITH outermost(x) AS (
1166+
SELECT 1
1167+
UNION (WITH innermost as (SELECT 2)
1168+
SELECT * FROM innermost
1169+
UNION SELECT 3)
1170+
)
1171+
SELECT * FROM outermost;
1172+
x
1173+
---
1174+
1
1175+
2
1176+
3
1177+
(3 rows)
1178+
1179+
WITH outermost(x) AS (
1180+
SELECT 1
1181+
UNION (WITH innermost as (SELECT 2)
1182+
SELECT * FROM outermost -- fail
1183+
UNION SELECT * FROM innermost)
1184+
)
1185+
SELECT * FROM outermost;
1186+
ERROR: relation "outermost" does not exist
1187+
LINE 4: SELECT * FROM outermost
1188+
^
1189+
DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
1190+
HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
1191+
WITH RECURSIVE outermost(x) AS (
1192+
SELECT 1
1193+
UNION (WITH innermost as (SELECT 2)
1194+
SELECT * FROM outermost
1195+
UNION SELECT * FROM innermost)
1196+
)
1197+
SELECT * FROM outermost;
1198+
x
1199+
---
1200+
1
1201+
2
1202+
(2 rows)
1203+
1204+
WITH RECURSIVE outermost(x) AS (
1205+
WITH innermost as (SELECT 2 FROM outermost) -- fail
1206+
SELECT * FROM innermost
1207+
UNION SELECT * from outermost
1208+
)
1209+
SELECT * FROM outermost;
1210+
ERROR: recursive reference to query "outermost" must not appear within a subquery
1211+
LINE 2: WITH innermost as (SELECT 2 FROM outermost)
1212+
^
11621213
--
11631214
-- Data-modifying statements in WITH
11641215
--

‎src/test/regress/sql/with.sql

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -539,6 +539,41 @@ WITH RECURSIVE t(j) AS (
539539
)
540540
SELECT * FROM t;
541541

542+
--
543+
-- test WITH attached to intermediate-level set operation
544+
--
545+
546+
WITH outermost(x) AS (
547+
SELECT 1
548+
UNION (WITH innermost as (SELECT 2)
549+
SELECT * FROM innermost
550+
UNION SELECT 3)
551+
)
552+
SELECT * FROM outermost;
553+
554+
WITH outermost(x) AS (
555+
SELECT 1
556+
UNION (WITH innermost as (SELECT 2)
557+
SELECT * FROM outermost -- fail
558+
UNION SELECT * FROM innermost)
559+
)
560+
SELECT * FROM outermost;
561+
562+
WITH RECURSIVE outermost(x) AS (
563+
SELECT 1
564+
UNION (WITH innermost as (SELECT 2)
565+
SELECT * FROM outermost
566+
UNION SELECT * FROM innermost)
567+
)
568+
SELECT * FROM outermost;
569+
570+
WITH RECURSIVE outermost(x) AS (
571+
WITH innermost as (SELECT 2 FROM outermost) -- fail
572+
SELECT * FROM innermost
573+
UNION SELECT * from outermost
574+
)
575+
SELECT * FROM outermost;
576+
542577
--
543578
-- Data-modifying statements in WITH
544579
--

0 commit comments

Comments
 (0)