Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb1c891e

Browse files
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 anintermediate-level set operation; it only handled WITH at the top levelor WITH attached to a leaf-level SELECT. Per report from Adam Mackler.In HEAD, I rearranged the order of SelectStmt's fields to put withClausewith the other fields that can appear on non-leaf SelectStmts. In backbranches, leave it alone to avoid a possible ABI break for third-partycode.Back-patch to 8.4 where WITH support was added.
1 parent118b941 commitb1c891e

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,orFOR 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 WITHclauses attached, we need to treat it like a leaf node to
1542+
*generate anindependent sub-Query tree.Otherwise, it can be
1543+
*represented by aSetOperationStmt 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
caseSETOP_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+
SELECT1
548+
UNION (WITH innermostas (SELECT2)
549+
SELECT*FROM innermost
550+
UNIONSELECT3)
551+
)
552+
SELECT*FROM outermost;
553+
554+
WITH outermost(x)AS (
555+
SELECT1
556+
UNION (WITH innermostas (SELECT2)
557+
SELECT*FROM outermost-- fail
558+
UNIONSELECT*FROM innermost)
559+
)
560+
SELECT*FROM outermost;
561+
562+
WITH RECURSIVE outermost(x)AS (
563+
SELECT1
564+
UNION (WITH innermostas (SELECT2)
565+
SELECT*FROM outermost
566+
UNIONSELECT*FROM innermost)
567+
)
568+
SELECT*FROM outermost;
569+
570+
WITH RECURSIVE outermost(x)AS (
571+
WITH innermostas (SELECT2FROM outermost)-- fail
572+
SELECT*FROM innermost
573+
UNIONSELECT*from outermost
574+
)
575+
SELECT*FROM outermost;
576+
542577
--
543578
-- Data-modifying statements in WITH
544579
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp