diff options
| -rw-r--r-- | src/backend/parser/parse_cte.c | 53 | ||||
| -rw-r--r-- | src/test/regress/expected/with.out | 40 | ||||
| -rw-r--r-- | src/test/regress/sql/with.sql | 29 |
3 files changed, 102 insertions, 20 deletions
diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c index c5b1a49725c..d7ccb65d647 100644 --- a/src/backend/parser/parse_cte.c +++ b/src/backend/parser/parse_cte.c @@ -883,25 +883,14 @@ checkWellFormedRecursion(CteState *cstate) cte->ctename), parser_errposition(cstate->pstate, cte->location))); - /* The left-hand operand mustn't contain self-reference at all */ - cstate->curitem = i; - cstate->innerwiths = NIL; - cstate->selfrefcount = 0; - cstate->context = RECURSION_NONRECURSIVETERM; - checkWellFormedRecursionWalker((Node *) stmt->larg, cstate); - Assert(cstate->innerwiths == NIL); - - /* Right-hand operand should contain one reference in a valid place */ - cstate->curitem = i; - cstate->innerwiths = NIL; - cstate->selfrefcount = 0; - cstate->context = RECURSION_OK; - checkWellFormedRecursionWalker((Node *) stmt->rarg, cstate); - Assert(cstate->innerwiths == NIL); - if (cstate->selfrefcount != 1) /* shouldn't happen */ - elog(ERROR, "missing recursive reference"); - - /* WITH mustn't contain self-reference, either */ + /* + * Really, we should insist that there not be a top-level WITH, since + * syntactically that would enclose the UNION. However, we've not + * done so in the past and it's probably too late to change. Settle + * for insisting that WITH not contain a self-reference. Test this + * before examining the UNION arms, to avoid issuing confusing errors + * in such cases. + */ if (stmt->withClause) { cstate->curitem = i; @@ -918,7 +907,9 @@ checkWellFormedRecursion(CteState *cstate) * don't make sense because it's impossible to figure out what they * mean when we have only part of the recursive query's results. (If * we did allow them, we'd have to check for recursive references - * inside these subtrees.) + * inside these subtrees. As for WITH, we have to do this before + * examining the UNION arms, to avoid issuing confusing errors if + * there is a recursive reference here.) */ if (stmt->sortClause) ereport(ERROR, @@ -944,6 +935,28 @@ checkWellFormedRecursion(CteState *cstate) errmsg("FOR UPDATE/SHARE in a recursive query is not implemented"), parser_errposition(cstate->pstate, exprLocation((Node *) stmt->lockingClause)))); + + /* + * Now we can get on with checking the UNION operands themselves. + * + * The left-hand operand mustn't contain a self-reference at all. + */ + cstate->curitem = i; + cstate->innerwiths = NIL; + cstate->selfrefcount = 0; + cstate->context = RECURSION_NONRECURSIVETERM; + checkWellFormedRecursionWalker((Node *) stmt->larg, cstate); + Assert(cstate->innerwiths == NIL); + + /* Right-hand operand should contain one reference in a valid place */ + cstate->curitem = i; + cstate->innerwiths = NIL; + cstate->selfrefcount = 0; + cstate->context = RECURSION_OK; + checkWellFormedRecursionWalker((Node *) stmt->rarg, cstate); + Assert(cstate->innerwiths == NIL); + if (cstate->selfrefcount != 1) /* shouldn't happen */ + elog(ERROR, "missing recursive reference"); } } diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out index a01efa50a51..86e64602262 100644 --- a/src/test/regress/expected/with.out +++ b/src/test/regress/expected/with.out @@ -1976,6 +1976,46 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1) ERROR: recursive reference to query "x" must not appear within its non-recursive term LINE 1: WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1) ^ +-- allow this, because we historically have +WITH RECURSIVE x(n) AS ( + WITH x1 AS (SELECT 1 AS n) + SELECT 0 + UNION + SELECT * FROM x1) + SELECT * FROM x; + n +--- + 0 + 1 +(2 rows) + +-- but this should be rejected +WITH RECURSIVE x(n) AS ( + WITH x1 AS (SELECT 1 FROM x) + SELECT 0 + UNION + SELECT * FROM x1) + SELECT * FROM x; +ERROR: recursive reference to query "x" must not appear within a subquery +LINE 2: WITH x1 AS (SELECT 1 FROM x) + ^ +-- and this too +WITH RECURSIVE x(n) AS ( + (WITH x1 AS (SELECT 1 FROM x) SELECT * FROM x1) + UNION + SELECT 0) + SELECT * FROM x; +ERROR: recursive reference to query "x" must not appear within its non-recursive term +LINE 2: (WITH x1 AS (SELECT 1 FROM x) SELECT * FROM x1) + ^ +-- and this +WITH RECURSIVE x(n) AS ( + SELECT 0 UNION SELECT 1 + ORDER BY (SELECT n FROM x)) + SELECT * FROM x; +ERROR: ORDER BY in a recursive query is not implemented +LINE 3: ORDER BY (SELECT n FROM x)) + ^ CREATE TEMPORARY TABLE y (a INTEGER); INSERT INTO y SELECT generate_series(1, 10); -- LEFT JOIN diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql index 582139df7bd..b2817a5c633 100644 --- a/src/test/regress/sql/with.sql +++ b/src/test/regress/sql/with.sql @@ -889,6 +889,35 @@ WITH RECURSIVE x(n) AS (SELECT n FROM x) WITH RECURSIVE x(n) AS (SELECT n FROM x UNION ALL SELECT 1) SELECT * FROM x; +-- allow this, because we historically have +WITH RECURSIVE x(n) AS ( + WITH x1 AS (SELECT 1 AS n) + SELECT 0 + UNION + SELECT * FROM x1) + SELECT * FROM x; + +-- but this should be rejected +WITH RECURSIVE x(n) AS ( + WITH x1 AS (SELECT 1 FROM x) + SELECT 0 + UNION + SELECT * FROM x1) + SELECT * FROM x; + +-- and this too +WITH RECURSIVE x(n) AS ( + (WITH x1 AS (SELECT 1 FROM x) SELECT * FROM x1) + UNION + SELECT 0) + SELECT * FROM x; + +-- and this +WITH RECURSIVE x(n) AS ( + SELECT 0 UNION SELECT 1 + ORDER BY (SELECT n FROM x)) + SELECT * FROM x; + CREATE TEMPORARY TABLE y (a INTEGER); INSERT INTO y SELECT generate_series(1, 10); |