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

Commite7f9f44

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 parent2b4a2a7 commite7f9f44

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
@@ -883,25 +883,14 @@ checkWellFormedRecursion(CteState *cstate)
883883
cte->ctename),
884884
parser_errposition(cstate->pstate,cte->location)));
885885

886-
/* The left-hand operand mustn't contain self-reference at all */
887-
cstate->curitem=i;
888-
cstate->innerwiths=NIL;
889-
cstate->selfrefcount=0;
890-
cstate->context=RECURSION_NONRECURSIVETERM;
891-
checkWellFormedRecursionWalker((Node*)stmt->larg,cstate);
892-
Assert(cstate->innerwiths==NIL);
893-
894-
/* Right-hand operand should contain one reference in a valid place */
895-
cstate->curitem=i;
896-
cstate->innerwiths=NIL;
897-
cstate->selfrefcount=0;
898-
cstate->context=RECURSION_OK;
899-
checkWellFormedRecursionWalker((Node*)stmt->rarg,cstate);
900-
Assert(cstate->innerwiths==NIL);
901-
if (cstate->selfrefcount!=1)/* shouldn't happen */
902-
elog(ERROR,"missing recursive reference");
903-
904-
/* WITH mustn't contain self-reference, either */
886+
/*
887+
* Really, we should insist that there not be a top-level WITH, since
888+
* syntactically that would enclose the UNION. However, we've not
889+
* done so in the past and it's probably too late to change. Settle
890+
* for insisting that WITH not contain a self-reference. Test this
891+
* before examining the UNION arms, to avoid issuing confusing errors
892+
* in such cases.
893+
*/
905894
if (stmt->withClause)
906895
{
907896
cstate->curitem=i;
@@ -918,7 +907,9 @@ checkWellFormedRecursion(CteState *cstate)
918907
* don't make sense because it's impossible to figure out what they
919908
* mean when we have only part of the recursive query's results. (If
920909
* we did allow them, we'd have to check for recursive references
921-
* inside these subtrees.)
910+
* inside these subtrees. As for WITH, we have to do this before
911+
* examining the UNION arms, to avoid issuing confusing errors if
912+
* there is a recursive reference here.)
922913
*/
923914
if (stmt->sortClause)
924915
ereport(ERROR,
@@ -944,6 +935,28 @@ checkWellFormedRecursion(CteState *cstate)
944935
errmsg("FOR UPDATE/SHARE in a recursive query is not implemented"),
945936
parser_errposition(cstate->pstate,
946937
exprLocation((Node*)stmt->lockingClause))));
938+
939+
/*
940+
* Now we can get on with checking the UNION operands themselves.
941+
*
942+
* The left-hand operand mustn't contain a self-reference at all.
943+
*/
944+
cstate->curitem=i;
945+
cstate->innerwiths=NIL;
946+
cstate->selfrefcount=0;
947+
cstate->context=RECURSION_NONRECURSIVETERM;
948+
checkWellFormedRecursionWalker((Node*)stmt->larg,cstate);
949+
Assert(cstate->innerwiths==NIL);
950+
951+
/* Right-hand operand should contain one reference in a valid place */
952+
cstate->curitem=i;
953+
cstate->innerwiths=NIL;
954+
cstate->selfrefcount=0;
955+
cstate->context=RECURSION_OK;
956+
checkWellFormedRecursionWalker((Node*)stmt->rarg,cstate);
957+
Assert(cstate->innerwiths==NIL);
958+
if (cstate->selfrefcount!=1)/* shouldn't happen */
959+
elog(ERROR,"missing recursive reference");
947960
}
948961
}
949962

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

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1976,6 +1976,46 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
19761976
ERROR: recursive reference to query "x" must not appear within its non-recursive term
19771977
LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1)
19781978
^
1979+
-- allow this, because we historically have
1980+
WITH RECURSIVE x(n) AS (
1981+
WITH x1 AS (SELECT 1 AS n)
1982+
SELECT 0
1983+
UNION
1984+
SELECT * FROM x1)
1985+
SELECT * FROM x;
1986+
n
1987+
---
1988+
0
1989+
1
1990+
(2 rows)
1991+
1992+
-- but this should be rejected
1993+
WITH RECURSIVE x(n) AS (
1994+
WITH x1 AS (SELECT 1 FROM x)
1995+
SELECT 0
1996+
UNION
1997+
SELECT * FROM x1)
1998+
SELECT * FROM x;
1999+
ERROR: recursive reference to query "x" must not appear within a subquery
2000+
LINE 2: WITH x1 AS (SELECT 1 FROM x)
2001+
^
2002+
-- and this too
2003+
WITH RECURSIVE x(n) AS (
2004+
(WITH x1 AS (SELECT 1 FROM x) SELECT * FROM x1)
2005+
UNION
2006+
SELECT 0)
2007+
SELECT * FROM x;
2008+
ERROR: recursive reference to query "x" must not appear within its non-recursive term
2009+
LINE 2: (WITH x1 AS (SELECT 1 FROM x) SELECT * FROM x1)
2010+
^
2011+
-- and this
2012+
WITH RECURSIVE x(n) AS (
2013+
SELECT 0 UNION SELECT 1
2014+
ORDER BY (SELECT n FROM x))
2015+
SELECT * FROM x;
2016+
ERROR: ORDER BY in a recursive query is not implemented
2017+
LINE 3: ORDER BY (SELECT n FROM x))
2018+
^
19792019
CREATE TEMPORARY TABLE y (a INTEGER);
19802020
INSERT INTO y SELECT generate_series(1, 10);
19812021
-- LEFT JOIN

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

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

892+
-- allow this, because we historically have
893+
WITH RECURSIVE x(n)AS (
894+
WITH x1AS (SELECT1AS n)
895+
SELECT0
896+
UNION
897+
SELECT*FROM x1)
898+
SELECT*FROM x;
899+
900+
-- but this should be rejected
901+
WITH RECURSIVE x(n)AS (
902+
WITH x1AS (SELECT1FROM x)
903+
SELECT0
904+
UNION
905+
SELECT*FROM x1)
906+
SELECT*FROM x;
907+
908+
-- and this too
909+
WITH RECURSIVE x(n)AS (
910+
(WITH x1AS (SELECT1FROM x)SELECT*FROM x1)
911+
UNION
912+
SELECT0)
913+
SELECT*FROM x;
914+
915+
-- and this
916+
WITH RECURSIVE x(n)AS (
917+
SELECT0UNIONSELECT1
918+
ORDER BY (SELECT nFROM x))
919+
SELECT*FROM x;
920+
892921
CREATE TEMPORARY TABLE y (aINTEGER);
893922
INSERT INTO ySELECT generate_series(1,10);
894923

0 commit comments

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