Skip to content

Commit f6ce81f

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 b76356a commit f6ce81f

File tree

10 files changed

+123
-21
lines changed

10 files changed

+123
-21
lines changed

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2494,12 +2494,12 @@ _copySelectStmt(const SelectStmt *from)
24942494
COPY_NODE_FIELD(groupClause);
24952495
COPY_NODE_FIELD(havingClause);
24962496
COPY_NODE_FIELD(windowClause);
2497-
COPY_NODE_FIELD(withClause);
24982497
COPY_NODE_FIELD(valuesLists);
24992498
COPY_NODE_FIELD(sortClause);
25002499
COPY_NODE_FIELD(limitOffset);
25012500
COPY_NODE_FIELD(limitCount);
25022501
COPY_NODE_FIELD(lockingClause);
2502+
COPY_NODE_FIELD(withClause);
25032503
COPY_SCALAR_FIELD(op);
25042504
COPY_SCALAR_FIELD(all);
25052505
COPY_NODE_FIELD(larg);

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -976,12 +976,12 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b)
976976
COMPARE_NODE_FIELD(groupClause);
977977
COMPARE_NODE_FIELD(havingClause);
978978
COMPARE_NODE_FIELD(windowClause);
979-
COMPARE_NODE_FIELD(withClause);
980979
COMPARE_NODE_FIELD(valuesLists);
981980
COMPARE_NODE_FIELD(sortClause);
982981
COMPARE_NODE_FIELD(limitOffset);
983982
COMPARE_NODE_FIELD(limitCount);
984983
COMPARE_NODE_FIELD(lockingClause);
984+
COMPARE_NODE_FIELD(withClause);
985985
COMPARE_SCALAR_FIELD(op);
986986
COMPARE_SCALAR_FIELD(all);
987987
COMPARE_NODE_FIELD(larg);

‎src/backend/nodes/nodeFuncs.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2909,8 +2909,6 @@ raw_expression_tree_walker(Node *node,
29092909
return true;
29102910
if (walker(stmt->windowClause, context))
29112911
return true;
2912-
if (walker(stmt->withClause, context))
2913-
return true;
29142912
if (walker(stmt->valuesLists, context))
29152913
return true;
29162914
if (walker(stmt->sortClause, context))
@@ -2921,6 +2919,8 @@ raw_expression_tree_walker(Node *node,
29212919
return true;
29222920
if (walker(stmt->lockingClause, context))
29232921
return true;
2922+
if (walker(stmt->withClause, context))
2923+
return true;
29242924
if (walker(stmt->larg, context))
29252925
return true;
29262926
if (walker(stmt->rarg, context))

‎src/backend/nodes/outfuncs.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2037,12 +2037,12 @@ _outSelectStmt(StringInfo str, const SelectStmt *node)
20372037
WRITE_NODE_FIELD(groupClause);
20382038
WRITE_NODE_FIELD(havingClause);
20392039
WRITE_NODE_FIELD(windowClause);
2040-
WRITE_NODE_FIELD(withClause);
20412040
WRITE_NODE_FIELD(valuesLists);
20422041
WRITE_NODE_FIELD(sortClause);
20432042
WRITE_NODE_FIELD(limitOffset);
20442043
WRITE_NODE_FIELD(limitCount);
20452044
WRITE_NODE_FIELD(lockingClause);
2045+
WRITE_NODE_FIELD(withClause);
20462046
WRITE_ENUM_FIELD(op, SetOperation);
20472047
WRITE_BOOL_FIELD(all);
20482048
WRITE_NODE_FIELD(larg);

‎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/backend/parser/parse_type.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -705,12 +705,12 @@ parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p)
705705
stmt->groupClause != NIL ||
706706
stmt->havingClause != NULL ||
707707
stmt->windowClause != NIL ||
708-
stmt->withClause != NULL ||
709708
stmt->valuesLists != NIL ||
710709
stmt->sortClause != NIL ||
711710
stmt->limitOffset != NULL ||
712711
stmt->limitCount != NULL ||
713712
stmt->lockingClause != NIL ||
713+
stmt->withClause != NULL ||
714714
stmt->op != SETOP_NONE)
715715
goto fail;
716716
if (list_length(stmt->targetList) != 1)

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1016,7 +1016,6 @@ typedef struct SelectStmt
10161016
List *groupClause; /* GROUP BY clauses */
10171017
Node *havingClause; /* HAVING conditional-expression */
10181018
List *windowClause; /* WINDOW window_name AS (...), ... */
1019-
WithClause *withClause; /* WITH clause */
10201019

10211020
/*
10221021
* In a "leaf" node representing a VALUES list, the above fields are all
@@ -1036,6 +1035,7 @@ typedef struct SelectStmt
10361035
Node *limitOffset; /* # of result tuples to skip */
10371036
Node *limitCount; /* # of result tuples to return */
10381037
List *lockingClause; /* FOR UPDATE (list of LockingClause's) */
1038+
WithClause *withClause; /* WITH clause */
10391039

10401040
/*
10411041
* These fields are used only in upper-level SelectStmts.

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

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

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

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