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

Commitf6ce81f

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 parentb76356a commitf6ce81f

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,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/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
gotofail;
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+
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