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

Commitae47eb1

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 parent706f792 commitae47eb1

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,orFOR 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 WITHclauses attached, we need to treat it like a leaf node to
1402+
*generate anindependent sub-Query tree.Otherwise, it can be
1403+
*represented by aSetOperationStmt 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
caseSETOP_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+1FROM tWHERE 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+
SELECT1
510+
UNION (WITH innermostas (SELECT2)
511+
SELECT*FROM innermost
512+
UNIONSELECT3)
513+
)
514+
SELECT*FROM outermost;
515+
516+
WITH outermost(x)AS (
517+
SELECT1
518+
UNION (WITH innermostas (SELECT2)
519+
SELECT*FROM outermost-- fail
520+
UNIONSELECT*FROM innermost)
521+
)
522+
SELECT*FROM outermost;
523+
524+
WITH RECURSIVE outermost(x)AS (
525+
SELECT1
526+
UNION (WITH innermostas (SELECT2)
527+
SELECT*FROM outermost
528+
UNIONSELECT*FROM innermost)
529+
)
530+
SELECT*FROM outermost;
531+
532+
WITH RECURSIVE outermost(x)AS (
533+
WITH innermostas (SELECT2FROM outermost)-- fail
534+
SELECT*FROM innermost
535+
UNIONSELECT*from outermost
536+
)
537+
SELECT*FROM outermost;

0 commit comments

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