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

Commit3786b9b

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 parent63aba79 commit3786b9b

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,orFOR 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 WITHclauses attached, we need to treat it like a leaf node to
1580+
*generate anindependent sub-Query tree.Otherwise, it can be
1581+
*represented by aSetOperationStmt 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
caseSETOP_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+
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