Skip to content

Commit ae47eb1

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 706f792 commit ae47eb1

File tree

4 files changed

+115
-13
lines changed

4 files changed

+115
-13
lines changed

‎src/backend/parser/analyze.c

Lines changed: 15 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -1144,6 +1144,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
11441144
Node *limitOffset;
11451145
Node *limitCount;
11461146
List *lockingClause;
1147+
WithClause *withClause;
11471148
Node *node;
11481149
ListCell *left_tlist,
11491150
*lct,
@@ -1159,13 +1160,6 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
11591160

11601161
qry->commandType = CMD_SELECT;
11611162

1162-
/* process the WITH clause independently of all else */
1163-
if (stmt->withClause)
1164-
{
1165-
qry->hasRecursive = stmt->withClause->recursive;
1166-
qry->cteList = transformWithClause(pstate, stmt->withClause);
1167-
}
1168-
11691163
/*
11701164
* Find leftmost leaf SelectStmt; extract the one-time-only items from it
11711165
* and from the top-level node.
@@ -1193,18 +1187,27 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
11931187
limitOffset = stmt->limitOffset;
11941188
limitCount = stmt->limitCount;
11951189
lockingClause = stmt->lockingClause;
1190+
withClause = stmt->withClause;
11961191

11971192
stmt->sortClause = NIL;
11981193
stmt->limitOffset = NULL;
11991194
stmt->limitCount = NULL;
12001195
stmt->lockingClause = NIL;
1196+
stmt->withClause = NULL;
12011197

12021198
/* We don't support FOR UPDATE/SHARE with set ops at the moment. */
12031199
if (lockingClause)
12041200
ereport(ERROR,
12051201
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
12061202
errmsg("SELECT FOR UPDATE/SHARE is not allowed with UNION/INTERSECT/EXCEPT")));
12071203

1204+
/* Process the WITH clause independently of all else */
1205+
if (withClause)
1206+
{
1207+
qry->hasRecursive = withClause->recursive;
1208+
qry->cteList = transformWithClause(pstate, withClause);
1209+
}
1210+
12081211
/*
12091212
* Recursively transform the components of the tree.
12101213
*/
@@ -1394,10 +1397,10 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
13941397
errmsg("SELECT FOR UPDATE/SHARE is not allowed with UNION/INTERSECT/EXCEPT")));
13951398

13961399
/*
1397-
* If an internal node of a set-op tree has ORDER BY, LIMIT, or FOR UPDATE
1398-
* clauses attached, we need to treat it like a leaf node to generate an
1399-
* independent sub-Query tree. Otherwise, it can be represented by a
1400-
* SetOperationStmt node underneath the parent Query.
1400+
* If an internal node of a set-op tree has ORDER BY, LIMIT, FOR UPDATE,
1401+
* or WITH clauses attached, we need to treat it like a leaf node to
1402+
* generate an independent sub-Query tree. Otherwise, it can be
1403+
* represented by a SetOperationStmt node underneath the parent Query.
14011404
*/
14021405
if (stmt->op == SETOP_NONE)
14031406
{
@@ -1408,7 +1411,7 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
14081411
{
14091412
Assert(stmt->larg != NULL && stmt->rarg != NULL);
14101413
if (stmt->sortClause || stmt->limitOffset || stmt->limitCount ||
1411-
stmt->lockingClause)
1414+
stmt->lockingClause || stmt->withClause)
14121415
isLeaf = true;
14131416
else
14141417
isLeaf = false;

‎src/backend/parser/parse_cte.c

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

630+
/* WITH mustn't contain self-reference, either */
631+
if (stmt->withClause)
632+
{
633+
cstate->curitem = i;
634+
cstate->innerwiths = NIL;
635+
cstate->selfrefcount = 0;
636+
cstate->context = RECURSION_SUBLINK;
637+
checkWellFormedRecursionWalker((Node *) stmt->withClause->ctes,
638+
cstate);
639+
Assert(cstate->innerwiths == NIL);
640+
}
641+
630642
/*
631643
* Disallow ORDER BY and similar decoration atop the UNION. These
632644
* don't make sense because it's impossible to figure out what they
@@ -882,7 +894,7 @@ checkWellFormedSelectStmt(SelectStmt *stmt, CteState *cstate)
882894
cstate);
883895
checkWellFormedRecursionWalker((Node *) stmt->lockingClause,
884896
cstate);
885-
break;
897+
/* stmt->withClause is intentionally ignored here */
886898
break;
887899
case SETOP_EXCEPT:
888900
if (stmt->all)
@@ -901,6 +913,7 @@ checkWellFormedSelectStmt(SelectStmt *stmt, CteState *cstate)
901913
cstate);
902914
checkWellFormedRecursionWalker((Node *) stmt->lockingClause,
903915
cstate);
916+
/* stmt->withClause is intentionally ignored here */
904917
break;
905918
default:
906919
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
@@ -1026,3 +1026,54 @@ SELECT * FROM t;
10261026
10
10271027
(55 rows)
10281028

1029+
--
1030+
-- test WITH attached to intermediate-level set operation
1031+
--
1032+
WITH outermost(x) AS (
1033+
SELECT 1
1034+
UNION (WITH innermost as (SELECT 2)
1035+
SELECT * FROM innermost
1036+
UNION SELECT 3)
1037+
)
1038+
SELECT * FROM outermost;
1039+
x
1040+
---
1041+
1
1042+
2
1043+
3
1044+
(3 rows)
1045+
1046+
WITH outermost(x) AS (
1047+
SELECT 1
1048+
UNION (WITH innermost as (SELECT 2)
1049+
SELECT * FROM outermost -- fail
1050+
UNION SELECT * FROM innermost)
1051+
)
1052+
SELECT * FROM outermost;
1053+
ERROR: relation "outermost" does not exist
1054+
LINE 4: SELECT * FROM outermost
1055+
^
1056+
DETAIL: There is a WITH item named "outermost", but it cannot be referenced from this part of the query.
1057+
HINT: Use WITH RECURSIVE, or re-order the WITH items to remove forward references.
1058+
WITH RECURSIVE outermost(x) AS (
1059+
SELECT 1
1060+
UNION (WITH innermost as (SELECT 2)
1061+
SELECT * FROM outermost
1062+
UNION SELECT * FROM innermost)
1063+
)
1064+
SELECT * FROM outermost;
1065+
x
1066+
---
1067+
1
1068+
2
1069+
(2 rows)
1070+
1071+
WITH RECURSIVE outermost(x) AS (
1072+
WITH innermost as (SELECT 2 FROM outermost) -- fail
1073+
SELECT * FROM innermost
1074+
UNION SELECT * from outermost
1075+
)
1076+
SELECT * FROM outermost;
1077+
ERROR: recursive reference to query "outermost" must not appear within a subquery
1078+
LINE 2: WITH innermost as (SELECT 2 FROM outermost)
1079+
^

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

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -500,3 +500,38 @@ WITH RECURSIVE t(j) AS (
500500
SELECT j+1 FROM t WHERE j < 10
501501
)
502502
SELECT * FROM t;
503+
504+
--
505+
-- test WITH attached to intermediate-level set operation
506+
--
507+
508+
WITH outermost(x) AS (
509+
SELECT 1
510+
UNION (WITH innermost as (SELECT 2)
511+
SELECT * FROM innermost
512+
UNION SELECT 3)
513+
)
514+
SELECT * FROM outermost;
515+
516+
WITH outermost(x) AS (
517+
SELECT 1
518+
UNION (WITH innermost as (SELECT 2)
519+
SELECT * FROM outermost -- fail
520+
UNION SELECT * FROM innermost)
521+
)
522+
SELECT * FROM outermost;
523+
524+
WITH RECURSIVE outermost(x) AS (
525+
SELECT 1
526+
UNION (WITH innermost as (SELECT 2)
527+
SELECT * FROM outermost
528+
UNION SELECT * FROM innermost)
529+
)
530+
SELECT * FROM outermost;
531+
532+
WITH RECURSIVE outermost(x) AS (
533+
WITH innermost as (SELECT 2 FROM outermost) -- fail
534+
SELECT * FROM innermost
535+
UNION SELECT * from outermost
536+
)
537+
SELECT * FROM outermost;

0 commit comments

Comments
 (0)