Skip to content

Commit 3786b9b

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 63aba79 commit 3786b9b

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
@@ -1322,6 +1322,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
13221322
Node *limitOffset;
13231323
Node *limitCount;
13241324
List *lockingClause;
1325+
WithClause *withClause;
13251326
Node *node;
13261327
ListCell *left_tlist,
13271328
*lct,
@@ -1338,14 +1339,6 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
13381339

13391340
qry->commandType = CMD_SELECT;
13401341

1341-
/* process the WITH clause independently of all else */
1342-
if (stmt->withClause)
1343-
{
1344-
qry->hasRecursive = stmt->withClause->recursive;
1345-
qry->cteList = transformWithClause(pstate, stmt->withClause);
1346-
qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
1347-
}
1348-
13491342
/*
13501343
* Find leftmost leaf SelectStmt. We currently only need to do this in
13511344
* order to deliver a suitable error message if there's an INTO clause
@@ -1375,18 +1368,28 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
13751368
limitOffset = stmt->limitOffset;
13761369
limitCount = stmt->limitCount;
13771370
lockingClause = stmt->lockingClause;
1371+
withClause = stmt->withClause;
13781372

13791373
stmt->sortClause = NIL;
13801374
stmt->limitOffset = NULL;
13811375
stmt->limitCount = NULL;
13821376
stmt->lockingClause = NIL;
1377+
stmt->withClause = NULL;
13831378

13841379
/* We don't support FOR UPDATE/SHARE with set ops at the moment. */
13851380
if (lockingClause)
13861381
ereport(ERROR,
13871382
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
13881383
errmsg("SELECT FOR UPDATE/SHARE is not allowed with UNION/INTERSECT/EXCEPT")));
13891384

1385+
/* Process the WITH clause independently of all else */
1386+
if (withClause)
1387+
{
1388+
qry->hasRecursive = withClause->recursive;
1389+
qry->cteList = transformWithClause(pstate, withClause);
1390+
qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
1391+
}
1392+
13901393
/*
13911394
* Recursively transform the components of the tree.
13921395
*/
@@ -1572,10 +1575,10 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
15721575
errmsg("SELECT FOR UPDATE/SHARE is not allowed with UNION/INTERSECT/EXCEPT")));
15731576

15741577
/*
1575-
* If an internal node of a set-op tree has ORDER BY, LIMIT, or FOR UPDATE
1576-
* clauses attached, we need to treat it like a leaf node to generate an
1577-
* independent sub-Query tree. Otherwise, it can be represented by a
1578-
* SetOperationStmt node underneath the parent Query.
1578+
* If an internal node of a set-op tree has ORDER BY, LIMIT, FOR UPDATE,
1579+
* or WITH clauses attached, we need to treat it like a leaf node to
1580+
* generate an independent sub-Query tree. Otherwise, it can be
1581+
* represented by a SetOperationStmt node underneath the parent Query.
15791582
*/
15801583
if (stmt->op == SETOP_NONE)
15811584
{
@@ -1586,7 +1589,7 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
15861589
{
15871590
Assert(stmt->larg != NULL && stmt->rarg != NULL);
15881591
if (stmt->sortClause || stmt->limitOffset || stmt->limitCount ||
1589-
stmt->lockingClause)
1592+
stmt->lockingClause || stmt->withClause)
15901593
isLeaf = true;
15911594
else
15921595
isLeaf = false;

‎src/backend/parser/parse_cte.c

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

681+
/* WITH mustn't contain self-reference, either */
682+
if (stmt->withClause)
683+
{
684+
cstate->curitem = i;
685+
cstate->innerwiths = NIL;
686+
cstate->selfrefcount = 0;
687+
cstate->context = RECURSION_SUBLINK;
688+
checkWellFormedRecursionWalker((Node *) stmt->withClause->ctes,
689+
cstate);
690+
Assert(cstate->innerwiths == NIL);
691+
}
692+
681693
/*
682694
* Disallow ORDER BY and similar decoration atop the UNION. These
683695
* don't make sense because it's impossible to figure out what they
@@ -933,7 +945,7 @@ checkWellFormedSelectStmt(SelectStmt *stmt, CteState *cstate)
933945
cstate);
934946
checkWellFormedRecursionWalker((Node *) stmt->lockingClause,
935947
cstate);
936-
break;
948+
/* stmt->withClause is intentionally ignored here */
937949
break;
938950
case SETOP_EXCEPT:
939951
if (stmt->all)
@@ -952,6 +964,7 @@ checkWellFormedSelectStmt(SelectStmt *stmt, CteState *cstate)
952964
cstate);
953965
checkWellFormedRecursionWalker((Node *) stmt->lockingClause,
954966
cstate);
967+
/* stmt->withClause is intentionally ignored here */
955968
break;
956969
default:
957970
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
@@ -1160,6 +1160,57 @@ SELECT * FROM t;
11601160
10
11611161
(55 rows)
11621162

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

‎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)