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

Commitb020a86

Browse files
committed
Avoid unhelpful internal error for incorrect recursive-WITH queries.
checkWellFormedRecursion would issue "missing recursive reference"if a WITH RECURSIVE query contained a single self-reference butthat self-reference was inside a top-level WITH, ORDER BY, LIMIT,etc, rather than inside the second arm of the UNION as expected.We already intended to throw more-on-point errors for such cases,but those error checks must be done before examining the UNION armin order to have the desired results. So this patch need onlymove some code (and improve the comments).Per bug #18536 from Alexander Lakhin. Back-patch to all supportedbranches.Discussion:https://postgr.es/m/18536-0a342ec07901203e@postgresql.org
1 parent2423c84 commitb020a86

File tree

3 files changed

+102
-20
lines changed

3 files changed

+102
-20
lines changed

‎src/backend/parser/parse_cte.c

Lines changed: 33 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -659,25 +659,14 @@ checkWellFormedRecursion(CteState *cstate)
659659
cte->ctename),
660660
parser_errposition(cstate->pstate,cte->location)));
661661

662-
/* The left-hand operand mustn't contain self-reference at all */
663-
cstate->curitem=i;
664-
cstate->innerwiths=NIL;
665-
cstate->selfrefcount=0;
666-
cstate->context=RECURSION_NONRECURSIVETERM;
667-
checkWellFormedRecursionWalker((Node*)stmt->larg,cstate);
668-
Assert(cstate->innerwiths==NIL);
669-
670-
/* Right-hand operand should contain one reference in a valid place */
671-
cstate->curitem=i;
672-
cstate->innerwiths=NIL;
673-
cstate->selfrefcount=0;
674-
cstate->context=RECURSION_OK;
675-
checkWellFormedRecursionWalker((Node*)stmt->rarg,cstate);
676-
Assert(cstate->innerwiths==NIL);
677-
if (cstate->selfrefcount!=1)/* shouldn't happen */
678-
elog(ERROR,"missing recursive reference");
679-
680-
/* WITH mustn't contain self-reference, either */
662+
/*
663+
* Really, we should insist that there not be a top-level WITH, since
664+
* syntactically that would enclose the UNION. However, we've not
665+
* done so in the past and it's probably too late to change. Settle
666+
* for insisting that WITH not contain a self-reference. Test this
667+
* before examining the UNION arms, to avoid issuing confusing errors
668+
* in such cases.
669+
*/
681670
if (stmt->withClause)
682671
{
683672
cstate->curitem=i;
@@ -694,7 +683,9 @@ checkWellFormedRecursion(CteState *cstate)
694683
* don't make sense because it's impossible to figure out what they
695684
* mean when we have only part of the recursive query's results. (If
696685
* we did allow them, we'd have to check for recursive references
697-
* inside these subtrees.)
686+
* inside these subtrees. As for WITH, we have to do this before
687+
* examining the UNION arms, to avoid issuing confusing errors if
688+
* there is a recursive reference here.)
698689
*/
699690
if (stmt->sortClause)
700691
ereport(ERROR,
@@ -720,6 +711,28 @@ checkWellFormedRecursion(CteState *cstate)
720711
errmsg("FOR UPDATE/SHARE in a recursive query is not implemented"),
721712
parser_errposition(cstate->pstate,
722713
exprLocation((Node*)stmt->lockingClause))));
714+
715+
/*
716+
* Now we can get on with checking the UNION operands themselves.
717+
*
718+
* The left-hand operand mustn't contain a self-reference at all.
719+
*/
720+
cstate->curitem=i;
721+
cstate->innerwiths=NIL;
722+
cstate->selfrefcount=0;
723+
cstate->context=RECURSION_NONRECURSIVETERM;
724+
checkWellFormedRecursionWalker((Node*)stmt->larg,cstate);
725+
Assert(cstate->innerwiths==NIL);
726+
727+
/* Right-hand operand should contain one reference in a valid place */
728+
cstate->curitem=i;
729+
cstate->innerwiths=NIL;
730+
cstate->selfrefcount=0;
731+
cstate->context=RECURSION_OK;
732+
checkWellFormedRecursionWalker((Node*)stmt->rarg,cstate);
733+
Assert(cstate->innerwiths==NIL);
734+
if (cstate->selfrefcount!=1)/* shouldn't happen */
735+
elog(ERROR,"missing recursive reference");
723736
}
724737
}
725738

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

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1054,6 +1054,46 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
10541054
ERROR: recursive reference to query "x" must not appear within its non-recursive term
10551055
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
10561056
^
1057+
-- allow this, because we historically have
1058+
WITH RECURSIVE x(n) AS (
1059+
WITH x1 AS (SELECT 1 AS n)
1060+
SELECT 0
1061+
UNION
1062+
SELECT * FROM x1)
1063+
SELECT * FROM x;
1064+
n
1065+
---
1066+
0
1067+
1
1068+
(2 rows)
1069+
1070+
-- but this should be rejected
1071+
WITH RECURSIVE x(n) AS (
1072+
WITH x1 AS (SELECT 1 FROM x)
1073+
SELECT 0
1074+
UNION
1075+
SELECT * FROM x1)
1076+
SELECT * FROM x;
1077+
ERROR: recursive reference to query "x" must not appear within a subquery
1078+
LINE 2: WITH x1 AS (SELECT 1 FROM x)
1079+
^
1080+
-- and this too
1081+
WITH RECURSIVE x(n) AS (
1082+
(WITH x1 AS (SELECT 1 FROM x) SELECT * FROM x1)
1083+
UNION
1084+
SELECT 0)
1085+
SELECT * FROM x;
1086+
ERROR: recursive reference to query "x" must not appear within its non-recursive term
1087+
LINE 2: (WITH x1 AS (SELECT 1 FROM x) SELECT * FROM x1)
1088+
^
1089+
-- and this
1090+
WITH RECURSIVE x(n) AS (
1091+
SELECT 0 UNION SELECT 1
1092+
ORDER BY (SELECT n FROM x))
1093+
SELECT * FROM x;
1094+
ERROR: ORDER BY in a recursive query is not implemented
1095+
LINE 3: ORDER BY (SELECT n FROM x))
1096+
^
10571097
CREATE TEMPORARY TABLE y (a INTEGER);
10581098
INSERT INTO y SELECT generate_series(1, 10);
10591099
-- LEFT JOIN

‎src/test/regress/sql/with.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -479,6 +479,35 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x)
479479
WITH RECURSIVE x(n)AS (SELECT nFROM xUNION ALLSELECT1)
480480
SELECT*FROM x;
481481

482+
-- allow this, because we historically have
483+
WITH RECURSIVE x(n)AS (
484+
WITH x1AS (SELECT1AS n)
485+
SELECT0
486+
UNION
487+
SELECT*FROM x1)
488+
SELECT*FROM x;
489+
490+
-- but this should be rejected
491+
WITH RECURSIVE x(n)AS (
492+
WITH x1AS (SELECT1FROM x)
493+
SELECT0
494+
UNION
495+
SELECT*FROM x1)
496+
SELECT*FROM x;
497+
498+
-- and this too
499+
WITH RECURSIVE x(n)AS (
500+
(WITH x1AS (SELECT1FROM x)SELECT*FROM x1)
501+
UNION
502+
SELECT0)
503+
SELECT*FROM x;
504+
505+
-- and this
506+
WITH RECURSIVE x(n)AS (
507+
SELECT0UNIONSELECT1
508+
ORDER BY (SELECT nFROM x))
509+
SELECT*FROM x;
510+
482511
CREATE TEMPORARY TABLE y (aINTEGER);
483512
INSERT INTO ySELECT generate_series(1,10);
484513

0 commit comments

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